阅读 157

全方位解析 MySQL 及相关面试题一(收藏点赞系列)

写在前面

  • 文章是在前人的基础上进行总结整理再加上自己的一点理解,仅作为自己学习的记录,不作任何商业用途!
  • 如果在文章中发现错误或者侵权问题,欢迎指出,谢谢!

image.png

MySQL 架构

  • MySQL 采用的是 C/S 架构,即客户端(Client)与服务端(Server)架构,我们在使用 MySQL 的时候,都是以客户端的身份,发送请求连接到运行服务端的 MySQL 守护进程,MySQL 服务器端则根据我们的请求进行处理并把处理后的结果返回给我们
  • 和其它数据库相比,MySQL 的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎
  • 架构图

image.png

  • 从上图可以看出来 MySQL 主要分为连接层、服务层、引擎层和存储层
    • 连接层:主要是和客户端进行连接,负责连接和授权认证等工作
    • 服务层:主要完成大部分的核心服务功能, 包括查询缓存、SQL 解析、优化等操作,而 服务层与存储引擎之间的交互则通过统一的 API 进行调用
    • 引擎层:存储引擎真正的负责了 MySQL 中数据的存储和提取,服务器通过 API 与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取
    • 存储层:主要是将数据存储在运行于该设备的文件系统之上,并完成与存储引擎的交互
连接管理器
  • 连接管理器的作用是管理和维持所有 MySQL 客户端的请求连接,当我们向 MySQL 发起请求时,连接管理器会负责创建连接并校验用户的权限
查询缓存
  • 当我们建立连接以后,如果执行的是 select 操作,那么连接器会先从查询缓存中查询之前是否执行过这条语句
    • 如果执行过则会返回缓存结果
    • 如果没有执行过则会继续后面的操作
  • 查询缓存是把查询语句当作一个 key,查询结果当用 value,建立起来的 key-value 缓存结构
  • 不推荐使用查询缓存
    • 因为只要数据库表发生了更新则会将所有的查询缓存给清除掉,所以往往都是不能够命中查询缓存的,存在的意义不大
解析器
  • 这条 SQL 要做什么
  • 当在查询缓存中没有命令查询时,则需要真正执行语句,这时候就交给解析器进行词法分析和语法分析
优化器
  • 这条 SQL 如何做
  • 经过分析器的词法和语法分析,MySQL 服务器已经知道这条 SQL 语句要做什么了,但是还需要进行优化,比如
    • 当涉及到多个索引时,决定用哪个索引
    • 多表关联时,决定连接顺序
    # 该步骤决定了先从 t1 表中取出 c=10 的 ID 值还是先查找出 t2 表中 d=20 的 ID 值
    select * from t1 join t2 using(ID) where t1.c=10 and t2.d=20;
    复制代码
执行器
  • 真正的执行步骤
  • SQ L语句在经过查询优化器的优化后,接下来就交由执行器开始执行,不过执行器在开始执行前,会判断用户对相应的数据表是否有权限
  • 若有权限,打开表后根据表的引擎定义,使用引擎提供的接口向底层的存储发送相应的指令,存储引擎负责具体执行,并将执行结果告诉执行器,然后再返回给客户端
存储引擎
  • 后文讲解
相关面试题
  • Q:问数据库的架构图?
    • 总体来说分为四层,连接层用来连接客户端并进行授权认证,服务层是主要的核心部分,包含了查询缓存、解析器、优化器、执行器等部分,引擎层是 MySQL 真正负责数据存取,不同的引擎有不同的功能,存储层则是将数据存储在文件系统中并和引擎层进行交互
  • Q:一条查询的 SQL 语句是怎么执行的?
    • 就是上图中每一流程中描述的过程

存储引擎

  • MySQL 支持 9 种存储引擎
  • 存储引擎,也叫做表类型,具体作用便是决定一个数据表怎么处理和存储表中的数据,MySQL 支持多种不同的存储引擎,而且存储引擎被设计为可插拔式的,在同一个数据库中不同的数据表可以使用不同的存储引擎
查询存储引擎
  • 查询 MySQL 支持的存储引擎
    mysql > show engines
    复制代码
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    | InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
    | MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
    | MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
    | BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
    | MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
    | CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
    | ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
    | PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
    | FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
    +--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
    9 rows in set (0.00 sec)
    复制代码
  • 查询表的存储引擎
    --查看具体某一个表所使用的存储引擎,这个默认存储引擎被修改了!
    show create table tablename
    
    --准确查看某个数据库中的某一表所使用的存储引擎
    show table status like 'tablename'
    show table status from database where name="tablename"
    复制代码
指定存储引擎
  • Innodb 是 MySQL 的默认存储引擎
  • 指定 user 表的存储引擎
    CREATE TABLE users(
        uid int not null,
        username varchar(32) not null,
        email varchar(64) not null,
        gender tinyint not null,
        primary key(uid)
    ) engine=MyISAM;
    复制代码
修改存储引擎
  • 数据库表的存储引擎也可以修改,修改 user 表的存储引擎
    ALTER TABLE users ENGINE=InnoDB;
    复制代码
存储引擎对比
  • 主要是指 InnoDB 和 MyISAM 两个存储引擎对比

    • 物理文件存储结构对比
      • MyISAM

        1. .frm文件:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
        2. .MYD (MYData) 文件:MyISAM 存储引擎专用,用于存储MyISAM 表的数据
        3. .MYI (MYIndex)文件:MyISAM 存储引擎专用,用于存储MyISAM 表的索引相关信息
      • InnoDB

        1. .frm 文件:与表相关的元数据信息都存放在frm文件,包括表结构的定义信息等
        2. .ibd 文件或 .ibdata 文件: 这两种文件都是存放 InnoDB 数据的文件

        之所以有两种文件形式存放 InnoDB 的数据,是因为 InnoDB 的数据存储方式能够通过配置来决定是使用共享表空间存放存储数据,还是用独享表空间存放存储数据

        独享表空间存储方式使用 .ibd 文件,并且每个表一个 .ibd 文件 共享表空间存储方式使用 .ibdata 文件,所有表共同使用一个或多个 .ibdata 文件

  • 其他对比

MyISAMInnoDB
存储空间MyISAM 可被压缩,存储空间较小InnoDB 的表需要更多的内存和存储,因为它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
外键不支持支持
锁范围表锁,即操作一条数据会锁住整张表,不适合高并发操作行级锁,即操作一条数据仅锁住那一行,适合高并发操作
缓存只缓存索引,不缓存真实数据不仅缓存索引还要缓存真实数据,因此对内存要求较高,内存大小对性能有决定性的影响
AUTO_INCREMENTMyISAM 表可以和其他字段一起建立联合索引InnoDB中必须包含只有该字段的索引
SELECTMyISAM 更优
INSERTInnoDB 更优
UPDATEInnoDB 更优
DELETEInnoDB 更优
COUNT without WHEREMyISAM 更优,因为 MyISAM 保存了表的具体行数InnoDB 没有保存表的具体行数,需要逐行扫描统计,就很慢了
COUNT with WHERE一样,都会锁住整张表一样,都会锁住整张表
FULLTEXT全文索引支持不支持(5.6.4以上支持英文全文索引) 可以通过使用 Sphinx 从 InnoDB 中获得全文索引,会慢一点
相关面试题
  • Q:MyISAM 和 InnoDB 的区别有哪些?

    1. InnoDB 支持事务,而 MyISAM 是不支持事务的。这也是 MySQL 把数据库默认引擎变为 InnoDB 的原因之一
    2. InnoDB 最小粒度的锁是行级锁,而 MyISAM 最小粒度的锁是表级索,执行一条更新语句会锁住整张表,导致其他查询和更新都会被阻塞,不适合高并发的场景。这也是 MySQL 把数据库默认引擎变为 InnoDB 的原因之一
    3. InnoDB 支持外键,而 MyISAM 不支持。对一个包含外键的 InnoDB 表转为 MYISAM 会失败
    4. InnoDB 是聚簇索引,MyISAM 是非聚簇索引 (后文索引分类中讲解)
    5. InnoDB 不保存表的具体行数,执行 select count( * ) from table 时需要全表扫描,而 MyISAM 用一个变量保存了整个表的行数,执行上述语句时只需要读出该变量即可,速度很快
  • Q:一张表里面有 ID 自增主键,当 insert 了 17 条记录之后,删除了第 15,16,17 条记录,再把数据库重启,再 insert 一条记录,这条记录的 ID 是 18 还是 15?

    • 如果表类型是 MyISAM,则是 18。因为 MyISAM 表会把自增主键的最大ID记录到数据文件中,重启数据库,自增主键的最大 ID 也不会丢失
    • 如果表类型是 InnoDB,则是 15。因为InnoDB 表只是把自增主键的最大ID记录到内存中,所以重启数据库或对表进行 OPTION 操作,都会导致最大ID丢失
  • Q:哪个存储引擎执行 select count( * ) 更快,为什么?

    • MyISAM 更快,因为 MyISAM 内部维护了一个计数器,把表的总行数存储在磁盘上,当执行 select count( * ) from t 时,直接返回总数据
    • 在 InnoDB 存储引擎中,跟 MyISAM 不一样,没有将总行数存储在磁盘上,当执行 select count( * ) from t 时,会先把数据读出来,一行一行的累加,最后返回总数量
  • Q:为什么 MyISAM 的读操作比 InnoDB 更快,写操作比 InnoDB 更慢?

    • 读操作更快是因为 MyISAM 是非聚簇索引,InnoDB 是聚簇索引。INNODB 在做 SELECT 的时候,要维护的东西比 MYISAM 引擎多很多
    • 写操作更慢是因为 MyISAM 是表级锁,更新操作会锁住整张表,而 InnoDB 是行级锁
  • Q:MyISAM 和 InnoDB 的主键支持有什么不同?

    • MyISAM 数据表允许没有主键和其他索引,而 InnoDB 数据表如果没有主键的话,而会生成一个用户不可见 6 字节的主键

