mysql学习-优快云博客

本文详细介绍了MySQL的查询过程、执行计划分析方法及重要参数解读，对比B树与B+树在索引实现上的优劣，阐述聚集与非聚集索引的区别，帮助读者深入理解MySQL索引机制。

一、MySQL基本架构

二、MySQL查询过程

MySQL查询过程如下：

客户端将查询发送到MySQL服务器
服务器先检查查询缓存，如果命中，立即返回缓存中的结果；否则进入下一阶段
服务器对SQL进行解析、预处理，再由优化器生成对象的执行计划
MySQL根据优化器生成的执行计划，调用存储引擎API来执行查询
服务器将结果返回给客户端，同时缓存查询结果

三、执行计划

（1）如何查看执行计划

使用explain命令查询

navicat中查询

（2）执行计划参数

显示的信息有id，select_type，table，type，key，ref等信息

id：

如果有子查询，ID的序号会递增（比如id会出现2了），在一组循环中id越大优先级越高，越早被执行
select_type:查询类型，可以理解为执行语句的类型，比如还可以update等。SIMPLE为简单查询，没有其他单位联合的查询，如果有其他单位一起查询，称之为组合查询

table：

显示这一行的数据是关于哪张表的

type：

这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、 indexhe和ALL，

ALL最差（全表扫描）

system（性能最好），const的特例：表仅有一行，平时基本不会出现，忽略不计

const数据库最多匹配一行，最优化的，比如查询id为1的那行数据，const直奔id为1的那行，常用于PRIMARY索引的查询，参数rows为1就表示只扫描的一行

所有查询数据的操作类型

system

表只有一行记录，相当于系统表

const

通过索引一次就找到，只匹配一行数据

eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配
常用于主键或唯一索引扫描

ref

非唯一性索引扫描，返回匹配某个单独值的所有行
用于=、< 或 > 操作符带索引的列

range

只检索给定范围的行，使用一个索引来选择行
一般使用between、>、<

index

只遍历索引树

ALL

全表扫描，性能最差

possible_keys：

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句

key：

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引

key_len：

使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref：

显示索引的哪一列被使用了，如果可能的话，是一个常数

rows：

MYSQL认为必须检查的用来返回请求数据的行数

Extra：

关于MYSQL如何解析查询的额外信息。坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。常见值如下：

using filesort

MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取
若出现有该值，应该优化SQL语句

using temporary

使用临时表保存中间结果，比如，MySQL在对查询结果排序时使用临时表
常见于order by和group by
若出现有该值，应该优化SQL语句

using index

表示select操作使用了覆盖索引，避免了访问表的数据行，效率不错

using where

where 子句用于限制哪一行

using join buffer

使用连接缓存

distinct

发现第一个匹配后，停止为当前的行组合搜索更多的行

四，面试问题

为什么B+比B树更适合做mysql索引

1、B树，每个节点都存储key和data，所有节点组成这棵树，并且叶子节点指针为nul，叶子结点不包含任何关键字信息。

2、B+树，所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接，所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)

为什么说B+比B树更适合实际应用中操作系统的文件索引和数据库索引？

1、B+的磁盘读写代价更低B+的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

2、B+-tree的查询效率更加稳定由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

聚集索引与非聚集索引的区分

区分聚集索引和非聚集索引的一个主要方法是查看叶子节点，如果叶子节点是真实的数据，那么就是聚集索引；如果叶子节点是指针，那么就是非聚集索引。

如果是在一个有聚集索引的表中使用非聚集索引，那么这个非聚集索引叶子节点指向的是聚集索引的位置，如果没有聚集索引，那么就指向数据页的rowid，这样的表示无序的，也叫做堆表。

读锁时，可以写入吗?如果可以写入，那么读锁定有什么意义呢？

----------------------------------------
有两种锁，共享锁和排他锁，也可以称为读锁与写锁。

1.如果处于写锁时，别的用户肯定不能写入，我想问，此可可以读取数据吗？
2.如果处于读锁时，别的用户也可以读取，但是我想问，别的用户可以写入吗？
如果不能写入的话，那么如果每时每刻都有用户在读取，则永远轮不到有写入的机会呀？
如果可以写入的话，那么读锁有什么意义呢？也就是说，相当于读锁什么也不限制呀？

1. 被写锁（排它）锁定时，其它会话（用户）不能读取数据
2. 处于读锁时，别的会话也可以申请读锁，也可以读取，但是读锁在释放前，别的用户得不到相同资源的写锁。

3. 不能写入，每时每刻都有用户读取，但并不一定读取相同粒度的数据，比如锁定的是若干数据行，不是整张表。一旦锁释放，就有写入的机会。你说的情况会发生在粒度较大的锁。有的数据库这方面处理的不太好，比如sqlite