MySQL（5） ——性能优化之索引优化

一、最左前缀原则

索引可以包含一个列或多个列的值。如果是多列，顺序也很重要，MySQL只能高效地使用索引最左前缀列。
比如对User表的name和city加联合索引就是(name,city)，最左前缀原则指的是：如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。

-- 可以命中索引
select * from user where name=xx and city=xx ;
-- 可以命中索引
select * from user where name=xx ; 
-- 无法命中索引
select * from user where city=xx ;

注意：查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，由此可以命中索引。

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDER BY子句也遵循此规则。

原理：
比如建立联合索引（a,b），MySQL数据库中a,b的值如下：
（1,2）、（1,3）、（2,2）、（2,4）、（3,1）、（3,3）
其索引树（B-Tree）如下图:

可以看到，a的顺序：1-1-2-2-3-3 是按顺序排列的；而b的顺序是2-3-2-4-1-3，非按顺序的。
但是在a相同的情况下，b是按顺序排列的，即2-3,2-4,1-3排列。
联合索引的规则是：首先会对联合索引的最左边第一个字段排序，在第一个字段的排序基础上，然后在对第二个字段进行排序——最左前缀原则

二、索引优化

创建表

CREATE TABLE `ums_permission` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `pid` bigint(20) DEFAULT NULL COMMENT '父级权限id',
  `name` varchar(100) DEFAULT NULL COMMENT '名称',
  `value` varchar(200) DEFAULT NULL COMMENT '权限值',
  `icon` varchar(500) DEFAULT NULL COMMENT '图标',
  `type` int(1) DEFAULT NULL COMMENT '权限类型：0->目录；1->菜单；2->按钮（接口绑定权限）',
  `uri` varchar(200) DEFAULT NULL COMMENT '前端资源路径',
  `status` int(1) DEFAULT NULL COMMENT '启用状态；0->禁用；1->启用',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `sort` int(11) DEFAULT NULL COMMENT '排序',
  PRIMARY KEY (`id`),
  KEY `idx_p_n_u_ct` (`pid`,`name`,`uri`,`create_time`)
) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8 COMMENT='后台用户权限表';

创建联合索引

create index idx_p_n_v_s on ums_permission(pid,name,value,status)

（一）全值匹配

explain select * from ums_permission where pid=1 and name='商品列表' and value='pms:product:read' and status=1

（二）最佳左前缀法则

1. 缺少联合索引的第一个值(最左前值)

explain select * from ums_permission where name='商品列表' and value='pms:product:read' and status=1

2. 缺少联合索引中间的值（跳索引列）

explain select * from ums_permission where pid=1  and value='pms:product:read' and status=1

可见，只使用到了第一列索引，即pid，value和status列未使用索引。

（三）操作索引（计算、函数、类型转换等）会导致索引失效

explain select * from ums_permission where pid +1 = 2  and name= '商品列表' and value='pms:product:read' and status=1

（四）范围条件右边的索引列将失效

explain select * from ums_permission where pid <2  and name= '商品列表' and value='pms:product:read' and status=1

只有pid列的索引生效

（五）尽量使用覆盖索引

覆盖索引：select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫做覆盖索引。覆盖索引必须要存储索引的列，而哈希索引、空间索引和全文索引等都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。

当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

explain select pid,name,value,status from ums_permission where pid =1  and name= '商品列表' and value='pms:product:read' and status=1

注意与（一）中select * 的差别

（六）不等于（!=,<>）会导致索引失效

explain select * from ums_permission where pid <>2  and name= '商品列表' and value='pms:product:read' and status=1

（七）is null & is not null对索引的影响

1. pid中2个null，16个非null

explain select * from ums_permission where pid is null  and name ='商品列表' and value = 'pms:product:read' and status=1

is null 正常使用索引

explain select * from ums_permission where pid is not null  and name ='商品列表' and value = 'pms:product:read' and status=1

is not null 不使用索引

2. pid中2个非null，16个null

explain select * from ums_permission where pid is null  and name ='商品列表' and value = 'pms:product:read' and status=1

is null 使用索引

explain select * from ums_permission where pid is not null  and name ='商品列表' and value = 'pms:product:read' and status=1

is not null 使用了pid列的索引

总结：
is null ，is not null是否使用索引，和数据量或者和其他元素有关系，sql优化器在执行的时候会计算成本，和基数、选择性、直方图有关，看搜索的部分占全表的比例，再决定使用索引还是全表成本低。

（八）like对索引的影响

1. %在参数右侧

explain select * from ums_permission where pid =1 and name like'商品%' and value = 'pms:product:read' and status=1

正常使用索引

2. %在参数左侧

explain select * from ums_permission where pid =1 and name like'%商品' and value = 'pms:product:read' and status=1

只有pid列索引生效。
like列之后的索引失效。

（九）OR会导致索引失效

explain select * from ums_permission where pid =1 or name ='商品列表' 

三、 ORDER BY和GROUP BY的索引优化

（一）ORDER BY

删除上文中建立的联合索引

drop index idx_p_n_v_s on ums_permission

show index from ums_permission

现在只存在主键索引，执行下面的语句:

explain select name,value,sort from ums_permission up order by sort,create_time 

创建联合索引

create index idx_n_v_s_ct on ums_permission(name,value,sort,create_time)

执行上述exlain语句：

删除联合索引，重新创建：

drop index idx_n_v_s_ct on ums_permission

create index idx_s_ct on ums_permission(sort,create_time)

执行上诉explain语句：

删除联合索引，重新创建：

drop index idx_s_ct on ums_permission


create index idx_s_ct_n_v on ums_permission(sort,create_time,name,value)

执行上诉explain语句：

执行（查询列多了一个uri字段）

explain select name,value,sort ,create_time,uri from ums_permission up order by sort,create_time 

执行（asc和desc并存）：

explain select name,value,sort ,create_time from ums_permission up order by sort,create_time desc 

执行（where和order by组合）:

explain select name,value,sort ,create_time from ums_permission up where sort=1 order by create_time

总结：
满足两种情况，会使用index进行排序：①order by语句使用索引最左前列，②使用where子句与order by子句组合满足最左前列。

提高order by的效率：

1. 不使用select * ，当query的字段大小总和小于max_length_for_sort_data,而且排序的字段不是TEXT和BLOB类型时，会使用单路排序，而不是多路排序。虽然单路排序和多路排序都有可能超出sort_buffer的容量，并在超过后创建tmp文件进行合并排序，导致多次IO，但是单路排序算法的风险更大一些，需要提高sort_buffer_size.
2. 提高sort_buffer_size，根据系统能力，适当提高这个参数，会提高排序的效率。
3. 尝试提高max_length_for_sort_data，可以增加用改进算法的概率，但是如果过大，会容易导致数据总量超出sort_buffer_size，进而增大IO活动，导致效率低下.

双路排序（又叫回表排序模式）：先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer，中进行排序，排序完后需要再次取回其它需要的字段。两次IO。
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序，1次IO。

（二）GROUP BY

1. group by的实质是先排序后分组，因此也遵循索引的最佳左前缀原则；
2. 当无法使用索引列时，可以考虑增大max_length_for_sort_data和sort_buffer_size参数；
3. where优先级高于having，因此，能写在where限定条件的，不要使用having进行限定。