数据类型

  • 主要包括五大类
    • 整数类型:BIT、BOOL、TINY INT、SMALL INT、MEDIUM INT、 INT、 BIG INT
    • 浮点数类型:FLOAT、DOUBLE、DECIMAL
    • 字符串类型:CHAR、VARCHAR、TINY TEXT、TEXT、MEDIUM TEXT、LONGTEXT、TINY BLOB、BLOB、MEDIUM BLOB、LONG BLOB
    • 日期类型:Date、DateTime、TimeStamp、Time、Year
    • 其他数据类型:BINARY、VARBINARY、ENUM、SET、Geometry、Point、MultiPoint、LineString、MultiLineString、Polygon、GeometryCollection 等
相关面试题
  • Q:CHAR 和 VARCHAR 的区别
    • 相同点
      • CHAR(n),VARCHAR(n) 中的 n 都代表字符的个数
      • CHAR,VARCHAR 最大长度 n 的限制后,字符串会被截断
    • 不同点
      • CHAR 不论实际存储的字符数都会占用 n 个字符的空间,而 VARCHAR 只会占用实际字符应该占用的字节空间加1(实际长度length,0<=length<255)或加2(length>255)
        • 原因:VARCHAR 保存数据时除了要保存字符串之外还会加一个字节来记录长度(如果列声明长度大于255则使用两个字节来保存长度)
      • 能存储的最大空间限制不一样:CHAR 的存储上限为 255 字节
      • CHAR 在存储时会截断尾部的空格,而 VARCHAR 不会
    • 使用策略
      • 对于经常变更的数据来说,CHAR 比 VARCHAR 更好,因为 CHAR 不容易产生碎片。
      • 对于非常短的列,CHAR 比 VARCHAR 在存储空间上更有效率
  • Q:BLOB和TEXT有什么区别?
    • BLOB 保存二进制数据,TEXT 保存字符数据

索引基础

索引是什么
  • 索引(Index)是一种用于提升数据库查询效率的数据结构
索引的语法
  • 创建索引

    CREATE INDEX nameIndex ON test(name)
    
    CREATE UNIQUE INDEX idIndex ON test(id)
    复制代码

    image.png

    CREATE TABLE mytable ( 
        ID INT NOT NULL, 
        username VARCHAR ( 16 ) NOT NULL, 
        INDEX [ indexName ] ( username ( length )) 
    );
    复制代码
  • 删除索引

    -- DROP INDEX [indexName] ON mytable
    DROP INDEX idIndex ON test
    复制代码
  • 显示索引

    SHOW INDEX FROM test
    复制代码

    image.png

索引的优缺点
  • 优点
    • 提高数据检索效率,降低数据库 IO 成本
    • 降低数据排序的成本,降低 CPU 的消耗
  • 缺点
    • 索引也是一张表,保存了主键和索引字段,并指向实体表的记录,所以也需要占用内存
    • 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。 因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段, 都会调整因为更新所带来的键值变化后的索引信息

索引的分类

image.png

Hash 索引
  • Hash 索引基于哈希表实现,只有精确匹配索引所有列的查询才有效。Memory 引擎默认使用的是此种索引

  • 存储引擎对所有的索隐列计算出一个 HashCode,将 HashCode 存储在索引中,同时 HashCode 中保存每个数据行的指针。这样对于此种索引查找速度是非常快的。出现哈希值碰撞的话,索引会以链表的形式存放多个记录指针到同一个哈希条目中

  • 检索算法

    • 在检索查询时,就再次对待查关键字再次执行相同的 Hash 算法,得到 HashCode,到对应 Hash 表对应位置取出数据即可,如果发生 Hash 碰撞,则需要在取值时进行筛选
  • 举例

    nameage
    Jane28
    Peter20
    Lily30
    • 假设使用假想的哈希函数f(),生成对应的设想值:
      1. f('Jane') = 2323
      2. f('Peter') = 2456
      3. f('David') = 2400
    • 则 Hash 索引的数据结构如下(Hash 表)
    槽(slot)值(value)
    2323指向第1行指针
    2400指向第3行指针
    2456指向第2行指针
    • 对于 select * from user where name = 'Jane' 那么直接先算 Jane 的 HashCode,然后根据Jane的 HashCode = 2323 去找到对应的第一行数据,查询速度相对于 B-Tree 索引是要快
  • MySQL 并没有显式支持 Hash 索引,而是作为内部的一种优化。具体在 Innodb 存储引擎里,会监控对表上二级索引的查找,如果发现某二级索引被频繁访问,二级索引成为热数据,就为之建立 Hash 索引。因此,在 MySQL 的 Innodb 里,对于热点的数据会自动生成 Hash 索引。这种 Hash 索引根据其使用的场景特点,也叫自适应 Hash 索引

  • 缺点

    1. 不能避免读取行:哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显
    2. 无法用于排序:哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序
    3. 只支持等值查找:哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>和<=>是不同的操作)。不支持任何范围查询,例如 WHERE price>100
    4. 存在 Hash 冲突:当出现哈希冲突的时候,存储引擎必须遍历链表中所有的行指针,逐行进行比较,直到找到所有符合条件的行
    5. 不支持部分列匹配查找:这里面是使用索引列的全部内容来计算哈希值,例如(A,B)两列一起建索引,单纯使用 A 一列,那么就无法使用索引,B-Tree 索引的话,因为支持匹配最左前缀,所以这种情况适用性偏好
