索引

一、索引是什么

MySQL官方对索引的定义：索引（Index）是帮助MySQL高效获取数据的数据结构。因此索引的本质就是数据结构。索引的目的在于提高查询效率，可类比字典、书籍的目录等这种形式。

可简单理解为“排好序的快速查找数据结构”。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

平常所说的索引，如果没有特别指明，都是B树索引。其中聚集索引、次要索引、覆盖索引、前缀索引、唯一索引默认都是用B树。

通过show index from tablename可以查看表的索引情况。

索引的优缺点

优点

①类似大学图书馆的书目索引，提高数据的检索效率，降低数据库的IO成本。

②通过索引列对数据进行排序，降低数据的排序成本，从而降低CPU的消耗。

缺点

①索引实际上也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也要占用空间。

②虽然索引大大提高了查询效率，但是降低了更新表的速度，如insert、update和delete操作。因为更新表时，MySQL不仅要保存数据，还要保存索引文件每次更新的索引列字段，并且在更新操作后，会更新相应字段索引的信息。

③索引只是提高查询效率的一个因素，如果你的MySQL有大量的数据表，就需要花时间研究建立最优秀的索引或优化查询语句。

索引的分类

索引主要分为以下三类：

①单值索引：一个索引只包含单个列，一个表可以有多个单值索引。

②唯一索引：索引列的值必须唯一，但允许有空值，主键就是唯一索引。

③复合索引：一个索引包含多个列

索引的结构：

①BTREE索引；②Hash索引；③Full-Text索引；④R-Tree索引。

基本语法

① 创建索引

create [unique] index indexname on tablename(columnname(length));

alter table tablename add index indexname (columnname(length));

注：如果是char、varchar类型的字段，length可以小于字段实际长度；如果是blob、text类型，必须指定length。

② 删除索引

drop index indexname on tablename;

③ 查看索引

show index from tablename;

④ 其他创建索引的方式

添加主键索引 
ALTER TABLE `table_name` ADD PRIMARY KEY (`column`) 

添加唯一索引
ALTER TABLE `table_name` ADD UNIQUE (`column`) 

添加全文索引
ALTER TABLE `table_name` ADD FULLTEXT (`column`) 

添加普通索引
ALTER TABLE `table_name` ADD INDEX index_name (`column` ) 

添加组合索引 
ALTER TABLE `table_name` ADD INDEX index_name (`column1`, `column2`, `column3`)

建立索引与否的具体情况

① 需建立索引的情况

#1.主键自动建立唯一索引。

#2.频繁作为查询条件的字段。

#3.查询中与其他表关联的字段，外键关系建立索引。

#4.高并发下趋向创建组合索引。

#5.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

#6.查询中统计或分组字段。

②不需要创建索引的情况

#1.表记录太少。（数据量太少MySQL自己就可以搞定了）

#2.经常增删改的表。

#3.数据重复且平均分配的字段，如国籍、性别，不适合创建索引。

#4.频繁更新的字段不适合建立索引。

#5.Where条件里用不到的字段不创建索引。

二、索引分析

首先创建三张表：tb_emp(职工表)、tb_dept(部门表)和tb_desc(描述表)

