mysql索引优化

sql优化是提升项目性能的一种方式，也就是索引优化，索引是一种数据结构，用于帮助在海量数据中快速定位到我们想要查找的数据，本文引用了一些别人的文章加上自己的见解，对mysql索引数据结构和优化方式进行了一些总结。

所谓索引就是排好序的数据结构，要学习sql优化首先需要了解它所使用的数据结构，下面列举三种常见的数据结构：

二叉树，平衡二叉树和B树

二叉树：二叉查找树的特点就是任何节点的左子节点的键值都小于当前节点的键值，右子节点的键值都大于当前节点的键值。顶端的节点我们称为根节点，没有子节点的节点我们称之为叶节点，我们通过二叉树可以准确的定位我们需要的数据，但是也有可能会出现极端情况：二叉查找树有可能变成了一个链表，这就会使查询效率大大降低，这时候就出现了平衡二叉树。

1594017405(1).jpg

平衡二叉树：又称 AVL 树，在满足二叉查找树特性的基础上，要求每个节点的左右子树的高度差不能超过 1，像大家熟知的红黑树就是平衡二叉树的一种，但是平衡二叉树每个节点只存储一个键值和数据，如果想要存储海量数据，则平衡二叉树的高度就会拉长，我们查询数据时的效率就会降低，所以就出现了B树。

1594018438(1).jpg

B树：下图是一个B树，图中的每个节点称为页，因为从磁盘中读取数据时，都是按照磁盘块来读取的，所以页就是磁盘块，这样的话每个节点就可以存储更多的键值（key）和数据（data），子节点的个数一般称为阶，以下是一个三阶B树。

1594019088(1).jpg

B+树：这是对B树的一种优化，mysql索引用的就是这种数据结构，B+树和B树最大的区别就是：B+ 树非叶子节点上是不存储数据的，仅存储键值，而 B 树节点中不仅存储键值，也会存储数据，这样的目的主要是为了缩短B树的高度，因为在InnoDB 中页的默认大小是16KB，如果不存放数据的话就可以存放更多的键值，可以使B树变得“矮胖”，减少io次数。

mysql中InnoDB 和MyISAM 对B+ 树索引的实现略有不同，MyISAM 叶子节点并不存储数据，而是存储数据的文件地址，这个到后面再说。

聚集索引和非聚集索引

mysql两种常见的表引擎有InnoDB 和MyISAM ，其中InnoDB 使用的就是聚簇索引，而MyISAM 使用的非聚簇索引

innodb中（聚簇索引），即存储主键索引值，又存储行数据
myisam中（非聚簇索引），引擎的索引文件和数据文件是独立分开的，则称之为非聚簇索引

聚簇索引查找流程：

select * from user where id>=18 and id <40

1594022465(1).jpg

非聚簇索引查找流程：

select * from user where luckNum=33

1594022619(1).jpg

了解了mysql索引数据结构就可以来对mysql进行索引优化了

sql解析过程

(5)SELECT (6)DISTINCT < select list >

(1)FROM < table source >

(2)WHERE < condition >

(3)GROUP BY < group by list >

(4)HAVING < having condition >

(7) ORDER BY < order by list >

sql执行计划

sql查询编写计划：

explain+sql语句
先看个简单的例子：

1594015024(1).jpg

id：编号

id值不同：一般有子查询时id值会不同，嵌套子查询时先查内层再查外层，id值越大越先查询

id值相同：从上往下顺序执行，造成执行顺序差异的原因是由表中实际数量决定，数量越少越先执行（笛卡尔积）

select_type：查询类型

primary:包含子查询sql中的主查询（最外层）

subquery:包含子查询sql中的子查询（非最外层）

simple:简单查询（不包含子查询、union）

derived:衍生查询（用到临时表 a.在from子查询中只有一张表。b.在from子查询中，如果有table1 union table2,则table1就是derived，table2就是union。）

union:如上例

table：表
type：索引类型

效率：system>const>eq_ref>ref>range>index>all

