MySQL索引-索引优化

索引优化

应该建立索引的情况

• 数据量大
• 字段差异大/唯一性
• 表（字段）的查询频率远大于增删改频率
• 经常用于查询的字段
• 经常用于过滤的字段
• 经常用于分组的字段
• 经常用于排序的字段
• 经常用于连接的字段

不应该建立索引的情况

• 数据量小
• 字段差异性小（反例：明显数据倾斜）
• 频繁更新的字段
• 不经常用于xx的字段

覆盖索引优化

• 覆盖索引
  MySQL的覆盖索引(Covering Index)是指二级索引中包含了查询所需的所有字段，从而使查询可以仅 通过访问二级索引而不需要访问实际的表数据(主键索引)。

• 回表查询
  回表是指在使用二级索引(非聚簇索引)作为条件进行查询时，由于二级索引中只存储了索引字段的值和对应 的主键值，无法得到其它数据。如果要查询数据行中的其它数据，需要根据主键去聚簇索引查找实际的数据 行，这个过程被称为回表。

• 索引下推
  是一种减少回表查询，提高查询效率的技术。它允许MySQL在使用索引查找数据时，将部分查询条件下推到 存储引擎层过滤，从而减少需要从表中读取的数据行，减少了I0(本该由Server层做操作，交由存储引擎层因 此叫做“下推”)。

前缀索引优化

• 长字段
• 明显的前缀检索
• order by无法使用
• 覆盖索引无法使用

主键索引优化

• 主键索引最好是自增的

排序优化

• index
  使用index是指利用索引自动实现排序

• filesort
  filesort是先把结果查出，然后在缓存或磁盘进行排序操作

  • 内存排序

    • 单路排序
      将所有数据加载到内存中进行排序

    • 双路排序
      先将排序字段和row_id进行排序，然后根据row_id加载其他字段

  • 磁盘排序

    • 归并排序
      分块的外部排序

防止索引失效

• 联合索引最左前缀匹配原则
• like ‘%xxx’ ‘%xxx%’ 开头
• 对索引进行计算，函数，类型转换
• OR

慢查询优化

1. 开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如 SHOW VARIABLES LIKE ‘slow_query_log%’ ;

2. 打开慢查询日志

SET global slow_query_log = ON; 
SET global slow_query_log_file = '主机名-slow.log'; 
SET global log_queries_not_using_indexes = ON; 
SET long_query_time = 10;

3. 查看慢查询日志

• time：日志记录的时间
• User@Host：执行的用户及主机
• Query_time：执行的时间
• Lock_time：锁表时间
• Rows_sent：发送给请求方的记录数，结果数量
• Rows_examined：语句扫描的记录条数
• SET timestamp：语句执行的时间点
• select…：执行的具体的SQL语句

4. 慢查询优化

• 判断是否存在慢查询：查看慢查询日志
• 判断是否使用了索引：explain key字段
  • 用了索引不一定快：select * from user where id>0;
• 提高索引的过滤性
• 使用覆盖索引和索引下推，减少回表查询
• 索引优化的方式

深分页优化

1. 普通分页

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始；

第二个参数指定返回记录行的最大数目；

如果只给定一个参数，它表示返回最大的记录行数目；

2. 深分页

select * from user limit 10000,1; 
select * from user limit 10000,10; 
select * from user limit 10000,100; 
select * from user limit 10000,1000; 
select * from user limit 10000,10000;

select * from user limit 1,100; 
select * from user limit 10,100; 
select * from user limit 100,100; 
select * from user limit 1000,100; 
select * from user limit 10000,100;

3. 优化

• 覆盖索引优化

select * from user limit 10000,100; 
select id from user limit 10000,100;

• 子查询优化

select * from user limit 10000,100; 
select * from user where id>= (select id from user limit 10000,1) limit 100;

• 记录id

连续查找，每次返回最大的id，下次查找时 通过id>maxId

原文地址：https://blog.csdn.net/m0_45362454/article/details/144725045

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！