MySQL索引优化入门基础篇


+----+-------------+---------+------+---------------+------+---------+------+------+-------
+ | id | select_type | table | type | possible_keys | key  | key_len | ref | rows | Extra |
 +----+-------------+---------+------+---------------+------+---------+------+------+-------
+ | 1 | SIMPLE      | servers | ALL | NULL          | NULL | NULL    | NULL | 1   | NULL | 
+----+-------------+---------+------+---------------+------+---------+------+------+-------

id

id 是用来顺序标识整个查询中 SELELCT 语句的，在嵌套查询中 id 越大的语句越先执行。该值可能为 NULL，如果这一行用来说明的是其他行的联合结果。

select_type

表示查询的类型

table

对应行正在访问哪一个表，表名或者别名

关联优化器会为查询选择关联顺序，左侧深度优先
当 from 中有子查询的时候，表名是 derivedN 的形式，N 指向子查询，也就是 explain 结果中的下一列
当有 union result 的时候，表名是 union 1,2 等的形式，1,2 表示参与 union 的 query id

注意：MySQL 对待这些表和普通表一样，但是这些“临时表”是没有任何索引的。

type

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到 range 级别，最好能达到 ref。

记下边几个比较常用的也可以 Type 访问类型排序从最好到最差依次是：

System>const>eq_ref>ref>range>index>ALL

possible_keys

显示查询使用了哪些索引，表示该索引可以进行高效地查找，但是列出来的索引对于后续优化过程可能是没有用的

KEY

实际使用的索引。如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len

key_len 列显示 MySQL 决定使用的键长度。如果键是 NULL，则长度为 NULL。使用的索引的长度。在不损失精确性的情况下，长度越短越好。

Ref

显示索引的哪一列被使用了。如果可能的话，是一个常数。那些列或常量被使用于查找索引列上的值

rows

rows 列显示 MySQL 认为它执行查询时必须检查的行数。注意这是一个预估值。

Ertx（补充说明）

Using filesort ：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”。

Using temporary ：使用了临时表保存中间结果。MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组 group by。拖慢你的sql

注意： 要建索引在排序的时候一定要使用，要不会产出临时表，要不就进行排序，要不就别建索引

Using index：表示相应的select操作中使用了覆盖索引，避免访问表的数据行，效率不错！如果同时出现using where,表明索引被使用来执行索引键值的查找； 如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。覆盖索引（查询列要被所建的索引覆盖或可以说一个索引包含了（或覆盖了）满足查询结果的数据就也叫覆盖索引）

注意：如果要遇到覆盖索引，一定要注意select列表中只取出需要的列，不可select*

因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

Using join buffer :使用了连接缓存

Impossible where :where子句的值总是false，不能用来获取任何元组（例如:explan select * from staffs where name=’july’ and name=’z3’; 你输的这个叫july又叫z3让MySQL错乱了）

Selecet tables optimized away: 在没有groupby字句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即完成优化。

Distinct:优化distinct操作，在找到第一次匹配的元组后即停止找同样值的动作。

索引优化：小案例

范围以后的索引会失效，效率低例如索引（sex_name_views）在 where sex =1 and name>1 order by views 中name后的索引用查询效率低

EXPLAIN selecet id ,author_id from ‘article’ where category_id =1 and comments>1 order by views desc limit 1;

EXPLAIN selecet id ,author_id from ‘article’ where category_id =1 and comments=1 order by views desc limit 1;

结论：

Type变成了range这个是可以忍受的。但是extra 里使用Using filesort仍是无法接受的。

但是我们已经建立了索引，为啥没用呢》

这里因为按照BTree索引的工作原理。

先排序category_id.

如果遇到相同的category_id则再排序comments，如果遇到相同的comment则再排序views。当comments字段在联合索引力处于中间位置时

因comments>1条件是一个范围值（所谓range）

MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效

EXPLAN select * from class left join book on class.card =book.card

结论：type有ALL

分别左右表都添加索引，第二行的type变为了ref，rows也变成小了，优化比较明显

这是有左连接的特性决定的，left join 条件用于确定如何从右表搜索行，左表一定都有。

所以左连接索引一定要加右表。

索引失效：

