MySQL基础1

Posted on | In |
Words count in article 54k | Reading time 49 mins.

1. 数据类型相关

1.1 数据类型分类

四类： 字符串、日期 / 时间、数值以及二进制

  • 字符串类型：以 char、varchar、text 为代表，用于存储字符、字符串数据
  • 日期 / 时间类型：以 date、time、datetime、timestamp 为代表，用于存储日期或时间，这种数据类型也是比 较难抉择的
  • 数值类型：以 tinyint、int、bigint、float、double、decimal 为代表，用于存储整数或小数
  • 二进制类型：以 tityblob、blob、mediumblob、longblob 为代表，用于存储二进制数据，适用场景最为受限

1.2 数据类型基本介绍

1.2.1 字符串类型

char

  • 固定长度的字符串，长度范围1~255之间，且必须是在创建表时制定
  • 特别：如果未达到指定长度，会使用空格填充到指定长度——如果我们想要存储不同记录的字符串长度差别较大，会造成较大的空间浪费
  • 当我们需要存储一些长度固定的数据列时，使用 char 是非常合适的：手机号码、身份证号

varchar

  • 它定义了一个可变长度的字符串，创建时指定它所允许的 最大长度。
    • 例如，如果创建时声明了 varchar (x)，则只能存储不超过 x 个字符的数据，且 x 的最大值是 65535。
  • 对于长度不固定的数据列，使用 varchar 就是最合适的：姓名、邮箱地址
  • 区别char、varchar
    • 定义了 char (x)，如果存入的字符个数小于 x，则以空格填充，查询时再将空格去掉（类似于 trim 操作）。所以，char 类型存储的字符串末尾不能有空格，而 varchar 则没有这一限制
    • char (x) 长度是固定的，不论存入什么，都会占用 x 个字节。但是 varchar 占用的字节数是存入的字符数 + 1（x <= 255）或 + 2（x > 255）
    • char 由于长度固定，不需要考虑边界问题，检索速度要快于 varchar

tinytext、text、mediumtext、longtext：变长字符串，区别是存储空间的不同

  • tinytext：最大长度是（2^8 - 1）个字符
  • text：最大长度是（2^16 - 1）个字符
  • mediumtext：最大长度是（2^24 - 1）个字符
  • longtext：最大长度是（2^32 - 1）个字符
  • 当我们存储的数据量比较大，应考虑使用文本。建议数据量超过500个字符时，应考虑使用文本。
  • 文本类型不能有默认值，且在创建索引时需要指定前多少个字符。

1.2.2 日期/时间类型

date

  • 存储范围是 ‘1000-01-01’ 到 ‘9999-12-31’
  • 场景有限，只能存储 “年月日”。常用出生年月

time

  • 用于存储时间，不仅可以表示一天中的时间，也可以用于表示两个时间的时间间隔
  • MySQL 将 time 的小时范围扩大了，而且支持负值
  • time 允许以 “D HH:MM:SS” 的格式存储。其中，D 的取值是 0 ~ 34。如果要 存储时间间隔，time 则是以（时间间隔 小时）作为小时进行存储。它的计算公式是：D 24 + HH。例如，插入了 “2 19:20:00”，相当于插入 67:20:00

datetime

  • 取值范围是 ‘1000-01-01 00:00:00.000000’ 到 ‘9999-12-31 23:59:59.999999’。
  • 它是最常见，用途最广的数据类型。例如：存储数据插入时 间、订单完成时间等等

timestamp

  • 它的取值范围是：‘1970-01-01 00:00:01.000000’ UTC 到 ‘2038-01-19 03:14:07.999999’ UTC。它与 datetime 的主要区别在于时间范围要小一些
  • timestamp 是与时区相关的，能够反映 “当前时间”。当插入时间时，会先转换为本地时区后再存储；查询时 间时，会转换为本地时区后再显示。所以，不同时区的人看到的同一时间是不一样的。

通常 datetime 是最佳选择。理由如下：

  • 时间范围跨度足够大，能够满足所有的时间需求
  • 即使是只用于存储日期或时间，也可以存储日期时间，只需要在代码中处理即可。避免将来需求变更时对数据 表的 Schema 有所变动

1.2.3 数值类型

1.2.3.1 整数类型

数据类型 占据空间 范围（有符号） 范围（无符号） 描述
tinyint 1个字节 -2^7 - 2^7 - 1 0-255 小整数值
smallint 2个字节 -2^15 - 2^15 - 1 0-65535 大整数值
mediumint 3个字节 -2^23 - 2^23 - 1 0-16777215 大整数值
int 4个字节 -2^31 - 2^31 - 1 0 - 4294967295 大整数值
bigint 8个字节 -2^63 - 2^63 - 1 0 - 18446744073709551615 极大整数值

1.2.3.2 浮点类型

  • float (M, D)：其中 M 定义显示长度，D 定义小数位数。但是它们是可选的，且默认值是 float (10, 2)，2 是小数的位数，10 是数字的总长（包括小数）。它的小数精度可以到 24 个浮点。
  • double (M, D)：M 和 D 的含义与 float 是相同的，默认值是 double (16, 4)。它的小数精度可以达到 53 位。

1.2.3.3 定点类型

  • decimal 被称为定点数据类型，由于它保存的是精确值，所以它通常用于精度要求非常高的计算中。
  • 另外，也可以利用 decimal 去保存比 bigint 还要大的整数值。
  • MySQL 自身实现了对 decimal 的高精度计算
  • 假如我们定义了 decimal (18, 9)：
    • 不包含小数点的数字总数（整数位数 + 小数位数）位数是 18，不指定的情况下默认是 10
    • 9 则代表小数的位数，如果不指定，默认是 0

1.2.4 二进制类型

二进制数据类型理论上可以存储任何数据，可以是文本数据，也可以存储图像或者其他多媒体数据。二进制数据类型相对于其他的数据类型来说，使用频率是比较低的。

  • tityblob:最大支持255字节
  • blob:最大支持64KB
  • mediumblob:最大支持16MB
  • longblob:最大支持4GB