system，const只是理想情况，实际能达到ref>range

system：只有一条数据的系统表或衍生表只有一条数据的主查询

const：仅仅能查到一条数据的sql，用于主键和unique索引

eq_ref：唯一性索引，对于每个索引键的查询，返回匹配唯一行数据，常见于唯一索引和主键索引

ref：非唯一性索引，对于每个索引键的查询，返回匹配的所有行

range：检索指定范围的行，where后面是一个范围查询（<,>,in,between）in有时会失效

index：查询全部索引数据

all：查询全部表数据

possible_keys：预测用到的索引
key：实际使用的索引
key_len：实际使用的索引的长度
ref：表之间的引用
指明当前表所参照的字段
select ...where a.c=b.x,若b.x是常量则ref为const
rows：通过索引查询到的数据量，被优化的数据条数
extra：额外的信息

using filesort：性能消耗大，需要额外的查询

避免方法：

ａ．若排序和查询条件是同一个字段则不会出现using filesort，反之

ｂ．最佳左前缀原则，索引和查询条件不能跨列，否则出现using filesort

总结：where和order by按照复合索引的顺序使用，不要跨列或无序使用

using temporary：常见于group by

避免方法：根据哪些列查询就根据哪些列分组

using index：性能提升：索引覆盖：不需要回表查询

using where：需要回表

impossible where：where条件永远false

优化事项

添加索引：alter table ... add index idx_a1234(a1,a2,a3,a4);

select a1,a2,a3,a4 from...where a1=1 and a2=2 and a4=4 order by a3

跨列使用索引会使a4失效，导致a4回表

select a1,a2,a3,a4 from...where a1=1 and a4=4 order by a3

未失效的where条件和order by拼起来，不能跨列使用，否则出现using filesort

总结：如果复合索引和使用顺序全部一致，则复合索引全部使用，若部分一致，则部分使用

sql单表和多表的优化

sql单表优化：
根据sql实际解析顺序来调整索引顺序，遵循最佳左前缀原则，不要跨列使用索引，且将in等范围查询条件放在where最后，因为in可能会使索引失效导致using where回表

sql多表优化：

索引建立经常使用的字段上
小表驱动大表：select ... where 小表.a=大表.b
对于左外链接给左表加索引，右外链接给右表加索引

避免索引失效的原则

复合索引，不要跨列使用或者无序使用（最佳左前缀）
复合索引，尽量使用全索引匹配
不要在索引上进行操作（计算，函数，类型转换）
复合索引不能使用不等于（<> !=）或者is null(is not null),否则自身以及后面的索引全部失效
对于复合索引，如果左边索引失效，则右边索引全部失效
一般而言（概率而言），范围查询（in,<,>）之后的索引失效
like尽量以常量开头，如果使用%开头索引失效，可以使用x%挽救一部分索引，还可以使用索引覆盖
尽量不要使用or否则索引失效，甚至可以将or左边的索引干掉

exist和in的选择和order by/group by优化

exist和in的选择:

如果主查询的数据集大，则使用in

如果子查询的数据集大，则使用exist

order by/group by优化:

总结：主要方法是选择使用单路，双路；调整buffer的容量大小：set max_length_for_sort_data

原理：

双路排序：Mysql4.1之前是使用双路排序，字面的意思就是两次扫描磁盘，最终得到数据，读取行指针和ORDER BY列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对数据输出。也就是从磁盘读取排序字段，在buffer进行排序，再从磁盘读取其他字段。文件的磁盘IO非常耗时的，所以在Mysql4.1之后，出现了第二种算法，就是单路排序。
单路排序：从磁盘读取查询所需要的所有列，按照ORDER BY在buffer对它进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在了内存里。

ORDER BY优化：

ORDER BY时不要使用SELECT *，只查需要的字段。

a：当查询的字段大小综合小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法---单路排序，否则用老算法---多路排序。假设只需要查10个字段，但是SELECT *会查80个字段，那么就容易把sort_buffer缓冲区用满。

