目录
1.索引是什么？
定义：索引是帮助MySQL高效获取数据的 排好序的 数据结构。
官方定义：在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。（引用自百度百科）
常见的索引结构：二叉树、红黑树、Hash表、B-Tree、B+树（InnoDB引擎默认使用的索引结构）
（注意：Hash表仅能满足等值查询，不能进行范围查询，Hash表进行范围查询容易导致全表扫描，并且使用Hash表存在哈希冲突问题）
下面详细介绍MySQLinnoDB引擎的数据结构。（InnoDB引擎默认使用B+Tree作为索引的数据结构，数据在存储的时候就是按照B+Tree的形式存储，不是一行一行的存储（我们所理解的按行存储 ×））
在MySQL客户端使用
show global variables like "%datadir%";可以查看数据库文件的存放位置。
2.B-Tree与B+Tree的相同点与不同点
相同点：节点中的数据索引从左到右依次递增。
不同点：
B树：叶子结点和非叶子结点都存放数据
B+树：非叶子不存放数据，只存放索引指针；叶子结点存放数据，并且叶子结点之间存在双向指针，叶子结点整体是一个大的双向链表（有利于范围查询）。
B-Tree的示意图：
B+Tree的示意图：
注意：要将Mysql的索引结构记在心中，对于Mysql的优化都是在索引的基础上进行的。
3.使用B+Tree作为索引可容纳的数据量（非精确计算）
MySQL中的数据通常是以页为单位存储数据的。页大小（索引通常存储在磁盘中，页是数据的存储单位）通常为16KB。假设存放bigint数据（8字节），指针通常为6字节。（数据+指针=14B）
那么，一个非叶子结点可以存放16KB/(8+6)B=1170个元素数据，一个叶子结点可以存放16个Data(假设每个Data大小为1KB)。如果该B+树是三层的，那么一棵树可以存放1170*1170*16≈2000万数据。（这就说明了使用B+树存放数据之庞大！）
说明：通常，B+树的根节点常驻内存。在查找数据的时候，会将数据所在页加载到内存，然后使用二分查找在该页上进行查找（在内存中查找数据相比IO的时间，查找数据的时间可以忽略不计，时间主要花费在IO读取上）。
4.InnoDB和MyISAM存储引擎的区别
InnoDB：
1.索引文件和数据文件是放在一起的（存在一个聚簇索引）
2.支持行锁、表锁
3.不记录表的总行数，因为事务的原因
4.支持事务
MyISAM：
1.索引文件和数据文件是分离存放的（均为非聚簇索引），它的叶子结点存放的是数据的地址
2.仅支持表锁，MyISAM在执行select前，会自动给涉及到的所有表加读锁，在执行insert、update、delete会自动给涉及的表加写锁。
3.MyISAM会记录表的总行数，如果需要查询一个表的总行数count(*)，MyISAM可以很快得到结果。
4.不支持事务
MyISAM的索引示意图：
5.常见问题
5.1InnoDB引擎通常需要整形自增主键的原因
如果存在整形自增主键，那么可以很方便地查找数据（充分使用B+树的特点）。
如果没有主键，就会选择unique列来建立索引（unique列是指列元素都不相同的列）；
否则，就创建一个隐藏列来建立聚簇索引（也叫做聚集索引）。（使用隐藏RowID）
5.2.通常，非主键索引的叶子结点要存放主键，便于回表
如，表student(id, age, name)建立主键索引和name索引，如下图：
回表：比如，在使用name作为索引查找该学生的age时，需要先查右边的索引（也就是name字段的索引），然后根据name索引得到的主键值，再到左边索引（主键索引）中查找该学生的age。此过程就是回表。
回表的定义：通俗的讲，如果select所需结果列中有非索引列，一次索引查询不能获取所有列的信息，需要到覆盖索引（主键索引）中找到相应的列的信息，这就叫回表。（一个索引无法查到所需的所有字段，需要利用另一个索引才能查找到完整数据）
而如果根据一次索引查询就能获得所有列的信息，就不需要回表。
5.3.建立索引的步骤（重要）
举例：对于表student(id,name,age,date,position)，可以建立如下联合索引（包含name，age，position）：
建立B+Tree索引的步骤（此步骤仅讨论建树时的字段顺序）：
1.先比较name字段，根据name字段进行排序，建立节点;
2.如果name字段相同，那么再比较age字段，然后建立节点；
3.如果age字段相同，再比较position字段，建立节点。
注意：索引的最左前缀原则与该图紧密联系。要牢记建立索引是从左到右的顺序，也就是说，如果建立联合索引，那么字段的比较就是从左到右。只有在左侧字段相同的情况下，才开始比较右边的字段。在查询的时候，如果查询语句中的where子句跳过某些字段，则会导致索引失效（也就是查询语句不满足最左前缀原则）。
如果索引了多列，SQL语句要遵守最左前缀法则。最左前缀法则指的是查询要从索引的最左前列开始并且不跳过索引中的列。（注意：要尽量使用覆盖索引，select语句尽量不使用select *，select *容易导致回表，因为*需要查询该表的所有字段，如果本次查询不会用到所有字段，则不要用select*，用到哪些字段就写哪些字段）
参考博客：
扫一扫
353
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
