优化在数据库中是一个非常大的概念，不仅仅是涉及语句层面的优化，可能还涉及数据库参数的优化、硬件优化等各方各面。在这些优化之中，最基础、日常接触最多的要数SQL语句的优化，本文将着重于介绍SQL语句的优化，包括如何查看分析执行计划、如何去分析一个SQL的优化点等。
1 环境配置
服务器：VMware Virtual Platform
CPU：Intel(R) Core(TM) i5-8250U CPU @ 1.60GHz
内存：1GB
MySQL：mysql-5.7.23
操作系统：CentOS Linux release 7.5.1804 (Core)
2 SQL优化四要素
在分析一个SQL的可优化点时，通常会去查几点的信息用以辅助分析SQL，这些需要查询的信息，在这里统称为SQL优化的四个要素。这四个要素分别是：
表结构
表数据量
索引统计信息
执行计划
通过分析四个要素，可以获取到SQL语句中涉及表的结构、数据信息以及优化器计划执行SQL语句时的方案信息。接下来将一一介绍这四个要素，更进一步认识它们。
表结构
如何查看
mysql>SHOW CREATE TABLE a\G
*************************** 1. row ***************************
Table: a
Create Table: CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`seller_id` bigint(20) DEFAULT NULL,
`seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`gmt_create` varchar(30) COLLATE utf8mb4_bin DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=65488 DEFAULT CHARSET=utf8mb4 COLLATE =utf8mb4_bin
1 row in set (0.11 sec)
要素中的可用信息
SHOW CREATE TABLE table_name语句会打印出建表的DDL语句，一般在SQL语句优化时，我们需要了解到优化语句涉及表的表结构，包括但不限于数据列的数据类型、字符集、属性及表已有的索引等。
表数据量
如何查看
# 方法1：使用COUNT函数查看
# 优缺点：COUNT能得到表数据量瞬时的精准值，但由于是一个查询语句，在表数据量过大的时候查询需要时间并且有可能阻塞其他会话的DDL语句
mysql>SELECT COUNT(1) FROM a;
+----------+
| COUNT(1) |
+----------+
| 16385 |
+----------+
1 row in set (0.31 sec)
# 方法2：查看表状态，表状态中的Rows值即该表的表数据量。
# 优缺点：在InnoDB等支持事务的存储引擎中，该值是估算值并不准确，尤其是在表经过大量DML操作之后，该值与精确值可能会有较大差距，如果要获取精确值请使用COUNT函数进行计算；在MyISAM等不支持事务的存储引擎中，该值是精确的。
mysql>SHOW TABLE STATUS LIKE 'a'\G
*************************** 1. row ***************************
Name: a
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 16108
Avg_row_length: 98
Data_length: 1589248
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: 65488
Create_time: 2019-11-29 16:40:36
Update_time: 2019-11-29 16:40:37
Check_time: NULL
Collation: utf8mb4_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
# 方法3：查看information.tables中的table_rows列
# 优缺点：与前述的方法2一样，该值是否精确也与其存储引擎有关，支持事务的存储引擎中，该值是估算值并不准确，在不支持事务的存储引擎中，该值是精确的。
mysql>SELECT * FROM information_schema.tables WHERE table_name = 'a' AND table_schema = 'test'\G
*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: test
TABLE_NAME: a
TABLE_TYPE: BASE TABLE
ENGINE: InnoDB
VERSION: 10
ROW_FORMAT: Dynamic
TABLE_ROWS: 16524
AVG_ROW_LENGTH: 96
DATA_LENGTH: 1589248
MAX_DATA_LENGTH: 0
INDEX_LENGTH: 0
DATA_FREE: 2097152
AUTO_INCREMENT: 65488
CREATE_TIME: 2019-11-30 02:34:12
UPDATE_TIME: NULL
CHECK_TIME: NULL
TABLE_COLLATION: utf8mb4_bin
CHECKSUM: NULL
CREATE_OPTIONS:
TABLE_COMMENT:
1 row in set (0.00 sec)
要素中的可用信息
在SQL优化中，统计表的数据量有几方面的作用。
首先是，如果SQL语句是有JOIN操作的，可以对比几张表的数据量大小，确认表与表之间的驱动顺序；
其次，在分析执行计划时与执行计划的扫描行数和索引使用情况进行比对，确认扫描行数是否正常、索引的使用是否合理；
最后，在实际执行的时候，评估执行时间与表数据量是否成正比，优化是否合理有效。
索引统计信息
如何查看
mysql>SHOW INDEX FROM a\G
*************************** 1. row ***************************
Table: a
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: id
Collation: A
Cardinality: 16524
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
要素中的可用信息
在SHOW INDEX FROM table_name中列出了单表的索引信息，除了索引是否唯一、索引名称、索引类型等基础信息之外，在优化中我们最关注的值只有一个：Cardinality。
Cardinality表示该索引中数据唯一值的估算行数，用来度量索引的选择性，Cardinality的值越大越接近表数据量（Cardinality/n_row_in_table尽可能的接近1），它的选择性就越高，在使用索引的时候需要扫描的行数就越少，性能也就越高。如果Cardinality的值非常小，就应当考虑重新触发Cardinality的统计，以获取当下的估算值，再结合业务情况具体考量该索引是否有存在的必要性。
对于Cardinality的统计，又是一个相对复杂的过程，在这里不做过多的赘述，在日后的学习中可以深入了解一下Cardinality值的估算方式、更新的触发条件以及目前MySQL中针对该值统计提供的相关参数设置（SHOW VARIABLES LIKE 'innodb_stat_%'）
执行计划
如何查看
mysql>EXPLAIN SELECT a.id ,b.id FROM a JOIN b ON a.id = b.id \G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
partitions: NULL
type: index
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: NULL
rows: 16524
filtered: 100.00
Extra: Using index
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
partitions: NULL
type: eq_ref
possible_keys: PRIMARY
key: PRIMARY
key_len: 4
ref: test.a.id
rows: 1
filtered: 100.00
Extra: Using index
2 rows in set, 1 warning (0.00 sec)
要素中的可用信息
在查询语句前添加EXPLAIN关键字（也可以使用DESC关键字）可以查看SQL的执行计划。执行计划中的可用信息非常多。
