MySQL数据库索引管理————(增删改查)

Wesley13
• 阅读 422
  • 索引的概念
  • 数据库建立索引的原则
  • 查看索引
    • 语法格式
    • 示例
    • 显示信息蚕食描述
  • 普通索引
    • 概述
    • 创建普通索引
      • 创建方式
      • 示例
  • 删除索引
    • 删除索引的方式
    • 示例
  • 唯一索引
    • 概述
    • 创建唯一索引
      • 语法格式
      • 示例
  • 主键索引
    • 概述
    • 创建主键索引
  • 全文索引
    • 概述
    • 创建全文索引
      • 创建方法
      • 示例
  • 组合索引
    • 概述
    • 创建组合索引
      • 创建方式

索引的概念

  • 是一个排序的列表,存储着索引值和这个值所对应的物理地址
  • 无须对整个表进行扫描,通过物理地址就可以找到所需数据
  • 是表中一列或者若干列值排序的方法
  • 需要额外的磁盘空间

数据库建立索引的原则

  1. 确定针对该表的操作是大量的查询操作还是大量的增删改操作;
  2. 尝试建立索引来帮助特定的查询。检查自己的sql语句,为那些频繁在where子句中出现的字段建立索引;
  3. 尝试建立复合索引来进一步提高系统性能。修改复合索引将消耗更长时间,同时复合索引也占磁盘空间;
  4. 对于小型的表,建立索引可能会影响性能;
  5. 应该避免对具有较少值的字段进行索引;
  6. 避免选择大型数据类型的列作为索引。

查看索引

语法格式

SHOW INDEX FROM 表名;
SHOW KEYS FROM表名 ;

示例

mysql> create table grade(
    -> 学号 int(16) not null,
    -> 姓名 char(16) not null,
    -> 班级 char(16) not null,
    -> 成绩 int(3) not null,
    -> primary key(学号));
Query OK, 0 rows affected (0.01 sec)
mysql> show keys from grade;   //第一种查看方式
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)

mysql> show index from grade;   //第二种查看方式
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> show index from grade \G;   //末尾加上\G表示竖向查看
*************************** 1. row ***************************
        Table: grade
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: 学号
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
1 row in set (0.00 sec)

显示信息蚕食描述

参数

描述

Table

表的名称

Non_unique

索引值得唯一性,0表示唯一,1表示不唯一

Key_name

索引的名称

Seq_in_index

索引的序列号,从1开始

Column_name

列的名称

普通索引

概述

  • 最基本的索引类型,没有唯─性之类的限制
  • 创建普通索引的方式

创建普通索引

创建方式

 1. 创建表时创建索引
 2. CREATE INDEX 索引名 ON 表名 (列名);
 3. ALTER TABLE 表名 ADD INDEX 索引名 (列名);

示例

mysql> create table ltp(
    ->  id int(4) not null primary key auto_increment,
    ->  name varchar(10) not null,
    ->  score decimal not null,
    -> hobby int(2) not null default '1',
    -> index index_scrore (score));
Query OK, 0 rows affected (0.01 sec)

mysql> show keys from ltp;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tp   |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| tp   |          1 | index_scrore |            1 | score       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
mysql> create index name on grade(姓名);  //使用create方式新增索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show keys from grade;  
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          1 | name     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> alter table grade add index 姓名(姓名);  //使用alter方式新建索引
Query OK, 0 rows affected, 1 warning (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show keys from grade;   //查看到新增了一条姓名索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          1 | name     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          1 | 姓名     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

删除索引

删除索引的方式

DROP INDEX 索引名 ON 表名;
ALTERTABLE 表名 DROP INDEX 索引名;

示例

mysql> drop index name on grade;  //删除name索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from grade;  //查看,name索引已经被删除
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          1 | 姓名     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> alter table grade drop index 姓名;  //删除姓名索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show keys from grade;  //查看,删除成功
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

唯一索引

概述

  • “普通索引”基本相同
  • 与普通索引的区别是索引列的所有值只能出现一次,即必须唯一
  • 创建唯一索引的方式

创建唯一索引

语法格式

1.创建表时创建索引
2.CREATE UNIQUE INDEX 索引名 ON 表名(列名);
3.ALTER TABLE 表名 ADD UNIQUE 索引名(列名);

示例

mysql> create table lllx (   //创建表的方式创建
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> score decimal not null,
    -> hobby int(2) not null default '1',
    -> unique index index_scrore (score));
Query OK, 0 rows affected (0.01 sec)

mysql> show keys from lllx;
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| lllx  |          0 | PRIMARY      |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| lllx  |          0 | index_scrore |            1 | score       | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> create unique index name on grade(姓名);   //新建name索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show keys from grade;   //查看索引,新建成功
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          0 | name     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
 
mysql> alter table grade drop index name;    //删除name索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0


mysql> alter table grade add unique 姓名(姓名);  //新建姓名索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from grade;  //查看,新建成功
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          0 | 姓名     |            1 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> alter table grade drop index 姓名;   //删除姓名索引
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引

概述

  • 是一种特殊的唯一索引,指定为“PRIMARY KEY",
  • 一个表只能有一个主键,不允许有空值
  • 创建表时必须创建,创建后不能删除

创建主键索引

mysql> create table test(   //创建表的方式创建索引
    -> id int(10) not null auto_increment,
    -> title char(255) not null,
    -> primary key (`id`));
Query OK, 0 rows affected (0.01 sec)

mysql> show keys from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

全文索引

概述

  • MySQL从3.23.23版开始支持全文索引和全文检索
  • 索引类型为FULLTEXT
  • 可以在CHAR、VARCHAR或者TEXT类型的列上创建

创建全文索引

创建方法

在创建表时创建索引
CREATE FULLTEXT INDEX 索引名 ON 表名(列名);
ALTER TABLE 表名 ADD FULLTEXT 索引名(列名);

示例

mysql> create table article (  新建方式创建索引
    -> 标题 char(48) not null,
    -> 目录 varchar(255) default null,
    -> 正文 varchar(8096) not null,
    -> primary key (标题),
    -> fulltext (正文));
Query OK, 0 rows affected (0.34 sec)

mysql> show keys from article;
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | 标题        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | 正文     |            1 | 正文        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)

