MySQL优化学习 | 索引的优化策略

申阳
316 阅读3分钟

这是我参与8月更文挑战的第15天，活动详情查看：8月更文挑战

前言

索引的优点有很多，但是在使用的时候我们依然要谨慎，在这里整理一下，索引使用时候的注意点。

索引失效

什么情况下不会使用到索引?

  • 索引选择性太差
  • <>/not in 无法使用索引
  • is null 会使用索引，is not null 不会使用索引（当where出现not不会使用索引）
  • where子句跳过左侧索引列，直接查询右侧索引字段
  • 对索引列进行计算或者使用函数 什么是选择性？ 可以理解为数据的差异性，比如用户表中性别字段，按照常理来说性别无非男女，也就是说我们要精准查询，即使命中了索引，依然要扫描一半的数据量，在一半的数据中选中目标，这样选择性就很低了。但是相比于身份证号，一旦匹配索引，就可以直接锁定查询目标，可以视为选择性大。

使用索引优化排序

当Order By字段与索引字段顺序/排序方向相同时索引可以优化排序速度

说明：

  • 当排序出现了索引左侧列则允许使用索引排序。
  • 左侧字段单字段排序时，索引支持升降序。
  • 在多字段情况下，左侧字段必须是升序，且顺序不允许打乱。

删除冗余索引

  • pt-duplicate-key-caecher是percona-toolkit工具包中的实用组件（注意该工具没有windows版本）
  • 它可以帮助我们检查表中重复的索引或者主键 image.png

工具下载 image.png

image.png

查看索引使用情况

SELECT
	OBJECT_TYPE,
	OBJECT_SCHEMA,
	OBJECT_NAME,
	INDEX_NAME,
	COUNT_READ,
	COUNT_FETCH,
	COUNT_INSERT,
	COUNT_UPDATE,
	COUNT_DELETE 
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
	ORDER BY SUM_TIMER_WAIT DESC

image.png

  • 这里统计了数据库层面表中索引的使用情况，如果在使用一段时间后发现一些索引的统计值全是0,则证明这个索引从来没有被使用过，可以考虑删除这个索引
  • index_name 为Null的表示没有走索引，为全表扫描
  • ORDER BY SUM_TIMER_WAIT DESC查询出最耗时的索引使用情况

减少表与索引碎片

-- 使统计信息重算
analyze table 表名
-- 使表中的数据重新排列，索引结构重新组织，优化表空间，释放表空间
-- 注意：执行的时候会锁表，维护时执行。
optimize table 表名

拓展

Hash索引

  • Hash索引基于哈希表实现
  • 精确匹配索引所有列的查询才有效
  • Hash索引为每一条数据生成一个HasCode

Hash索引的特点

  • Hash索引只包含哈希值的行指针
  • 只支持精准匹配，不支持范围查询，模糊查询及排序
  • Hash取值速度非常快，但索引选择性很低时不建议使用
  • MySQL目前只有Memory显示支持Hash索引

InnoDB中的Hash索引

  • InnoDB存储引擎只支持显示创建BTree索引
  • 数据精准匹配时MySQL会自动生成HashCode，存入缓存

总结

索引的优点

  • 索引大幅度提升了数据的检索效率
  • 索引把随机IO,变成了顺序IO 索引的缺点
  • 降低了写入数据的效率
  • 太多的索引增加了查询优化器的选择时间，增加CPU的开销
  • 不合理的使用索引，会大幅度占用磁盘空间
avatar
文章
阅读
粉丝