mysql+获取+一列+的和_Mysql随笔 - JiaChang的个人空间 - OSCHINA - 中文开源技术交流社区...

本篇文章仅作为本人学习mysql相关知识笔记

以下内容都是以innodb 存储引擎为前提

修改mysql配置： my.ini文件

记录是以行的形式存储(Compact格式)

Compact数据结构

变长字段长度列表

NULL值列表

记录头信息

列1

列2

列3

变长字段长度列表：

NULL值列表：

记录头信息包含(record_type和next_record)

record_type = 0 代表普通记录，record_type = 1 代表B+树非叶子节点记录，record_type = 2 最小记录，record_type = 3 最大记录

next_record 指向下一条记录的相对位置

页

页是管理存储空间的基本单位(一页默认大小16K)，一次最少读取一页的数据到内存(局部性原理/减少IO)。

一页至少有两条记录(最小记录和最大记录)

对页中的数据无论做增删改的操作，记录之间始终维护成一条单链表，且链表的每个记录通过主键值从小到大顺序串连起来

页目录(Page Directory)

槽：每一个分组的最后一条记录(由于页中数据时按主键值由小到大排序，最后一条记录=分组中主键值最大的记录)

初始状态下一页中只有最小记录和最大记录，分别分为2组(槽)。当插入一条数据时，先找到第一个大于插入数据主键值的槽，然后该槽的n_owned 值加1，代表本组又增加了一条记录。

当一个组中的记录达到8条再插入一条记录时，这个组将会二分为两个组(一个组4条记录，另一个组5条记录)，这个过程会在页目录中新增一个槽来记录新增分组中最大记录的偏移量。

在一个数据页中查找数据将分为两步：

1.通过二分法找到记录所在的槽

2.在槽中通过记录之间的next_record遍历找到记录。

为什么二分法不适合链表？

二分法对于数组而言，中位数的计算可以直接通过length/2直接得出，复杂度为O(1)，而链表由于是通过next指针进行相连，找到中位数需要遍历链表，复杂度为O(n)。

所以槽的设计优化了单链表查找性能的不足。二分查找时间复杂度O(log n)

索引

由于页在物理存储上是不连续的，在没有索引的情况下查找数据时需要扫描所有的页(全表扫描)，扫描磁盘是非常耗时的。

对于查找效率来说，二分查找有着很好的性能，前提条件是数据必须是有序的。所以引入索引作为物理数据的映射，这样物理数据在逻辑上就是有序的。

页与页之间通过双向链表进行关联。如下图

随着用户数据量的增加，页的个数也越来越多，为了更快速的定位到数据页，给每一个数据页建一个目录项，其中包含(key和page_no)

key：当前页中最小的主键值

page_no：页号

一页就对应着一个目录项，为了更快定位到目录项，可以使用同样的数据结构，将目录项放入页中，这种特殊的页，称之为目录项纪录。

如何在众多目录项纪录中快速定位到页？我们可以对目录项纪录生成更高级的目录项纪录即可，保证最高一级的目录项纪录的数据页只有一个。

到此，上述结构，就是一颗B+树，叶子节点存储实际用户数据，非叶子节点存储目录项。

上述索引就是聚簇索引(聚簇索引 = 主键索引)

聚簇索引是innoDB的数据存储方式(索引即数据，数据即索引)

1.通过主键值进行纪录和页的排序(数据页中排序，目录项排序)

2.B+树的叶子节点存储的是用户数据

二级索引(辅助索引)

1.使用索引列的值进行纪录和页的排序

2.B+树的叶子节点存储的是主键值。(这样做有两个好处：第一在索引重建时无需重建二级索引，第二减少索引存储空间)

通过二级索引查找纪录：先在二级索引查找主键值，再通过主键值去聚簇索引中查找(这个过程称为回表)

联合索引(多列创建一个索引)

比如为列C1和C2建立联合索引

1.先把纪录和数据页通过C1进行排序

2.在C1相同的情况下再通过C2进行排序

覆盖索引(通过索引能直接获取到数据，无需回表)

