MySQL索引详解

简书 https://www.jianshu.com/p/7e3e2f814d22

聚簇索引

• 主键索引

辅助索引

• 单列索引
• 联合索引
• 前缀索引：截取字段的前几个字符去创建索引。

索引的管理

什么时候该创建索引

并不是将所有列都建立为索引，不是索引越多越好。

按照业务语句的需求创建合适的索引。

将索引语句建立在经常where group by order by join on 的条件

为什么不能乱建索引

• 如果冗余索引过多，表的数据变化的时候，很有可能导致索引频繁更新。

会阻塞很多正常的业务更新的需求。

• 索引过多，会导致优化器选择出现偏差。

数据更新、删除操作需要维护索引。

管理命令

• 查询表的索引情况

  desc t1;

  key:

  • PRI 聚簇索引

  • MUL 辅助索引

  • UNI 唯一索引

• 查看具体索引信息情况

  • show index from t1;

• 创建索引

  单列索引：alter table 表名 add index 索引名(列名);

  联合索引：alter table 表名 add index 索引名(列名1,列名2,…);

  前缀索引：alter table 表名 add index 索引名(列名(长度));

  索引名一般以idx_开头。

  注意最左原则

• 删除索引

  alter table 表名 drop index 索引名;

压力测试

source 导入100w数据

mysqlslap 压测

怎么减少回表

1.将查询尽可能用ID主键查询

2.设计合理的联合索引，完全覆盖

3.更精确的查询条件+联合索引

执行计划

查看执行计划

explain SQL语句
或
desc SQL语句

mysql> explain select * from product;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | product | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

table 此次执行计划涉及到的表

type 查询类型，全表，索引扫

possible_keys 可能会用到的索引

key 最后选择的索引

key_len 索引的覆盖程度

rows 此次查询扫描的行数

Extra 额外的信息

输出信息介绍

table 此次查询涉及到的表，针对一个查询中的多个表时，精确到问题表。

type 查询类型

• 全表扫描：不用任何索引，类型是ALL 需要优化。

  desc select * from product;
  desc select * from product where 1 = 1
  
  like '%%'、not in () 、!= 不走索引
  特殊情况：
  查询条件为主键时 not in 、!= 会走range索引。
  

• 索引扫描：index、 range 、ref、 eq_ref 、 const(system) 性能越好

  • index：全索引扫描，扫描索引树；

  • range：范围查询 >、 >=、 < 、<= 、 like ‘xx%’、 in、 or、 between and。

    in 可以改为union all 可能会提升性能。

  • ref 辅助索引等值查询

     desc select * from product where product_name = '111';
    

  • eq_ref 多表连接中，非驱动表连接条件是主键或唯一键。

  • const(system)：聚簇索引等值查询

    desc desc select * from product where product_id = 1;
    

possible_keys 可能会走的索引，所有和此次查询有关的索引；

key 此次查询选择的索引，一般我们不会去强制sql走某个索引。

key_len：联合索引的覆盖程度，

key_len的计算

对于索引 idx(a, b, c)，key_len = a的长度 + b的长度 + c的长度
长度指的是列的最大存储值的字节长度。
长度受到数据类型、字符集的影响。

数字：
	数字不受字符集的影响。
						not null		没有not null
	tinyint		1字节					1+1
	int				4字节					4+1
	bigint		8字节					8+1
	
	没有 not null 需要1个字节用来存储是否非空。
	比如：
	a int not null -- 4
	a int -- 5(4+1)
	
字符：
	utf8 一个字符最多占3个字节（utf8mb4 一个字符最多占4个字节）
								not null		没有not null
	char(10)				3*10				3*10+1
	varchar(10)			3*10+2			3*10+2+1

	对于varchar另外需要有存储长度的字节，最多2个字节。
	例子：
	b char(10) not null 		30
	b char(10) 							31
	c varchar(10) not null 	32
	c varchar(10)						33

extra：
using file sort 表示此次查询使用了文件排序，说明查询中有排序操作order by 、group by 、distinct 等。

需要将where条件和排序条件创建联合索引。

索引规范

• 一定要有主键，一般是数字类型的无关列。

• 最好使用唯一值多的列作为索引，如果索引列重复值较多，可以考虑使用联合索引，联合索引将重复值少的列放在最左侧。

• 经常做where 、order by、group by、join on 等条件操作的字段建立索引

  排序操作会浪费很多时间。

  where A B C – A,B,C

  where A group by B order by C – A,B,C

• 尽量使用前缀来索引，如果索引字段的值很长，最好使用值的前缀来索引。

• 限制索引的数目

  索引的数目不是越多越好，每个索引都需要占用磁盘空间。

  修改表时，对索引的重构和更新很麻烦，会使更新表变得浪费时间。

  优化器的负担会很重，有可能会影响到优化器的选择。

• 删除不再使用或者很少使用的索引（percona toolkit）

• 删除冗余索引（SQLyog工具可以查询冗余索引）

• 大表加索引以及索引维护时，要避开业务繁忙期。

• 尽量减少在经常更新的列上建索引。

不走索引的情况分析

• 没有查询条件或者查询条件没有建立索引

  在业务数据库中，特别是数据量比较大的表，是不允许有全表扫描着这种情况的。

  对用户查看非常慢，对服务器来讲也是毁灭性的。

  select * from t1;
  改成如下语句，需要在price上建索引。
  select * from t1 order by price limit 10;
  

• 查询结果是原表中的大部分数据，差不多15%-30%，优化器觉得没必要走索引了。

  如果业务允许，可以使用limit控制。如果没有更好的方式，可以将数据存放在redis等NoSQL数据库中。

• 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括（+ - * / ! 等）。

• 隐式转换导致索引失效，这点在开发中经常会犯的错误。

• < > not in 不走索引（辅助索引）。

• like ‘%_’ 百分号在前面不走索引，’%hello%'这类的搜索需求，可以使用elasticsearch solr mongodb 等专门做搜索服务的数据库产品。

优化分页查询

select id, device_id from req_log where id >= 
(select id from req_log order by create_at, id limit 100000, 1)
limit 10;

select id, device_id from req_log order by create_at, id limit 100000, 10;