关于mysql索引:mysql索引优化总结

7次阅读

共计 12180 个字符，预计需要花费 31 分钟才能阅读完成。

最近一周的工作都集中在慢 sql 的治理上，大部分都是基于索引进行优化，所以做了下述的总结。

explain（执行打算），应用 explain 关键字能够模仿优化器执行 sql 查问语句，从而晓得 MySQL 是如何解决 sql 语句。explain 次要用于剖析查问语句或表构造的性能瓶颈。

通过 explain + sql 语句能够晓得如下内容：

表的读取程序。（对应 id）
数据读取操作的操作类型。（对应 select_type）
哪些索引能够应用。（对应 possible_keys）
哪些索引被理论应用。（对应 key）
表间接的援用。（对应 ref）
每张表有多少行被优化器查问。（对应 rows）

explain 执行打算蕴含字段信息如下：别离是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、extra 12 个字段。每个字段对应的介绍如下。能够先建几张表举例。
上面建表各自举例子：

xCREATE TABLE `blog` (
  `blog_id` int NOT NULL AUTO_INCREMENT COMMENT '惟一博文 id-- 主键',
  `blog_title` varchar(255) NOT NULL COMMENT '博文题目',
  `blog_body` text NOT NULL COMMENT '博文内容',
  `blog_time` datetime NOT NULL COMMENT '博文公布工夫',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `blog_state` int NOT NULL COMMENT '博文状态 --0 删除 1 失常',
  `user_id` int NOT NULL COMMENT '用户 id',
  PRIMARY KEY (`blog_id`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `user` (
  `user_id` int NOT NULL AUTO_INCREMENT COMMENT '用户惟一 id-- 主键',
  `user_name` varchar(30) NOT NULL COMMENT '用户名 -- 不能反复',
  `user_password` varchar(255) NOT NULL COMMENT '用户明码',
  PRIMARY KEY (`user_id`),
  KEY `name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8

CREATE TABLE `discuss` (
  `discuss_id` int NOT NULL AUTO_INCREMENT COMMENT '评论惟一 id',
  `discuss_body` varchar(255) NOT NULL COMMENT '评论内容',
  `discuss_time` datetime NOT NULL COMMENT '评论工夫',
  `user_id` int NOT NULL COMMENT '用户 id',
  `blog_id` int NOT NULL COMMENT '博文 id',
  PRIMARY KEY (`discuss_id`)
) ENGINE=InnoDB AUTO_INCREMENT=61 DEFAULT CHARSET=utf8

1. id

示意查问中执行 select 子句或者操作表的程序，id 的值越大，代表优先级越高，越先执行。针对上面 sql 例子：

explain select discuss_body 
from discuss 
where blog_id = (
    select blog_id from blog where user_id = (select user_id from user where user_name = 'admin'));

三个表顺次嵌套，发现依照 id 从小到大排序的 table 值顺次是：discuss -> blog -> user。

2. select_type

示意 select 查问的类型，次要是用于辨别各种简单的查问，例如：一般查问、联结查问、子查问等。

SIMPLE：示意最简略的 select 查问语句，在查问中不蕴含子查问或者交并差集等操作。
PRIMARY：查问中最外层的 select（存在子查问的外层的表操作为 PRIMARY）。
SUBQUERY：子查问中首个 select。
DERIVED：被驱动的 select 子查问（子查问位于 from 子句）。
UNION：在 select 之后应用了 UNION。

3. table

查问的表名，并不一定是实在存在的表，有别名显示别名，也可能为长期表。当 from 子句中有子查问时，table 列是 <derivenN> 的格局，示意以后查问依赖 id 为 N 的查问，会先执行 id 为 N 的查问。

4. partitions

查问时匹配到的分区信息，对于非分区表值为 NULL，当查问的是分区表时，partitions 显示分区表命中的分区状况。

5. type

查问应用了何种类型，它在 SQL 优化中是一个十分重要的指标。

system: 当表仅有一行记录时（零碎表），数据量很少，往往不须要进行磁盘 IO，速度十分快。比方，Mysql 零碎表 proxies_priv 在 Mysql 服务启动时候曾经加载在内存中，对这个表进行查问不须要进行磁盘 IO。
```
explain select * from mysql.proxies_priv;
```
const: 单表操作的时候，查问应用了主键或者惟一索引。
```
explain select * from user where user_id=1;
```
eq_ref: 多表关联查问的时候，主键和惟一索引作为关联条件。如下图的 sql，对于 user 表（外循环）的每一行，user_role 表（内循环）只有一行满足 join 条件，只有查找到这行记录，就会跳出内循环，持续外循环的下一轮查问。
```
explain select u.user_name from user u,user_role ur where u.user_id= ur.user_id;
```
ref: 查找条件列应用了索引而且不为主键和惟一索引。尽管应用了索引，但该索引列的值并不惟一，这样即便应用索引查找到了第一条数据，依然不能进行，要在目标值左近进行小范畴扫描。但它的益处是不须要扫全表，因为索引是有序的，即使有反复值，也是在一个十分小的范畴内做扫描。
```
explain select user_id from user where user_name='admin';
```
ref_or_null: 相似 ref，会额定搜寻蕴含 NULL 值的行。
index_merge: 应用了索引合并优化办法，查问应用了两个以上的索引。新建 comment 表，id 为主键，value_id 为非惟一索引，执行explain select content from comment where value_id = 1181000 and id > 1000;，执行结果显示查问同时应用了 id 和 value_id 索引，type 列的值为 index_merge。
range: 有范畴的索引扫描，绝对于 index 的全索引扫描，它有范畴限度，因而要优于 index。像 between、and、’>’、'<‘、in 和 or 都是范畴索引扫描。
```
explain select * from user where user_id>0;
```
index: index 包含 select 索引列，order by 主键两种状况。
（1）order by 主键。这种状况会依照索引程序全表扫描数据，拿到的数据是依照主键排好序的，不须要额定进行排序。
```
explain select * from user order by user_id;
```
（2）select 索引列。type 为 index，而且 extra 字段为 using index，也称这种状况为索引笼罩。所须要取的数据都在索引列，无需回表查问。
```
explain select user_id from user_id;
```
all: 全表扫描，查问没有用到索引，性能最差。
```
explain select user_id from user;
```
6. possible_keys

此次查问中可能选用的索引。但这个索引并不定一会是最终查问数据时所被用到的索引。

7. key

此次查问中确切应用到的索引。

8. key_len

9. ref

10. rows

估算要找到所需的记录，须要读取的行数。评估 SQL 性能的一个比拟重要的数据，mysql 须要扫描的行数，很直观的显示 SQL 性能的好坏，个别状况下 rows 值越小越好。

11. filtered

存储引擎返回的数据在通过过滤后，剩下满足条件的记录数量的比例。

12. extra

示意额定的信息阐明。上面建两张表来举例：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `user_id` int DEFAULT NULL,
  `order_id` int DEFAULT NULL,
  `order_status` tinyint DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_userid_order_id_createdate` (`user_id`,`order_id`,`create_date`)
) ENGINE=InnoDB AUTO_INCREMENT=99 DEFAULT CHARSET=utf8

CREATE TABLE `t_orderdetail` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_id` int DEFAULT NULL,
  `product_name` varchar(100) DEFAULT NULL,
  `cnt` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_orderid_productname` (`order_id`,`product_name`)
) ENGINE=InnoDB AUTO_INCREMENT=152 DEFAULT CHARSET=utf8

using where: 查问的列未被索引笼罩，where 筛选条件非索引的前导列。对存储引擎返回的后果进行过滤（Post-filter，后过滤），个别产生在 MySQL 服务器，而不是存储引擎层。
```
explain select order_id,order_status from t_order where order_id=1;
```
using index: 查问的列被索引笼罩，并且 where 筛选条件合乎最左前缀准则，通过索引查找就能间接找到符合条件的数据，不须要回表查问数据。
```
explain select user_id,order_id,create_date from t_order where user_id=1;
```
Using where&Using index: 查问的列被索引笼罩，但无奈通过索引查找找到符合条件的数据，不过能够通过索引扫描找到符合条件的数据，也不须要回表查问数据。

包含两种状况：
（1）where 筛选条件不合乎最左前缀准则

explain select user_id,order_id,create_date from t_order where order_id=1;

（2）where 筛选条件是索引列前导列的一个范畴

explain select user_id,order_id,create_date from t_order where user_id>1;

null: 查问的列未被索引笼罩，并且 where 筛选条件是索引的前导列，也就是用到了索引，然而局部字段未被索引笼罩，必须回表查问这些字段，Extra 中为 NULL。
```
explain select user_id,order_id,order_status from t_order where user_id=1;
```
using index condition: 索引下推（index condition pushdown，ICP），先应用 where 条件过滤索引，过滤完索引后找到所有合乎索引条件的数据行，随后用 where 子句中的其余条件去过滤这些数据行。
using temporary: 应用了长期表保留两头后果，常见于 order by 和 group by 中。典型的，当 group by 和 order by 同时存在，且作用于不同的字段时，就会建设长期表，以便计算出最终的后果集。
filesort: 文件排序。示意无奈利用索引实现排序操作，以下状况会导致 filesort：
- order by 的字段不是索引字段
- select 查问字段不全是索引字段
- select 查问字段都是索引字段，然而 order by 字段和索引字段的程序不统一
```
explain select * from t_order order by order_id;
```
using join buffer: Block Nested Loop，须要进行嵌套循环计算。两个关联表 join，关联字段均未建设索引，就会呈现这种状况。比方内层和外层的 type 均为 ALL，rows 均为 4，须要循环进行 4 * 4 次计算。常见的优化计划是，在关联字段上增加索引，防止每次嵌套循环计算。

同样提前建表用于演示：

CREATE TABLE `student_info` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `course_id` int NOT NULL,
  `class_id` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8;

CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `course_id` int NOT NULL,
  `course_name` varchar(40) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=101 DEFAULT CHARSET=utf8;

#筹备数据
select count(*) from student_info;#1000000
select count(*) from course;      #100

1. 优先应用更快的索引（联结索引）

如下一条 sql 语句是没有索引的状况：

select * from student_info where name='123' and course_id=1 and class_id=1;

咱们通过建设索引来优化它的查问效率：

建设一般索引：

# 建设一般索引
create index idx_name on student_info(name);
#均匀耗时 25 毫秒，查看 explain 执行打算，应用到的是 idx_name 索引查问
select * from student_info where name='MOKiKb' and course_id=1 and class_id=1;

在一般索引的根底上，再减少联结索引：

#name，course_id 组成的联结索引
create index idx_name_courseId on student_info(name,course_id);
#该查问语句个别应用的是联结索引，而不是一般索引，具体看优化器决策
#均匀耗时 20ms
select * from student_info where name='zhangsan' and course_id=1 and class_id=1;

通过执行打算后果能够看到，在多个索引都能够应用时，零碎个别优先应用更长的联结索引，因为联结索引相比来说更快，这点应该也很好了解，前提是要恪守联结索引的最左匹配准则。
如果再创立一个 name,course_id,class_id 组成的联结索引，那么上述 sql 语句不出意外会应用这个 key_len 更长的联结索引(意外是优化器可能会抉择其余更优的计划，如果它更快的话)。

联结索引速度不肯定优于一般索引，比方第一个条件就过滤了所有记录，那么就没必要用后序的索引了。

2. 最左匹配准则

create index idx_name_cou_cls on student_info(name,course_id,class_id);

联结索引全副匹配的状况：
```
# 关联字段的索引比拟残缺
explain select * from student_info where name='11111' and course_id=10068 and class_id=10154;
```
该 sql 语句合乎最左前缀准则，每个字段条件中的字段恰好和联结索引吻合。这种状况是最优的，因为依附一个联结索引就能够疾速查找，不须要额定的查问。
联结索引最左边缺失的状况：
```
explain select * from student_info where name='11111' and course_id=10068;
```
该 sql 语句条件中，并不含有联结索引的全副条件，而是抹去了右半局部，该语句应用的索引仍旧是该关联查问，只不过只用到了一部分，通过查看 key_len 能够晓得少了 5 字节，这 5 字节对应的是 class_id，证实 class_id 并未失效而已 (where 中没有，当然用不到啦)。
同理，抹掉 where 中的 course_id 字段，联结索引依旧会失效，只是 key_len 会减小。
联结索引两头缺失的状况：
```
# 联结索引两头的字段未应用，而右边和左边的都存在
explain select * from student_info where name='11111' and class_id=10154;;
```
如上 sql 语句仍旧应用的是联结索引，然而它的 key_len 变小了，只有 name 字段应用到了索引，而 class_id 字段尽管在联结索引中，然而因为不合乎最左匹配准则而 GG 了。
整个 sql 语句的执行流程为：先在联结索引的 B 树中找到所有 name 为 11111 的记录，而后全文过滤掉这些记录中 class_id 不是 10154 的记录。多了一个全文搜寻的步骤，相比于①和②状况性能会更差。
联结索引最右边缺失的状况：
```
explain select * from student_info where class_id=10154 and course_id=10068;
```
该状况是上一个状况的特例，联结索引中最右边的字段未找到，所以尽管有其余局部，然而通通都生效了，走的是全文查找。

论断：最左匹配准则指的是查问从索引的最左列开始，并且不能跳过索引中的列，如果跳过了某一列，索引将局部生效(前面的字段索引全副生效)。

留神：创立联结索引时，字段的程序就定格了，最左匹配就是依据该程序比拟的；然而在查问语句中，where 条件中字段的程序是可变的，意味着不须要依照关联索引字段的程序，只有 where 条件中有就行了。

3. 范畴条件左边的列索引生效

承接下面的联结索引，应用如下 sql 查问

#key_len=> name:63,course_id:5,class_id:5
explain select * from student_info where name='11111' and course_id>1 and class_id=1;

执行打算中 key_len 只有 68，代表关联索引中 class_id 未应用到，尽管合乎最左匹配准则，但因为 > 符号让关联索引中该条件字段左边的索引生效了。

但如果应用 >= 号的话

# 不是 >、<, 而是 >=、<=
explain select * from student_info where name='11111' and course_id>=20 and course_id<=40 and class_id=1;

左边的索引并未生效，key_len 为 73，所有字段的索引都应用到了。

论断：为了充分利用索引，咱们有时候能够将 >、< 等价转为 >=、<= 的模式，或者将可能会有 <、> 的条件的字段尽量放在关联索引靠后的地位。

4. 计算、函数导致索引生效

# 未应用索引，破费工夫更久
explain select * from student_info where LEFT(name,2)='li';
#相似的也不会应用索引
explain select * from student_info where name+''='lisi';

论断：字段应用函数会让优化器无从下手，B 树中的值和函数的后果可能不搭边，所以不会应用索引，即索引生效。 字段能不必就不必函数。

5. 类型转换导致索引生效

# 不会应用 name 的索引
explain select * from student_info where name=123;
#应用到索引
explain select * from student_info where name='123';

如上，name 字段是 VARCAHR 类型的，然而比拟的值是 INT 类型的，name 的值会被隐式的转换为 INT 类型再比拟，两头相当于有一个将字符串转为 INT 类型的函数。

6. 不等于 (!= 或者 <>) 索引生效

# 创立索引
create index idx_name on student_info(name);
#索引生效
explain select * from student_info where name<>'zhangsan';
explain select * from student_info where name!='zhangsan';

不等于的状况是不会应用索引的。因为!= 代表着要进行全文的查找，用不上索引。

7. is (not) null 回表多，索引生效

is null / is not null 自身是反对走索引的，但在少数场景中确实没有走索引，为何公众误会认为 is null、is not null、!= 这些判断条件会导致索引生效而全表扫描呢？

导致索引生效而全表扫描的通常是因为一次查问中回表数量太多。mysql 计算认为应用索引的工夫老本高于全表扫描，于是 mysql 宁肯全表扫描也不违心应用索引。应用索引的工夫老本高于全表扫描的临界值能够简略得记忆为 20% 左右。

也就是如果一条查问语句导致的回表范畴超过全副记录的 20%，则会呈现索引生效的问题。而 is null、is not null、!= 这些判断条件常常会呈现在这些回表范畴很大的场景，而后被人误会为是这些判断条件导致的索引生效。

8. like 以 % 结尾，索引生效

# 应用到了索引
explain select * from student_info where name like 'li%';
#索引生效
explain select * from student_info where name like '%li';

只有以 % 结尾就无奈应用索引，因为如果以 % 结尾，在 B 树排序的数据中并不好找。

9. OR 前后存在非索引的列，索引生效

# 创立好索引
create index idx_name on student_info(name);
create index idx_courseId on student_info(course_id);

如果 or 前后都是索引，则失常走索引：

# 应用索引
explain select * from student_info where name like 'li%' or course_id=200;

如果其中一个没有索引，那么索引就生效了，假如还是应用索引，那就变成了先通过索引查，而后再依据没有的索引的字段进行全表查问，这种形式还不如间接全表查问来的快。

explain select * from student_info where name like 'li%' or class_id=1;

10. 字符集不对立

字符集如果不同，会存在隐式的转换，索引也会生效，所有应该应用雷同的字符集，避免这种状况产生。

1. 什么是回表查问

个别状况下是：先到一般索引上定位主键值，再到汇集索引上定位行记录，它的性能较扫一遍索引树低。

具体解释：

一般索引： 咱们本人建的索引不论是单列索引还是联结索引，都称为 一般索引，每个一般索引就对应着一颗独立的索引 B + 树，索引 B+ 树的节点仅仅蕴含了索引里的几个字段的值以及主键值。
聚簇索引： 主键索引是 聚簇索引，也就是索引的叶子节点存的是整个单条记录的所有字段值。

在什么状况会呈现回表操作呢？举个例子：假如表 tbl 有 a,b,c 三个字段，其中 a 是主键，b 上建了索引。

当编写 sql 语句 select * from tbl where a=1; 这样不会产生回表，因为所有的数据在 a 的索引树中均能找到；
当编写 sql 语句 select a,b from tbl where b=1; 这样也不会产生回表，因为 a、b 数据在 b 的索引树中也都能找到；
但如果是 select * from tbl where b=1; 这样就会产生回表。因为 where 条件是 b 字段，那么会去 b 的索引树里查找数据，但 b 的索引外面只有 a,b 两个字段的值，没有 c，那么这个查问为了取到 c 字段，就要取出主键 a 的值，而后去 a 的索引树去找 c 字段的数据。查了两个索引树，就呈现了回表操作。

2. 什么是索引笼罩？

简略说就是，索引列 + 主键蕴含 select 到 from 之间查问的列。就是索引笼罩。能够不必去进行回表操作。

3. 为什么设置了命中了索引但还是造成了全表扫描

就是尽管命中了索引，但在叶子节点查问到记录后还要大量的回表，优化器认为不如间接去扫描全表。

1. 联结索引的劣势

相较于一般的单列索引而言，联结索引的劣势如下：

如果正确应用，一个联结索引能够抵得上多个单列索引。建了一个 (a,b,c) 的复合索引，那么理论等于建了 (a),(a,b),(a,b,c) 三个索引。因为每多一个索引，都会减少写操作的开销和磁盘空间的开销。对于大量数据的表，这可是不小的开销！
笼罩索引。同样的有复合索引 (a,b,c)，如果有如下的 sql: select a,b,c from table where a=1 and b = 1。那么 MySQL 能够间接通过遍历索引获得数据，而无需回表，这缩小了很多的随机 io 操作。缩小 io 操作，特地的随机 io 其实是 dba 次要的优化策略。所以，在真正的理论利用中，笼罩索引是次要的晋升性能的优化伎俩之一。
索引列越多，通过索引筛选出的数据越少。有 1000W 条数据的表，有如下 sql:select * from table where a = 1 and b =2 and c = 3, 假如假如每个条件能够筛选出 10% 的数据。
（1）如果只有单列索引，那么通过该索引能筛选出 1000W*10%=100w 条数据，而后再回表从 100w 条数据中找到合乎 b =2 and c= 3 的数据，而后再排序，再分页；
（2）如果是联结索引，通过索引筛选出 1000w 10% 10% *10%=1w，而后再排序、分页，哪个更高效，一眼便知。

2. 联结索引的字段程序

大家都晓得联结索引的最左匹配准则，因而创立联结索引时，如何保障索引中字段的程序就很要害。

集体总结经验：剖析表构造的业务查问需要，找出查问优先级从高到低的字段，在索引中从左往右。

例如：咱们在做 toB 的我的项目，须要对不同客户机构做数据隔离，就要求所有建表时都蕴含 org_id 字段，所有查问都要过滤。因而优先级最高的就是“机构 ID”；而后这是张机构导航表，简直所有查问都须要依据导航组过滤，因而第二优先级就是“导航组 ID”；其次再是“导航编号”或“导航名称”等。所以建联结索引时必须要保障是 (“机构 ID”,“导航组 ID”,...) 结尾，以保障能让绝大多数的查问能尽可能匹配该索引更多的字段。

通过索引优化来实现 MySQL 的 order by 语句优化：

1. 无 where 排序

order by 的索引优化。如果一个 SQL 语句形如：

select [column1],[column2],…. from [TABLE] order by [sort];

在 [sort] 这个栏位上建设索引就能够实现利用索引进行 order by 优化。

2. where 一个字段排序

order by 的索引优化。如果一个 SQL 语句形如：

select [column1],[column2],…. from [TABLE] where [columnX] = [value] order by [sort];

建设一个联结索引 (columnX,sort) 来实现 order by 优化。

留神：如果 columnX 对应多个值，如上面语句就无奈利用索引来实现 order by 的优化！

select [column1],[column2],…. from [TABLE] where [columnX] IN ([value1],[value2],…) order by[sort];

3. where 多个字段排序

select * from [table] where uid=1 ORDER x,y LIMIT 0,10;

建设索引 (uid,x,y) 实现 order by 的优化, 比建设 (x,y,uid) 索引成果要好得多。

MySQL order by 不能 应用索引来优化排序的状况：

对不同的索引键做 order by：(key1,key2 别离建设索引)
```
select * from t1 order by key1, key2;
```
在非间断的索引键局部上做 order by：(key_part1,key_part2 建设联结索引;key2 建设索引)
```
select * from t1 where key2=constant order by key_part2;
```
同时应用了 ASC 和 DESC：(key_part1,key_part2 建设联结索引)。
```
select * from t1 order by key_part1 DESC, key_part2 ASC;
```
用于搜寻记录的索引键和做 order by 的不是同一个：(key1,key2 别离建设索引)。
```
select * from t1 where key2=constant order by key1;
```
如果在 where 和 order by 的栏位上利用表达式 (函数) 时，则无奈利用索引来实现 order by 的优化。
```
select * from t1 order by YEAR(logindate) LIMIT 0,10;
```