1. 如何查看MySQL的执行计划？

执行计划是SQL语句调优的一个重要依据，MySQL中使用EXPLAIN命令来查看SQL语句的查询执行计划（QEP）。从这条命令的输出结果中就能够了解MySQL优化器是如何执行SQL语句的。EXPLAIN命令可以运行在SELECT语句或特定表上，如果作用在表上，那么此命令等同于DESC命令；MySQL5.6.3版本之前只能对SELECT生成执行计划，5.6.3及之后的版本对SELECT、UPDATE、INSERT、DELETE和REPLACE都可以生成执行计划。

补充：MySQL REPLACE语句的工作原理如下：
· 如果新行已不存在，则MySQL REPLACE语句将插入新行。
· 如果新行已存在，则REPLACE语句首先删除旧行，然后插入新行。在某些情况下，REPLACE语句仅更
  新现有行（与UPDATE语句不同，如果未在SET子句中指定列的值，则REPLACE语句将使用列的默认值）。
要确定表中是否已存在新行，MySQL使用PRIMARY KEY或UNIQUE KEY索引。如果表没有这些索引之一，
则REPLACE语句等同于INSERT语句。

MySQL优化器是基于开销来工作的，它并不提供任何的QEP的位置。这意味着QEP是在每条SQL语句执行的时候动态地计算出来的。在MySQL存储过程中的SQL语句也是在每次执行时计算QEP的。下面来看一下查看MySQL的执行计划的示例：

# 首先创建一个学生信息表
mysql> CREATE TABLE TB_STUDENT(
    -> id int,
    -> name varchar(20),
    -> age int,
    -> INDEX mul_index (id, name, age)
    -> );
Query OK, 0 rows affected (0.02 sec)

# 查看表的信息
mysql> DESC TB_STUDENT;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# EXPLAIN直接作用于表，与DESC作用相同
mysql> EXPLAIN TB_STUDENT;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  | MUL | NULL    |       |
| name  | varchar(20) | YES  |     | NULL    |       |
| age   | int(11)     | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec)

# 查看表中的索引
mysql> SHOW KEYS FROM TB_STUDENT;
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table      | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| TB_STUDENT |          1 | mul_index |            1 | id          | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| TB_STUDENT |          1 | mul_index |            2 | name        | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
| TB_STUDENT |          1 | mul_index |            3 | age         | A         |           4 |     NULL | NULL   | YES  | BTREE      |         |               |
+------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)

# 插入一些示例数据
mysql> INSERT INTO TB_STUDENT VALUES (1, 'test1', 20), (2, 'test2', 30), (3, 'test3', 40), (4, 'test4', 50);
Query OK, 4 rows affected (0.00 sec)
Records: 4  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM TB_STUDENT;
+------+-------+------+
| id   | name  | age  |
+------+-------+------+
|    1 | test1 |   20 |
|    2 | test2 |   30 |
|    3 | test3 |   40 |
|    4 | test4 |   50 |
+------+-------+------+
4 rows in set (0.00 sec)

# EXPLAIN作用于SELECT语句，生成如下的执行计划
mysql> EXPLAIN SELECT name, age FROM TB_STUDENT WHERE id < 3;
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
| id | select_type | table      | partitions | type  | possible_keys | key       | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | TB_STUDENT | NULL       | range | mul_index     | mul_index | 5       | NULL |    2 |   100.00 | Using where; Using index |
+----+-------------+------------+------------+-------+---------------+-----------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

mysql>

下面介绍执行计划中各个指标的含义：
（1）id
id包含一组数字，表示查询中执行SELECT子句或操作表的顺序，执行顺序从大到小执行；当id值一样的时候，执行顺序由上往下。
（2）select_type
select_type表示查询中每个SELECT子句的类型，最常见的值包括SIMPLE、PRIMARY、SUBQUERY、DERIVED和UNION，其他可能的值还有UNION RESULT、DEPENDENT SUBQUERY、DEPENDENT UNION、UNCACHEABLE UNION以及UNCACHEABLE QUERY等。部分类型的具体的含义如下表所示：

类型	含义
SIMPLE	查询中不包含子查询、表连接或者UNION等其他复杂语法的简单查询，这是最常见的类型。
PRIMARY	查询中若包含任何复杂的子查询，则最外层查询被标记为PRIMARY，这个类型通常可以在DERIVED和UNION类型混合使用时见到。
SUBQUERY	在SELECT或WHERE列表中包含了子查询，则子查询被标记为SUBQUERY。
DERIVED	在FROM列表中包含的子查询被标记为DERIVED（衍生），或者说当一个表不是物理表（如临时表）时，那么就被称为DERIVED。例如： EXPLAIN SELECT SUM(DURATION) FROM (SELECT * FROM information_schema.PROFILING) T;
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION，即UNION中的第二个或者后面的查询语句会被标记为UNION；若UNION包含在FROM子句的子查询中，那么外层SELECT将被标记为DERIVED。
UNION RESULT	从UNION表获取结果的SELECT被标记为UNION RESULT。这是一系列定义在UNION语句中的表的返回结果。例如： EXPLAIN SELECT A.* FROM TB_NAME1 A UNION SELECT * FROM TB_NAME2 B;
DEPENDENT SUBQUERY	这个类型是为使用子查询而定义的，例如： EXPLAIN SELECT A.* FROM TB_NAME1 A WHERE EXISTS (SELECT 1 FROM TB_NAME2 B WHERE A.id = B.id);