1.3 数据类型使用建议

  1. 使用NOT NULL

    这个建议适用于所有的数据类型，MySQL 在索引值为 NULL 的列时，需要额外的存储空间，所以，相对于NOT NULL 来说，NULL 会占用更多的空间。另外，在进行比较和计算时，MySQL 要对 NULL 值做特别的处理，使用效 率较低。

  2. 带有 COMMENT

    COMMENT 用于定义列的注释信息，就好像我们在写代码一样，把重要的或者不易理解的地方，加上一些注释， 方便以后查阅。

  3. 使用存储需要的最小数据类型：在满足需求的同时选择最小的。

    例如，要存储事件状态，可以选择tinyint；要存储班级人数，可以选择smallint等等。关于最小数据类型，它有两大优势：

    • 越小的数据类型占用的磁盘、内存、CPU缓存都会更小，存取速度也会更快
    • 小的数据类型建立索引时所需要的空间也相对较小，这样一页中所能存储的索引节点数量也就越多，遍历时IO次数就会越少，索引的性能也就越好

  4. 选择简单的数据类型
    例子：在一列中存储10、100、201这样的数据，我们可以选择使用int或varchar来存储。但是整型要比字符型的操作复杂度小太多，那么，选择整型（例如int)就是最简单的数据类型。

  5. 尽量避免使用text和blob

    MySQL内存临时表并不支持text、blob这样的大数据类型，如果查询时包含有这样的数据，则排序操作必须使用磁盘临时表，性能会下降很多。而且对于这种数据，MySQL还要做二次查询（因为MySQL实际保存的是指针，而不是真实数据），会使SQL性能变得很差。

    但是，也并不是说我们一定就不能用text和blob。如果确实有需求需要使用这样的数据类型，那么在查询时一定不要直接SELECT*,而是取出需要的列。这样MySQL就不会去主动查询这些数据列，也是提高性能的一种惯用手段。最后，还需要注意，因为MySQL对索引长度的限制，text类型只能用到前缀索引，并且由于存储的是指针，txt列上不能有默认值。

1.4 Q&A

  1. 将时间转换为时间戳，并使用int或者bigint类型去存储，这么做可行度？

    date类型的时间要精确到分秒才能转成13位的long类型,且必须通过数据库语句,在数据库中进行插入bigint类型的操作,java中bigint类型只能支持11位.

  2. 大多数时候，我们会选择将主键设置为bigint数据类型，你知道这是为什么吗？

    https://blog.csdn.net/nrsc272420199/article/details/102877399

    • （1）mysql索引B+树的高度应为：2-4
      （2）一个bigint类型的数据为8B，一个integer类型的数据为4B，在B+树上每个指针的大小为6B，如下图：在这里插入图片描述

      这样的话，对于B+树来说一个非叶子节点能存储的索引的个数为：

      主键为integer类型 ： 16384/(4+6) ≈ 1638个
      主键为bigint类型：16384/(8+6)≈ 1170个

      假如每条数据大小为1K（1024B）的话，一个叶子节点就可以存大概16条数据

      那假如B+树的高度为4层的话，那mysql存储的所有数据的条数应为：

      主键为integer类型：1638 1638 1638 16 = 70317217152 大于int的上限42亿
      主键为bigint类型：1170       1170 1170 16 = 25625808000 肯定远小于long的上限，也大于42亿

      也就是说虽然42亿的预留量可能已经非常充足了，但万一不够了呢？

      既然mysql即使是256亿的数据量也仅需4次IO就可以查到，而这个时候int类型的预留量肯定不够了，bigint却十分充足，为什么不用bigint呢？

      我想得可能正是基于这个原因，阿里才会强制要求mysql表的主键应为bigint unsigned类型。

2. Schema设计规范

Schema 设计指的是对数据表的的设计，在实际业务中，也可以把它的概念扩展为对库和表的设计。

2.1 Schema设计的目标

可用 + 好用

  • 可用

    • 设计的数据库和数据表能够支撑当前的业务需求，且在技术实现上没有太大的弊端

    • 这个设计目标的核心其实是对需求的理解。确实，理清了需求，你会得出结论：

      应该存储哪些数据、这些数据是什么类型、在代码中怎样使用这些数据等等。余下的建库建表也自然就水到渠成了

  • 好用

    • 需求也许不会变化，但是随着业务量的增长触发数据和并发的增长，数据库是否还能保持相对较高的性能是个 值得思考的问题，同时也是衡量设计目标是否好用的重要指标。
    • 无论什么时候，我们对 MySQL（数据库）的使用都肯定是围绕数据的增删改查。而这些基本的操作，当数据 量加速膨胀的过程中，也会引起性能瓶颈。所以，好用的设计目标讲究能够 “预见未来”，能够对未来做出预判。例如：将通用信息单独使用一张表存储、建立适当的索引等等

2.2 Schema通用的设计原则

  1. 使用小写的名称，且只有英文字母：不论是库、表还是数据列，应该是只包含英文字母的名称，不要出现特殊 字符或者是数字。这也比较好理解，英文字母不论是阅读还是编码都非常的便捷。另外，由于 MySQL 是大小 写不敏感的，选择一律小写的名称能够统一书写规则，避免不必要的书写错误。
  2. 取一个有意义的名称，单词之间使用下划线连接：除了基本的名称书写规范之外，取一个有意义的名称是非常 有必要的。例如：我们需要创建学生表，表的名称叫做 student 就会比 other 更易理解。当然，可能有些时候 我们无法用一个单词表达清楚想要的含义，此时，可以使用多个单词，且单词之间使用下划线连接，例如： insert_time。最后，名称不要过长，最长不要超过 32 个字符。
  3. 记住 “够用且尽量小” 的原则：很明显，这条原则对应的是数据表列的数据类型选择问题。占用空间少的数据类 型最直接的优势就是减少了用户数据存储空间和索引存储空间，这对于数据传输与检索的性能提高有着巨大意 义。
  4. 不要使用物理外键：物理外键是说让数据库去管理表与表之间的关联关系，而它相对的逻辑外键，则是我们自己用代码去管理这种关系。这是因为物理外键存在两个重大缺陷：消耗数据库资源，降低数据库实例可扩展 性；母表一旦受损，子表很难恢复，造成数据丢失。
  5. 表一定要有主键：MySQL 并不要求表一定要有主键，但是主键的作用是能够唯一区分表中的每一行。没有主键，更新或删除表中的特定行将会很困难，因为没有安全的方法保证只涉及相关的行。并且，主键能够为方便 扩展、高可用的数据库系统做铺垫。
  6. 保持一致的字符集：库、表、数据列的字符集都应该是一致的，统一为 utf8 或 utf8mb4。字符集编码不仅影响 数据存储，还会影响客户端与数据库之间的交互，最常见的问题就是字符集导致的乱码。所以，相同的字符集更利于管理，也更方便去排查问题。

2.3 库设计规范

MySQL 自身并没有对库的容量做出限制，也就是说，你几乎不用考虑表的数量上限问题。但是，当表的数量越

