mysql关于优化设计模式与数据类型

标签：语句 mysql 数据类型 actor 索引 MySQL 设计模式 id film

一、设计数据结构的字段数据类型基本原则是：

1、更小的通常更好。就是尽量使用可以正确存储数据的最小数据类型，主要更小的数据类型可以更快，占用磁盘空间，内存和CPU缓存，处理周期更加少。例如：能使用tinyint unsigned就别使用smallint.这个需要早期对数据表的作用与将来增长范围进行一个评估

2、简单就好。简单的数据类型减少CPU处理周期。例如整型比字符串型处理更加简单。字符串需要对字符集与校对规则进行比较。尽量使用mysql内建的类型，例如date、datetime、TIMESAMP和整型IP。

3、尽量避免Null

很多表都包含可为NULL （空值）的列，即使应用程序并不需要保存NULL也是如此, 这是因为可为NULL是列的默认属性。通常情况下最好指定列为NOT NULL,除非真的需要存储NULL值。

如果査询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。可为NULL的列会使用更多的存储空间，在MySQL里也需要特殊处理。当可为NULL的列被索引时，每个索引记录需要一个额外的字节，在MylSAM里甚至还可能导致固定大小的索引（例如只有一个整数列的索引）变成可变大小的索引。

通常把可为NULL的列改为NOT NULL带来的性能提升比较小，所以（调优时）没有必要首先在现有schema中査找并修改掉这种情况，除非确定这会导致问题。但是, 如果计划在列上建索引，就应该尽量避免设计成可为NULL的列。

当然也有例外，例如值得一提的是，InnoDB使用单独的位（bit）存储NULL值，所以对于稀疏数据(很多行为NULL，只有少数行为NO NULL)有很好的空间效率。但这一点不适用于MyISAMo

二、字段类型的选择

1、浮点型：float、double，decimal的选择。

1.1：占用空间float：4个字节，double：8个字节，对于DECIMAL列，可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中（每4个字节存9个数字）。例如，DECIMAL（18,9）小数点两边将各存储9个数字，一共使用9个字节：小数点前的数字用4个字节，小数点后的数字用4个字节, 小数点本身占1个字节。

decimal在存储方面，占用空间比float，double大。decimal在mysql内部计算还是转换为double来计算。decimal只是一种存储方式。

有多种方法可以指定浮点列所需要的精度，这会使得MySQL悄悄选择不同的数据类型, 或者在存储时对值进行取舍。这些精度定义是非标准的，所以我们建议只指定数据类型, 不指定精度。

decimal类型需要额外的空间和计算开销，所以应该尽量只在对小数进行精确计算时才使用DECIMAL——例如存储财务数据。但在数据量比较大的时候，可以考虑使用BIGINT代替decimal,将需要存储的货币单位根据小数的位数乘以相应的倍数即可。假设要存储财务数据精确到万分之一分，则可以把所有金额乘以一百万，然后将结果存储在BIGINT里, 这样可以同时避免浮点存储计算不精确和DECIMAL精确计算代价高的问题。

2.字符串类型

字符串就要涉及到字符集与排序规则（校检）方式。不同的字符集需要存储空间则不同。例如varchar（长度10）使用latinl，那么实际需要11个字节来存储。假如使用utf-8,这个就会更多字节来存储。

2.1 char和varchar。

固定长度和变长字符串

a、varchar由于可变长，导致后期的编辑产生碎片化问题。varchar在使用场合首先考虑少编辑的字段内容。例如用户名

b、char固定长度。一般用来存储类似MD5之类的数据，对于了解字段内容为最大长度情况下，可以考虑选择char来代替varchar。

c、char与varchar在存储的区别，char在存储时，会删除字符串后面的空格，varchar会保留字符串末尾的空格。当然在不同的引擎下，有不同的结果。以上针对innodb

2.2 binary和varbinary。

这两个跟char与varchar类似。二进制字符串跟常规字符串非常相似，但是二进制字符串存储的是字节码而不是字符。填充也不一样：MySQL填充BINARY采用的是\0 (零字节)而不是空格，在检索时也不会去掉填充。

当需要存储二进制数据，并且希望MySQL使用字节码而不是字符进行比较时，这些类型是非常有用的。二进制比较的优势并不仅仅体现在大小写敏感上。MySQL比较BINARY字符串时，每次按一个字节，并且根据该字节的数值进行比较。因此，二进制比较比字符比较简单很多，所以也就更快。

