MySQL索引

MySQL索引

索引基础知识

什么是索引

索引是依靠某些数据结构和算法来组织数据，最终引导⽤户快速检索出所需要的数据。索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，直到找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。索引并非是越多越好，创建索引也需要耗费资源，一是增加了数据库的存储空间，二是在插入和删除时要花费较多的时间维护索引。任何标准表最多可以创建16个索引列。MySql为了快速检索数据，也⽤到了⼀些好的数据结构和算法，来组织表中的数据，加快检索效率。

索引有2个特点：
1）通过数据结构和算法来对原始的数据进⾏⼀些有效的组织
2）通过这些有效的组织，可以引导使⽤者对原始数据进⾏快速检索

总结：
1）索引是一种能提高数据库查询效率的数据结构。它可以比作一本字典的目录，可以帮你快速找到对应的记录。
2）索引一般存储在磁盘的文件中，它是占用物理空间的。
3）正所谓水能载舟，也能覆舟。适当的索引能提高查询效率，过多的索引会影响数据库表的插入和更新功能。

索引的类别

============================数据结构维度
1）R-树索引: 用来对GIS数据类型创建SPATIAL索引。
2）B+树索引：所有数据存储在叶子节点，复杂度为O(logn)，适合范围查询。
3）哈希索引：适合等值查询，检索效率高，一次到位。
4）全文索引（Full Text）：MyISAM和InnoDB中都支持使用全文索引（MySQL5.6开始InnoDB支持，之前版本InnoDB不支持），一般在文本类型char、text、varchar类型上创建。全文索引就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有【天下第一武道大会】通过“第一”，就可以找到该条记录。
FULLTEXT INDEX FullTxtIdx(info)
SELECT * FROM t4 WHERE MATCH(info) AGAINST('gorlr')

============================物理存储维度
1）聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据。（Innodb存储引擎）
2）非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列。（Innodb存储引擎）

============================逻辑维度
1）主键索引：一种特殊的唯一索引，不允许有空值。
PRIMARY KEY(id)
2）普通索引（Normal）：MySQL中基本索引类型，允许空值和重复值。MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。
Index(xx)或者key(xx)
3）联合索引：多个字段创建的索引，使用时遵循最左前缀原则。
4）唯一索引（Unique）：索引列中的值必须是唯一的，但是允许为空值。
UNIQUE INDEX UniqIdx(xx)
5）空间索引（SPATIAL）：MySQL5.7之后支持空间索引，在空间索引这方面遵循OpenGIS几何数据模型规则。空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyIsam，创建空间索引的列，必须将其声明为NOT NULL。
SPATIAL INDEX spatIdx(g)

============================索引数量维度
1）单列索引：一个索引只包含单个列，但一个表中可以有多个单列索引。
2）组合索引（复合索引）：在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。
INDEX MultiIdx(id,name,age)

如何创建索引

-- 查看索引
SHOW INDEX FROM stu_info;

---------------------------------CREATE---------------------------------
-- 创建表的时候直接指定唯一索引
CREATE TABLE stu_info(id int NOT NULL,user_name VARCHAR(16) NOT NULL, UNIQUE index_username (user_name(10)));

-- 创建索引，name是索引名称，括号里面是字段名称
CREATE [UNIQUE] INDEX 索引名称 ON 表名(列名[(length)]);
-- CREATE INDEX可对表增加普通索引或UNIQUE索引，但是不能创建PRIMARY KEY索引
CREATE INDEX index_sName ON stu_info(sName);
-- 创建唯一索引
CREATE UNIQUE INDEX index_sName ON stu_info(sName(10))

---------------------------------ALTER---------------------------------
-- 修改表的方式追加索引，如果字段是char、varchar类型，length可以⼩于字段实际长度，如果是blog、text等长⽂本类型，必须指定length。[unique]中括号代表可以省略，如果加上了unique，表⽰创建唯⼀索引。如果table后⾯只写⼀个字段，就是单列索引，如果写多个字段，就是复合索引，多个字段之间⽤逗号隔开。length用法：如10个字符，前面6个不一样，后面一样，则设置为6合适。
ALTER 表名 ADD [UNIQUE] INDEX 索引名称 ON (列名[(length)]);
-- 例
ALTER stu_info ADD UNIQUE INDEX index_sName ON (sName(10)) 

