mysql常见索引的结构、数据类型及注意事项讲解

一、mysql索引主要有两种结构：b+tree索引和hash索引

hash索引

mysql中，只有memory(memory表只存在内存中，断电会消失，适用于临时表)存储引擎显示支持hash索引，是memory表的默认索引类型，尽管memory表也可以使用b+tree索引。hash索引把数据以hash形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为hash结构，每个键只对应一个值，而且是散列的方式分布。所以它并不支持范围查找和排序等功能。

b+tree索引

b+tree树是mysql使用最频繁的一个索引数据结构，是inodb和myisam存储引擎模式的索引类型。相对hash索引，b+tree在查找单条记录的速度比不上hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对进行单条记录的操作。

带顺序访问指针的b+tree

b+tree所有索引数据都在叶子节点上，并且增加了顺序访问指针，每个叶子节点都有指向相邻叶子节点的指针。

这样做是为了提高区间效率，例如查询key为从18到49的所有数据记录，当找到18后，只要顺着节点和指针顺序遍历就可以以此向访问到所有数据节点，极大提高了区间查询效率。

大大减少磁盘i/o读取

数据库的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点需要一次i/o就可以完全载入。

二、选择索引的数据类型

mysql支持很多数据类型，选择合适的数据类型存储数据对性能有很大的影响。

(1)越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和cpu缓存中都需要更少的空间，处理起来更快。

(2)简单的数据类型更好：整形数据比起字符，处理开销更小，因为字符串的比较更复杂。在mysql中，应用内置的日期和时间数据类型，而不是字符串来存储时间；以及用整形数据存储ip地址。

(3)尽量避免null：应该制定列为not null，除非你想存储null。在mysql中，含有空值的列很难进行查询优化，因为他们使得索引、索引的统计信息以及比较运算更加复杂。

三、mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引

1，index（普通索引）：alter table 'table_name' add index index_name('col')

最基本的索引，没有任何限制

2，unique（唯一索引）：alter table 'table_name' add unique('col')

与“普通索引”类似，不同的就是：索引列的值必须唯一，但允许有空值。

3，primary key（主键索引）：alter table 'table_name' add primary key('col')

是一种特殊的唯一索引，不允许有空值。

4，fulltext（全文索引）：alter table 'table_name' add fulltext('col')

仅可用于myisam和inodb，针对较大的数据，生成全文索引很耗时耗空间

5，组合索引：alter table 'table_name' add index index_name('col1','col2','col3')

为了更多的提高mysql效率可建立组合索引，遵循“最左前缀”原则。创建复合索引应该将最常用（频率）做限制条件的列放在最左边，一次递减。组合索引最左字段用in是可以用到索引的。相当于建立了col1,col1col2,col1col2col3三个索引

四，索引使用注意事项

1，不要滥用索引

①，索引提高查询速度，却会降低更新表的速度，因为更新表时，mysql不仅要更新数据，保存数据，还要更新索引，保存索引

②，索引会占用磁盘空间

2，索引不会包含含有null值的列

复合索引只要有一列含有null值,那么这一列对于此符合索引就是无效的，因此我们在设计时不要让字段的默认值为null。

3，mysql查询只是用一个索引

如果where字句中使用了索引的话，那么order by中的列是不会使用索引的

4，like

like '%aaa%'不会使用索引而like "aaa%"可以使用索引