Mysql索引查询

MySQL查询过程

MySQL 的架构共分为两层：Server 层和存储引擎层
- Server 层负责建立连接、分析和执行 SQL。主要包括连接器，查询缓存、解析器、预处理器、优化器、执行器等。另外，所有的内置函数（如日期、时间、数学和加密函数等）和所有跨存储引擎的功能（如存储过程、触发器、视图等。）都在 Server 层实现
- 存储引擎层负责数据的存储和提取。支持 InnoDB、MyISAM、Memory 等多个存储引擎，不同的存储引擎共用一个 Server 层。从 MySQL 5.5 版本开始， InnoDB 成为了 MySQL 的默认存储引擎。索引数据结构就是由存储引擎层实现，不同的存储引擎支持的索引类型也不相同，比如 InnoDB 支持索引类型是 B+树，且是默认使用，在数据表中创建的主键索引和二级索引默认使用的是 B+ 树索引
第一步：连接器
- 启动Mysql服务，连接 MySQL 服务
```
# -h 指定 MySQL 服务得 IP 地址，如果是连接本地的 MySQL服务，可以不用这个参数；
# -u 指定用户名，管理员角色名为 root；
# -p 指定密码，如果命令行中不填写密码（为了密码安全，建议不要在命令行写密码），就需要在交互对话里面输入密码
mysql -h$ip -u$user -p
```
- 查看 MySQL 服务被多少客户端连接
  执行 show processlist 命令进行查看
- MySQL 定义空闲连接的最大空闲时长，由 wait_timeout 参数控制的，如果空闲连接超过了这个时间，连接器就会自动将它断开
```
mysql> show variables like 'wait_timeout';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| wait_timeout  | 28800 |
+---------------+-------+
1 row in set (0.00 sec)
```
  处于空闲状态的连接被服务端主动断开后，这个客户端并不会马上知道;发起请求获知
- MySQL 服务支持的最大连接数由 max_connections 参数控制
```
mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
```
- 长连接占用内存
  - 定期断开长连接。既然断开连接后就会释放连接占用的内存资源，那么可以定期断开长连接
  - 客户端主动重置连接。MySQL 5.7 版本实现了 mysql_reset_connection() 函数的接口
- 连接器总结：
  - 与客户端进行 TCP 三次握手建立连接；
  - 校验客户端的用户名和密码，如果用户名或密码不对，则会报错；
  - 如果用户名和密码都对了，会读取该用户的权限，然后后面的权限逻辑判断都基于此时读取到的权限
第二步：查询缓存
- SQL 是查询语句（select 语句），MySQL 就会先去查询缓存（ Query Cache ）里查找缓存数据，看看之前有没有执行过这一条命令，这个查询缓存是以 key-value 形式保存在内存中的，key 为 SQL 查询语句，value 为 SQL 语句查询的结果
- 查询缓存还挺有用，但是其实查询缓存挺鸡肋的。对于更新比较频繁的表，查询缓存的命中率很低的
- MySQL 8.0 版本直接将查询缓存删掉了，也就是说 MySQL 8.0 开始，执行一条 SQL 查询语句，不会再走到查询缓存这个阶段了
第三步：解析SQL
正式执行 SQL 查询语句之前， MySQL 会先对 SQL 语句做解析
- 第一件事情：词法分析
  - 根据你输入的字符串识别关键字，例如：SQL语句 select username from userinfo，分析之后，会得到4个Token，其中2个Keyword，为select和from。
- 第二件事情：语法分析
  - 根据词法分析结果，语法解析器会根据语法规则，如果没问题就会构建出 SQL 语法树，这样方便后面模块获取 SQL 类型、表名、字段名、 where 条件等等
第四步：执行SQL
- 每条SELECT 查询语句流程主要可以分为下面这三个阶段：
  - prepare 阶段，也就是预处理阶段；
  - optimize 阶段，也就是优化阶段；
  - execute 阶段，也就是执行阶段；
- 预处理阶段
  - 检查 SQL 查询语句中的表或者字段是否存在；
  - 将 select * 中的 * 符号，扩展为表上的所有列
- 优化阶段
  - 优化器主要负责将 SQL 查询语句的执行方案确定下来，比如在表里面有多个索引的时候，优化器会基于查询成本的考虑，来决定选择使用哪个索引