思考：使用VARCHAR(5)和VARCHAR(200)存储 ‘hello'的空间开销是一样的。那么使用更短的列有什么优势吗？

事实证明有很大的优势。更长的列会消耗更多的内存，因为MySQL通常会分配固定大小的内存块来保存内部值。尤其是使用内存临时表进行排序或操作时会特别糟糕。在利用磁盘临时表进行排序时也同样糟糕。

2.3 BLOB和TEXT类型

BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型，分别采用二进制和字符方式存储。MySQL把每个BLOB和TEXT值当作一个独立的对象处理。存储引擎在存储时通常会做特殊处理。当BLOB和TEXT值太大时，InnoDB会使用专门前“外部”存储区域来进行存储，此时每个值在行内需要1〜4个字节存储一个指针，然后在外部存储区域存储实际的值。

BLO和text区别。BLOB类型存储的是二进制数据，没有排序规则或字符集，而TEXT类型有字符集和排序规则。

最好的解决方案是尽量避免使用BLOB和TEXT类型。如果实在无法避免，有一个技巧是在所有用到BLOB字段的地方都使用SUBSTRING（column, length）将列值转换为字符串（在ORDER BY子句中也适用），这样就可以使用内存临时表了。但是要确保截取的子字符串足够短，不会使临时表的大小超过max_heap_table size或tmp_ table_size,超过以后MySQL会将内存临时表转换为MylSAM磁盘临时表。

最坏情况下的长度分配对于排序的时候也是一样的，所以这一招对于内存中创建大临时表和文件排序，以及在磁盘上创建大临时表和文件排序这两种情况都很有帮助。

2.4 ENUM枚举类型

有时候可以使用枚举列代替常用的字符串类型。枚举列可以把一些不重复的字符串存储成一个预定义的集合。MySQL在存储枚举时非常紧凑，会根据列表值的数量压缩到一个或者两个字节中。MySQL在内部会将每个值在列表中的位置保存为整数，并且在表的.力w文件中保存“数字-字符串”映射关系的“査找表”。下面有一个例子：

