MySQL b树与b+树，mysql索引等与数据的关系、命中多个索引走哪一个、为什么不建议is_del建索引

一、mysql页与磁盘页的概念

1、InnoDB存储引擎的最小储存单元——页（Page），一个页的默认大小是16K，可调。此时默认区是1M。

        【课外知识1：一个表有三个段：回滚段、非叶子节点段，叶子节点段。一个段=256个区=256x64个区】

        【课外知识2：每张表最开始添加数据时都是在碎片区里一页一页申请；当数据量达到32页时，就给这个段（一般先是叶子节点段达到标准）申请专属区，一个区一个区申请；当该段总区数达到32时，就会一次以4个区申请】

2、页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页的大小通常为4k）

3、磁盘或内存的每一页又被分为8个相等的扇区（最小单位）。每个扇区512byte。

4.扇区上又有磁道，怎么在磁盘上查询存取数据看这个链接（总结就是：除了因存储介质外：磁盘比内存速度慢的原因是多了【寻道时间+旋转时间】）

MySQL索引背后的数据结构及算法原理 - RunningSnail - 博客园

 5.由于操作系统【局部性原理】，会返回除所需要的目标数据之外紧接后面一定长度的数据。在innodb中就会演变成：比如我查id=5的数据，磁盘就会找到id=5所处的扇出，他会返回之后31个扇区。因为innodb一次查询至少是以一页（16k=32个扇区）为单位查询的。操作系统把16k的数据返给innodb，innodb就在内存中进行查找对比。

二、b树结构及查询

结构：

1.每一个节点（每一页）指针比关键字多1。

2.数据是存在关键字下。意思就是非叶子节点与叶子节点都有数据 

总结：

1.查询。比如查询20：经历2次io就可得到数据，在数据量少的时候并且是等值查询就很快。当数据多的时候就没得b+树快。因为同等大数据下b树的高度远远高于b+树。

2.b树排序慢，需要中序遍历。而b+树快，原因下面说。

三、b+树及查询

结构：

1.每个节点指针等于关键字

2.数据全部存放在叶子节点，且有序（因为是链表）；非叶子节点只存放关键字与指针，每一个非叶子节点的关键字都是它下面叶子节点的最小关键字（如下图的15，20，49。都是其子节点的最小关键字）。

 总结：

1.查询快，在两千万条数据的情况下查询20也只需要三次io。

2.三层就能存约2000w数据。每条数据1k（具体算法看下面）

3.可以进行快速范围查询，如（20-50）。因为子叶点上数据是个列表。直接遍历就是。

4.因为数据有序，排序很快且方便

四、三层b+树为啥可以存大概2000w数据

前提：假设数据大小为1k。在innodb中，指针为6字节，假设主键用的bigint（8字节）。那么，第一层（跟节点）最大能存16k/(6byte+8byte)个关键字。换算单位就是16384/14=1170。第二层最大能存1170x1170个节点（如果是两层b+树就只能能存放1170x16=18720条数据）。最后。第三层存放的数据就是1170x1170x16=21902400，

小知识：互联网大部分公司单条数据差不多都在1k左右。我们建表时也尽量单条数据不超过1k，不然超过了3层b+树就存不了这么多数据了。

五、为什么MySQL建议自增主键？页分裂是啥？

原因：每次新添加的数据都会在对应的某页的末尾添加，不会有数据的移动及重新排序（雪花id也是自增的）

 页分裂：页分裂就是插入主键不是有自增的，如uuid。每次添加数据之后。都会对整个主键索引树就行重新排序和数据移动。

页分裂完整回答：此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面

页分裂示意图：

随机写入，后一个数据页中的所有行并不一定比前一个数据页中的行的id大

 

六、innodb中数据是怎么落盘以及非子叶点是怎么来

链接：B树 是怎么存到硬盘上的？ - 知乎

【额外知识：要注意行溢出，就是blob，text，varchar这些字段太大，行会溢出，这行数据里会多字节指针指向溢出数据出。所以这就是为什么mysql建议这些大字段单独建一张表来存】

七、MyISAM与innodb对比

1.文件对比：

• MyISAM有三个文件：
  • .frm:表结构的定义文件
  • .MYD:数据文件
  • .MYI:索引文件
• InnoDB有两个文件：
  • .frm文件：存储为表结构文件
  • .ibd：存储的是数据和索引文件

2.其他对比

• MyISAM不支持事务，InnoDB支持事务
• MyISAM表解锁，InnoDB可以支持行级锁，并发更高
• MyISAM支持全文索引，InnoDB以前不支持，现在都支持了
• MyISAM表空间小，InnoDB表空间约大一倍
• MyISAM的主键b+树叶子结点存的是地址，InnoDB主键b+树叶子结点存的是数据

八、索引类别

从数据结构角度

1、B+树索引(O(log(n)))：关于B+树索引，可以参考 MySQL索引背后的数据结构及算法原理

2、hash索引：

        a 仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询

        b 其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引
        c 只有Memory存储引擎显示支持hash索引

3、FULLTEXT索引（现在MyISAM和InnoDB引擎都支持了）

4、R-Tree索引（用于对GIS数据类型创建SPATIAL索引）

从物理存储角度

1、聚集索引（clustered index）

Innodb引擎：叶节点存放一整行记录的索引叫聚集索引。

innodb中主键索引就是聚集索引（一个表只有一个聚集索引，因为一个表的物理顺序只有一种情况，或者说表中的记录在磁盘上的排列顺序只有一种情况，那就是主键索引，因为它记录（数据）顺序与索引顺序是一致的）。

