MySQL索引优化

本文总结了个人学习索引优化知识，欢迎参考

一、MySQL原理

MYSQL逻辑分层 ：连接层 服务层 引擎层 存储层

InnoDB(默认) ：事务优先 （适合高并发操作；行锁）
MyISAM ：性能优先 （表锁）

二、SQL优化

优化原因：性能低、执行时间太长、等待时间太长、SQL语句欠佳（连接查询）、索引失效、服务器参数设置不合理（缓冲、线程数）主要就是在优化索引

SQL编写过程：

1
2
3
1select dinstinct ...from ...join ...on ...where ...group by ...having ...order by ...limit ...

2

3

SQL解析过程：

1
2
3
1from ... on ...join ...where ...group by ...having ...select dinstinct ...order by ...limit ...

2

3

三、索引

索引： index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：默认B树、Hash树…）

索引的弊端：

1. 索引本身很大， 可以存放在内存/硬盘（通常为 硬盘）
2. 索引不是所有情况均适用： a.少量数据 b.频繁更新的字段 c.很少使用的字段
3. 索引会降低增删改的效率（增删改 查）

优势：

1. 提高查询效率（降低IO使用率）
2. 降低CPU使用率 （…order by age desc,因为 B树索引 本身就是一个 好排序的结构，因此在排序时 可以直接使用）

https://www.cnblogs.com/annsshadow/p/5037667.html

索引分类：

• 主键索引： 不能重复。id 不能是null

• 唯一索引 ：不能重复。id 可以是null

• 单值索引 ： 单列， age ;一个表可以多个单值索引,name。

• 复合索引 ：多个列构成的索引 （相当于 二级目录 ： z: zhao） (name,age) (a,b,c,d,…,n)

创建索引：

方式一：

1
2
3
4
5
6
7
8
9
1create 索引类型  索引名  on 表(字段)

2单值：

3create index   dept_index on  tb(dept);

4唯一：

5create unique index  name_index on tb(name) ;

6复合索引：

7create index dept_name_index on tb(dept,name);

8

9

方式二：

1
2
3
4
5
6
7
8
9
10
11
1alter table 表名 索引类型  索引名（字段）

2单值：

3alter table tb add index dept_index(dept) ;

4唯一：

5alter table tb add unique index name_index(name);

6复合索引：

7alter table tb add index dept_name_index(dept,name);

8

9注意：如果一个字段是primary key，则改字段默认就是 主键索引  

10

11

删除索引：

1
2
3
4
1drop index 索引名 on 表名 ;

2drop index name_index on tb ;

3

4

查询索引：

1
2
3
4
1show index from 表名 ;

2show index from 表名 \G

3

4

四、SQL性能问题

a.分析SQL的执行计划 : explain ，可以模拟SQL优化器执行SQL语句，从而让开发人员 知道自己编写的SQL状况

b.MySQL查询优化其会干扰我们的优化

​ 查询执行计划： explain +SQL语句
​ explain select * from tb ;

1
2
3
4
5
6
7
8
9
10
11
12
1id :编号

2select_type :查询类型

3table :表

4type :类型

5possible_keys :预测用到的索引 

6key :实际使用的索引

7key_len :实际使用索引的长度     

8ref  :表之间的引用

9rows :通过索引查询到的数据量 

10Extra :额外的信息

11

12

• id

id值不同：id值越大越优先查询 (本质：在嵌套子查询时，先查内层 再查外层)
id值相同：从上往下 顺序执行

表的执行顺序 因数量的个数改变而改变的原因： 笛卡儿积

1
2
3
4
5
1   a        b       c

2   4        3       2      =       2*3=6 * 4   =24

3                                   3*4=12* 2   =24

4

5

数据小的表 优先查询；

• select_type:查询类型

PRIMARY:包含子查询SQL中的 主查询 （最外层）
SUBQUERY：包含子查询SQL中的 子查询 （非最外层）
simple:简单查询（不包含子查询、union）
derived:衍生查询(使用到了临时表)在from子查询中， 如果有table1 union table2 ，则table1 就是derived,table2就是union

