mysql系列4—mysql数据库设计和查询优化

背景

数据库作为持久层需要从磁盘读写数据，是整个系统的短板，其性能会直接体现在产品性能上。因此数据库的设计和优化再怎么强调也不为过。本文将从数据库设计和查询优化两个角度思考如何提高数据库性能，内容包括数据库范式与表设计、基本字段类型介绍和使用原则、索引设计原则、explain和optimze_trace命令使用介绍、索引失效场景等。本文旨在为读者提供数据库设计指导以及帮助读者提高数据库设计能力。

1.数据库范式

数据库设计依赖于软件设计，而软件设计依赖于业务需求; 不同业务场景下，数据库需要因地制宜。总体而言，数据库设计需要从数据的准确性、冗余度、使用方便角度出发，即设计业务合理、数据完整、节省空间且好用的数据库表。存在一种脱离业务的数据库设计指导原则，称为数据库范式；遵循数据库范式可以有效地减少数据的冗余度，提高查询效率, 部分场景需要迎合业务进行反范式设计。数据库范式由低到高包括1NF、 2NF、 3NF、 BCNF、 4NF、 5NF，其中高阶范式符低阶范式要求；一般而言只需遵循前3个范式即可，因此本文仅对前三个范式进行说明。

1.1 第一范式

第一范式确保表中每个字段必须具备原子性，即每个字段的值是不可拆分的最小数据单元。字段的原子性具备业务主观性，需要从业务的角度考虑，不需要拆分的字段可以认为是最小数据单元。
以下结合案例进行介绍:
假设用户表信息如下:

CREATE TABLE `t_student` (`id` INT(10) NOT NULL COMMENT '学号，唯一ID',`name` VARCHAR(50) NOT NULL COMMENT '姓名',`address` VARCHAR(128) NULL DEFAULT NULL COMMENT '地址',`subject` VARCHAR(32) NOT NULL COMMENT '科目',`score` INT(10) NULL DEFAULT NULL COMMENT '得分',`rank` CHAR(1) NULL DEFAULT NULL COMMENT '等级：A,B,C,D',`phone` VARCHAR(1024) NULL DEFAULT NULL COMMENT '手机号'
)
ENGINE=InnoDB
;

数据如下所示:

学校需要对学生按国际进行区分，如国外同学需要强制学一年汉语；此时地址因不具备原子性而需要进行拆分，拆分为: 国籍+具体地址。手机号是一个列表，也需要进行拆分；拆分如下所示:

CREATE TABLE `t_phone` (`id` INT(10) NULL DEFAULT NULL COMMENT '学号',`phone` CHAR(11) NULL DEFAULT NULL COMMENT '手机号',
)
ENGINE=InnoDB
;CREATE TABLE `t_student` (`id` INT(10) NOT NULL AUTO_INCREMENT COMMENT '学号，唯一ID',`name` VARCHAR(50) NOT NULL COMMENT '姓名',`country` VARCHAR(50) NULL DEFAULT NULL COMMENT '国籍',`address` VARCHAR(1024) NULL DEFAULT NULL COMMENT '地址',`subject` VARCHAR(32) NULL DEFAULT NULL COMMENT '科目',`score` INT(10) NULL DEFAULT NULL COMMENT '得分',`rank` CHAR(1) NULL DEFAULT NULL COMMENT '等级：A,B,C,D',PRIMARY KEY (`id`) USING BTREE
)
ENGINE=InnoDB
;

数据如下:

拆分过程对业务使用的影响：
[1] 查询用户信息，由单表t_student查询变成了t_student和t_phone连表查询;
[2] 查询外籍学生时，不需要根据address的前缀进行匹配，可以根据country进行完全匹配;
[3] 新增或者删除手机号时，仅对t_phone进行增删即可，省去了对t_student表phone字段的字符串分割和拼接过程。
另外，address字段从内容上可以继续按照市、区等行政区划进行拆分；由于学校系统没有对应的业务，即必要进行细化，此时address可以认为是最小数据单元。

1.2 第二范式

在第一范式的基础上，要求数据库表里的每条记录都是可唯一标识的；且所有的非主键字段必须完全依赖于主键，而不能只依赖主键的部分。
t_student表中，id与subject字段组成主键，即表示记录的唯一性；score和rank依赖于id和subject，而name, country, address仅依赖于id，因此需要对name, country, address三个字段进行拆表:

