mysql索引

1、概念

什么是索引

索引就是一种排好序的数据结构

2、 索引有哪些类型

2.1 二叉树

根节点右面的元素比根节点大，根节点左边的元素比根节点小
若以age为索引元素建立二叉树

但是若以自增id作为索引建立二叉树（一般数据库表创建的时候就会默认以主键建立一颗索引树，也叫一级索引或主键索引），就会使树的高度变得非常高，变为一个链表，这个时候与全表扫描就没有差别，所以显然以这种数据结构建立索引并不合理。

2.2 hash

以key：value的形式存储，key：索引的值，value：索引所对应的行的值，所以查询速度会非常快，但是无法支持范围查询，因为hash的形式无法支持排序。

2.3 红黑树

与二叉树类似，但是一旦出现父节点与子节点相差两层的情况，就会自动帮我们转化，降低树的高度，虽然这种数据结构比较好，但mysql依旧没有以这种形式建立索引。

2.4 B树和B+树

我们力求找到一种树的高度低，又可以存储很多元素的数据结构，所以这时就有了B树和B+树。

每个节点的位置现在不仅仅只能存储一个元素，而是可以存储多个元素，每个节点为一页，一页大概为16kb，而我们的一个int类型的数据是4个字节，估算下来一个高度为3的B+树可以存储2000万条数据，显然这种数据结构很合理。
B树
每个节点的位置现在不仅仅只能存储一个元素，而是可以存储多个元素
所有叶子节点的深度相同
每个节点都既存储索引，也存储索引所对应的数据（问题：主键还是整条数据？）
节点中的元素从做到右递增，满足左小右大，根节点同样大于左子树、小于右子树
不支持排序

B+树
根节点没有数据，只存索引，同时其索引按照从左到右递增的顺序
所有叶子节点的深度相同
叶节点之间有一个双向指针用来排序
由于一二层位置的节点不存数据，所以可以存储更多的索引
有很多的冗余节点，用来方便查找元素的位置，在叶子节点处可以找到整张表的数据

2.5 BTree与B+Tree的区别

B+Tree中只有叶子节点存储数据，而BTree中所有节点都存储数据
B+Tree中有一些冗余的节点，所有节点都可以在叶子节点中找到，而BTree中插入几个节点就有几个节点，没有冗余的节点
B+Tree中叶子节点之间有双向指针，方便范围查找，但BTree中没有

最左前缀原则

假如建立的索引为根据a、b、c三个字段组成的a_b_c，则B+树中，顺次根据索引a、b、c排序，而一旦条件中出现select * from table where a = 1 and xxx,就可以使用索引查询,但若没有字段a,只有字段b、c,就不能使用索引查询，因为B+Tree是根据a字段作为第一个元素排序建立的，而比较完成a之后，才会使用b排序，最后使用c,所以，相较而言，b和c是无序的，并不能有效使用索引查询，只能进行全表扫描。

注意：并不是所有包含最左前缀原则的查询都要走索引，因为涉及到回表操作，可能扫描全表比索引扫描更快能得到结果，所以主要还是看具体场景

聚簇索引

索引和数据存在一起就叫聚簇索引
比如Innodb的主键索引就是聚簇索引（只要表里有主键，就会默认使用主键建立一颗索引树，没有主键则会自动找出一列能充当主键的列，则会自动帮我们维护出一个主键列）
Innodb的索引和数据是存储在一个文件中的
但myISAM的索引和数据是存在两个文件中的（1个文件存索引，1个文件存数据），所以不是聚簇索引

Innodb与myISAM

正常建表会根据主键生成一棵B+Tree,但往往我们的查询条件并不是主键，所以为了便于查询大数据量的操作，我们会将经常用到查询条件的字段放在一起建立联合索引，这时mysql还会帮我们创建一个索引B+树,根据使用索引（explain工具的key为索引字段时，证明这条sql确实走了索引查询）的字段查询时，会调用这颗二级索引的B+Tree。

Innodb中，二级索引B+树的叶子节点中，数据部分存储的是主键（这棵树中没有所完整的一条数据，仅有索引和主键），若查询使用当前这个B+Tree,找到对应叶节点后，会根据主键回表（查询使用主键所建立的B+Tree）,进而得到我们要的完整的一条数据。

myISAM中，二级索引B+Tree的叶子节点中，存储的是数据的地址，所以查询结束会根据地址找到对应的数据。

覆盖索引（索引查询的一种情况）

若我们查询的字段，在二级索引树上都有，并不用回表（再次查询主键B+树），我们称之为覆盖索引

order by的一种情况

explain select * from t1 order by b,c,d
这里的b,c，d为联合索引，考虑会走全表扫描还是索引查找？
因为 这里是查询所有字段，但我们索引树中并没有所有的数据（只有索引+主键），所以会涉及到多次回表查询，导致效率下降
但若为全表扫描，我们将所有情况遍历，需要一次额外的排序，但相较而言不用n次回表，回表的效率较低，但排序较快，所以使用全表扫描。

数据库锁

待定