- 执行阶段
  - 经历完优化器后，就确定了执行方案，接下来 MySQL 就真正开始执行语句
  - 主键索引查询
    - 查询条件用到了主键索引，而且是等值查询，同时主键 id 是唯一，不会有 id 相同的记录
  - 全表扫描
    - 这条查询语句的查询条件没有用到索引，所以优化器决定选用访问类型为 ALL 进行查询
  - 索引下推
    - 索引下推能够减少二级索引在查询时的回表操作，提高查询的效率
- 总结：
  执行一条 SQL 查询语句，发生了什么？
  - 连接器：建立连接，管理连接、校验用户身份；
  - 查询缓存：如果查询语句命中查询缓存则直接返回，否则继续往下执行。MySQL 8.0 已删除该模块；
  - 解析 SQL，通过解析器对 SQL 查询语句进行词法分析、语法分析，然后构建语法树，方便后续模块读取表名、字段、语句类型；
  - 执行 SQL：执行 SQL 共有三个阶段：
    - 预处理阶段：检查表或字段是否存在；将 select * 中的 * 符号扩展为表上的所有列。
    - 优化阶段：基于查询成本的考虑，选择查询成本最小的执行计划；
    - 执行阶段：根据执行计划执行 SQL 查询语句，从存储引擎读取记录，返回给客户端
- 更新语句也是同样会走一遍：
  - 更新一条记录 UPDATE t_user SET name = 'xiaolin' WHERE id = 1; 的流程如下:
    - 客户端先通过连接器建立连接，连接器自会判断用户身份；
    - 因为这是一条 update 语句，所以不需要经过查询缓存，但是表上有更新语句，是会把整个表的查询缓存清空的，所以说查询缓存很鸡肋，在 MySQL 8.0 就被移除这个功能了；
    - 解析器会通过词法分析识别出关键字 update，表名等等，构建出语法树，接着还会做语法分析，判断输入的语句是否符合 MySQL 语法；
    - 预处理器会判断表和字段是否存在；
    - 优化器确定执行计划，因为 where 条件中的 id 是主键索引，所以决定要使用 id 这个索引；
    - 执行器负责具体执行，会调用存储引擎的接口，通过主键索引树搜索获取 id = 1 这一行记录：
      - 如果 id=1 这一行所在的数据页本来就在 buffer pool 中，就直接返回给执行器更新；
      - 如果记录不在 buffer pool，将数据页从磁盘读入到 buffer pool，返回记录给执行器。
    - 执行器得到聚簇索引记录后，会看一下更新前的记录和更新后的记录是否一样：
      - 如果一样的话就不进行后续更新流程；
      - 如果不一样的话就把更新前的记录和更新后的记录都当作参数传给 InnoDB 层，让 InnoDB 真正的执行更新记录的操作；
    - 开启事务， InnoDB 层更新记录前，首先要记录相应的 undo log，因为这是更新操作，需要把被更新的列的旧值记下来，也就是要生成一条 undo log，undo log 会写入 Buffer Pool 中的 Undo 页面，不过在内存修改该 Undo 页面后，需要记录对应的 redo log。
    - InnoDB 层开始更新记录，会先更新内存（同时标记为脏页），然后将记录写到 redo log 里面，这个时候更新就算完成了。为了减少磁盘I/O，不会立即将脏页写入磁盘，后续由后台线程选择一个合适的时机将脏页写入到磁盘。
    - 至此，一条记录更新完了

索引

索引：帮助存储引擎快速获取数据的一种数据结构，形象的说就是索引是数据的目录

存储引擎：就是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法

