数据库的索引理解

数据库的数据存储是在磁盘上的，在进行数据查询的时候，需要不断寻找磁盘的地址，所以索引就像是一个目录记录数据存在的位置。有顺序的数据结构。

索引有四种索引类型：

普通索引：大部分情况都可以用,最基本的索引，没有任何限制

唯一索引：索引列的值必须是唯一的，但允许有空值，如果是组合索引，则列值的组合必须唯一，如果字段不允许重复，比如手机号，用户id，做索引时，可以设置为unique；

主键自动设置的唯一索引，因为不可重复。

全文索引：检索长文章时候效果比较好，比如搜一篇文章。文章标题这种一般还是用普通索引。

单列索引、多列索引：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MYSQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。

组合索引（最左前缀）：平时用SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySql的效率，就要考虑建立组合索引。

索引失效：

在索引列上做运算，索引列存在隐式转化时，在索引列不使用等于号或者not查询时（MySQL会判断不走索引比较合适）

索引有两种数据结构：

1.B+树结构：底层是多路平衡查找树，每次查询都是从根节点开始，查找到叶子节点，就得到查找的键值，根据查询判断是否需要回表查询数据。

2.Hash索引：底层是hash表，进行查找的时候，调用一次hash函数，可以获取到对应的键值，之后进行回表查询获得实际的数据。

两种数据结构的差异：

Hash索引进行等值查询更快，无法进行范围查找，因为经过hash索引建立索引后，索引顺序和原顺序无法保持一致，不能支持范围查询。但是不稳定，性能不可预测，某个键值大量重复时，发生哈希冲突，此时效率可能极差。

B+树比较稳定，所有查询都是从根节点到叶子节点，且树的高度较低，一般都是用B+树，而不适用hash索引。

hash不支持使用索引进行排序，原理也是因为节点的顺序性。

hash索引不支持模糊查询和多列索引最左前缀匹配，因为哈希函数是不可预测的。

hash索引任何情况下都不可避免回表查询数据。

B+树在符合聚族索引、索引覆盖等，可以只通过索引完成查询。

B+树所有节点都遵循左节点小于父节点，右节点大于父节点，和多叉树类似，天然支持范围。

MySQL数据库索引的优化

过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

1. 何时使用聚集索引或非聚集索引？

动作描述 使用聚集索引 使用非聚集索引
列经常被分组排序 使用 使用
返回某范围内的数据 使用 不使用
一个或极少不同值 不使用 不使用
小数目的不同值 使用 不使用
大数目的不同值 不使用 使用
频繁更新的列 不使用 使用
外键列 使用 使用
主键列 使用 使用
频繁修改索引列 不使用 使用

2. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

3. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

4. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

5. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

6. 不要在列上进行运算

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<’2007-01-01′。关于这一点可以围观：一个单引号引发的MYSQL性能损失。

最后总结一下，MySQL只对一下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如刚才针对text类型的字段创建索引的时候，系统差点就卡死了。