mysql> CREATE TABLE enum_test(

-> e ENUM('fish',apple', 'dog') NOT NULL

-> )；

mysql> INSERT INTO enum_test(e) VALUES ('fish'), ('dog'), ('apple');

这三行数据实际存储为整数，而不是字符串。可以通过在数字上下文环境检索看到这个双重属性：

mysql> SELECT e + 0 FROM enum_test;

mysql关于优化设计模式与数据类型_mysql

mysql关于优化设计模式与数据类型_字符串_02

如果使用数字作为ENUM枚举常量，这种双重性很容易导致混乱，例如ENUM('1','2','3')建议尽量避免这么做。

另外一个让人吃惊的地方是，枚举字段是按照内部存储的整数而不是定义的字符串进行排序的：

mysql关于优化设计模式与数据类型_字符串_03

下面这个sql是以字段e的字母进行排序。

mysql关于优化设计模式与数据类型_数据_04

如果在定义时就是按照字母的顺序，就没有必要这么做了。

枚举最不好的地方是，字符串列表是固定的，添加或删除字符串必须使用ALTER TABLEO 因此，对于一系列未来可能会改变的字符串，使用枚举不是一个好主意，除非能接受只在列表末尾添加元素，这样任MySQL 5.1中就可以不用重建整个表来完成修改。

由于MySQL把每个枚举值保存为整数，并且必须进行査找才能转换为字符串，所以枚举列有一些开销。通常枚举的列表都比较小，所以开销还可以控制，但也不能保证一直如此。在特定情况下，把CHAR/VARCHAR列与枚举列进行关联可能会比直接关联CHAR/ VARCHAR列更慢。

2.5 日期和时间类型

MySQL可以使用许多类型来保存日期和时间值，例如YEAR和DATE。MySQL能存储的最小时间粒度为秒（MariaDB支持微秒级别的时间类型）。但是MySQL也可以使用微秒级的粒度进行临时运算，我们会展示怎么绕开这种存储限制。

大部分时间类型都没有替代品，因此没有什么是最佳选择的问题。唯一的问题是保存日期和时间的时候需要做什么。MySQL提供两种相似的日期类型：DATETIME和TIMESTAMP对于很多应用程序，它们都能工作，但是在某些场景，一个比另一个工作更好。主要是表现为范围不同。

TIMETAMP：就像它的名字一样，TIMETAMP类型保存了从1970年1月1日午夜(格林尼治标准时间)以来的秒数，它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间, 因此它的范围比DATETIME小得多：只能表示从1970年到2038年。MySQL提供了FR0M_UNIXTIME()函数把Unix时间戳转换为日期，并提供了UNIX_TIMESTAMP()函数把日期转换为Unix时间戳。

DATETIME:类型能保存大范围的值，从1001年到9999年，精度为秒。它把日期和时间封装到格式为YYYYMMDDHHMMSS的整数中，与时区无关。使用8个字节的存储空间。

默认情况下，MySQL以一种可排序的、无歧义的格式显示DATETIME值，例如"2008-01-16 22:37:08"。这是ANSI标准定义的日期和时间表示方法。

2.6:位数据类型

BIT：MySQL有少数几种存储类型使用紧凑的位存储数据。所有这些位类型，不管底层存储格式和处理方式如何，从技术上来说都是字符串类型。底层使用的是能够容纳它的最小整数类型，和数值类型相比，没有节省空间。

MySQL 默认把 bit 类型的字段值看作字符串。

例如 b’00111001’ 转换成十进制是 57 ，对应 ascii 字符 ‘9’，如果直接读取，MySQL 认为它是字符 ‘9’：

mysql关于优化设计模式与数据类型_数据_05

如果用于数值运算， b’00111001’ 会被转换成数值 57，bit 的数值行为容易让人困惑，尽量避免使用。

如果表示一个Y/N，建议用 tinyint，耗费的存储空间和 bit(1) 相同
如果要表示多个Y/N，建议用 Set 或整数（在代码中标记每个位的含义）

2.7 SET类型

用 Set 存放多个 Y/N：

mysql> CREATE TABLE acl (
    -> perms SET('CAN_READ', 'CAN_WRITE', 'CAN_DELETE') NOT NULL
    -> );
mysql> INSERT INTO acl(perms) VALUES ('CAN_READ,CAN_DELETE');
mysql> SELECT perms FROM acl WHERE FIND_IN_SET('CAN_READ', perms);
+---------------------+
| perms               |
+---------------------+
| CAN_READ,CAN_DELETE |
+---------------------+

用整数存放多个 Y/N：

mysql> SET @CAN_READ := 1 << 0,
    -> @CAN_WRITE := 1 << 1,
    -> @CAN_DELETE := 1 << 2;
mysql> CREATE TABLE acl (
    -> perms TINYINT UNSIGNED NOT NULL DEFAULT 0
    -> );
mysql> INSERT INTO acl(perms) VALUES(@CAN_READ + @CAN_DELETE);
mysql> SELECT perms FROM acl WHERE perms & @CAN_READ;
+-------+
| perms |
+-------+
| 5     |
+-------+

注意事项：

Set 方式优点是直观，查询时直接显示实际含义，缺点是增加新值时，要修改字段定义

Json

相比拆分成多个列，json 的存储空间增加，查询开销也小幅增加。

主键的选择

设定主键后，其它表引用主键时，一定要用同样的定义，避免额外转换开销或转换错误
主键类型占用的空间应尽可能小
优先选择整数类型，避免使用字符串
避免使用完全随机数，随机会导致数值分布空间大，数值分布不可预测，影响缓存加载，增加插入和查询开销
如果要存放UUID，转换成16字节的数字存放

注意事项：

ORM 以及一些框架自动生成的表定义，很可能使用了不合理的字段类型

其它注意事项

表的列数如果有上百行，会带来大量的 cpu 开销，即使只使用几列，innodb 也需要解析所有数据列
太多表 join 会成为严重问题，MySQL 设置的上限为 61 张表

Chapter 6. Indexing For High Performance

索引分类：

B树索引
全文索引

MySQL实现的B树索引的限制：

不支持非最左匹配场景
不能跳过索引中的某一列，索引中的列整体构成 key，不能跳过中间某一个，以右侧的列为索引（还是最左匹配的问题）

索引推荐读物：Relational Database Index Design and the Optimizers, by Tapio Lahdenmaki and Mike Leach (Wiley)

B树索引优点：

数值临近的索引，在存储空间上临近，可以为 order by 、 group by 提供优化手段
索引中备份了构成给索引的字段值，如果查询请求只需要这些字段，可以避免回表查询整行数据

索引评价三星指标：

是否将相关的行临近存放
存放顺序是否和查询顺序一致
是否包含查询需要的列

索引适合中型表和大型表，如果特别小的表，直接扫表更高效。如果表的规模非常大，索引的额外开销会很高，可以使用分区的方式处理。

提高索引性能的策略

目标索引包含的列在 where 条件中单独呈现，不要加函数处理，或者放在运算表达式中：

mysql> SELECT actor_id FROM actor WHERE actor_id + 1 = 5;      --  不会命中 actor_id 索引

如果区分度足够，没必要使用整个列，可以使用这个列中数值的前缀，以减少索引占用的空间。对于 blog/text，以及过长的 varchar，MySQL 不允许对完整值建索引。

如果发现某个语句使用了 index_merge，需要考虑下索引能不能优化：

mysql> EXPLAIN SELECT film_id, actor_id FROM film_actor
    -> WHERE actor_id = 1 OR film_id = 1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: index_merge
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY,idx_fk_film_id
      key_len: 2,2
          ref: NULL
         rows: 29
        Extra: Using union(PRIMARY,idx_fk_film_id); Using where

TODO: Multicolumn Indexes 这节没有看明白，说是如果为每个列单独建索引，MySQL 可能使用 index_merge 技术，并发过滤多个索引，然后将结果汇和，这个汇和可能非常耗费内存和CPU。

包含多列的索引：

如果不考虑排序和 group by，把区分度最高的列放在最左边

普通索引的叶子节点中存放的是主键数值，不是行的地址，如果主键过大，其它索引开销相应增加。

主键索引/聚簇索引的示意图，叶子节点存放的是完整行：

mysql关于优化设计模式与数据类型_数据_06

普通索引的示意图，叶子节点存放的是主键数值：

mysql关于优化设计模式与数据类型_mysql_07

避免用 uuid 做主键，会导致写入性能大幅下降，uuid 数值随机导致插入时写入位置随机：

mysql关于优化设计模式与数据类型_mysql_08

用 ANALYZE TABLE 重建索引的统计数值。

Chapter 7. Query Performance Optimization

执行过程与开销度量

MySQL 语句执行过程：

mysql关于优化设计模式与数据类型_mysql_09

响应时间由两部分组成：

servcie time：MySQL 真正执行语句的时间
queue time：等待执行的时间，IO、锁等各种原因导致的排队

客户端与 MySQL Server 的通信是半双工的，发送方的数据发送过程不会被打断。SQL 语句长度受到 max_allowed_packet 的限制。

MySQL 每找到一条满足条件的数据后，就立即发送给客户端，边找边发送（排序情况除外）。

服务端向客户端发送数据时，语句处于 sending data 状态，不会释放锁以及其它资源。

服务端线程状态：

sleep；等待客户端的查询语句
query：正在执行查询或者返回数据中
locked：等待服务端实现的表锁
analyzing and statistics：分析存储引擎的统计数据，进行查询优化
coping to tmp table [on disk]：数据拷贝到零时表中
sorting result：结果排序
sending data

上一条语句的执行开销记录在当前会话的 Last_query_cost 变量中，意思是执行了多少次数据页面随机读取动作（这是实际代价，和 explain 语句中的估计代价不同）：

mysql> show status like 'Last_query_cost';;
+-----------------+--------------+
| Variable_name   | Value        |
+-----------------+--------------+
| Last_query_cost | 20276.199000 |
+-----------------+--------------+
1 row in set (0.00 sec)

导致 SQL 执行慢的原因：

获取了过多的行、以及过多的列
扫描太多的行才找到匹配的数据

MySQL访问数据方式由快到慢：

constants
unique index lookups
range scans
index scans
full table scans

where 语句有三个作用位置：

作用于存储引擎查询：查询存储引擎时直接排除不需要的数据
作用于索引字段排除：使用了 covering index 时，排除索引中不需要的字段
作用于 MySQL Server：对从存储引擎中取出的数据进一步过滤

如果 MySQL 检查的数据行数，远远大于最终查找到的行数，尝试用以下几种优化方式：

使用 covering indexes，减少回表
重新设计表，譬如针对数据聚合场景，设计里一个专用的 Summary 表
重构查询语句，让 MySQL 能够有效优化

查询语句的重构思路主要有以下几个：

单条复杂语句与多条语句的权衡：相比数据读取，网络延迟更大，如果可能，减少查询是一个好的策略。
分治处理：譬如要检索全表删除一部分数据，可以每万条执行一次。
join 语句拆解：把一个 join 语句拆成多个独立语句，相应数据处理放在应用层实现。

优化器与语句

优化器的两个基本的优化方法：

static 静态优化：通过分析 sql 语句解析出来 parse tree，静态计算 where 语句开销，与查询语句中的数值无关
dynamic 动态优化：根据 where 语句中的数值、数据行数、索引情况等「变动的数据」估算代价，进行语句优化

MySQLserver 不记录数据的的统计数值，统计数值由存储引擎维护。

优化器未选出实际最优方案的原因：

受到事务/mvcc等影响，优化器使用的统计数据错误（没有更新到准确数值）
估计的执行代价和实际执行代价不同，MySQL 优化器不假定数据在缓存中，全部按磁盘读取估计代价
优化器的优化策略是代价尽可能小，而不是尽可能快
优化器不考虑并行的其它语句可能带来的改善（譬如缓存）
优化器有些固化的规则，导致没有选出最优方案。譬如如果存在 match，无脑选择 fulltext 索引
优化器不考虑不再掌控范围内的操作代价，譬如sql中使用的自定义函数、存储过程函数
优化器不会评估所有的执行计划，可能漏掉了最佳方案

优化器能够识别的优化项目：

reordering join：join 语句重排，优化器能够判断出最佳的 join 顺序
couverting out joins to inner joins：转换成等价的 inner join
Applying algebraic equivalence rules：譬如 5=5 and a>5 会被转换成等价的 a>5
count(), min(), and max() optimizations：利用B树索引特点，直接从第一个/最后一个位置取出最小/最大值
Evaluating and reducing constant expressions：直接把结果为常量的表达式计算成常量

优化时发现条件不可能满足，直接终止：

mysql> EXPLAIN SELECT film.film_id FROM film WHERE film_id = −1;
+----+...+-----------------------------------------------------+
| id |...| Extra                                               |
+----+...+-----------------------------------------------------+
|  1 |...| Impossible WHERE noticed after reading const tables |
+----+...+-----------------------------------------------------+

结果排序过程

MySQL 不能直接获取到有序的结果（除非是按照有索引的列排序），通常需要执行专门的排序操作。无论是内存中排序还是利用磁盘文件排序，在 MySQL 中统一称为 filesort，选用的排序算法是快排。

排序时，以行的最大可能长度为单位申请内存，使用 varchar 时要特别小心：

如果使用了 varchar，采用 varchar 的最大长度
如果使用了 utf-8，每个字符默认占用 3 字节

如果 order by 使用了非第一个表中的列，MySQL 将结果存放在临时表，然后在临时表上排序，explain 中显示 Using temporary; Using filesort。

limit 作用于排序后的结果，不能减少排序开销。

in 语句特点

MySQL 把 in 语句中的多个数值构建成一个二叉搜索树，实现 O(logn) 的时间复杂度。有些数据库选用等价于 or 语句的方案，时间复杂度为 O(n)。

优化时，如果发现一个查询任务的 in() 列表，适用于另一个查询任务，优化器会将 in() 条件加入另一个查询任务中。如果 in() 列表过大，反而会导致整体变慢。

最重要的 join 优化

MySQL将每条语句都当作 join 语句处理，或者说会把众多语句都归化为 join 场景，单表查询被看作是一个特殊的 join，因此 join 优化是最重要的优化。

MySQL 执行的 join 的过程：从一张表中找到一条符合条件的记录后，立即转入下一个表寻找符合条件的记录，如果没找到回退到上一张表，如果所有表中符合条件的行都找到了，提取每个表中的相关的列组成一条结果。把各种类型的 join 都转换 left outer join / inner join 场景，不支持 full outer join。

mysql关于优化设计模式与数据类型_数据_10

MySQL 的 join 实现方法导致执行计划是一棵向左倾斜的树：

mysql关于优化设计模式与数据类型_字符串_11

join 优化中最重要的一项是通过调整表的顺序，即通过调整驱动表，用更小的代价得到同样的结果。

注意：如果表的数量过多，排列组合的总数会指数式增加，MySQL 不会评估所有组合，表的数量超过 optimizer_search_depth 时，使用贪心策略寻找较优方案，因此可能漏掉最优方案。

另外，MySQL 会根据表中数据以及查询语句特点，提前结束或者缩小数据遍历范围。

file.file_id = 1 的记录只有一条时，对应的 ref 类型为 const：

mysql> EXPLAIN SELECT film.film_id, film_actor.actor_id
    -> FROM film
    ->    INNER JOIN film_actor USING(film_id)
    -> WHERE film.film_id = 1;
+----+-------------+------------+-------+----------------+-------+------+
| id | select_type | table      | type  | key            | ref   | rows |
+----+-------------+------------+-------+----------------+-------+------+
|  1 | SIMPLE      | film       | const | PRIMARY        | const |    1 |
|  1 | SIMPLE      | film_actor | ref   | idx_fk_film_id | const |   10 |
+----+-------------+------------+-------+----------------+-------+------+

对于一个 film_id 只要在 file_actor 表中找到一条对应记录，就排除它，不再检查 file_actor 表中的其它数据：

mysql> SELECT film.film_id
    -> FROM film
    ->    LEFT OUTER JOIN film_actor USING(film_id)
    -> WHERE film_actor.film_id IS NULL;

MySQL 会识别出 film_id > 500 也适用于 film_actor，限制对 film_actor 表的扫描范围：

mysql> SELECT film.film_id
    -> FROM film
    ->    INNER JOIN film_actor USING(film_id)
    -> WHERE film.film_id > 500;

优化器的局限

MySQL 的 join 优化机制，有一些不适应的场景，需要避免使用一些语句用法。

关联子查询缺陷

如果在 in 中使用关联子查询（correlated subqueries）， MySQL 的优化结果会很差，例如：

mysql> SELECT * FROM film
    -> WHERE film_id IN(
    ->    SELECT film_id FROM film_actor WHERE actor_id = 1);

MySQL 将其优化成类似下面的语句：

SELECT * FROM film
WHERE EXISTS (
   SELECT * FROM film_actor WHERE actor_id = 1
   AND film_actor.film_id = film.film_id);

它没有取出有限的 film_id 作为约束，而是对 film 进行全表扫描，判断每行数据是否满足约束：

mysql> EXPLAIN SELECT * FROM film ...;
+----+--------------------+------------+--------+------------------------+
| id | select_type        | table      | type   | possible_keys          |
+----+--------------------+------------+--------+------------------------+
|  1 | PRIMARY            | film       | ALL    | NULL                   |
|  2 | DEPENDENT SUBQUERY | film_actor | eq_ref | PRIMARY,idx_fk_film_id |
+----+--------------------+------------+--------+------------------------+

改进方法一：用 exists 语句代替 in，等价效果的 exists 语句通常好于 in。（书中未说明原因 TODO)