-- 添加索引，age是索引名称，括号里面是字段名称，ALTER TABLE用来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE stu_info ADD INDEX index_age(sAge);
-- 添加组合索引
ALTER TABLE stu_info ADD INDEX name_city_age(username(10),city,age);

-- 创建前缀索引，使用字段值的前10个字符建立索引，默认是使用字段的全部内容建立索引
ALTER TABLE stu_info ADD INDEX index_sName(sName(10))
-- 通过从调整prefixLen的值（即下面的100，从1自增）查看不同前缀长度的一个平均匹配度，接近1时就可以了（表示一个密码的前prefixLen个字符几乎能确定唯一一条记录）
SELECT COUNT(DISTINCT sName)/count(*) AS a,COUNT(DISTINCT LEFT(sName,100)) AS b, COUNT(DISTINCT LEFT(sName,110)) AS c FROM stu_info

-- 修改主键
alter table test1 modify id int not null primary key;

---------------------------------DROP---------------------------------
-- 删除索引方法，index_age是索引名称
ALTER TABLE stu_info DROP INDEX index_age;
-- 删除索引方法，index_age是索引名称
DROP INDEX index_age ON stu_info;

索引区分度

索引区分度=count(distint 记录)/count(记录)。当索引区分度⾼的时候，检索数据更快⼀些，索引区分度太低，说明重复的数据⽐较多，检索的时候需要访问更多的记录才能够找到所有⽬标数据。当索引区分度⾮常⼩的时候，基本上接近于全索引数据的扫描了，此时查询速度是⽐较慢的。所以我们创建索引的时候，尽量选择区分度⾼的列作为索引。当多个条件中有索引的时候，并且关系是and的时候，会⾛索引区分度⾼的。如：select * from test1 where sex=2 and name='javacode3500000';显然name字段重复度很低，⾛name查询会更快⼀些（先⾛name索引，然后再根据sex过滤）。

补充：区分度就是指大部分数据不一样，所以一般索引会建在大部分数据不一样的字段上，但是如性别只有2个种类就不适合创建索引了。

索引进阶知识

最左前缀原则

1）MySql建立多列索引（联合索引）有最左前缀的原则，即最左优先，如：
如果有一个2列的索引(col1,col2)，则已经对(col1)、(col1,col2)上建立了索引；
如果有一个3列索引(col1,col2,col3)，则已经对(col1)、(col1,col2)、(col1,col2,col3)上建立了索引；
如果想索引最大化，则继续追加索引(col2,col3)；

2）范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。MySql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如 a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。=和in可以乱序，比如 a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，MySql的查询优化器会帮你优化成索引可以识别的形式。

3）从B+树说明
当b+树的数据项是复合的数据结构，⽐如(name,age,sex)的时候，b+树是按照从左到右的顺序来建⽴搜索树的，⽐如当(张三,20,F)这样的数据来检索的时候，b+树会优先⽐较name来确定下⼀步的所搜⽅向，如果name相同再依次⽐较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下⼀步该查哪个节点，因为建⽴搜索树的时候name就是第⼀个⽐较因⼦，必须要先根据name来搜索才能知道下⼀步去哪⾥查询。⽐如当(张三,F)这样的数据来检索时，b+树可以⽤name来指定搜索⽅向，但下⼀个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是⾮常重要的性质，即索引的最左匹配特性。

聚簇、非聚簇索引

聚集索引与非聚集索引的区别是：叶节点是否存放一整行记录。

聚簇索引（主键加数据）
1）将数据存储与索引放到了一块，找到索引也就找到了数据。表数据是和主键一起存储的，主键索引的叶结点存储行数据（包含了主键值），二级索引（普通索引）的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录（数据内容）。
2）每个表有且⼀定会有⼀个聚集索引，整个表的数据存储在聚集索引中，mysql索引是采⽤B+树结构保存在⽂件中，叶⼦节点存储主键的值以及对应记录的数据，⾮叶⼦节点不存储记录的数据，只存储主键的值。当表中未指定主键时，mysql内部会⾃动给每条记录添加⼀个隐藏的rowid字段（默认4个字节）作为主键，⽤rowid构建聚集索引。聚集索引在mysql中又叫主键索引。

