Mysql——索引优化原理/实践/场景

Mysql——索引优化概述

索引存在形式：索引在数据库启动的时候会加载到内存中，存储则是在文件中；mysql文件可以配置大小，如果不足会使用磁盘交换；

多重索引结构：在同一张表中，不同的索引走不同的B+树，利用非索引拿到主键索引后需要进行回表查询；每个索引是为数据建立不同的索引树，索引的保存是需要消耗磁盘写入IO的；

索引结构分布：索引在B+树中的非叶子结点存储的是索引上相关字段的值；在叶子结点中，存储的是主键值或者数据；输出索引值，又名覆盖索引，不必回表；

索引搜寻原理：利用二分法思想结合B树多路平衡特点进行增删改查，（具体搜寻，插入请看B+树原理）

索引终点设计：B+树由于所有数据都在叶子结点，同时由于有链表结构，只需要找到首尾，通过链表就能把所有数据取出来了解決了跨层问题，也得益于适合做范围查询因为索引值是离散连续的，给定端点只需要不断遍历即可；

Mysql——优化基本能力

slow log慢查询捕捉分析

全局查看配置

show variables like “%slow%”
show global VARIABLES like ‘%slow_query_log%’
show global VARIABLES like ‘long_query_time%’
show global status like ‘%Slow_queries%’

全局修改配置

set global slow_query_log=0 开启
set global long_query_time=3 时间

会话配置

show VARIABLES like ‘%slow_query_log%’
show VARIABLES like ‘long_query_time%’
set long_query_time=3 开启
set slow_query_log=0 时间

永久配置

[mysqld]
slow_query_log = ON
slow_query_log_file = /tmp/slow_querys.log #定义慢查询日志的路径
long_query_time = 1 #定义查过多少秒的查询算是慢查询，我这里定义的是1秒，5.6之后允许设置少于1秒，例如0.1秒
#log-queries-not-using-indexes #用来设置是否记录没有使用索引的查询到慢查询记录,默认关闭,看需求开启,会产生很多日志,可动态修改
#log-slow-admin-statements 管理指令也会被记录到慢查询。比如OPTIMEZE TABLE, ALTER TABLE,默认关闭,看需求开启,会产生很多日志,可动态修改
Ps：永久生效修改配置文件；

慢查询分析

tail -f slow_query.log

第一行,SQL查询执行的时间 第二行,执行SQL查询的连接信息，用户和连接IP 第三行,记录了一些我们比较有用的信息，如下解析:

• Query_time,这条SQL执行的时间,越长则越慢
• Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
• Rows_sent,查询返回的行数
• Rows_examined,查询检查的行数，越长就当然越费时间
  第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间。
  第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长。

show profile语句分析

show profiles应用场景

是Jeremy Cole捐献给MySQL社区版本的一个功能；这个命令只是在本会话内起作用，即无法分析本会话外的语句。查看分析sql的生命周期，一条sql究竟卡在这里花的时间比较多

show profiles全局指令

全局查看近期所有的语句以及queryId，这些信息保存就内存中；

show profiles详情指令

SHOW PROFILE for QUERY 66 – 显示用户CPU时间、系统CPU时间
SHOW PROFILE ALL for QUERY 66 – 显示所有性能信息
SHOW PROFILE BLOCK IO for QUERY 66 – 显示块IO操作的次数
SHOW PROFILE CONTEXT SWITCHES for QUERY 66 – 显示上下文切换次数，不管是主动还是被动
SHOW PROFILE cpu for QUERY 66 – IPC 显示发送和接收的消息数量
~
~
SHOW PROFILE MEMORY for QUERY 66 – MEMORY [暂未实现] SHOW PROFILE PAGE
FAULTS for QUERY 66 – 显示页错误数量 SHOW PROFILE SOURCE for QUERY 66 –
SOURCE 显示源码中的函数名称与位置 SHOW PROFILE SWAPS for QUERY 66 – SWAPS
显示SWAP的次数 Ps：如果没有指定FOR QUERY，那么输出最近一条语句的信息。

dump slow统计分析

mysqldumpslow命令本质

用于打开慢查询日志，并自动根据格式进行统计分析，按照要求进行排序、统计；

