mysql索引详解

索引可以提高查询效率，将随机I/O变为顺序I/O，mysql的索引采用的是B+Tree索引数据结构，那么该如何创建索引？
一、创建索引语句

ALTER TABLE table_name ADD INDEX index_name (column_list)

ALTER TABLE table_name ADD UNIQUE (column_list)

ALTER TABLE table_name ADD PRIMARY KEY (column_list)

二、索引的有效查询
1、全值匹配指的是和索引中的所有列进行匹配，与where查询条件的顺序无关。

2、最左前缀匹配：优先匹配最左列，同时也与索引列顺序无关。

3、匹配列前缀：匹配列值得开头部分。如like ‘%a’。

注意：
a、即使where的查询条件是最左索引列，也无法使用索引查询后缀如‘a%’。

b、如果where查询条件中有某个列的范围查询，则其右边的所有列都无法使用索引优化查询

c、索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。

三、索引前缀
对于较长的字符列，可以采用哈希索引，或者索引前缀，只对列的开头一部分字符进行索引。
索引前缀的创建：
ALTER TABLE table_name ADD INDEX index_name (index_column(length));

注意：MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。

四、聚集索引与非聚集索引
1、聚集索引
聚集索引决定数据在物理磁盘上的物理排序，一个表只能有一个聚集索引，mysql一般会默认将主键作为聚集索引，如果没有主键，innodb会选择唯一非空的一列作为聚集索引，如果没有唯一的非空索引，InnoDB会隐式定义一个主键来作为聚集索引。

如何理解：索引中的数据物理存放地址和索引的顺序是一致的，可以这么理解：只要是索引是连续的，那么数据在存储介质上的存储位置也是连续的。
比方说：想要到字典上查找一个字，我们可以根据字典前面的拼音找到该字，注意拼音的排列时有顺序的。
2、非聚集索引
非聚集索引与聚集索引相反，索引的逻辑顺序与磁盘上的物理存储顺序不同。即记录的逻辑顺序和实际存储的物理顺序没有任何联系。

如何理解：索引的记录节点有一个数据指针指向真正的数据存储位置。非聚集索引就像根据偏旁部首查字典一样，字典前面的目录在逻辑上也是连续的，但是查两个偏旁在目录上挨着的字时，字典中的字却很不可能是挨着的。

3、覆盖索引
如果一个索引包含所有要查询的字段则成为覆盖索引，其可以极大的提升查询效率。

4、使用索引排序
使用索引排序则ORDER BY子句后的列顺序要与组合索引的列顺序一致，且所有排序列的排序方向（正序/倒序）需一致，同时须满足覆盖索引。

注意：多表连接查询时，只有当ORDER BY后的排序字段都是第一个表中的索引列（需要满足以上索引排序的两个规则）时，方可使用索引排序。