在上一篇《索引优化(一)》的文章中，已经介绍了联合索引、索引下推、排序分组和文件排序的优化策略，还介绍了如果通过trace工具来查看MySQL选择执行方案的过程，这边文章继续介绍常用的索引优化以及如何设计索引。
一、分页查询优化
很多时候，业务系统需要实现分页展示的功能，可能会用到下面的SQL:
select * from employees limit 10000,10;
该SQL从表中取出从10001开始的10条数据。虽然从结果看是只查询了10条数据，但实际上这条SQL是先读取10010条记录，然后抛弃前10000条记录，然后读到后面10条想要的数据。
因此，如果要查询一张大表比较靠后的数据，执行效率是非常低的，有两种解决方案：
1.1 依靠自增且连续主键
以下面的SQL为例：
select * from employees limit 90000,5;
该SQL查询从第90001开始的五条数据，由于没有单独指定order by，则表示默认通过主键排序。
在前面通过存储过程向employees表插入数据的时候，因为主键是自增且连续的，所以可以改写成通过主键去查询从第90001开始的第五行数据，如下：
select * from employees where id > 90000 limit 5;
这两条SQL的查询结果是一模一样的
原始SQL执行计划：
改写后SQL执行计划：
改写后的SQL扫描行数大大减少，效率显著提高。
但这种优化在实际业务场景中并不适用，因为它对数据表的记录有严格的限制，要保证主键ID一定是自增且连续的。但凡第90000行之前的数据删除一行，这两个SQL的直接结果就是不一样的。
这种优化需要满足两个前提条件：
1) 主键自增且连续
2) 结果是按照主键排序的
1.2 让返回的字段尽可能少
对于非主键字段排序的分页查询(实际场景中这种情况居多)，以下面的SQL为例:
select * from employees ORDER BY name limit 90000,5;
通过执行计划可以看出，虽然name字段有连续索引，但并没有走索引，MySQL通过成本计算觉得扫全表比走索引还要快一些，所以优化器放弃使用索引。
既然我们已经知道limit自身的缺陷导致它就是要把之前全部的记录查出来，这是没法改变的，那我们就想可以通过让返回的字段尽可能少来提升效率，可以对其进行改写。
原始SQL执行计划：
改写后SQL执行计划
通过关联查询，先分页查询出主键ID，在根据主键ID关联就可以走索引，原始SQL排序使用的是Using filesort，而改写为内连接的查询方式后，可以直接使用索引进行排序。
二、Join关联查询优化
MySQL的表关联查询中，会用Nested-Loop Join算法或Block Nested-Loop Join算法。
2.1 数据准备
关联查询中存在驱动表与被驱动表的概念，也就是先查询哪个表，再查询哪个表。
所以这里创建user1和user2两个表结构完全一样的表，比较数据量不同的表作为驱动表的效率。
CREATE TABLE user1 (
id int(11) NOT NULL AUTO_INCREMENT,
name VARCHAR(20) DEFAULT NULL,
age int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY idx_name (name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
create table user2 like user1;
user1表插入10000条数据
DROP PROCEDURE IF EXISTS INSERT_user1;
delimiter $
CREATE PROCEDURE INSERT_user1()
BEGIN
DECLARE i INT;
SET i=1;
WHILE (i<=10000) DO
INSERT INTO user1(name,age) VALUES(CONCAT('lizhi',i),i);
SET i=i+1;
END WHILE;
END $
CALL INSERT_user1();
user2表插入100条数据
DROP PROCEDURE IF EXISTS INSERT_user2;
delimiter $
CREATE PROCEDURE INSERT_user2()
BEGIN
DECLARE i INT;
SET i=1;
WHILE (i<=100) DO
INSERT INTO user2(name,age) VALUES(CONCAT('lizhi',i),i);
SET i=i+1;
END WHILE;
END $
CALL INSERT_user2();
2.2 Nested-Loop Join(NLJ)算法
嵌套循环连接**Nested-Loop Join(NLJ)**算法的逻辑是：
依次一行一行从第一张表(称为驱动表)中读取满足条件的行，然后在这些行数据中取到关联字段，根据关联字段在另一张表(称为被驱动表)取出满足条件的行，然后取出两张表的结果合集。
它的执行计划如下：
从执行计划中可以看出:
user2表是作为驱动表先执行它的查询，user1作为被驱动表(执行计划的ID如果一样就从上到下依次执行);优化器一般会选择小表作为驱动表，用where条件过滤完驱动表，然后再与被驱动表做关联查询。
注:使用inner join时，排在前面的并不一定是驱动表，MySQL会根据筛选的数据量大小自行选择驱动表
当使用left join时，左边的表就是驱动表，右边的表是被驱动表；使用right join时，右边的表就是驱动表，左边的表是被驱动表。
一般Join语句中，如果执行计划没有Extra中没有出现Using join buffer则表示使用的算法是NLJ。
上面SQL的执行过程大概如下：
- 从表
user2中过滤出age=22的记录，然后从记录中取出一行数据 - 从第一次的数据中，取出关联字段
name，然后到user1表中查找(由于有索引，基本依次一次就找到) - 取出表
user1中满足条件的行，与user2中获取到的结果合并，作为结果返回给客户端 - 重复上面的步骤
前面的SQL使用NLJ的关联查询，整个过程会读取user2表中所有数据(扫描100行，age没有建索引，直接是ALL,全表扫描)，然后遍历每行数据中name的值，根据user2表中name的值利用索引扫描user1表中对应的行(因为有索引，一次扫描可以认为是最终只扫描user1表中一行完整数据，而user2表筛选的数据只有一条，所以user1也就只扫描了一行)。因此整个扫描过程扫描了101行数据。
如果被驱动表的关联字段没有索引，使用NLJ算法性能就比较低，因为没有索引的情况下，扫描user1表时，就是全表进行磁盘扫描，效率很低。
这时MySQL就会选择Block Nested-Loop Join(BNL)算法
2.3 Block Nested-Loop Join(BNL)算法
基于块的嵌套循环连接Block Nested-Loop Join(BNL)算法相比于NLJ算法，BNL算法多了一个Join buffer的内存空间，关联查询时，会把驱动表的数据全部加载到join buffer中，然后扫描被驱动表，把被驱动表每一行数据读取出来与join buffer中的数据做对比。
执行计划如下：
Extra中的Using join buffer (Block Nested Loop)说明该关联查询使用的是BNL 算法。
上面的SQL关联查询，关联字段没有索引，所以会采用BNL算法来执行，其大概过程如下：
- 把
user2表中的数据都加载进join buffer中 - 把表
user1中的每一行数据取出来，跟join buffer中的数据做比对 - 返回满足
join条件的数据
整个过程对user1和user2都做了一次全表扫描，因此扫描的总行数为100+10000=10100。并且因为join buffer中的的数据是无序的，因此表user1的每一行数据，都需要在内存中做100次判断，索引内存中的判断次数为100*10000=100万次
在这个例子中，user2表的数据是比较少的，如果表中数据很多，而join buffer的内存空间又是有限的，这个时候就没法一次性将表中数据全部加载到join buffer中。Join buffer的大小由参数join_buffer_size决定，默认值是256K。如果不能一次性放下user2的所有数据，解决方式就是分段放。
比如user2表有500行数据，而join buffer一次只能存放300行，那么就先把前300行数据放入到join buffer中。然后从user1表中取出数据与join buffer中数据做比对，这300行记录比对完成后，就把join buffer清空，把剩下的200行数据加载到join buffer中，然后再重新扫描user1表与join buffer中的数据做比对。
相比于一次性全部放入，这种需要放两次的情况，就需要多扫描一次user1表。
问题：关联字段无索引为什么选择BNL算法？
以上面的SQL为例，如果选择使用NLJ算法，就需要一次从磁盘中读取user2表的数据，然后在对表user1进行10000次磁盘扫描比对，这样下来，就需要100万次的磁盘扫描。而使用BNL算法，可以通过几次扫描将user2的数据全部加载进join buffer中，然后在对user1表进行10000次的磁盘扫描，然后在内存中做100万次的比较，因为磁盘扫描的次数大幅度降低，所以性能也就更好一些。
MySQL对于被驱动表的关联字段，如果有索引，就是用NLJ算法；如果没有索引，就是用BNL算法。
2.4 SQL关联的优化
2.4.1 关联字段加索引
MySQL做Join操作时，尽量选择NLJ算法，即让为关联字段建立索引。
驱动表因为要全部查询出来，所以对于驱动表的过滤条件，也尽量使用索引，避免全表扫描。总之，能走索引的过滤条件都尽量走索引。
2.4.2 小表驱动大表
在写多表关联的SQL时，如果明确直到哪张表是小表，可以使用straight_join写法固定连接驱动方式，省去MySQL优化器判断的时间。
straight_join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
比如下面的SQL:
select * from user2 straight_join user1 on user2.name = user1.name;
执行user2作为关联查询的驱动表。
straight_join只适用于inner join，并不适用于left join，right join。（因为left join，right join已经代表指定了表的执行顺序）
尽可能让优化器去判断，使用straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
大表和小表并不是看原始记录数，而是按照各自的条件进行过滤，过滤完成之后，计算参与join的各个字段的总数据量，数据量小的那个表，就是小表，应该作为驱动表
2.4.3 IN和EXISITS优化
IN和EXSITS的优化原则依然是小表驱动大表
假设有A和B两个表：
当B表的数据集小于A表时，in优于exsits
select * from A where id in (select id from B)
等价于:
for(select id from B){
select * from A where A.id = B.id
}
当A表的数据集小于B表时，exsits优于in
select * from A where exists (select 1 from B where B.id = A.id)
等价于:
for(select * from A){
select * from B where B.id = A.id
}
将主查询A的数据，放入到子查询B中做条件验证，根据验证的结果(true或false)来决定主查询的数据是否保留
总结：
1、EXISTS (subquery)只返回TRUE或FALSE,因此子查询中的SELECT * 也可以用SELECT 1替换,官方说法是实际执行时会忽略SELECT清单,因此没有区别
2、EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3、EXISTS子查询往往也可以用JOIN来代替，何种最优需要具体问题具体分析
三、count()查询优化
3.1 统计方式比较
-- 临时关闭mysql查询缓存，为了查看sql多次执行的真实时间
mysql> set global query_cache_size=0;
mysql> set global query_cache_type=0;
在统计全表的记录时，通常有以下四种方式来统计:
EXPLAIN select count(1) from employees;
EXPLAIN select count(id) from employees;
EXPLAIN select count(name) from employees;
EXPLAIN select count(*) from employees;
注：以上四种方式，只有根据某个字段统计时，不会统计该字段为null的数据行
执行计划：
这四个SQL的执行计划是一模一样的，可见这四个SQL的执行效率都差不多，但这四个SQL还是有一些略微的差异，主要分为两种情况：
统计字段有索引：
在统计字段有索引的情况下，count(字段)会走二级索引，二级索引索引存储数据比主键索引少，所以count(字段) > count(id)
综合就是：
count(*)≈count(1)>count(字段)>count(id)
统计字段无索引：
如果统计的字段没有索引，count(字段)就没法走索引，只能全表扫描，而count(id)还可以走主键索引，所以count(id) > count(字段)
综合就是：
count(*)≈count(1)>count(id)>count(字段)
3.2 常见优化方案
3.2.1 MySQL自行维护总行数
对于MyISAM存储引擎的表，不带where条件的count查询性能是非常高的，因为MyISAM存储引擎的表的总行数会被MySQL存储在磁盘上；查询时直接取出来就可以了，不需要计算。
3.2.2 show table status
如果只需要统计表记录数的估计值，可以使用show table status，性能也是非常高的，只是记录数不是绝对准确的而已：
show table status LIKE 'employees';
3.2.3 将数据维护在缓存里面
插入或删除表数据行的时候同时维护redis里的表总行数key的计数值(用incr或decr命令)，但是这种方式可能不准，很难保证表操作和redis操作的事务一致性
3.2.4 增加数据库计数表
插入或删除表数据行的时候同时维护计数表，这样就可以很简单的保证事务一致性。计数器表可以通过多条记录来摊分记录总数，类似于JDK中LongAddr的热分散设计思想。
四、数据类型选择
在MySQL中选择正确的数据类型，对于性能至关重要，一般遵循下面两步：
1、确定合适的大类型：数字、字符串、时间、二进制(基本不用)
2、确定具体的类型：有无符号，取值范围、变长定长等。
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL。
4.1 数值类型
1）如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
2）建议使用TINYINT代替ENUM、BITENUM、SET。
3）避免使用整数的显示宽度，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT。
4）DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。DECIMAL实际是使用字符串存储的，所以它所占用的字节大小与DECIMAL(M,D)中的M和D有关，如果M>D,字节大小为M+2;否则字节大小为D+2.
5）建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
6）整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。
*注意：使用INT时，如果指定了长度，这个长度并不是存储的数值长度，而是显示长度。如果只是指定了数值并没有什么用，只有与ZEROFILL结合时才会生效，表示用0进行填充，比如字段类型为TINYINT(2)，而字段值为5，它实际显示的内容就是05,也就是在显示长度不够时，用0填充
4.2 日期和时间
1）MySQL能存储的最小时间粒度为秒。
2）建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
3）用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
4）当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认(MySQL5.6以后)，MySQL会自动返回记录插入的确切时间。
5）TIMESTAMP是UTC时间戳，与时区相关。
6）DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
7）除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
4.3 字符串
1）字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
2）CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计算的准确性和完整性。
3）尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
4）BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
5）BLOB和TEXT都不能有默认值。
更多SQL优化的内容可以参考阿里巴巴开发手册中SQL优化指南部分。
五、索引设计原则
5.1 先代码，后索引
一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
5.2 联合索引尽量覆盖查询条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
5.3 不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，其值不是男就是女，那么该字段的基数就是2。
如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。
一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
5.4 长字符串可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点。
当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。
对于这种**varchar(255)**的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。
此时在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。
注：这个20是阿里巴巴的开发手册提供的一个通用值，在后面的文章中，会介绍如果通过现有的数据进行计算来设置这个前缀值。
但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里要对前缀索引有一个了解。
5.5 where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?
一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。
因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
5.6 基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。
扫一扫
3650
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
