mysql 加索引 null mysql 索引列有null

1.索引

是什么？

提到索引必须提到存储引擎（需要了解Mysql逻辑架构 1.Server层 2.存储引擎层）

补充：

连接过程：

• 与客户端进行 TCP 三次握手建立连接；
• 校验客户端的用户名和密码
• 读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限

词法分析：构建SQL语法树

语法分析：判断是否符合Mysql语法

预处理器（分析器和优化器之间）：负责

• 检查 SQL 查询语句中的表或者字段是否存在；
• 将 select * 中的 * 符号，扩展为表上的所有列；

如何查看 MySQL 服务被多少个客户端连接了？

show processlist

MySQL 定义了空闲连接的最大空闲时长，由 wait_timeout 参数控制的，默认值是 8 小时

（服务端自动断开，客户端不知道）

连接分为长连接和短连接

长连接

优点：减少建立连接和断开连接带来的开销

缺点：长连接后可能会占用内存增多，因为 MySQL 在执行查询过程中临时使用内存管理连接对象，这些连接对象资源只有在连接断开时才会释放。如果长连接累计很多，将导致 MySQL 服务占用内存太大，有可能会被系统强制杀掉，这样会发生 MySQL 服务异常重启的现象。

怎么解决长连接占用内存的问题？

1.服务端定期断开

2.客户端在执行很大的操作后自动重置

查看优化器执行计划：explain +SQL

key=primary 使用主键

key=null type=all全表扫描

存储引擎定位符合条件的第一条记录

执行器判断是否符合查询条件

while

索引的具体实现与存储引擎相关，存储引擎负责将数据持久化在磁盘中，以及提供数据读写接口

值得注意的是，记录是按行进行存储的，但存储引擎则是以数据页为单位进行读取（读取磁盘非常耗时，所以我们尽量一次读取尽可能多的数据）InnoDB数据页的默认大小为16KB(一次最少从磁盘中读取16K的内容到内存中，一次最少把内存中的16K内容刷新到磁盘中)

学过操作系统，就会知道磁盘中数据页的结构和组织

 在文件头中存在前向指针和后向指针，将数据页串成一个双向链表，使得数据页之间不需要物理上的连续，而是逻辑上的连续

主键顺序组成单向链表，单向链表插入删除非常方便，但是检索效率不高

所以通过数据页中页目录存储用户记录中每组最后一个记录的偏移量（槽），对用户记录起到索引作用，以便于快速找到记录（注意这种方法是查找一个数据页内的方法）

由于数据页中用户记录是有限的，且主键值是有序的，所以我们可以通过二分查找slot查找对应记录

if(check(slot))r=mid;
l=mid+1;

check函数通过将目标值与该槽对应的组的记录的最大值（最后一条记录）进行比较

页目录的创建过程:

1. 将所有的记录划分成几个组，这些记录包括最小纪录和最大记录，但不包括标记为已删除的记录

2. 每个记录组的最后一条记录就是组内最大的记录，并且最后一条记录的头信息中会存储该组一共有多少条记录，作为 n_owned 字段（下图中粉红色字段）

3. 页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录。

InnoDB 对每个分组中的记录条数都是有规定的：

• 第一个分组中的记录只能有 1 条记录；
• 最后一个分组中的记录条数范围只能在 1-8 条之间；
• 剩下的分组中记录条数范围只能在 4-8 条之间。

• 对于一个数据页内数据页中的用户记录，可以通过数据页中的页目录中的槽进行查找行记录对应的组从而找到对应的行记录（单个数据页内部索引，用于找到行记录）
• 而数据库中存储大量的数据页，对于多个数据页中的记录，就需要考虑建立新型的索引（多个数据页之间索引，用于找到对应的数据页）

每个数据库有一个Server层和多个存储引擎层

存储引擎功能：存储数据，为存储的数据建立索引，提供读写接口

不同的存储引擎的索引采用的数据结构不同

Mysql常用的存储引擎：