二级索引是先查询到主键值，再根据主键值回表获取数据，当二级索引查询到的主键值集合过多是，执行器有可能选择全表扫描而不走二级索引。

索引用于排序(分组同理)

如果需要对查询结果进行排序，一般情况下会加载用户数据到内存中，如果结果集太大，还会使用磁盘作为中间结果，这种在内存或者磁盘上的排序称为文件排序，性能较差。如果使用到了索引，就可以避免文件排序，索引的顺序映射了数据的顺序，只需要通过索引回表获取数据即可。

注意order by使用联合索引，列的顺序必须跟索引一致，否则无法使用联合索引进行排序

Hash编排索引的不足？

无法满足范围查找和 order by 排序

B+树为什么比B树要好？

1.

减少io，内存是有限的，B树是索引跟数据放在一个节点里面，会导致一页能装的数据比较少，会频繁的从磁盘读取页，io 效率上来说(B+ > B)

2.B+树的纪录是存在叶子结点，叶子结点之间又是一个双向链表，所以只需要一次索引的检索，就能够一直next...，因此范围检索和排序要更优于 B树

为什么索引尽量不要太大？

索引太大，首先直接影响的就是目录项占用更大的空间，进而导致存放目录项的页，也就是目录项纪录只能存更少的纪录(一页的空间默认16K)，进而导致索引节点多，第一影响检索速度，第二占用额外的内存空间

为什么索引不要太多？

一个索引对应一颗B+树，第一会占用额外的空间。第二在增删改的过程可能会导致索引重建，可能会移动大量的数据甚至产生页分裂。

为什么主键值要求自增呢？

1.因为数据是直接存储在主键/聚簇索引的叶子结点中，当插入一行新数据，将新增一个叶子节点，如果主键是有序自增的，只需要纪录在上一个节点的后面，这样一页里面的数据将是被顺序填满的。

2.如果主键是无序的(唯一ID)，将会需要在主键索引树中寻找合适的位置进行插入，有可能会导致页分裂(插入数据位置将原本数据挤到下页中)，会导致移动大量的数据

为什么MySQL的修改操作会影响检索效率？

修改操作，如果修改到了索引字段值，将会导致索引树的重建，甚至可能引发页分裂

如何挑选索引？

1.为出现在查询条件的列建索引(where 关键字后面)

2.为区分度高的列建索引(不重复的数据占总数的比值)：select count(DISTINCT user_name) / count(*) from table_name

3.索引列的类型尽可能小(参考为什么索引不要太大的回答)

4.对长字符串列截取作为前缀索引

索引匹配规则？

假设索引为A,B,C

全值匹配(select * from XXX where A = 1 and B = 1 and C = 1)

最左匹配原则(select * from XXX where A = 1 and C = 1)select * from XXX where B = 1 and C = 1 and A = 1 (只要查询条件包含组合索引最左边那一列，mysql 优化器会选择查询效率高的那一列，就是先根据 索引 A 进行查询，where条件顺序不等于sql执行顺序)

前缀匹配(对于字符串类型的索引，匹配前缀也可以快速定位纪录，like abc% 反例 like %abc)

范围匹配(select * from XXX where A >1 and A <10)

先找到1和10，再通过双向链表获取完整纪录

精确匹配某一列并范围匹配另外一列(select * from XXX where A = 1 and B > 1 and B<10)

先找到A=1的所有纪录，再对查找到的纪录进行范围查找

索引失效场景(待补充)

1.索引列使用函数或者表达式(假设C是索引列C * 2 < 10 可以改成 C < 10/2 )

2.类型不匹配

3.组合索引不满足最左匹配原则

count(1)，count(*)，count(id)和count(字段)的效率比较

count(字段)：返回该字段非null值的总数

count(id):统计ID，统计ID的个数(server层需要取ID)

count(1):直接返回1，server层统计1的个数

count(*):mysql做了优化，连1都不返回了，直接统计返回结果给server层

count(字段) < count(id) < count (1) ≈ count(*)

为什么count(字段) < count(id)？

count(id)可能会选择最小的索引来遍历

而count(字段)的话，如果字段上没有索引，就只能选主键索引