mysql索引优化（一）

标签：name age 索引 mysql position 排序优化 employees

（1）数据准备

建表插入部分数据

CREATE TABLE `employees` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(24) NOT NULL DEFAULT '' COMMENT '姓名',
  `age` int(11) NOT NULL DEFAULT '0' COMMENT '年龄',
  `position` varchar(20) NOT NULL DEFAULT '' COMMENT '职位',
  `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`),
  KEY `idx_name_age_position` (`name`,`age`,`position`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COMMENT='员工记录表';

INSERT INTO employees(name,age,position,hire_time) VALUES('LiLei',22,'manager',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('HanMeimei', 23,'dev',NOW());
INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());

View Code

通过函数批量创建10w条数据

BEGIN
    #Routine body goes here...
    DECLARE i INT DEFAULT 1;
    WHILE i<params1 DO
        INSERT INTO employees(`name`,age,position,hire_time) VALUES (CONCAT('test',i,'hello'),i,'开发',NOW());
        SET i=i+1;
    END WHILE;
    RETURN i;
END

View Code

（2）联合索引范围查找不走索引优化

示例一

EXPLAIN SELECT * FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

原因

　　联合索引第一个字段就用范围查找不会走索引，mysql内部可能觉得第一个字段就用范围，结果集应该很大，回表效率不高，还不如就全表扫描

执行时间比较

-- 关闭查询缓存
set global query_cache_size=0;  
set global query_cache_type=0;
-- 执行时间40.242s
SELECT * FROM employees WHERE name > 'LiLei';
-- 执行时间40.760s
SELECT * FROM employees force index(idx_name_age_position) WHERE name > 'LiLei';

优化策略

　　使用覆盖索引，避免回表查找

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'LiLei' AND age = 22 AND position ='manager';

（3）in和or索引使用情况

结论

　　in和or在表数据量比较大的情况会走索引，在表记录不多的情况下会选择全表扫描

数据多的情况

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position='manager';
EXPLAIN SELECT * FROM employees WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position='manager';

数据少的情况（将employees 表复制一张employees_copy的表，里面保留两三条记录）

EXPLAIN SELECT * FROM employees_copy WHERE name in ('LiLei','HanMeimei','Lucy') AND age = 22 AND position ='manager';
EXPLAIN SELECT * FROM employees_copy WHERE (name = 'LiLei' or name = 'HanMeimei') AND age = 22 AND position ='manager

（4）like KK% 一般情况都会走索引

拓展概念（索引下推）

　　对于辅助的联合索引(name,age,position)，正常情况按照最左前缀原则，SELECT * FROM employees WHERE name like 'LiLei%'AND age = 22 AND position ='manager' 这种情况只会走name字段索引，因为根据name字段过滤完，得到的索引行里的age和position是无序的，无法很好的利用索引。在MySQL5.6之前的版本，这个查询只能在联合索引里匹配到名字是 'LiLei' 开头的索引，然后拿这些索引对应的主键逐个回表，到主键索引上找出相应的记录，再比对age和position这两个字段的值是否符合。MySQL 5.6引入了索引下推优化，可以在索引遍历过程中，对索引中包含的所有字段先做判断，过滤掉不符合条件的记录之后再回表，可以有效的减少回表次数。使用了索引下推优化后，上面那个查询在联合索引里匹配到名字是 'LiLei' 开头的索引之后，同时还会在索引里过滤age和position这两个字段，拿着过滤完剩下的索引对应的主键id再回表查整行数据。　

EXPLAIN SELECT * FROM employees WHERE name like 'LiLei%' AND age = 22 AND position ='manager';

（5）Order by与Group by优化

　　case1

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' AND position ='manager' ORDER BY age;

分析

　　利用最左前缀法则：中间字段不能断，因此查询用到了name索引，从key_len=74也能看出，age索引列用在排序过程中，因为Extra字段里没有using filesort

　　case2

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position;

分析

　　从explain的执行结果来看：key_len=74，查询使用了name索引，由于用了position进行排序，跳过了age，出现了Using filesort。

　　case3

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY age,position;

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei'  ORDER BY position,age;

分析

　　第一条sql根据name查找和age、position字段排序，符合最左前缀原则所以使用的是索引排序；第二条sql根据name查找和position、age排序，不符合最左前缀原则所以position、age排序只能使用文件排序

　　case4

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' and age = 18  ORDER BY position,age;

分析

　　position、age不符合最左前缀原则，但是依旧没使用文件排序是因为查询条件中age=18是一个常量，所以position、age无论谁前谁后得出的结果都是一致的，mysql会对排序进行优化进而使用索引排序

　　case5

EXPLAIN SELECT * FROM employees WHERE name = 'LiLei' ORDER BY age asc,position desc;

分析

　　虽然排序的字段列与索引顺序一样，且order by默认升序，这里position desc变成了降序，导致与索引的排序方式不同，从而产生Using filesort。Mysql8以上版本有降序索引可以支持该种查询方式。

　　case6

EXPLAIN SELECT * FROM employees WHERE name in ('LiLei','zhuge') ORDER BY age,position;

分析

　　对于排序来说，in相当于范围查找，对于第一个字段使用范围查找来说，age、position自然无法使用索引进行排序

　　case7

EXPLAIN SELECT * FROM employees WHERE name like 'Li%' ORDER BY age,position;

分析

　　同case6原理一致

　　case8

EXPLAIN SELECT * FROM employees WHERE name > 'Li' ORDER BY name;

EXPLAIN SELECT name,age,position FROM employees WHERE name > 'Li' ORDER BY name;

分析

　　通过覆盖索引对sql进行优化，达到对查找和排序方式优化

　　优化总结

MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index效率高，filesort效率低。
order by满足两种情况会使用Using index。

order by语句使用索引最左前列。
使用where子句与order by子句条件列组合满足索引最左前列。

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using filesort。
能用覆盖索引尽量用覆盖索引
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中的限定条件就不要去having限定了。

（6）Using filesort文件排序原理详解

　　filesort文件排序方式

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

　　排序方式的选择

MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。

如果字段的总长度小于max_length_for_sort_data ，那么使用单路排序模式；
如果字段的总长度大于max_length_for_sort_data ，那么使用双路排序模式；

（7）索引设计原则

　　代码先行，索引后上

不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？

　　　　　这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。

　　联合索引尽量覆盖条件

比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。

　　不要在小基数字段上建立索引

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查 找的优势来。

　　长字符串我们可以采用前缀索引

尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEYindex(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

　　where与order by冲突时优先where

在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?

　　　　一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出　　　　你要的少部分数据，然后做排序的成本可能会小很多。

标签：name,age,索引,mysql,position,排序,优化,employees
From： https://www.cnblogs.com/Aaron-cell/p/17536426.html