mysql查询全表比加上搜索条件更快_Mysql索引数据结构详解及性能调优

以前学习了不少东西，都忘了不少，最近就想着总结一下，就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况。

如果觉得好看，请给个赞

你有一个思想，我有一个思想，我们交换后，一个人就有两个思想

If you can NOT explain it simply, you do NOT understand it well enough

简单介绍下这篇文章的流程

1.为什么使用索引

A：那还用说，肯定在某些方面有特长呗，比如你知道的【它很快，非常快】



我也很赞同这个答案，但说的不够具体，你得说明它为啥这么快

如果问你选择索引的原因就是一个【快】字，面试也就不会出那么多幺蛾子了。你有没有问过你自己

索引在所有场景下都是快的吗？

知道它很快，何为快？怎样度量？

索引(翻译官方文档)是帮助MySQL高效获取数据的排好序的数据结构

拿汉语字典的目录页(索引)打比方，我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

2.索引数据结构详解

在创建索引时，通常采用的数据结构有：Hash、二叉树、红黑树、B树以及B+树 可以在线查看数据结构的网页 Data Structure

二叉树:

定义规则为左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树

要是索引采取这种结构，数值递增那种，就要满足右边节点值比根节点大，导致检索数据会导致查了6遍磁盘

红黑树：

(在jdk8之后，用数组+链表+红黑树来实现hashmap，当碰撞的元素个数大于8时 & 总容量大于64，会有红黑树的引入。)红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。(右边的树的高度不会大于左边树的高度超过1，大于等于1级后会自动平衡，自己可在数据结构上插入试试)

特点：

节点是红色或者黑色

根节点是黑色

每个叶子的节点都是黑色的空节点(NULL)

每个红色节点的两个子节点都是黑色的

从任意节点到其每个叶子的所有路径都包含相同的黑色节点

优点：解决二叉搜索树的极端情况的退化问题。

缺点：检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就会比较多，检索的时间会比较久，效率低。

从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree就在这样伟大的时代背景下诞生了

B-Tree

基于以上进行扩容，每个横向的节点变多了意味的存放的数据变多了，整个树的高度也变小了，减少磁盘io的搜索速度

特点

1.叶节点具有相同的深度，叶节点的指针为空

2.所有索引元素不重复

3.节点中的数据索引从左到右递增排列

缺点：可以看到存放的数据类似key+value 的数据 要是InnoDB 的话data可能存放的是除了索引外的字段

页节点mysql默认推荐的是16k大小 ( show global status like 'Innodb_page_size';)，假如大节点的每个节点的data存的数据比较大，那么意味着横向能存储的索引就会变很少，大节点的能存储的索引变少意味着整颗树的高度受到限制

B+Tree

(B-Tree变种 MySql默认使用索引结构)

1.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引

2.叶子节点包含所有索引字段

3.叶子节点用指针连接，提高区间访问的性能 (快速定位范围查询，例如查询大于20，第一次io从根节点查询三次定位到20，然后通过后面的指针查询大于20的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的)

Hash

前面说的mysql默认索引结构是B+Tree，还有一种索引结构是Hash

如果是hash 的话是通过 hash(值)运算然后在磁盘中快速查找对应的磁盘文件指针从而找到行数据

hash 索引查数据是很方便也快的，但是不支持范围性查找 例如 >= < between and 也不支持排序

Hash索引适合等值查询 ，不适合范围查询

总结

为什么mysql索引结构默认使用B+Tree

为什么mysql索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？

B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少(有些资料也称为扇出)，指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；

Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。

二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关(树的高度)，并且IO代价高。

红黑树：树的高度随着数据量增加而增加，IO代价高。

索引是如何支持千万级表的快速查询

索引可以把它想象跟旁边的指针的成对存在的(指针是指向下一个节点的磁盘位置(占用6字节))



索引假设字段为数字类型 Bigint 8b+ 指针默认占用空间6b = 14b (索引跟旁边的指针的成对存在的大小总和)

大节点能存放 16kb数据 那么最多能存放 16kb * 1024/ 14= 1170个索引