（3）table
table表示查询涉及的表或衍生的表，是EXPLAIN命令输出结果中一个单独行的唯一标识符。这个值可能是表名、表的别名或者一个为查询产生的临时表的标识符，如派生表、子查询或集合。
（4）partitions
partitions表示给定表所使用的分区。这一列只会在EXPLAIN PARTITIONS语句中出现。
（5）type
type表示MySQL在表中找到所需行的方式，也称为“访问类型”。常见的类型如下表所示，从上到下，性能由最差到最好。

类型	说明
ALL	全表扫描（Full Table Scan），MySQL将进行全部扫描。
index	索引全扫描（Index Full Scan），MySQL将遍历整个索引来查询匹配的行，index与ALL的区别在于index类型只遍历索引树。
range	索引范围扫描（Index Range Scan），对索引的扫描开始于某一点，返回匹配值域的行，常见于BETWEEN、<、>、>=、<=的查询。注意当WHERE条件中使用IN时，则该列也是显示range。
ref	返回匹配某个单独值的所有行，常见于使用非唯一索引或唯一索引的非唯一前缀进行查找。此外，ref还经常出现在join操作中。
eq_ref	唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描，即在多表连接中，使用主键或唯一索引作用连接条件。
const	当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。例如，将主键列或唯一索引列置于WHERE列表中，此时MySQL就能将该查询转换为一个常量const。
system	表中只有一行数据或者是空表，且只能用于MyISAM和MEMORY表。如果是InnoDB表，那么type列通常都是ALL或者index。
NULL	MySQL在优化过程中分解语句，执行时不用访问表或索引就能直接得到结果。例如： EXPLAIN SELECT 1 FROM TB_NAME;

类型type还有其他值，如ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery（in的后面是一个查询主键字段的子查询）、index_subquery（与unique_subquery类似，区别在于in的后面是一个查询非唯一索引字段的子查询）、fulltext（全文索引检索，注意全文索引的优先级很高，若全文索引和普通索引同时存在时，MySQL不会考虑代价，优先选择使用全文检索）等。

（6）possible_keys
possible_keys表示查询时可能使用到的索引，指出MySQL能使用哪个索引在表中找到行，若查询涉及的字段上存在索引，则该索引将被列出，但不一定被使用。一个会列出大量可能的索引（例如多于3个）的QEP意味着备选索引数量太多了，同时也可能提示存在一个无效的单列索引。
（7）key
key显示MySQL在查询中实际使用的索引，若没有使用索引则显示为NULL。若查询中使用了覆盖索引，则该索引仅出现在key列表中，一般来说，SQL查询中的每个表都仅使用一个索引。
（8）key_len
key_len表示使用到索引字段的长度（字节数），可通过该列计算查询中使用的索引的长度。此列的值对于确认索引的有效性以及多列索引中用到的列的数目很重要。
常见的计算规律：
• 1个utf8字符集的字符占用3个字节；1个gbk字符集的字符占用2个字节。
• 对于变长的类型（VARCHAR），key_len还要加2个字节；若字段允许为空，则key_len需要加1。
• INT类型的长度为4。
• 对于DATETIME类型的字段，在MySQL5.6.4以前是8个字节（不能存储小数），之后的长度为5个字节加上小数位字节数（小数位为1或2，总字节数为6；小数位为3或4，总字节数为7；小数位为5或6，总字节数为8）。
（9）ref
ref表示上述表的连接匹配条件，即那些列或常量被用于查找索引列上的值。
（10）rows
rows表示MySQL根据表统计信息及索引选用情况，估算找到所需的记录所需要读取的行数。
（11）filtered
filtered表示返回结果的行占需要读到的行（rows列的值）的百分比。百分比越高，说明需要查询到数据越准确；百分比越小，说明查询到的数据量大，而结果集很少。
（12）Extra
Extra包含不适合在其他列中显示但十分重要的额外信息，包括以下内容：

包含的信息	解释
Using where	表示MySQL服务器在存储引擎收到记录后进行“后过滤”（Post-filter），如果查询未能使用索引，那么这个值的作用只是说明MySQL将用where子句来过滤结果集。如果查询使用了索引，那么行的限制条件是通过获取必要的数据之后处理读缓冲区来实现的。
Using temporary	表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询。这个值表示使用了内部临时（基于内存的）表。一个查询可能用到多个临时表。有很多原因会导致查询期间创建临时表，两个常见的原因如下：一是在来自不同表的列上使用了DISTINCT，二是使用了不同的ORDER BY和GROUP BY列。
Using filesort	MySQL中无法利用索引完成的排序操作称为“文件排序”。这是ORDER BY语句的结果，这可能是一个CPU密集型的过程。可以通过选择合适的索引来改进性能，用索引来为查询结果排序。
Using index	这个值强调了只需要使用索引就可以满足查询表的要求，不需要直接访问数据，说明MySQL正在使用覆盖索引。
Using join buffer	这个值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，应该注意要根据查询的具体情况添加索引来改进性能。
Impossible where	这个值强调了where语句会导致没有符合条件的行。
Select tables optimized away	这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
Distinct	这个值意味着MySQL在找到第一个匹配的行之后就会停止搜索其他行。
Index merges	当MySQL决定要在一个给定的表上使用超过一个索引的时候，就会出现该信息，用来详细说明使用的索引以及合并的类型。

