Mysql索引设计和使用

1 概述

索引就是对某些字段提前排序，生成特定的数据结构（例如B+树或者哈希），然后加快查询。

2 设计原则

• 要在过滤字段上创建索引。即where后出现的字段上创建索引。
• 要在取值范围广的字段创建索引。比如用性别字段创建索引，那意义就不大。
• 尽量使用短字段做索引。首先是节省空间，其次是IO较少，比较更快。
• 主键尽量使用自增、整型字段。因为普通索引会保存主键，太长的主键会占用空间。
• 创建联合索引时，利用最左字段查询。创建联合索引后，where后一定要使用上最左边的一个字段，否则根本使用不上索引。

3 设计误区

• 每个表都要有索引。有的公司DBA，不管三七二十一，凡是创建的表都要有索引，否则不给审核，这明显谨慎过度，也能理解。对于数据量不大的表，也没有必须创建索引，反而会占用一定的空间。但是DBA会反驳说：既然数据量不大，创建索引也没有坏的影响。无可反驳。
• 不要过度索引。索引当然要有，但是不可太多。因为数据更新的时候，同样会更新索引。太多的索引会影响数据更新的效率。另外，也会占用空间。

4 索引方法

包括2种： btree索引和hash索引

btree索引很常见，优势在于区间查询，适合的查询方式 >，>=，<，=<。

hash索引，顾名思义，底层是hash结构，查询单个值，常数级时间复杂度，不善于区间查询，适合的查询方式 = ，in。

实际中，似乎大部分情况用btree，如果某些字段不使用区间查询，可以用hash，比如某个字段是url。

5 索引类型

• FULLTEXT 全文索引，用于模糊查询，对长字段做匹配。
• NORMAL 普通索引，任意普通字段都可以创建。
• SPATIAL 空间索引，这个暂时没有用过，不知道是什么东西。
• UNIQUE 唯一索引，比普通索引多了一个唯一性约束，若是联合索引，字段组合起来必须唯一。

6 参考资料

• 《深入浅出MYSQL：数据库开发、优化与管理维护》（第三版）