Mysql中（你知道为什么用Select*查询很慢吗）

程序员文章站 2024-03-16 20:58:34

...

Mysql底层篇(大约2万字，可能需要20分钟)

引言:当有人问你为什么select * 查询慢的时候，不如用索引给他解释清除。

select * from xxdb where xx>1;

假设数据库有1000条数据

这是一道我们常用的sql语句，但是在我初学的时候，我是很天真的，我以为直接在硬盘中就对这条数据进行比较，然后再显示给用户。其实真尴尬。。。

大家可能没有这种误区吧，但是我还是在这里说明一下，当执行这条最基本的sql语句时，其实是先将我们存储在磁盘上的这1000条数据加载到内存中去的，然后cpu再和这1000条数据进行一个一个比较。听着是不是觉得很佩服cpu的执行能力。没错，是这样的，但是这里存在了一个很大的问题，就是效率问题，加入我现在执行这样一条语句：

select * from xxdb where xx=10000;

假设数据库仍然有1000条数据，并且要查取的这个字段位于数据库中最后一条。（极端情况）

那么cpu就要执行1000次比较运算，才能将我们需要的结果给查询到。这样就又将计算机显得很撒刁了，我们可能这样想，要是我们人的话，我们一眼就能在第1000行找到这条数据，计算机为什么这么智障呢。当然，计算机它并不是人也没有所谓的眼睛，它是看不到需要的数据到底位于哪个准确的位置，因此他就需要一条一条去查，一次一次比较。因此当数据量十分大的情况下，这个效率就相当底了。那么我们能不能对众多的数据进行分块呢？？？

1.Mysql的局部性原理

在InnoDB中，数据会存储到磁盘上，在真正处理数据时需要先将数据加载到内存，表中读取某些记录时， InnoDB存储引擎不需要一条一条的把记录从磁盘上读出来，InnoDB采取的方式是：将数据划分为若干个页，以页作为磁盘和内存之间交互的基本单位，InnoDB中页的大小一般为 16 KB（16384个字节），也就是说，当需要从磁盘中读数据时每一次最少将从磁盘中读取16KB的内容到内存中，每一次最少也会把内存中的16KB内容写到磁盘中。

2.页的结构

2.1页是设么？

页是InnoDB管理存储空间的基本单位，一个页的大小默认是16KB

查看命令：

SHOW GLOBAL STATUS like 'Innodb_page_size';

2.2页的结构

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-J6gZ0UHV-1605686886289)(image-20201114175439412.png)]

各个字段说明：

名称名称	中文名中文名	占用空间占用空间	简单描述
File Header	文件头部	38字节	页的一些通用信息
Page Header	页面头部	56字节	数据页专有的一些信息
Infimum + Supremum	最小记录和最大记录	26字节	两个虚拟的行记录
User Records	用户记录	不确定	实际存储的行记录内容
Free Space	空闲空间	不确定	页中尚未使用的空间
Page Directory	页面目录	不确定	页中的某些记录的相对位置
File Trailer	文件尾部	8字节	校验页是否完整

InnODb行格式

一行记录可以以不同的格式存在InnoDB中，行格式分别是Compact、Redundant、Dynamic和Compressed行格式。

compact行格式

Mysql中（你知道为什么用Select*查询很慢吗）

记录的额外信息

这部分信息是服务器为了描述这条记录而不得不额外添加的一些信息，这些额外信息分为3类，分别是：变长字段长度列表 NULL值列表记录头信息

变长字段长度列表

MySQL支持一些变长的数据类型，比如VARCHAR(M)、VARBINARY(M)、TEXT类型，BLOB类型，这些数据类型修饰列称为变长字段，变长字段中存储多少字节的数据不是固定的，所以我们在存储真实数据的时候需要顺便把这些数据占用的字节数也存起来。在Compact行格式中，把所有变长字段的真实数据占用的字节长度都存放在记录的开头部位，从而形成一个变长字段长度列表。

CHAR是一种固定长度的类型，VARCHAR则是一种可变长度的类型。 VARCHAR(M)，M代表最大能存多少个字符。( MySQL5.0.3以前是字节，以后就是字符)

Null值列表

Compact行格式会把可以为NULL的列统一管理起来，存一个标记为在NULL值列表中，如果表中没有允许存储 NULL 的列，则 NULL值列表也不存在了。

二进制位的值为1时，代表该列的值为NULL。

二进制位的值为0时，代表该列的值不为NUll.

记录头信息

除了变长字段长度列表、NULL值列表之外，还有一个用于描述记录的记录头信息，它是由固定的5个字节组成。 5个字节也就是40个二进制位，不同的位代表不同的意思，如图：