2. 如何分析SQL语句的性能消耗？

MySQL可以使用profile分析SQL语句的性能消耗情况，比如SQL执行多少时间，CPU和内存使用量，执行过程中系统锁及表锁的花费时间等信息。通过have_profiling参数可以查看MySQL是否支持profile，通过profiling参数可以查看当前profile是否开启。查看profile是否开启的方法如下：

mysql> show variables like '%profil%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| have_profiling         | YES   |      # 当前MySQL是否支持profile
| profiling              | OFF   |      # 当前profile是否开启
| profiling_history_size | 15    |      # 设置profiling的数目，默认是15，范围为0~100，为0时表示禁用profiling
+------------------------+-------+
3 rows in set (0.00 sec)

mysql>

下面是有关profile的一些常用命令：
（1）set profiling=1; # 基于会话级别开启profile，关闭使用set profiling=off
（2）show profile for query query_id; # 根据query_id查看性能消耗情况
（3）show profile cpu for query query_id; # 根据query_id查看CPU的消耗情况
（4）show profile memory for query query_id; # 根据query_id查看内存的消耗情况
（5）show profile block io, cpu for query query_id; # 根据query_id查询I/O以及CPU的消耗情况
（6）show profile source for query query_id; # 根据query_id查询SQL执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数
profile是一个非常量化的指标，可以根据这些指标来比较各项资源的消耗，有利于对SQL语句的整体把控。在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io、context switch、page faults等明细类型来查看MySQL在使用什么资源上耗费了过多的时间。下面来看个实际例子：

# 开启profile
mysql> SET profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

# 执行一个SQL语句
mysql> SELECT * FROM TB_PERSON;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
|  1 | TEST1  |   10 |
|  2 | TEST2  |   20 |
|  3 | TEST3  |   30 |
|  4 | TEST4  |   40 |
|  5 | TEST5  |   50 |
|  6 | TEST6  |   60 |
|  7 | TEST7  |   70 |
|  8 | TEST8  |   80 |
|  9 | TEST9  |   90 |
| 10 | TEST10 |  100 |
+----+--------+------+
10 rows in set (0.00 sec)

# 查看总体的性能消耗情况
mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000050 |
| checking permissions | 0.000005 |
| Opening tables       | 0.000013 |
| init                 | 0.000013 |
| System lock          | 0.000006 |
| optimizing           | 0.000003 |
| statistics           | 0.000009 |
| preparing            | 0.000007 |
| executing            | 0.000002 |
| Sending data         | 0.000036 |
| end                  | 0.000003 |
| query end            | 0.000004 |
| closing tables       | 0.000004 |
| freeing items        | 0.000107 |
| cleaning up          | 0.000012 |
+----------------------+----------+
15 rows in set, 1 warning (0.01 sec)

# 查看I/O、内存和CPU消耗情况
mysql> show profile block io, memory, cpu for query 1;
+----------------------+----------+----------+------------+--------------+---------------+
| Status               | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+----------------------+----------+----------+------------+--------------+---------------+
| starting             | 0.000050 | 0.000024 |   0.000024 |            0 |             0 |
| checking permissions | 0.000005 | 0.000003 |   0.000003 |            0 |             0 |
| Opening tables       | 0.000013 | 0.000006 |   0.000007 |            0 |             0 |
| init                 | 0.000013 | 0.000006 |   0.000006 |            0 |             0 |
| System lock          | 0.000006 | 0.000003 |   0.000003 |            0 |             0 |
| optimizing           | 0.000003 | 0.000001 |   0.000001 |            0 |             0 |
| statistics           | 0.000009 | 0.000005 |   0.000005 |            0 |             0 |
| preparing            | 0.000007 | 0.000003 |   0.000003 |            0 |             0 |
| executing            | 0.000002 | 0.000001 |   0.000001 |            0 |             0 |
| Sending data         | 0.000036 | 0.000017 |   0.000018 |            0 |             0 |
| end                  | 0.000003 | 0.000001 |   0.000001 |            0 |             0 |
| query end            | 0.000004 | 0.000003 |   0.000002 |            0 |             0 |
| closing tables       | 0.000004 | 0.000001 |   0.000002 |            0 |             0 |
| freeing items        | 0.000107 | 0.000006 |   0.000005 |            0 |             0 |
| cleaning up          | 0.000012 | 0.000005 |   0.000006 |            0 |             0 |
+----------------------+----------+----------+------------+--------------+---------------+
15 rows in set, 1 warning (0.00 sec)

# 查看SQL执行过程中每个步骤对应的源码的文件、函数名以及具体的源文件行数
mysql> show profile source for query 1;
+----------------------+----------+-----------------------+----------------------+-------------+
| Status               | Duration | Source_function       | Source_file          | Source_line |
+----------------------+----------+-----------------------+----------------------+-------------+
| starting             | 0.000050 | NULL                  | NULL                 |        NULL |
| checking permissions | 0.000005 | check_access          | sql_authorization.cc |         809 |
| Opening tables       | 0.000013 | open_tables           | sql_base.cc          |        5815 |
| init                 | 0.000013 | handle_query          | sql_select.cc        |         128 |
| System lock          | 0.000006 | mysql_lock_tables     | lock.cc              |         330 |
| optimizing           | 0.000003 | optimize              | sql_optimizer.cc     |         158 |
| statistics           | 0.000009 | optimize              | sql_optimizer.cc     |         374 |
| preparing            | 0.000007 | optimize              | sql_optimizer.cc     |         482 |
| executing            | 0.000002 | exec                  | sql_executor.cc      |         126 |
| Sending data         | 0.000036 | exec                  | sql_executor.cc      |         202 |
| end                  | 0.000003 | handle_query          | sql_select.cc        |         206 |
| query end            | 0.000004 | mysql_execute_command | sql_parse.cc         |        4956 |
| closing tables       | 0.000004 | mysql_execute_command | sql_parse.cc         |        5009 |
| freeing items        | 0.000107 | mysql_parse           | sql_parse.cc         |        5622 |
| cleaning up          | 0.000012 | dispatch_command      | sql_parse.cc         |        1931 |
+----------------------+----------+-----------------------+----------------------+-------------+
15 rows in set, 1 warning (0.00 sec)