CREATE TABLE `t_student` (`id` INT(10) NOT NULL COMMENT '学号，唯一ID',`name` VARCHAR(50) NOT NULL COMMENT '姓名',`country` VARCHAR(32) NULL DEFAULT NULL,`address` VARCHAR(128) NULL DEFAULT NULL COMMENT '地址'
)
ENGINE=InnoDB
;CREATE TABLE `t_student_score` (`id` INT(10) NOT NULL COMMENT '学号，唯一ID',`subject` VARCHAR(32) NOT NULL COMMENT '科目',`score` INT(10) NULL DEFAULT NULL COMMENT '得分',`rank` CHAR(1) NULL DEFAULT NULL COMMENT '等级：A,B,C,D'
)
ENGINE=InnoDB
;

对应数据如下:

拆分后，学生的名字、国籍、住址等信息与课程解耦，使得用户信息只需要存一份，修改时仅需要修改一行记录。

1.3 第三范式

第三范式在第二范式的基础上，要求数据表里的每个非主键字段和主键字段直接相关，即非主键字段之间不能有依赖关系。
学校规定: score>85分，为A；60~85之间为B，小于60为C；此时，score和rank之间有依赖关系，rank依赖于score，按照第三范式，需要对其进行拆分：将id,subject和score组成一个表，而rank和score的依赖关系组成另一张表。
此时，由于rank是基于score计算出的，可使用mysql的自定义函数计算，也可通过业务查库后计算，建议使用后者(考虑更强的扩展性)。
修改后的数据对应如下:

拆分后，rank由业务计算代替，减少不必要字段；且修改得分等级规则时，仅需修改服务层，数据库层不需要变动，有利于版本升级的稳定性。

1.4 反范式

遵循范式会减少数据库的冗余程度，对更改操作比较友好；但可能引入连表查询的问题。因此，数据库设计时要因地制宜，不能完全按照范式来，业务需要应当优先于范式要求。
上述案例中，如经常需要查询学生的成绩表，涉及学号、姓名、成绩字段，因此需要关联t_stuent表和t_stuent_score表:

select st.id, st.name, sc.subject, sc.score from t_student st left join t_student_score sc on st.id=sc.id;

由于学生的姓名不是经常更改的字段，可在t_stuent_score表中添加一个冗余字段(学生名称)，从而将连表查询优化为了单表查询:

select id, name, subject,score from t_student_score;

查询如下所示:

添加名字冗余字段后，不满足数据库范式，却很有效地提高了查询效率。

2.字段基本类型

表的设计需要考虑每个字段的类型以及字段长度等问题，本章对常用基本类型以及类型选择原则进行介绍。

2.1 整数和浮点类型

整数类型
整数包括tinyint、smallint、mediumint、int和bigint, 分别使用8、16、23、32、64位存储空间，存储范围对应-2^(n-1) 到2^(n-1)-1。其中，需要记住最小的tinyint占据8字节，范围为-128～127；常用的int占据32字节，范围为-21亿~21亿。
当确定不为负数时，可设置unsigned属性，储值范围将扩大一倍。

浮点类型
float和double用于存储浮点类型。float单精度浮点数占据4字节，精度大约7位有效数字；double双精度浮点数占据8字节，精度大约15位有效数字。
float和double属于不精确类型；如需精确计算,如金融领域，可使用decimal类型代替。DECIMAL类型的定义方式为DECIMAL(M, D)，其中, M表示总位数(包括小数点前后的数字)，范围是1到65; D表示小数点后的位数, 范围是0到30, 并且D不能大于M.

2.2 字符串类型

MySQL的字符串类型用于存储文本数据，常见的字符串类型包括CHAR和VARCHAR，以及用于存储大文本数据的TEXT类型。
char类型
CHAR用于存储固定长度的字符串, 长度在建表时指定，范围从0到255个字符；如果存储的字符串长度小于指定长度，MySQL会自动在字符串末尾填充空格；CHAR比较适合存储长度几乎不会变化的数据，如身份证号码、MD5值等固定长度数据。

