昨天 11:54 西北工业大学 Java 发布于江苏

关注

Java面试专题-MySQL篇优化05-08

5.覆盖索引、超大分页优化

知道什么叫覆盖索引嘛？

覆盖索引是指SELECT查询使用了索引，并且返回的列在该索引中已经全部能够找到。避免了回表查询，提高了性能。使用覆盖索引可以减少对主键索引的查询次数，提高查询效率。

例如：
- id为主键,默认是主键索引
- name字段为普通索引

select * from tb_user where id = 1 # 覆盖索引

select id, name from tb_user where name = 'Arm' # 覆盖索引

select id, name, gender from tb_user where name = 'Arm' # 非覆盖索引(需要回表査询)

！那可以这样理解，不需要回表查询就是覆盖索引

MySQL超大分页怎么处理？

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

我们一起来看看执行limit分页查询耗时对比:

mysql> select * from tb_sku limit 0, 10;
10 rows in set (0.00 sec)

mysql> select * from tb_sku limit 9000000, 10;
10 rows in set (11.05 sec)

因为，当在进行分页查询时，如果执行limit 9000000,10，此时需要MySQL排序前9000010记录，仅仅返回9000000-9000010的记录，其他记录丢弃，查询排序的代价非常大。

优化思路：

一般分页查询时,通过创建覆盖索引能够比较好地提高性能,可以通过覆盖索引加子查询形式进行优化

select *
from tb_sku t,
	(select id from tb_sku order by id limit 9000000,10) a
where t.id = a.id;

总结

知道什么叫覆盖索引嘛?

覆盖索引是指查询使用了索引，返回的列，必须在索引中全部能够找到
- 使用id查询，直接走聚集索引查询，一次索引扫描，直接返回数据，性能高。
- 如果返回的列中没有创建索引，有可能会触发回表查询，尽量避免使用select *
MySQL超大分页怎么处理？

问题：在数据量比较大时，limit分页查询，需要对数据进行排序，效率低

解决方案：覆盖索引+子查询
1. 先使用覆盖索引快速查询出目标数据的id（只查id，利用索引有序性）
2. 再用这些id查询完整数据

所以是这样的：

深入理解：两种方法的区别

1. 传统方法的回表（笨重的回表）

SELECT * FROM users ORDER BY create_time DESC LIMIT 1000000, 10;

-- MySQL内部执行：
1. 遍历索引找到 1,000,010 条记录的位置
2. 对这 1,000,010 条记录进行回表（读完整数据）
3. 排序这 1,000,010 条完整数据
4. 丢弃前 1,000,000 条，返回后10条

2. 优化方法

SELECT * FROM users WHERE id IN (
    SELECT id FROM users ORDER BY create_time DESC LIMIT 1000000, 10
);

-- MySQL内部执行：
1. 遍历索引找到 1,000,010 条记录，但只取id（不读完整数据）
2. 得到10个id：id1, id2, ..., id10
3. 用这10个id进行索引，读取完整数据
4. 返回结果

6.索引创建的原则

索引创建原则有哪些?
先陈述自己在实际的工作中是怎么用的
主键索引
唯一索引
根据业务创建的索引（复合索引）（一般说的索引创建原则说的就是复合索引）

原则：

针对于数据量较大,且查询比较频繁的表建立索引。 单表超过10万数据(增加用户体验)
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引。
尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。

前缀索引 = 用字符串的一部分建立的索引

尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。

联合索引，也叫复合索引或组合索引，是指由数据库表中多个列（字段）共同组成的一个索引。

特性	说明
本质	由多个列组成的单个索引结构
核心规则	最左前缀匹配原则
关键因素	列的顺序决定索引的可用性
主要优点	1. 支持复杂查询条件 2. 可实现覆盖索引，极大提升性能 3. 优化多列排序
主要缺点	1. 占用更多存储 2. 设计不当会导致失效
设计口诀	等值在前，范围在后；高频在前，排序照搬

例如：经典设计模式： 对于

SELECT * FROM orders WHERE user_id = 123 AND order_date > ‘2023-01-01’ ORDER BY order_date DESC;

一个优秀的联合索引是：(user_id, order_date)。

user_id 等值查询，快速定位到该用户的所有订单。
order_date 在索引中已排序，可以高效进行范围查询和排序。

要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。

总结

索引创建原则有哪些?（这四点至少要说出来）
1. 数据量较大,且查询比较频繁的表
2. 常作为查询条件、排序、分组的字段
3. 字段内容区分度高
4. 内容较长,使用前缀索引
5. 尽量联合索引
6. 要控制索引的数量
7. 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它

7.索引失效

什么情况下索引会失效？

索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题

(适当的思考一下，回想一下，更真实)

例如：给tb_seller创建联合索引，字段顺序：name,status, address 可以通过执行计划explain判断索引是否失效

1.违反最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引： alt 违反最左前缀法则，索引失效（没有name）： alt 如果符合最左法则，但是出现跳跃某一列，只有最左列索引生效（只命中了name索引）： alt 2.范围查询右边的列，不能使用索引 alt 根据前面的两个字段name，status查询时走索引的，但是最后一个条件address没有用到索引。

走索引 指的是数据库在执行查询时，使用索引来快速定位和检索数据，而不是扫描整个表。这类似于使用书的目录查找内容，而不是一页一页翻遍全书。

3.不要在索引列上进行运算操作，索引将失效。 alt 4.字符串不加单引号（自动进行类型转换），造成索引失效。 alt 由于在查询的是，没有对字符串加单引号，MySQL的查询优化器，会自动地进行类型转换，造成索引失效

5.以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。 alt

总结

什么情况下索引会失效?

违反最左前缀法则
范围查询右边的列，不能使用索引
不要在索引列上进行运算操作，索引将失效
字符串不加单引号，造成索引失效。(类型转换)
以%开头的Like模糊查询，索引失效

8.sql优化经验

谈谈你对sql的优化的经验
- 表的设计优化
- 索引优化（参考优化创建原则和索引失效）
- SQL语句优化
- 主从复制、读写分离
- 分库分表（后面有专门章节介绍）

（一）表的设计优化（参考阿里开发手册《嵩山版》）

比如设置合适的数值（tinyint int bigint），要根据实际情况选择
比如设置合适的字符串类型（char和varchar）char定长效率高，varchar可变长度，效率稍低

（二）SQL语句优化

SELECT语句务必指明字段名称（避免直接使用select *)（这样可以尽量使用覆盖索引，大概率避免回表查询）
SQL语句要避免造成索引失效的写法
尽量用union all代替union，union会多一次过滤，效率低（union会去重）
避免在where子句中对字段进行表达式操作
Join优化能用innerjoin就不用left join right join，如必须使用一定要以小表为驱动（小表放外面，连接数据库次数少，性能好）,

内连接会对两个表进行优化，优先把小表放到外边,把大表放到里边。left join或right join，不会重新调整顺序