mysql>

3. MySQL中CHECK、OPTIMIZE和ANALYZE的作用分别是什么？

MySQL中检查表（CHECK）的主要作用是检查表是否存在错误；优化表（OPTIMIZE）的主要作用是消除删除或者更新造成的空间浪费；分析表（ANALYZE）的主要作用是分析关键字的分布。详细的信息如下表所示：

	CHECK	OPTIMIZE	ANALYZE
作用	CHECK不仅可以检查表是否存在错误，还可以检查视图是否有错误。例如，在视图定义中视图引用的表已不存在，可以使用REPAIR TABLE来修复损坏的表。	OPTIMIZE可以回收空间、减少碎片、提高I/O。如果已经删除了表的大部分数据，或者已经对含有可变长度行的表进行了很多更改，那么应该使用OPTIMIZE命令对表进行优化，将表中的空间碎片进行合并，并且消除由于删除或更新造成的空间浪费。	ANALYZE用于收集优化器统计信息，分析和存储表的关键字分布，分析的结果可以使数据库系统获得准确的统计信息，使得SQL能生成正确的执行计划。对于MyISAM表，本语句与使用myisamchk -a作用相当。
语法	CHECK TABLE TB_NAME [,TB_NAME] … [option] … option={FOR UPGRADE 或 QUICK FAST 或 MEDIUM 或 EXTENDED CHANGED}	OPTIMIZE [NO_WRITE_TO_BINLOG 或 LOCAL] TABLE TB_NAME [,TB_NAME] …	ANALYZE [NO_WRITE_TO_BINLOG 或 LOCAL] TABLE TB_NAME [,TB_NAME] …
举例	check table mysql.user;	optimize table mysql.user;	analyze table mysql.user;
注意事项	CHECK只对MyISAM和InnoDB表起作用。	OPTIMIZE只对MyISAM、BDB和InnoDB表起作用.	ANALYZE只对MyISAM、BDB和InnoDB表起作用。

但是，需要注意以下几点：
（1）对于InnoDB表来说，可以通过innodb_file_per_table参数来设置InnoDB为独立表空间模式，这样每个数据库的每个表都会生成一个独立的ibd文件，用于存储表的数据和索引，这样可以在一定程度上减轻InnoDB表的空间回收压力。另外，在删除大量数据后，InnoDB表可以通过ALTER TABLE但是不修改引擎的方法来回收不用的空间，该操作会重建表：ALTER TABLE TB_NAME ENGINE=InnoDB;。
（2）ANALYZE、CHECK、OPTIMIZE、ALTER TABLE执行期间会对表进行锁定（数据库系统会对表加一个只读锁），因此一定要注意在数据库不繁忙的时候执行相关操作。
（3）工具mysqlcheck可以检查和修复MyISAM表，还可以优化和分析表，它集成了MySQL工具中CHECK、REPAIR、ANALYZE和OPTIMIZE的功能。

4. 如何对MySQL进行优化？

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性，它是随着用户量的增加，基础架构才逐渐完善。

4.1 数据库的设计

（1）尽量让数据库占用更小的磁盘空间。
（2）尽可能使用更小的整数类型。
（3）尽可能地定义字段为NOT NULL，除非这个字段需要NULL。
（4）如果没有用到变长字段（如VARCHAR），那就使用固定大小的记录格式（如CHAR）。
（5）只创建确实需要的索引。索引有利于检索记录，但不利于快速保存记录（需要维护索引）。
（6）所有数据都得在保存到数据库前进行处理。
（7）所有字段都得有默认值。

4.2 系统的用途

（1）尽量使用长连接（一直保持数据库的连接，减少不停连接的开销）。
（2）通过EXPLAIN查看复杂SQL的执行方式，并进行优化。
（3）如果两个关联表要做比较，那么做比较的字段必须类型和长度都一致。
（4）LIMIT语句尽量跟ORDER BY或DISTINCT搭配使用，这样可以避免全表扫描（FULL TABLE SCAN）。
（5）如果想要清空表的所有记录，建议使用TRUNCATE TABLE TB_NAME而不是DELETE FROM TB_NAME。（补充：DROP和TRUNCATE是数据定义语言DDL，DELETE是数据操纵语言DML，一般执行速度DROP > TRUNCATE > DELETE）
（6）在一条INSERT语句中采用多重记录插入格式，而且使用load data infile来导入大量数据（从固定格式的文件中批量导入数据），这比单纯的INSERT快很多。

1	LOAD DATA INFILE '/file_path/test.txt' INTO TABLE tmp_loaddata FIELDS TERMINATED BY '\t';