mysqldumpslow命令合集

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log（得到返回记录集最多的10个SQL）
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log（得到访问次数最多的10个SQL）
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log（得到按照时间排序的前10条里面含有左连接的查询语）
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more（另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况）
-s：是表示按照何种方式排序，子参数如下：
c、ar：被捕获次数正序、被捕获次数倒序
t、at：被捕获时间正序、被捕获时间倒叙
l、al：查询时间正序、查询时间倒序
r、ar：返回记录数正序、返回记录倒序
-t：返回前面多少条的数据，这里意思就是返回10条数据了（也可以说是前十）

pt(Percona Toolkit)命令配置

用将日志整理成文件输出统计信息

pt(Percona Toolkit)命令合集

pt-query-digest slow_querys.log >t.txt

global catch全局查询分析

更狠，直接全部查询一起输出到日志进行分析；

general_log=1
general_log_file=/path/logflie
log_output=file

set global general_log=1
set global log_output=’TABLE’
select * from mysql.general_log

Explan查询计划分析

索引分析——查询参数

【ID】
Id相同，上面的先执行；
Id不同，大的先执行；
【SELECT TYPE】
simple：简单的 select 查询,不包含任何嵌套部份  -------> 子查询或者union

primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary
derived：在from后面列表中包含的子查询被标记为derived(衍生)mysql会递归执行这些子查询, 把结果放在临时表里
subquery：select或where后面包含了子查询，与外层无关联
dependent subquery：基于外层关联关系的子查询，性能差；

union：
union result：
【TABLE】
显示这一行的数据是关于哪张表的，可能多个

索引分析——索引相关信息

【TYPE】
用于表示此次查询和索引的关系
system：表中只有一行记录
const：唯一或者主键的索引值是个常量，而不是语句相关的变量；
eq_reft：唯一或者主键的索引值是变量，但是一个索引只对应一条数据；
ref:不唯一，用一个索引找出多行记录；
range:范围查询，where语句中出现了between、<、>、in等的查询，告诉索引的开始和索引末尾即可；
index:只全表扫描非聚集索引的数据行，不涉及回表，这个文件比较实际的数据文件小，所以性能更高；利用索引进行了排序分组也会发生这个；

index_merge:采用了多个索引结构；当多条件联合查询时，优化器会评估用哪个条件的索引效率最高！它会选择最佳的索引
ref_or_null:null值的情况被考虑进来了
index_subquery: 作为子查询输出的是索引字段的遍历
unique_subquery :作为子查询输出的唯一键的遍历
【possible_keys】
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
【Key】
实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠
【Key_len】
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，同一行数据利用多个索引确定和利用少量索引确定是不一样的； 
key_len字段能够帮你检查是否充分的利用上了索引，同一个索引利用了几次！
【ref】
针对于非唯一性索引被使用的情况；索引值是常量还是一个与查询语句有关的变量，这个会将所有索引的ref写出来；一个const或者一个变量
【Rows】
rows列显示MySQL认为它执行查询时必须检查的行数。

索引分析——Extra信息

using filesort： 在组合索引中不存在某个字段的排序索引即外部索引，就会触发这个；
双路排序：mysql4.1之前，先利用IO获取排序类以及指针，然后将排序好的指针数组去顺序读取数据；（频繁IO）
单路排序：一次型取出所有数据，然后在buffer中进行orderby排序，但会占用过多的空间；而sort_buffer大小是固定的，超出后会进行多次io，因此有以下调优策略（需要内存支持）
（1）不要用select *，会把sort-buffer缓存区用完非常容易
（2）max_length_for_sort_data，超出后就采用多路排序；
（3）sort_buffer_size,针对每个进程buffer的容量大小
using temporary ：MySQL在对查询结果如果排序不符合要求，排序时使用临时表。常见于排序 order by 和分组查询 group by。
using index：
表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
如果同时出现using where，表明索引被用来执行索引键值的查找;
如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
using join buffer：使用了连接缓存

Mysql——索引优化场景

单表优化（转化、覆盖、下推）

转化

