Mysql索引优化

使用到的数据表：

索引使用说明

说明：以下所有对sql执行索引的描述，都不是绝对的，都有可能和描述的索引使用方式不一样，只代表大部分的执行结果。

1. 联合索引， 查询语句只使用第一个字段做范围查询，是否走索引？

        不会，mysql内部可能认为第一个字段就使用索引，查询的范围比较大，回表效率不高，不如全表扫描。

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

3. like 'KK%'  一般情况走索引：

        msyql将其当成个常量处理。 有可能使用到下推索引。

        为什么范围查询没有用到下推索引？估计mysql认为范围查询的过滤结果集比过大，like 'KK%'在绝大多数情况，过滤后的结果集比较小。

索引优化

1. 覆盖索引优化

mysql> EXPLAIN select * from employees where name > 'a'; --  全表扫描

mysql> EXPLAIN select * from employees where name > 'zzz' ; -- index-name-age-postion

优化方式：

mysql> EXPLAIN select name,age,position from employees where name > 'a' ;

2. order by 与group by

排序方式

1. MySQL支持两种方式的排序 filesort 和 index (explain 中的Extra字段)。index效率高，filesort效率低。

        1）Using index是指MySQL扫描索引本身完成排序, 排序使用到了索引树。

        2）fileSort指的是将数据从磁盘读取到内存中做排序（order by的条件不在索引列上）（排序没有用到索引树， 直接用聚集索引）。

2. order by满足两种情况会使用Using index。（保证可使用索引树）

        1) order by语句使用索引最左前列。

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

3.  group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。

     对于group by的优化，如果不需要排序的可以加上order by null禁止排序。

     注意，where高于having，能写在where中的限定条件就不要去having限定了。

排序优化方式

尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

3. 分页查询优化

1、根据自增且连续的主键排序的分页查询

>mysql> select * from employees limit 10000,10;

看似查了10条记录，实际查了10010条记录，然后丢弃前10000条

优化: mysql> EXPLAIN select * from employees where id > 90000 limit 5; (走主键索引)

        适应场景：根据自增且连续主键排序。

        缺点：如果有删除记录， 两个语句查询的结果可能不一致。

2、根据非主键字段排序的分页查询

>mysql> EXPLAIN select * from employees ORDER BY name limit 90000,5; （扫描的量比较大，name索引不一定用上）

优化：mysql> select * from employees e inner join (select id from employees order by name limit 90000,5) ed  on e.id = ed.id

优化点：1. join中的语句是覆盖索引， 且查出的结果只有5条  2. 最外层的select 也走到主键索引。

JOIN查询及优化

使用到的数据表， t1记录10000条， t2记录100条

1. 嵌套循环连接 Nested-Loop Join(NLJ) 算法

        一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集。

mysql> EXPLAIN select * from t1 inner join t2 on t1.a= t2.a;

• 扫描行： 100（t2的读取次数） + 100（t1的去取次数） = 200次。扫描100次 t1 表的索引，1次扫描可以认为最终只扫描 t1 表一行完整数据（关联字段走了索引），也就是总共 t1 表也扫描了100行，因此整个过程扫描了 200 行。
• 比价次数：100次

2. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法

        把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比。

        join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。如果放不下表 t2 的所有数据话，策略很简单，就是分段放。

mysql>EXPLAIN select * from t1 inner join t2 on t1.b= t2.b;

• 扫描行：100（t2扫描行） + 10000（t1扫描行） = 100100次。
• 比较次数： join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。
• 

思考：关联字段没有索引使用NLJ算法执行效率？

• 扫描行：100（t2扫描行） + 10000 * 100（t1扫描行） = 100w次
• 比较次数：100w次
• 扫描涉及到读磁盘，显然这时效率远低于BNL算法
• 

3. JOIN优化

• 对于关联sql的优化关联字段加索引，让mysql做join操作时尽量选择NLJ算法
• 小表驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间

straight_join解释：straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。

比如：select * from t2 straight_join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。

straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）。使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

小表定义的明确：

在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

COUNT

count使用

四个sql的执行计划一样，说明这四个sql执行效率应该差不多

• 字段有索引：count(*)≈count(1)>count(字段)>count(主键 id)  //字段有索引，count(字段)统计走二级索引，二级索引存储数据比主键索引少，所以count(字段)>count(主键 id)
• 字段无索引：count(*)≈count(1)>count(主键 id)>count(字段) //字段没有索引count(字段)统计走不了索引，count(主键 id)还可以走主键索引，所以count(主键 id)>count(字段)
• count(1)跟count(字段)执行过程类似，不过count(1)不需要取出字段统计就用常量1做统计，count(字段)还需要取出字段，所以理论上count(1)比count(字段)会快一点。

• count(*) 是例外，mysql并不会把全部字段取出来，而是专门做了优化，不取值，按行累加，效率很高，所以不需要用count(列名)或count(常量)来替代 count(*)。

count优化

1. Mysql服务存储总行数

• myisam存储引擎的表做不带where条件的count查询性能是很高的，因为myisam存储引擎的表的总行数会被mysql存储在磁盘上，查询不需要计算
• innodb存储引擎的表mysql不会存储表的总记录行数(因为有MVCC机制， 每个事务内读到的行数都可能不一致）

2. show table status

如果只需要知道表总行数的估计值可以用如下sql查询，性能很高

3. 将总数维护到Redis里

插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)。
缺点：很难保证表操作和redis操作的事务一致性

4. 增加数据库计数表

插入或删除表数据行的时候同时维护计数表，让他们在同一个本地事务里操作

SQL执行过程分析工具

trace工具：分析sql执行过程。

开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭。

mysql> EXPLAIN select * from employees where name > 'a'; --  全表扫描
 

索引相关概念

索引下推

（Index Condition Pushdown, ICP）

辅助联合索引，正常情况按照最左前缀原则（只用联合索引的前部分字段），

分析这个语句：select * from employees force index(idx_name_age_position) where name like 'LiLei%' and age =22 and position = 'manager';

说明：索引下推只能用于二级索引，主键索引不涉及回表。

单路/双路排序

排序时 Using filesort文件排序原理详解：

sort_buff默认大小1M, 不要轻易修改。

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

• 如果 字段的总长度 < max-length-for-sort-data那么使用 单路排序模式；
• 如果 字段的总长度 > max-length-for-sort-data那么使用 双路排序模∙式。

示例：

explain select * from employees where name ='zhuge'  order by position;

-> using filesort（排序字段无法使用索引）

索引设计原则

1、代码线上，索引先行

2、联合索引尽量覆盖条件

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

4、长字符串可以采用前缀索引

5、where 与order by 冲突优先where

6、慢sql优化

文档：04‐mysql慢查询资料.note

链接：http://note.youdao.com/noteshare?id=c71f1e66b7f91dab989a9d3a7c8ceb8e&sub=0B91DF863FB846AA9A1CDDF431402C7B

sql语句：

-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
set global query_cache_size = 0;

set global query_cache_type=0;

_
type=0;
# 强制走索引
select * from employees force index(idx_name_age_position) where name >'LiLei' and age =22 and position = 'manager';