全值匹配
最佳左前法则（指的时查询从索引的最左前列开始且不跳过索引的列）
不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引范围条件右边的列，范围之后全失效
尽量使用覆盖索引（之访问索引的查询（索引列和查询列一致）），而减少select *
Mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null,is not null 也无法使用索引
Like以通配符开头（‘%abc...’）mysql索引失效会变成全表扫描的操作
字符串不加单引号索引失效
少用or，用它来连接时会索引失效

总结

Optimizer 优化器自我优化分析会根据索引的进行自我调整where语句的顺序。

group by 分组并排序

定值、范围还是排序，一般order by是给个范围

Group by 基本上都需要进行排序，会有临时表产生

索引一般性建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

优化总结口诀

全值匹配我最爱，最左前缀要遵守。

带头大哥不能死，中间兄弟不能断。

索引列上少计算，范围之后全失效。

LIKE百分写最右，覆盖索引不写星。

不等空值还有or，索引失效要少用。

VARCHAR引号不可丢，SQL高级也不难。

一般优化流程分析：

观察，至少跑1天，看看生产的慢SQL情况。
开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来。
Explain+慢SQL分析
Show profile
运维经理or DBA，进行SQL数据库服务器的参数调优

总结

慢查询的开启并捕获
Explain+慢SQL分析
Show profie查询SQL在MySQL服务器力面的执行细节和生命周期情况
SQL数据库服务器的参数调优。

查询优化

永远小表驱动大表，即小的数据集驱动大的数据集。

原理（RBO）

Select * from A where id in (select id from B);

当B表的数据集必须小于A表的数据集时，用in优于exists。

当A表的数据集系小于B表的数据集时，用exists优于in。

EXISTS（用途解释）

Select from table where exists（subquery）

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（True 或 false）来决定主查询的结果是否得以保留。

提示：

exists（subquery）只返回true或false，因此子查询中的select * 也可以时select 1 或select ’x‘,官方说法时实际执行时会忽略Select清单，因此没有区别。
Exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
Exists 子查询往往也可以用条件表达式、其子查询或者JOIN来替代，何种最优需要具体问题具体分析

Order by 优化

Order by子句，尽量使用index方式排序，避免使用filesort凡是排序

尽可能在索引列上完成排序操作，遵照索引建的最左前缀

如果不能在索引列上，filesort有两种算法；

Mysql就要启动双倍排序和单路排序

优化策略：增大sort_buffer_size参数的设置

增大max_length_for_sort_data参数的设置

名词解释

双路算法：

取一批数据，要对磁盘进行了两次扫描。

单路排序：

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机io变成了顺序io，但是它会使用更多的空间，因为它把每一行都保存在内存中。

什么情况下单路不如双路

在sort_buffer中，方法B比方法A要多占用更多空间，因为方法B时把所有字段都取出，所以有可能取出的数据的总大小超过了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完在取取sort_buffer容量大小，再排。。。。从而多次i/o

Order by满足两情况，会使用Index方式排序

1.Order by语句使用索引最左前列

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

3.如果WHERE使用索引的最左前缀为常量，则order by能使用索引

Order by什么情况，不能使用索引进行排序：

1.排序不一致

2.丢失最左索引和中间索引

3.字段不是索引的一部分

4.a in(...)order by b,c对于排序来说，多个相等条件也是范围查询，

Gourp by

Gourp by比order by就多个having，能在where中使用的尽量不要使用having

索引着重排序和查找

慢查询日志浅谈

慢查询，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中

long_query_time默认10秒

原来写的有兴趣可以看看

(39条消息) SQL优化_✘迟暮的博客-CSDN博客

(39条消息) MySQL_✘迟暮的博客-CSDN博客

(39条消息) MySQL索引_✘迟暮的博客-CSDN博客_mysql创建位图索引

✘迟暮

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
1
评论
MySQL索引优化入门基础篇

耐心看完你会有不一样的收获，适合入门的基础调优全值匹配我最爱，最左前缀要遵守。带头大哥不能死，中间兄弟不能断。索引列上少计算，范围之后全失效。LIKE百分写最右，覆盖索引不写星。不等空值还有or，索引失效要少用。VARCHAR引号不可丢，SQL高级也不难。
复制链接

扫一扫