索引分类
- 按数据结构分类：常见有 B+Tree 索引、HASH 索引、Full-Text 索引
  - InnoDB 是在 MySQL 5.5 之后成为默认的 MySQL 存储引擎，B+Tree 索引类型也是 MySQL 存储引擎采用最多的索引类型
  - InnoDB 存储引擎根据不同的场景选择不同的列作为索引：
    - 如果有主键，默认会使用主键作为聚簇索引的索引键（key）；
    - 如果没有主键，就选择第一个不包含 NULL 值的唯一列作为聚簇索引的索引键（key）；
    - 在上面两个都没有的情况下，InnoDB 将自动生成一个隐式自增 id 列作为聚簇索引的索引键（key）；
  - 其它索引都属于辅助索引（Secondary Index），也被称为二级索引或非聚簇索引。创建的主键索引和二级索引默认使用的是 B+Tree 索引
  - 存储在 B+Tree 索引时是长什么样子
    - B+Tree 是一种多叉树，叶子节点才存放数据，非叶子节点只存放索引，而且每个节点里的数据是按主键顺序存放
    - 每一层父节点的索引值都会出现在下层子节点的索引值中，因此在叶子节点中，包括了所有的索引值信息
    - 每一个叶子节点都有两个指针，分别指向下一个叶子节点和上一个叶子节点，形成一个双向链表
    - 数据库的索引和数据都是存储在硬盘的，我们可以把读取一个节点当作一次磁盘 I/O 操作
    - B+Tree 存储千万级的数据只需要 3-4 层高度就可以满足，B+Tree 相比于 B 树和二叉树来说，最大的优势在于查询效率很高，因为即使在数据量很大的情况，查询一个数据的磁盘 I/O 依然维持在 3-4次
  - 主键索引的 B+Tree 和二级索引的 B+Tree 区别：
    - 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
    - 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据
  - 找到对应的叶子节点，然后获取主键值，再通过主键索引中的 B+Tree 树查询到对应的叶子节点，然后获取整行数据。这个过程叫「回表」，也就是说要查两个 B+Tree 才能查到数据
  - 当查询的数据是能在二级索引的 B+Tree 的叶子节点里查询到，这时就不用再查主键索引查；在二级索引的 B+Tree 就能查询到结果的过程就叫作「覆盖索引」，也就是只需要查一个 B+Tree 就能找到数据
  - 联合索引是二级索引的一种形式（除非它是主键），如果表显式定义了主键索引（聚簇索引），则所有其他索引（无论是单列、联合、唯一索引等）均属于二级索引
- B+Tree 相比于 B 树、二叉树或 Hash 索引结构的优势
  1、B+Tree vs B Tree
  B+Tree 只在叶子节点存储数据，而 B 树的非叶子节点也要存储数据，B+Tree 的单个节点的数据量更小，在相同的磁盘 I/O 次数下，就能查询更多的节点。
  B+Tree 叶子节点采用的是双链表连接，适合 MySQL 中常见的基于范围的顺序查找，而 B 树无法做到这一点。
  2、B+Tree vs 二叉树
  对有 N 个叶子节点的 B+Tree，其搜索复杂度为O(logdN)，其中 d 表示节点允许的最大子节点个数为 d 个
  在实际的应用当中， d 值是大于100的，这样就保证了，即使数据达到千万级别时，B+Tree 的高度依然维持在 3~4 层左右
  二叉树的每个父节点的儿子节点个数只能是 2 个，意味着其搜索复杂度为 O(logN)，因此二叉树检索到目标数据所经历的磁盘 I/O 次数要更多
  3、B+Tree vs Hash
  Hash 在做等值查询的时候效率贼快，搜索复杂度为 O(1)
  但是 Hash 表不适合做范围查询，它更适合做等值的查询，这也是 B+Tree 索引要比 Hash 表索引有着更广泛的适用场景的原因
物理存储分类
- 物理存储的角度：索引分为聚簇索引（主键索引）、二级索引（辅助索引）
- 两个区别：
  - 主键索引的 B+Tree 的叶子节点存放的是实际数据，所有完整的用户记录都存放在主键索引的 B+Tree 的叶子节点里；
  - 二级索引的 B+Tree 的叶子节点存放的是主键值，而不是实际数据
字段特性分类
- 主键索引
  - 建立在主键字段上的索引，通常在创建表的时候一起创建，一张表最多只有一个主键索引，索引列的值不允许有空值。
    创建表时，创建主键索引的方式如下：
```
CREATE TABLE table_name  (
  ....
  PRIMARY KEY (index_column_1) USING BTREE
);
```
- 唯一索引
  - 建立在 UNIQUE 字段上的索引，一张表可以有多个唯一索引，索引列的值必须唯一，但是允许有空值
    在创建表时，创建唯一索引的方式如下：