名称名称	大小（单大小（单位：位：bit））	描述
预留位1	1	没有使用
预留位2	1	没有使用
delete_mask	1	标记该记录是否被删除
min_rec_mask	1	B+树的每层非叶子节点中的最小记录都会添加该标记
n_owned	4	表示当前记录拥有的记录数
heap_no	13	表示当前记录在记录堆的位置信息
record_type	3	表示当前记录的类型，0表示普通记录，1表示B+树非叶子节点记录，2表示最小记录，3表示最大记录
next_record	16	表示下一条记录的相对位置

记录的真实数据

记录的真实数据除了我们自己定义的列的数据以外，还会有三个隐藏列：

列名列名	是否必须是否必须	占用空间	描述描述
row_id	是	6字节	行ID，唯一标识一条记录
transaction_id	是	6字节	事务ID
roll_pointe	是	7字节	回滚指针

实际上这几个列的真正名称其实是：DB_ROW_ID、DB_TRX_ID、DB_ROLL_PTR。 8M-'U 8MC,>m 一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

行溢出数据

VARCHAR(M)类型的列最多可以占用65535个字节。其中的M代表该类型最多存储的字符数量，如果我们使用 ascii字符集的话，一个字符就代表一个字节，我们看看VARCHAR(65535)是否可用：

mysql> CREATE TABLE varchar_size_demo(
 -> c VARCHAR(65535)
 -> ) CHARSET=ascii ROW_FORMAT=Compact;
ERROR 1118 (42000): Row size too large. The maximum row size for the used table type,
not counting BLOBs, is 65535. This includes storage overhead, check the manual. You
have to change some columns to TEXT or BLOBs
mysql>

报错信息表达的意思是：MySQL对一条记录占用的最大存储空间是有限制的，除BLOB或者TEXT类型的列之外，其他所有的列（不包括隐藏列和记录头信息）占用的字节长度加起来不能超过65535个字节。这个65535个字节除了列本身的数据之外，还包括一些其他的数据，比如说我们为了存储一个VARCHAR(M)类型的列，其实需要占用3部分存储空间：

真实数据
变长字段真实数据的长度
NULL值标识

如果该VARCHAR类型的列没有NOT NULL属性，那最多只能存储65532个字节的数据，因为变长字段的长度占用 2个字节，NULL值标识需要占用1个字节。

mysql> CREATE TABLE varchar_size_demo(
 -> c VARCHAR(65532)
 -> ) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)
