5-05 MySQL索引优化实战二
2022-12-13 16:48:04 0 举报05 MySQL索引优化实战二
作者其他创作
大纲/内容
实战应用示例表1（以下分页查询和count(*)查询优化示例使用此表）
1. 创建示例表
2. 通过存储过程插入示例数据
实战应用示例表2（以下Join关联查询优化示例使用此表）
1. 创建示例表
2. 通过存储过程插入示例数据
分页查询优化
实际分页功能实现问题
如下分页SQL实现示例：（从表 employees 中取出从 10001 行开始的 10 行记录）
示例中看似只查询了 10 条记录，实际这条 SQL ：
1. 先读取 10010 条记录
2. 然后抛弃 10000 条记录
3. 再然后读取后面 10 条想要的数据
因此要查询一张大表，且比较靠后的数据，执行效率非常低
常见的分页场景优化技巧
1. 根据自增且连续的主键排序的分页查询
如下所示：（根据自增且连续主键排序的分页查询）
示例中 SQL 表示：
查询从第 90001 开始的5行数据
没有添加单独 order by，默认通过主键排序
优化 SQL：
因表 employees 的主键是自增且连续的，所以可以优化成按主键查询从第90001开始的5行数据
如下所示：
优化前后 SQL 执行计划对比：
优化前：
优化后：
优化后的 SQL 走了索引，而且扫描的行数大大减少，执行效率更高
但是，这种优化后的 SQL 很多场景并不实用：
1. 因为表中可能某些记录被删除后，主键空缺，导致结果不一致
如下所示：（先删一条前面的记录，再测试原 SQL 和 优化后的 SQL）
因此，如果主键不连续，不能使用上面的优化方法
2. 如果原 SQL 排序 order by 字段为非主键字段，使用上面优化方法会导致两条SQL的结果不一致
这种优化方案，需要同时满足以下两个条件：
1. 主键自增且连续
2. 结果是按照主键排序的
2. 根据非主键字段排序的分页查询
如下所示：（根据非主键字段排序的分页查询，执行时间 0.094s）
优化SQL：
关键是让排序时返回的字段尽可能少，所以可以让排序和分页操作先查出主键，然后根据主键查到对应的记录
如下所示：（执行时间 0.026s）
优化前后 SQL 执行计划对比：
查看原SQL执行计划：
发现并没有使用name字段的索引（key字段对应的值为null），且排序使用的filesort
原因：扫描整个索引并查找到没有索引的行（可能要遍历多个索引树）的成本比扫描全表成本更高
查看优化后SQL执行计划：
优化后的查询结果与原 SQL 一致，且执行时间减少了一半以上
原 SQL 使用的是 filesort 排序，而优化后的 SQL 使用的是索引排序
Join 关联查询优化
1. MySQL的表关联常见有两种算法：
Nested-Loop Join （NLJ）算法
Block Nested-Loop Join（BNL） 算法
2. 嵌套循环连接 Nested-Loop Join(NLJ) 算法
一次一行循环地从第一张表（称为驱动表）中读取行，在这行数据中取到关联字段，根据关联字段在另一张表（被驱动表）里取出满足条件的行，然后取出两张表的结果合集
如下所示：
从执行计划中可以看到：
驱动表是 t2，被驱动表是 t1
1）先执行的就是驱动表 (如果执行计划结果的id一样，则按从上到下顺序执行)
2）优化器一般会优先选择小表做驱动表，t2 表数据量小，作为驱动表
3）所以当使用 inner join 时，排在前面的表并不一定就是驱动表，小表做驱动表，大表作为被驱动表
而当使用 left join 时：
左表是驱动表，右表是被驱动表
而当使用 right join 时：
右表是驱动表，左表是被驱动表
以上查询使用了 NLJ算法：
一般 join 语句中，如果执行计划 Extra 中未出现 Using join buffer，则表示使用的 join 算法是 NLJ
上面SQL的大致流程如下：
1）从表 t2 中读取一行数据（如果t2表有查询过滤条件的，会从过滤结果里取出一行数据）；
2）从第 1 步的数据中，取出关联字段 a，到表 t1 中查找；（t2每行数据找t1数据做比对）
3）取出表 t1 中满足条件的行，跟 t2 中获取到的结果合并，作为结果返回给客户端；
4）重复上面 3 步。
整个过程中扫描了多少行？
1）整个过程会读取 t2 表的所有数据(扫描100行) --> 100行
2）然后遍历这每行数据中字段 a 的值，根据 t2 表中 a 的值索引扫描 t1 表中的对应行 --> 100行
因此整个过程扫描了 200 行
如果被驱动表的关联字段没索引，使用NLJ算法性能会比较低，MySQL会选择 Block Nested-Loop Join算法
3. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL)算法
把驱动表的数据读入到 join_buffer 中，然后扫描被驱动表，把被驱动表每一行取出来跟 join_buffer 中的数据做对比
如下所示：
从执行计划中可以看到这些信息：
Extra 中 的 Using join buffer (Block Nested Loop) 说明该关联查询使用的是 BNL 算法
上面sql的大致流程如下：
1. 把 t2 的所有数据放入到 join_buffer 中
2. 把表 t1 中每一行取出来，跟 join_buffer 中的数据做对比（t1每行数据找t2数据做比对）
3. 返回满足 join 条件的数据
整个过程中扫描了多少行？
整个过程对表 t1 和 t2 都做了一次全表扫描
因此扫描的总行数为10000(表 t1 的数据总量) + 100(表 t2 的数据总量) =10100
因为 join_buffer 里的数据是无序的，因此对表 t1 中的每一行，都要做 100 次判断，所以内存中的判断次数是100 * 10000= 100 万次。
如果表 t2 是一个大表，join_buffer 放不下怎么办呢？
join_buffer 的大小是由参数 join_buffer_size 设定的，默认值是 256k。
如果放不下表 t2 的所有数据话，策略很简单，就是分段放
被驱动表的关联字段没索引为什么要选择使用 BNL 算法而不使用 Nested-Loop Join 呢？
如果上面第二条SQL使用 Nested-Loop Join，那么扫描行数为 100 * 10000 = 100万次，这个是磁盘扫描。
很显然，用BNL磁盘扫描次数（10100行）少很多，相比于磁盘扫描，BNL的内存计算会快得多。
因此MySQL对于被驱动表的关联字段没索引的关联查询，一般都会使用 BNL 算法。如果有索引一般选择 NLJ 算法，有索引的情况下 NLJ 算法比 BNL算法性能更高
4. 对于关联sql的优化
1）关联字段加索引，让MySQL做 join 操作时尽量选择NLJ算法
2）小表驱动大表，关联查询时，如果明确知道哪张表是小表可以用 straight_join 写法固定连接驱动方式，省去MySQL优化器自己判断的时间
straight_join 解释：
straight_join 功能同 join 类似，但能让左边的表来驱动右边的表，即修改表优化器对于联表查询的执行顺序
比如：[SQL] select * from t2 straight_join t1 on t2.a = t1.a; 代表指定 MySQL选 t2 表作为驱动表
straight_join 只适用于 inner join，并不适用于 left join，right join（因为left join，right join已经代表指定了表的执行顺序）
使用 straight_join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。
尽可能让优化器去判断，因为大部分情况下mysql优化器是比人要聪明的。
对于小表定义的明确：
在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤完成之后，计算参与 join 的各个字段的总数据量，数据量小的那个表就是“小表”，应该作为驱动表
5. in和exsits的优化
原则：
小表驱动大表，即小的数据集驱动大的数据集
in：
当B表的数据集小于A表的数据集时，in 优于 exists
将主查询B的数据，放到子查询A中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
它的执行逻辑如下所示：
[SQL] select * from A where id in (select id from B);
等价于
for(select id from B){
select * from A where A.id = B.id
}
select * from A where A.id = B.id
}
exists：
当A表的数据集小于B表的数据集时，exists 优于 in
将主查询A的数据，放到子查询B中做条件验证，根据验证结果（true或false）来决定主查询的数据是否保留
它的执行逻辑如下所示：
[SQL] 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
}
select * from B where B.id = A.id
}
exists 补充：
1）EXISTS (subquery) 只返回 TRUE 或 FALSE ，因此子查询中的 SELECT * 也可以用 SELECT 1 替换（官方说法是实际执行时会忽略SELECT清单），因此没有区别
2）EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比
3）EXISTS子查询往往也可以用 JOIN 来代替，何种最优需要具体问题具体分析
count(*) 查询优化
1. 四种 count 查询情况对比
如下所示：
注意：以上4条sql只有根据某个字段count不会统计字段为null值的数据行
查看执行计划：
四个SQL从实际执行结果可看出，执行计划一样，说明这四个SQL执行效率应该差不多
四个SQL从理论上比较执行效率：
字段有索引时：
count(*) ≈ count(1) > count(字段) > count(主键 id)
count(字段) > count(主键 id) ：
count(字段) 统计走二级索引
count(主键 id) 统计走主键索引
二级索引存储数据 比 主键索引 少
字段无索引时：
count(*) ≈ count(1) > count(主键 id) > count(字段)
count(主键 id) > count(字段)：
count(字段) 统计走不了索引
count(主键 id) 还可以走主键索引
count(1) > count(字段)：
count(1) 跟 count(字段) 执行过程类似
count(1) 不需要取出字段统计，就用常量1做统计
count(字段) 还需要取出字段
count(*) ≈ count(1)：
count(*) 是个例外
mysql并不会把全部字段取出来
而是专门做了优化，不取值，按行累加，效率很高
不需要用 count(列名) 或 count(常量) 来替代 count(*)
为什么对于 count(id) ，MySQL最终选择辅助索引而不是主键聚集索引？
因为二级索引相对主键索引存储数据更少，检索性能应该更高，MySQL内部做了点优化（应该是在5.7版本才优化）。
2. 常见优化方法
1、查询mysql自己维护的总行数
1）对于MyISAM存储引擎的表：
如下所示：
不带where条件的count查询性能很高
因为表的总行数会被MySQL存储在磁盘上，查询不需要计算
2）对于Innodb存储引擎的表：
查询count需要实时计算
MySQL不会存储表的总记录行数（因为有MVCC机制）
2、使用 show table status
如果只需要知道表总行数的估计值可以用，如下sql查询，性能很高
如下所示：（该值不准确，只适用于特定场景）
3、将总数维护到Redis里
插入或删除表数据行的时候，同时维护到 Redis 里的表总行数key的计数值(用incr或decr命令)，
但是这种方式可能不准，很难保证表和 Redis 操作的事务一致性
4、增加数据库计数表
插入或删除表数据行的时候，同时维护计数表，让他们在同一个事务里操作
阿里MySQL规范推荐解读
1. 单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表
2. 业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引
3. 超过三个表禁止 join。需要 join 的字段数据类型保持绝对一致；多表关联查询时，保证被关联的字段需要有索引。关联逻辑可放在java中处理，而且扩展性比较好。
4. 页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决
5. 不要使用count(列名)，不会统计null值，使用count(*)
6. 禁止使用存储过程
7. in 操作能避免就避免，控制在100个以内
8. 采用utf8 字符集（3kb），utf8mb4存储表情
9. 定长的字符用char，不定长的用varchar
10. 小数类型为 decimal，禁止使用 float 和 double
MySQL字段类型的选择
1. 选择正确的数据类型，对于性能至关重要。
一般应该遵循下面两步：
1）确定合适的大类型：数字、字符串、时间、二进制
2）确定具体的类型：有无符号、取值范围、变长定长等
在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量把字段定义为NOT NULL，避免使用NULL
2. 数值类型优化建议：
1）如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍
2）建议使用 TINYINT 代替 ENUM、BITENUM、SET
3）避免使用整数的显示宽度(参看笔记最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用INT
4）DECIMAL 最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意长度设置。
5）建议使用整形类型来运算和存储实数，方法是：实数乘以相应的倍数后再操作。
6）整数通常是最佳的数据类型，因为它速度快，并且能使用 AUTO_INCREMENT。
3. 日期和时间优化建议：
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将来的时间上限问题。
8）有些把Unix的时间戳保存为整数值，但是这通常没有任何好处，格式处理起来也不太方便，不推荐
4. 字符串优化建议：
1）字符串的长度相差较大用 VARCHAR；字符串短且所有值都接近一个长度用CHAR
2）CHAR 和 VARCHAR：
适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合
那些要用来计算的数字，不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题
3）尽量少用 BLOB 和 TEXT，如果实在要用可以考虑将 BLOB 和 TEXT 字段单独存一张表，用id关联
4）BLOB系列存储二进制字符串，与字符集无关；TEXT系列存储非二进制字符串，与字符集相关
5）BLOB 和 TEXT 都不能有默认值
补充：INT显示宽度
我们经常会使用命令来创建数据表，而且同时会指定一个长度，如下所示：
CREATE TABLE `user`(
`id` TINYINT(2) UNSIGNED
);
`id` TINYINT(2) UNSIGNED
);
但是，这里的长度并非是TINYINT类型存储的最大长度，而是显示的最大长度
这里user表的id字段的类型是 TINYINT，可以存储的最大数值是255
如果存入值小于等于255，如200，虽然超过（2）位，但是没有超出TINYINT类型长度，可以正常保存
如果存入值大于255，如500，那么MySQL会提示 Out of range value for column 'id'
这里TINYINT(2)中2的作用，是当需要在查询结果前填充0时，在建表语句中加上 ZEROFILL 就可以实现，如下：
CREATE TABLE `user` (
`id` TINYINT(2) UNSIGNED ZEROFILL
);
`id` TINYINT(2) UNSIGNED ZEROFILL
);
如果存入值是5，那么查询结果输出就是05
其实实际存储的值还是5，只是MySQL输出数据时在前面填充了0
在MySQL命令中，字段的类型长度TINYINT(2)、INT(11)不会影响数据的插入，只会在使用ZEROFILL时有用，让查询结果前填充0
0 条评论
下一页
