文章目录
总览
- 优化慢查询
1. 慢查询的怀疑方向
2. 覆盖索引
3. 索引基数
4. count()函数
5. ORDER BY
6. RAND()函数
7. 索引下推
8. 松散索引扫描
9. 字段函数操作
- 原则是不动索引字段,去动别的字段
10. 分页查询
1. Sakila-db的安装使用
1.1 Sakila-db的认识
1.2 下载与安装
官网下载地址
wget
命令下载
tar -xvf
解压
执行sakila-schema.sql
建表语句和导入data数据
source /root/sakila-db/sakila-schema.sql
source /root/sakila-db/sakila-data.sql
用MySQL Workbench 打开sakila.mwb
文件
2. where 查询太慢怎么办
2.1 覆盖索引
- 因此,如果不是业务需要,尽量不适用select *
- 增加联合索引,也有一定代价,即磁盘空间的占用变大
- 复制数据表,剔除联合索引,进行性能比较
1. 查看inventory
建表语句
show create table inventory;
2. 复制建表语句-剔除联合索引和外键
- 复制建表语句
CREATE TABLE `inventory1` (
`inventory_id` mediumint unsigned NOT NULL AUTO_INCREMENT,
`film_id` smallint unsigned NOT NULL,
`store_id` tinyint unsigned NOT NULL,
`last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`inventory_id`),
KEY `idx_fk_film_id` (`film_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4582 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
- 将之前的表的数据到新的表里
insert into inventory_1 select * from inventory;
3. 查询语句-进行测试
- 用
explain
查看执行计划
- 对于删除联合索引的数据表
inventory_1
没有走任何索引,进行了全局扫描
- 查询原表,走了
index
索引查询
3. MySQL使用索引规则以及如何重新走正确的索引
3.1 MySQL选取索引规则
测试:
- 创建一个单字段表,将原表数据导入5次
- 将新表的数据打乱
update city_1 set city =
(select city from city order by rand() limit 1);
- 增加前缀索引
- 会根据区分度去使用索引,区分度越大,且使用空间不大的索引
3.2 强制使用索引
3.3 优化索引
4. 优化COUNT查询语句
4.1 count()函数认识
4.2 测试count函数性能差异
1. 使用非索引字段
- 走了全表扫描
- 将全表扫描所有行,储存引擎将行中的指定字段
first_name
解析出来,交给Server层,进行逐个判空处理
2. 使用普通索引字段和主键索引字段
- 可以使用覆盖索引,因此只需要查询辅助索引,无需回表,也无需解析字段
- 但是辅助索引可以为null, 故Server层还是需要每次判断字段是否为null
- 即使使用主键索引,MySQL没有对主键索引优化,储存引擎还是会将所有含有主键的索引交给Server层,Server层还是会逐一判断字段是否为null
count(1)
3. count(*)
- MySQl真正对count(*)做了优化
- 直接在B+树中,查询到数据个数,然后直接返回,无需提交给server层进行逐个字段判断处理,因此,理论上效率是最高的
5. 优化ORDER BY 语句
- 如果能够索引覆盖,效率是最高的
- 不能索引覆盖,从两个角度考虑调优:
- 1是中间表的阀值,根据需要,如果调大一些,多走内存
- 2是中间表行的字段,根据需要,如果调大一些,就不会先排序两个字段,再回表填充数据
5.1 order by 步骤原理
5.2 优化策略
1. 条件查询
2. 中间结果集处理
- 因此,若内存空间足够,可以调节
sort_buffer_size
中间表在内存中的阀值
3. 回表生成完整结果集
- 当大于一个阀值时,MySQL会优化中间表,将只生成两个字段的中间表,但是需要回表填充最终结果
- 阀值过大,中间表过大,效率不一定最好,因为,1是会大于内存中的阀值,会落盘到硬盘,效率降低。2是数量过多,排序性能受影响。
4. 最高效-索引覆盖
- 即筛选字段、排序字段、输出字段都在一个联合索引中,可以使用索引覆盖,跳过中间表,直接输出最终筛选和排序的结果
6. random随机函数作用及性能优化
6.1 RAND()原理
- 该函数生成0~1随机的数
1. 创建第一个临时表
2. 将原表行记录转为临时表的所有行记录
- 最终将原表的所有行数据-转为临时表的所有行记录
3. 针对临时表,创建sort_buffer
第二张临时表
- 临时表若在内存里,找行位置字段,如果在硬盘里,找临时表的隐藏主键
4. 对sort_buffer
排序,取出第一个行位置,查询临时表
6.2 为什么会慢
6.3 优化策略
1. 临时方案
- 也有可能找不到,概率比较小
- sql语句比较复杂,真实业务中,排除和修补bug会有麻烦
2. 业务方案
7. 带头大哥丢了,怎么办
- 即联合索引的左侧原则,不能走索引,只能走全局扫描的两种特殊场景的优化
7.1 索引下推
- 不能走联合索引,主要排序字段,只是一个范围,第二个字段是无序的,需要回表,如上例,需要回四次表
- MySQl5.6以后优化,直接在索引中查找,无需扫描多行记录
7.2 松散索引扫描
- 首先扫描联合索引的每个带头大哥,比如
store_id
,然后第二个索引就有序了,再扫描查询第二个索引film_id
,无需遍历每个索引,当索引值大于要查询的字段时,直接跳过,因此,会减少很多次查询
8. 明明有索引,就是不走,怎么办
8.1 问题产生
- 因为字段加了函数后,破坏了字段原有的顺序,因此,不能再使用字段的索引,而是找其他索引,如果找不到,就走全局扫描
8.3 不走字段索引的其他例子
1. 字符串与数字转换
2. 隐式字符编码转换
9. 分页查询优化
- 即首先尝试能否是否排序字段、查询字段的联合索引,进行索引覆盖
- 如果不能使用索引覆盖,考虑使用排序字段索引查询指定的数据id,再用指定数据与原表联表查询,回表查询到指定id的其他字段
9.1 问题产生
- 先排序,再丢弃,性能浪费严重
9.2 优化策略