1. （默认） InnoDB存储引擎   大多采用 B+Tree 作为索引的数据结构
2. MyISAM 存储引擎   支持多种索引数据结构    eg.B+ 树索引、R 树索引、Full-Text 索引。创建表时，创建的主键索引默认使用的是 B+ 树索引。

二者都支持B+树索引，但二者对数据的存储方式不同

B+树非叶子节点存放索引，叶子结点存放数据页

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身；
• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址；

• 定位记录在哪一个页也是通过二分法快速定位到包含该记录的页
• 定位到该页后，又会在该页内进行二分快速定位记录所在的分组（slot）,最后在分组内遍历查找

Mysql数据是持久化的，数据（索引+记录）是保存到磁盘上的

磁盘相对于内存读取速度要慢上上万倍

磁盘读写的最小单位是扇区（512B），操作系统一次会读写多个扇区，最小读写单位是块（4KB,8个扇区）

当我们通过索引查找某行数据时，需要先从磁盘读取索引到内存，再通过索引从磁盘中找到某行数据，读入到内存——查询过程中发生多次磁盘I/O

为什么要用B+树作为数据库索引？

数据库的索引和数据都存储在硬盘，读取B+树的一个节点就相当于进行一次磁盘I/O

B+树与二叉树：

        B+树是多叉树，存储相同节点的树相对于二叉树矮了很多

搜索时间复杂度为O(logd^N),d表示节点允许的最大子节点个数为d（实际应用中>100）,N为叶子结点个数

查询效率高，即使在数据量很高的情况下，查询一个数据的磁盘I/O在3-4次,相对于二叉树少了很多

二叉查找树：当每次插入元素都是二叉树中最大元素，二叉查找树就会退化成链表，查询时间复杂度变为O(n)

自平衡二叉查找树：

        AVL树（平衡二叉查找树）

        红黑树

        

B+树与B树：

        都是M叉树，每个节点最多有M-1个索引和M个子节点（维基百科），超过要求就会分裂

节点

        Mysql中节点的索引和子节点都是M个

差别：

   1. B+树叶子结点存放数据+索引，非叶子节点只存放索引；B树每个节点都存放数据+索引

   2.叶子结点构成一个有序双链表

B树缺点：

1. 单点查询：用户记录的大小很有可能远远超过了索引数据，这就需要花费更多的磁盘I/O操作次数来读到有用的索引数据。查询路径上的节点里的数据也会被加载到内存，耗费内存资源
2. 范围查询时：B树就得中序遍历，涉及多个节点的磁盘I/O，而B+树只需顺序遍历双链表（Mysql的B+树是双链表）
3. 插入删除时，B树没有冗余节点，可能涉及复杂树的变形，B+树有冗余节点，节点饱和时可能存在节点分裂，但是最多只会涉及树的一条路径，效率更高

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

        B+树单个节点的数据量更小，在相同的I/O次数下，能查询更多节点

     

B+树与Hash:

        Hash在做等值查询的时候搜索时间复杂度为O(1),但不适合做范围查询

        

B+树的每个节点都是一个数据页：只有叶子结点才存放了数据。非叶子节点仅用来存放目录项作为索引

所有节点按照索引键大小排序，构成一个双向链表，便于范围查询

2. 索引分类

是什么？

索引分类

• 按「数据结构」分类：B+tree索引、Hash索引、Full-text索引。
• 按「物理存储」分类：聚簇索引（主键索引）、二级索引（辅助索引）。
• 按「字段特性」分类：主键索引、唯一索引、普通索引、前缀索引。
• 按「字段个数」分类：单列索引、联合索引。

主键索引：建立在主键字段上的索引，主键索引存在且唯一，索引列不允许有空值

唯一索引：建立在UNIQUE上的索引，唯一索引可以有多个，索引列的值必须唯一，允许有空值

普通索引：普通

前缀索引：对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，可以建立在char、varchar、binary、varbinary的列上

