博客迁移：http://cui.zhbor.com/article/14.html
MySQL索引，有很多很多的东西需要去学习，我会写一些自己的总结，这些总结主要是平时运用在实际项目中的，有很多的经验往往设计表的人很清楚，但是总是有：‘这个东西就在哪里哪里用一下，加不加都行，设计成这样也没关系。’诸如此类的想法，这样是很不负责任的。关于索引方面理论的东西也很多很深奥，我也有很多不懂的地方，所以我不会去写很多理论的东西，以免误导大家。
一、首先谈一下什么是索引
官方描述：数据库系统维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
MySQL普遍使用B+Tree实现其索引结构，你也不用纠结B+Tree是什么，其实它就像字典的目录一样。
二、索引解决了哪些问题
下面根据一个表来做些实验：
索引是一个单独的文件，是与数据文件分离的，具有更好的数据结构，而且文件更小。
索引的作用就是可以快速定位、快速知道从上读还是从下读的、排好顺序的列表。比如上面的表，要获取性别为男二班的的数据，那么建立sex+class的联合索引scindex，就能迅速的从索引文件中获取数据地址，再从数据文件中获取内容，如下。
|
1
|
explain SELECT * FROM `stu_test` where sex='男' and class='二班'; |
其实呢，索引也是可以存储内容的，不需要访问数据文件，速度非常非常快，这就是‘覆盖索引(Covering Index)’，在输出的EXtra信息中输出的是Using index，就会用到覆盖索引（前提是你select字段要在索引中）。比如你所查询的数据：
在explain中有一个字段rows（相对应慢查询日志中是Rows_examined），就是你sql语句所扫描的行数，添加索引的目标就是为了降低这个行数。
注意：一次检索只能用一个索引，如果所用的不是你所期望的索引，那么你可以用 force index来使sql语句强制使用你指定的索引。
总结：索引可以快速定位到内容、快速完成排序、甚至不需要读取数据文件就可以返回数据。
三、需要注意的一些问题
1、核心的sql语句务必添加索引，状态类型字段最好不要添加索引，我在上一篇中有详细说明为什么。
2、字符字段的索引如果要加索引则考虑前缀索引。
MySQL 前缀索引能有效减小索引文件的大小，提高索引的速度。但是前缀索引也有它的坏处：MySQL 不能在 ORDER BY 或 GROUP BY 中使用前缀索引，也不能把它们用作覆盖索引(Covering Index)。语法：
ALTER TABLE table_name ADD KEY(column_name(prefix_length));
prefix_length这个值标志着区分多少条记录，比如这个值为5 那么就可以区分26x26x26x26x26条数据
3、不要在sql语句中做运算，运算要放在程序中进行。
4、不要用字符串做主键
我的数据库是MariaDB，它的默认引擎是InnoDB，如果你的表中有主键索引，InnoDB会为主键建立聚簇索引（注意聚簇索引不是在所有引擎都支持的，现在的是InnoDB支持）。如果表中没有主键索引，InnoDB也会定义一个隐藏的主键对其建立聚簇索引，一个表只能有一个聚簇索引。至于为什么不用字符串做主键，我找了一些理由：
通常情况下，DBMS（数据库管理系统）会在主键上建立聚集索引，由于一般使用B-Tree的数据结构来存储索引数据，所以一般对主键有以下两个要求：
-
越短越好——越短在一个Page中存储的节点越多，检索速度就越快。
-
顺序增长——如果每一条插入的数据的主键都比前面的主键大，那么B-Tree上的节点也是顺序增长的，不会造成频繁的B-Tree分割。
越短越好是为了查询的速度快，顺序增长是为了插入速度快。
有了这两个要求，我们再来分析下各个数据类型：
字符类型：基本不满足前面提到的2点要求，字符类型一般不会很短，而且也很可能不是顺序增长的，所以不是特别推荐的主键类型。当然如果确实业务需求使用字符类型，那么也尽量使用char(XX)而不要使用varchar(XX)。
数字类型：不需要多介绍