varchar类型
VARCHAR用于存储可变长的字符串, 长度范围从0到65535个字符。与CHAR不同，VARCHAR不会填充空格，因此更适合存储长度不固定的字符串，如用户名、地址等。VARCHAR实际占用的空间是字符串实际长度加上1或2个字节来存储字符串的长度信息：长度小于255的字符串，使用1个字节长度信息，长度大于255的字符串使用2个字节。

text类型
TEXT用于存储较大的文本数据, 适合于存储如文章内容、评论、描述等较长的文本信息。TEXT类型可以存储的最大长度为2^16 - 1个字符，即65535个字符，TINYTEXT、MEDIUMTEXT、LONGTEXT分别是255个字符(2^8 - 1)、1千万(2^24 - 1)和40亿(2^32 - 1). 当TEXT长度小于或等于255个字符，会直接存储在行数据中; 超过255个字符时，数据将存储在溢出页中，并通过一个20字节的指针在行数据中引用。
即相对于char和varchar, TEXT类型数据可能需要更多的I/O操作。

字符集和排序规则
字符串可以使用不同的字符集和排序规则。常见的字符集包括latin1、utf8、utf8mb4:
[1] latin1每个字符占用1个字节，能够表示256个字符;
[2] utf8字符占用1至3个字节，能够表示大部分常用字符，但不支持某些特殊字符;
[3] utf8mb4字符占用1到4个字(是UTF-8的扩展)，能够存储所有Unicode字符，包括特殊字符和表情符号;一般推荐使用utf8或者utf8mb4.
MySQL中的字符串排序依赖于字符集的排序规则, 排序规则决定了字符串如何比较和排序。一般推荐使用utf8mb4_general_ci, 排序时区分大小写，字符串首先按照字符串的第一个字符的Unicode编码值进行比较，然后是第二个字符，依此类推。

2.3 二进制数据

BINARY、VARBINARY和BLOB类型都用于存储二进制数据。
BINARY存储固定长度的二进制字符串，数据时长度不足，MySQL会用零字节(0x00)填充至指定长度，超出长度的数据会被截断；
VARBINARY是可变长度的二进制字符串，与BINARY不同，VARBINARY不会进行填充也不会截断数据，而是根据实际数据长度存储，并且在存储时额外需要1或2个字节来存储长度信息(长度小于255时用1字节，否则用2字节, 与varchar相同)；
BLOB用于存储二进制大对象，如图片、音频、视频等文件。BLOB类型有四种变体,分别对应不同的最大存储大小：TINYBLOB(255字节)、BLOB(64KB)、MEDIUMBLOB(16MB)和LONGBLOB(4GB).

2.4 日期和时间类型

mysql中的日期类型有:year年(YYYY或者YY), time时间(HH:MM:SS), date日期(YYYY-MM-DD)以及日期时间datetime(YYYY-MM-DD HH:MM:SS)和代表UTC时间的timestamp(YYYY-MM-DD HH:MM:SS), 常用的时间类型有timestamp和datetime.
datetime占据8字节，可以保存的时间范围为1001年到9999年，精度为秒；将时间和日期封装为格式为YYYYMMDDHHMMSS的整数进行保存，因此无时区概念。timestamp占据4字节，保存的是UTC时间戳，即从"1970-1-1 00:00:00"以来的秒数; timestamp表示范围为1970-2038年。因此timestamp的显示依赖于时区。

2.5 字段选取原则

选择字段类型时应遵循以下准则：
(1) 优先选择简单类型：相较于整型，字符串类型在操作如排序时成本更高，因为它们涉及到更复杂的处理规则;
(2) 选用更小的数据类型：选择能够满足存储需求的最小数据类型，这样可以减少对磁盘、内存和CPU资源的占用;
(3) 尽量避免使用NULL：对于那些明确不会为空的字段，应设计为NOT NULL。当一个可为空的列被索引时，索引需要额外存储一个字节来标记该列是否为NULL，这会增加存储负担。
总之，在选择数据类型时，需综合考虑性能和存储效率。简单类型的选择有助于降低数据库操作的复杂度，尤其是在处理大量数据时。同时，选择适当的数据类型可以优化存储空间的使用，提高数据检索效率。