可以减少索引占用的存储空间，提升存储效率

索引按叶节点存放数据内容可以分为聚簇索引和非聚簇索引

聚簇索引：叶节点存放完整用户数据

• 一定存在且唯一   因为表的数据都是存放在聚簇索引的叶子节点里，所以 InnoDB 存储引擎一定会为表创建一个聚簇索引，且由于数据在物理上只会保存一份，所以聚簇索引只能有一个。
• 如果有主键，默认使用主键作为聚簇索引的索引键（主键索引）
• 如果没有主键，选择第一个不包含NULL值的列作为聚簇索引的索引键
• 如果都没有，会自动生成一个隐式自增id列作为聚簇索引的索引键

非聚簇索引（二级索引）：只存放主键值，用于实现非主键的快速搜索

• 可以有多个
• 回表  如果查询的数据包含非主键数据，就需要到聚簇索引中获取数据行（一共查询了两个B+树）
• 索引覆盖 如果查询的数据是主键值，只用在二级索引查，不用回表（一共查询了两个B+树）

单列索引：建立在单列上的索引

联合索引（组合索引）： 建立在多列上的索引

多个普通字段组合在一起创建的索引

遵循最左匹配原则 按照最左优先的原则进行匹配

比如，如果创建了一个（a,b,c）联合索引，如果查询条件是以下这几种，就可以匹配上联合索引：

由于有查询优化器，所以a在where子句中的顺序并不重要

• where a=1；
• where a=1 and b=2 and c=3；
• where c=3 and b=2 and a=1；
• where a=1 and b=2；

对于范围查找（<,>,between,like），范围列可以用联合索引，范围后面的列无法用到联合索引

3.如何使用索引不会导致索引失效

索引作用是进行数据查找，但查询条件用到了索隐列并不意味着查询过程一定用到了索引

要使用到索引进行查找，需要避免索引失效的情况，否则会进行全盘扫描

建议建立联合索引时，把区分度大（不同值的个数/表的总行数）的字段排在前面

eg.uuid放在性别前面

查询优化器发现某个值出现在表的数据行中的百分比（惯用的百分比界线是"30%"）很高的时候，它一般会忽略索引，进行全表扫描。

为什么联合索引不遵循最左匹配原则就会失效？

原因是，在联合索引的情况下，叶节点中的数据（主键）是先按索引第一列排序，第一列数据相同时才会按照第二列排序。

也就是说，如果我们想使用联合索引中尽可能多的列，查询条件中的各个列必须是联合索引中从最左边开始连续的列。如果我们仅仅按照第二列搜索，肯定无法走索引。

其中a是全局有序

b,c是全局无序，局部相对有序

where b=2 and c=3；

什么情况下索引会失效

1. 对索引使用左、或者左右模糊匹配，且查询内容包含非索引字段

即 like %xx 或like %xx%时

 type=ALL代表走了全盘扫描，没有走索引扫描 

 type=ALL说明利用索引进行了范围查询，走了索引扫描 ，key=index_name即为使用的索引

为什么 like 关键字左或者左右模糊匹配无法走索引呢？

B+树按照索引值排列有序存储，只能根据前缀进行比较

如果使用 name like '%林' 方式来查询，因为查询的结果可能是「陈林、张林、周林」等之类的，所以不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。

2.对索引使用函数

为什么对索引使用函数，就无法走索引了呢？

因为经过函数计算后的值不是索引原始值，比如说length(name)，只能把索引字段的值都抽出来，然后依次进行表达式计算来进行条件判断，因此采用全盘扫描

不过，从 MySQL 8.0 开始，索引特性增加了函数索引，即可以针对函数计算后的值建立一个索引，也就是说该索引的值是函数计算后的值，所以就可以通过扫描索引来查询数据。

alter table t_user add key idx_name_length ((length(name)));

再进行查询就会走索引了

3. 对索引进行表达式计算

 

 为什么对索引进行表达式计算，就无法走索引了呢？