多，越容易产生以下问题（以下所讨论的都是单个库）：

  • 表越多，需要维护的元数据（表结构、统计信息等）就会越多。即使是这些元数据只占据很少的空间，但是也 会让管理这些元数据变得很复杂，且通常也是不合理的需求分析造成的；
  • 表越多，可能存储的数据量也会越大，这无疑会给数据库造成压力。且大量的数据聚集在同一个库中也是非常 危险的，一旦出现库损坏，丢失的数据量也会更多。

综上所述，我们讨论了单库中表太多的缺陷，再去结合日常的工作实践来说，建议大家在一个库中创建的表数量不 要超过 200。更常见的情况是，一个库中只维护几十到 100 张表

2.4 表设计规范

2.4.1 范式、反范式的选择

控制冗余的反范式模式 | 毫末室 (songzi.info)

  • 范式：核心思想是数据只出现一次，不存在信息冗余。

    范式化设计的优点：使用更少的存储空间 ；由于没有冗余存储，增删改查的速度相对较快

    1. 列不可再分
    2. 属性完全依赖于主键
    3. 属性不依赖于其它非主属性 属性直接依赖于主键

  • 反范式：破坏了范式的规范，它允许出现冗余的数据。所以，问题在于：冗余字段是否是可取的。

    如果我们想要的数据出现在两张或者多张表中，对于范式不存在冗余的设计，就不得不采用关联查询。而这恰恰是反范式设计最大的优势，适当的冗余设计，可以减少或避免表关联，提高查询效率。

2.4.2 宽表、窄表的选择

MySQL 对于每张表有 4096 个列 的硬限制，而真正在使用上的限制又会取决于你所使用的存储引擎。例如：对于 InnoDB 来说，一张表最多可以有 1017 列。在不考虑 “宽和窄” 的问题上来说，MySQL 和存储引擎支持的列数目肯定是足够的了。

以 40 列为界，超过 40 列的表，我们可以称之为宽表， 相对的，少于 40 列的表，我们称之为窄表。但同时，需要知道，这里的数字是人为定义的，MySQL 规范中并没 有这种定义。我这里的划分是基于工作经验和总结

对应的优缺点

  • 窄表较多，数据列会更加分散，编写关联查询的难度就会很大
  • 数据项会有不同的安全级别，宽表中涉及的列过多，数据权限的管理会带来很大的挑战
  • 窄表数据量通常较少，但是等量的数据项会创建更多的表，管理难度大
  • 宽表数据量通常较大，单表占据的存储空间过大，会降低排序、分组等查询的性能

2.4.3 合理的索引是提升性能的关键

典型的以空间换时间的优化策略：索引的存在，也会使插入、删除、更新的性能降低，因为这些操作都会伴随着索引的修改。所以，这一条设计规范所要追求的是空间与时间的平衡，达到既不占用过多的存储空间，也有较高的查询性能。

索引创建和使用的特性：

  • 一定要为作为搜索条件的字段创建索引，不是搜索条件的字段建索引反而会降低使用性能
  • 选择区分度高的字段作为索引字段，重复性高的字段不要加索引
  • 联合索引存在 “最左前缀” 的特性，不要建多余的索引

2.5 Q&A

  • 如果你的表没有定义主键，你知道 MySQL 会怎么做吗 ？

    InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id，而且InnoDB维护了一个全局的dictsys.row_id，所以未定义主键的表都会共享该row_id，每次插入一条数据都把全局row_id当成主键id，然后全局row_id加1。

    该全局row_id在代码实现上使用的事bigint unsigned类型，但实际上只给row_id保留了6字节，所以这种设计就会存在一个问题：如果全局row_id一直涨，直到2的48次幂-1时，这个时候再加1，row_id的低48位都会变为0，如果再插入新一行数据时，拿到的row_id就为0，这样的话就存在主键冲突的可能，所以为了避免这种隐患，每个表都需要一个主键。

  • 我编写的 SQL 语句需要做多表的 join 操作，应该给哪些列建索引呢 ？

    多表关联查询中，关联字段都应该创建索引吗？ - 寻找的路上 - 博客园 (cnblogs.com)

    不管是内联还是外连接关联查询，为各个表建立索引应该要根据当时的条件来建立：

    • 主表（驱动表）根据where中涉及到的列建索引（比如例子中的tb1需要给col1建立索引)
    • 从表根据被关联的列建立索引，如果where中涉及到从表的列，可以考虑结合关联列建立组合索引（注意最左匹配原则）。

3. 聚合与分组聚合

3.1 聚合函数

数据库书分为单行函数、多行函数。

单行函数即函数会针对每一行返回一个结果，而多行函数 则是作用于多行（也可以作用于单行）并返回一个结果。

聚合函数则属于多行函数，表中的多行记录会参与计算， 并返回一个数值，且它通常用于分组的相关统计。

3.2 常用的聚合函数

AVG COUNT MIN MAX SUM

语法 功能 备注
AVG ([DISTINCT] expr) 返回 expr 的平均值 DISTINCT 选项用于去除字段值重复的行记录
COUNT(expr) 统计表中的行数
MIN ([DISTINCT] expr) 返回 expr 的最小值
MAX ([DISTINCT] expr) 返回 expr 的最大值
SUM ([DISTINCT] expr) 返回 expr 的和
  • 每个聚合函数接受一个参数，参数可以是数据表列，也可以是函数表达式
  • 默认情况下，聚合函数会忽略列值为 NULL 的行，不参与计算
  • 聚合函数不允许嵌套，例如：COUNT(SUM(expr)) 是不合法的
  • 一次查询中可以出现多个聚合函数，例如：SELECT MAX(expr), MIN(expr) FROM …
  • 关于 COUNT 函数，总结如下：
    • COUNT(n) 和 COUNT() 用于统计表中的总行数，*不关心列值是否为 NULL
    • COUNT(expr) 用于统计列值非 NULL 的行记录数
    • COUNT(DISTINCT expr) 用于统计列值不同且非 NULL 的行记录数
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT id, type, name, salary FROM worker; 
+----+------+--------+--------+ 
| id | type | name | salary | 
+----+------+--------+--------+ 
| 1 | A | tom | 1800 | 
| 2 | B | jack | 2100 | 
| 3 | C | pony | NULL | 
| 4 | B | tony | 3600 | 
| 5 | B | marry | 1900 | 
| 6 | C | tack | 1200 | 
| 7 | A | tick | NULL | 
| 8 | B | clock | 2000 | 
| 9 | C | noah | 1500 | 
| 10 | C | jarvis | 1800 | 
+----+------+--------+--------+