（7）如果DATE类型的数据需要频繁地做比较，那么尽量保存为UNSIGNED INT类型，这样可以加快比较的速度。

4.3 系统的瓶颈

（1）磁盘搜索：并行搜索，把数据分开存放到多个磁盘中，这样能加快搜索时间。
（2）磁盘读写（I/O）：可以从多个媒介中并行的读取数据。
（3）CPU周期：数据存放在主内存中，这样就得增加CPU的个数来处理这些数据。
（4）内存带宽：当CPU要将更多的数据存放到CPU的缓存中来的话，内存的带宽就成了瓶颈。

4.4 数据库的参数优化

MySQL常用的有两种存储引擎，分别是MyISAM和InnoDB，每种存储引擎的参数比较多，以下列举出了主要影响数据库性能的参数。
（1）公共参数默认值
• max_connections=151：同时处理的最大连接数，推荐设置最大连接数为上限连接数的80%左右。
• sort_buffer_size=2M：查询排序时的缓冲区大小，只对ORDER BY和GROUP BY起作用，可增大此值为16M。
• open_files_limit=1024：打开文件数的上限，如果show global status like ‘open_files’查看的值等于或者大于open_files_limit值时，程序会无法连接数据库或是卡死。
（2）MyISAM参数默认值
• key_buffer_size=16M：索引缓存区大小，一般设置物理内存的30%~40%。
• read_buffer_size=128K：读操作缓冲区大小，推荐设置16M或32M。
• query_cache_type=ON：打开查询缓存功能。
• query_cache_limit=1M：查询缓存的上限，只有1M以下查询结果才会被缓存，以免结果数据较大把缓存池覆盖。
• query_cache_size=16M：查看缓存区大小，用于缓存SELECT的查询结果，下一次有同样SELECT查询时将直接从缓存池返回结果，可适当成倍增加此值。
（3）InnoDB参数默认值
• innodb_buffer_pool_size=128M：索引和数据缓冲区大小，一般设置物理内存的60%~70%。
• innodb_buffer_pool_instances=1：缓冲池实例个数，推荐设置4个或8个。
• innodb_flush_log_at_trx_commit=1：关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1s左右事务数据；1代表每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低；2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效率明显。
• innodb_file_per_table=OFF：默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。
• innodb_log_buffer_size=8M：日志缓冲区大小，由于日志最长每秒刷新一次，所以一般不用超过16M。

4.5 系统内核的优化

大多数MySQL都部署在Linux系统上，所以操作系统的一些参数也会影响到MySQL的性能，以下参数的设置可以对Linux内核进行适当优化。
（1）net.ipv4.tcp_fin_timeout=30：TIME_WAIT超时事件，默认是60s。
（2）net.ipv4.tcp_tw_reuse=1：1表示开启复用，允许TIME_WAIT socket重新用于新的TCP连接；0则表示关闭。
（3）net.ipv4.tcp_tw_recycle=1：1表示开启TIME_WAIT socket快速回收；0则表示关闭。
（4）net.ipv4.tcp_max_tw_buckets=4096：系统保持TIME_WAIT socket的最大数量，如果超出这个数，系统将随机清除一些TIME_WAIT并打印警告信息。
（5）net.ipv4.tcp_max_syn_backlog=4096：进入SYN队列的最大长度，加大队列长度可容纳更多的等待连接。
在Linux系统中，如果进程打开的文件句柄数量超过系统默认值1024，就会提示“too many files open”信息，所以要调整打开文件句柄限制。具体的配置如下：

vi /etc/security/limits.conf # 加入以下配置，*代表所有用户，也可以指定用户，重启系统生效
# * soft nofile 65535
# * hard nofile 65535

ulimit -SHn 65535   # 立刻生效

4.6 硬件配置

（1）硬件配置应加大物理内存，提高文件系统性能。Linux内核会从内存中分配缓冲区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。总之，物理内存越大，分配缓存区越大，缓存的数据越多。
（2）采用SSD固态硬盘代替SAS硬盘，将RAID（Redundant Arrays of Independent Disks，磁盘阵列）级别调整为RAID1+0，相对于RAID1和RAID5它有更好的读写性能（IOPS，Input/Output Operations Per Second，每秒读写操作的次数），毕竟数据库的压力主要来自磁盘I/O方面。

4.7 SQL语句的优化

执行缓慢的SQL语句大约能消耗数据库70%-90%的CPU资源，而SQL语句独立于程序设计逻辑，相对于对程序源代码的优化，对SQL语句的优化在时间成本和风险上的代价都很低。在实际使用SQL语句的过程中，有以下几个需要注意的原则：
（1）避免使用子查询（针对MySQL5.5及以下版本）。

# 在MySQL5.5版本中，下面的语句执行会先查外表T1再匹配内表T2，而不是先查内表
# 因此，当外表的数据很大时，查询的速度就会非常慢
SELECT * FROM T1 WHERE T1.ID in (SELECT ID FROM T2 WHERE NAME = 'test1');

# 在MySQL5.6版本中，采用JOIN关联方式对其进行了优化，上面的语句会自动转换如下
# 需要注意的是，该优化只对SELECT有效，对UPDATE或DELETE子查询无效
SELECT T1.* FROM T1 JOIN T2 ON T1.ID = T2.ID WHERE T2.NAME = 'test1';