union result :告知开发人员，那些表之间存在union查询

• type类型

system>const>eq_ref>ref>range>index>all

要对type进行优化的前提：有索引

其中：system,const只是理想情况；实际能达到 ref>range，system（忽略）只有一条数据的系统表或衍生表只有一条数据的主查询

eq_ref（唯一性索引）：对于每个索引键的查询，返回匹配唯一行数据（有且只有1个，不能多 、不能0）常见于唯一索引 和主键索引。

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

range：检索指定范围的行 ,where后面是一个范围查询(between ,> < =, 特殊:in有时候会失效 ，从而转为 无索引all)

index：查询全部索引中数据，只需要扫描索引表，不需要所有表中的所有数据

all：查询全部表中的数据，扫描到不是索引的数据，需要全表所有，即需要所有表中的所有数据

• possible_keys ：可能用到的索引，是一种预测，不准。

• key ：实际使用到的索引

• **key_len ：索引的长度 **，作用：用于判断复合索引是否被完全使用 （a,b,c）。

例1：char是固定长度，在utf8：1个字符站3个字节

1
2
3
4
5
6
7
8
9
1  create table test_kl

2  (

3   name char(20) not null default ''

4  );

5  alter table test_kl add index index_name(name) ;

6  

7  explain select * from test_kl where name ='' ;  -- key_len :60

8

9

例2：如果索引字段可以为Null,则会使用1个字节用于标识。

1
2
3
4
5
6
1alter table test_kl add column name1 char(20) ;  -- name1可以为null

2alter table test_kl add index index_name1(name1) ;

3

4explain select * from test_kl where name1 ='' ; -- key_len :61

5

6

例3：增加一个复合索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
1alter table test_kl add index name_name1_index (name,name1) ; 

2

3explain select * from test_kl where name1 = '' ; --121

4explain select * from test_kl where name = '' ; --60

5

6varchar(20)

7alter table test_kl add column name2 varchar(20) ; --可以为Null 

8alter table test_kl add index name2_index (name2) ;

9

10explain select * from test_kl where name2 = '' ;  --63

11-- 20*3=60 +  1(null)  +2(用2个字节 标识可变长度)  =63

12

13utf8:1个字符3个字节

14gbk:1个字符2个字节

15latin:1个字符1个字节

16

17

• ref : 作用： 指明当前表所参照的字段。(注意与type中的ref值区分)

• rows: 被索引优化查询的数据个数 (实际通过索引而查询到的数据个数)

• Extra：

(i). using filesort ： 性能消耗大；需要“额外”的一次排序（查询） 。常见于 order by 语句中。
排序：先查询

1
2
3
4
5
6
7
8
9
1a1:姓名  a2：年龄

2

3explain select * from test02 where a1 ='' order by a1 ;

4explain select * from test02 where a1 ='' order by a2 ; -- using filesort

5

6对于单索引， 如果排序和查找是同一个字段，则不会出现using filesort；如果排序和查找不是同一个字段，则会出现using filesort；

7避免： where哪些字段，就order by那些字段

8

9

复合索引：不能跨列（最佳左前缀）

1
2
3
4
5
6
7
8
9
1alter table test02 add index idx_a1_a2_a3 (a1,a2,a3) ;

2explain select *from test02 where a1='' order by a3 ; -- using filesort

3explain select *from test02 where a2='' order by a3 ; -- using filesort

4explain select *from test02 where a1='' order by a2 ;

5explain select *from test02 where a2='' order by a1 ; -- using filesort

6

7-- 小结：避免： where和order by 按照复合索引的顺序使用，不要跨列或无序使用。

8

9

(ii) .using temporary:性能损耗大 ，用到了临时表。

一般出现在group by 语句中。

explain select a1 from test02 where a1 in (‘1’,‘2’,‘3’) group by a1 ;

explain select a1 from test02 where a1 in (‘1’,‘2’,‘3’) group by a2 ; –using temporary

避免：查询那些列，就根据那些列 group by .

(iii). using index :性能提升; 索引覆盖（覆盖索引）。原因：不读取原文件，只从索引文件中获取数据 （不需要回表查询）只要使用到的列全部都在索引中，就是索引覆盖using index