SELECT AVG(salary) FROMworker;
SELECT AVG(salary) FROMworker WHERE id < 3;
SELECT COUNT(0), COUNT(1), COUNT(9.9), COUNT(*) FROMworker;
SELECT COUNT(salary), COUNT(DISTINCT salary) FROMworker;
SELECT MIN(salary), MAX(salary) FROMworker;
SELECT SUM(salary) FROMworker WHERE id < 5;
SELECT SUM(1) FROMworker; #统计行数，但效率很差

3.3 分组聚合

分组的意思就是数据根据某一列或者某几列分类，MySQL 中可以使用 GROUP BY 子句实现这一功能。GROUP BY 结合聚合函数就可以实现将表数据分类再汇总的效果，这在报表型的数据统计任务中是非常常见的需求。 

1
2
# GROUP BY 子句语法
SELECT <列名1>, <列名2>...... FROM <表名> WHERE ...... GROUP BY <列名1>, <列名2>......;

3.3.1 按照type分组对数据进行统计

需要注意的是，出现在 SELECT 子句中的单独列（非聚合列，示例中的即为 type，必须出现在 GROUP BY 子 句中作为分组列。但是反过来，分组列是可以不出现在 SELECT 子句中的。

1
2
3
4
5
6
7
8
9
SELECT type, AVG(salary), COUNT(1), MIN(salary), MAX(salary), SUM(salary) FROM worker GROUP BY type; 
# 使用 GROUP BY 对 type 字段值进行分组，结果有三类：A、B、C。分组之后，AVG、COUNT 等聚合函数再按照自身的特性对每一组数据进行聚合统计，最后，打印结果。 
+------+-------------+----------+-------------+-------------+-------------+ 
| type | AVG(salary) | COUNT(1) | MIN(salary) | MAX(salary) | SUM(salary) | 
+------+-------------+----------+-------------+-------------+-------------+ 
| A | 1800.0000 | 2 | 1800 | 1800 | 1800 | 
| B | 2400.0000 | 4 | 1900 | 3600 | 9600 | 
| C | 1500.0000 | 4 | 1200 | 1800 | 4500 | 
+------+-------------+----------+-------------+-------------+-------------+

3.3.2 对分组聚合结果进行排序

指定排序的列可以是分组列，也可以不是分组列。

1
2
3
4
5
6
7
8
9
# 按照SUM(salary)实现排序
SELECT type, SUM(salary) as sum_s FROM worker GROUP BY type ORDER BY sum_s desc; 
+------+-------+ 
| type | sum_s | 
+------+-------+ 
| B | 9600 | 
| C | 4500 | 
| A | 1800 | 
+------+-------+

当排序列与分组列相同时，则可以合并 GROUP BY 和 ORDER BY 子句，即只需要在 GROUP BY 子句的后面添加 DESC 或 ASC。

1
2
3
4
5
6
7
8
SELECT type, AVG(salary), COUNT(1), MIN(salary), MAX(salary), SUM(salary) FROM worker GROUP BY type DESC; 
+------+-------------+----------+-------------+-------------+-------------+ 
| type | AVG(salary) | COUNT(1) | MIN(salary) | MAX(salary) | SUM(salary) | 
+------+-------------+----------+-------------+-------------+-------------+ 
| C | 1500.0000 | 4 | 1200 | 1800 | 4500 | 
| B | 2400.0000 | 4 | 1900 | 3600 | 9600 | 
| A | 1800.0000 | 2 | 1800 | 1800 | 1800 | 
+------+-------------+----------+-------------+-------------+-------------+

3.3.3 对分组结果进行过滤

过滤的是分组后的聚合结果，而不是数据表中的原始记录。

在 MySQL 中，使用 AVG、COUNT 等聚合函数对表记录进行统计操作后，可以使用 HAVING 子句对结果进行过滤，且 HAVING 子句需要写在 GROUP BY 子句之后。

1
2
3
4
5
6
7
SELECT type, AVG(salary), COUNT(1), SUM(salary) FROM worker GROUP BY type HAVING SUM(salary) > 4000; 
+------+-------------+----------+-------------+ 
| type | AVG(salary) | COUNT(1) | SUM(salary) | 
+------+-------------+----------+-------------+ 
| B | 2400.0000 | 4 | 9600 | 
| C | 1500.0000 | 4 | 4500 | 
+------+-------------+----------+-------------+

HAVING 的使用方法与 WHERE 是相似的，只是它们执行的时机不同。总结下来，它们有以下两个区别：

  1. WHERE 子句在分组前对记录进行过滤
  2. HAVING 子句在分组后对记录进行过滤

分组聚合的精髓在于数据分组，可以把每一个分组都认为是单独的数据表记录，最终的聚合结果则是将每一个单独数据表聚合之后 merge 而成的。另外，需要知道，聚合函数可以在 SELECT 、HAVING 和 ORDER BY 子句中使用，但是不能在 WHERE 子句中使用。

3.4 Q&A

  • 为什么说 SUM(1) 的执行效率要比 COUNT(n) 或 COUNT(*) 低很多呢 ？

    sum的效率不足体现在cpu上，I/O完全一样。

    在排序统计的时候
    sum的算法为1+1+1+1+1+1=6，sum是未知的，需要额外的CPU每一步都需要计算
    count是1+2+3=6，就是说count的计算公式是定的，发现一行，加1，那么就是1，下一次是从2开始加
    显然sum的效率不如count。：SUM 操作会遍历整个表，遇到一条记录，就会执行一次加 N 的操作，最终返回累加和，即行记录数的 N 倍。

  • COUNT(n) COUNT(*)

    官方标准统计行数就是用count (*)，不建议写其他

    目前基于磁盘的数据库或者搜索引擎（比如Lucene）的性能瓶颈主要都是在IO阶段，相比于CPU和RAM，IO操作实在太慢了，所以这类系统的优化方向也都都是类似的——尽一切可能减少IO的次数（所以很多用ES的程序在性能优化到极限的时候选择直接上SSD）。这里统计行数的操作，查询优化器的优化方向就是选择能够让IO次数最少的索引，也就是基于占用空间最小的字段所建的索引（每次IO读取的数据量是固定的，索引占用的空间越小所需的IO次数也就越少）。而Innodb的主键索引是聚簇索引（包含了KEY，除了KEY之外的其他字段值，事务ID和MVCC回滚指针）所以主键索引一定会比二级索引（包含KEY和对应的主键ID）大，也就是说在有二级索引的情况下，一般COUNT()都不会通过主键索引来统计行数，在有多个二级索引的情况下选择占用空间最小的。

    如果说有张Innodb的表只有主键索引，而且记录还比较大（比如30K），则统计行的操作会非常慢，因为IO次数会很多（这里就不做实验截图了，有兴趣可以自己试一下）。一个优化方案就是预先建一个小字段并建二级索引专门用来统计行数，极端情况下这种优化速度提高上千倍也是正常的。

    结论就是对于COUNT(1)和COUNT()执行优化器的优化是完全一样的，并没有COUNT(1)会比COUNT()快这个说法。

  • SELECT、FROM、WHERE、HAVING、GROUP BY、ORDER BY 这些子句的正确书写顺序应该是怎样的

    1. FROM
    2. WHERE （先过滤单表／视图／结果集，再JOIN）
    3. GROUP BY
    4. HAVING （WHERE过滤的是行，HAVING过滤的是组，所以在GROUP之后）
    5. ORDER BY

