MySQL是现阶段最盛行和广为采用的开放源码亲密关系型资料库众所周知，随著信息量的快速增长和出访阻抗的提高，强化资料库操控性显得非常重要，以保证控制系统能高工作效率地处置大批的mammalian允诺。责任编辑将历史记录许多MySQL资料库操控性强化的技巧，提高资料库的运转工作效率，提高控制系统操控性。
产品目录
来衡量查阅开支的分项MySQL出访类别查阅操控性强化 采用LIMIT 增加回到的列 加进检索 覆盖范围检索扫描器次序强化 检索次序 文档次序JOIN关连查阅其他强化基本功 储存发动机使用内存 解构查阅归纳
来衡量查阅开支的分项
对MySQL，最简单的来衡量查阅开支的四个分项如下表所示：
响应天数
扫描器的个字符
回到的个字符
响应天数是三个部份之和：服务项目天数和排队等候天数。
服务项目天数是指资料库处置这个查阅真正花了多长天数。
排队等候天数是指服务项目器因为等待某些资源而没有真正执行查阅的天数——可能是等I/O操作完成，也可能是等待行锁等。一般最常见和重要的等待是I/O和锁等待。
储存发动机的锁（表锁、行锁）、高mammalian资源竞争、硬件响应等诸多因素都会影响响应天数。
一般来说，数据表个字符越少出访速度更快，内存中的行也比磁盘中的行的出访速度要快得多。
理想情况下扫描器的个字符和回到的个字符应该是相同的，而实际情况中，通常需要扫描器多行才能生成结果集中的一行。
扫描器的个字符对回到的个字符的比率通常很小，一般在1:1和10:1之间，不过有时候这个值也可能非常非常大。——《高操控性MySQL》
MySQL出访类别
不同的出访方式下，需要扫描器的个字符可能会不同。出访类别有很多种，EXPLAIN 语句中的type列显示了当前查阅的出访类别：
mysql> EXPLAIN SELECT * FROM employees.employees WHERE first_name =Zvonko;+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 10.00 | Using where |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+1 row in set, 1 warning (0.04 sec)主要包括以下几种类别（速度从慢到快）：
ALL：全表扫描器
index：检索扫描器
range：覆盖范围扫描器
ref：非主键非唯一检索等值扫描器
eq_ref：主键检索或者非空唯一检索等值扫描器
const：常数引用，采用主键或唯一检索进行等值条件查阅时会用 const。
下面如是说许多MySQL操控性强化方法。
查阅操控性强化
MySQL基础架构：SQL查阅语句执行过程 中如是说了查阅语句的执行路径：
执行一条查阅语句时，主要执行流程为：
客户端发送一条查阅给服务项目器。
服务项目器先检查查阅内存，如果命中了内存，则立刻回到储存在内存
服务项目器端进行SQL解析、预处置，再由强化器生成对应的执行计划。
MySQL根据强化器生成的执行计划，调用储存发动机的API来执行查阅。
将结果回到给客户端。
查阅的每个操作都会花费天数，包括网络，CPU计算，生成统计信息和执行计划、锁等待（互斥等待）等操作，尤其是向底层储存发动机检索数据的调用操作，这些调用需要在内存操作、CPU操作和内存不足时导致的I/O操作上消耗天数。根据储存发动机不同，可能还会产生大批的上下文切换以及控制系统调用。
执行查阅包括了大批为了检索数据到储存发动机的调用以及调用后的数据处置，包括次序、分组等。查阅操控性低的最基本原因是出访的数据太多，因此可以通过增加出访的信息量的方式进行查阅操控性强化。
采用LIMIT
LIMIT，不需要查阅所有数据，然后再过滤。
如果没有加进检索，并且知道查阅结果只有一个，可以采用 LIMIT 1 来提高查阅工作效率。因为找到这条历史记录后就不会继续扫描器了，如果不采用LIMIT，会进行全表扫描器。
SELECT * FROM t_user WHERE email = [email protected] LIMIT 1;如果在email字段上加进了检索就不需要采用LIMIT了。
注意: EXPLAIN 方法在估计个字符时不考虑LIMIT语句，比如:
mysql> EXPLAIN SELECT * FROM employees.employees where gender=M;+—-+————-+———–+——+—————+——+———+——+——–+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+———–+——+—————+——+———+——+——–+————-+| 1 | SIMPLE | employees | ALL | NULL | NULL | NULL | NULL | 299556 | Using where |+—-+————-+———–+——+—————+——+———+——+——–+————-+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM employees.employees where gender=MLIMIT2;+—-+————-+———–+——+—————+——+———+——+——–+————-+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+—-+————-+———–+——+—————+——+———+——+——–+————-+| 1 | SIMPLE |employees| ALL | NULL | NULL | NULL | NULL | 299556 | Using where |+—-+————-+———–+——+—————+——+———+——+——–+————-+1 row in set (0.00 sec)mysql>增加回到的列
查阅时，如果不需要所有数据列，可以只取需要的列。如果看到SELECT * 语句时，检查一下是否需要回到全部列。
取出全部列，会让强化器无法完成检索覆盖扫描器这类强化，还会为服务项目器带来额外的I/O、内存和CPU的消耗。因此，许多DBA是严格禁止SELECT * 的写法的，这样做有时候还能避免某些列被修改带来的问题。——《高操控性MySQL》
加进检索
加进合适的检索是改善操控性的最优手段，尤其是当表中的信息量很大时，检索对操控性的影响非常大。
在MySQL中，检索是在储存发动机层实现的，所以，不同储存发动机的检索的工作方式可能不一样。此外检索有很多种类别，比如B-Tree检索、哈希检索、空间数据检索（R-Tree）等，它们在不同场景下有操控性差异，这里不做过多如是说，大多储存发动机采用的类别是B+Tree。
比如下表所示面的查阅中 birth_date 字段没有加进检索，采用的是全表扫描器：
mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date =1965-01-20;+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 10.00 | Using where |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+1 row in set, 1 warning (0.00 sec)接下来给 birth_date 字段加进一个检索：
mysql> ALTER TABLE `employees`.`employees` ADD INDEX `birth_date` (`birth_date` ASC) VISIBLE;执行查阅：
mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date =1965-01-20;+—-+————-+———–+————+——+—————+————+———+——-+——+———-+——-+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———–+————+——+—————+————+———+——-+——+———-+——-+| 1 | SIMPLE |employees| NULL | ref | birth_date | birth_date | 3 | const | 50 | 100.00 | NULL |+—-+————-+———–+————+——+—————+————+———+——-+——+———-+——-+1rowin set, 1 warning (0.00 sec)可以看到出访类别变成了ref(非唯一检索等值扫描器)，EXPLAIN估计的扫描器个字符大大增加，变成了50。
覆盖范围检索扫描器
覆盖范围扫描器（range）类别是一个有覆盖范围限制的检索扫描器，比全检索扫描器（index）更高工作效率。
以下情况都会采用到覆盖范围扫描器：
覆盖范围条件查阅： WHERE 子句中采用 BETWEEN、>、<、>=、<= 的查阅。注意!= 或者 <> 无法采用检索。
多个等值条件查阅：
采用 IN() 和 OR 。NOT IN条件运算符会执行全表扫描器，不会采用覆盖范围扫描器。
采用 like 进行前缀匹配模糊查阅，注意必须是前缀匹配：xxx%（这是由MySQL检索的储存结构决定的，因为MySQL的检索是采用B树(B-Tree)储存的，每个B树节点中储存检索值和对应行的地址。B树的搜索是基于前缀进行的，所以只有前缀匹配可以利用到B树检索）。
IN 条件运算符注意事项：
如果IN列表包含了太多的值，MySQL可能不会采用检索而采用全表扫描器。
在IN中采用子查阅会采用到检索。
如果 WHERE 子句采用了Mysql函数，会导致检索失效。比如搜索出生年份为1965年的职员(birth_date字段加进了检索)：
mysql> EXPLAIN SELECT * FROM employees.employees WHERE (LEFT(`birth_date`, 4) = 1965);+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 100.00 | Using where |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+————-+1 row in set, 1 warning (0.00 sec)根据结果可以发现上面的语句采用的是全表扫描器，没有采用检索，原因是WHERE 子句采用了Mysql函数，导致检索失效。要搜索出生年份为1965年的职员，且采用到检索，可采用如下表所示查阅语句：
mysql> EXPLAIN SELECT * FROM employees.employees WHERE birth_date >=1965-01-01 AND birth_date <= 1965-12-31;+—-+————-+———–+————+——-+—————+————+———+——+——+———-+———————–+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+—-+————-+———–+————+——-+—————+————+———+——+——+———-+———————–+| 1 | SIMPLE | employees | NULL | range | birth_date | birth_date | 3 | NULL | 1940 | 100.00 | Using index condition |+—-+————-+———–+————+——-+—————+————+———+——+——+———-+———————–+1 row in set, 1 warning (0.00 sec)可发现采用了覆盖范围扫描器，扫描器个字符显著减小。
在前面采用 WHERE 条件的例子中，Extra 列显示了 Using index 或者 Using Where，一般MySQL能采用如下表所示三种 WHERE 条件（操控性从好到坏）：
在检索中采用 WHERE 条件来过滤不匹配的历史记录。这是在储存发动机层完成的。
采用检索覆盖扫描器（Using index）来回到记录，直接从检索中过滤不需要的历史记录并回到命中的结果。这是在MySQL服务项目器层完成的，但无须再回表查阅历史记录。
从数据表中回到数据，然后过滤不满足条件的历史记录（Using Where）。这在MySQL服务项目器层完成，MySQL需要先从数据表读出历史记录然后过滤。
次序强化
检索次序
检索次序是对储存在资料库检索中的数据进行次序的过程。如果EXPLAIN 回到的 type 列的值为 index ，则说明 MySQL 采用了检索扫描器来次序，比如
mysql> EXPLAIN SELECT * FROM employees.dept_emp ORDER BY emp_no;+—-+————-+———-+————+——-+—————+———+———+——+——–+———-+——-+| id | select_type | table | partitions | type |possible_keys| key | key_len | ref | rows | filtered | Extra |+—-+————-+———-+————+——-+—————+———+———+——+——–+———-+——-+| 1 | SIMPLE | dept_emp | NULL |index| NULL | PRIMARY | 20 | NULL | 331143 | 100.00 | NULL |+—-+————-+———-+————+——-+—————+———+———+——+——–+———-+——-+1 row in set, 1 warning (0.00sec)文档次序
当不能采用检索次序时，MySQL需要自己进行次序，如果信息量小于“次序缓冲区”，则在内存中进行“快速次序”操作，如果信息量大则需要采用磁盘。MySQL会先将数据分块，对每个独立的块采用“快速排序”进行次序，并将各个块的次序结果存放在磁盘上，然后将各个排好序的块进行合并（merge），最后回到次序结果。MySQL将内存和在磁盘的这个次序过程统一称为文档次序（filesort）。
采用文档次序时，EXPLAIN 回到的 Extra 列显示的是 Using filesort:
mysql> EXPLAIN SELECT * FROM employees.employees order by first_name;+—-+————-+———–+————+——+—————+——+———+——+——–+———-+—————-+| id | select_type | table | partitions | type | possible_keys | key |key_len| ref | rows | filtered | Extra |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+—————-+| 1 | SIMPLE | employees | NULL | ALL | NULL | NULL | NULL | NULL | 299556 | 100.00 | Using filesort |+—-+————-+———–+————+——+—————+——+———+——+——–+———-+—————-+1 row in set, 1warning (0.00 sec)文档次序有两种次序算法：
两次传输次序（旧版本采用）：需要从数据表中读取两次数据，读取行指针和需要次序的字段，对其进行次序，然后再根据次序结果读取所需要的数据行。
单次传输次序（新版本采用）：先读取查阅所需要的所有列，然后再根据给定列进行次序，最后直接回到次序结果。
可以通过调整 max_length_for_sort_data这个参数来影响MySQL次序算法的选择，当查阅需要所有列的总长度小于这个参数值时，MySQL会采用“单次传输次序”。
次序强化注意事项
MySQL在进行文档次序的时候需要分配临时储存空间，如果需要回到的列非常多、非常大，会额外占用大批的空间，所以应尽可能避免次序或者尽可能避免对大批数据进行次序。如果一定要次序，可以采用检索次序来进行次序强化。
要采用检索次序需要注意以下情况： 1. 对单列次序（无论升序或降序），都会采用检索次序。 2. 如果是组合检索，次序规则要和组合检索的顺序匹配，顺序须满足检索最左前缀规则。如果 WHERE 子句或者 JOIN 子句中对左侧的检索列指定了常量，可以不满足检索的最左前缀的要求。 3. ORDER BY多个字段时，如果其中一个字段没有加进检索，将会走文档次序。 4. 如果次序采用了函数或表达式，不是直接引用检索列，无法采用检索次序。比如：SELECT column1, column2 FROM table_name ORDER BY ABS(column1);JOIN关连查阅
实际应用中，业务通常比较复杂，需要进行关连查阅。下面是许多 JOIN 关连查阅的强化方法：
尽量增加关连表的数量。JOIN 每增加一个表，查阅操控性就会下降。
保证关连字段的数据类别相同或兼容。如果数据类别不匹配，MySQL 会进个字符据转换，查阅工作效率会降低。
采用主键或唯一检索作为关连键。
尽量采用内连接(INNER JOIN)，避免外连接(LEFT JOIN)。外连接会导致中间表中的每个数据都会回到一次。
采用小表做驱动表，这样可以增加中间表的大小。
采用检索。在关连字段上创建检索可以显著提高查阅速度。
只回到需要的列，不要回到所有（*）
其他强化基本功
储存发动机
这里如是说MyISAM和InnoDB这两种最常用的MySQL储存发动机的差异：
事务支持：InnoDB支持事务，MyISAM不支持。所以在需要事务支持的应用场景下，InnoDB的操控性会更高。
mammalian操控性：MyISAM采用表锁，更新时会锁定整张表。InnoDB采用行锁，更新时只锁定当前行，可以实现更细粒度的mammalian控制，不同行之间的写操作可以mammalian进行，操控性更高。
外键约束：MyISAM不支持外键约束，Join时需要在应用层保证数据完整性，操控性较差。InnoDB支持外键约束，在多表Join时操控性会更高。
检索：InnoDB 支持聚簇检索，MyISAM 则只支持非聚簇检索，聚簇检索的查找工作效率要比非聚簇检索快，因为聚簇检索查找到检索就查找到了数据位置，而非聚簇检索查找到检索之后，根据历史记录的数据地址，再去查找数据。（详细如是说可参考MySQL检索如是说）
采用内存
对要重复执行的查阅，当初次查阅的时候将这个数据内存起来，需要的时候从内存中取出，这样操控性会更好。比如可以利用Redis内存查阅结果来提高MySQL的工作效率，将频繁查阅的结果内存到Redis中，当下次有相同的查阅允诺时，首先在Redis中查找结果，如果存在则直接回到，避免了对MySQL的查阅操作，从而提高响应速度和降低资料库的阻抗。
解构查阅
可以将一个大查阅分解为多个小查阅。比如删除旧的数据，定期地清除大批数据时，如果用一个大的语句一次性完成的话，则可能需要一次锁住很多数据、占满整个事务日志、耗尽控制系统资源、阻塞很多小的但重要的查阅。将一个大的DELETE语句切分成多个较小的查阅可以尽可能小地影响MySQL操控性，同时还可以增加MySQL复制的延迟。
归纳
责任编辑介绍的MySQL资料库操控性强化基本功主要有:
选择合适的储存发动机。
采用LIMIT增加回到数据。
增加回到的列，不要回到所有（*）。
检索强化：合理加进检索，正确采用检索。
JOIN关连查阅中采用小表做驱动表。
MySQL资料库操控性强化是一门比较广为和深入的学科,强化的方法和基本功较多,责任编辑对其做了比较简单的归纳和概括。在实际应用和开发中,需要综合考虑实际业务场景来有针对性地进行强化，以获得最佳的操控性提高效果。
MySQL强化方法很多，责任编辑仅做简单如是说。在实际应用和开发中，需要根据具体的业务场景和需求进行深入分析和强化，选择合适的强化方法。
END