例如：test02表中有一个复合索引(a1,a2,a3)

1
2
3
4
5
6
7
1explain select a1,a2 from test02 where a1='' or a2= '' ; -- using index   

2drop index idx_a1_a2_a3 on test02;

3

4alter table test02 add index idx_a1_a2(a1,a2) ;

5explain select a1,a3 from test02 where a1='' or a3= '' ;

6

7

​ (iii).using where （需要回表查询）
​ 假设age是索引列
​ 但查询语句select age,name from …where age =…,此语句中必须回原表查name，因此会显示using where.

(iv). impossible where ： where子句永远为false
explain select * from test02 where a1=‘x’ and a1=‘y’ ;

优化总结:

a.最佳做前缀，保持索引的定义和使用的顺序一致性

b.索引需要逐步优化 c.将含In的范围查询放到where条件的最后，防止失效。

c. 多表优化索引往哪张表加？

• 小表驱动大表
• 索引建立经常使用的字段上 （本题 t.cid=c.cid可知，t.cid字段使用频繁，因此给该字段加索引） [一般情况对于左外连接，给左表加索引；右外连接，给右表加索引]

小表：10
大表：300
where 小表.x 10 = 大表.y 300; –循环了几次？10
大表.y 300=小表.x 10 –循环了300次

1
2
3
4
5
1当编写 ..on t.cid=c.cid 时，将数据量小的表 放左边（假设此时t表数据量小）

2

3Using join buffer:extra中的一个选项，作用：Mysql引擎使用了 连接缓存。

4

5

避免索引失效的一些原则

复合索引
a. 不要跨列或无序使用（最佳左前缀）
b. 尽量使用全索引匹配 (a,b,c) 都用到

不要在索引上进行任何操作（计算、函数、类型转换），否则索引失效

1
2
3
4
5
6
7
1-- 假设A.x是索引不要：select ..where A.x*3 = .. ;

2

3explain select * from book where authorid = 1 and typeid = 2 ;at,2个索引

4explain select * from book where authorid = 1 and typeid*2 = 2 ;a,1个索引

5explain select * from book where authorid*2 = 1 and typeid = 2 ;0个索引

6

7

原因：对于复合索引，如果左边失效，右侧全部失效。(a,b,c)，例如如果 b失效，则b c同时失效。

复合索引不能使用不等于（!= <>）或is null (is not null)，否则自身以及右侧所有全部失效。复合索引中如果有>，则自身和右侧索引全部失效

补救。尽量使用索引覆盖（using index）

like尽量以“常量”开头，不要以’%'开头，否则索引失效

1
2
3
4
5
1explain select * from teacher  where tname like '%x%'; --tname索引失效

2explain select * from teacher  where tname like 'x%';

3explain select tname from teacher  where tname like '%x%';

4

5

如果必须使用like '%x%'进行模糊查询，可以使用索引覆盖 挽救一部分。

尽量不要使用类型转换（显示、隐式），否则索引失效

1
2
3
4
5
1explain select * from teacher where tname = 'abc' ;

2explain select * from teacher where tname = 123 ;

3//程序底层将 123 -> '123'，即进行了类型转换，因此索引失效

4

5

尽量不要使用or，否则索引失效

1
2
3
4
1explain select * from teacher where tname ='' or tcid >1 ; 

2-- 将or左侧的tname 失效。

3

4

一些其他的优化方法

***主查询***的数据集大，则使用In ,效率高。

***子查询***的数据集大，则使用exist,效率高。

order by 优化
using filesort 有两种算法：双路排序、单路排序 （根据IO的次数）

• MySQL4.1之前 默认使用 双路排序；双路：扫描2次磁盘

（1：从磁盘读取排序字段 ,对排序字段进行排序（在buffer中进行的排序） 2：扫描其他字段 ）IO较消耗性能

1
2
3
4
1* MySQL4.1之后 默认使用 单路排序 ： 只读取一次（全部字段），在buffer中进行排序。但种单路排序 会有一定的隐患 （不一定真的是“单路|1次IO”，有可能多次IO）。原因：如果数据量特别大，则无法 将所有字段的数据 一次性读取完毕，因此 会进行“分片读取、多次读取”。