非聚簇索引（索引、数据分开存储）
1）将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，MyISAM通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因。B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据，非聚簇索引也不一定要回表查询，这涉及到查询语句所要求的字段是否全部命中了索引，如果全部命中了索引，那么就不必再进行回表查询。表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据。
2）非聚簇索引也是b+树结构，不过有⼀点和聚集索引不同，⾮聚集索引叶⼦节点存储字段（索引字
段）的值以及对应记录主键的值，其他节点只存储字段的值（索引字段）。每个表可以有多个⾮聚集索引。

聚簇索引优点：当你需要取出一定范围内的数据时，用聚簇索引也比用非聚簇索引好。当通过聚簇索引查找目标数据时理论上比非聚簇索引要快，因为非聚簇索引定位到对应主键时还要多一次目标记录寻址，即多一次I/O。使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

聚簇索引缺点：插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键。更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。二级索引的叶节点存储的是主键值，而不是行指针（非聚簇索引存储的是指针或者说是地址），这是为了减少当出现行移动或数据页分裂时二级索引的维护工作，但会让二级索引占用更多的空间。采用聚簇索引插入新值比采用非聚簇索引插入新值的速度要慢很多，因为插入要保证主键不能重复，判断主键不能重复，采用的方式在不同的索引下面会有很大的性能差距，聚簇索引遍历所有的叶子节点，非聚簇索引也判断所有的叶子节点，但是聚簇索引的叶子节点除了带有主键还有记录值，记录的大小往往比主键要大的多。这样就会导致聚簇索引在判定新记录携带的主键是否重复时进行昂贵的I/O代价。

聚簇索引不一定是主键索引：InnoDB主键使用的是聚簇索引，MyISAM不管是主键索引，还是二级索引使用的都是非聚簇索引。在InnoDB引擎中，每张表都会有一个聚簇索引，一般情况下聚簇索引等于主键索引，但聚簇索引又不完全等于主键索引，因为一张表中没有主键索引，那么聚簇索引会使用第一个唯一索引（此列必须为 not null），如果以上情况都不满足，那么InnoDB则会默认创建一个隐藏的row_id作为聚簇索引。

回表查询

-- 如果查询条件为主键（聚簇索引），则只需扫描一次B+树即可通过聚簇索引定位到要查找的行记录数据。如：
select * from user where id = 1;

-- 如果查询条件为普通索引（非聚簇索引），需要扫描两次B+树，第一次扫描通过普通索引定位到聚簇索引的值，然后第二次扫描通过聚簇索引的值定位到要查找的行记录数据（回表查询）。先通过普通索引age=30定位到主键值id=1，再通过聚集索引id=1定位到行记录数据（查询了所有字段，不是所有字段加了索引）。如age加上索引，其他字段没有索引：
select * from user where age = 30;

-- 索引覆盖：只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快。如id、age都有索引：
select id,age from user where age = 10;

-- 回表查询：先通过普通索引的值定位聚簇索引值，再通过聚簇索引的值定位行记录数据，需要扫描两次索引B+树，它的性能较扫一遍索引树更低。如age是普通索引，但name列不在索引树上，所以通过age索引在查询到id和age的值后，需要进行回表再查询name的值（和查询全部类似）。此时的Extra列的NULL表示进行了回表查询。通过全部加上索引变成索引覆盖可优化，实现：
select id,age,name from user where age = 10;

覆盖索引

查询中采⽤的索引树中包含了查询所需要的所有字段的值，不需要再去聚集索引检索数据，这种叫索引覆盖。如果索引包含满足查询的所有数据（所有查询的字段加上索引），就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：索引项通常比记录要小，所以MySQL访问更少的数据；索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。对于索引覆盖查询（index-covered query），使用EXPLAIN时，可以在Extra一列中看到Using index（覆盖索引）。MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表操作，减少IO，提高了效率。

索引下推

简称ICP，Index	Condition Pushdown(ICP)是MySQL5.6中新特性，是⼀种在存储引擎层使⽤索引过滤数据的⼀种优化⽅式，ICP可以减少存储引擎访问基表的次数以及MySQL服务器访问存储引擎的次数。之前版本在联合索引下，先找到name，然后拿到id去回表查询数据，再匹配sex。优化后是找到name，然后在这个基础上再过滤出sex，不回表查询。