mysql> SELECT * FROM film
    -> WHERE EXISTS(
    ->    SELECT * FROM film_actor WHERE actor_id = 1
    ->       AND film_actor.film_id = film.film_id);

改进方法二：用 join 语句替换子查询，MySQL 选择则符合条件的数据量更少的 film_actor 表做驱动表。

mysql> SELECT film.* FROM film
    ->    INNER JOIN film_actor USING(film_id)
    -> WHERE actor_id = 1;

改进方法三：拆成两条语句，先查出 film_id，第二条语句的 in 中直接填入 id，这种方式会比较 join 快。

mysql> SELECT film_id FROM film_actor WHERE actor_id = 1);
mysql> SELECT * FROM film  WHERE film_id IN(XXX)

需要注意，并非所有 join 语句都比使用子查询高效，例如下面的 join 语句：

mysql> SELECT DISTINCT film.film_id FROM sakila.film
    ->    INNER JOIN sakila.film_actor USING(film_id);

用 join 语句会把一个 film_id 能够关联的行全部生成，再执行去重动作。

实际并不需要生成所有行，只要找到一个关联行就可以确定当前 film_id 在结果中，子查询更高效：

mysql> SELECT film_id FROM sakila.film
    ->    WHERE EXISTS(SELECT * FROM sakila.film_actor
    ->    WHERE film.film_id = film_actor.film_id);