(yA>m
CREATE TABLE varchar_size_demo(
 c VARCHAR(65533) not null
) CHARSET=ascii ROW_FORMAT=Compact;
Query OK, 0 rows affected (0.02 sec)

记录中的数据太多产生行溢出

一个页的大小一般是16KB，也就是16384字节，而一个VARCHAR(M)类型的列就最多可以存储65533个字节，这样就可能出现一个页存放不了一条记录。在Compact和Reduntant行格式中，对于占用存储空间非常大的列，在记录的真实数据处只会存储该列的一部分数据，把剩余的数据分散存储在几个其他的页中，然后记录的真实数据处用20个字节存储指向这些页的地址（当然这20个字节中还包括这些分散在其他页面中的数据的占用的字节数），从而可以找到剩余数据所在的页。

Dynamic和Compressed行格式

这两种行格式类似于COMPACT行格式，只不过在处理行溢出数据时有点儿分歧，它们不会在记录的真实数据处存储一部分数据，而是把所有的数据都存储到其他页面中，只在记录的真实数据处存储其他页面的地址。另外， Compressed行格式会采用压缩算法对页面进行压缩。

3.B+树的形成

从上面我们知道一个表没有手动定义主键，则会选取一个Unique键作为主键，如果连Unique键都没有定义的话，则会为表默认添加一个名为row_id的隐藏列作为主键。所以row_id是在没有自定义主键以及Unique键的情况下才会存在的。

假设我现在有几条这样的数据，并且以Id作为主键,这里主键是每一行的第一个数字。

insert into t2 values(4,3,1,1,‘d’);

insert into t2 values(1,1,1,1,‘a’);

insert into t2 values(8,8,8,8,‘h’);

insert into t2 values(2,2,2,2,‘b’);

insert into t2 values(5,2,3,5,‘e’);

insert into t2 values(3,3,2,2,‘c’);

先开辟一页16k的大小，开始按照顺序插入。
Mysql中（你知道为什么用Select*查询很慢吗）

2.开始插入（2,2,2,2,‘b’）这条记录当插入这条记录的时候，在页的里面需要调整数位置了因为我们是按照主键排序，需要进行指针的切换，然后将这条数据插入。

Mysql中（你知道为什么用Select*查询很慢吗）

其实我们可以发现如果这样做排序，对插入是非常有影响的，因为当每次顺序不一致时，都需要变换指针将其调整为顺序结构。但是一旦将做成顺序之后，对查询将会有很大帮助。并且我们知道大多数业务中，客户的查询是最高的。MySql就利用这一点。

假设现在想要查询id=3。那么这样设计完之后Mysql是如何帮我们查找的呢？？？

先比较第一条记录的Id值发现<3
接着比较第二条记录的Id值发现仍然<3
接着继续比较第三条记录的Id值，这时发现>3.

这时通过比较虽然没有找到id=3的这条记录，但是在比较打破第三条记录结束之后，就不会再往下继续比较了。

这时我们可能想这四个字段Id假设我没有将其进行排序，我也只用了四次就能查到，不就比你这排完序的多一次吗？

对，当其数据少的时候，这个是相差不大的。但是一旦数据多的时候，你可以大胆想象一下他俩的差距有多大。

这样做其实也会产生问题，比如说我们一张表中只有一个int类型的字段，那么区存储这一行占用的空间就很小，

那么一页中就能存储很多行，我在这里举个例子，比如说之前一行记录有10个字段，一页中16kb可以存储100行这样的记录，那么现在由于我一行的记录空间占用少了，同样16kb的页面可能存放200行的记录。那么问题就被我带出来了，因为行数一旦增多，那么这个行链表就变得很长。链表很长的时候，就会给查询带来很大的麻烦，效率会变得很低。那么我们Mysql的innodb是怎么做的呢？

3.1页目录

为了解决上面提到的问题，Mysql的innodb引擎在一页中有一块空间叫做页目录Page Directory。那这个页目录这到底用来干什么的呢？
Mysql中（你知道为什么用Select*查询很慢吗）

首先会对这些记录进行分组，比如我在这里将这四条记录分为两组，分别用不同的颜色表示，然后再页目录里在存放

每一组的起始主键。然后通过每组的起始指针指向具体的记录数，当确定在具体的哪一组之后，然后在这个组的记录数里去遍历其它，这样就会避免了全局扫描这个链表，效率会带来很大提升。

Mysql中（你知道为什么用Select*查询很慢吗）

这个分组该怎么理解？

其实我们可以用从一本书中查找一个具体的内容来做例子：

加入我们要在一本书中找一个具体的内容，我想我们没有一个人去一页一页查找这本书，当然这样也肯定是能找到，但是你找到的时间肯定要很长对吧。其实在这本书一出版时，人家已经对这本书的具体内容做了分类，比如这本书一共分了8章，第一章主要讲的是什么，并且这一章是从x页到y页，那么这本书就被分了8组，我们如果通过这种目录的方式去查找，查找的时间一定比之前要快的多。那么这里面其实x到y页我们就可以理解成将其分为一组或者叫一章。

由于我们的主键是顺序的，并且在页目录中是将每组的第一条记录的Id存放到页目录中，那么在这个页目录中它的排列也会是顺序的。

因此在以后的查找中，就会先从页目录里找。由于我们的页目录中存放的Id是每组的最小Id但是上一组的最大Id,因此查找的时候也会有规律，比如我们现在要查找id=5的，我们是这样找的。

先去页目录的第一个找发现5>1。
接着就会去目录中找第二个发现5>4。
然后再去目录中找第三个发现5<10。那么就会去第二个目录所对应的记录中找。

这样做就缩小了搜索的范围了。

其实我们在一去想，我们现在还是要一个一个去查找这个有顺序的目录，那么我们在用这个思想能不能在进一步去优化呢？说白了我们现在其实就是从[1,4,10,20]中查找5位于哪里？那么既然是这样，不是还可以优化这个页目录吗。没错，这种查找的优化就是我们的二分法，也叫折半查找。而二分法最大的特点就是一组数据要保持有序。

初次之外，由于一页的大小是16kb,那么当数据多的时候，一页总会用满，那么肯定会开辟多个页来存储这些数据。

假设我规定每一页只存储4条记录，那么这八条记录就要分两页来存储。

Mysql中（你知道为什么用Select*查询很慢吗）

3.2目录页

存放页的目录+

那么数据一旦分为不同的页之后，我们如果查询的数据不再某一页，我们该怎么找呢？比如我现在查找Id=5的记录，我们是不是要在第二个图中找，没错，这是我们人眼看到的，但是计算机该怎么找呢，你要想找到另外一页，那么你必须要告诉计算机这一页在哪里，我才能去你给我的地方找你，对吧。因此我们在每一页中记录当前的页码，然后innoDB会在上方在开辟一页叫做目录页（这里我说明一下，这个新开辟的空间）肯定不是是在上面，因为内存中压根就不存在上下这种空间结构，只是我们逻辑上称作为上方。

Mysql中（你知道为什么用Select*查询很慢吗）

当然页之间也是不相邻的，页中会有一个一个指针指向下一个页的地址，因此只是逻辑上相邻的。

但是目录页中光存页码是不够的，比如我查询id=5的记录，我现在仍然不知道在哪一页，如果你仔细阅读完上面我写的

之后，其实你可以想到如何解决这个问题，我们仍然借助上面的思想，在目录页中我们仍然存放每一页的最小主键。就像这样。

Mysql中（你知道为什么用Select*查询很慢吗）

那么假设我现在要查询Id=7

直接在目录页中查询7>1
然后再目录页中下一个查询7>5 那么我就不会再第100页中查找了。
直接在页码为200的查找，然后在里面再按照上面查找的方式查找具体的记录。

针对上面的查找我们又加快了查询效率，并且也即是上面说的对页目录优化采用二分法思想。因此提取出来的目录页就是优化的结果。

那么我们仔细看哈，这种结构像啥，是不是想像一颗树呀，没错这就是一颗B+树。我把上面的图像给给抽象出来

Mysql中（你知道为什么用Select*查询很慢吗）

如果你还不知道B+树到底长啥样，我在这里提供一个网址可以模拟出B+树的形成：

https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

因此我们常说InnoDb引擎是B+树结构，到底数据是如何形成的，就是像上面一步一步形成的。我想看到这里应该对B+树的形成有了一个大致的了解。

而上面这种按照顺序对主键建立的索引就是我们的聚集索引。聚集索引最大的特点就是：

行在磁盘的排列和索引排序保持一致。

聚簇索引的好处：

按照聚簇索引排列顺序，查询显示一定范围数据的时候，由于数据都是紧密相连，数据库不用从多个数据块中提取数据，所以节省了大量的io操作。

聚簇索引的限制：

对于MySQL数据库目前只有innodb数据引擎支持聚簇索引，而Myisam并不支持聚簇索引。

由于数据物理存储排序方式只能有一种，所以每个MySQL的表只能有一个聚簇索引。一般情况下就是该表的主键。

为了充分利用聚簇索引的聚簇的特性，所以innodb表的主键列尽量选用有序的顺序id，而不建议用无序的id，比如uuid这种。

刚刚是对一个id字段建立索引，那么如过我现在对（b,c,d）这三个字段建立索引，该怎么办呢？一个字段很好理解，但是当多个组合在一起如何排？

我们知道如果我们对主键建立索引，那么innodb将会按照主键进行排序，那么当有多个字段时，innodb也会根据一定规则进行排序

比如我们经常用多个字符进行比较，比如a,b,c/a,d,c：

首先先比较第一个字符，如果第一个相等，接着比较第二个字符。
第二个字符如果相等，则比较第三个字符，如果第二个字符不相等，若b<d.那么就返回第一组字符小于第二组字符。
当多组有多个字符同理排序…

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-isBx5MTT-1605686886305)(image-20201116205128165.png)]