4. 条件判断函数与系统同函数

MySQL 中的系统信息包含：数据库的版本号、当前登录用户、当前连接数、系统字符集等等。这些信息在特定的环境中非常有用，例如：查看连接数定位 MySQL 是否过载、查看字符集定位乱码原因等等。

4.1 常用条件判断函数

包括 IF CASE ;

单一条件对比 IF分为IFNULL NULLIF

多条件对比 CASE

4.1.1 IF条件判断函数

IF (expr, v1, v2) 函数。expr 是表达式的意思，它的含义是：如果 expr 为真（expr<>0 and expr<>NULL），则 IF 函数的返回值是 v1，否则，返回 v2。IF 函数的返回值是数字还是字符串，则视其所在语境而定。

1
2
3
4
5
6
7
SELECT name, IF(type='A', '研发', '非研发') AS type FROM worker WHERE id IN (1, 2); 
+------+-----------+ 
| name | type | 
+------+-----------+ 
| tom | 研发 | 
| jack | 非研发 | 
+------+-----------+

如果把 NULL 显示为 0 是不是更好一些呢 ？此时，IFNULL(v1, v2) 函数可以解决这个问题。

IFNULL (v1, v2) 表达的语义是：如果 v1 不为 NULL，则返回 v1，否则，返回 v2。同样，IFNULL 函数的返回值是 数字还是字符串，则视其所在语境而定。我们可以利用 IFNULL 将 salary 是 NULL 的列值变成 0。

1
2
3
4
5
6
7
8
SELECT name, IFNULL(salary, 0) AS salary FROM worker WHERE id IN (1, 2, 3); 
+------+--------+ 
| name | salary | 
+------+--------+ 
| tom | 1800 | 
| jack | 2100 | 
| pony | 0 | 
+------+--------+

NULLIF (v1, v2)与IFNULL名称类似。表达的语义是：如果 v1 等于 v2，那么返回值是 NULL，否则返回值为 v1。

1
2
3
4
5
6
7
8
SELECT id, name, NULLIF(salary, NULL) AS salary FROM worker WHERE id IN (1, 2, 3); 
+----+------+--------+ 
| id | name | salary | 
+----+------+--------+ 
| 1 | tom | 1800 | 
| 2 | jack | 2100 | 
| 3 | pony | NULL | 
+----+------+--------+

4.1.2 CASE条件判断函数

IF 条件判断函数所表达的语义是非 A 即 B，也就是单个条件的判断；CASE 函数则能够实现多条件的匹配。

1
2
3
4
5
6
CASE expr 
 WHEN v1 THEN r1 
 ...... 
 WHEN vx THEN rx 
 ELSE rn 
END

“CASE WHEN THEN”：适用于一个条件判断有多种可能值的情况下分别去执行不同的操作，或返回不同的结果值。

CASE 函数有两种写法：简单 CASE 函数写法、CASE 搜索函数写法，它们的区别在于：

  • 简单 CASE 函数写法只适合相等条件判断，不能用于大于、小于、不等于的判断
  • CASE 搜索函数写法适合复杂条件判断，可用于大于、小于、不等于的判断 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
# 简单case函数写法
mysql> SELECT 
-> name, 
-> (CASE type 
->  WHEN 'A' THEN '研发' 
->  WHEN 'B' THEN '测试' 
->  WHEN 'C' THEN '运维' 
->  ELSE '其他' 
-> END) AS type 
-> FROM 
-> worker 
-> WHERE 
-> id IN ( 
->  1, 2, 3 
-> ); 

# CASE搜索函数写法
mysql> SELECT 
-> name, 
-> (CASE 
->  WHEN type='A' THEN '研发' 
->  WHEN type='B' THEN '测试' 
->  WHEN type='C' THEN '运维' 
->  ELSE '其他' 
-> END) AS type 
-> FROM 
-> worker 
-> WHERE 
-> id IN ( 
->  1, 2, 3 
-> ); 
+------+--------+ 
| name | type | 
+------+--------+ 
| tom | 研发 | 
| jack | 测试 | 
| pony | 运维 | 
+------+--------+

涉及数值范围判断等等类似的例子，则只能使用 CASE 搜索函数的写法。 

1
2
3
4
5
6
7
...
->(CASE 
-> WHEN salary >= 2000 THEN '高收入' 
-> WHEN salary <= 1500 THEN '低收入' 
-> ELSE '中等收入' 
->END) AS salary 
...

4.1.3 条件判断函数总结

  • CASE 是 SQL 标准定义的，而 IF 是数据库系统的扩展

  • 在高级语言中，CASE 可以使用 IF 来代替，但是 SQL 中却不行

  • 在 SQL 的存储过程和触发器中，用 IF 替代 CASE 的代价是非常高的，难以应用

CASE 语句可以让 SQL 变得简单高效，提高执行效率，且通常不会引起性能问题，所以，通常应该作为首选。

4.2 常用的系统函数

4.2.1 MySQL自身的基本信息

4.2.2 当前用户信息

4.2.3 库和表相关信息

4.3 Q&A

  • MySQL 的默认字符排列方式是大小写不敏感的，如果想要大小写敏感，怎么做 ？

  • mysql大小写敏感配置与两个参数相关 —— lower_case_file_system 和 lower_case_table_names

    lower_case_file_system：表示当前系统文件是否大小写敏感（ON为不敏感，OFF为敏感），只读参数，无法修改。
    lower_case_table_names：表示表名是否大小写敏感，可以修改。
    lower_case_table_names = 0时，mysql会根据表名直接操作，大小写敏感。
    lower_case_table_names = 1时，mysql会先把表名转为小写，再执行操作。

5. 用户与权限

6. 数据备份与恢复

7. 事务隔离级别

参考 ：彻底搞懂 MySQL 事务的隔离级别-阿里云开发者社区 (aliyun.com)

7.1 事务是什么

事务是作为单个逻辑工作单元执行的一系列操作，它所表达的语义是：要么全部执行，要么全部不执行。