案例：
我们需要查询name以javacode35开头的，性别为1的记录数，并且有一个联合索引(name,sex)，sql如下：
select count(id) from test1 a where name like 'javacode35%' and sex = 1;
5.6版本之前检索过程如下：
1、⾛name索引检索出以javacode35的第⼀条记录，得到记录的id。
2、利⽤id去主键索引中查询出这条记录R1。	
3、判断R1中的sex是否为1，然后重复上⾯的操作，直到找到所有记录为⽌。

上⾯的过程中需要⾛name索引以及需要回表操作。如果采⽤ICP的⽅式，我们可以这么做，创建⼀个(name,sex)的组合索引，查询过程如下：
1、⾛(name,sex)索引检索出以javacode35的第⼀条记录，可以得到(name,sex,id)，记做R1。
2、判断R1.sex是否为1，然后重复上⾯的操作，直到找到所有记录为⽌。
这个过程中不需要回表操作了，通过索引的数据就可以完成整个条件的过滤，速度⽐上⾯的更快⼀些。

索引优化方案

索引使用原则

1）并不是所有索引对查询都有效
并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。即应该在区分度⾼的字段上⾯建⽴索引可以有效的使⽤索引，区分度太低，⽆法有效的利⽤索引，可能需要扫描所有数据页，此时和不使⽤索引差不多。

2）索引并不是越多越好
索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数较好不要超过6个（最多16个索引列），若太多则应考虑一些不常使用到的列上建的索引是否有必要。

3）避免更新聚簇索引数据列
应尽可能的避免更新clustered索引数据列，mysql默认的clustered索引为主键，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引。

4）若是这张表增删改多而查询较少的话，就不要创建索引了
因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，若是删，则会把索引中以这个字段为名的索引的子集删掉。所以，索引会减慢增删改的执行速度，若是这张表增删改多而查询较少的话，就不要创建索引了。

5）当数据多且字段值有相同的值得时候用普通索引，当字段多且字段值没有重复的时候用唯一索引。

6）当有多个字段名都经常被查询的话用复合索引，普通索引不支持空值，唯一索引支持空值。

7）更新太频繁地字段不适合创建索引，不会出现在where条件中的字段不该建立索引。

8）索引的建立要根据业务特点进行，不能凭空想象的设置索引。经常作为查询条件的列才有建立索引的必要性。

9）查询记录的时候，少使⽤*，尽量去利⽤索引覆盖，可以减少回表操作，提升效率。

10）有些查询可以采⽤联合索引，进⽽使⽤到索引下推（IPC），也可以减少回表操作，提升效率。

11）禁⽌对索引字段使⽤函数、运算符操作，会使索引失效。

12）字符串字段和数字⽐较的时候会使索引⽆效。

13）模糊查询'%值%'会使索引⽆效，变为全表扫描，但是'值%'这种可以有效利⽤索引。

14）排序中尽量使⽤到索引字段，这样可以减少排序，提升查询效率。

索引失效场景

⾛索引术语说明：当我们对某个字段的值进⾏某种检索的时候，如果这个检索过程中，我们能够快速定位到⽬标数据所在的页，有效的降低页的io操作，⽽不需要去扫描所有的数据页的时候，我们认为这种情况能够有效的利⽤索引，也称这个检索可以⾛索引，如果这个过程中不能够确定数据在那些页中，我们认为这种情况下索引对这个查询是⽆效的，此查询不⾛索引。

索引失效场景：
1）使用以%开头的LIKE模糊匹配语句（%加在后面是可以的），索引失效。

2）使用OR语句前后搜索条件没有同时加上索引，索引失效。

3）数据类型出现隐式转化，如varchar不加单引号的话可能会自动转换为int型，索引失效。
select * from test1 where name = '1';
select * from test1 where name = 1;
第一个查询很快，第二个⽤name和1⽐较，name上有索引，name是字符串类型，字符串和数字⽐较的时候，会将字符串强制转换为数字，然后进⾏⽐较，所以第⼆个查询变成了全表扫描，只能取出每条数据，将name转换为数字和1进⾏⽐较。但是如果是数值类型，比如id上⾯有主键索引，id是int类型的，匹配的值是字符串还是数值都会⾛索引。

4）不符合最左前缀原则的语句，索引失效