3.数据库索引设计

按照业务和数据库范式设计表的操作完成后，需要对表字段添加索引，本章节介绍如何设计索引。
索引的设计围绕着查询进行，而查询源于业务需求。所以，首先需要根据业务梳理出可能的查询逻辑，并提取出条件语句中常用的字段及组合、结果集字段及组合等，基于此建立索引。由于索引的维护需要消耗数据库性能，因此也需要根据具体业务在写库于读库之间进行权衡。

3.1 索引类型

索引按照类型有主键索引、唯一索引、普通索引；也可以对多个列组合成组合索引，组合索引可以设置为主键、唯一、普通索引。
主键索引：要求索引值必须唯一，且不能为NULL；主键索引确保了表中每一行数据的唯一性;
唯一索引：与主键索引类似，唯一索引也要求索引值唯一，区别是允许值为NULL;
普通索引：允许值的重复和NULL值，它主要用于提高查询性能，而不保证数据的唯一性;
组合索引：由两个或多个列组成的索引，组合索引可以针对列的不同顺序进行优化，以提高查询效率。

3.2 索引实现类型

mysql的索引有两种实现类型，B+树和哈希索引。B+树在前文已在[mysql系列3—mysql索引图解]中详细介绍，本文仅对哈希索引进行介绍。
哈希索引是Memory存储引擎的默认索引类型，InnoDB可通过自适应哈希索引支持用户创建哈希索引，其他的存储引擎不支持。哈希索引基于哈希表实现：对列字段添加哈希类型的索引后，对索引列计算出的哈希值作为索引键，索引值保存指向数据的行指针，即哈希索引由哈希值和行指针组成。由于哈希特性，哈希索引只支持精确匹配而不支持范围检索，但就精确匹配而言，哈希索引效率远高于B+树索引。
[一般推荐使用B+树索引]

3.3 索引设计原则

[1] 应当为每个表建立一个主键，用于唯一标识记录；
[2] 对于业务上具有唯一性约束的，需要添加唯一索引；
[3] 频繁出现在where条件中的列，建议添加索引，多个列可以组成联合索引，最频繁的列放在最左侧;
[4] 频繁出现在order by和group by关键字中的列，多个列则按照顺序组合成联合索引;
[5] 查询类型考虑使用覆盖索引，具备业务亲密性的字段建议建立联合索引。

3.4 不建议使用索引的场景

合理使用索引可提高查询效率，然而索引也会占据磁盘和内存空间，且修改数据时需要额外的维护操作。不恰当的使用反而会降低系统效率，以下场景不建议使用索引。
[1] 数据量小
表中的数据量比较小时，一个数据页基本就可以完全包含，此时全表读取的速度较快。走索引查询后可能还需要回表查询，效率可能比不上全表查询。MYSQL在数据量较少的场景，优化器会将索引查询优化为全表查询，后续通过案例进行介绍。
[2] 大量重复数据
对于性别、血型等大量重复的数据，建立索引没有实际意义。
[3] 频繁更新
数据更新时，索引也会被更新，引入了额外的IO开销。如果数据列被频繁地更新，索引会严重影响修改性能，此时不推荐使用索引。
[4] where和group by和order by没有使用的字段
索引起作用的场景是作为where条件、group by和order by的字段列表中；如果上述场景没有使用，则不应该建立索引。
[5] 大数据类型 text和blob
对于大数据如text和blob字段，索引会占据较大的内存和磁盘。可能一个记录就会占据一个数据页，检索索引会在大量页之间跨越搜索，效率较低。
[6] 对无序的字段
索引是排序存储的，检索时也根据字段的比较规则进行。如果字段无序，则索引会失效。因此对于uuid, md5和hash类型的字段，不应该建立索引。
[7] 避免索引重复
索引占据资源，因此尽量避免重复建立索引。创建idx_keys(key1,key2,key3)索引等价于创建了idx_keys(key1)和idx_keys(key1,key2)和idx_keys(key1,key2,key3)这三个索引，因此没必要再对key1进行索引。

4.explain命令

explain是 mysql用于获取 SQL语句执行路径的命令，返回信息包括如何执行(索引查询、范围查询、全表查询等)以及执行效率等相关的细节。基于此可进行SQL语句的优化，提高数据库性能。
使用方式如下:

EXPLAIN [statement]

statement 是待要分析的SQL语句，可以是SELECT/INSERT/UPDATE/DELETE。
常用于分析SELECT语句的查询效率，如下所示：

以下结合案例对explain返回字段进行介绍。
案例使用定义完全相同的t_a、t_b、t_a1000、t_b1000表， 建表语句如下所示:

CREATE TABLE `t_a` (`id` INT(10) NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',`key1` INT(10) NULL DEFAULT NULL,`key2` INT(10) NULL DEFAULT NULL,`address` INT(10) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,UNIQUE INDEX `uniq_name` (`name`) USING BTREE,INDEX `idx_keys` (`key1`, `key2`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

id作为自增主键，name为非空唯一索引，key1和key2联合作为普通索引，address是普通字段。其中t_a表有3条记录，t_b表有1条记录，t_a1000和t_b1000有1000条记录:

-- t_a表记录为1->3
mysql> select * from t_a;
+----+------+------+------+---------+
| id | name | key1 | key2 | address |
+----+------+------+------+---------+
|  1 | 1    |    1 |    1 |       1 |
|  2 | 2    |    2 |    2 |       2 |
|  3 | 3    |    3 |    3 |       3 |
+----+------+------+------+---------+-- t_b表记录为1
mysql> select * from t_b;
+----+------+------+------+---------+
| id | name | key1 | key2 | address |
+----+------+------+------+---------+
|  1 | 1    |    1 |    1 |       1 |
+----+------+------+------+---------+-- t_a1000和t_b1000表记录为1->1000
mysql> select count(1) from t_a1000;
+----------+
| count(1) |
+----------+
|     1000 |
+----------+

(1) id
表示查询ID，每个查询(select操作)对应一个id；id越大越先执行，id相同从上往下执行。
简单的普通查询只有一个id:

连接查询的多条记录id相同:

id相同时，执行顺序为从上向下，即先查询t_a表再查询t_b, 符合预期: 上述语句先全表查询t_a，然后对于每条记录分别查询一次t_b.

union用于连接多个select，每个select对应一个id:

(2) select_type
select_type用于表示当前查询在整个查询语句中的角色，常见有以下几种取值:
[1] SIMPLE: 不包含uinon或子查询的查询都是SIMPLE查询
需要注意，有时候mysql的优化器会将子查询优化为连表查询，使得select_type都为SIMPLE：

[2] PRIMARY和UNION和SUBQUERY:
PRIMARY表示子查询SQL或者union查询SQL中的主查询; UNION和SUBQUERY与PRIMARY相对，uinon查询SQL中其他select都是UNION类型，SUBQUERY表示子查询SQL中的子查询语句。
在union查询SQL中，主查询是最左侧的select:

而其他两个通过union关键字合并的select都是UNION类型。

在子查询中，主查询是最外层查询:

内部的查询为SUBQUERY类型。

[3] UNION RESULT
union all用于合并各个结果集, 而union合并结果集后需要去除二者重复部分，存在操作结果集部分。这一去重步骤对应UNION RESULT:

基于内存的操作，无需查表。

[4] subquery和dependent subquery
subquery和dependent subquery都是子查询类型，二者区别是subquery不依赖于外层查询可以缓存，而dependent subquery基于外层查询，不可缓存。

注意，有些场景下，subquery会被转为dependent subquery，在第5章中作为案例进行介绍。

(3) table
结果集对应的表名，表使用别名是，table为别名；

(4) partitions
如果查询是基于分区表会显示查询将访问的分区，否则返回null;

(5) type
type表明按照什么策略进行查询，使用索引或者全表扫描等。type这是优化SQL语句时重点关注的字段, 常见有以下几种取值:

[1] system
表中只有一条记录，且存储引擎为MyIsam或Memory.

此时t_b表只有一条记录，InnoDB类型时查询类型为ALL，存储引擎修改为MyIsam后查询类型为system.

[2] const
通过主键或者唯一索引与常数进行等值匹配时，查询类型为const.

此时，id作为主键与常数1进行等值比较，因此查询类型为const。

[3] eq_ref
进行表连接查询时，如果被驱动表通过主键或者唯一索引进行等值匹配，则对应该被驱动表的查询类型为eq_ref.

此时，t_a1000为驱动表而t_b1000为被驱动表；由于通过id进行等值关联，而id为t_b1000表的主键, 因此查询类型为eq_ref.
这里对连表查询简单展开一下: 先对t_a1000进行全表查询得到记录，每条记录的id分别作用常量通过b.id进行等值查询，因此t_b1000表使用的索引是PRIMARY.

[4] ref和ref_or_null
普通索引与常量进行等值匹配时的查询类型为ref类型，当索引类可能为空时，查询类型为ref_or_null.

此时，通过key1和key2组合的普通索引进行查询，查询类型为ref; 由于查询所有字段，需要通过组合索引得到主键数据后回表查询，而不能通过覆盖索引查询。

[5] range
通过索引获取范围数据时，查询类型为range.

此时，通过主键id进行范围查询，类型为range。

[6] index
查询需要遍历索引树时，查询类型为index.

此时，通过key2查询key1，不满足最左侧匹配原则，索引idx_keys (key1, key2)对key2=1条件失效，查询类型无法设置为ref，需要进行遍历. 由于查询结果仅包含key1字段，可通过遍历idx_keys索引进行，即走覆盖索引进行查询。

[7] all
all表示全表扫描，效率最低。

address为普通字段，未设置索引，因此address=1条件需要进行全表扫描。

(6) possible_keys
查询可能使用到的索引集合；

(7) key
查询实际用到的索引，当全表扫描时为空；

(8) key_len
所选索引的长度，表示索引中使用的最长关键字段的长度。对于允许为空的字段，会多出一个字节用于记录数据是否为空，可变长字段会多出2字节用于表示长度。案例如下所示:

选用的utf8mb4字符集的最大字节数是4，name字段长度为50，额外使用2字节保存name实际长度，因此ken_len = (4*50)+2=202; 如果name字段可以为空，则额外再需要1个字节存储是否为空。

(9-12) 其他
ref表示与索引列比较的列或常量；rows表示预先估计的数值，为了找到所有匹配的行而需要检查的行数；filtered表示通过表的条件过滤的行占据的百分比；extra表示额外的执行信息，例如Using temporary、Using index等。

5.OptimizerTrace工具

mysql提供了一个查询优化器生成执行计划流程的工具，通过optimizer_trace变量控制是否开启追踪。可以全局设置也可对当前连接进行设置:
[1] 全局设置时，新连接的当前optimizer_trace变量从全局复制, 即影响后续连接;
[2] 当前设置，仅对当前连接生效;
查询和设置optimizer_trace变量:

-- 查询optimizer_trace变量
SHOW [GLOBAL] VARIABLES LIKE 'optimizer_trace';-- 设置optimizer_trace变量
SET [GLOBAL] optimizer_trace="enabled=on";
SET [GLOBAL] optimizer_trace="enabled=off";

SQL查询语句执行后，记录在OPTIMIZER_TRACE表中，可通过如下语句查询优化过程:

SELECT * FROM information_schema.OPTIMIZER_TRACE; 

以下通过OptimizerTrace工具对章节4中案例进行细致分析。
案例1：子查询优化为连表查询

explain select * from t_a where name in (select name from t_b);其中 select name from t_b为子查询，而外层的t_a查询为主查询，应当为: PRIMARY+SUBQUERY. explain显示结果为SIMPLE+SIMPLE.
查询information_schema.OPTIMIZER_TRACE表, 结果如下:

{"steps": [// 省略...{"transformations_to_nested_joins": {"transformations": ["semijoin"],"expanded_query": "/* select#1 */ select `t_a`.`id` AS `id`,`t_a`.`name` AS `name`,`t_a`.`key1` AS `key1`,`t_a`.`key2` AS `key2`,`t_a`.`address` AS `address` from `t_a` semi join (`t_b`) where ((`t_a`.`name` = `t_b`.`name`))"}}// 省略...]
}

优化器将上述SQL优化为了连表查询:

select t_a.id AS id,t_a.name AS name,t_a.key1 AS key1,t_a.key2 AS key2,t_a.address AS address from t_a left join t_b on t_a.name = t_b.name

案例2：subquery查询改为dependent subquery
表t_b和t_b1000的表结构完全相同，区别是t_b中有1条记录，而t_b1000有1000条记录。
当进行连表查询时，通过explain可以看出相同的SQL，得到不同的执行路径:

explain select * from t_b1000 where name in (select name from t_a) or name is null或者explain select * from t_b where name in (select name from t_a) or name is null中(select name from t_a)是脱离外层而存在的，应该为SUBQUERY而不是DEPENDENT SUBQUERY类型，查OPTIMIZER_TRACE表如下:

{"steps": [// 省略...{"expanded_query": "/* select#1 */ select `t_b`.`id` AS `id`,`t_b`.`name` AS `name`,`t_b`.`key1` AS `key1`,`t_b`.`key2` AS `key2`,`t_b`.`address` AS `address` from `t_b` where (<in_optimizer>(`t_b`.`name`,<exists>(/* select#2 */ select `t_a`.`name` from `t_a` where (<cache>(`t_b`.`name`) = `t_a`.`name`))) or (`t_b`.`name` is null))"}// 省略...]
}

优化器将上述SQL进行了优化:

select t_b.id AS id,t_b.name AS name,t_b.key1 AS key1,t_b.key2 AS key2,t_b.address AS address from t_b 
where exists( select t_a.name from t_a where (t_b.name = t_a.name)) or (t_b.name is null)

内部的子查询select t_a.name from t_a where (t_b.name = t_a.name)依赖于外曾的t_a表查询。
t_b仅行数较少，可以进行遍历，对于t_b的每一行满足t_b.name is null的加入结果集，不满足的提取name进行子查询select t_a.name from t_a where t_a.name = ‘已知的b.name’; 而t_b1000数据量较大，mysql不愿意这么做。

6.索引失效场景

本章节枚举常见的数据库失效场景，通过理解索引失败的案例，对于数据库设计以及SQL语句优化有借鉴意义。
对第五章的案例进行修改，给t_a、t_a1000、t_b、t_b1000添加一个int类型的key3字段，且为key3添加一个普通索引， 修改之后的建表语句如下所示：

CREATE TABLE `t_a` (`id` INT(10) NOT NULL AUTO_INCREMENT,`name` VARCHAR(50) NOT NULL COLLATE 'utf8mb4_general_ci',`key1` INT(10) NULL DEFAULT NULL,`key2` INT(10) NULL DEFAULT NULL,`key3` INT(10) NULL DEFAULT NULL,`address` INT(10) NULL DEFAULT NULL,PRIMARY KEY (`id`) USING BTREE,UNIQUE INDEX `uniq_name` (`name`) USING BTREE,INDEX `idx_keys` (`key1`, `key2`) USING BTREE,INDEX `idx_key3` (`key3`) USING BTREE
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=1
;

6.1 全表查询更快

当mysql优化器认为全表查询比使用索引更快时，不会走索引。

-- 相同的表结构和SQL语句，区别是t_a1000有1000条数据，而t_a只有3条数据；
-- mysql直接全表查询t_a的效率更高，因此不会走索引查询
explain SELECT * FROM t_a1000 ORDER BY KEY3 LIMIT 10;
explain SELECT * FROM t_a ORDER BY KEY3 LIMIT 10;

6.2 索引列计算或者函数

对索引进行运算或者函数计算时，mysql的搜索对象转为计算后确定的结果而非索引键，所以需要遍历全部记录并分别计算。

案例中对主键进行id-1以及log(id)导致索引失效，走ALL全表查询。

6.3 索引列之间对比

因为mysql每次查询只能走一个索引，而组合索引也只有最左侧前缀有效；因此对索引列之间的比较也会导致索引失效，需要全表搜索。

6.4 类型转换

当向字符串类型的索引字段传递整型时会导致索引失效:

[name为字符串类型]

注意: 向整型索引字段传递字符串索引不会失效，因为索引字段为整型时，会自动将字符串转换为整型:

[key3为整型]

6.5 不满足最左侧匹配原则

t_a1000表中的key1和key2形成组合索引，根据做左侧前缀匹配原则，相当于对key1和(key1,key2)加上了索引，而没有对key2形成索引。当对key2进行条件查询时，走全表查询:

6.6 select *

在进行查库时，不建议直接使用select *,一方面会导致索引失效，一方面会返回不需要的字段，影响查询和传输效率。select * 索引失效针对于覆盖索引场景, select * 中包含了所有字段，需要回表查询索引列外的字段。

6.7 like

字符串类型的索引字段根据字符串排序规则进行排列，按字母从左到右进行排序。模糊查询如果使用了左侧模糊匹配，则该排序规则失去了意义，需要进行全表扫描。

可以看到 %A和%A%失效，而A%可以通过索引进行范围搜索。

6.8 or

当SQL的select有多个条件时，如果有or语句添加了对非索引字段的条件，会导致整个索引失效。

id为主键，id=1时走主键const类型查询；address为普通字段，address=1条件会进行全表扫描；因此id=1 or address=1会进行全表扫描。

6.9 不等于

字符串类型的索引进行不等于(!=或者 <>)查询时，索引失效:

6.10 is null和is not null

是否为空的条件需要配合索引字段是否为空进行判断。如果字段不允许为空，对于is null条件，则Extra会提示"Impossible WHERE", 条件永远不会成立(无需查询，立刻给出结果)；对于 is not null条件则永远成立，mysql会忽略这个条件，即下面两个SQL查询等价:

-- 由于时全表查询，此时不会走索引
explain select * from t_a1000 where id not null;
explain select * from t_a1000;

因此，这里讨论的是对于可为空的字段，is null 和is not null的查询类型。由于非索引字段查询一定为ALL类型(全表扫描)，只需要关注索引字段即可。
结论是"is null会走索引，而is not null不会走索引", 案例如下:

6.11 not in

仅主键的not in走索引的range查询，其他索引字段的not in走ALL类型查询:

6.12 order by

order by场景比较复杂，拿捏不准时，建议对SQL语句使用explain命令测试一下

order by索引问题是指order by的不正确写法，导致原本可以走索引的查询却未走索引。mysql执行含order by的SQL语句时，通过select和where条件查询出结果集后，再根据order by指定的字段对结果集进行排序，如果排序字段有索引，可以利用索引加快排序；没有索引时，数据库可能会使用临时表来存储排序结果，这通常涉及到额外的I/O操作，效率较低，所以一般建议order by后的字段为索引列。
例如全表查询时，对主键进行order by比其他索引或普通字段效率更高:

注意到EXPLAIN SELECT * FROM t_a是全量查询，因此查询类型为ALL; 添加对主键id的排序后，查询类型变为index, 反而提高了查询效率，

6.13 not exists

explain SELECT * FROM t_a1000 AS a WHERE EXISTS (SELECT 1 FROM t_b1000 AS b WHERE b.name = a.name);语句对应两个select, 分别走了index和eq_ref查询，即走了子查询。而not exist对t_a1000表的查询变成了ALL全表查询:

从select_type的SIMPLE可以看出，两条子查询语句都被进行了优化，转为了连接查询。使用OptimizerTrace工具对第一条SQL语句进行跟踪:

{"steps": [//...{"transformations_to_nested_joins": {"transformations": ["semijoin"],"expanded_query": "/* select#1 */ select `a`.`id` AS `id`,`a`.`name` AS `name`,`a`.`key1` AS `key1`,`a`.`key2` AS `key2`,`a`.`key3` AS `key3`,`a`.`address` AS `address` from `t_a1000` `a` semi join (`t_b1000` `b`) where ((`a`.`name` = `b`.`name`))"}}//...]
}

对应优化后的SQL语句为:

select a.id AS id,a.name AS name,a.key1 AS key1,a.key2 AS key2,a.key3 AS key3,a.address AS address 
from t_a1000 a join t_b1000 b on a.name = b.name;

可以通过explain验证一下:

连表查询且通过索引name进行关联，所以被驱动表和驱动表的查询会走索引。
再看一下not exist:

先执行ID=2的查询，走name索引查询出t_b1000表中所有name字段并物化；然后遍历t_a1000表，对于每条记录，取出name字段与物化的子查询进行比较，不相等的加入结果集。

上述所列并不完整，重在理解；总之，对于拿捏不准的，可以通过explain命令确认一下。