银行取款例子：取款事务=操作1+操作2

  • 操作1：将 A 的余额减少 100 元
  • 操作2：A 获得 100 元取款

这两个操作就是一次事务，因为这两个操作只能全部成功或全部失败，任何一个部分成功或失败，将会是非常严重的系统漏洞。事务的目标是保证数据库的完整性，避免各种原因引起的数据库内容不一致的问题。所以，事务可以保证数据安全，事务控制实际上就是在控制数据的安全访问

7.2 事务的四个属性 ACID

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）和持久性 （Durability）

  • 原子性：原子本身是化学中的一个名词，它指的是构成化学元素的最小粒子，即不能再更细的分割了。事务操作必须是原子的，对于一个事务中的所有操作，要么全部执行（COMMIT），要么全部不执行（ROLLBACK）
  • 一致性：一致性指的是数据的完整性，即执行事务的前后，数据整体应该是一致的。事务必须能够让数据库从一个一致性状态变换到另一个一致性状态。对于取款的案例来说，A 的数据总值就是一致的
  • 隔离性：它指的是一个事务的执行不能被其他事务所干扰，这里又涉及到并发的概念。一个事务内部的操作对其他并发的事务是隔离的，简单的说，每个事务都认为是自己独占数据库
  • 持久性：这个属性简单易懂，它是说一个事务一旦提交（COMMIT），它对数据库中数据的改变就是永久性的。任何操作甚至是系统故障都不应该对其产生影响

事务有着严格的判定标准，想要同时实现它们又要求有很高的性能，可谓是难上加难。所以，在各大数据库厂商的实现中，真正能够满足这四个特性的事务寥寥无几。例如：

InnoDB 存储引擎默认的事务隔离级别是可重复读，它不能满足隔离性要求；

而 MySQL 的 NDB Cluster 事务则不满足持久性和隔离性。

所以，与其说 ACID是事务必须满足的条件，不如说它们是衡量事务的严谨性标准。

7.3 并发事务会引发的问题

并发事务的概念是多个事务并发运行，那么，如果在并发运行的过程中对相同的数据进行了修改，就可能会引起一些问题：

  • 脏读：事务 A 读取了事务 B 当前更新的数据，但是事务 B 出现了回滚或未提交修改，事务 A 读到的数据就被称为 “脏数据”。通常情况下，使用 “脏数据” 会造成系统数据不一致，出现错误
  • 从根上理解MySQL事务的隔离级别
  • 不可重复读：事务 A 在执行过程中多次读取同一数据，但是事务 B 在事务 A 的读取过程中对数据做了多次修改并提交，则会导致事务 A 多次读取的数据不一致，进而无法做出准确性判断
  • 从根上理解 MySQL 事务的隔离级别
  • 幻读：事务 A 在执行过程中读取了一些数据，但是事务 B 随即插入了一些数据，那么，事务 A 重新读取时，发现多了一些原本不存在的数据，就像是幻觉一样，称之为幻读
  • 从根上理解 MySQL 事务的隔离级别
  • 不可重复读指的是对原来存在的数据做修改；而幻读指的是新增或者删除数据。

7.4 四种隔离级别

由低到高依次为：

READ-UNCOMMITTED（未提交读）、READ-COMMITTED（提交读）、REPEATABLE-READ（可重复读）、SERIALIZABLE（串行化）。

它们可以逐个解决脏读、不可重复读、幻读这几类问题。

  • READ-UNCOMMITTED：它是最低的隔离级别，正如它的名称一样，它允许一个事务读取其他事务未提交的 数据。这个隔离级别很少在工业环境中应用，因为它的性能并不会比其他高级别的性能好很多

    彻底搞懂 MySQL 事务的隔离级别

  • READ-COMMITTED：它可以保证一个事务修改的数据提交之后才能被其他的事务读取。这个隔离级别是大多 数数据库系统的默认隔离级别，但并不是 MySQL 默认的

  • 彻底搞懂 MySQL 事务的隔离级别

  • REPEATABLE-READ：它的核心在于 “可重复”，即在一个事务内，对同一字段的多次读取结果都是相同的， 也是 MySQL 的默认事务隔离级别

  • 彻底搞懂 MySQL 事务的隔离级别

  • SERIALIZABLE：它是最高的隔离级别，花费的代价也是最高的，事务的处理是顺序执行的。在这个级别上， 可能会导致大量的锁超时现象和锁竞争。同样，在工业级环境中，很少被使用。通过强制事务排序，并按顺序执行，使各个事务之间不可能会产生冲突，从而才能够解决脏读、不可重复读、幻读所有的问题。

隔离级别越低，事务请求的锁也就越少，所以，可能出现的问题也就越多。

隔离级别 脏读 不可重复读 幻读
READ-UNCOMMITTED 未提交读 NO NO NO
READ-COMMITTED 提交读 YES NO NO
REPEATABLE-READ 可重复读 YES YES NO
SERIALIZABLE 串行化 YES YES YES

具体选择哪一种隔离级别应该是多个维度的考虑，例如：事务请求锁的多少（性能问题）、能够解决什么问题、业务特点等等。一般情况下，使用 InnoDB 存储引擎，选择 READ-COMMITTED。

7.5 不同事务隔离级别对事务的影响

7.6 MySQL默认隔离级别（可重复读）实现原理

每条记录在更新的时候都会同时记录一条回滚操作（回滚操作日志undo log）。同一条记录在系统中可以存在多个版本，这就是数据库的多版本并发控制（MVCC）。即通过回滚（rollback操作），可以回到前一个状态的值。

假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

彻底搞懂 MySQL 事务的隔离级别

当前值是 4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的 read-view。如图中看到的，在视图 A、B、C 里面，这一个记录的值分别是 1、2、4，同一条记录在系统中可以存在多个版本，就是数据库的多版本并发控制（MVCC）。对于 read-view A，要得到 1，就必须将当前值依次执行图中所有的回滚操作得到。即使现在有另外一个事务正在将 4 改成 5，这个事务跟 read-view A、B、C 对应的事务是不会冲突的。

回滚操作日志（undo log）什么时候删除？MySQL会判断当没有事务需要用到这些回滚日志的时候，回滚日志会被删除。

什么时候不需要了？当系统里么有比这个回滚日志更早的read-view的时候。

7.7 Q&A

  • 修改系统级的隔离级别（SET global tx_isolation），验证对所有的会话都是生效的 ？

    set tx_isolation=’read-committed’

  • 挑选一个事务隔离级别，例如：READ-COMMITTED，演示并验证它可能会导致的问题 ？

  • 当会话处于 REPEATABLE-READ 隔离级别时，读取到的数据就一定是一样的吗 ？