接下来我就以（b,c,d）三个字段作为复合索引。

Mysql中（你知道为什么用Select*查询很慢吗）

这是以（b,c,d）三个字段作为组合索引形成的树，知道了多个字段的排序原则，那么它的形成和上面单个索引道理相同，但是这里面可能还是有点小疑问就是叶子节点的红色部分，这个部分我特意给标红了，那么它代表的什么意思呢，

由单个索引创建的过程我们知道，叶子节点存放的都是数据和索引主键，没毛病，比如111,222,235…这些是我们建立的复合索引，但是下面的1,2,5是什么鬼呢，不是应该存放每一条实际的记录吗？对，本来是应该这样存的，但是如果想我们对一张表中建立多个索引的时候，那么每个叶子节点都要存放一份数据，那么这样不仅显得数据十分冗余，更会占用更大空间。因此Innodb它将除了主键索引之外的索引中叶子节点存放的是当前索引这条记录的id,而不是整个记录，这样就会节约一部分页的空间大小，这个存储这条记录的id值也叫做辅助索引。

3.3辅助索引

那么既然Mysql这样搞个辅助作用，除了能够节约页的空间大小之外，当然还有别的作用，这个辅助索引也可以帮我们查找。由于我的真正记录并没有在辅助索引这个树里面，那么我如果想查找id=5的这条记录，我怎么才能把这条记录查出来呢，因为我组合索引这棵树存的叶子节点存放的是辅助索引，那么我是不是能通过辅助索引去查找我真实的数据所在地方呢。对，是这样的，当建立好了组合索引后，它会通过一步一步查询到本棵树的叶子节点处，然后在去另外一颗树（主键索引）查询真实的数据，这个过程也叫回表操作。

