MYSQL_02Mysql索引优化实战
2023-04-11 16:44:46 16 举报AI智能生成
MySQL索引优化实战是一本关于如何提高MySQL数据库性能的书籍。本书详细介绍了如何使用索引来优化查询，包括创建和使用索引、分析查询性能、调整配置参数等。此外，本书还提供了大量实例和案例分析，帮助读者更好地理解和应用所学知识。总之，MySQL索引优化实战是一本实用性强、内容丰富的参考书籍，值得广大MySQL开发人员和管理员阅读。
作者其他创作
大纲/内容
索引下推
索引条件下推优化（Index Condition Pushdown (ICP) ）是MySQL5.6添加的，用于优化数据查询
索引下推的下推其实就是指将部分上层（服务层）负责的事情，交给了下层（引擎层）去处理。
不使用索引条件下推优化时存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件
适用条件
需要整表扫描的情况
比如：range, ref, eq_ref, ref_or_null
适用于InnoDB 引擎和 MyISAM 引擎的查询
对于InnDB引擎只适用于二级索引
因为InnDB的聚簇索引会将整行数据读到InnDB的缓冲区，这样一来索引条件下推的主要目的减少IO次数就失去了意义。
因为数据已经在内存中了，不再需要去读取了
因为数据已经在内存中了，不再需要去读取了
引用子查询的条件不能下推
调用存储过程的条件不能下推
存储引擎无法调用位于MySQL服务器中的存储过程
不使用索引条件下推
获取下一行，首先读取索引信息，然后根据索引将整行数据读取出来
然后通过where条件判断当前数据是否符合条件，符合返回数据
使用索引条件下推
获取下一行的索引信息
检查索引中存储的列信息是否符合索引条件，如果符合将整行数据读取出来，如果不符合跳过读取下一行
用剩余的判断条件，判断此行数据是否符合要求，符合要求返回数据
当使用explan进行分析时，如果使用了索引条件下推，Extra会显示Using index condition
trace工具
开启trace工具会影响mysql性能，所以只能临时分析sql使用，用完之后立即关闭
开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on;
SELECT * FROM information_schema.OPTIMIZER_TRACE
关键内容
join_preparation第一阶段：SQL准备阶段，格式化sql
join_optimization第二阶段：SQL优化阶段
condition_processing件处理
table_dependencies表依赖详情
rows_estimation预估表的访问成本
table_scan全表扫描情况
rows扫描行数
cost查询成本
potential_range_indexes查询可能使用的索引
analyzing_range_alternatives分析各个索引使用成本
ranges索引使用范围
rowid_ordered使用该索引获取的记录是否按照主键排序
index_only是否使用覆盖索引
rows索引扫描行数
cost索引使用成本
chosen是否选择该索引
considered_execution_plans
best_access_path最优访问路径
considered_access_paths最终选择的访问路径
"access_type": "scan"访问类型：为scan，全表扫描
"chosen": true确定选择
优化思路
MySQL支持两种方式的排序filesort和index
index效率高，filesort效率低
index是指MySQL扫描索引本身完成排序
尽量在索引列上完成排序，遵循索引建立时的最左前缀法则
group by先排序后分组，遵照索引创建顺序的最左前缀法则
order by的条件不在索引列上，就会产生Using filesort
order by满足两种情况会使用Using index
order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
Using filesort
filesort文件排序方式
单路排序
一次性取出满足条件行的所有字段，然后在sort buffer中进行排序
双路排序
首先根据相应的条件取出相应的排序字段和可以直接定位行数据的行 ID
在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段
通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式
字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式
字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模·式
索引设计原则
先分析代码中涉及到该表的sql，在进行索引分析
联合索引尽量覆盖条件
让每一个联合索引都尽量去包含sql语句里的where、order by、group by
还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则
不要在小基数字段上建立索引
例如，性别
长字符串我们可以采用前缀索引
字段类型较小的话，占用磁盘空间也会比较小，此时你在搜索的时候性能也会比较好一点
例如varchar(255)
针对这个字段的前20个字符建立索引(阿里规范)
where与order by冲突时优先where
基于慢sql查询做优化
针对mysql监控的慢sql查询做特定的索引优化
0 条评论
下一页
