Mysql SQL优化
Mysql 查询执行的过程链接
一、不要建立太多索引
我们虽然可以根据我们的喜好在不同的列上建立索引，但是建立索引是有代价的：
【1】空间上的代价：每建立一个索引都要为它建立一棵 B+树，每一棵 B+树的每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，一棵很大的 B+树由许多数据页组成，可想而知会占多少存储空间了。
【2】时间上的代价：每次对表中的数据进行增、删、改操作时，都需要去修改各个 B+树索引。在B+树上每层节点都是按照索引列的值从小到大的顺序排序而组成了双向链表。不论是叶子节点中的记录，还是内节点中的记录（也就是不论是用户记录还是目录项记录）都是按照索引列的值从小到大的顺序而形成了一个单向链表。而增、删、改操作可能会对节点和记录的排序造成破坏，所以存储引擎需要额外的时间进行一些记录移位，页面分裂、页面回收等操作来维护好节点和记录的排序。
二、联合索引使用问题
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条数据：
如果这时候我插入一条 id为4的数据，那么我们就要在分配一个新页。由于 5>4，索引是有序的，所以需要将 id=5这条数据移动到下一页中，并插入一条 id=4新的数据到页10中：
这个过程我们也可以称为页分裂。页面分裂和记录移位意味着性能损耗所以如果我们想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以我们建议：让主键具有 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】如果有必要的话可以使用覆盖索引，这样在返回数据的时候连通过主键回表都不需要做就可以直接查询得到数据
十、隐式类型转换
在这条 sql中，交易编号 tradeid这个字段上，本来就有索引，但是 explain的结果却显示，这条语句需要走全表扫描。你可能也发现了，tradeid的字段类型是 varchar(32)，而输入的参数却是整型，所以需要做类型转换。因为在 MySQL中，字符串和数字做比较的话，是将字符串转换成数字。所以上面的SQL相当于：
mysql> select * from tradelog where CAST(tradid AS signed int) = 110717;
所以这条包含了隐式类型转换的 SQL是无法走树搜索功能的。
十一、隐式字符编码转换
在这条 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的各个字段的总数据量，数据量小的那个表，就是“小表”，应该作为驱动表。
