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分页查询，需要对数据进行排序，效率低
解决方案：覆盖索引+子查询
- 先使用覆盖索引快速查询出目标数据的id（只查id，利用索引有序性）
- 再用这些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值时,它可以更好地确定哪个索引最有效地用于查询。
总结
- 索引创建原则有哪些?（这四点至少要说出来）
- 数据量较大,且查询比较频繁的表
- 常作为查询条件、排序、分组的字段
- 字段内容区分度高
- 内容较长,使用前缀索引
- 尽量联合索引
- 要控制索引的数量
- 如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它
7.索引失效
-
什么情况下索引会失效？
索引失效的情况有很多，可以说一些自己遇到过的，不要张口就得得得说一堆背诵好的面试题
(适当的思考一下，回想一下，更真实)
例如：给tb_seller创建联合索引，字段顺序：name,status, address
可以通过执行计划explain判断索引是否失效
1.违反最左前缀法则
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过索引中的列。匹配最左前缀法则，走索引：
走索引 指的是数据库在执行查询时，使用索引来快速定位和检索数据，而不是扫描整个表。这类似于使用书的目录查找内容，而不是一页一页翻遍全书。
3.不要在索引列上进行运算操作，索引将失效。
5.以%开头的like模糊查询，索引失效。如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
总结
- 什么情况下索引会失效?
- 违反最左前缀法则
- 范围查询右边的列，不能使用索引
- 不要在索引列上进行运算操作，索引将失效
- 字符串不加单引号，造成索引失效。(类型转换)
- 以%开头的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，不会重新调整顺序
（三）主从复制、读写分离
如果数据库的使用场景读的操作比较多的时候，为了避免写的操作所造成的性能影响，可以采用读写分离的架构。
读写分离解决的是，数据库的写入，影响了查询的效率。
开始看黑马Java面试，27届的，大家有什么建议欢迎来说哟
查看9道真题和解析