使用 join 语句时，要注意以下事项：

用于连接的列上有索引
group by 和 order by 使用的列位于一张表，使 MySQL 能够选出可用的索引
不同版本的 MySQL 的 join 行为可能不同，升级版本时要特别注意

union 语句缺陷

union 中的限制条件不会被用于子语句。

下面的语句会把 actor 和 customer 两张表的所有数据全部导入到临时表中，然后从临时表中取 20 行：

(SELECT first_name, last_name
 FROM sakila.actor
 ORDER BY last_name)
UNION ALL
(SELECT first_name, last_name
 FROM sakila.customer
 ORDER BY last_name)
LIMIT 20;

改成下面的方式可以避免两张表的全量数据排序（但是结果会有变化，需要根据实际情况决定）：

(SELECT first_name, last_name
 FROM sakila.actor
 ORDER BY last_name
 LIMIT 20)
UNION ALL
(SELECT first_name, last_name
 FROM sakila.customer
 ORDER BY last_name
 LIMIT 20)
LIMIT 20;

使用 union 还需要注意，

MySQL 默认对 union 的结果进行排序，如果不需要排序，使用

union all

Index Merge 缺陷

当 where 后面的条件比较复杂时，用一个表的多个索引分别查出多个结果，然后对多个结果集合进行集合运算得出最终结果，这个特性叫做 Index Merge。但是 MySQL 不支持在多个 cpu 上并发执行一个会话线程，index merge 中的多个索引独立查询是串行的。