5）索引字段使⽤函数查询，索引失效
select a.name+1 from test1 a where a.name = 'javacode1';
select * from test1 a where concat(a.name,'1') = 'javacode11';
name上有索引，上⾯查询，第⼀个⾛索引，第⼆个不⾛索引，第⼆个使⽤了函数之后，name所在的索引树是⽆法快速定位需要查找的数据所在的页的，只能将所有页的记录加载到内存中，然后对每条数据使⽤函数进⾏计算之后再进⾏条件判断，此时索引⽆效了，变成了全表数据扫描。

6）order by索引失效说明：
在使用order by对某列进行排序时, 其sql一定进行了多表联查的操作。说白了肯定做了表关联，既然有表关联, 那order by排序就可以对任意的表进行排序，那么应该对哪个表进行排序好点呢。先说结论，order by后面跟的索引列只是对驱动表才起作用, 也就是说如果是对驱动表进行排序, 那么order by后面的索引是起作用的, 但是如果order by后面跟的排序字段不是驱动表的, 那么这时索引是不起作用的。

驱动表：在进行多表联查时, sql执行时会先查出一个表中符合查询条件的数据，然后再用这些条件去匹配另一个表中的符合条件的数据, 合并后的数据就是我们要的结果集。那么先查出来结果集的这个表就是驱动表。谁查出来符合条件的数据量少, 谁就最有资格做驱动表。这里为什么只说最有资格做，而不是一定做，因为当用户使用left join, right join指定了驱动表时, 以用户指定的表作为驱动表（关键字左边的表是驱动表）, 当用户使用join或者没有指定驱动表时, 由数据库自己挑出一个结果集最少的表作为驱动表。如果用户使用left join指定了一个查询出来数据量很大的表作为驱动表时, 那就很糟糕了。

总结：
如果order by后的排序字段用的是驱动表的排序字段, 那么这个字段的索引是不失效的, 效率高。
如果order by后的排序字段用的不是驱动表的字段, 那么索引不会起作用, 数据库会将驱动表查出来的数据和其他表进行关联, 关联后的结果集创建一个临时表进行存储, 然后对这个临时表按非驱动表的列进行排序，这样sql效率会变慢。

索引优化口诀

全值匹配我最爱（索引覆盖），最左前缀要遵守；带头大哥不能死（最左前缀），中间兄弟不能断；
索引列上少计算（函数失效），范围之后全失效；Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；VAR引号不可丢（字符转换失效），SQL高级也不难！

大表如何添加索引

如果一张表数据量级是千万级别以上的，那么，如何给这张表添加索引？
我们需要知道一点，给表添加索引的时候，是会对表加锁的。如果不谨慎操作，有可能出现生产事故的。可以参考以下方法：
1）先创建一张跟原表A数据结构相同的新表B。
2）在新表B添加需要加上的新索引。
3）把原表A数据导到新表B。
4）rename新表B为原表的表名A，原表A换别的表名或删除。

索引原理解析

树结构说明

树（倒立的树结构）：其实树就是从一个根节点出发，其可以有很多子节点，而子节点又可以有很多子节点，这样就像我们现实生活中的树一样，不过我们这颗树是倒立的。因为树的分支太多且没有规律所以很难控制，要想让树发挥他的作用就得在基本的树结构上加上一些特性，让有了特性的树成为帮助我们解决问题的结构。

==========================二叉树==========================
二叉树：最常用的就是二叉树了，二叉树听名字就知道是一个节点至多只有两个节点，这样对数进行了一定的限制，整棵树看起来就顺眼多了。

二叉搜索树（无序的时候左小于父，右大于父。有序的时候会变成一条链表）：二叉搜索树的节点满足一个规律，父节点的左孩子的键值小于父节点的键值，而右孩子的键值大于父节点的键值，这样当我们在这颗数中查询某个键值时就可以根据当前节点的键值和要寻找的键值的大小比较，确定该忘哪条路走下去。二叉搜索树还有一个特点就是中序遍历的时候其键值是按大小排序的。

