Mysql系列 一 索引和explain

什么是索引：

索引是帮助MySQL高效获取数据的排好序的数据结构

索引数据结构：

• 二叉树
• 红黑树
• Hash表
• B-Tree

B-Tree

• 叶节点具有相同的深度，叶节点的指针为空
• 所有索引元素不重复
• 节点中的数据索引从左到右递增排列

B+Tree(B-Tree变种)

• 非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引
• 叶子节点包含所有索引字段
• 叶子节点用指针连接，提高区间访问的性能

MyISAM索引文件和数据文件是分离的(非聚集索引)

InnoDB索引实现(聚集索引)

最左前缀匹配原则：从左到右的顺序匹配 
create index ix_name_email on s1(name,email,) 
最左前缀匹配：必须按照从左到右的顺序匹配 
select * from s1 where name='egon'; #可以 
select * from s1 where name='egon' and email='asdf'; #可以 
select * from s1 where email='alex@oldboy.com'; #不可以 
mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配：
比如a = 1 and b = 2 and c > 3 and d = 4 
如果建立(a,b,c,d)顺序的索引， d是用不到索引的
如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整

---

Explain

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL。

注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

使用：

explain select * from actor;

输出：

explain中的列含义：

 

1. id列

id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的。

id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。

 

2. select_type列

select_type 表示对应行是简单还是复杂的查询。

• simple：简单查询。查询不包含子查询和union
• primary：复杂查询中最外层的 select
• subquery：包含在 select 中的子查询（不在 from 子句中）
• derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）
• union

3. table列

这一列表示 explain 的一行正在访问哪个表。

 

4. type列

这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。

依次从最优到最差分别为： system > const > eq_ref > ref > range > index > ALL

 

5. possible_keys列

这一列显示查询可能使用哪些索引来查找。

explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。

如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。

 

6. key列

这一列显示mysql实际采用哪个索引来优化对该表的访问。

如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index

 

7. key_len列

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

 

8. ref列

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）

 

9. rows列

这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。

 

10. Extra列

这一列展示的是额外信息。常见的重要值如下：

• Using index：使用覆盖索引
• Using where：使用 where 语句来处理结果，查询的列未被索引覆盖
• Using index condition：查询的列不完全被索引覆盖，where条件中是一个前导列的范围
• Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化
• Using filesort：将用外部排序而不是索引排序
• Select tables optimized away：使用某些聚合函数（比如 max、min）来访问存在索引的某个字段

索引最佳实践 

• 1.全值匹配

• 2.最左前缀法则

• 3.不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转
• 向全表扫描

• 4.存储引擎不能使用索引中范围条件右边的列
• 5.尽量使用覆盖索引（只访问索引的查询（索引列包含查询列）），减少select *语句
• mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
• 6.is null,is not null 也无法使用索引
• 7.like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作
• 8.字符串不加单引号索引失效

• 9.少用or或in，用它查询时，mysql不一定使用索引，mysql内部优化器会根据检索比例、表大小10.等多个因素整体评估是否使用索引，详见范围查询优化

• 范围查询优化

 like KK%相当于=常量，%KK和%KK% 相当于范围

常见sql优化

Order by与Group by优化

1、MySQL支持两种方式的排序 filesort 和 index ，Using index是指MySQL 扫描索引本身完成排序 。index效率高，filesort效率低。

2、order by满足两种情况会使用Using index。

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

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

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

4、如果order by的条件不在索引列上，就会产生Using filesort。

5、能用覆盖索引尽量用覆盖索引

6、group by与order by很类似，其实质是先 排序后分组 ，遵照 索引创建顺序 的最左前缀法则。对于groupby的优化如果不需要排序的可以加上 order by null禁止排序 。

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

 

分页查询优化

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

     通常我们取第90001--90005条数据，我们会这么写 ： select * from employees limit 90000,5

      因为主键是自增并且连续的，所以可以改写成按照主键去查询从第 90001开始的五行数据：select * from employees where id > 90000 limit 5

      但是，这条 改写的SQL 在很多场景并不实用，因为表中可能某些记录被删后，主键空缺，导致结果不一致

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

    select * from employees ORDER BY name limit 90000,5;

    explain发现并没有使用 name 字段的索引（key 字段对应的值为 null），应为扫描整个索引并查找到没索引的行(可能要遍历多个索引树)的成本比扫描全表的成本更高，所以优化器放弃使用索引。

    那么怎么优化呢？可以是让排序时返回的字段尽可能少：select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id

Join关联查询优化

    关联字段加索引，让mysql做join操作时尽量选择NLJ算法     

    小标驱动大表，写多表连接sql时如果明确知道哪张表是小表可以用straight_join写法固定连接驱动方式，省去mysql优化器自己判断的时间 

in和exists优化

    原则：小表驱动大表，即小的数据集驱动大的数据集