与对索引进行表达式计算失效的原因类似

4. 对索引进行隐式类型转换

结果为1，说明Mysql在遇到字符串和数字比较时，会自动把字符串转为数字，然后再进行比较

（“10”<"9"    10>9）

 type=ALL 走全盘扫描

字符串和数字进行比较，将字符串转换为数字，相当于

select * from t_user where CAST(phone AS signed int) = 1300000001;

相当于对索引使用了函数，所以索引失效

type=const key=PRIMARY 走主键索引

id 是int类型，和字符串进行比较，将字符串‘1’转换为1，相当于

select * from t_user where id = CAST("1" AS signed int);

没有对索引使用函数，所以可以走索引扫描

5.联合索引不遵循最左匹配，且查询内容包含非索引字段

特殊查询条件：where a = 1 and c = 3   索引截断

或者碰到范围查询

此时a可以用到联合索引，c不能用到联合索引

不同版本处理方式不同

• Mysql5.5  存储引擎层定位到联合索引第一条满足条件的记录（a=1/a>1），联合索引找到主键值后，开始回表，到主键索引读取数据行，返回给Server层，由Server层对比c的值
• Mysql5.6 索引下推 （extra=using index condition）

将Server层负责的事情（判断存储引擎返回的数据是否符合查询条件，即对比c的值），交给存储引擎层处理

• 在存储引擎中定位到二级索引后，先不回表操作，判断索引中包含的列的条件是否成立，如果不成立，直接跳过该二级索引，成立，则进行回表，减少了回表次数，Server层再判断其他字段是否满足条件

 6.where子句中的OR

OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效。

id 是主键，age 是普通列

 解决方法：将age字段设置为索引

思考题：

题目1：

第一条和第二条都会走索引扫描，而且都是选择扫描二级索引（index_name）

 

 第三和第四条会发生索引失效，执行计划的结果 type= ALL，代表了全表扫描。

题目2：

第一条和第二条模糊查询语句也是一样可以走索引扫描

第二条查询语句的执行计划如下，Extra 里的 Using index 说明用上了覆盖索引

而第一和第二条查询语句的执行计划中 type 是range，表示对索引列进行范围查询，也就是利用了索引树的有序性的特点，通过查询比较的方式，快速定位到了数据行。

所以，type=range 的查询效率会比 type=index 的高一些。

第三条查询语句的执行计划（第四条也是一样的结果）

 可以看到 key=index_name，也就是说用上了二级索引，而且从 Extra 里的 Using index 说明用上了覆盖索引。

------------------------------------------------------------------------------------------------------------------------

为什么选择全扫描二级索引树，而不扫描全表（聚簇索引）呢？

1. 因为二级索引树的记录东西很少，就只有「索引列+主键值」

聚簇索引记录的东西会更多，比如聚簇索引中的叶子节点则记录了主键值、事务 id(用于事务)和 MVCC 的回流指针以及所有的剩余列。

2. 这个 select * 不用执行回表操作。

所以， MySQL 优化器认为直接遍历二级索引树要比遍历聚簇索引树的成本要小的多，因此 MySQL 选择了「全扫描二级索引树」的方式查询数据。

 ----------------------------------------------------------------------------------------------------------------------

为什么这个数据表加了非索引字段，执行同样的查询语句后，怎么变成走的是全表扫描呢？

加了其他字段后，select * from t_user where name like "%xx"; 要查询的数据就不能只在二级索引树里找了，得需要回表操作才能完成查询的工作，再加上是左模糊匹配，无法利用索引树的有序性来快速定位数据，所以得在二级索引树逐一遍历，获取主键值后，再到聚簇索引树检索到对应的数据行，这样实在太累了。

所以，优化器认为上面这样的查询过程的成本实在太高了，所以直接选择全表扫描的方式来查询数据。

4.什么时候选择使用索引

优点：

提高查询速度

缺点：

1.占用物理空间

