前言
之前在做项目的时候, 接触到的千万级以上的表数据不是太多, 对于联合索引的认知不是太深刻, 用索引与不用索引以及索引的创建顺序和规则之前的区别不是太明显, 最近手头有优化查询千万级数据量的慢 sql 的任务, 优化前, 查询时间达到了 60 秒, 导致前端请求挂起, 做了相应的优化后, 查询千万级别数据时, 速度基本保持在零点几秒到两点几秒之间.
- sql
- SELECT
- *
- FROM
- table
- WHERE
- field1 = 'bf1adfe6_f71a_11e6_b131_6c92bf2bf639'
- AND field_time>= '2018-10-09 00:00:00'
- AND field_time <= '2018-10-15 23:59:59'
- AND field3 LIKE CONCAT('模糊查询字段', '%')
- ORDER BY
- field_time DESC
- LIMIT 0,
- 5;
- SELECT
- *
- FROM
- table
- WHERE
- field1 = 'bf1adfe6_f71a_11e6_b131_6c92bf2bf639'
- AND field_time>= '2018-10-09 00:00:00'
- AND field_time <= '2018-10-15 23:59:59'
- AND field3 LIKE CONCAT('模糊查询字段', '%')
- ORDER BY
- field_date DESC
- LIMIT 0,
- 5;
- SELECT
- *
- FROM
- table
- WHERE
- field1 = 'bf1adfe6_f71a_11e6_b131_6c92bf2bf639'
- ORDER BY
- field_date DESC
- LIMIT 0,
- 5;
情景描述
field1,field_time 是必填字段, field3 是非必填字段.
当查询的总条数少于 5 条时, 查询时间会达到 60 秒左右, 数据量有一千多万条. 原因是当查询总数少于 5 条时, 会扫描全表的数据.
分析
第一个 sql,field_time 范围查询字段和排序字段是一个字段, 也有对应的索引, 如果 field1 ,field_time 设置为单列索引, 则会根据 field1 通过索引过滤一部分数据, 在通过 field_time 对应的索引进行排序, 这会更多的消耗数据库内存, 把 field1 ,field_time 设置为联合索引, 则相当于建了 field1 索引,(field1 ,field_time)索引, 共两个索引, 然后再建一个 (field1 ,field_time,field3) 索引, 这样会大大提高查询效率, 测试结果也是如此, 提高 6 秒左右.
第二个 sql, 中排序的时间字段和范围查询时间字段不是同一个字段, 而这两个字段都有相应的索引, 这样会导致经过过滤查询出来的数据再次进行时间上的排序, 是比较消耗数据库内存, 影响查询效率的. 尽量考虑用同一个时间字段.
第三个 sql, 则没有太多的纠结了, 直接用 (field1 ,field_date) 索引就可以了, 根据最左前缀规则.
问题纠结
sql 查询时, 模糊查询中查询的数据量不同, 那么用到的索引也不尽相同, 公司的 DBA 工程师和从网上查询的结论是 MySQL 数据库通过内部自己判断最优使用索引, 从而来决定使用哪个索引, 但是它是否选择的正确也不一定, 所以有时候可以是用 force 来强制是用合适的索引, 但是不提倡使用, 如果相应的索引被 DBA 给删除了, 程序就会报错了. 这时候原本查询千万级别数据时, 有可能就会从毫秒级别上升为秒级别了, 比如前面说的有时候会达到一秒或两秒左右.
我问 DBA, 如果 field_time 有的为空, 会不会是索引不生效, DBA 说不会, 只是如果为空的情况下, 这个为空的数据不会被计算在内.
感触
索引如果用的比较好, 那么在操作千万级别甚至亿级别的数据时, 也是可以很快的. 当查询的数据量越多时, 用联合索引查询的速度会越快. 以后还得好好深入研究这方面的知识啊, 生命有限, 知识无涯啊!
来源: https://www.cnblogs.com/yanduanduan/p/9799008.html