Mysql SQL优化

​​

Mysql

查询执行的过程链接

一、索引的代价

我们虽然可以根据我们的喜好在不同的列上建立索引，但是建立索引是有代价的，所以不要建太多的索引：
【1】空间上的代价： 每建立一个索引都要为它建立一棵

B+

树，每一棵

B+

树的每一个节点都是一个数据页，一个页默认会占用

16KB

的存储空间，一棵很大的

B+

树由许多数据页组成，可想而知会占多少存储空间了。
【2】时间上的代价： 每次对表中的数据进行增、删、改操作时，都需要去修改各个

B+

树索引。在

B+

树上每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。

通过一个示例，来说明索引的代价。如下，给

person_info

表中创建一个联合索引

idx_name_birthday_phone_number

。

CREATETABLE person_info(
    id INTNOTNULLauto_increment,
    name VARCHAR(100)NOTNULL,
    birthday DATENOTNULL,
    phone_number CHAR(11)NOTNULL,
    country varchar(100)NOTNULL,PRIMARYKEY(id),KEY idx_name_birthday_phone_number (name, birthday, phone_number));

以

idx_name_birthday_phone_number

索引为例，看下边这个查询：

SELECT*FROM person_info WHERE name >'Asa'AND name <'Barlow';

**在使用

idx_name_birthday_phone_number

索引进行查询时大致可以分为这两个步骤：**
【1】索引

idx_name_birthday_phone_number

对应的

B+

树中取出

name

值在

Asa～Barlow

之间的用户记录。
【2】由于索引

idx_name_birthday_phone_number

对应的

B+

树用户记录中只包含

name

、

birthday

、

phone_number

、

id

这

4

个字段，而查询列表是*，意味着要查询表中所有字段，也就是还要包括

country

字段。这时需要把从上一步中获取到的每一条记录的

id

字段，再去聚簇索引对应的

B+

树中找到完整的用户记录，也就是我们通常所说的回表，然后把完整的用户记录返回给查询用户。

读取索引

idx_name_birthday_phone_number

数据中，在

Asa～Barlow

之间的记录在磁盘中的存储是相连的，集中分布在一个或几个数据页中，我们可以很快的把这些连着的记录从磁盘中读出来，这种读取方式我们也可以称为顺序

I/O

。

而获取到的记录的

id

字段的值可能并不相连，而在聚簇索引中记录是根据

id

（也就是主键）的顺序排列的，所以根据这些并不连续的

id

值到聚簇索引中访问完整的用户记录可能分布在不同的数据页中，这样读取完整的用户记录可能要访问更多的数据页，这种读取方式我们也可以称为随机

I/O

。

**所以这个使用索引

idx_name_birthday_phone_number

的查询有这么两个特点：**.
【1】使用到两个

B+

树索引，一个二级索引，一个聚簇索引。
【2】访问二级索引使用顺序

I/O

，访问聚簇索引使用随机

I/O

。

需要回表的记录越多，使用二级索引的性能就越低，甚至让某些查询宁愿使用全表扫描也不使用二级索引。比方说

name

值在

Asa～Barlow

之间的用户记录数量占全部记录数量

90%

以上，那么如果使用

idx_name_birthday_phone_number

索引的话，有

90%

多的

id

值需要回表，这不是吃力不讨好么，还不如直接去扫描聚簇索引（也就是全表扫描）。

查询优化器做的工作，查询优化器会事先对表中的记录计算一些统计数据，然后再利用这些统计数据根据查询的条件来计算一下需要回表的记录数，需要回表的记录数越多，就越倾向于使用全表扫描，反之倾向于使用二级索引 + 回表的方式。比方说上边的查询可以改写成这样：

二、联合索引使用问题

B+

树中每层节点都是按照索引列的值从小到大的顺序而形成了一个单链表。如果是联合索引的话，则页面和记录先按照联合索引前边的列排序，如果该列值相同，再按照联合索引后边的列排序。

匹配左边的列

因为

B+

树的数据页和记录先是按照

name

列的值排序的，在

name

列的值相同的情况下才使用

birthday

列进行排序，也就是说

name

列的值不同的记录中

birthday

的值可能是无序的。如果用的不是最左列的话就无法使用到索引，例如：