```
CREATE TABLE table_name  (
  ....
  UNIQUE KEY(index_column_1,index_column_2,...) 
);
```
  - 建表后，如果要创建唯一索引，可以使用这面这条命令：
```
CREATE UNIQUE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 
```
- 普通索引
  - 是建立在普通字段上的索引，既不要求字段为主键，也不要求字段为 UNIQUE
  - 在创建表时，创建普通索引的方式如下：
```
CREATE TABLE table_name  (
  ....
  INDEX(index_column_1,index_column_2,...) 
);
```
    建表后，如果要创建普通索引，可以使用这面这条命令：
```
CREATE INDEX index_name
ON table_name(index_column_1,index_column_2,...); 
```
- 前缀索引
  - 对字符类型字段的前几个字符建立的索引，而不是在整个字段上建立的索引，前缀索引可以建立在字段类型为 char、 varchar、binary、varbinary 的列上
  - 目的：减少索引占用的存储空间，提升查询效率
  - 创建表时，创建前缀索引的方式如下：
```
CREATE TABLE table_name(
    column_list,
    INDEX(column_name(length))
); 
```
    建表后，如果要创建前缀索引，可以使用这面这条命令：
```
CREATE INDEX index_name
ON table_name(column_name(length)); 
```
按字段个数分类
- 从字段个数的角度来看，索引分为单列索引、联合索引（复合索引）
  - 建立在单列上的索引称为单列索引，比如主键索引
  - 建立在多列上的索引称为联合索引
- 联合索引
  - 使用联合索引时，存在最左匹配原则，就是按照最左优先的方式进行索引的匹配。在使用联合索引进行查询的时候，如果不遵循「最左匹配原则」，联合索引会失效，这样就无法利用到索引快速查询的特性了
    - 查询条件之所以会失效，是因为(a, b, c) 联合索引，
      - 先按 a 排序，在 a 相同的情况再按 b 排序，在 b 相同的情况再按 c 排序。
      - b 和 c 是全局无序，局部相对有序的，这样在没有遵循最左匹配原则的情况下，是无法利用到索引的
  - 联合索引范围
    联合索引的最左匹配原则，在遇到范围查询（如 >、<）的时候，就会停止匹配，也就是范围查询的字段可以用到联合索引，但是在范围查询字段的后面的字段无法用到联合索引。注意，对于 >=、<=、BETWEEN、like 前缀匹配的范围查询，并不会停止匹配，前面我也用了四个例子说明了
  - 索引下推
    可以在联合索引遍历过程中，对联合索引中包含的字段先做判断，直接过滤掉不满足条件的记录，减少回表次数
    当你的查询语句的执行计划里，出现了 Extra 为 Using index condition，那么说明使用了索引下推的优化
  - 索引区分度
    建立联合索引时的字段顺序，对索引效率也有很大影响。越靠前的字段被用于索引过滤的概率越高，实际开发工作中建立联合索引时，要把区分度大的字段排在前面，这样区分度大的字段越有可能被更多的 SQL 使用到
  - 实际使用
```
select * from order where status = 1 order by create_time asc
```
    给 status 和 create_time 列建立一个联合索引，因为这样可以避免 MySQL 数据库发生文件排序
    要利用索引的有序性，在 status 和 create_time 列建立联合索引，这样根据 status 筛选后的数据就是按照 create_time 排好序的，避免在文件排序，提高了查询效率
优化索引
- 前缀索引优化
  - 使用某个字段中字符串的前几个字符建立索引，可以增加一个索引页中存储的索引值，有效提高索引的查询速度
  - 前缀索引有一定的局限性，例如：
    - order by 就无法使用前缀索引；
    - 无法把前缀索引用作覆盖索引；
- 覆盖索引优化
  - SQL 中 query 所有字段，在索引 B+Tree 的叶子节点上都能找得到的那些索引，从二级索引中查询得到记录，而不需要通过聚簇索引查询获得，可以避免回表的操作
- 主键索引自增
  - 如果我们使用自增主键，那么每次插入的新数据就会按顺序添加到当前索引节点的位置，不需要移动已有的数据，当页面写满，就会自动开辟一个新页面。因为每次插入一条新记录，都是追加操作，不需要重新移动数据，因此这种插入数据的方法效率非常高
  - 如果我们使用非自增主键，由于每次插入主键的索引值都是随机的，因此每次插入新的数据时，就可能会插入到现有数据页中间的某个位置，这将不得不移动其它数据来满足新数据的插入，甚至需要从一个页面复制数据到另外一个页面，我们通常将这种情况称为页分裂。页分裂还有可能会造成大量的内存碎片，导致索引结构不紧凑，从而影响查询效率
