Mysql SQL优化

​​
Mysql查询执行的过程链接

一、索引的代价

我们虽然可以根据我们的喜好在不同的列上建立索引，但是建立索引是有代价的，所以不要建太多的索引：
【1】空间上的代价： 每建立一个索引都要为它建立一棵B+树，每一棵B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的B+树由许多数据页组成，可想而知会占多少存储空间了。
【2】时间上的代价： 每次对表中的数据进行增、删、改操作时，都需要去修改各个B+树索引。在B+树上每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。

通过一个示例，来说明索引的代价。如下，给person_info表中创建一个联合索引idx_name_birthday_phone_number。

CREATE TABLE person_info(
    id INT NOT NULL auto_increment,
    name VARCHAR(100) NOT NULL,
    birthday DATE NOT NULL,
    phone_number CHAR(11) NOT NULL,
    country varchar(100) NOT NULL,
    PRIMARY KEY (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' ORDER BY name LIMIT 10;

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

SELECT * FROM person_info ORDER BY name, country LIMIT 10;

【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。

alter table trade_detail modify tradeid varchar(32) CHARACTER SET utf8mb4 default null;

【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的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。