SELECT*FROM person_info WHERE birthday ='1990-09-27';

如果我们使用的是：

SELECT*FROM person_info WHERE name ='Ashburn'AND phone_number ='15123983239';

这样只能用到

name

列的索引，

birthday

和

phone_number

的索引就用不上了，因为

name

值相同的记录先按照

birthday

的值进行排序，

birthday

值相同的记录才按照

phone_number

值进行排序。

匹配范围值

在使用联合索引进行范围查找时候，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到

B+

树索引。

SELECT*FROM person_info WHERE name >'Asa'AND name <'Barlow'AND birthday >'1980-01-01';

对于联合索引

idx_name_birthday_phone_number

来说，可以用

name

快速定位到通过条件

name > ‘Asa’ AND name < ‘Barlow’

，但是却无法通过

birthday > ‘1980-01-01’

条件继续过滤，因为通过

name

进行范围查找的记录中可能并不是按照

birthday

列进行排序的。精确匹配某一列并范围匹配另外一列。

SELECT*FROM person_info WHERE name ='Ashburn'AND birthday >'1980-01-01'AND birthday <'2000-12-31'AND phone_number >'15100000000';

在这条

SQL

中，由于对

name

是精确查找，所以在

name

相同的情况下

birthday

是排好序的，

birthday

列进行范围查找是可以用到

B+

树索引的。但是对于

phone_number

来说，通过

birthday

的范围查找的记录的

birthday

的值可能不同，所以这个条件无法再利用

B+

树索引了。

排序

【1】对于联合索引来说，

ORDER BY

子句后边的列的顺序也必须按照索引列的顺序给出，如果给出

ORDER BY phone_number, birthday, name

的顺序，那也是用不了

B+

树索引。
【2】

ASC

、

DESC

混用是不能使用到索引的：对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是

ASC

规则排序，要么都是

DESC

规则排序。
【3】

Where

子句中出现非索引列则无法使用索引：这个语句需要回表后查出整行记录进行过滤后才能进行排序，无法使用索引进行排序。

SELECT*FROM person_info WHERE country ='China'ORDERBY name LIMIT10;

【4】排序列包含非索引列无法使用索引：

SELECT*FROM person_info ORDERBY name, country LIMIT10;

【5】

Order by

中使用了函数也无法使用索引；

三、匹配列前缀

和联合索引其实有点类似，如果一个字段比如是

varchar

类型的

name

字段，那么在索引中

name

字段的排列就会：
【1】先比较字符串的第一个字符，第一个字符小的那个字符串就比较小；
【2】如果两个字符串的第一个字符相同，那就再比较第二个字符，第二个字符比较小的那个字符串就比较小；
【3】如果两个字符串的第二个字符也相同，那就接着比较第三个字符，依此类推；

所以这样是可以用到索引：

SELECT*FROM person_info WHERE name LIKE'As%';

但是这样就用不到：

SELECT*FROM person_info WHERE name LIKE'%As%';

四、覆盖索引

如果我们查询的所有列都可以在索引中找到，那么就不需要回表去查找对应的列了。

SELECT name, birthday, phone_number FROM person_info WHERE name >'Asa'AND name <'Barlow'

因为我们只查询

name

,

birthday

,

phone_number

这三个索引列的值，所以在通过

idx_name_birthday_phone_number

索引得到结果后就不必到聚簇索引中再查找记录的剩余列，也就是

country

列的值了，这样就省去了回表操作带来的性能损耗。

五、让索引列在比较表达式中单独出现

假设表中有一个整数列

m y_col

，我们为这个列建立了索引。下边的两个

Where

子句虽然语义是一致的，但是在效率上却有差别：
【1】

Where my_col * 2 < 4

；
【2】

Where my_col < 4/2

；
第

1

个

Where

子句中

my_col

列并不是以单独列的形式出现的，而是以

my_col * 2

这样的表达式的形式出现的，存储引擎会依次遍历所有的记录，计算这个表达式的值是不是小于

4

，所以这种情况下是使用不到为

my_col

列建立的

B+

树索引的。而第

2

个

Where

子句中

my_col

列是以单独列的形式出现的，这样的情况可以直接使用

B+

树索引。