b：两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size大小。
增大sort_buffer_size参数大小
不管用哪种算法，提高这个参数都会提高效率。当然要根据系统能力去提高，因为这个参数是针对每个进程的。
增大max_length_for_sort_data参数大小，
提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

GROUP BY优化：

GROUP BY实质上是先排序后进行分组，遵照索引的最佳左前缀。
当无法使用索隐列，考虑增大max_length_for_sort_data和sort_buffer_size的参数设置。
WHERE 高于 HAVING，能写在WHERE限定的条件就不要去HAVING限定了。

sql排查

sql排查-慢查询日志：超过慢查询阀值响应时间就是慢查询（默认10s）

检查是否开启慢查询日志：

show variables like '%slow_query_log%'

临时开启慢查询日志（生存周期服务开关期间）：

set global slow_query_log = 1

永久开启慢查询日志：在/etc/my.cnf中追加配置：

[mysqld]
slow_query_log=1
slow_query_log_file=/var/lib/mysql/localhost-slow.log

查询慢查询阀值：

show variables like '%long_query_time%'

临时设置阀值：

set global long_query_time= 5；-不会立即生效，需重新登陆

永久设置阀值：/etc/my.cnf中追加配置：

[mysqld]
long_query_time= 5

查看超过阀值的sql:

show global status like '%slow_queries%'

慢查询的sql记录在日志中，可以通过日志文件查看具体sql

查看日志文件的linux命令：

cat /var/lib/mysql/localhost-slow.log

或者通过mysqldumpslow工具查看：

mysqldumpslow --help
a.获取返回记录最多的三个sql:mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log
b.获取访问次数最多的三个sql:mysqldumpslow -s c -t 3 /var/lib/mysql/localhost-slow.log
c.按照时间排序，前十条包含left join查询语句的sql:mysqldumpslow -s r -t 3 /var/lib/mysql/localhost-slow.log

分析海量数据

使用存储过程（无return）/存储函数（有return）：

通过存储函数创建随机字符：

delimiter $  //防止；带来的语句中断
create function randstring(n int) returns varchar(225)
begin
    declare all_str varchar(100) default 'abcdefg...ABCDEFG...';
    declare return_str varchar(255) default'';
    declare i int default 0;
    while i<n
    do
        set return_str=concat(return_str,substring(all_str,FLOOR(rand*52),1));
        set i=i+1;
    end while;
    return return_str;
end $

通过存储函数创建随机整数：

create function ran_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(rand()*100);
    return i;
end $

通过存储过程，插入海量数据：

create procedure insert_emp(in eid_start int(10),in data_times int(10))
begin
    declare i int default 0;
    set autocommit=0;--关闭自动提交
    
    repeat
        set i=i+1;
        insert into emp values(eid_start+i,randstring(5),'other',ran_num());
        set i=i+1;
        until i=data_times
        end repeat;
    commit;
end $

插入数据：

call insert_emp(1000,800000);

开始分析：

show variables like '%profiling%';--查看数据分析状态
set profiling=on;
show profiles--会记录所有set profiling=on后所有的sql查询语句所花费的时间

精确分析，sql诊断：

show profile all for query +'show profiles中查询得id'
show profile cpu,blockio for query +'show profiles中查询得id'

全局日志查询：记录开启后得全部sql语句：

show variables like '%general_log%';
set global general_log=1;--开启全局日志
set global log_output='table';--开启后会记录所有sql,会被记录到mysql.general_log表中
set global log_output='file';set global general_log=on;set global general_log_file='...';--也可以记录在文件中

mysql锁机制

操作类型：

a.读锁（共享锁）：对同一个数据（衣服），多个读操作可以同时进行，互不干扰

b.写锁（互斥锁）：如果当前写操作没有完毕（买衣服得一系列操作），则无法进行其他的读操作和写操作

操作范围：

a.表锁：一次性对一张表整体加锁，如myisam存储引擎用表锁，开销小、加锁快、无死锁，但锁范围大容易锁冲突，并发度低

