当前位置： 首页 > news >正文

4. MySQL 索引优化实战

news 2025/9/17 18:08:58
  • 批量创建10w条数据
START TRANSACTION;
DROP PROCEDURE IF EXISTS insert_10W;
DELIMITER ;;
CREATE PROCEDURE insert_10W()
begindeclare i int;SET i = 1;WHILE i <= 100000 DOINSERT INTO your_table (column1, column2) VALUES (CONCAT('value_', i), i);SET i = i + 1;END WHILE;
END;;
DELIMITER ;
CALL insert_10W();
COMMIT;
  • 开启trace
set session optimizer_trace="enabled=on",end_markers_in_json=on; -- 开启traceselect * from user where name like "name"; # 执行语句select * from information_schema.OPTIMIZER_TRACE; # 查看trace

4.1 索引下推荐优化详解

（1）当结果集的行数超过了全表的一半，则会使用全表扫描

# 背景：name是索引字段, 索引名为idx_name
explain select * from student where name > "zeta";  # 如果结果集数量大于全表一半则不走idx_name索引,否则走该索引

（2）强制使用索引

EXPLAIN SELECT * FROM student force index(idx_name) where name > "tom" and age = 22;

4.2 MySQL优化器索引选择探究

4.3 索引优化Order By与Group By

（1） Order By 优化

  • 根据排序字段建立合适的索引，多字段排序时，也要遵循最左前缀法则

  • 尽量使用覆盖索引

  • 多字段排序，一个升序一个降序，此时需要注意联合索引在创建时的规则

    CREATE INDEX idx_age_name on table_a(age asc, name desc);

  • 如果不可避免使用filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256kb)

（2） Group By 优化

4.4 Using filesort文件排序详解

官方的定义是，MySQL must do an extra pass to find out how to retrieve the rows in sorted order. The sort is done by going through all rows according to the join type and storing the sort key and pointer to the row for all rows that match the WHERE clause . The keys then are sorted and the rows are retrieved in sorted order。

MySQL需要额外的一次传递，以找出如何按排序顺序检索行。通过根据联接类型浏览所有行并为所有匹配WHERE子句的行保存排序关键字和行的指针来完成排序。然后关键字被排序，并按排序顺序检索行。标红，重点。

filesort 有两种排序方式

  1. 双路排序：对需要排序的记录生成 <sort_key,rowid> 的元数据进行排序，该元数据仅包含排序字段和rowid。排序完成后只有按字段排序的rowid，因此还需要通过rowid进行回表操作获取所需要的列的值，可能会导致大量的随机IO读消耗；
  2. 单路排序：对需要排序的记录生成 <sort_key,additional_fields> 的元数据，该元数据包含排序字段和需要返回的所有列。排序完后不需要回表，但是元数据要比第一种方法长得多，需要更多的空间用于排序。
# 配置文件示例 (my.cnf)
[mysqld]
sort_buffer_size = 2M  # 从1-4MB开始测试

filesort 使用的算法是QuickSort，即对需要排序的记录生成元数据进行分块排序，然后再使用mergesort方法合并块。其中filesort可以使用的内存空间大小为参数 sort_buffer_size 的值，默认为2M。当排序记录太多 sort_buffer_size 不够用时，mysql会使用临时文件来存放各个分块，然后各个分块排序后再多次合并分块最终全局完成排序。可以增大 sort_buffer_size 来解决 filesort 慢问题，也就是上面的第二种排序。

  • Using index:效率高,通过有序索引顺序扫描, 直接返回有序数据
  • Using filesort: 不通过索引顺序排序, 效率低

总结一句话，避免using filesort就是使用覆盖索引。

explain SELECT * FROM user ORDER BY create_date DESC limit 20.40; # 优化前
select * from (select id from user order by create_date DESC limit 20.40) a left join user b on a.id=b.id; # 优化后

4.5 索引设计原则与实战

（1）代码先行，索引后上

（2）联合索引尽量覆盖条件

（3）在区分度大的列上建立索引

（4）长字符串我们可以采用前缀索引

对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position) 。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。

（5）基于慢sql查询做优化

索引设计实战

以社交场景APP来举例，我们一般会去搜索一些好友，这里面就涉及到对用户信息的筛选，这里肯定就是对用户user表搜索了，这个表一般来说数据量会比较大，我们先不考虑分库分表的情况，比如，我们一般会筛选地区(省市)，性别，年龄，身高，爱好之类的，有的APP可能用户还有评分，比如用户的受欢迎程度评分，我们可能还会根据评分来排序等等。

  • 简单查询