（2）避免函数索引。

# 就算在字段D上建立了索引，下面的SQL会使用全表扫描，因为MySQL不支持函数索引
SELECT * FROM T WHERE YEAR(D) >= 2018;

# 改为下面的SQL就能使用索引了
SELECT * FROM T WHERE D >= '2018-01-01'

（3）用IN来替换OR。

# 低效率查询
SELECT * FORM T WHERE ID = 10 OR ID = 15 OR ID = 20;

# 高效率查询
SELECT * FORM T WHERE ID IN (10, 15, 20);

（4）在LIKE中双百分号无法使用到索引。

# 下面的SQL无法使用索引，MySQL5.7及以上版本才支持全文索引
SELECT * FROM T WHERE name LIKE '%mi%';

# 下面的SQL可以使用索引（前缀匹配原则）
SELECT * FROM T WHERE name LIKE 'mi%';

（5）读取适当的记录，LIMIT M,N。

1 2	SELECT * FROM T WHERE 1 = 1; SELECT * FROM T WHERE 1 = 1 LIMIT 10;

（6）避免数据类型不一致。

# 假设实际中出现了这样的SQL语句
SELECT * FROM T WHERE ID='123';

# 实际的ID是数值类型，所以WHERE条件中应该使用数值123，而不是'123'
SELECT * FROM T WHERE ID=123;

（7）分组统计可以禁止排序。

# 假设实际中出现了这样的SQL语句
SELECT SID, COUNT(SCORE) FROM TB_SCORE GROUP BY SID;

# MySQL在默认情况下会对GROUP BY的字段进行排序，可以指定ORDER BY NULL来禁止排序
SELECT SID, COUNT(SCORE) FROM TB_SCORE GROUP BY SID ORDER BY NULL;

（8）避免随机取记录。

# 假设实际中出现了这样的SQL语句
SELECT * FROM T1 WHERE 1 = 1 ORDER BY RAND() LIMIT 4;

# 由于MySQL不支持函数索引，上面的SQL会使用全表扫描，修改为下面的SQL就能触发索引了
# CEIL()函数返回大于或等于数字的最小整数值
SELECT * FROM T1 WHERE ID >= CEIL(RAND()*1000) LIMIT 4;

（9）禁止不必要的ORDER BY排序操作。

# 假设实际中出现了这样的SQL语句
SELECT COUNT(1) FROM T1 JOIN T2 ON T1.ID = T2.ID WHERE 1 = 1 ORDER BY T1.ID DESC;

# 由于是计数操作，没有必要进行排序，所以应该去掉ORDER BY
SELECT COUNT(1) FROM T1 JOIN T2 ON T1.ID = T2.ID;

（10）尽量使用批量INSERT。

# 实际中插入大量数据时，可能会使用如下的多次插入数据
INSERT INTO TB_PERSON (id, name) VALUES (1, 'test1');
INSERT INTO TB_PERSON (id, name) VALUES (2, 'test2');
INSERT INTO TB_PERSON (id, name) VALUES (3, 'test3');

# 可以修改为一次性的批量插入数据
INSERT INTO TB_PERSON (id, name) VALUES (1, 'test1'), (2, 'test2'), (3, 'test3');

5. 如何对SQL语句进行跟踪（trace）？

MySQL5.6.3提供了对SQL语句的跟踪功能，通过trace文件可以进一步了解优化器是如何选择某个执行计划的。在使用时需要先打开设置，然后执行一次SQL，最后查看information_schema.optimizer_trace表的内容。注意，该表为临时表，只能在当前会话进行查询，每次查询返回的都是最近一次执行的SQL语句。设置时相关的参数如下：

mysql> show variables like '%trace%';
+------------------------------+----------------------------------------------------------------------------+
| Variable_name                | Value                                                                      |
+------------------------------+----------------------------------------------------------------------------+
| optimizer_trace              | enabled=off,one_line=off                                                   |
| optimizer_trace_features     | greedy_search=on,range_optimizer=on,dynamic_range=on,repeated_subselect=on |
| optimizer_trace_limit        | 1                                                                          |
| optimizer_trace_max_mem_size | 16384                                                                      |
| optimizer_trace_offset       | -1                                                                         |
+------------------------------+----------------------------------------------------------------------------+
5 rows in set (0.00 sec)

mysql>

下面是打开设置的命令：

SET optimizer_trace='enabled=on';   # 打开设置
SET optimizer_trace_max_mem_size=1000000;   # 设置最大内存，也可以不设置
SET end_markers_in_json=ON;     # 增加JSON格式注释，默认是OFF
SET optimizer_trace_limit=1;    # 设置跟踪信息存储的个数

6. MySQL中的隐式类型转换

当对两个不同类型的值进行比较时，为了使得这些数值可比较（也称为类型的兼容性），MySQL会做一些隐式类型转化（Implicit type Conversion）。比如：

# 和计算
mysql> SELECT 1 + '2' as ans;
+-----+
| ans |
+-----+
|   3 |
+-----+
1 row in set (0.00 sec)

# 字符串拼接
mysql> SELECT CONCAT(2, 'test');
+-------------------+
| CONCAT(2, 'test') |
+-------------------+
| 2test             |
+-------------------+
1 row in set (0.00 sec)

mysql>