B+Tree 索引
  • 这个是 MySQL 索引的基本实现方式。除了全文索引、Hash索引,Innodb、MyISAM 的索引都是通过 B+Tree 实现的
  • 本部分内容较多,会从以下几个方面进行讲述:
    1. 为何从二叉树变为选择 B+Tree
    2. 磁盘存取
    3. B-Tree 数据结构
    4. B+Tree 数据结构
  • 为何从二叉树变为选择 B+Tree

    • 二叉搜索树

      image.png

      • 二叉搜索树具有以下性质:左子树的值小于根的值,右子树的值大于根的值
      • 对该二叉树的节点进行查找发现:深度为1的节点的查找次数为1,深度为2的查找次数为2,深度为n的节点的查找次数为n,因此其平均查找次数为 (1+2+2+3+3+3+3) / 7 = 2.4 次
      • 但是当这个二叉搜索树构造成以下形式

      image.png

      • 其平均查找次数为 (1+2+3+4+5+6+6) / 7 = 4 次
      • 二叉搜索树缺点:如果树的太深(太高),那么这棵二叉树的查询效率就低了。因此若想二叉树的查询效率尽可能高,需要这棵二叉树是平衡的,从而引出新的定义——平衡二叉树,或称 AVLTree
    • 平衡二叉树

      • 平衡二叉树(AVL树)在符合二叉查找树的条件下,还满足任何节点的两个子树的高度最大差为 1(图片来源于网络)

      image.png

      • 如果在AVL树中进行插入或删除节点,可能导致AVL树失去平衡,这种失去平衡的二叉树可以概括为四种姿态:
        1. LL:LeftLeft,也称“左左”。插入或删除一个节点后,根节点的左孩子(Left Child)的左孩子(Left Child)还有非空节点,导致根节点的左子树高度比右子树高度高2,AVL树失去平衡
        2. RR:RightRight,也称“右右”。插入或删除一个节点后,根节点的右孩子(Right Child)的右孩子(Right Child)还有非空节点,导致根节点的右子树高度比左子树高度高2,AVL树失去平衡
        3. LR:LeftRight,也称“左右”。插入或删除一个节点后,根节点的左孩子(Left Child)的右孩子(Right Child)还有非空节点,导致根节点的左子树高度比右子树高度高2,AVL树失去平衡
        4. RL:RightLeft,也称“右左”。插入或删除一个节点后,根节点的右孩子(Right Child)的左孩子(Left Child)还有非空节点,导致根节点的右子树高度比左子树高度高2,AVL树失去平衡

      image.png

      • AVL树失去平衡之后,可以通过旋转使其恢复平衡
      • 使用平衡二叉树作为索引的数据结构

      image.png

      • 将每一个节点看成一块磁盘,磁盘的结构如图所示:
        1. 关键字:即我们建立索引的关键字段的对应值。
        2. 数据区:即关键字对应的数据存储磁盘位置,通过关键字所对应的磁盘位置进行IO读写操作获取数据
        3. 节点引用:即指向子节点的磁盘位置
      • 当我们需要查询 ID=8 的数据的时候,那么先会获取根节点 10 加载到内存中,比较数据大小,发现比 10 小,那么查找左节点5,发现比 5 大,查找 5 的右节点,发现命中,然后根据数据区地址去进行 IO读写操作
      • AVLTree 缺点:如果数据太深(或者说太高),会导致频繁操作 IO,而 IO 又是一个耗时操作,因此引出了新的定义—— B-Tree
  • 磁盘存取

    • 局部性原理与磁盘预读
      • 由于存储介质的特性,磁盘本身存取就比主存慢很多,因此为了提高效率,要尽量减少磁盘 IO
      • 为了达到这个目的,磁盘往往不是严格按需读取,而是每次都会预读,即使只需要一个字节,磁盘也会从这个位置开始,顺序向后读取一定长度的数据放入内存
      • 预读可以提高I/O效率,预读的长度一般为页(page:计算机管理存储器的逻辑块-通常为 4k)的整倍数,主存和磁盘以页为单位交换数据
      • 当程序要读取的数据不在主存中时,会触发一个缺页异常,此时系统会向磁盘发出读盘信号,磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中
    • 系统从磁盘读取数据到内存时是以磁盘块(block)为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么
    • InnoDB 存储引擎默认每一页的大小为 16k,而系统一个磁盘块的存储空间往往没有这么大,因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB
    • InnoDB 在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘 IO 次数,提高查询效率,B-Tree 结构的数据可以让系统高效的找到数据所在的磁盘块
  • B-Tree 数据结构

    • 首先定义一条记录为一个二元组[key, data],key为记录的键值,对应表中的主键值,data为一行记录中除主键外的数据来描述 B-Tree
    • 一棵 m 阶的 B-Tree 有如下特性:
      1. 每个节点最多有 m 个孩子
      2. 除了根节点和叶子节点外,其它每个节点至少有 Ceil(m/2) 个孩子
      3. 若根节点不是叶子节点,则至少有 2 个孩子
      4. 所有叶子节点都在同一层,且不包含其它关键字信息
      5. 每个非终端节点包含n个关键字信息(P0,P1,…Pn, k1,…kn)
      6. 关键字的个数 n 满足:ceil(m/2)-1 <= n <= m-1
      7. ki(i=1,…n) 为关键字,且关键字升序排序。
      8. Pi(i=1,…n) 为指向子树根节点的指针,P(i-1)指向的子树的所有节点关键字均小于ki,但都大于k(i-1)
    • 一个三阶的 B-Tree 结构如下图所示

    image.png

    • 每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例,关键字为 17 和 35,P1 指针指向的子树的数据范围为小于 17,P2 指针指向的子树的数据范围为 17~35,P3 指针指向的子树的数据范围为大于 35。模拟查找关键字 29 的过程:
      1. 根据根节点找到磁盘块 1,读入内存。【磁盘I/O操作第1次】
      2. 比较关键字 29 在区间(17,35),找到磁盘块1的指针P2
      3. 根据 P2 指针找到磁盘块 3,读入内存。【磁盘I/O操作第2次】
      4. 比较关键字 29 在区间(26,30),找到磁盘块3的指针P2
      5. 根据 P2 指针找到磁盘块 8,读入内存。【磁盘I/O操作第3次】
      6. 在磁盘块8中的关键字列表中找到关键字 29
    • 分析上面过程,发现需要 3 次磁盘I/O操作,和 3 次内存查找操作
    • 由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率,3 次磁盘 I/O 操作是影响整个 B-Tree 查找效率的决定因素
    • B-Tree 相对于 AVLTree 缩减了节点个数(因为 AVLTree 中一个节点只有一个关键字和两个指针),使每次磁盘 I/O 取到内存的数据都发挥了作用,从而提高了查询效率
    • B-Tree 缺点:从 B-Tree 结构图中可以看到每个节点中不仅包含数据的 key 值,还有 data 值。而每一个页的存储空间是有限的,当 data 数据较大时将会导致每个节点(即一个页)能存储的 key 的数量很小,当存储的数据量很大时同样会导致 B-Tree 的深度较大,增大查询时的磁盘 I/O 次数,进而影响查询效率,因此引出新的定义——B+Tree
  • B+Tree

    • B+Tree 是在 B-Tree 基础上的一种优化,使其更适合实现外存储索引结构,InnoDB 存储引擎就是用B+Tree实现其索引结构
    • 在 B+Tree 中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储 key 值信息,这样可以大大加大每个节点存储的 key 值数量,降低 B+Tree 的高度
    • B+Tree 相对于 B-Tree 有几点不同:
      1. B+Tree 节点关键字搜索采用闭合区间
      2. B+Tree 非叶节点不保存数据相关信息,只保存关键字和子节点的引用
      3. B+Tree 关键字对应的数据保存在叶子节点中
      4. B+Tree 叶子节点是顺序排列的,并且相邻节点具有顺序引用的关系
      5. 每个节点的指针上限为 2d 而不是 2d+1(d 表示出度的意思,后文讲解)
    • 由于 B+Tree 的非叶子节点只存储 key 值信息不再存储 data 数据,所有的 data 数据均存储在叶子节点中,这样使得非叶子节点 出度d 值会变得大
    • 假设每个磁盘块能存储 4 个键值及指针信息,则变成 B+Tree 后其结构如下图所示

    image.png

    • 通常在 B+Tree 上有两个头指针,一个指向根节点,另一个指向关键字最小的叶子节点,而且所有叶子节点(即数据节点)之间是一种链式环结构。因此可以对 B+Tree 进行两种查找运算:
      1. 一种是对于主键的范围查找和分页查找
      2. 一种是从根节点开始,进行随机查找
    • 可能上面例子中只有 22 条数据记录,看不出 B+Tree 的优点,下面做一个推算:
      • InnoDB 存储引擎中页的大小为 16KB,一般表的主键类型为 INT(占用4个字节)或 BIGINT(占用8个字节),指针类型也一般为 4 或 8 个字节,也就是说一个页(即 B+Tree 中的一个节点)中大概存储 16KB/(8B+8B)=1K 个键值(因为是估值为方便计算,这里的 K 取值为10^3)。也就是说一个深度为 3 的 B+Tree 索引可以维护 (10^3 * 10^3 * 10^3 = 10亿) 条记录
    • 实际情况中每个节点可能不能填充满,因此在数据库中,B+Tree 的高度一般都在 2-4 层。MySQL 的 InnoDB 存储引擎在设计时是将根节点常驻内存的,也就是说查找某一键值的行记录时最多只需要 1~3 次磁盘 I/O 操作
  • B-Tree/B+Tree 索引的性能分析

    • 前置内容:
      • h:指数的度
      • 度:二叉树中节点的度指的是结点拥有的子树的数目,而整棵树的度指的是树中结点的最大的度(出度d 表示的就是整棵树的度)
      • 一般使用磁盘 I/O 次数评价索引结构的优劣
    • 先从 B-Tree 分析:根据 B-Tree 的定义,可知检索一次最多需要访问 h(高度) 个节点。数据库系统的设计者巧妙利用了磁盘预读原理,将一个节点的大小设为等于一个页,这样每个节点只需要一次 I/O 就可以完全载入。为了达到这个目的,在实际实现B-Tree还需要使用如下技巧:
      • 每次新建节点时,直接申请一个页的空间(对应上面一个页即 B+Tree 的一个节点),这样就保证一个节点物理上也存储在一个页里,加之计算机存储分配都是按页对齐的,就实现了一个 node 只需一次 I/O
      • B-Tree 中一次检索最多需要 h-1 次 I/O(因为根节点常驻内存),渐进复杂度为 O(h)=O(logdN)。一般实际应用中,出度d 是非常大的数字,通常超过 100,因此 h 非常小(通常不超过3)
      • 综上所述,用 B-Tree 作为索引结构效率是非常高的
    • 在分析 B+Tree:B+Tree 更适合外存索引,原因和内节点 出度d 有关。从上面分析可以看到,d 越大索引的性能越好,而出度的上限取决于节点内 key 值和 data 值的大小:
      • dmax = floor(pagesize / (keysize + datasize + pointsize)) (pagesize – dmax >= pointsize) 或
      • dmax = floor(pagesize / (keysize + datasize + pointsize)) – 1 (pagesize – dmax < pointsize)
    • 因此在非叶子节点中删除了 data 域的 B+Tree 明显有更大的 出度d 值(根据上面的粗略估算是 1K),所以更加适合索引
  • 经过上面的讲解,可以引出一个面试题:为什么 InnoDB 采用的是 B+Tree,而不是 Hash 索引、红黑树亦或者是 B-Tree 呢?

    1. 首先 B-Tree 和 B+Tree 对比:由于 B+Tree 的非叶子节点舍弃了 Data 域,所以单个节点中 B+Tree 能存储更多的指针,也就是说指向的子节点更多,这样查询到的节点也就更多,同时意味着树的高度 h 不高(一般为 2~4 层),这样可以减少 IO 次数,最后 B+tree 所有叶子节点之间都采用单链表连接,这样适合基于范围的顺序查找
    2. 其次 B+tree 与红黑树的对比:对于有 N 个叶子结点的 B+tree,其搜索复杂度为 O(logdN),d 值一般是大于 100 的,即使数据量达到千万级别时 B+tree 的高度依然维持在 3-4 左右,保证了 3-4 次磁盘 I/O 操作就能查询到目标数据,然而红黑树是二叉树,节点子节点个数为两个,意味着其搜索复杂度为 O(logN),树的高度也会比 B+tree 高出不少,因此红黑树检索到目标数据所需经历的磁盘 I/O 次数更多
    3. 最后 B+Tree 和 Hash 索引对比:Hash 索引其检索效率非常高,根据索引值可以一次定位,不像 B-Tree 索引需要从根节点到枝节点,最后才能访问到页节点这样多次的 IO 访问,所以 Hash 索引的查询效率要远高于 B-Tree 索引,但是 Hash 索引存在许多弊端,比如:Hash 冲突、只支持等值查找,不能避免读取行等等...
  • 下面几种分类索引可以在看完索引的实现部分再来理解