对于后台程序来说除了过滤用户的各种条件，还需要分页之类的处理，可能会生成类似sql语句执行：select xx from user where xx=xx and xx=xx order by xx limit xx,xx 对于这种情况如何合理设计索引?

  • 联合索引设计

比如用户可能经常会根据省市优先筛选同城的用户，还有根据性别去筛选，那我们是否应该设计一个联合索引 (province,city,sex) ？

这些字段好像基数都不大，其实是应该的，因为这些字段查询太频繁了。

假设又有用户根据年龄范围去筛选了，比如 where province=xx and city=xx and age>=xx and age<=xx，我们尝试着把age字段加入联合索引 (province,city,sex,age)，注意，一般这种范围查找的条件都要放在最后，之前讲过联合索引范围之后条件的是不能用索引的，但是对于当前这种情况依然用不到age这个索引字段，因为用户没有筛选sex字段，那怎么优化了？

其实我们可以这么来优化下sql的写法：where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx对于爱好之类的字段也可以类似sex字段处理，所以可以把爱好字段也加入索引(province,city,sex,hobby,age)

  • 辅助索引

假设可能还有一个筛选条件，比如要筛选最近一周登录过的用户，一般大家肯定希望跟活跃用户交友了，这样能尽快收到反馈，对应后台sql可能是这样：where province=xx and city=xx and sex in ('female','male') and age>=xx and age<=xx andlatest_login_time>= xx 那我们是否能把 latest_login_time 字段也加入索引？

比如 (province,city,sex,hobby,age,latest_login_time) ，显然是不行的，那怎么来优化这种情况了？其实我们可以试着再设计一个字段is_login_in_latest_7_days，用户如果一周内有登录值就为1，否则为0，那么我们就可以把索引设计成 (province,city,sex,hobby,is_login_in_latest_7_days,age) 来满足上面那种场景了！

  • 总结

一般来说，通过这么一个多字段的索引是能够过滤掉绝大部分数据的，就保留小部分数据下来基于磁盘文件进行order by语句的排序，最后基于limit进行分页，那么一般性能还是比较高的。不过有时可能用户会这么来查询，就查下受欢迎度较高的女性，比如sql：where sex = 'female' orderby score limit xx,xx，那么上面那个索引是很难用上的，不能把太多的字段以及太多的值都用 in 语句拼接到sql里的，那怎么办了？其实我们可以再设计一个辅助的联合索引，比如 (sex,score)，这样就能满足查询要求了。以上就是给大家讲的一些索引设计的思路了，核心思想就是，尽量利用一两个复杂的多字段联合索引，抗下你80%以上的查询，然后用一两个辅助索引尽量抗下剩余的一些非典型查询，保证这种大数据量表的查询尽可能多的都能充分利用索引，这样就能保证你的查询速度和性能了！

4.6 分页查询优化详解

SELECT * FROM table_a limit 9000000,10;
# 这条sql会排序9000010条记录，但只返回10条数据。 排序代价非常大# 优化：覆盖索引+子查询
SELECT t.* from table_a t, (SELECT id FROM table_a LIMIT 9000000,10) ai where t.id = ai.id;

4.7 表JOIN关联原理详解及优化

4.8 表COUNT查询优化

# count(普通字段) < count(主键) < count(二级索引字段) < count(1) ≈ count(*) ## 效率比较

  • count(普通字段)
    没有not null约束:InnoD8 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为null，计数累加。
    有not null约束:InnoD8 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。

    不走索引

  • count(主键)
    InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul)。

    走主键索引

  • count(1)
    InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“”进去，直接按行进行累加。

  • count(*)
    InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。

4.9 阿里巴巴MYSQL规范解读

4.10 MYSQL数据类型选择分析

在MySQL中，选择正确的数据类型，对于性能至关重要。一般应该遵循下面两步：
（1）确定合适的大类型：数字、字符串、时间、二进制；
（2）确定具体的类型：有无符号、取值范围、变长定长等。

在MySQL数据类型设置方面，尽量用更小的数据类型，因为它们通常有更好的性能，花费更少的硬件资源。并且，尽量
把字段定义为NOT NULL，避免使用NULL。