平衡二叉树（AVL树，它的左右两个⼦树的⾼度差的绝对值不超过1，并且左右两个⼦树都是⼀棵平衡
⼆叉树）：由于我们要插入的数据可能是本身就排好序的，所以会导致插入数据时树变成线性的结构，只有一条路（上面说的变成链表）。于是我们需要保证二叉树的平衡，当发现这棵树要出现往一边倒的情况时就要想某种方式让其保持平衡（叶子节点的高度差最大为1），这就涉及到一些节点的旋转变换了。平衡⼆叉树相对于⼆叉树来说，树的左右⽐较平衡，不会出现⼆叉树那样退化成链表的情
况，不管怎么插⼊数据，最终通过⼀些调整，都能够保证树左右⾼度相差不⼤于1。这样可以让查询速度⽐较稳定，查询中遍历节点控制在O(logN)范围内。如果数据都存储在内存中，采⽤AVL树来存储，还是可以的，查询效率⾮常⾼。如果我们的数据是存在磁盘中（mysql），采⽤这种结构，每个节点对应⼀个磁盘块，数据量⼤的时候，也会和⼆叉树⼀样，会导致树的⾼度变⾼，增加了io次数，显然⽤这种结构存储mysql数据也是不可取的。

红黑树：红黑树也是一种平衡二叉树，不过加入了一些新的特性，听名字就知道，在红黑树中节点的颜色要么是红色要么是黑色的，当然还有其他的一些特性，当插入或者删除数据破坏了红黑树的这些特性时，我们需要进行一些操作（一般是颜色改变和树的旋转）红黑树保持其原有的特性。

==========================多叉树（B树）==========================
B-树：B杠树，千万不要读作B减树了，B-树在是平衡⼆叉树上进化来的，前⾯介绍的⼏种树，每
个节点上⾯只有⼀个元素，⽽B-树节点中可以放多个元素，主要是为了降低树的⾼度。由于二叉树是二叉的，所以当树的节点不断增加时就会导致树的高度不断的增加，所以查询的效率就很低了，当我们面对海量数据（像数据库中保存的数据）的时候这种结构是不行的，所以我们又衍生出了新的树结构。B数一样拥有自平衡的特性，最大的区别在于B树不是二叉的，而是多叉的，具体有多少个叉要根据树的阶数来判断。B-树相对于avl树，通过在节点中增加节点内部数据的个数来减少磁盘的io操作。

B+树：和B树相比，B+树又增加了一些特性，B+树主要是为了方便查询一个区间的数据集合，因为我们使用B树的时候要想查询某个区间内的数据得使用中序遍历将树中的数据全部遍历一遍，这样的时间复杂度是O(n)，效率太低了。而B+树只用叶子节点保存具体值的地址，非叶子节点只保存其子节点的指针，叶子节点之间通过指针链接起来，是有序的，所以在查找一个范围内的数据是很有效的。其时间复杂度为O(logn+M)，M为要查找的数据个数。

页结构说明

页结构整体示例图：

页结构内部示例图：

头部与主体的链表结构图：

B+树中一个节点等于一个page：

directory数据结构图：

页结构说明：

1）mysql中页是innodb中存储数据的基本单位，也是mysql中管理数据的最⼩单位，和磁盘交互的时候都是以页来进⾏的，默认是16kb，mysql中采⽤b+树存储数据，页相当于b+树中的⼀个节点。

2）page头部：每个Page都有通⽤的头和尾，但是中部的内容根据Page的类型不同⽽发⽣变化。Page的头部⾥有我们关⼼的⼀些数据。Page的头部保存了两个指针，分别指向前⼀个Page和后⼀个Page，根据这两个指针我们很容易想象出Page链接起来就是⼀个双向链表的结构。

3）page主体：再看看Page的主体内容，我们主要关注⾏数据和索引的存储，他们都位于Page的User Records部分，User	Records占据Page的⼤部分空间，User Records由⼀条⼀条的Record组成。在⼀个Page内部，单链表的头尾由固定内容的两条记录来表⽰，字符串形式的"Infimum"代表开头，"Supremum"代表结尾，这两个⽤来代表开头结尾的Record存储在System	Records。Infinum、Supremum和User Records组成了⼀个单向链表结构。最初数据是按照插⼊的先后顺序排列的，但是随着新数据的插⼊和旧数据的删除，数据物理顺序会变得混乱，但他们依然通过链表的⽅式保持着逻辑上的先后顺序。