六、页分裂带来的性能损耗

我们假设一个页中只能存储

5

条数据：

这个过程我们也可以称为页分裂。页面分裂和记录移位意味着性能损耗所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有

AUTO_INCREMENT

，让存储引擎自己为表生成主键。

七、减少对行锁的时间

两阶段锁协议：在

InnoDB

事务中，行锁是在需要的时候才加上的，但并不是不需要了就立刻释放，而是要等到事务结束时才释放。所以，如果你的事务中需要锁多个行，要把最可能造成锁冲突、最可能影响并发度的锁尽量往后放。假设你负责实现一个电影票在线交易业务，顾客

A

要在影院

B

购买电影票。我们简化一点，这个业务需要涉及到以下操作：
【1】从顾客

A

账户余额中扣除电影票价；
【2】给影院

B

的账户余额增加这张电影票价；
【3】记录一条交易日志。
也就是说，要完成这个交易，我们需要

update

两条记录，并

insert

一条记录。当然，为了保证交易的原子性，我们要把这三个操作放在一个事务中。

试想如果同时有另外一个顾客

C

要在影院

B

买票，那么这两个事务冲突的部分就是语句

2

了。因为它们要更新同一个影院账户的余额，需要修改同一行数据。

根据两阶段锁协议，不论你怎样安排语句顺序，所有的操作需要的行锁都是在事务提交的时候才释放的。所以，如果你把语句

2

安排在最后，比如按照

3、1、2

这样的顺序，那么影院账户余额这一行的锁时间就最少。这就最大程度地减少了事务之间的锁等待，提升了并发度。

八、count 函数优化

我们主要来看看

count(*)

、

count

(主键

id

)、

count

(字段)和

count(1)

这三者的性能差别。
【1】对于

count

(主键

id

)来说，

InnoDB

引擎会遍历整张表，把每一行的

id

值都取出来，返回给

server

层。

server

层拿到

id

后，判断是不可能为空的，就按行累加。
【2】对于

count(1)

来说，

InnoDB

引擎遍历整张表，但不取值。

server

层对于返回的每一行，放一个数字

“1”

进去，判断是不可能为空的，按行累加。

单看这两个用法的差别的话，你能对比出来，

count(1)

执行得要比

count

(主键

id

)快。因为从引擎返回

id

会涉及到解析数据行，以及拷贝字段值的操作。

对于

count

(字段)来说：
【1】如果这个“字段”是定义为

not null

的话，一行行地从记录里面读出这个字段，判断不能为

null

，按行累加；
【2】如果这个“字段”定义允许为

null

，那么执行的时候，判断到有可能是

null

，还要把值取出来再判断一下，不是

null

才累加。

也就是前面的第一条原则，

server

层要什么字段，

InnoDB

就返回什么字段。但是

count()

是例外，并不会把全部字段取出来，而是专门做了优化，不取值。

count()

肯定不是

null

，按行累加。所以结论是：按照效率排序的话，

count

(字段)<

count

(主键

id

)<

count(1)

≈

count()

，所以建议尽量使用

count()

。

九、order by性能优化

在

MySQL

排序中会用到内存来进行排序，

sort_buffer_size

，就是

MySQL

为排序开辟的内存

sort_buffer

的大小。如果要排序的数据量小于

sort_buffer_size

，排序就在内存中完成。但如果排序数据量太大，内存放不下，则不得不利用磁盘临时文件辅助排序。

如果查询要返回的字段很多的话，那么

sort_buffer

里面要放的字段数太多，这样内存里能够同时放下的行数很少，要分成很多个临时文件，排序的性能会很差。

MySQL

就会根据

max_length_for_sort_data

参数来限定排序的行数据的长度，如果单行的长度超过这个值，

MySQL

就认为单行太大，要根据

rowid

排序。

rowid

排序只会在

sort_buffer

放入要排序的字段，减少要排序的数据的大小，但是

rowid

排序会多访问一次主键索引，多一次回表以便拿到需要返回的数据。

所以我们在写排序

SQL

的时候，需要尽量做到以下三点：
【1】返回的数据列数尽量的少，不要返回不必要的数据列；
【2】因为索引天然是有序的，所以如果要排序的列如果有必要的话，可以设置成索引，那么就不需要在