- 索引设置为NOT NULL
  1. 原因：索引列存在 NULL 就会导致优化器在做索引选择的时候更加复杂，更加难以优化
  2. 原因：NULL 值是一个没意义的值，但是它会占用物理空间，所以会带来的存储空间的问题
- 防止索引失效
  - 说一下，发生索引失效情况：
    - 使用左或者左右模糊匹配的时候，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效；
    - 在查询条件中对索引列做了计算、函数、类型转换操作，这些情况都会造成索引失效；
    - 能正确使用需要遵循最左匹配原则，否则就会导致索引失效。
    - 在 WHERE 子句中，如果在 OR 前的条件列是索引列，而在 OR 后的条件列不是索引列，那么索引会失效
Explain使用
在sql语句前添加explain，作用是查看mysql对这条sql的执行计划信息
- 对于执行计划，参数有：
  - possible_keys 字段表示可能用到的索引；
  - key 字段表示实际用的索引，如果这一项为 NULL，说明没有使用索引；
  - key_len 表示索引的长度；
  - rows 表示扫描的数据行数。
  - type 表示数据扫描类型，我们需要重点看这个。
- type 字段就是描述了找到所需数据时使用的扫描方式是什么，常见扫描类型的执行效率从低到高的顺序为：
  - All（全表扫描）；
  - index（全索引扫描）；
  - range（索引范围扫描）；
  - ref（非唯一索引扫描）；
  - eq_ref（唯一索引扫描）；
  - const（结果只有一条的主键或唯一索引扫描）
- 也要关注 extra 显示的结果，几个重要的参考指标：
  - Using filesort ：当查询语句中包含 group by 操作，而且无法利用索引完成排序操作的时候，这时不得不选择相应的排序算法进行，甚至可能会通过文件排序，效率是很低的，所以要避免这种问题的出现。
  - Using temporary：使了用临时表保存中间结果，MySQL 在对查询结果排序时使用临时表，常见于排序 order by 和分组查询 group by。效率低，要避免这种问题的出现。
  - Using index：所需数据只需在索引即可全部获得，不须要再到表中取数据，也就是使用了覆盖索引，避免了回表操作，效率不错
需要与不需要索引
- 索引最大好处：提高查询速度
- 索引缺点：
  - 需要占用物理空间，数量越大，占用空间越大
  - 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增大
  - 会降低表的增删改的效率，因为每次增删改索引，B+ 树为了维护索引有序性，都需要进行动态维护
- 需要索引
  - 字段有唯一性限制的，比如商品编码；
  - 经常用于 WHERE 查询条件的字段，这样能够提高整个表的查询速度，如果查询条件不是一个字段，可以建立联合索引。
  - 经常用于 GROUP BY 和 ORDER BY 的字段，这样在查询的时候就不需要再去做一次排序了，因为我们都已经知道了建立索引之后在 B+Tree 中的记录都是排序好的
- 不需要索引
  - WHERE 条件，GROUP BY，ORDER BY 里用不到的字段，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的
  - 字段中存在大量重复数据，不需要创建索引，比如性别字段，只有男女，如果数据库表中，男女的记录分布均匀，无论搜索哪个值都可能得到一半的数据
  - 表数据太少的时候，不需要创建索引；
  - 经常更新的字段不用创建索引，比如不要对电商项目的用户余额建立索引，因为索引字段频繁修改，由于要维护 B+Tree的有序性，那么就需要频繁的重建索引，这个过程是会影响数据库性能的

MySQL查询过程#

第一步：连接器#

第三步：解析SQL#

第四步：执行SQL#

索引#

索引分类#

物理存储分类#

字段特性分类#

按字段个数分类#

优化索引#

Explain使用#

需要与不需要索引#

MySQL查询过程

第一步：连接器

第三步：解析SQL

第四步：执行SQL

索引

索引分类

物理存储分类

字段特性分类

按字段个数分类

优化索引

Explain使用

需要与不需要索引