b.行锁：一次性对一行数据加锁，如innodb存储引擎使用行锁，开销大，加锁慢，容易出现死锁，锁范围小，不易发生锁冲突，高并发，不易出现脏读幻读

c.页锁

手动加锁：

增加锁：locak table 表1 read/write,表2 read/write ...
释放锁：unlock tables;
查看加锁的表：show open tables;

总结：

两个会话1，2（其他会话），会话1对A表添加了read锁

会话1，该会话可以对A进行读操作、不能进行写操作，且不能对其他表进行读写操作
会话2（其他会话），该会话可以对除A以外的任何表进行读写操作，对A表可以执行读操作、但是A表执行写操作会‘等待’会话1将锁释放

两个会话1，2（其他会话），会话1对A表添加了write锁

会话1，可以对加了写锁的A表进行读写操作，但是不能对其他表进行读写操作
会话2（其他会话），对会话1中加锁的A表可以进行读写的前提是等待会话1释放锁

mysql表锁分析

查看加锁的表：show open tables;
分析表锁定的严重程度：show status like 'table%';
-table_locks_immediate:可能获取到的锁
-table_locks_waited:需要等待的表锁数

一般规定：table_locks_immediate/table_locks_waited>5000,建议采用innodb引擎，否则采用myisam

mysql表锁模式

myisam引擎在执行查询（select）前，会自动给涉及的所有表加读锁，在执行DEL操作前，会自动给涉及的表加写锁。
所以对myisam表进行操作会有两种情况：

a.对myisam表进行读操作，不会阻塞其他进程（会话）对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作

b.对myisam表进行写操作，会阻塞其他进程（会话）对同一个表的写操作，只有当写锁释放后，才会执行其他进程的读写操作

mysql行锁模式

首先关闭自动提交：set autocommit=0;

会话1:增加第六条数据，会话2：修改第六条数据

a.如果会话x对某条数据a进行dml操作，则其他会话必须等待会话x结束事务（commit/rollback）之后，才能对数据a进行操作

b.表锁通过unlock tables或者事务解锁，行锁只能通过事务解锁

行锁使用注意事项：

a.如果没有索引、或者索引失效，行锁会转化为表锁

b.行锁的一种特殊情况：间隙锁，指的是值在范围内但不存在

如果表中的数据有1234568，没有7，update linelock set name='x' where id>6 and id<8;
此时mysql会自动加间隙锁

行锁分析：

show status like '%innodb_row_lock%';
innodb_row_lock_current_waits:正在等待锁的数量
innodb_row_lock_time：等待总时长，从系统启动到现在一共等了多久
innodb_row_lock_time_avg：平均等待时长
innodb_row_lock_time_max：最大等待时长
innodb_row_lock_waits：等待次数

注意：仅仅是查询语句也可以加锁，在select语句最后加for update

mysql索引优化总结

复合索引，不要跨列使用或者无序使用索引，遵循最佳左前缀原则
尽量使用全索引匹配，不要在索引上进行计算，函数或类型转换
利用小表驱动大表：select ... where 小表.a=大表.b（小表驱动大表可以减少迪科尔积关联次数）
对于左外链接给左表加索引，右外链接给右表加索引
范围查询类似于in、<、>等以及可能会使索引失效的条件放到where最后，这样可以挽救一部分索引
like尽量以常量开头，这样可以使用一部分索引
不要使用关键词or，因为可能让所有索引失效
在exist和in的选择上，如果主查询的数据集大，则使用in，如果子查询的数据集大，则使用exist
使用order by尽量覆盖索引
使用group by的时候，根据什么条件查询，就根据什么条件分组
比较运算符能用 “=”就不用“<>”，因为“=”增加了索引的使用几率
如果只有一条查询结果，就使用 “LIMIT 1”，因为“LIMIT 1”可以避免全表扫描
如果结果集允许重复的话，使用UNION ALL 代替 UNION，因为 UNION ALL 不去重，效率高于 UNION