3.4多种索引查询

比如现在我有这样一条语句：

seslect * from t1 where b=1 and c=1 and d=1

这条sql语句其实很简单，就是查询t1表的所有数据，由于我建立了一个组合索并且还有innodb默认的主键索引，因此它的查询方式有很多种：比如根据辅助索引+组合索引方式查询，也可以直接根据主键索引查询，但是到底根据哪种查询方式，页就是说用explain关键字看到的结果，其实是innodb优化器优化后认为查询效率最高的结果。就好像我们从深圳宝安区到罗湖区，我们打开高德地图可以看到中间有很多方式到达目的地，但是高德会根据它自己的算法，给你推荐时间最短的走法。现实中的高德就相当于mysql中的优化器。

但是也不是说加了索引查询一定会变快

辅助索引一定比全变扫描快吗？

比如我数据库现在id有1,2,3,4,5,6 而我现在查询的条件是where id>0.那么如果我辅助索引的话，我需要将组合索引这棵树的叶子节点都查询一遍，然后再走辅助索引这颗树，将所对应的真实数据查询出来。因此需要走两次。但如果我直接全表扫描的话我直接走主键索引，只需要一次查询主键索引这颗树就可以查询到真实数据，只需要走一次。

结论：通过辅助索引找出来的主键个数已经超过整个表的主键值得80%的时候，那么这时候查询优化器就会走全表扫描。

3.5索引的代价

3.5.1空间上的代价

一个索引都为对应一棵B+树，树中每一个节点都是一个数据页，一个页默认会占用16KB的存储空间，所以一个索引也是会占用磁盘空间的。

3.5.2时间上的代价

索引是对数据的排序，那么当对表中的数据进行增、删、改操作时，都需要去维护修改内容涉及到的B+树索引。所以在进行增、删、改操作时可能需要额外的时间进行一些记录移动，页面分裂、页面回收等操作来维护好排序。

3.6B+树索引实战

3.6.1全值匹配

select * from t1 where b = 1 and c = 1 and d = 1;

建立索引前：

Mysql中（你知道为什么用Select*查询很慢吗）

建立索引（b,c,d）后：

Mysql中（你知道为什么用Select*查询很慢吗）

3.6.2匹配最左前缀法：

select * from t1 where b = 1;
select * from t1 where b = 1 and c = 1;

Mysql中（你知道为什么用Select*查询很慢吗）

下面这个sql是用不到索引的

select * from t1 where c = 1;

因为B+树先是按照b列的值排序的，在b列的值相同的情况下才使用c列进行排序，也就是说b列的值不同的记录中c的值可能是无序的。而现在你跳过b列直接根据c的值去查找，这是做不到的。

3.6.3匹配最前列

如果只给出后缀或者中间的某个字符串，比如：

select * from t1 where b like '%101%';

这种是用不到索引的，因为字符串中间有’101’的字符串并没有排好序，所以只能全表扫描了。有时候我们有一些匹配某些字符串后缀的需求，比方说某个表有一个url列，该列中存储了许多url：

www.baidu.com

www.gogle.com

www.qq.com

假设已经对该url列创建了索引，如果我们想查询以com为后缀的网址的话可以这样写查询条件：WHERE url LIKE ‘%com’，但是这样的话无法使用该url列的索引。为了在查询时用到这个索引而不至于全表扫描，我们可以把后缀查询改写成前缀查询，不过我们就得把表中的数据全部逆序存储一下，也就是说我们可以这样保存url列中的数据：

moc.udiab.www

moc.elgog.www

moc.qq.www

这样再查找以com为后缀的网址时搜索条件便可以这么写：WHERE url LIKE ‘moc%’，这样就可以用到索引了。

3.6.4匹配范围值

select * from t1 where b > 1 and b < 8

由于B+树中的数据页和记录是先按b列排序的，所以我们上边的查询过程其实是这样的：

找到b值为1的记录。
找到b值为20000的记录。
由于所有记录都是由链表连起来的（记录之间用单链表，数据页之间用双链表），所以他们之间的记录都可以很容易的取出来找到这些记录的主键值，再到聚簇索引中回表查找完整的记录。
不过在使用联合进行范围查找的时候需要注意，如果对多个列同时进行范围查找的话，只有对索引最左边的那个列进行范围查找的时候才能用到B+树索引，比如：

select * from t1 where b > 1 and c > 1;

上边这个查询可以分成两个部分：

通过条件b > 1来对b进行范围，查找的结果可能有多条b值不同的记录，
对这些b值不同的记录继续通过c > 1继续过滤。