sort_buffer

中排序就可以直接返回了；
【3】如果有必要的话可以使用覆盖索引，这样在返回数据的时候连通过主键回表都不需要做就可以直接查询得到数据

十、隐式类型转换

mysql>select*from tradelog where tradeid=110717;

在这条

sql

中，交易编号

tradeid

这个字段上，本来就有索引，但是

explain

的结果却显示，这条语句需要走全表扫描。你可能也发现了，

tradeid

的字段类型是

varchar(32)

，而输入的参数却是整型，所以需要做类型转换。因为在

MySQL

中，字符串和数字做比较的话，是将字符串转换成数字。所以上面的

SQL

相当于：

mysql>select*from tradelog where  CAST(tradid AS signed int)=110717;

所以这条包含了隐式类型转换的 SQL是无法走树搜索功能的。

十一、隐式字符编码转换

mysql>select d.*from tradelog l, trade_detail d where d.tradeid=l.tradeid and l.id=2;#语句Q1

在这条

SQL

中，如果

tradelog

表的字符集编码是

utf8mb4

，

trade_detail

表的字符集编码是

utf8

，那么也是无法走索引的。因为在这个

SQL

中，我们跑执行计划可以发现

tradelog

是驱动表，

trade_detail

是被驱动表，也就是从

tradelog

表中取

tradeid

字段，再去

trade_detail

表里查询匹配字段。

字符集

utf8mb4

是

utf8

的超集，所以当这两个类型的字符串在做比较的时候，

MySQL

内部的操作是，先把

utf8

字符串转成

utf8mb4

字符集，再做比较。因此， 在执行上面这个语句的时候，需要将被驱动数据表里的字段一个个地转换成

utf8mb4

。所以是无法走索引的。所以我们可以如下优化：
【1】把

trade_detail

表上的

tradeid

字段的字符集也改成

utf8mb4

。

altertable trade_detail modify tradeid varchar(32)CHARACTERSET utf8mb4 defaultnull;

【2】修改

SQL

语句

select d.*from tradelog l , trade_detail d where d.tradeid=CONVERT(l.tradeid USING utf8)and l.id=2;

十二、Join优化

【在关联字段上使用索引】：我这里有两个表，

t1

和

t2

，表结果一模一样，字段

a

是索引字段

select*from t1 straight_join t2 on(t1.a=t2.a);

这样关联的数据执行逻辑就是：
【1】从表

t1

中读入一行数据

R

；
【2】从数据行

R

中，取出

a

字段到表

t2

里去查找；
【3】取出表

t2

中满足条件的行，跟

R

组成一行，作为结果集的一部分；
【4】重复执行步骤

1

到

3

，直到表

t1

的末尾循环结束。

这个

SQL

由于使用了索引，所以在将

t1

表数据取出来后根据

t1

表的

a

字段实际上是对

t2

表的一个索引的等值查找，所以

t1

和

t2

比较的行数是相同的，这样使用被驱动表的索引关联称之为

“Index Nested-Loop Join”

，简称

NLJ

。由于是驱动表

t1

去匹配被驱动表

t2

，那么匹配次数取决于

t1

有多少数据，所以在用索引关联的时候还需要注意，最好使用数据量少的表作为驱动表。

【使用

join_buffer

来进行关联】：如果我们将

sql

改成如下（在

t2

表中

b

字段是无索引的）：

select*from t1 straight_join t2 on(t1.a=t2.b);

这时候，被驱动表上没有可用的索引，算法的流程是这样的：
【1】把表

t1

的数据读入线程内存

join_buffer

中，由于我们这个语句中写的是

select *

，因此是把整个表t1放入了内存；
【2】扫描表

t2

，把表

t2

中的每一行取出来，跟

join_buffer

中的数据做对比，满足

join

条件的，作为结果集的一部分返回；

join_buffer

的大小是由参数

join_buffer_size

设定的，默认值是

256k

。如果放不下表

t1

的所有数据话，策略很简单，就是分段放。如果分段放的话，那么被驱动表就要扫描多次，那么就会有性能问题。

所以如果

join_buffer_size

放不下的话就要使用小表作为驱动表，减少分段放的次数，在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与 join的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。

​