2、非聚集索引（non-clustered index）

MyISAM引擎：叶子结点存放磁盘地址的索引叫做非聚簇索引

像这样规定是为了跟innodb区分

从逻辑角度

1、主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、普通索引或者单列索引

3、多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

4、唯一索引或者非唯一索引

5、空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创

九、索引一些规则及疑问

1、非聚集索引一定会回表吗？

答：不会，如果普通索引命中了就不会回表。

举例：如有一个user表（有id，name，age，sex，hight，weight，phone字段），对age字段建立索引idx_age。sql语句：select age from user where age>18。像这种，就不会回表。因为我只查询age这一个字段，而idx_age这个索引已经是包含所有age，且有序（b+树原理）。在这颗b+树上找到18这个关键字，向后遍历关键字就行。同时在这种情况下，此时这个索引也可叫“覆盖索引”，因为它此次不会回表

2、回表是什么意思？

答：回表就是拿到普通索引上的id到主键索引里找具体的数据。

举例：以上面那个user表为例，索引不变，sql语句不变。在idx_age索引的叶子结点里找到age>18的主键id，一个一个拿到主键索引里去查找对应的数据。这种就叫回表。如果没建立idx_age索引，直接执行上面sql，这叫全表扫描。

3、联合索引是什么？为什么联合索引要注意顺序？

答：联合索引就是一个索引里包含了多个字段。还有为啥要注意顺序在举例中说明

举例：还是上面那个user表，此时对age、sex、hight、weight四个字段建了一个名为idx_age_sex_hight_weight 的联合索引。

SQL1：select * from user where age=15（走索引）

SQL2：select * from user where sex=1（1代表性别男）（不走索引）

SQL3：select * from user where hight=180（不走索引）

SQL4：select * from user where weight=70（不走索引）

为什么不走？因为联合索引有个最左前缀原则（从建立联合索引顺序，从左开始匹配），必须要先命中age。

SQL5：select * from user where sex=1 and age=15（像这种就走索引，命中age跟sex，写的sql语句可以不管顺序，因为mysql优化器会帮我们把age=15优化到前面）

SQL6：select * from user where sex>0 and age=15 and hight=180（走索引，但只命中age跟sex，不会命中hight）

为什么不会命中hight？因为联合索引还有另一个特性，当遇到<,>,like等，就不匹配后面的了

4、联合索引有个特别优势

举例：查询年龄等于15岁。并按升高从低到高升序

一般SQL：select * from user where age=15 order by hight （只建立idx_age索引）

优势SQL：select * from user where age=15 （建立idx_age_hight联合索引索引，不用排序，因为已经联合索引已经排好序了）

5、索引下推是什么？

上面第三问有个尾巴没解决，当遇到>,<，like这之后的的字段就没用了吗？必须在此步回表查询筛选吗？

如SQL6：select * from user where sex>0 and age=15 and hight=180。

mysql5.6之前，联合索引命中到sex>0的索引后先回表，再遍历进行hight=180的筛选。

mysql5.6之后引入了索引下推。联合索引命中到sex>0的索引后先不忙回表，先在联合索引这边获取到sex>0的索引，筛选满足hight=180索引，最后再回表查询

6、前缀索引是什么？

十、生产过程中遇到的问题

1.sql语句命中两个索引，mysql选择走哪个？

答：走数据量小的那个（没探究原理）

生产环境测试：如idx_status索引命中的status=1的数据量是20万条，idx_area索引命中的area=519的的数据量是一万条，查看执行计划，发现走的是area索引（idx_area索引建立的顺序在idx_status下面,根本不是网上有些人说的谁在前用谁）。

根据以上学的知识猜测：mysql的二级索引最后叶子结点上除了存放着主键id之外，还有一个字段存储了当前索引关键字（status=1/area=519）的总数据条数。它发现area数据量小，就走idx_area索引。

2.为什么不建议对is_del等字段建立索引？

如上面的status。在真实环境只有0,1,2三种状态。处于status=1的就有20万条。如果对其建立索引，就出现叶子结点向下分页。原因是我们都知道mysql一页是16k，二级索引叶子结点存的主键，如果主键的bigint（8字节），指针（6字节）。那么一个数据页只能存放16x1024/(8+6)=1074个主键。20万个主键存不下怎么办，mysql只能像建索引树一样，对这20万个主键分裂成的200000/1074=186个数据页建立像b+树一样的索引页。如下图：

结论：所以为什么is_del,status等字段，不适合建立索引了吧！但是也不绝对。比如当你的status=1时数据量为20万条，staus=2时数据量为500条，如果你想追求速度极致，可以对其建立索引，在SQL语句判断条件为status=2时，用use index(idx_status)或者force index(idx_status)走idx_status。其他判断条件如status=1时，用ignore index(idx_status)忽略这个索引。

3.NULL列会影响索引吗，为什么不建议有空值？

结论：

1、如果你没对如name字段做不为空的限制，那么where条件【name is null】会走索引，如果你建表时已经对name字段做了不为空限制，并且空字符串填充。那么name is null不走索引，因为MySQL已经知道该列绝对不会出现null，所以他连name索引都懒得扫描，直接返空集合给你。

2、is not null不会走索引。跟not in()不走索引一个理

3、因为null是一个特殊的值，不能进行=,<,>这样的运算符，对null做算术运算的结果都是null，count时不会包括null行等，null比空字符串需要更多的存储空间等。