这样子对于联合索引来说，只能用到b列的部分，而用不到c列的部分，因为只有b值相同的情况下才能用c列的值进行排序，而这个查询中通过b进行范围查找的记录中可能并不是按照c列进行排序的，所以在搜索条件中继续以 c列进行查找时是用不到这个B+树索引的。

而对于这条sql语句是可以用到索引的：
```
select * from t1 where b = 1 and c > 1;
```
解释一下：

因为现在是对b,c,d建立的组合索引，那么b,c,d一定是有序的，比如（1,1,1），（1,2,2），（1,2,3）那么如果b=1后，c一定是顺序排列的。所以这时候在用从c>1，那么这个索引就起到作用了。但是如果b>1 那么结果可能是（2,1,1），（2,2,1），（3,1,1）很显然c的排列是起不到作用的，因为c的排列是无序的。

3.6.5排序

 explain  select * from t1  order by b,c,d

Mysql中（你知道为什么用Select*查询很慢吗）

虽然我建立了一个组合索引（b,c,d）但是为什么用优化器查询出来依然是全表呢？按道理说，我对这三个建立好了索引之后，在来排序，就会将建立好的那棵B+树结构直接返回。确实是这样的，但是由于我现在是select * ，因此还有a.e字段都需要查询，很明显我并没有对这些字段建立索引，如果我想查找它们，那么我就需要进行回表查询，并且用cpu进行对b,c,d排序。同样，当我查询的字段在索引之中，那么我就直接在索引表里面获取到数据，而不用进行再去回表。

explain  select b from t1  order by b,c,d
explain  select c from t1  order by b,c,d
explain  select d from t1  order by b,c,d

Mysql中（你知道为什么用Select*查询很慢吗）

那么当我执行这样一条sql的时候，我们可以看到用到了系统内部排序。

explain  select b from t1  order by c,d

Mysql中（你知道为什么用Select*查询很慢吗）

并没有完全用到索引排序，是因为我的索引树中组合索引是（b,c,d）排序的，而c,d的顺序并不是有序的，因此你根据c，d来排序，是用不到组合索引的。但是这条sql可以优化。比如像这样:

explain  select b from t1 where b=1 order by c,d

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-d7heiBDO-1605686886313)(image-20201117224207518.png)]

当b这个值固定之后，那么我的c,d肯定都是顺序，比如可以这样，（1,1,1）（1,1,2），（1,2,1），（1,2,2）。那么我们就可以用到了索引，就不会再走系统排序。

3.6.6分组

select b, c, d, count(*) from t1 group by b, c, d;

这个查询语句相当于做了3次分组操作：

先把记录按照b值进行分组，所有b值相同的记录划分为一组。
将每个b值相同的分组里的记录再按照c的值进行分组，将title值相同的记录放到一个分组里。
再将上一步中产生的分组按照d的值分成更小的分组。
如果没有索引的话，这个分组过程全部需要在内存里实现，而如果有索引的话，正好这个分组顺序又和B+树中的索引列的顺序是一致的，所以可以直接使用B+树索引进行分组。

3.6.7使用联合索引进行分组或者排序注意事项

对于联合索引有个问题需要注意，ORDER BY的子句后边的列的顺序也必须按照索引列的顺序给出，如果给出 order by c, b, d 的顺序，那也是用不了B+树索引的。

同理， order by b҅order by b, c 这种匹配索引左边的列的形式可以使用部分的B+树索引。当联合索引左边列的值为常量，也可以使用后边的列进行排序，比如这样：

select * from t1 where b = 1 order by c, d;

这个查询能使用联合索引进行排序是因为b列的值相同的记录是按照c, d排序的。

不可以使用索引进行排序或分组的情况

AESC /DESC混用

对于使用联合索引进行排序的场景，我们要求各个排序列的排序顺序是一致的，也就是要么各个列都是ASC规则排序，要么都是DESC规则排序。

ORDER BY子句后的列如果不加ASC或者DESC默认是按照ASC排序规则排序的，也就是升序排序的。

select * from t1 order by b ASC, c DESC;

这个查询是用不到索引的

总结：
无过滤，必索引
顺序错，必排序（系统排序）
方向反，必排序（系统排序）

4.B树和B+树的区别：

Mysql中（你知道为什么用Select*查询很慢吗）

4.1为啥要用B+Tree而不用btree?

btree所有的节点都存储数据，而b+tree只有叶子节点才存储数据
而内存每次加载数据的大小是有限的，而有数据的时候，加载的量就会很小，如果都是索引，就会加载更多的索引值

4.2.B+Tree能存多少数据

mysql> show global status like 'Innodb_page_size';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| Innodb_page_size | 16384 |
+------------------+-------+
1 row in set
1234567