1、索引是字符串，但是数据直接写成数字，不加单引号索引失效；mysql里面会将字符转成数字，所以字符建立索引不能比较数字，因为所有字符转数字没有建立这种索引
2、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描；
3、is not null 也无法使用索引,但是is null是可以使用索引的；首先，问问你自己“Empty”和“NULL”有多大的区别(如果是 INT，那就是 0 和 NULL)?

操作

1、在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
2、某些查询确定只有一条时，在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据
3、 or 、in、not in; 非操作不要很难搞；对于连续的数值，能用 between 就不要用 in 了

连表

如果你要把DECIMAL 字段和一个 INT 字段 Join 在一起，MySQL 就无法使用它们的索引。对
于那些 STRING 类型，还需要有相同的字符集才行。

复合

1、断层避免，根据左匹配原则，当中间的索引使用了范围、非确定性模糊则会导致右边的索引全部失效；
2、当前Query中过滤性最好字段在索引字段顺序中，位置越靠前越好，可以过滤条很多路线；
3、如果某个字段可经常范围查询时，尽量把这个字段放在索引次序的最后面；
4、覆盖索引（双边like例子）：两边都是like只能用覆盖索引解决，因为覆盖索引只会遍历索引树
select * from test where b like ‘b%’ range
select * from test where b like ‘%b%’ All
select b from test where b like ‘%b%’ index
5、索引条件下推，此次查询条件中用了非索引字段则会利用主键id拿到数据行直接做判断，explain select name from test where name=‘2’ and c>1

设计

extra中的额外信息，Using index condition
1、如果你有一个字段，比如“性别”，“国家”，“民族”，“状态”或 “部门”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM而不是 VARCHAR。
2、注意在不必要的字段上加组合索引会引起索引失效
3、记录存储是按行存储的，数据块大小又是固定的（16K），每条记录越小，相同的块存储的记录就越多。此时应该把大字段拆走（不经常用时），这样应付大部分小字段的查询时，就能
4、任何标准表最多可以创建 16 个索引列，建议每个表8个

子查询

很多时候用 exists 代替 in 是一个好的选择：select num from a where num in(select num from b)
用下面的语句替换：elect num from a where exists(select 1 from b where num=a.num)
IN适合于外表大而内表小的情况，一次把所有的小的查出来过滤外面大的；EXISTS适合于外表小而内表大的情况，用小的作为驱动找到大的。
No Primary key count(1)>count()
Yes Primary key count(key)>count(1)>count()

关联优化（驱动表）

左外连接：左表全表扫描；left join，右边匹配交集；
右外连接：右表全表扫描；right join，左边匹配交集；
内连接：左边全表扫描，最后匹配，两边匹配交集；
Ps：由此可见全表扫描的一方，必须是小表不然就导致很多没必要的数据IO
EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;
ALTER TABLE book ADD INDEX Y ( card);
Ps：只是少部分数据过滤，全表扫描效率更高；每个阶段都会产生一个虚拟表，该虚拟表会作为下一个阶段的输入

排序分组（左前缀确定）

排序的本质：只要你此次查新也是用索引字段作为条件，那就具备按照这个字段的天然排序功，因为二分法左边小，右边大；而如果是复合索引树，则是比较左边，然后比较中间这样决定左右放，因此按照索引树取出来的时候已经是我们所需要的排序结果；复合索引中，必须先按照左边开始排；
分组的本质：分组之前必排序，所以如果此时排序失败则会产生临时表来临时存储内部生成的排序；using temp
降序索引：mysql中如果经常要利用查询时间做操作，可以利用时间作为倒序索引，mysql8.0反向扫描和正向扫描的支持；也因此终于不再对group by进行隐式排序;反向扫描？不一定从顶层？这一层研究确实我没够知识储备，学C看源码把！
UsingFileSort：此次索引利用中索引顺序不是我们要的顺序规则则会产生这个；
左前缀确定：
复合排序中，只有左前缀非范围和模糊查询，那么后面的排序索引才会生效；因此这些条件应该滞后；
index(catogory,comments,views)
explain select id.author_id from article, where catogoryjd = 1 and comments >1 order by views desc limit 1;
explain select id,author_id from ‘article’ where categoryjd= 1 and comments <=3 order by views desc limit 1
index(catogory,views，comments)