mysql 索引

索引是数据结构

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的。MySQL存储引擎分为：InnoDB 和 MyISAM

• MyISAM引擎使用B+Tree作为索引结构，叶节点data域存放数据记录的地址。
• MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
• MyISAM中，主索引和辅索引在结构上没有任何区别，只是主索引要求key是唯一的，而辅索引的key可以重复。
• MyISAM的索引方式也叫做“非聚簇”的，之所以这么称呼是为了与InnoDB的聚簇索引区分。

• InnoDB也使用B+Tree作为索引结构，叶节点包含了完整的数据记录。这种索引叫做聚簇索引。
• InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
• InnoDB的辅索引data域存储相应记录主键的值而不是地址。

聚簇索引的优点

1. 聚簇索引将索引和数据行保存在同一个B-Tree中，查询通过聚簇索引可以直接获取数据，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率要高。
2. 聚簇索引对于范围查询的效率很高，因为其数据是按照大小排列的。

聚簇索引的缺点

1. 聚簇索引的更新代价比较高，如果更新了行的聚簇索引列，就需要将数据移动到相应的位置。这可能因为要插入的页已满而导致“页分裂”。
2. 插入速度严重依赖于插入顺序，按照主键进行插入的速度是加载数据到Innodb中的最快方式。如果不是按照主键插入，最好在加载完成后使用OPTIMIZE TABLE命令重新组织一下表。
3. 聚簇索引在插入新行和更新主键时，可能导致“页分裂”问题。
4. 聚簇索引可能导致全表扫描速度变慢，因为可能需要加载物理上相隔较远的页到内存中（需要耗时的磁盘寻道操作）。

非聚簇索引

非聚簇索引，又叫二级索引。二级索引的叶子节点中保存的不是指向行的物理指针，而是行的主键值。当通过二级索引查找行，存储引擎需要在二级索引中找到相应的叶子节点，获得行的主键值，然后使用主键去聚簇索引中查找数据行，这需要两次B-Tree查找。

 

索引使用策略及优化

(1) 定义有主键的列一定要建立索引。因为主键可以加速定位到表中的某行

(2) 定义有外键的列一定要建立索引。外键列通常用于表与表之间的连接，在其上创建索引可以加快表间的连接

(3) 对于经常查询的数据列最好建立索引。

• 对于需要在指定范围内快速或频繁查询的数据列，因为索引已经排序，其指定的范围是连续的，查询可以利用索引的排序，加快查询的时间
• 经常用在 where子句中的数据列，将索引建立在where子句的集合过程中，对于需要加速或频繁检索的数据列，可以让这些经常参与查询的数据列按照索引的排序进行查询，加快查询的时间。

 

最左前缀原理

• 最左前缀原则就是先要看第一列，在第一列满足的条件下再看左边第二列，以此类推。
• 在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。
• 当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+树是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

 

B-Tree 索引的特点

B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。

Hash 索引的特点
 

Hash 索引只能够用于使用 = 或者 <=> 运算符的相等比较(但是速度更快)，不能够用于诸如 < 等用于查找一个范围值的比较运算符。