innodb的所有数据文件.idb大小都是16K即16384的整数倍
我们的InnoDB页的大小默认是16k？为啥是16K？因为16K的话，高度为3的树就可以存储千万级别的数据，如下有说明
假设一行数据的大小是1k，那么一个页可以存放16行这样的数据。
我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节
我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。
那么可以算出一棵高度为2的B+树，即存在一个根节点和若干个叶子节点，那么这棵B+树的存放总记录数为：根节点指针数单个叶子节点记录行数。能存放117016=18720条这样的数据记录。
根据同样的原理我们可以算出一个高度为3的B+树可以存放：1170117016=21902400条这样的记录。

所以在InnoDB中B+树高度一般为1-3层，它就能满足千万级的数据存储。
在查找数据时一次页的查找代表一次IO，所以通过主键索引查询通常只需要1-3次IO操作即可查找到数据。

之所以Btree能加快访问数据的速度，是因为存储引擎不再需要进行全表扫描
只需要从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，
存储引擎会根据这些指针向下层查找，而这些指针实际上定义了子节点页中值的上限和下线

比如：

create table people(
xing varchar(20) not null,
ming varchar(20) not null,
birthday date not null,
gender enum('male','unknow','female') not null,
key(last_name ,first_name,birthday)
)
1234567

索引如果查询多条数据，也是可以排序的

4.3 BTREE索引使用的限制

1. 如果不是按照最左列开始查找，无法使用索引
	例如无法用于查找  ming为XXX的，也无法查找 birthday为XXX的
     ABC三个组成的索引，只能A 、AB、 ABC
     其它的组合都是无法用到索引的
2. 如果中间有一列是范围查询，则之后的列不能用于索引查询
    比如		
    WHERE A =XXX AND B like 'XXX%' and C =XXX
    这个索引只能用到前两列

5.HASH索引

先查询当前数据库的版本
mysql> select  version();
查询当前实际表结构
show create table peopleHash ;
mysql> show create table peopleHash ;

