本文是 MySQL(三)|千万级大数据查询优化第一篇: 创建高性能的索引的一个补充.
主要包括如下几点:
关于 sex 列创建索引的处理
sex 可以理解为那种选择性不高, 但是可能很多查询都会用到的列. 考虑到使用的频率, 还是建议在创建不同组合索引的时候将它作为前缀.
但是根据经验法则 (将选择性最高的列放到索引最前列) 不是说不应该在选择性低的列上创建索引吗? 那为什么这里要将 sex 列字段作为索引的前缀列? 这里有两个理由:
第一点, 几乎所有的查询都会用到它;
第二点, 索引中加上这一列也没有坏处, 即使查询没有使用 sex 列也可以通过一些 "诀窍" 绕过, 这个诀窍就是: 如果每个查询不限制性别, 那么可以通过在查询条件中新增
AND SEX IN('m', 'f')
来让 MySQL 选择该索引. 这样写并不会过滤任何行, 和没有这个条件时返回的结果相同. 但是必须加上这个列的条件, MySQL 才能够匹配索引的最左前缀.
避免创建冗余和重复索引
重复索引的概念很好理解, 我们也不会出现创建两个 idx_ab,idx_ab 的索引, 很多时候是在不经意间创建的. 关于冗余, 就得做一个说明啦. 举例如下, 我在新建一个表时, 要把 ID 设置为主键, 必须保证它是唯一的, 还要在它身上加上索引:
- create table test(
- ID INT NOT NULL PRIMARY KEY,
A INT NOT NULL,
B INT NOT NULL,
- UNIQUE(ID),
- INDEX(ID),
- )ENGINE-InnoDB;
因为 MySQL 的唯一限制和主键限制都是通过索引实现的, 所以实际上上面对 ID 创建了 3 个重复的索引.
如果创建了索引 idx_ab, 再创建索引 idx_a 就是冗余索引, 创建索引 idx_b 或 idx_ba 都不是冗余索引.(这个冗余只是对 B-Tree 索引来说的)
找出未使用的索引, 进行删除
除了冗余索引和重复索引, 可能还会有一些服务器永远不用的索引, 这种索引完全是累赘, 需要删除. 有很多工具可以帮助定位未使用的索引.
没有万金油般的索引, 也没有放之四海而皆准的经验法则
经常在网上听到一些经验法则, 包括我在上一篇文章中也提到过一些法则:
"在多列索引中将选择性最高的列放在第一列","应该为 where 子句中出现的所有列创建索引"... 所有的法则都只是在特定场景才有效果.
定期维护索引和表
维护表有三个主要的目的: 找到并修复损害的表, 维护准确的索引统计信息, 减少碎片.
对索引的优点做一个总结
索引的优点:
1)索引大大减少了服务器需要扫描的数据量.
2)索引可以帮助服务器避免排序和临时表.
3)索引可以将随机 I/O 变为顺序 I/O.
来源: http://www.jianshu.com/p/1da7a43a6bbe