8. 通过锁解决并发数据问题

数据库的锁机制是并发控制的重要内容，是对程序控制数据一致性的重要补充

8.1 行级锁、表级锁

从应用的角度来看，分为悲观锁与乐观锁的概念

从数据库（InnoDB 存储引擎）的角度看，数据库锁可以分为行级锁和表级锁（通常称为行锁和表锁）。

8.1.1 什么是行级锁

InnoDB 存储引擎支持行级锁和表级锁，默认情况下，使用行级锁。行级锁是 MySQL 中锁定粒度最细的一种锁，它锁住的是行记录。行级锁可以大大减少数据库操作的冲突，但是加锁的开销也是非常大的。它具有如下特点：

  • 由于开销比较大，加锁会很慢
  • 锁定粒度小，所以锁冲突的概率低，并发度高
  • 可能会出现死锁

8.1.2 什么是表级锁

  • 由于开销比较小，加锁会很快
  • 锁定粒度大，所以锁冲突的概率高，并发度低
  • 不会出现死锁

8.1.3 MySQL内部如何选择行级锁、表级锁？什么场景下会锁住行、什么场景下又会锁住表

InnoDB 行级锁是通过给索引上的索引项加锁来实现的，所以，只有通过索引条件检索的数据，InnoDB 才会使用行级锁。否则，使用表级锁。

即 使用索引==行级锁

  • 不通过索引条件查询时，InnoDB 一定会使用表锁，而不是行锁（因为没有索引只能全表扫描）
  • 查询时，不论是使用主键索引、唯一索引或者普通的索引，InnoDB 都会使用行锁来对数据加锁 。特殊情况下不成立
    • 对于数据量很少的表，MySQL 会认为全表扫描更快，此时，即使使用索引字段查询，InnoDB 也会使用表锁，而不是行锁。
    • 因此如果想确定当前使用 的是哪一种锁，检查下 SQL 的执行计划（EXPLAIN），确认是否在查询时使用了索引。

8.2 悲观锁、乐观锁

参考：Laravel中MySQL的乐观锁与悲观锁 技术指南 (chinacion.cn)

从应用的角度来说，乐观并发控制与悲观并发控制是并发场景下保证数据一致性的主要方法

8.2.1 什么是悲观锁

当我们要对数据库中的一条数据进行修改的时候，为了避免同时被其他人修改，最好的办法就是直接对该数据进行加锁以防止并发。这种借助数据库锁机制在修改数据之前锁定，再修改的方式被称为悲观并发控制 （PCC）

它会以悲观的态度去对待并发的数据控制，认为共享数据被并发修改的可能性较高，在修改之前先去加锁。在实现效率上，处理加锁的过程会让数据库产生额外的开销，降低并发度，同时，还可能会有死锁的可能

悲观锁的实现，依赖于数据库提供的锁机制，即行级锁、表级锁

  1. 对数据操作之前，尝试获取锁
  2. 获取锁成功，对数据进行修改、提交事务，最后释放锁
  3. 获取锁失败，则锁正在被占用，等待或抛出异常

悲观锁示意图

8.2.2 什么是乐观锁

乐观锁是相对悲观锁而言的，乐观锁假设数据一般情况下不会造成冲突，所以在数据进行提交更新的时候，才会正式对数据的冲突与否进行检测。

乐观锁示意图

8.3 悲观锁、乐观锁 实现原理和使用方法

8.3.1 使用悲观锁

在 MySQL 中使用悲观锁，必须关闭 MySQL 的自动提交（MySQL 默认使用自动提交模式，即执行 INSERT、UPDATE、DELETE 时，结果自动生效）

1
2
# my关闭自动提交 
mysql> SET autocommit = off;

MySQL 提供的悲观锁实现方式是：SELECT … FOR UPDATE

1
2
3
4
5
6
7
8
9
10
11
12
-- “会话 A” 通过悲观锁语法锁住 id 为 1 的记录 
mysql> SELECT * FROMworker WHERE id = 1 FOR UPDATE; 
+----+------+------+--------+ 
| id | type | name | salary | 
+----+------+------+--------+ 
| 1 | A | tom | 1800 | 
+----+------+------+--------+ 

-- “会话 B” 也通过悲观锁语法锁住 id 为 1 的记录 
-- 由于锁被 “会话 A” 占据，长时间获取不到锁，报了锁超时错误 
mysql> SELECT * FROMworker WHERE id = 1 FOR UPDATE; 
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

“会话 A” 更新被锁定记录的 salary 字段，并提交事务（在这个过程中，同时让 “会话 B” 尝试去获取锁），如下所示

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- “会话 A” 更新记录 
mysql> UPDATE worker SET salary = 2000 WHERE id = 1; 
Query OK, 1 row affected (0.01 sec) 
Rows matched: 1 Changed: 1 Warnings: 0 

-- “会话 A” 提交事务 
mysql> commit; 
Query OK, 0 rows affected (0.02 sec) 

-- “会话 B” 在 “会话 A” 提交事务之后，获取锁成功（观察 SQL 语句执行耗时） 
mysql> SELECT * FROMworker WHERE id = 1 FOR UPDATE; 
+----+------+------+--------+ 
| id | type | name | salary | 
+----+------+------+--------+ 
| 1 | A | tom | 2000 | 
+----+------+------+--------+ 
1 row in set (25.13 sec)

8.3.2 使用乐观锁

乐观锁其实是对 CAS（compare-and-swap）的实现：在做修改时，检查当前的环境是否与预定义的一致，如果一致则可以提交；否则，重试或抛异常

如何判断当前环境一致：

  • 简单方法：对修改的值进行前后判断
    • 这里存在一个隐藏很深的问题：ABA。考虑这样一种情况：“线程 A” 在更新 salary 之前，“线程 B” 先将 salary 更新为2500，再将 salary 更新为 2000。此时，“线程 A” 则会认为 salary 没有发 生变化，而实际的情况是，“线程 B” 对此更新了两次。
  • 进阶办法：加入一个不会重复修改的值数据来作为版本号，即 version参 数,用时间戳类型，version 只能增加，不能减少。乐观锁在每次执行数据修改时，都需要去比对 version，如果一致，则更新数据 的同时，也要更新 version。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 给 worker 表添加 version 列 
mysql> ALTER TABLE `worker` ADD COLUMN `version` BIGINT(20) NOT NULL DEFAULT '0' COMMENT '乐观锁版本号'; 

-- 读取数据，记录 version 的值 
mysql> SELECT * FROMworker WHERE id = 1; 
+----+------+------+--------+---------+ 
| id | type | name | salary | version | 
+----+------+------+--------+---------+ 
| 1 | A | tom | 1800 | 0 | 
+----+------+------+--------+---------+ 