| peopleHash | CREATE TABLE `peoplehash` (
  `xing` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `ming` varchar(20) COLLATE utf8mb4_general_ci NOT NULL,
  `birthday` date NOT NULL,
  `gender` enum('male','unknow','female') COLLATE utf8mb4_general_ci NOT NULL,
  KEY `ming` (`ming`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci |

5. 1 为什么在InnoDB创建HASH索引失败？

结果并没有出现我们想要的USING HASH ，也就是说创建HASH索引失败
为什么呢？
下面是MYSQL官方的回答：
1.不支持HASH索引（但是InnoDB在内部利用哈希索引来实现其自适应哈希索引功能。）

2.也就是InnoDB会根据表的使用情况自动为表生成hash索引，不能人为干预是否在InnoDB一张表中创建HASH索引

3.或者说，如果InnoDB注意到某些索引值被使用的特别频繁时，
它会在内存中基于Btree的索引之上再创建一个HASH索引，这样BTREE索引也具备了HASH索引的一些优点

但是也没有支持HSAH索引的引擎呢？

答案是肯定的：
MEMORY引擎是支持的

5.2 为什么InnoDB和MyISAM引擎不支持HASH索引？

1. HASH索引本身只存储对应的HASH值和行指针，而不是存储字段值

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

2. HASH索引并不是按照索引顺序来存储的，因此无法排序

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

3. HASH索引不支持部分索引列查找，因为HASH索引是使用全部的内容来计算HASH值的

例如在（A,B）两列建立索引，只查询A无法使用索引
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

4.HASH索引只支持等值比较查询，包括 =,而IN（）不能进行任何的范围查询

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（5）Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

5.3HASH冲突

1. 最严重的是既然HASH值是数字，肯定会出现相同的，也就是HASH冲突
2. 出现HASH冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行
3. 当HASH冲突特别多的时候，维护操作的成本就会变大，比如一次数据的删除
引擎需要遍历对应HASH值链表上的每一行，找到并删除对应的引用，冲突越多，代价越大

5.4. 模拟一个HASH索引

比如只有两个字段 ID  和URL
我们想查询URL是什么的  select id from XXX where url ='http://XXXXXXXX'
如果用BTREE来存储URL，则需要用到大量的空间，那我们还想用索引，怎么办呢？
删除URL原来的索引，新增一个字段  url_crc  使用CRC32做HASH
select id from XXX where url ='http://XXXXXXXX' and url_crc =CRC32('http://XXXXXXXX')
1.只要在插入和更新数据的时候，对url_crc进行更新就可以了
2. 但是如果用这个方式，记住不要用SHA1() MD5()作为HASH函数
3. 因为那两个函数的hash值非常长，会浪费大量的空间，但是如果数据量非常大，CRC32()会出现大量冲突
4. 自定义64位的哈希函数
简单的做法是：用MD5()函数返回值的一部分作为自定义哈希函数
select CONV(RIGHT(MD5('http://XXXXXXX'),16),16,10)AS HASH64;
插入和更新的时候都可以用这个了
insert into XXX 
(id,url,url_crc) values 
(XXX,XXX ,CONV(RIGHT(MD5('http://XXXXXXX'),16),16,10));

5.5. 索引是最好的解决方案吗

当然不是
1.对于数据量特别小的表，全表扫描更高效，没必要索引
2.中到大型的表，索引很高效，没有的话，速度很低
3.超大型表，由于数据量大，索引使用的代价会很大，这种情况下可以使用分区技术

6.创建索引的原则

列的离散型：
离散型的计算公式：count(distinct col):count(col)，离散型越高，选择型越好。
比如性别字段，只有男女、未知等在索引查询的时候其实还不如全表扫描，因为在索引创建的时候，如果此时检索 sex = 1的数据，根节点判断的时候，结果是查询左子树，但是当在左子树第二层再进行判断的时候，因为左右分支都满足条件，所以很难抉择选择哪一个分支继续搜索，或者是把两个分支同时进行搜索。如图：
最左匹配原则并且优先创建联合索引原则
这个很多介绍，不必说了
覆盖索引：
假如teacherNo 使用到了索引，检索到teacherNo 时候，可以直接将索引中的teacherNo 值返回，不需要进入数据区。

Select teacherNo from teacher where teacherNo = ?
1

Ps1:mysql为啥不推荐用UUID

UUID字段很长，而且不是int类型的，存储的数据量就会大大减少，索引效率不高
UUID因为不是自增的，在插入索引的时候，会对原有的叶片就行维护并分裂的成本特别高
比如是自增的iD，我们看下是怎么进行分裂的,在下图中插入7，并且规定一个一片中只能插入4个记录

插入记录7，由于叶页面中只能存放4条记录，插入记录7，导致叶页面分裂，产生一个新的叶页面。
传统B+树页面分裂操作分析：

按照原页面中50%的数据量进行分裂，针对当前这个分裂操作，3，4记录保留在原有页面，5，6记录，移动到新的页面。最后将新纪录7插入到新的页面中；
50%分裂策略的优势：
分裂之后，两个页面的空间利用率是一样的；如果新的插入是随机在两个页面中挑选进行，那么下一次分裂的操作就会更晚触发；
50%分裂策略的劣势：
空间利用率不高：按照传统50%的页面分裂策略，索引页面的空间利用率在50%左右；
分裂频率较大：针对如上所示的递增插入(递减插入)，每新插入两条记录，就会导致最右的叶页面再次发生分裂；
.传统50%分裂的策略，有不足之处，如何优化？接着往下看。
B+树分裂操作的优化？
由于传统50%分裂的策略，有不足之处，因此，目前所有的关系型数据库，包括Oracle/InnoDB/PostgreSQL，都针对B+树索引的递增/递减插入进行了优化。经过优化，以上的B+树索引，在记录6插入完毕，记录7插入引起分裂之后，新的B+树结构如下图所示：
Mysql中（你知道为什么用Select*查询很慢吗）
对比上下两个插入记录7之后，B+树索引的结构图，可以发现二者有很多的不同之处：

新的分裂策略，在插入7时，不移动原有页面的任何记录，只是将新插入的记录7写到新页面之中；
原有页面的利用率，仍旧是100%；
优化分裂策略的优势：
索引分裂的代价小：不需要移动记录；
索引分裂的概率降低：如果接下来的插入，仍旧是递增插入，那么需要插入4条记录，才能再次引起页面的分裂。相对于50%分裂策略，分裂的概率降低了一半；
索引页面的空间利用率提高：新的分裂策略，能够保证分裂前的页面，仍旧保持100%的利用率，提高了索引的空间利用率；
优化分裂策略的劣势：
如果新的插入，不再满足递增插入的条件，而是插入到原有页面，那么就会导致原有页面再次分裂，增加了分裂的概率。

ps2:

MYISAM引擎，非聚集索引
因为索引文件myi和数据文件myd是分开的，myi里面存储的是myd文件的指针,而且主键索引和非主键索引没有层次关系，因为存储的都是指向myd文件的指针

Innodb引擎是聚集索引
只有一个idb文件，在主键索引和非主键索引有层次的关系，因为主键索引存储的是真是的信息，而非主键索引存储的是主键的信息

上一篇： Python第六天课后作业

下一篇： mysql复合索引