2

3* **注意**：单路排序 比双路排序 会占用更多的buffer。  

4

单路排序在使用时，如果数据大，可以考虑调大buffer的容量大小：

set max_length_for_sort_data = 1024 单位 byte

如果 max_length_for_sort_data 值太低，则mysql会自动从单路–>双路（太低：需要排序的列的总大小超过了 *max_length_for_sort_data *定义的字节数）

提高order by查询的策略：
a.选择使用单路、双路 ；调整buffer的容量大小；
b.避免select * …
c.复合索引 不要跨列使用 ，避免using filesort
d.保证全部的排序字段 排序的一致性（都是升序 或 降序）

​

SQL排查 – 慢查询日志:MySQL提供的一种日志记录，用于记录MySQL种响应时间超过阈值的SQL语句 （long_query_time，默认10秒）

慢查询日志默认是关闭的；建议：开发调优是 打开，而 最终部署时关闭。
​检查是否开启了 慢查询日志 ：show variables like ‘%slow_query_log%’ ;

• 临时开启：

​ set global slow_query_log = 1 ; –在内存种开启
​ exit
​ service mysql restart

1
2
1* 永久开启：  

2

​ /etc/my.cnf 中追加配置：
​ vi /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 中追加配置：
​ vi /etc/my.cnf
​ [mysqld]
​ long_query_time=3

具体的不详细列出了

• 锁机制 ：解决因资源共享 而造成的并发问题。

示例：买最后一件衣服X
A: X 买：X加锁 ->试衣服…下单…付款…打包 ->X解锁
B: X 买：发现X已被加锁，等待X解锁， X已售空
分类：

• 操作类型：

a.读锁（共享锁）： 对同一个数据（衣服），多个读操作可以同时进行，互不干扰。
b.写锁（互斥锁）： 如果当前写操作没有完毕（买衣服的一系列操作），则无法进行其他的读操作、写操作

• 操作范围：

a.表锁 ：一次性对一张表整体加锁。

如MyISAM存储引擎使用表锁，开销小、加锁快；无死锁；但锁的范围大，容易发生锁冲突、并发度低。
b.行锁 ：一次性对一条数据加锁。

如InnoDB存储引擎使用行锁，开销大，加锁慢；容易出现死锁；锁的范围较小，不易发生锁冲突，并发度高（很小概率 发生高并发问题：脏读、幻读、不可重复度、丢失更新等问题）。
c.页锁

• MySQL表级锁的锁模式

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（DML）前，会自动给涉及的表加写锁。所以对MyISAM表进行操作，会有以下情况：

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

b、对MyISAM表的写操作（加写锁），会阻塞其他进程（会话）对同一表的读和写操作，
只有当写锁释放后，才会执行其它进程的读写操作。

分析表锁定：
查看哪些表加了锁： show open tables ; 1代表被加了锁
分析表锁定的严重程度： show status like ‘table%’ ;
Table_locks_immediate :即可能获取到的锁数
Table_locks_waited：需要等待的表锁数(如果该值越大，说明存在越大的锁竞争)
一般建议：
Table_locks_immediate/Table_locks_waited > 5000， 建议采用InnoDB引擎，否则MyISAM引擎
​ 如果索引类 发生了类型转换，则索引失效。 因此 此次操作，会从行锁 转为表锁。
​
​ update linelock set name =‘x’ where id >1 and id<9 ; 即在此where范围中，没有id=7的数据，则id=7的数据成为间隙。
​ 间隙：Mysql会自动给 间隙 加索 ->间隙锁。即 本题 会自动给id=7的数据加 间隙锁（行锁）。
​ 行锁：如果有where，则实际加索的范围 就是where后面的范围（不是实际的值）

行锁：
​ InnoDB默认采用行锁；
​ 缺点： 比表锁性能损耗大。
​ 优点：并发能力强，效率高。
​ 因此建议，高并发用InnoDB，否则用MyISAM。

如有错误欢迎指正！