从上面的结果中可以判断出，第一条SQL将字符串“1”转换为了数字1，第二条SQL将数字2转换为了字符串“2”。MySQL也提供了CAST()函数将数值转换为字符串，当使用CONCAT()函数的时候，也可能会出现隐式转换，因为它希望参数是字符串形式，故传递的不是字符串的话，就会发生隐式类型转换。示例如下：

# 语法：CAST (expression AS data_type)
mysql> SELECT 1.23, CAST(1.23 AS CHAR), CONCAT(1.23);
+------+--------------------+--------------+
| 1.23 | CAST(1.23 AS CHAR) | CONCAT(1.23) |
+------+--------------------+--------------+
| 1.23 | 1.23               | 1.23         |
+------+--------------------+--------------+
1 row in set (0.00 sec)

mysql>

隐式转换的规则有以下几条：
（1）当两个参数至少有一个是NULL时，比较的结果也是NULL。若使用<=>对两个NULL作比较时会返回1。这两种情况都不需要做类型转换。
（2）当两个参数都是字符串时，会按照字符串比较，不做类型转换。
（3）当两个参数都是整数时，按照整数来比较，不做类型转换。
（4）当十六进制的值和非数字做比较时，会被当作二进制串。
（5）当有一个参数是TIMESTAMP或DATETIME，并且另一个参数是常量时，常量会被转换成TIMESTAMP。
（6）的那个有一个参数是DECIMAL时，如果另一个参数是DECIMAL或者整数，那么会将整数转换为DECIMAL后进行比较；如果另一个参数是浮点数，那么会把DECIMAL转换为浮点数后进行比较。
（7）所有其他情况下，两个参数都会别转换为浮点数再进行比较。示例如下：