另外 MySQL 不支持通用的稀疏索引（只在个别 group by 场景有一定支持），需要明确建立更多索引。

例如语句 SELECT ... FROM tbl WHERE b BETWEEN 2 AND 3;，如果支持稀疏索引，在不为 b 建索引的情况下，先对 a=1 的行对应对 b 列快速查找（稀疏索引应该自动实现 b 列上索引），查找完后 a=1 的行后，再从 a=2 处重复上述动作。

mysql关于优化设计模式与数据类型_数据_12

禁止同时读写

MySQL 不允许一个 sql 语句同时读写同一张表：

mysql> UPDATE tbl AS outer_tbl
    ->    SET cnt = (
    ->       SELECT count(*) FROM tbl AS inner_tbl
    ->       WHERE inner_tbl.type = outer_tbl.type
    ->    );
ERROR 1093 (HY000): You can't specify target table 'outer_tbl' for update in FROM
clause

常用技巧与注意事项

利用索引特性找最大/最小值

first_name 上没有索引，actor_id 是有索引， actor_id 是已经排好顺序的。

如果用下面的写法，MySQL 需要对全表进行扫描，找出所有 first_name = ‘PENELOPE’ 的列，然后取 actor_id 的最小值：

mysql> SELECT MIN(actor_id) FROM sakila.actor WHERE first_name = 'PENELOPE';