1）tb_emp表。

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(20) NOT NULL,   `deptid` int(11) NOT NULL,   PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp`(username,deptid) VALUES ('Tom', '1');
INSERT INTO `tb_emp`(username,deptid) VALUES ('Jack', '1');
INSERT INTO `tb_emp`(username,deptid) VALUES ('Mary', '2');
INSERT INTO `tb_emp`(username,deptid) VALUES ('Rose', '3');

2）tb_dept表。

DROP TABLE IF EXISTS `tb_dept`;
CREATE TABLE `tb_dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_dept`(name) VALUES ('综合部');
INSERT INTO `tb_dept`(name) VALUES ('研发');
INSERT INTO `tb_dept`(name) VALUES ('测试');
INSERT INTO `tb_dept`(name) VALUES ('总裁');

3）tb_desc表。

DROP TABLE IF EXISTS `tb_desc`;
CREATE TABLE `tb_desc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `empid` int(11) DEFAULT NULL,
  `deptid` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_desc`(empid,deptid) VALUES (1, 1);
INSERT INTO `tb_desc`(empid,deptid) VALUES (2, 1);
INSERT INTO `tb_desc`(empid,deptid) VALUES (3, 2);
INSERT INTO `tb_desc`(empid,deptid) VALUES (4, 3);

注：这里强行将员工表与部门表不直接关联，通过第三张表（描述表）进行关联，主要为了进行join的分析。

left join

#1.首先执行查询。

#2.通过explain进行分析。

 

分析：从explain执行结果可以看出对两表都是用了全表扫描（ALL），并且在tb_desc表中还使用了join连接缓存，需要进行优化。但是如何优化？是在左表建立索引还是右表建立索引呢？因为左连接左表是全有，所以应该在右表建立索引。

#3.右表创建索引。

通过explain执行可以看到，在创建索引后，获得了比较不错的结果。（type=ref,Extra=Using index）。

结论：left join（左连接）情况下，应该在右表（tb_desc）创建索引。

right join

通过上面left join的例子，我们直接交换两表位置，并将left join改变成right join。

分析：

与left join进行对比，可以得到如下结论：

#1.在left join下，首先执行tb_emp（左表），type=ALL，因为左连接情况下左表全有，因此我们在tb_desc（右表）创建索引，得到比较理想的效果。

#2.在right join下（我们交换了tb_emp和tb_desc的位置），执行顺序：tb_emp（右表）→ tb_desc（左表）。右表type=ALL，因为右连接情况下右表全有，因此在左表（tb_desc，我们交换了位置）创建索引，效果肯定和left join一样。

总结

left join（左连接）：右表创建索引。

right join（右连接）：左表创建索引。

简记：左右外连接，索引相反建（left：右表建，right：左表建）。

三、索引优化

索引优化的目的主要是让索引不失效，本篇通过相关案例对索引优化进行讲解。

创建经典的tb_emp表。

DROP TABLE IF EXISTS `tb_emp`;
CREATE TABLE `tb_emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) NOT NULL,
  `age` int(11) NOT NULL,
  gender varchar(10) NOT NULL,  email varchar(20),
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- ----------------------------
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Tom', '22','male','1@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Mary', '21','female','2@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Jack', '27','male','3@qq.com');
INSERT INTO `tb_emp` (name,age,gender,email) VALUES ('Rose', '23','female','4@qq.com');

最左前缀法则

#1.定义：在创建了多列索引的情况下，查询从索引的最左前列开始且不能跳过索引中的列。

最佳左前缀法则就是说如果创建了多个索引，在使用索引时要按照创建索引的顺序来使用，不能缺少或跳过，当然如果只使用最左边的索引列，也就是第一个索引是可以的，通俗理解：“带头大哥不能死，中间兄弟不能断”。要点：“头不能掉”。下面将用案例进行说明。

#2.创建组合索引，并执行explain。

示例1：

分析：

①索引的创建顺序为name，age，gender；

②直接使用name（带头大哥）作为条件，可以看到type=ref，key_len=82，ref=const，效果还不错。

示例2：

分析：

没使用带头大哥（name），直接用兄弟，type=ALL，为全表扫描。

示例3：

 

分析：

①对比上面两句sql语句可发现：我们使用：火车头（name）和中间车厢（age）、火车头（name）和车尾（gender）。

②虽然type=ref，但是观察key_len和ref两项，并对比Case1中的结果，可得出在使用火车头（name）和车尾（gender）时，只使用了部分索引也就是火车头（name）的索引。

③通俗理解：火车头单独跑没问题，火车头与直接相连的车厢一起跑也没问题，但是火车头与车尾，如果中间没有车厢，只能火车头自己跑。

示例4：

分析：

火车头加车厢加车尾，三者串联，就变成了奔跑的小火车。type=ref，key_len=128，ref=const，const，const。

最佳左前缀法则总结：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。

不要在索引列上做任何操作