假设叶子节点每个元素存放1kb大小，总共16kb ，那么叶子节点能存放16个元素，那么 高度h = 3 的B+Tree 叶子节点能存放的元素为

1170*1170*16 = 2100w多 (2000多w的数据只需访问磁盘io三次)

3.聚集索引与非聚集索引

聚集(clustered)索引，也叫聚簇索引

定义：数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同，一个表中只能拥有一个聚集索引。

注：第一列的地址表示该行数据在磁盘中的物理地址，后面三列才是我们SQL里面用的表里的列，其中id是主键，建立了聚集索引。

数据行的物理顺序与列值的顺序相同，如果我们查询id比较靠后的数据，那么这行数据的地址在磁盘中的物理地址也会比较靠后。而且由于物理排列方式与聚集索引的顺序相同，所以也就只能建立一个聚集索引了。

InnoDB索引实现(聚集索引)

(可以看到InnoDB 的索引跟表数据全部放在一起 .frm 存的是表结构的定义文件 .ibd 存取索引跟表数据)

InnoDB表数据文件本身就是按B+Tree组织的一个索引结构文件

聚集索引-叶节点包含了完整的数据记录

为什么InnoDB表必须有主键，并且推荐使用整型的自增主键？

(如果没有主键，mysql会优先查找有唯一数据的字段当主键，没有的话会在后台新增row_id来标识主键)

结合B+Tree的特点，B+Tree组织是一个索引结构文件，所以需要一个主键元素来充当组织数据的角色，自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分。并且能减少数据的移动，每次插入都是插入到最后。总之就是减少分裂和移动的频率。如果是uid的话，会进行字符串比较大小，相比之下肯定是数字比较大小会比较快。

非聚集(unclustered)索引

定义：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

其实按照定义，除了聚集索引以外的索引都是非聚集索引，只是人们想细分一下非聚集索引，分成普通索引，唯一索引，全文索引。如果非要把非聚集索引类比成现实生活中的东西，那么非聚集索引就像新华字典的偏旁字典，它结构顺序与实际存放顺序不一定一致。

非聚集索引叶节点仍然是索引节点，只是有一个指针指向对应的数据块，此如果使用非聚集索引查询，而查询列中包含了其他该索引没有覆盖的列，那么他还要进行第二次的查询，查询节点上对应的数据行的数据。

MyISAM索引文件和数据文件是分离的(非聚集)

frm 存的是表结构的定义文件 ，MYD 存取的所有数据行， MYI 存取的表的索引字段

例如查找 col1 = 18 的数据 先从MYI 中找到索引所在磁盘文件指针然后定位到MYD 找到col1 = 18那行的数据

为什么非主键索引结构叶子节点存储的是主键值？(一致性和节省存储空间)

主键索引

非主键索引

查找Alice 找到主键18 再去主键索引查找整行的数据

(非主键索引 与 单值索引的null 不会存储在非叶节点，会放在叶子节点最左边上)

例如在叶子节点最左边上开辟一个空间存放

联合索引 多个字段联合起来(索引最左前缀原理—> 索引优化重点)

联合索引也是排好序的数据结构 例如(a,b,c)依次从左到右排序 也就是先排好a，再b，再到c

对于有些索引字段为空的情况也是排在最左边

例如a 相等 ，b为空， c相等

4.mysql执行计划Explain使用

Explain 工具介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈 在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返 回执行计划的信息，而不是执行这条SQL
注意:如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

示例表

DROP TABLE IF EXISTS `singer`;

CREATE TABLE `singer`(

`id` int(11) NOT NULL,

`name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `singer` (`id`,`name`,`update_time`) VALUES (1,'周杰伦', NULL),(2,'林俊杰', NULL),(3,'五月天', NULL);

explain select * from singer;

DROP TABLE IF EXISTS `song`;

CREATE TABLE`song`(

`id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`),

KEY `idx_name` (`name`)

)ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO`song`(`id`,`name`)VALUES(3,'晴天'),(1,'曹操'),(2,'突然好想你');

DROP TABLE IF EXISTS`