2.创建索引和维护索引要耗费时间，时间随数据量增加而增大

3.降低表增删改查的效率，为了维护索引有序性，每次增删改查索引都需要进行动态维护

适用场景：

1. 字段有唯一性限制

2. 经常用于where查询条件的字段

3.经常用于group by和order by的字段，这样查询时就不需要再去做一次排序了

不需要索引场景：

表数据很少

字段中存在大量重复数据（没啥用且查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描）

经常频繁更新的字段（eg.电商项目的用户余额）

where,group by,order by里用不到的字段

优化索引方法：

1.前缀索引优化

        我的理解是之前都是对列建立索引，那么对于字符串，相当于对一整个字符串都建立了索引，而前缀索引可以对部分字符建立索引

前缀索引的局限性：

        order by无法使用前缀索引

        无法把前缀索引用作覆盖索引

2.覆盖索引优化

        SQL查询的所有字段，在二级索引树上都能找到都能，不需要通过聚簇索引去查，避免回表

优点：使用覆盖索引不需要查出包含整行记录的所有信息，减少了大量的I/O操作

3.主键索引最好自增

        每次插入的数据就会按序添加到当前索引节点的位置上，不需要移动已有的数据，效率非常高

        而非自增主键由于插入的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，需要移动其他数据，甚至需要从一个页面复制数据到另一个一碗面（页分裂）。页分裂可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率。

        建议主键的长度越小越好，意味着二级索引叶子结点页越小，二级索引占用空间就越小。

        

4.索引值最好设置为NOT NULL

        1.索引列存在NULL就会导致优化器在做索引选择时更加复杂 eg.count会忽略值为NULL的行

        2.NULL无意义，但会占用物理空间

5.防止索引失效

对于执行计划，参数有：

• possible_keys 字段表示可能用到的索引；
• key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
• key_len 表示索引的长度；
• rows 表示扫描的数据行数。
• type 表示找到所需数据时使用的扫描方式（按执行效率由低到高分为）

1. All（全表扫描）
2. index（全索引扫描）
3. range（索引范围扫描）：where子句中使用>,<,in,between等，属于范围查找
4. ref（非唯一索引/唯一索引的非唯一前缀扫描）：索引列值不唯一，有重复，返回数据可能是多条
5. eq_ref（主键/唯一索引扫描）：多表联查，关联条件时唯一索引
6. const（结果只有一条的主键或唯一索引扫描）：主键/唯一索引=常量

• extra

7. Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候， 这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
8. Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
9. Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错。

5.count(*) 和 count(1) 有什么区别？哪个性能最好？

 

count()是聚合函数，函数参数可以为字段名，也可以是其他任何表达式

作用：统计符合查询条件的记录中，参数不为NULL的记录有多少

count(1)

select count(1) from tb;

相当于统计tb表中有多少记录

过程：如果没有二级索引，InnoDB循环遍历聚簇索引，将读到记录返回给server层，但不读取记录中任何字段的值（1！=NULL），所以比count(主键）执行效率高

如果有二级索引，循环遍历二级索引

count(*)

count(\*)相当于count(0),Mysql会将参数*转化为参数0，所以count(*)和count(1)执行过程基本一样，性能无差异

count(字段）

如果没有二级索引，进行全盘扫描，如果有，采用二级索引进行扫描

所以如过要使用，建议给这个字段建立索引

为什么要通过遍历的方式来计数

MyISAM每张 MyISAM 的数据表都有一个 meta 信息有存储了row_count值，由于表级锁一致性，可以直接读取该值，单count查询复杂度O(1)

InnoDB支持事务，同一时刻的多个查询，由于多版本并发控制（MVCC），InnoDB应该返回的行数不确定

如何优化count(*)

1.近似值

show table status;

explain select count(*) from tb;
--并不真的查询，只会估算

2.额外表保存计数值

新增或删除时，需要维护这个表

参考：

《小林coding》

极客时间《Mysql45讲》

....