利用 actor_id 已经有序的特性，写出更高效的语句，找出 first_name = ‘PENELOPE’ 的第一个记录：

mysql> SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY)
    -> WHERE first_name = 'PENELOPE' LIMIT 1;

这种写法的问题是，语句含义不直观，表面看是查找满足条件的第一行数据，实际目的是找 min(actor_id)。

用 count() 统计记录数

count() 函数有两个工作场景：

count(列名）：统计列中非 NULL 的数值数量，注意是不去重的数值数量，等同于有数值的行数
count(*)：统计行数

以下表中的数据为例：

mysql> select * from scores;
+----+---------+-----------+-------+---------------------+---------------------+
| id | name    | home      | score | create_at           | update_at           |
+----+---------+-----------+-------+---------------------+---------------------+
|  1 | 小明    | NULL      |   104 | 2021-01-01 00:00:00 | 2021-09-23 17:14:57 |
|  2 | 小明    | NULL      |   100 | 2021-01-02 00:00:00 | 2021-08-10 18:19:25 |
|  3 | 小红    | NULL      |    20 | 2021-11-02 18:37:00 | 2021-11-02 18:37:00 |
|  4 | 小红    | NULL      |    98 | 2021-02-01 00:00:00 | 2021-08-10 18:19:25 |
|  5 | 小王    | NULL      |    10 | 2021-09-01 14:01:33 | 2021-09-01 14:01:33 |
|  6 | 小王    | NULL      |    11 | 2021-09-01 14:02:58 | 2021-09-01 14:02:58 |
|  7 | 王二    | 黑龙江    |     0 | 2021-09-24 16:01:58 | 2021-11-02 19:35:09 |
|  8 | wanger  | 黑龙江    |     0 | 2021-09-24 16:08:31 | 2021-09-24 16:08:31 |
|  9 | wanger2 | NULL      |     0 | 2021-09-24 16:16:59 | 2021-09-24 16:16:59 |
+----+---------+-----------+-------+---------------------+---------------------+

count(home) 的结果是 2，两个「黑龙江」，排除了 NULL：

mysql> select count(home) from  scores;
+-------------+
| count(home) |
+-------------+
|           2 |
+-------------+
1 row in set (0.00 sec)

count(*) 的结果是表的行数：

mysql> select count(*) from  scores;
+----------+
| count(*) |
+----------+
|        9 |
+----------+
1 row in set (0.00 sec)

可以用一个语句同时计算出一个列中不同数值出现的次数。

方法1：用 count(col) 实现，非关心的数值标记为 NULL

mysql> select count(name='小明' or NULL),count(name='王二' or NULL) from scores;
+------------------------------+------------------------------+
| count(name='小明' or NULL)   | count(name='王二' or NULL)   |
+------------------------------+------------------------------+
|                            2 |                            1 |
+------------------------------+------------------------------+

方法2：用 sum() 实现，非关心的数值累加 0

mysql> select sum(if(name='小明',1,0)) ,sum(if(name='王二',1,0))  from scores;
+----------------------------+----------------------------+
| sum(if(name='小明',1,0))   | sum(if(name='王二',1,0))   |
+----------------------------+----------------------------+
|                          2 |                          1 |
+----------------------------+----------------------------+

count() 计算的是精确数量，需要把所有数据查出后才能获得。

如果对精确性要求低，可以直接使用 explain 估计的行数：

mysql> explain select * from scores;
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | scores | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
+------+-------------+--------+------+---------------+------+---------+------+------+-------+

group by 和 distinct 注意事项

group by 和 distinct 的内部实现类似，优化的时候经常彼此转换，主要优化方式：

使用的聚集列上要有索引
需要读取非聚集列，建议在 SQL_MODE 中添加 ONLY_FULL_GROUP_BY，在读取非聚集列时报错
如果不需要排序，用 order by null 取消，MySQL 默认 group by 的结果排序
不要在语句中使用 with rollup，把汇总操作放在应用层实现

group by 使用的列上没有索引时，MySQL 要使用临时表或者 filesort 处理。

with rollup 说明：将 group by 的分组聚合数值累加后作为最后一列。

mysql> select coalesce(name,'合计') name,sum(score) from scores group by name with rollup;
+---------+------------+
| name    | sum(score) |
+---------+------------+
| 王二    |          0 |
| wanger  |          0 |
| wanger2 |          0 |
| 小王    |         21 |
| 小红    |        118 |
| 小明    |        204 |
| 合计    |        343 |
+---------+------------+
7 rows in set, 1 warning (0.01 sec)

limit 优化

常规的 limit 写法会检索 offset+limit 条数据，如果 offset 非常大，会导致检索大量数据。

下面的语句检索 55 条数据后，抛弃前 50 行：

mysql> SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;

优化方法1：用 join 语句减少列数据读取。

子查询中先完成排序，获得要输出的 film_id，这一步只读取 film_id，不读取其它列，film_id 要有索引，利用 covering index 避免回表。然后 MySQL 使用子查询生成的表作为驱动表，只扫描 5 个 film_id 对应的行：

mysql> SELECT film.film_id, film.description
    -> FROM sakila.film
    ->    INNER JOIN (
    ->       SELECT film_id FROM sakila.film
    ->       ORDER BY title LIMIT 50, 5
    ->    ) AS lim USING(film_id);

优化方法2：使用游标。

mysql> SELECT * FROM sakila.rental
-> WHERE rental_id < 16030
-> ORDER BY rental_id DESC LIMIT 20;

不要使用 SQL_CALC_FOUND_ROWS，SQL_CALC_FOUND_ROWS 是把所有行都查出来再统计数值：

mysql> select SQL_CALC_FOUND_ROWS * from scores limit 2,2;
+----+--------+------+-------+---------------------+---------------------+
| id | name   | home | score | create_at           | update_at           |
+----+--------+------+-------+---------------------+---------------------+
|  3 | 小红   | NULL |    20 | 2021-11-02 18:37:00 | 2021-11-02 18:37:00 |
|  4 | 小红   | NULL |    98 | 2021-02-01 00:00:00 | 2021-08-10 18:19:25 |
+----+--------+------+-------+---------------------+---------------------+
2 rows in set (0.00 sec)

mysql> SELECT FOUND_ROWS();
+--------------+
| FOUND_ROWS() |
+--------------+
|            9 |    <-- 把所有数据都查出后统计的数字
+--------------+
1 row in set (0.01 sec)

如果要获取数据总行数：

用 explain 获取估计数值（google 搜索结果中的数量就是估计数值）
用 count(*) 单独查出来

标签：语句,mysql,数据类型,actor,索引,MySQL,设计模式,id,film
From： https://blog.51cto.com/wyf1226/5881753