4）innodb为了快速查找记录，在页中定义了⼀个称之为page directory的⽬录槽（slots）,每
个槽位占⽤两个字节（⽤于保存指向记录的地址），page	directory中的多个slot组成了⼀个有序数组（可⽤于⼆分法快速定位记录），⾏记录被Page Directory逻辑的分成了多个块，块与块之间是有序的，能够加速记录的查找。每个⾏记录的都有⼀个nowned的区域（图中粉⾊区域），nowned标识所属的slot这个这个块有多少条数据，伪记录Infimum的nowned值总是1，记录Supremum的
nowned的取值范围为[1,8]，其他⽤户记录nowned的取值范围[4,8]，并且只有每个块中最⼤的那条记录的nowned才会有值，其他的⽤户记录的n_owned为0。

5）在page中查询数据的时候，先通过b+树中查询⽅法定位到数据所在的页，然后将页内整体加载到内存中，通过⼆分法在page directory中检索数据，缩⼩范围，⽐如需要检索7，通过⼆分法查找到7位于slot2和slot3所指向的记录中间，然后从slot3指向的记录5开始向后向后⼀个个找，可以找到记录7，如果⾥⾯没有7，⾛到slot2指向的记录8结束。nowned范围控制在[4,8]内，能保证每个slot管辖的范围内数据量控制在[4,8]个，能够加速⽬标数据的查找，当有数据插⼊的时候，page directory为了控制每个slot对应块中记录的个数（[4,8]），此时page directory中会对slot的数量进⾏调整。

页结构总结：
1）B+树中叶⼦页之间⽤双向链表连接的，能够实现范围查找
2）页内部的记录之间是采⽤单向链表连接的，⽅便访问下⼀条记录
3）为了加快页内部记录的查询，对页内记录上加了个有序的稀疏索引，叫页⽬录page directory

索引原理详解

3阶B-树示例图：

B+树示例图：

不同存储引擎的索引区别

索引原理说明

索引的本质：通过不断地缩⼩想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是⽤同⼀种查找⽅式来锁定数据。整体上来说mysql中的索引⽤到了b+树，链表，⼆分法查找，做到了快速定位⽬标数据，快速范围查找。

数据库索引底层：常用就是用就是B树或者是B+树这种结构，索引的原理很简单，就是把无序的数据变成有序的查询，先把创建了索引的列的内容进行排序，再对排序结果生成倒排表，在倒排表内容上拼上数据地址链，在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据。

B-Tree结构的数据可以让系统⾼效的找到数据所在的磁盘块。每个节点占⽤⼀个盘块的磁盘空间，⼀个节点上有两个升序排序的关键字和三个指向⼦树根节点的指针，指针存储的是⼦节点所在磁盘块的地址。两个键将数据划分成的三个范围域，对应三个指针指向的⼦树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的⼦树的数据范围为⼩于17，P2指针指向的⼦树的数据范围为17~35，P3指针指向的⼦树的数据范围为⼤于35。模拟查找关键字29的过程：
1、根据根节点找到磁盘块1，读⼊内存。【磁盘I/O操作第1次】
2、⽐较关键字29在区间（17,35），找到磁盘块1的指针P2	
3、根据P2指针找到磁盘块3，读⼊内存。【磁盘I/O操作第2次】
4、⽐较关键字29在区间（26,30），找到磁盘块3的指针P2	
5、根据P2指针找到磁盘块8，读⼊内存。【磁盘I/O操作第3次】
6、在磁盘块8中的关键字列表中找到关键字29
分析上⾯过程，发现需要3次磁盘I/O操作，和3次内存查找操作，由于内存中的关键字是⼀个有序表结构，可以利⽤⼆分法快速定位到⽬标数据，⽽3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。mysql是采⽤页⽅式来读写数据，每页是16KB，我们⽤B-树来存储mysql的记录，每个节点对应mysql中的⼀页（16KB），假如每⾏记录加上树节点中的1个指针占160Byte，那么每个节点可以存储1000（16KB/160byte）条数据，树的⾼度为3的节点⼤概可以存储（第⼀层1000+第⼆层10002
+第三层10003）10亿条记录，是不是⾮常惊讶，⼀个⾼度为3个B-树⼤概可以存储10亿条记录，我们从10亿记录中查找数据只需要3次io操作可以定位到⽬标数据所在的页，⽽页内部的数据又是有序的，然后将其加载到内存中⽤⼆分法查找，是⾮常快的。可以看出使⽤B-树定位某个值还是很快的(10亿数据中3次io操作+内存中⼆分法)，但是也是有缺点的：B-不利于范围查找，⽐如上图中我们需要查找[15,36]区间的数据，需要访问7个磁盘块（1/2/7/3/8/4/9），io次数又上去了，范围查找也是我们经常⽤到的，所以b-树也不太适合在磁盘中存储需要检索的数据。