数值类型优化建议：

  1. 如果整形数据没有负数，如ID号，建议指定为UNSIGNED无符号类型，容量可以扩大一倍。
  2. 建议使用TINYINT代替ENUM、BITENUM、SET。
  3. 避免使用整数的显示宽度(参看文档最后)，也就是说，不要用INT(10)类似的方法指定字段显示宽度，直接用
    INT。
  4. DECIMAL最适合保存准确度要求高，而且用于计算的数据，比如价格。但是在使用DECIMAL类型的时候，注意
    长度设置。
  5. 建议使用整形类型来运算和存储实数，方法是，实数乘以相应的倍数后再操作。
  6. 整数通常是最佳的数据类型，因为它速度快，并且能使用AUTO_INCREMENT。

日期和时间优化建议：

  1. MySQL能存储的最小时间粒度为秒。
  2. 建议用DATE数据类型来保存日期。MySQL中默认的日期格式是yyyy-mm-dd。
  3. 用MySQL的内建类型DATE、TIME、DATETIME来存储时间，而不是使用字符串。
  4. 当数据格式为TIMESTAMP和DATETIME时，可以用CURRENT_TIMESTAMP作为默认（MySQL5.6以后），
    MySQL会自动返回记录插入的确切时间。
  5. TIMESTAMP是UTC时间戳，与时区相关。
  6. DATETIME的存储格式是一个YYYYMMDD HH:MM:SS的整数，与时区无关，你存了什么，读出来就是什么。
  7. 除非有特殊需求，一般的公司建议使用TIMESTAMP，它比DATETIME更节约空间，但是像阿里这样的公司一般
    会用DATETIME，因为不用考虑TIMESTAMP将来的时间上限问题。
  8. 有时人们把Unix的时间戳保存为整数值，但是这通常没有任何好处，这种格式处理起来不太方便，我们并不推荐
    它。

字符串优化建议

  1. 字符串的长度相差较大用VARCHAR；字符串短，且所有值都接近一个长度用CHAR。
  2. CHAR和VARCHAR适用于包括人名、邮政编码、电话号码和不超过255个字符长度的任意字母数字组合。那些
    要用来计算的数字不要用VARCHAR类型保存，因为可能会导致一些与计算相关的问题。换句话说，可能影响到计
    算的准确性和完整性。
  3. 尽量少用BLOB和TEXT，如果实在要用可以考虑将BLOB和TEXT字段单独存一张表，用id关联。
  4. BLOB系列存储二进制字符串，与字符集无关。TEXT系列存储非二进制字符串，与字符集相关。
  5. BLOB和TEXT都不能有默认值。
http://www.wxhsa.cn/company.asp?id=6819

相关文章：

  • 算法课第一周作业
  • 线段树懒标记模板
  • 谁在我这位置遗留或丢失了一颗口罩爆珠（好像是桃子味）？
  • python小计划——学生管理系统
  • C++ 并发
  • UML 5章
  • 《微服务事务管理》 - 教程
  • python之socket udp服务器实现
  • kylin SP3安装mysql 8.4.5
  • Unity中是否可以禁用GC
  • 经典SQL语句大全
  • 开源软件图形库
  • IvorySQL 与 deepin 完成兼容性认证，共创开源生态新篇章
  • 在 Nginx 上搭建静态站点
  • 测试
  • 认真做膜你赛
  • 使用GitHub Dork快速发现漏洞：我的第一个Bugcrowd漏洞挖掘实战
  • kylin SP3安装mysql8.0.41
  • DIFY 项目中通过 Makefile 调用 Dockerfile 并采用 sudo make build-web 命令构建 web 镜像的方法和注意事项
  • 代码随想录算法训练营第一天 | 704. 二分查找、27. 移除元素、209.长度最小的子数组
  • 从 MLPerf Storage v2.0 看 AI 训练中的存储性能与扩展能力
  • Revit二次开发 钢筋生成API(二)
  • 创建会计凭证报错：FI/CO接口:待更新的不一致的FI/CO凭证标题数据（转）
  • Uri uri = new Uri(Path); 这行代码的作用
  • Qt函数方法传入参数未使用-警告warning错误error提示解决
  • mysql 性能监控，关键指标解析与优化案例剖析
  • 如何提取docker镜像用于NAS手动安装
  • 百家大型企业共同选择：2025年人力资源管理系统权威推荐榜单
  • 不修改环境变量的基础下使用Java
  • new 和make 切片和map