在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效从而转向全表扫描。

示例1：

分析：

这里使用了函数计算，type=ALL，导致索引失效。

示例2：

分析：

将name=‘Tom’的值修改为‘123’，使用sql后，发生了类型转换，type=ALL，导致全表扫描。

结论：在索引列上做任何操作，都会导致索引失效转向全表扫描。

范围右边全失效

存储引擎不能使用索引中范围右边的列，也就是说范围右边的索引列会失效。

示例1：

示例2：

示例3：

示例4：

对以上4个case进行分析：

①条件单独使用name时，type=ref，key_len=82，ref=const。

②条件加上age时（使用常量等值），type=ref，key_len=86，ref=const，const。

③当全值匹配时，type=ref，key_len=128，ref=const，const，const。说明索引全部用上，从key_len与ref可以看出。

④当使用范围时（age>27），type=range，key_len=86，ref=Null，与Case 1、Case2和Case3可知，使用了部分索引，但gender索引没用上（与Case 3对比）。

结论：范围右边的索引列失效。

尽量使用覆盖索引

尽量使用覆盖索引（查询列和索引列尽量一致，通俗说就是对A、B列创建了索引，然后查询中也使用A、B列），减少select *的使用。

示例1：

 示例2：

分析：

对比Case1和Case2，Case1使用select *，Case2使用覆盖索引（查询列与条件列对应），可看到Extra从Null变成了Using index，提高检索效率。

使用不等于（！=或<>）会使索引失效

结论：使用！=会使type=ALL，key=Null，导致全表扫描，并且索引失效。

is null 或 is not null也无法使用索引

示例1：

示例2：

 分析：

在使用is null的时候，索引完全失效，使用is not null的时候，type=ALL全表扫描，key=Null索引失效。

这里的例子可能有点特殊，具体情况肯能和case上的有所不同，但是还是要注意is null和is not null的使用。

like通配符以%开头会使索引失效

示例1：

示例2：

示例3：

分析：

①like的%位置不同，所产生的效果不一样，当%出现在左边的时候，type=ALL，key=Null（全表扫描，索引失效），当%出现在右边的时候，type=range，索引未失效。

②like查询为范围查询，%出现在左边，则索引失效。%出现在右边索引未失效。口诀：like百分加右边。

但是在实际生产环境中，%仅出现在右边可能不能够解决我们的问题，所以解决%出现在左边索引失效的方法：使用覆盖索引。

示例4：

分析：对比Case1可知，通过覆盖索引type=index，并且使用了Using index，从全表扫描变成了全索引扫描，还是不错的。

示例5：

分析：这里出现type=index，因为主键自动创建唯一索引。

示例6：

示例7：

 分析：由于只在（name，age，gender）上创建索引，当包含email时，导致结果集偏大（email未建索引）【锅大，锅盖小，不能匹配】，所以type=ALL。

字符串不加单引号导致索引失效

示例1：

分析：上述两条sql语句都能查询出相同的数据。

示例2：

 

分析：

通过explain执行结果可以看出，字符串（name）不加单引号在查询的时候，导致索引失效（type=ref变成了type=ALL，并且key=Null），并全表扫描。

结论：varchar类型的字段，在查询的时候不加单引号导致索引失效，转向全表扫描。

少用or，用or连接会使索引失效

结论：通过上述explain的执行结果可看出，在使用or连接的时候type=ALL，key=Null，索引失效，并全表扫描。

总结

①全值匹配。

②最佳左前缀法则：带头大哥不能死，中间兄弟不能断；带头大哥可跑路，老二也可跟着跑，其余兄弟只能死。

③索引列上不计算。

④覆盖索引记住用。

⑤不等于、is null、is not null导致索引失效。

⑥like百分加右边，加左边导致索引失效，解决方法：使用覆盖索引。

⑦字符串不加单引号导致索引失效。

⑧少用or，用or导致索引失效。

转载：https://www.cnblogs.com/developer_chan/p/9208404.html

理解索引：《彻底搞懂MySQL的索引》