mysql中索引原理（mysql每日一讲）

                                         mysql中的索引

 

索引的发展及原理
1、哈希索引：哈希是典型的KV，只要给出K，就能快速找到V
缺点：不适合找连续的范围，只能给一个K，返回一个V
2、有序数组索引：有序的话用二分法非常快，适合静态查询
3、二叉树索引：父节点比左边的大，比右边的小，类似于顺时针增大。时间复杂度小。
缺点：当数很大时，就需要保证这棵树是平衡二叉树，又由于树太高，这样占用不用的存储单元就多，造成在磁盘上查询时间长。
4、B+树索引：二叉树是两个叉，这是多个叉，保证层高低，从左到右依次增大，时间复杂度低。

索引不是数据库特有的，而是存储引擎特有的，InoDB默认的索引是B+数索引

主键索引和普通索引
主键索引
InnoDB中主键都是存储在B+数上的。假如表t，a是主键，执行 select * from t where a=100的时候，只用搜索1行数据，只用在B+树搜索

普通索引
假如表t,a是主键，b列是普通索引，执行 select * from t 
where b='北京'。这时候首先搜索b索引树，搜到b列=‘北京’的所有结果集，然后知道这行数据的a列，然后用对应的a列值在B+树上搜索返回结果，这个过程我们称为‘回表’。因此能用主键索引尽量用主键索引。

索引在数据量大的情况下利大于弊
缺点：索引占用磁盘空间
插入更新数据慢，不仅要更新原有的磁盘空间，还要更新索引的磁盘空间

索引最左原则
联合索引（a,b）,select * from t where b=100 ,无法使用索引。
select * from t where a=100 ,可以使用索引。

覆盖索引
表t，ID是主键，k是普通索引
select ID from t where k in (1,2)
这种不需要回表，因为直接就可以通过普通索引K定位到主键。

索引下推原则
从mysql5.6及之后带有了这个特性，减少了回表次数。
假如t表中有（a,b）联合所有，现在查询
select * from t where a like '张%'  and b=10 and c='上海'
执行首先使用最左原则，查询出来所有姓‘张’的人
5.6之前：查询出来100条姓张的，直接回表查询其他字段
5.6包括及之后：查询出100条姓张的，在索引（a,b）中，提前筛选掉 b!-10的所有记录，剩余只有2条，这样回表就只用回两次就可以了。