主键索引和辅助索引
  • MyISAM 主键索引与辅助索引
    • MyISAM 引擎的索引文件和数据文件是分离的,其 B+Tree 结构中叶子节点的 data 域存放的是数据记录的内存地址
    • 索引文件与数据文件分离,这样的索引称为"非聚簇索引"
    • MyISAM 的主索引与辅助索引区别并不大,只是主键索引不能有重复的关键字
  • InnoDB 主键索引与辅助索引
    • InnoDB 引起索引文件和数据文件并没有分离,其 B+Tree 结构中叶子节点的 data 域存放的就是实际的数据记录(对于主键索引存储方式实际的数据记录,对于辅助索引存储的是主键索引的值)
    • 索引文件与数据文件没有分离,这样的索引称为"聚簇索引",一个表只能有一个聚簇索引
单值索引和联合索引
  • 索引既可以仅包含一个字段,也可以同时包含多个字段。单个字段组成的索引可以称为单值索引,否则称之为联合索引,也称为组合索引或多值索引

  • 假如我们现在有如下表结构:id 字段为主键索引,username 字段为辅助索引,那么(id, username) 就是联合索引

    CREATE TABLE `user_table` (
      `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
      `username` varchar(255) NOT NULL,
      `password` varchar(255) DEFAULT NULL,
      `age` int(11) unsigned Not NULL,
      PRIMARY KEY (`id`),
      key (`username`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8
    复制代码
    • 注意是 InnoDB 存储引擎
  • 联合索引的索引的数据顺序跟字段的顺序相关,包含多个值的索引中,如果当前面字段的值重复时,将会按照其后面的值进行排序

  • InnoDB 存储引擎的 B+Tree 遵循最左前缀原则,下面我从一个 sql 语句入手去讲解什么是 “最左前缀原则”

    select password from user_table where username = 'xdh'
    复制代码
    • 我们添加了一个联合索引 (username,password),特别注意这个联合索引的顺序,如果我们颠倒下顺序改成 (password,username),这样查询能使用这个索引吗?答案是不能的!
    • 这是最左前缀的第一层含义:联合索引的多个字段中,只有当查询条件为联合索引的一个字段时,查询才能使用该索引
    • 我们现在有如下三种查询场景:
      1. 查出用户名的第一个字是“张”开头的人的密码,即查询条件子句为"where username like '张%'"
      2. 查处用户名中含有“张”字的人的密码,即查询条件子句为"where username like '%张%'"
      3. 查出用户名以“张”字结尾的人的密码,即查询条件子句为"where username like '%张'"
    • 那么实际上这个联合索引只有在第一个场景中才会生效,为什么呢?
    • 这就是最左前缀的第二层含义:索引可以用于查询条件字段为索引字段,根据字段值最左若干个字符进行的模糊查询
  • 扩展

    • 维护索引是需要付出代价的,我们有时候可以利用“最左前缀原则”来减少索引数量,比如说:如果我们现在需要通过 username 来查找对应的 age 信息,有以下三种方式(一般选择第三种):
      1. 直接通过 username 索引树查找到主键索引值,在通过回表查询(后文会讲)来获取 age 数据
      2. 通过创建联合索引 (username, age) 来查询
      3. 我们可以在 (username, password) 的基础上创建 (username, password, age) 联合索引,这样我们需要维护的索引数量不变
    • 创建索引时,我们还需要考虑空间代价,使用较少的空间来创建索引,比如说:经常需要通过 username 查询 age 或通过 age 查询 username,有以下两种方式(一般选择第一种)
      1. (username, age) 联合索引 + age 单字段索引
      2. (age, username) 联合索引 + username 单字段索引
      • 一般来说,username 字段比 age 字段大的多,所以我们应选择第一种,索引占用空间较小,这就是最少空间原则
覆盖索引
  • 是联和索引查询时的最优情况,不要进行回表查询

  • 假设执行如下两条 sql 语句

    select id from user_table where username = 'xdh'
    select password from user_table where username = 'xdh'
    复制代码
  • 执行第一条语句的过程是这样的:通过 username 辅助索引创建的 B+Tree 树的叶子节点存储的是 key = username 的值,data 存储的是主键索引的值(在后文索引的实现中会讲),所以通过 username 辅助索引我们就已经找到了我们需要的数据了,不用在通过主键索引去查找另外的数据

  • 执行第二条语句的过程是这样的:通过 username 辅助索引创建的 B+Tree 树的叶子节点存储的是 key = username 的值,data 存储的是主键索引的值,然后在通过回表查询在主键索引树中找到满足条件的数据(username > id > password)

  • 当 sql 语句的所求查询字段(select列)和查询条件字段(where子句)全都包含在一个索引中,可以直接使用索引查询而不需要回表,这就是覆盖索引

  • 通过使用覆盖索引,可以减少搜索树的次数,是常用的性能优化手段,比如上面的第二条 sql 语句就可以通过建立联合索引(username, password) 来实现覆盖索引,达到优化的目的

  • 查看是否成功的使用联合索引

    EXPLAIN SELECT id FROM user_table WHERE username = 'xdh';
    EXPLAIN SELECT password FROM user_table WHERE username = 'xdh';
    复制代码
  • 可以从 Extra 这一列看出是否成功使用覆盖索引

    image.png

    image.png

索引下推
  • MySQL 数据库对联合索引的一种优化策略,在后文性能优化中讲解
聚簇索引
  • Innodb 的主键索引,非叶子节点存储的是索引指针,叶子节点存储的是既有索引也有数据,是典型的聚簇索引(这里可以发现,索引和数据的存储顺序是强相关的,因此是典型的聚簇索引)
  • Inndob 里的辅助索引也是非聚簇索引
非聚簇索引
  • MyISAM 中索引和数据文件分开存储,B+Tree 的叶子节点存储的是数据存放的地址,而不是具体的数据,是典型的非聚簇索引,换言之就是:数据可以在磁盘上随便找个地方存,索引也可以在磁盘上随便找个地方存,只要叶子节点记录上对应关系就行。()索引存储顺序和数据存储关系毫无关联,是典型的非聚簇索引)
  • Inndob 里的辅助索引也是非聚簇索引
唯一索引
  • 不允许具有索引值相同的行,从而禁止重复的索引或键值,系统在创建该索引时检查是否有重复的键值,并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查, 如果有重复的值,则会操作失败,抛出异常
  • 注意的是:主键索引一定是唯一索引,而唯一索引不一定是主键索引
全文索引
  • 在 MySQL5.6 版本以前只有 MyISAM 存储引擎支持全文引擎
  • 在 MySQL5.6 版本中 InnoDB 存储引擎加入了对全文索引的支持,但是不支持中文全文索引
  • 在MySQL5.6 版本以后 InnoDB 存储引擎支持中文全文索引

索引的实现

  • 索引的实现是由存储引擎来实现的, MYSQL 中比较主流的两大引擎是:Myisam 和 InnoDB,存储引擎是建立在表上面的,在建立表的时候可以指定所需要的存储引擎
    CREATE TABLE `user` {
        `id` int(11) NOT NULL,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY(`id`)
    } ENGINE=InnoDB DEFAULT CHARSET=UTF8
    复制代码
    CREATE TABLE `user` {
        `id` int(11) NOT NULL,
        `name` varchar(255) NOT NULL,
        PRIMARY KEY(`id`)
    } ENGINE=MYISAM DEFAULT CHARSET=UTF8
    复制代码
B+Tree 在 MyISM 中的实现
  • 我们在上文提到过的使用 MyISAM 存储引擎的表创建的时候会生成 3 个文件:table_name.frm(表结构文件)、table_name.MYD(数据保存文件)、table_name.MYI(索引保存文件)

image.png (图片来源于网络)

  • 例如上诉 teacher 表,两个文件分别保存了数据及索引,由 于B+Tree 中只有叶子节点保存数据区,在MyISAM 中,data 区中保存的是数据的引用地址,就比如说 ID=101 的数据信息所保存到物理磁盘地址为 0x123456,在索引中的节点数据去中所保存的就是这个磁盘地址指针。当扫描到这个指针位置,就可以通过这个磁盘指针讲数据加载出来
  • 在 MyISAM 中 B+Tree 的实现中,比如现在不用 ID 作为索引了,要用 name,那么他的一个展现形式又是怎么样的呢?其实他与 ID 作为索引是一样的,也是保存他指定的磁盘位置指针,如下图(图片来源于网络)

image.png

B+Tree 在 InnoDB 中的体现
  • 会在数据目录生成 2 个文件,分别是table_name.frm(表结构文件),table_name.idb(数据与索引保存文件)
  • 在 InnoDB中,因为设计之初就是认为主键是非常重要的。是以主键为索引来组织数据的存储,当我们没有显示的建立主键索引的时候,存储引擎会隐式的为我们建立一个 6 位数的主键索引来组织数据存储,在叶子节点上,data 区保存了数据的所有信息
  • 如果这个时候建立了name 字段的索引:会产生一个以 name 字段为索引的辅助索引,而此刻叶子节点上所保存的数据为聚集索引(ID 索引)的关键字的值,基于辅助索引找到 ID 索引的值,再通过 ID 索引区获取最终的数据,这就是所谓的 "回表查询"

image.png

索引策略

覆盖索引
最左前缀策略
索引下推
  • ICP(Index Condition Pushdown)是在 MySQL 5.6 版本上推出的查询优化策略,把本来由 Server 层做的索引条件检查下推给存储引擎层来做,以降低回表和访问存储引擎的次数,提高查询效率

  • 原理

    • 在没有使用 ICP 的情况下,联合索引的查询过程是怎么样的
      1. 存储引擎读取索引记录
      2. 根据索引中的主键值,定位并读取完整的行记录
      3. 存储引擎把记录交给 Server 层去检测该记录是否满足 WHERE 条件
    • 在使用了 ICP 的情况下,联合索引的查询过程又是怎么样的
      1. 读取索引记录(不是完整的行记录,即辅助索引的记录)
      2. 判断 WHERE 条件部分能否用索引中的列来做检查,条件不满足,则处理下一行索引记录;、
      3. 条件满足,使用辅助索引中的主键值去定位并读取完整的行记录(就是所谓的回表);
      4. 存储引擎把记录交给 Server 层,Server 层检测该记录是否满 WHERE 条件的其余部分
  • 案例

    • 创建一张 user 表,并插入数据
    CREATE TABLE USER (
        id INT ( 11 ) NOT NULL AUTO_INCREMENT COMMENT "主键",
        name VARCHAR ( 32 ) COMMENT "姓名",
        city VARCHAR ( 32 ) COMMENT "城市",
        age INT ( 11 ) COMMENT "年龄",
        PRIMARY KEY ( id ),
        KEY idx_name_city ( NAME, city ) 
    ) ENGINE = INNODB DEFAULT CHARSEt = UTF8;
    
    INSERT INTO USER2 (`name`, city, age )
    VALUES
        ( "ZhaoDa", "BeiJing", 20 ),
        ( "QianEr", "ShangHai", 21 ),
        ( "SunSan", "GuanZhou", 22 ),
        ( "LiSi", "ShenZhen", 24 ),
        ( "ZhouWu", "NingBo", 25 ),
        ( "WuLiu", "HangZhou", 26 ),
        ( "ZhengQi", "NanNing", 27 ),
        ( "WangBa", "YinChuan", 28 ),
        ( "LiSi", "TianJin", 29 ),
        ( "ZhangSan", "NanJing", 30 ),
        ( "CuiShi", "ZhengZhou", 65 ),
        ( "LiSi", "KunMing", 29 ),
        ( "LiSi", "ZhengZhou", 30 );
    复制代码
    • 表记录

    image.png

    • 查看表中索引

    image.png

    • 执行 SQL 查询语句
    SELECT * FROM user2 WHERE name = "LiSi" AND city LIKE "%Z%" AND age > 25;
    复制代码
  • 案例分析

    • 索引条件下推默认是开启的,可以使用系统参数 optimizer_switch 来控制器是否开启,通过下面的命令可以控制是否开始索引下推
      • set optimizer_switch="index_condition_pushdown=off";
      • set optimizer_switch="index_condition_pushdown=on";
  • 不使用索引下推:据联合索引“最左匹配”原则,只有 name 列能用到索引,city 列由于是模糊匹配,是不能用到索引的,此时的执行过程是这样的

    1. 存储引擎根据 (name, city) 联合索引,找到 name 值为 LiSi 的记录,共 4 条记录
    2. 然后根据这 4 条记录中的 id 值,逐一进行回表扫描,去主索引中取出完整的行记录,并把这些记录返回给 Server 层
    3. Server 层接收到这些记录,并按条件 name="LiSi" and city like "%Z%" and age > 25 进行过滤,最终留下 ("LiSi", "ZhengZhou", 30) 这条记录

    image.png

  • 使用索引下推:此时的执行过程是这样的

    1. 存储引擎根据 (name, city) 联合索引,找到 name='LiSi' 的记录,共 4 条
    2. 由于联合索引中包含 city 列,存储引擎直接在联合索引中按 city like "%Z%" 进行过滤,过滤后剩下 2 条记录
    3. 根据过滤后的记录的 id 值,逐一进行回表扫描,去主索引中取出完整的行记录,并把这些记录返回给 Server 层
    4. Server 层根据 WHERE 语句的其它条件 age > 25,再次对行记录进行筛选,最终只留下 ("LiSi", "ZhengZhou", 30) 这条记录

    image.png

  • 从执行计划上判断是否使用索引下推(Extra 里显示 Using index condition

    image.png

  • 使用场景

    • 只能用于 range、 ref、 eq_ref、ref_or_null 访问方法
    • 只能用于 InnoDB 和 MyISAM 存储引擎及其分区表
    • 对 InnoDB 存储引擎来说,索引下推只适用于辅助索引,因为主键索引的叶子节点包含了整个行记录,,用不到索引下推

索引的适用/不适用场景

适用场景
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段
  • 查询中与其他表关联的字段,外键关系建立索引
  • 单键/组合索引的选择问题,高并发下倾向创建组合索引
  • 查询中排序的字段,排序字段通过索引访问大幅提高排序速度
  • 查询中统计或分组字段
不适用场景
  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布均匀的表字段,只应该为最经常查询和最经常排序的数据列建立索引(如果某个数据类包含太多的重复数据,建立索引没有太大意义)
  • 频繁更新的字段不适合创建索引(会加重IO负担)
  • where 条件里用不到的字段不创建索引

索引相关面试题

  • Q:说说你对 MySQL 索引的理解?
  • Q:数据库索引的原理,为什么要用 B+Tree,为什么不用二叉树,红黑树亦或者是 B-Tree 呢?
    • 首先可以回答索引是什么:索引是存储引擎中一种用于快速查找记录的一种数据结构,其本质都是通过不断缩小想要的数据的范围来筛选出最终的结果,数据库中是通过 B+Tree 来实现索引的
    • 在讲 B+Tree 前可以先讲 B-Tree,它是一种平衡二叉树,说下一个 m 阶的 B-Tree 有哪些特性,在说明 B-Tree 的缺点:如果节点中存储的 data 值过大,而一个节点的空间是有限的,那么就会导致 key 值的缩小,当数据量很大的时候就会增大 B-Tree 的树的高度,从而导致了 I/O 操作的增加,最终影响到查询效率
    • 接下来就可以引出 B+Tree,说明它与 B-Tree 的几点区别,这些区别带来的好处
    • 最后要是问为什么不用红黑树或者 Hash 索引则可以用在上文 B-Tree/B+Tree 索引的性能分析解答过的内容回答
  • Q:聚集索引与非聚集索引的区别?
    • 区别就是索引的存储顺序和数据的存储顺序是否是关系的,有关就是聚簇索引,无关就是非聚簇索引
  • Q:叶子结点存放的是数据还是指向数据的内存地址?
    • 如果该表的存储引擎是 MyISAM,其叶子节点的 data 区域保存的是内存地址
    • 如果该表的存储引擎是 InnoDB,并且是由主键索引创建出来的 B+Tree,其叶子节点的 data 区域保存的是数据,如果是非主键索引创建出来的 B+Tree,其叶子节点保存的是主键索引的的值
  • Q:InnoDB 引擎中的索引策略,了解过吗?
    • 覆盖索引
    • 最左前缀原则
    • 索引下推
  • Q:创建索引的方式有哪些?
    • 建表时创建
      CREATE TABLE `user_table` (
          `id` INT ( 11 ) UNSIGNED NOT NULL AUTO_INCREMENT,
          `username` VARCHAR ( 255 ) NOT NULL,
          `password` VARCHAR ( 255 ) DEFAULT NULL,
          `age` INT ( 11 ) UNSIGNED NOT NULL,
          PRIMARY KEY ( `id` ),
          KEY ( `username` ) 
      ) ENGINE = INNODB DEFAULT CHARSET = utf8
      复制代码
    • 建表后创建
      CREATE UNIQUE INDEX idIndex ON test(id)
      复制代码
    • 查看表索引
      SHOW INDEX FROM TABLE_NAME
      复制代码
  • Q:使用索引需要注意的几个地方?
    • 用来创建的索引的字段要尽量小,因为存储引擎是通过这个字段值去创建 B+Tree 中节点的 key 值的,如果 key 值过大的话,就会导致每一个节点的叶子节点数量变少,会影响树的高度,导致 I/O 操作增加,影响查询速率
  • Q:那为什么推荐使用整型自增主键而不是选择UUID?
    • UUID是字符串,比整型消耗更多的存储空间
    • 在 B+Tree 中进行查找时需要跟经过的节点值比较大小,整型数据的比较运算比字符串更快速
    • 自增的整型索引在磁盘中会连续存储,在读取一页数据时也是连续。UUID 是随机产生的,读取的上下两行数据存储是分散的,不适合执行 where id > 5 && id < 20 的条件查询语句。
    • 在插入或删除数据时,整型自增主键会在叶子结点的末尾建立新的叶子节点,不会破坏左侧子树的结构。UUID 主键很容易出现这样的情况,B+Tree 为了维持自身的特性,有可能会进行结构的重构,消耗更多的时间
  • Q:为什么非主键索引结构叶子节点存储的是主键值?
    • 保持数据的一致性和节省存储空间
  • Q:索引哪些情况会失效?
    • 查询条件包含 or,可能导致索引失效
    • 如何字段类型是字符串,where 时一定用引号括起来,否则索引失效
    • like 通配符可能导致索引失效(在最左边使用了通配符)
    • 联合索引,查询时的条件列不是联合索引中的第一个列,索引失效
    • 在索引列上使用 mysql 的内置函数,索引失效
    • 对索引列运算(如,+、-、* 、/),索引失效
    • 索引字段上使用(!= 或者 < >,not in)时,可能会导致索引失效
    • 索引字段上使用is null, is not null,可能导致索引失效
    • 左连接查询或者右连接查询查询关联的字段编码格式不一样,可能导致索引失效
    • mysql 估计使用全表扫描要比使用索引快,则不使用索引

SQL 语句

  • count( * ) 和 count(1) 和 count(列名) 的区别?
  • count(expr) 函数

    image.png

    • 意思就是
      1. COUNT(expr) ,返回 SELECT 语句检索的行中 expr的值不为 NULL 的数量,结果是一个 BIGINT 值
      2. 如果查询结果没有命中任何记录,则返回 0
      3. COUNT( * ) 的查询结果中会包含值为 NULL 的行数
    • count( * ) 是 SQL92 中规定的标准的统计行数的语法,与数据库无关,无是否为 NULL 值无关
  • COUNT(列名)、COUNT(常量) 和 COUNT( * )

    • 根据上面的 count 函数可知,此时的 expr 对应的分别为:列名、常数、*
    • COUNT( * ) 和 COUNT(常量) 和 COUNT(列名) 在查询结果上的区别
      • COUNT(常量) 和 COUNT( * ) 表示的是直接查询符合条件的数据库表的行数
      • COUNT(列名) 表示的是查询符合条件的列的值不为 NULL 的行数
    • COUNT( * ) 和 COUNT(1) 有区别吗?下面有两种说法:
      1. 有的说 COUNT( * ) 执行时会转换成 COUNT(1),所以 COUNT(1) 少了转换步骤,所以更快
      2. 还有的说,因为 MySQL 针对 COUNT( * ) 做了特殊优化后文会讲,所以COUNT( * ) 更快
      • 实际上,官方文档做出了如下回答:
        InnoDB handles SELECT COUNT(*) and SELECT COUNT(1) operations in the same way. There is no performance difference.
        复制代码
      • 对于 COUNT(1) 和 COUNT( * ),MySQL 的优化是完全一样的,根本不存在谁比谁快!
    • COUNT(列名)
      • COUNT(列名) 的查询过程就是进行全表扫描,然后判断指定字段的值是不是为 NULL,不为 NULL 则累加
      • 相比COUNT( * ),COUNT(列名) 多了一个步骤就是判断所查询的字段是否为 NULL,所以他的性能要比COUNT( * ) 慢
    • COUNT( * ) 的优化
      • MyISAM 存储引擎中会直接把表的总行数单独记录下来供 COUNT( * ) 查询
      • InnoDB 存储引擎则会在扫表的时候选择最小的索引来降低成本
      • 注意:这些优化的前提都是没有进行 where 和 group 的条件查询
  • 总结

    • COUNT( * ) = COUNT(1) > COUNT(列名)
  • MySQL中 in和 exists 的区别?
  • 先对 EXISTS 和 IN 做一个简单的介绍

  • EXIST:指定一个子查询,检测行的存在

    • exists() 后面的子查询被称做相关子查询,他是不返回列表的值的,只是返回一个 ture 或 false 的结果,其运行方式是先运行主查询一次,再去子查询里查询与其对应的结果。如果是 ture 则输出,反之则不输出
    • 再根据主查询中的每一行去子查询里去查询(一个 loop 循环结构)
    • 如下:
      select * from user where exists (select 1);
      复制代码
      • 对 user 表的记录逐条取出,由于子条件中的 select 1 永远能返回记录行,那么 user 表的所有记录都将被加入结果集,所以与 select * from user; 是一样的
    • 又如下:
      select * from user where exists (select * from user where user_id = 0);
      复制代码
      • 可以知道对 user 表进行 loop 时,检查条件语句(select * from user where user_id = 0),由于 user_id 永远不为 0,所以条件语句永远返回空集,条件永远为 false,那么 user 表的所有记录都将被丢弃
  • IN:确定给定的值是否与子查询或列表中的值相匹配

    • in() 后面的子查询是返回结果集的,换句话说执行次序和 exists() 不一样。子查询先产生结果集,然后主查询再去结果集里去找符合要求的字段列表去。符合要求的输出,反之则不输出
    • 注意:in 所对应的 select 语句返回的结果一定是一列!可以为多行
  • 关系

    • sql 经过改变,二者是可以达到同一个目标的
    SELECT *  FROM p_user_2 
    WHERE id IN ( 
      SELECT id FROM p_user 
    );
    
    SELECT *  FROM p_user_2 
    WHERE EXISTS (
      SELECT id  FROM p_user 
      WHERE 
        id = p_user_2.id 
    );
    复制代码
  • 对比

    • in 是把外表和内表作 hash 连接,而 exists 是对外表作 loop 循环,每次 loop 循环再对内表进行查询
    • in 其实与等于相似,比如 in(1,2) 就是 = 1 or = 2 的一种简单写法,所以一般在元素少的时候使用 in,如果多的话就用 exists
    • exists 一般都需要和子表进行关联,而且关联时则需要用索引,这样就可以加快速度
    • in 可以与子查询一起使用,也可以直接 in (a, b…)
    • exist 会针对子查询的表使用索引
    • not exist会对主子查询都会使用索引
    • in 与子查询一起使用的时候,只能针对主查询使用索引
    • not in 则不会使用任何索引
    • 注意:一直以来认为 exists 比 in 效率高的说法是不准确的
  • 案例

    • 案例一
      SELECT * FROM A 
      WHERE id IN (
          SELECT id FROM B
      )
      复制代码
      • 以上查询使用了 in 语句,in() 只执行一次,它查出 B 表中的所有 id 字段并缓存起来。之后检查 A 表的 id 是否与 B 表中的 id 相等。如果相等则将 A 表的记录加入结果集中,直到遍历完 A 表的所有记录。它的查询过程类似于以下过程
      List resultSet=[];
      Array A=(select * from A);
      Array B=(select id from B);
      
      for(int i=0;i<A.length;i++) {
         for(int j=0;j<B.length;j++) {
            if(A[i].id==B[j].id) {
               resultSet.add(A[i]);
               break;
            }
         }
      }
      return resultSet;
      复制代码
    • 结论一
      • 可以看出,当 B 表数据较大时不适合使用 in(),因为它会 B 表数据全部遍历一次
        • 比如:A 表有 10000 条记录,B 表有 1000000 条记录,那么最多有可能遍历 10000 * 1000000 次,效率很差
        • A 表有 10000 条记录,B 表有 100 条记录,那么最多有可能遍历 10000 * 100 次,遍历次数大量减少
      • in() 适合 B 表比 A 表数据小的情况
    • 案例二
      SELECT a.* FROM A a
      WHERE EXISTS (
          SELECT 1 FROM B b
          WHERE a.id = b.id
      )
      复制代码
      • 以上查询使用了 exists 语句,exists() 会执行 A.length 次,它并不缓存 exists() 结果集。因为 exists() 结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回 true,没有则返回 false。它的查询过程类似于以下过程
      List resultSet=[];
      Array A=(select * from A)
      for(int i=0;i<A.length;i++) {
         if(exists(A[i].id) {    // SELECT 1 FROM B b WHERE a.id = b.id 是否有记录返回
             resultSet.add(A[i]);
         }
      }
      return resultSet;
      复制代码
    • 结论二
      • 当 B 表比 A 表数据大时适合使用 exists(),因为它没有那么遍历操作,只需要再执行一次查询就行
      • exists() 适合 B 表比 A 表数据大的情况
  • 总结

    • EXISTS 与 IN 的使用效率的问题,通常情况下采用 EXISTS 要比 IN 效率高,因为 IN 不走索引,但要看实际情况具体使用:
      • IN 适合于外表大而内表小的情况
      • EXISTS 适合于外表小而内表大的情况
      • 当内表数据与外表数据一样大时,IN 与 EXISTS 效率差不多,可任选一个使用
SQL 查询语句执行顺序
  • 对于查询处理,可将其分为逻辑查询处理物理查询处理
    • 逻辑查询处理表示执行查询应该产生什么样的结果
    • 物理查询代表 MySQL 数据库是如何得到该结果的
    • 两种查询的方法可能完全不同,但是得到的结果必定是相同的
  • SQL 查询案例

    • 我们先从一个查询语句去解读相关 SQL 命令的作用
    • 创建表结构并插入数据
    -- 创建 customers 表
    CREATE TABLE customers (
            customer_id VARCHAR(10),
            city VARCHAR(10) NOT NULL,
            PRIMARY KEY (customer_id)
    ) ENGINE = InnoDB;
    
    -- 向 customers 表插入数据
    INSERT INTO customers
    VALUES ('163', 'HangZhou'),
            ('9you', 'ShangHai'),
            ('TX', 'HangZhou'),
            ('baidu', 'HangZhou');
    
    -- 创建 orders 表
    CREATE TABLE orders2 (
            order_id INT AUTO_INCREMENT,
            customer_id VARCHAR(10),
            PRIMARY KEY (order_id)
    ) ENGINE = InnoDB;
    
    -- 向 orders 表插入数据
    INSERT INTO orders2
    VALUES (1, '163'),
            (2, '163'),
            (3, '9you'),
            (4, '9you'),
            (5, '9you'),
            (6, 'TX'),
            (7, NULL);
    复制代码
    • 表记录

    image.png

    image.png

    • 执行查询语句:【查询来自杭州且订单数少于2的客户,并且查询出他们的订单数量,查询结果按订单数从小到大排序】
    SELECT
        c.customer_id,
        count( o.order_id ) AS total_orders 
    FROM
        customers AS c
        LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id 
    WHERE
        c.city = 'HangZhou' 
    GROUP BY
        c.customer_id 
    HAVING
        count( o.order_id ) < 2 
    ORDER BY
        total_orders DESC;
    复制代码
    • 查询结果

    image.png

  • 逻辑查询顺序

    • 查询语句的一般结构如下
    (8)SELECT (9)DISTINCT <select_list>
    (1)FROM <left_table>
    (3)<join_type>JOIN <right_table>
    (2)ON<join_condition>
    (4)WHERE<where_condition>
    (5)GROUP BY<group_by_list>
    (6)WITH {CUBE|ROLLUP}
    (7)HAVING<having_condition>
    (10)ORDER BY<order_by_list>
    (11)LIMIT<limit_number>
    复制代码
    • 查询语句中的序号为该查询语句的处理顺序,可以看到一共有 11 个步骤,最先执行的是 FROM 操作,最后执行的是 LIMIT 操作
    • 每个操作都会产生一张虚拟表,该虚拟表作为一个处理的输入。这些虚拟表对用户是透明的,只有最后一步生成的虚拟表才会返回给用户
    • 具体分析各个阶段
      1. FROM:对 FROM 子句中的左表 <left_table> 和右表 <right_table> 执行笛卡儿积(Cartesianproduct),产生虚拟表 VT1
      2. ON:对虚拟表 VT1 应用 ON 筛选,只有那些符合 <join_condition> 的行才被插入虚拟表 VT2 中
      3. JOIN:如果指定了 OUTER JOIN(如LEFT OUTER JOIN、RIGHT OUTER JOIN),那么保留表中未匹配的行作为外部行添加到虚拟表 VT2 中,产生虚拟表 VT3。如果 FROM 子句包含两个以上表,则对上一个连接生成的结果表 VT3 和下一个表重复执行步骤(1)~步骤(3),直到处理完所有的表为止
      4. WHERE:对虚拟表 VT3 应用 WHERE 过滤条件,只有符合 <where_condition> 的记录才被插入虚拟表 VT4 中
      5. GROUP BY:根据 GROUP BY 子句中的列,对 VT4 中的记录进行分组操作,产生 VT5
      6. CUBE|ROLLUP:对表 VT5 进行 CUBE 或 ROLLUP 操作,产生表 VT6
      7. HAVING:对虚拟表 VT6 应用 HAVING 过滤器,只有符合 <having_condition> 的记录才被插入虚拟表 VT7 中
      8. SELECT:第二次执行 SELECT 操作,选择指定的列,插入到虚拟表 VT8 中
      9. DISTINCT:去除重复数据,产生虚拟表 VT9
      10. ORDER BY:将虚拟表 VT9 中的记录按照 <order_by_list> 进行排序操作,产生虚拟表 VT10
      11. LIMIT:取出指定行的记录,产生虚拟表 VT11 ,并返回给查询用户
  • 分析上面的查询 SQL 的执行过程

    1. 第一步:需要做的是对 FROM 子句前后的两张表进行笛卡儿积操作,也称做交叉连接(CrossJoin),生成虚拟表 VT1

      • 如果 FROM 子句前的表中包含 a 行数据,FROM 子句后的表中包含 b 行数据,那么虚拟表 VT1 中将包含 a * b 行数据,得到的结果集中前表的列在前,后表的列在后
      FROM customers as c ....... JOIN orders as o
      复制代码

      image.png

    2. 应用 ON 过滤器:SELECT 查询一共有 3 个过滤过程,分别是 ON、WHERE、HAVING。ON 是最先执行的过滤过程。根据上一小节产生的虚拟表 VT1,过滤条件为

      ON c.customer_id = o.customer_id
      复制代码

      image.png

    3. 添加外部行:这一步只有在连接类型为OUTER JOIN时才发生

      • 如LEFT OUTER JOIN、RIGHT OUTERJOIN、FULL OUTER JOIN,虽然在大多数时候我们可以省略 OUTER 关键字,但 OUTER 代表的就是外部行
      • LEFT OUTER JOIN 把左表记为保留表,RIGHT OUTER JOIN 把右表记为保留表,FULL OUTER JOIN 把左右表都记为保留表
      • 添加外部行的工作就是在 VT2 表的基础上添加保留表中被过滤条件过滤掉的数据,非保留表中的数据被赋予 NULL 值,最后生成虚拟表 VT3
      • 在这个例子中,保留表是 customers,顾客 baidu 在 VT2 表中由于没有订单而被过滤,因此 baidu 作为外部行被添加到虚拟表 VT2 中,将非保留表中的数据赋值为 NULL
      • 如果需要连接表的数量大于 2,则对虚拟表 VT3 重做本节首的步骤(1)~步骤(3),最后产生的虚拟表作为下一个步骤的输出
      customers as c LEFT JOIN orders as o
      复制代码

      image.png

    4. 应用 WHERE 过滤器:对上一步骤产生的虚拟表 VT3 进行 WHERE 条件过滤,只有符合 <where_condition> 的记录才会输出到虚拟表 VT4 中

      WHERE c.city='HangZhou'
      复制代码

      image.png

      • 注意:在当前应用 WHERE 过滤器时,有两种过滤是不被允许的
        • 由于数据还没有分组,因此现在还不能在 WHERE 过滤器中使用统计过滤,如
          SELECT customer_id, count(customer_id) FROM ordersWHERE COUNT(customer_id) < 2;
          复制代码
        • 由于没有进行列的选取操作,因此在 SELECT 中使用列的别名也是不被允许的,如
          SELECT order_id as o, customer_id as cFROM ordersWHERE c = '163';
          复制代码
      • 在 WHERE 过滤器中进行的过滤和在 ON 过滤器中进行的过滤是有所不同的
        • 对于 OUTER JOIN 中的过滤,在 ON 过滤器过滤完之后还会添加保留表中被 ON 条件过滤掉的记录
        • 对于 WHERE 条件中被过滤掉的记录则是永久的过滤
    5. 分组:在本步骤中根据指定的列对上个步骤中产生的虚拟表进行分组,最后得到虚拟表 VT5

      GROUP BY c.customer_id
      SELECT * FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id;
      复制代码

      image.png

    6. 应用 ROLLUP 或 CUBE:如果指定了 ROLLUP 选项,那么将创建一个额外的记录添加到虚拟表 VT5 的最后,并生成虚拟表 VT6

      • 因为我们的查询并未用到 ROLLUP,所以将跳过本步骤
    7. 应用 HAVING 过滤器:这是最后一个条件过滤器了,之前已经分别应用了 ON 和 WHERE 过滤器

      • 在该步骤中对于上一步产生的虚拟表应用 HAVING 过滤器,HAVING 是对分组条件进行过滤的筛选器
      • 对于示例的查询语句,经过分组条件后,将 customer_id 为 163 的订单从虚拟表中删除,生成的虚拟表 VT6 虚拟表 VT6
      HAVING count(o.order_id < 2)
      SELECT * FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2;
      复制代码

      image.png

    8. 处理 SELECT 列表:虽然SELECT是查询中最先被指定的部分,但是直到步骤8)时才真正进行处理。在这一步中,将SELECT中指定的列从上一步产生的虚拟表中选出 SELECT部分为:

      SELECT c.customer_id,count(o.order_id) AS total_orders
      SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2;
      复制代码

      image.png

    9. 应用 DISTINCT 子句:如果在查询中指定了 DISTINCT 子句,则会创建一张内存临时表(如果内存中存放不下就放到磁盘上)

      • 这张内存临时表的表结构和上一步产生的虚拟表一样,不同的是对进行 DISTINCT 操作的列增加了一个唯一索引,以此来去除重复数据
      • 对于使用了GROUP BY 的查询,再使用 DISTINCT 是多余的,因为已经进行分组,不会移除任何行
      • 由于在这个 SQL 查询中未指定 DISTINCT,因此跳过本步骤
    10. 应用 ORDER BY 子句:根据 ORDER BY 子句中指定的列对上一步输出的虚拟表进行排列,返回新的虚拟表

      • 还可以在 ORDER BY 子句中指定 SELECT 列表中列的序列号,如下面的语句 2,通常情况下,并不建议采用这种方式来进行排序,因为程序员可能修改了 SELECT 列表中的列,而忘记修改 ORDER BY 中的列表
      ORDER BY total_orders DESC
      SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2 ORDER BY total_orders DESC;
      SELECT c.customer_id, count(o.order_id) as total_orders FROM customers AS c LEFT JOIN orders2 AS o ON c.customer_id = o.customer_id  WHERE c.city = 'HangZhou'  GROUP BY c.customer_id HAVING count(o.order_id) < 2 ORDER BY 2, 1;
      复制代码

      image.png

      image.png

      • 注意:下面有两个经常犯错的地方
        • 在MySQL数据库中,NULL值在升序过程中总是首先被选出,即 NULL 值在 ORDER BY 子句中被视为最小值
        • 很多开发人员都错误地认为在选取表中的数据时,记录会按照表中主键的大小顺序地取出,即结果像进行了 ORDER BY 一样,实际上不是这样的
      • 扩展
        • 关系数据库是在数学的基础上发展起来的,关系对应于数学中集合的概念。数据库中常见的查询操作其实对应的是集合的某些运算:选择、投影、连接、并、交、差、除。最终的结果虽然是以一张二维表的方式呈现在用户面前,但是从数据库内部来看是一系列的集合操作。因此对于表中的记录,用户需要以集合的思想来理解
        • 因为表中的数据是集合中的元素,而集合是无序的。因此对于没有 ORDER BY 子句的 SQL 语句,其解析结果应为:从集合中选择期望的子集合,这表明结果并不一定要有序
    11. LIMIT 子句:从上一步骤的虚拟表中选出从指定位置开始的指定行数据

      • 对于没有应用 ORDER BY 的 LIMIT 子句,结果同样可能是无序的,因此 LIMIT 子句通常和 ORDER BY子句一起使用
      • 由于本 SQL 语句没有 LIMIT 子句,将跳过
  • 物理查询处理

    • 数据库也许并不会完全按照逻辑查询处理的方式来进行查询
    • 在 MySQL 数据库服务层有解析器和优化器两个组件,解析器的工作就是分析 SQL 语句,而优化器的工作就是对这个 SQL 语句进行优化,选择一条最优的路径来选取数据,但是必须保证物理查询处理的最终结果和逻辑查询处理是相等的
  • 总结

image.png

JOIN 连接
  • 常见的 JOIN 可以分为以下七种

image.png

  • 下面通过建立两张表来解释这七种 JOIN

    CREATE TABLE customers (
        customer_id VARCHAR(10),
        city VARCHAR(10) NOT NULL,
        PRIMARY KEY (customer_id)
    ) ENGINE = InnoDB;
    
    CREATE TABLE orders2 (
        order_id INT AUTO_INCREMENT,
        customer_id VARCHAR(10),
        PRIMARY KEY (order_id)
    ) 
    复制代码

    image.png

  • LEFT JOIN

    • 返回包括左表(A)中的所有记录 + 右表(B)中联结字段相等的记录
    • 左表(A)的记录将会全部表示出来,而右表(B)只会显示符合搜索条件的记录(例子中为: customers.customer_id = orders2.customer_id) B 表记录不足的地方均为 NULL
    SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id;
    复制代码

    image.png

  • RIGHT JOIN

    • 返回包括右表(B)中的所有记录 + 左表(A)中联结字段相等的记录
    • 右表(B)的记录将会全部表示出来,而左表(A)只会显示符合搜索条件的记录(例子中为: customers.customer_id = orders2.customer_id) A 表记录不足的地方均为 NULL
    SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id;
    复制代码

    image.png

  • INNER JOIN

    • 只返回两个表中联结字段相等的记录
    SELECT * FROM customers INNER JOIN orders2 ON customers.customer_id = orders2.customer_id;
    复制代码

    image.png

  • LEFT JOIN 且不含右表的内容

    • 意味着我们只返回右表为 NULL 的记录
    SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE orders2.customer_id IS NULL;
    复制代码

    image.png

    • RIGHT JOIN 且不含左表的内容
    • 意味着我们只返回左表为 NULL 的记录
    SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
    复制代码

    image.png

  • FULL JOIN

    • 两边的所有内容都要有,连得起的就连,连不起的就补充 NULL
    • MySQL 语法不支持 FULL OUTER JOIN,所以我们用 UNION 来实现
    SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id
    UNION
    SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
    复制代码

    image.png

  • FULL JOIN 且不含交集

    SELECT * FROM customers LEFT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE orders2.customer_id IS NULL
    UNION
    SELECT * FROM customers RIGHT JOIN orders2 ON customers.customer_id = orders2.customer_id WHERE customers.customer_id IS NULL;
    复制代码

    image.png

相关面试题
  • Q:count( * ) 和 count(1) 和 count(列名) 的区别?
  • Q:MySQL中 in和 exists 的区别?
  • Q:什么是左连接、右连接,有什么区别?

后续内容

参考与感谢

文章分类
后端
文章标签