mysql> SELECT 11 + '11', 11 + 'ab', '11' + 'ab', 'aa' + 'bb', 11 + '0.01ab';
+-----------+-----------+-------------+-------------+---------------+
| 11 + '11' | 11 + 'ab' | '11' + 'ab' | 'aa' + 'bb' | 11 + '0.01ab' |
+-----------+-----------+-------------+-------------+---------------+
|        22 |        11 |          11 |           0 |         11.01 |
+-----------+-----------+-------------+-------------+---------------+
1 row in set, 5 warnings (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+--------------------------------------------+
| Level   | Code | Message                                    |
+---------+------+--------------------------------------------+
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ab'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'ab'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'aa'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: 'bb'     |
| Warning | 1292 | Truncated incorrect DOUBLE value: '0.01ab' |
+---------+------+--------------------------------------------+
5 rows in set (0.00 sec)

mysql> SELECT '11a'=11, '11.0'=11, '11.0'='11', NULL=1;
+----------+-----------+-------------+--------+
| '11a'=11 | '11.0'=11 | '11.0'='11' | NULL=1 |
+----------+-----------+-------------+--------+
|        1 |         1 |           0 |   NULL |
+----------+-----------+-------------+--------+
1 row in set, 1 warning (0.00 sec)

mysql>

注意，如果再SQL的WHERE条件中，列名为字符串，而传入参数的值为数值类型，则MySQL不会使用索引。

7. 常见的SQL Hint（提示）有哪些？

MySQL中Hint功能种类很多，下面主要介绍常用的一些功能。

关键词	简介	示例
USE INDEX	提供希望MySQL去参考的索引列表	SELECT * FROM TB_NAME USE INDEX (FIELD1) …
FORCE INDEX	强制索引，只使用指定列上的索引，而不使用其他字段上的索引	SELECT * FROM TB_NAME FORCE INDEX (FIELD1) …
IGNORE INDEX	忽略索引，可以忽略一个或多个指定的索引	SELECT * FROM TB_NAME IGNORE INDEX (FIELD1,FIELD2) …
SQL_NO_CACHE	关闭查询缓冲，有一些SQL语句需要实时地查询数据，或者并不经常使用（可能一天就执行一两次），那样就需要把缓冲关了，即每次都会重新执行这条SQL	SELECT SQL_NO_CACHE field1,field2 FROM TB_NAME;
SQL_CACHE	强制查询缓冲，如果在my.cnf配置文件中将query_cache_type设置为2，那么只有在使用了SQL_CACHE后才能使用查询缓冲	SELECT SQL_CACHE * FROM TB_NAME;
HIGH_PRIORITY	优先操作，可以使用在SELECT和INSERT操作中，让MySQL知道该操作优先执行	SELECT HIGH_PRIORITY * FROM TB_NAME;
LOW_PRIORITY	滞后操作，可以使用在INSERT、UPDATE、DELETE和SELECT操作中，让MySQL知道该操作滞后执行	UPDATE LOW_PRIORITY TB_NAME SET field1=’test’ …
INSERT DELAYED	延时插入，客户端提交数据给MySQL后，MySQL会返回OK状态给客户端，但数据还未插入表中，而是被存储在内存里面等待排队。当MySQL有空余时，再把数据插入。这样做的好处是，来自许多客户端的插入被集中到一起，并被编写入一个块，这比执行许多独立的插入要快很多。坏处是不能返回自动递增的ID，以及系统崩溃时，数据可能会丢失	INSERT DELAYED INTO TB_NAME SET field1=’test’ …
STRAIGHT_JOIN	强制连接顺序，通过STRAIGHT_JOIN强迫MySQL按照TABLE1、TABLE2的顺序连接表，主要用在你认为自己设定的顺序比MySQL推荐的顺序效率高的情况下	SELECT T1.field1,T2.field2 FROM TABLE1 STRAIGHT_JOIN TABLE2 WHERE …
SQL_BUFFER_RESULT	强制使用临时表，当查询结果集中的数据比较多时，可以通过该选项强制将结果集放到临时表中，这样就可以很快释放MySQL的表锁，并且可以长时间为客户端提供大记录集	SELECT SQL_BUFFER_RESULT * FROM TB_NAME WHERE …
SQL_BIG_RESULT/ SQL_SMALL_RESULT	分组使用临时表，一般用于分组或DISTINCT关键字，这个选项通知MySQL，如果有必要就将查询结果放到临时表中，甚至在临时表中进行排序。SQL_SMALL_RESULT和SQL_BIG_RESULT差不多，都很少使用	SELECT SQL_BIG_RESULT field1, COUNT(*) FROM TB_NAME GROUP BY field1;

8. 如何查看SQL的执行频率？

MySQL客户端连接成功后，可以通过SHOW [SESSION|GLOBAL]命令查询服务器的状态信息，也可以在操作系统上使用mysqladmin extended-status命令获取这些信息。可以通过查询表的方式来来查询状态变量的值，MySQL5.6查询information_schema.global_status表或information_schema.session_status表；MySQL5.7查询performance_schema.global_status表或performance_schema.session_status表。查看SQL的执行频率的常用命令如下表所示：

命令	含义
show status like ‘uptime’;	查询当前MySQL本次启动后的运行统计时间（单位：秒）
show status like ‘com_select’;	com_select表示本次启动后执行的SELECT语句的次数，查询1次累加1，执行错误的SQL也会加1。同理，com_insert、com_update和com_delete分别表示INSERT、UPDATE和DELETE语句的执行次数。
show status like ‘Thread_%’;	MySQL服务器的线程信息。threads_cached表示线程缓存内的线程数量，threads_connected表示当前打开的连接数量，threads_created表示创建用来处理连接的线程数量，threads_running表示激活的（非睡眠状态）线程数量。
show status like ‘connections’;	查看试图连接到MySQL（不管是否连接成功）的连接数。
show status like ‘table_locks_immediate’;	查看立即获得的表的锁的次数。
show status like ‘table_locks_waited’;	查看不能立即获得的表的锁的次数。如果该值较高大，说明性能有问题，那么应该首先优化SQL，然后拆分表或使用复制。
show status like ‘show_launch_threads’;	查看创建时间超过show_launch_time秒的线程数量。
show status like ‘show_queries’;	查看慢查询（查询时间超过long_query_time秒）的次数，如果慢查询很多，那么可以通过慢查询日志或者show processlist检查慢查询语句。
show status like ‘Max_used_connections’;	查看已经使用的连接数，如果该值过大，说明单个服务器可能不堪重负了。一般的，使用的连接数应该为最大连接数的85%左右。

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

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

mysql> show status like 'Thread_%';
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Threads_cached    | 1     |
| Threads_connected | 1     |
| Threads_created   | 2     |
| Threads_running   | 1     |
+-------------------+-------+
4 rows in set (0.00 sec)

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

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

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

mysql> show status like 'show_launch_threads';
Empty set (0.01 sec)

mysql> show status like 'show_queries';
Empty set (0.00 sec)

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

mysql>

9. 如何定位执行效率低的SQL语句？

MySQL中可以通过以下2种方法来定位执行效率低的SQL语句。
（1）通过慢查询日志定位。可以通过慢查询日志定位那些已经执行完毕的SQL语句。
（2）使用SHOW PROCESSLIST来查询。慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题。此时，可以使用SHOW PROCESSLIST命令查看当前MySQL正在进行的线程，包括线程的状态、是否锁表等，可以实时查看SQL的执行情况，同时对一些锁表操作进行优化。
找到执行效率低的SQL语句后，就可以通过SHOW PROFILE FOR QUERY N;、EXPLAIN或trace等方法来优化这些SQL语句。

10 如何对MySQL的大表进行优化？

当MySQL单表记录过大时，数据库的CRUD（C即Create表示增加，R即Retrieve表示读取查询，U即Update表示更新，D即Delete表示删除）性能会明显下降，一些常用的优化措施如下：
（1）限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。例如，当用户在查询历史订单的时候，可以控制在一个月的范围内。
（2）读写分离：经典的数据库拆分方案，主库负责写，从库负责读。
（3）缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存。
（4）垂直分区：根据数据库里面的表的相关性进行拆分。例如，用户表中既有用户的登录信息又有用户的基本信息，此时就可以将用户表拆分为两个单独的表，甚至可以放到单独的库做分库。简单来说，垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。
• 优点：可以使得行数据变小，在查询时减少读取的block次数，减少I/O的次数。此外，还简化了表的结构，易于维护。
• 缺点：主键会出现冗余，需要管理冗余列，并会引起join操作。此外，还会让事务变得更加复杂。
（5）水平分区：保持数据表结构不变，通过某种策略存储数据分片（将数据表行进行拆分）。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。例如，如果用户信息表的行数超过200万行时，就会变得很慢，此时可以将用户信息拆分成多个用户信息表，从而避免单一表数据量过大对性能造成影响。
• 优点：能够支持非常大的数据量存储，引用程序需要的改动也很少。
• 缺点：分片事务难以解决，跨节点join性能较差，逻辑复杂。

...

00:00

数据库基本理论知识总结——MySQL优化篇