b+树的⼏个特点：
1、叶⼦节点（最下⾯的⼀层）存储关键字（索引字段的值）信息及对应的data，叶⼦节点存储了所有记录的关键字信息
2、其他⾮叶⼦节点只存储关键字的信息及⼦节点的指针
3、每个叶⼦节点相当于mysql中的⼀页，同层级的叶⼦节点以双向链表的形式相连
4、每个节点（页）中存储了多条记录，记录之间⽤单链表的形式连接组成了⼀条有序的链表，顺序是按照索引字段排序的
5、b+树中检索数据时：每次检索都是从根节点开始，⼀直需要搜索到叶⼦节点

页结构：InnoDB的数据是按数据页为单位来读写的。也就是说，当需要读取⼀条记录的时候，并不是将这个记录本⾝从磁盘读取出来，⽽是以页为单位，将整个也加载到内存中，⼀个页中可能有很多记录，然后在内存中对页进⾏检索。在innodb中，每个页的⼤⼩默认是16kb。

b+树与b-树的⼏点不同：
1、b+树中⼀个节点如果有k个关键字，最多可以包含k个⼦节点（k个关键字对应k个指针）；⽽b-树对应k+1个⼦节点（多了⼀个指向⼦节点的指针）
2、b+树除叶⼦节点之外其他节点值存储关键字和指向⼦节点的指针，⽽b-树还存储了数据，这样同样⼤⼩情况下，b+树可以存储更多的关键字
3、b+树叶⼦节点中存储了所有关键字及data，并且多个节点⽤链表连接，从上图中看⼦节点中数据从左向右是有序的，这样快速可以⽀撑范围查找（先定位范围的最⼤值和最⼩值，然后⼦节点中依靠链表遍历范围数据）

B-Tree和B+Tree该如何选择：
1、B-Tree因为⾮叶⼦结点也保存具体数据，所以在查找某个关键字的时候找到即可返回。⽽B+Tree所有的数据都在叶⼦结点，每次查找都得到叶⼦结点。所以在同样⾼度的B-Tree和B+Tree中，B-Tree查找某个关键字的效率更⾼。
2、由于B+Tree所有的数据都在叶⼦结点，并且结点之间有指针连接，在找⼤于某个关键字或者⼩于某个关键字的数据的时候，B+Tree只需要找到该关键字然后沿着链表遍历就可以了，⽽B-Tree还需要遍历该关键字结点的根结点去搜索。
3. 由于B-Tree的每个结点（这⾥的结点可以理解为⼀个数据页）都存储主键+实际数据，⽽B+Tree⾮叶⼦结点只存储关键字信息，⽽每个页的⼤⼩有限是有限的，所以同⼀页能存储的B-Tree的数据会⽐B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更⼤，增⼤查询时的磁盘I/O次数，进⽽影响查询效率。

Innodb中有2种索引：主键索引（聚集索引）、辅助索引（⾮聚集索引）。
主键索引：每个表只有⼀个主键索引，b+树结构，叶⼦节点同时保存了主键的值和数据记录，其他节点只存储主键的值。
辅助索引（先找ID再找数据，回表查询）：每个表可以有多个，b+树结构，叶⼦节点保存了索引字段的值以及主键的值，其他节点只存储索引指端的值。

MyISAM（查询速度快）使⽤的是⾮聚簇索引，⾮聚簇索引的两棵B+树看上去没什么不同，节点的结构完全⼀致只是存储的内容不同⽽已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独⽴的地⽅，这两颗B+树的叶⼦节点都使⽤⼀个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独⽴的，通过辅助键检索⽆需访问主键的索引树。

我们⽤的最多的是innodb存储引擎，innodb中最好是采⽤主键查询，这样只需要⼀次索引，如果使⽤辅助索引检索，涉及到回表操作，⽐主键查询要耗时⼀些。innodb中辅助索引为什么不像myisam那样存储记录的地址？因为表中的数据发⽣变更的时候，会影响其他记录地址的变化，如果辅助索引中记录数据的地址，此时会受影响，⽽主键的值⼀般是很少更新的，当页中的记录发⽣地址变更的时候，对辅助索引是没有影响的。