-- 比对 version 是否符合预期，更新数据和 version 
mysql> UPDATE worker SET salary = 2000, version = version + 1 WHERE id = 1 AND version = 0; 

-- 再次读取数据，校验是否符合预期 
mysql> SELECT * FROMworker WHERE id = 1; 
+----+------+------+--------+---------+ 
| id | type | name | salary | version | 
+----+------+------+--------+---------+ 
| 1 | A | tom | 2000 | 1 | 
+----+------+------+--------+---------+

乐观锁的思想比较简单，而且不会使用到真正的锁，实现效率很高。但是，乐观锁也并不是没有缺点的：当遇到高并发时，只可能会有一个线程修改成功，会造成大量的线程失败或重试。最后，补充一点，乐观锁同样可以使用时间戳来实现，一样的道理。

8.4 悲观锁、乐观锁 适用场景

悲观锁是依赖于数据库（存储引擎）的，它的优缺点总结如下：

  • 优点：利用锁机制保证了数据的顺序执行，不需要自己控制，加锁、释放完全由数据库代劳
  • 缺点：一旦一个事务获取了锁，其他的事务必须等待，势必会影响系统的吞吐量
  • 适用场景：写入操作比较频繁的场景，如果有大量的读取操作，每次读取都需要加锁，会增加锁开销，降低系统的吞吐量

乐观锁不是真正的加锁，对其他事务友好，它的优缺点总结如下：

  • 优点：由于不需要加锁，其他的事务可以同时操作数据，相比于悲观锁，系统吞吐量会提高
  • 缺点：需要自行控制，如果并发度较高，失败重试的情况会成为系统瓶颈
  • 适用场景： 读取操作比较频繁的场景，如果有大量的写入操作，冲突的可能性会剧增，降低系统的吞吐量

8.5 Q&A

  • 你能给出一个行级锁升级为表级锁的案例吗（使用 worker 表或者你定义的表都可以）？
  • 你能用 timestamp（时间戳）的方式实现乐观锁吗 ？
  • 除了我这里的讲解，你是怎样看待 MySQL 中的锁机制呢（可以从锁粒度、锁应用的角度去思考）

9. 索引定义及其优化

9.1 何为索引

索引是存储引擎用于快速找到记录的一种数据结构

索引是一种数据结构，而数据结构是用来表示数据的特定规则。

如果我想要查询 salary = 2000 的员工有哪些，最简单的就是遍历所有的数据做比较，这样的时间复杂度是 O(n) 量 级。但是如果 MySQL 维护了一个 salary 的有序数组（MySQL 中使用的索引并不是有序数组)：[1200, 1500, 1800, 1800, 1900, 2000, 2100, 3600]，就可 以将查询过程优化为二分查找，此时的时间复杂度将降为 O(logn)。而这个有序数组也就对应到索引的概念了

优点：

  • 减少扫描的数据量，加速查询
  • 减少或完全消除数据库的排序操作（ORDER BY），因为索引是有序的
  • 将服务器的随机 IO 变为顺序 IO，例如，想要查询 salary 处于 1500 ~ 2100 的员工，就可以按照索引顺序查询

缺点：

  • 索引会占据额外的存储空间（毕竟它是数据结构），包括磁盘和内存
  • 由于对数据需要排序，自然会影响到数据更新（插入、更新、删除）的速度

9.2 实现原理和分类

9.2.1 实现原理

对于 MySQL 来说，服务器层并不会实现索引，而是交给了存储引擎。所以，不同的存储引擎自然也就会有各自不同的实现。对于 InnoDB 而言，它的内部实现使用的是 B+ 树

M 阶 B 树

B+ 树是对 B 树的改进得到的，而 B 树则是一棵多路平衡查找树（是平衡二叉树的变体，稍有不同的是 B 树属于多叉树，即查找路径不止两个）。所以，想要搞清楚 B+ 树，就一定要搞清楚 B 树。着重理解 M 的概念，M 阶代表一个节点最多有多少个查找路径

  • 根节点至少有两个子节点
  • 每个节点包含 k - 1 个元素和 k 个子节点，其中 m/2 <= k <= m（元素是存储的数据）
  • 每个叶子节点都包含 k - 1 个元素，且位于同一层，其中 m/2 <= k <= m
  • 每个节点中的元素从小到大排列，类似于一个有序数组

M阶B+ 树

添加了两项约束（完全包含 B 树的约束）：

  • 除叶子节点之外的其他节点都不保存数据，所以，数据在同一层
  • 叶子节点之间按照排列顺序链接在一起，形成了一个有序链表

img

为什么 InnoDB 会选用 B+ 树作为索引数据结构呢

  • 中间节点不保存真实数据，就可以去存储更多的索引数据，以降低整体树的层级，减少磁盘 IO 的次数，提高 查询性能
  • 所有的数据都保存在叶子节点，查询的次数也就是相同的，查询稳定性很高
  • 叶子节点之间顺序链接在一起，范围查询的效率极高

9.2.2 索引分类

  • 普通索引：针对于单个列创建的索引，之所以说它普通是因为它对列值没有什么限制，允许被索引的列包含重复的值
  • 唯一索引：正如它的关键字一样，它要求列值是唯一的，这个索引保证了数据记录的唯一性
  • 主键索引：它是一种特殊的唯一索引，在一张表中只能定义一个（但不是必须）主键索引
  • 联合索引：也被称为复合索引，它是将多个列值绑定在一起作为索引

索引的分类是存在交叉的。唯一索引和主键索引也可以针对多个列做索引，即它们可以是联合索引。

9.3 创建高性能索引

参考：[高性能索引策略——《高性能MySQL》第五章 | ]

9.3.1 聚簇索引

9.3.2 联合索引

9.3.3 前缀索引

9.3.4 覆盖索引

9.4 查看索引元数据

9.5 Q&A

  • 根据我对 B 树的描述，再加上你的理解，你知道图中的 B 树是几阶的吗 ？
  • 你能举一个联合索引建立 B+ 树的例子吗 ？
  • 根据你的理解，你能说说哪些列（不仅仅是数据类型需要是字符型，还要关注列值）适合前缀索引吗 ？
  • 你平时是怎样建立、使用索引的，为什么要这样做呢 ？

本文标题:MySQL基础1

文章作者:松子

发布时间:2019年06月11日 - 10:06

最后更新:2022年03月27日 - 20:03

博文链接:https://songzi.info/post/7de48ef/

许可协议: 署名-非商业性使用-禁止演绎 4.0 国际 转载请保留原文链接及作者。

0%