mysql> alter table article add fulltext page(目录);  //alter方式新创建page索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show keys from article;  //查看,创建成功
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | 标题        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | 正文     |            1 | 正文        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
| article |          1 | page     |            1 | 目录        | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

mysql> drop index page on article;  //删除page索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create fulltext index mulu on article(目录);  //使用create方式创建mulu索引
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from article;  查看,创建成功
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table   | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| article |          0 | PRIMARY  |            1 | 标题        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | 正文     |            1 | 正文        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
| article |          1 | mulu     |            1 | 目录        | NULL      |           0 |     NULL | NULL   | YES  | FULLTEXT   |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

组合索引

概述

  • 可以是单列上创建的索引,也可以是在多列上创建的索引
  • 最左原则,从左往右依次执行
  • 创建组合索引的方式

创建组合索引

创建方式

1.创建表时创建索引
2.CREATE UNIQUE INDEX 索引名 ON 表名(列名1,列名2,……);
3.ALTER TABLE 表名 ADD UNIQUE 索引名(列名1,列名2,……);


mysql> create table users (    //创建表的方式创建
    -> name char(9),
    -> age int(3),
    -> sex tinyint(1),
    -> index user (name,age,sex));
Query OK, 0 rows affected (0.00 sec)
mysql> show keys from user;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user  |          1 | user     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | user     |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| user  |          1 | user     |            3 | sex         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> create unique index student on grade(学号,姓名,成绩);  //给学号,姓名,成绩这几列创建索引,名为student
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from grade;   //查看,创建成功
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          0 | student  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          0 | student  |            2 | 姓名        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          0 | student  |            3 | 成绩        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

mysql> drop index student on grade;  //删除student索引
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table grade add fulltext 学生(姓名,班级); //给姓名,班级两列创建全文索引,索引名为学生
Query OK, 0 rows affected, 1 warning (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 1

mysql> show keys from grade;  查看,创建成功
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| grade |          0 | PRIMARY  |            1 | 学号        | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| grade |          1 | 学生     |            1 | 姓名        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
| grade |          1 | 学生     |            2 | 班级        | NULL      |           0 |     NULL | NULL   |      | FULLTEXT   |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
添砖java的啾 添砖java的啾
2年前
distinct效率更高还是group by效率更高?
目录00结论01distinct的使用02groupby的使用03distinct和groupby原理04推荐groupby的原因00结论先说大致的结论(完整结论在文末):在语义相同,有索引的情况下groupby和distinct都能使用索引,效率相同。在语义相同,无索引的情况下:distinct效率高于groupby。原因是di
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
Wesley13 Wesley13
1年前
MySQL索引类型
一、简介MySQL目前主要有以下几种索引类型:1.普通索引2.唯一索引3.主键索引4.组合索引5.全文索引二、语句CREATETABLEtable_namecol_namedatatypeunique|fulltextindex|keyindex_name(c
Wesley13 Wesley13
1年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
1年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Stella981 Stella981
1年前
ES中删除索引的mapping字段时应该考虑的点
1.创建新索引2.新索引创建新mapping3.原索引导出数据到新索引4.新索引创建原索引一致的别名5.删除原索引针对于第四步:这个就要用到索引别名了,如果你最开始建索引的时候没有考虑设计索引别名,那就杯具了。你可以把索引的名称设置成name\_v1 别名设置为name,然后代码里面访问搜索的时候连接的其实是别名na
Wesley13 Wesley13
1年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
Stella981 Stella981
1年前
ELK学习笔记之ElasticSearch的索引详解
0x00ElasticSearch的索引和MySQL的索引方式对比Elasticsearch是通过Lucene的倒排索引技术实现比关系型数据库更快的过滤。特别是它对多条件的过滤支持非常好,比如年龄在18和30之间,性别为女性这样的组合查询。倒排索引很多地方都有介绍,但是其比关系型
京东云开发者 京东云开发者
9个月前
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究