1. 优化一般步骤⚓

1.1 了解SQL的执行频率⚓

使用show [session|global] status提供服务器的状态信息，默认是session等级。

MariaDB [MYISAM_TEST]> SHOW GLOBAL STATUS LIKE "Com_%";

Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。

Com_select：执行 select 操作的次数，一次查询只累加 1。
Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update：执行 UPDATE 操作的次数。
Com_delete：执行 DELETE 操作的次数。

上面这些参数对于所有存储引擎的表操作都会进行累计。下面这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

Innodb_rows_read：select 查询返回的行数。
Innodb_rows_inserted：执行 INSERT 操作插入的行数。
Innodb_rows_updated：执行 UPDATE 操作更新的行数。
Innodb_rows_deleted：执行 DELETE 操作删除的行数。

对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况。

此外，以下几个参数便于用户了解数据库的基本情况。

Connections：试图连接 MySQL 服务器的次数。
Uptime：服务器工作时间。
Slow_queries：慢查询的次数。

1.2 定位执行效率较低的 SQL⚓

两种方法：

通过慢查询日志定位那些执行效率较低的 SQL 语句，用--log-slow-queries[=file_name]选项启动时， mysqld 写一个包含所有执行时间超过long_query_time（show variables like 'long_query_time';）秒的 SQL 语句的日志文件。
使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1.3 分析低效 SQL 的执行计划⚓

定位到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC 命令获取 MySQL 如何执行 SELECT 语句的信息。

EXPLAIN [explain_type] SELECT select_options

explain_type:
    EXTENDED
  | PARTITIONS

Or:

-- 同 DESC tbl_name
EXPLAIN tbl_name

MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM CHILD \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: CHILD
         type: index
possible_keys: NULL
          key: PARENT_ID
      key_len: 3
          ref: NULL
         rows: 5
        Extra: Using index

select_type：SIMPLE （简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
type：连接类型。性能由好到差的连接类型为 system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用 primary key 或者 unique index）、 ref （与 eq_ref 类似，区别在于不是使用 primary key 或者 unique index，而是使用普通的索引）、ref_or_null（与 ref 类似，区别在于条件中包含对 NULL 的查询）、index_merge(索引合并优化)、unique_subquery（in的后面是一个查询主键字段的子查询）、 index_subquery （与 unique_subquery 类似，区别在于 in 的后面是查询非唯一索引字段的子查询）、 range （单表中的范围查询）、index （对于前面的每一行，都通过查询索引来得到数据）、 all （对于前面的每一行都通过全表扫描来得到数据）。
key：表示实际使用的索引。
rows：扫描行的数量。
Extra：执行情况的说明和描述。

1.4 通过 SHOW PROFILE 分析 SQL⚓

语法：

-- 列出所有的进程及消耗时间
SHOW PROFILES;
-- 列出进程中的每个线程具体的耗时
SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

显示分析信息，指示在当前会话过程中执行的语句的资源使用情况。

使用示例

还可以通过INFORMATION_SCHEMA.PROFILING表获取信息。

查看是否支持：

SELECT @@HAVE_PROFILING;

打开该功能：

-- 查看是否开启
SELECT @@profiling;
-- 开启
SET profiling = 1;

Note

Sending data 表示的是MySQL线程从开始访问数据行并将结果返回给客户端的时长。

2. 索引⚓

2.1 索引的存储分类⚓

MyISAM 存储引擎的表的数据和索引是自动分开存储的，各自是独立的一个文件； InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

MySQL 中索引的存储类型：

BTREE：MyISAM、InnoDB、MEMORY、HEAP存储引擎都支持 BTREE 索引
HASH：MEMORY/HEAP 存储引擎支持 HASH 索引
RTREE：空间索引。MyISAM 特殊的索引类型
FULLTEXT：全文索引。MyISAM 特殊的索引类型；InnoDB从 5.6 版本支持。

前缀索引

2.2 使用索引⚓

查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字第一个关键字的前缀时，才可以使用索引，否则将不能使用索引。

对于创建的多列索引，只要查询的条件中用到了第一列，索引一般就会被使用
对于使用like的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会被使用

-- 使用了索引
MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM index_test WHERE NAME LIKE 'DS' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_test
         type: range
possible_keys: mutil_index
          key: mutil_index
      key_len: 31
          ref: NULL
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

-- 没有使用索引
MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM index_test WHERE NAME LIKE '%DS' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

如果对大的文本进行搜索，使用全文索引而不要使用like ‘%…%’。相关博客。
如果列名是索引，使用 column_name is null 将使用索引。

MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM index_test WHERE NAME IS NULL \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_test
         type: ref
possible_keys: mutil_index
          key: mutil_index
      key_len: 31
          ref: const
         rows: 1
        Extra: Using index condition
1 row in set (0.00 sec)

-- 虽然 DESCRIPTION 也是索引的中的关键字，但是不是第一个关键字，所以不能使用索引
MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM index_test WHERE DESCRIPTION IS NULL \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: index_test
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

仅仅对索引进行查询，当查询的列都在索引的字段中时（查询字段中的索引列要在查询条件中使用的索引中），查询的效率更高

2.3 存在索引但不使用索引⚓

如果 MySQL 估计使用索引比全表扫描更慢，则不使用索引。
如果使用 MEMORY/HEAP 表并且 where 条件中不使用=进行索引列，那么不会用到索引。heap 表只有在=的条件下才会使用索引。
用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。即有一个条件没有用到索引那么就不会使用索引。
如果不是索引列的第一列
如果 like 是以％开始
如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的

2.4 查看索引使用情况⚓

MYSQL> SHOW STATUS LIKE 'Handler_read%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Handler_read_first       | 3     |
| Handler_read_key         | 0     |
| Handler_read_last        | 0     |
| Handler_read_next        | 18    |
| Handler_read_prev        | 0     |
| Handler_read_rnd         | 0     |
| Handler_read_rnd_deleted | 0     |
| Handler_read_rnd_next    | 36    |
+--------------------------+-------+

如果索引正在工作，Handler_read_key的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，Handler_read_rnd_next的值较高，则通常说明表索引不正确或写入的查询没有利用索引。

2.5 两个简单实用的优化方法⚓

这些维护操作会造成表的锁定，不要在数据库繁忙的时候执行。

2.5.1 定期分析表和检查表⚓

ANALYZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

本语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。

如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。

支持InnoDB, NDB, MyISAM以及分区表，不支持视图。

CHECK TABLE tbl_name [, tbl_name] ... [option] ...

option = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

检查表的作用是检查一个或多个 表/视图 是否有错误。

支持InnoDB, MyISAM, ARCHIVE, CSV以及分区表，对于InnoDB有注意事项。

2.5.2 定期优化表⚓

OPTIMIZE [NO_WRITE_TO_BINLOG | LOCAL] TABLE
    tbl_name [, tbl_name] ...

如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则可以使用此语句优化。

这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费。

对于InnoDB，可以设置innodb_file_per_table参数，这样会对每个数据库的每个表生成一个独立的ibd文件，用于存储表的数据和索引。另外。在删除大量数据之后InnoDB表可以通过ALTER TABLE table_name ENGINE=Innodb但是不修改引擎的方式来回收不使用的空间。

使用时有诸多限制，详情参考MySQL 5.5手册。

2.6 常用 SQL 的优化⚓

2.6.1 大批量插入数据⚓

2.6.1.1 提高MyISAM表的导入效率⚓

当用 load 命令导入数据的时候，适当的设置可以提高导入的速度。对于MyISAM存储引擎的表，可以通过以下方式快速的导入大量的数据。

-- 关闭 MyISAM 表非唯一索引的更新
ALTER TABLE tbl_name DISABLE KEYS;
loading the data
-- 打开 MyISAM 表非唯一索引的更新
ALTER TABLE tbl_name ENABLE KEYS;

在导入大量的数据到一个非空的 MyISAM 表时，通过设置这两个命令，可以提高导入的效率。对于导入大量数据到一个空的 MyISAM 表，默认就是先导入数据然后才创建索引的，所以不用进行设置。

2.6.1.2 提高InnoDB表的导入效率⚓

因为 InnoDB 类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

2.6.2 优化 INSERT 语句⚓

如果同时从同一客户端插入很多行，尽量使用多值插入语句。这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗

insert into table_name values(1,2),(1,3),(1,4)…

如果从不同客户插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。

DELAYED 的含义是让 INSERT 语句马上执行，其实数据都被放在内存的队列中，并没有真正写入磁盘，这比每条语句分别插入要快的多；LOW_PRIORITY 刚好相反，在所有其他用户对表的读写完后才进行插入；

将索引文件和数据文件分在不同的磁盘上存放（利用建表中的选项）。
如果进行批量插入，可以增加 bulk_insert_buffer_size 变量值的方法来提高速度，但是，这只能对 MyISAM 表使用。
当从一个文本文件装载一个表时，使用LOAD DATA INFILE。这通常比使用很多 INSERT 语句快 20 倍。

2.6.3 优化 GROUP BY 语句⚓

默认情况下， MySQL 对所有字段 GROUP BY col1， col2.... 进行排序。这与在查询中指定ORDER BY col1，col2... 类似。

如果查询包括 GROUP BY 但用户想要避免排序结果的消耗，则可以指定 ORDER BY NULL 禁止排序。

2.6.4 优化 ORDER BY 语句⚓

优化方式：尽量减少额外的排序，通过索引直接返回有序数据。

2.6.4.1 MySQL的两种排序方式⚓

第一种：通过有序索引顺序扫描直接返回有序数据（查询的字段是索引字段，但你仍然需要指定排序规则）。此方式在使用EXPLAIN分析时显示为Using Index，无需额外排序。

第二种：对返回数据进行排序，即filesort。排序的操作取决于MySQL对排序参数的设置和排序数据大小。

可以使用索引的情况：WHERE 条件和 ORDER BY 使用相同的索引，并且 ORDER BY 的顺序和索引顺序相同，并且 ORDER BY 的字段都是升序或者都是降序。

以下几种情况下则不使用索引：

order by 的字段混合 ASC 和 DESC
用于查询行的关键字与 ORDER BY 中所使用的不相同
对不同的关键字使用 ORDER BY。？？？

2.6.4.2 filesort优化⚓

通过比较系统变量max_length_for_sort_data的大小和query语句去除的字段总大小来判断使用哪种算法。前者大使用一次扫描算法，否则使用二次扫描算法。https://dev.mysql.com/doc/refman/5.5/en/order-by-optimization.html#order-by-filesort

适当加大系统变量max_length_for_sort_data的值，达到CPU与IO利用平衡。

适当加大sort_buffer_size排序区，尽量让排序在内存中完成。

2.6.5 优化嵌套查询⚓

SQL 的子查询。这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。有些情况下，子查询可以被更有效率的连接（JOIN）替代。

连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

-- 子查询
mysql> select * from sales2 where company_id not in ( select id from 
company2 );
-- 连接
mysql> select * from sales2 left join company2 on sales2.company_id = 
company2.id where sales2.company_id is null;

2.6.6 优化 OR 条件⚓

对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

2.6.7 优化分页查询⚓

2.6.7.1 思路一⚓

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其它列内容。例：

MariaDB [MYISAM_TEST]> explain select * from Scores order by id limit 2 offset 1;
+------+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id   | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+------+-------------+--------+------+---------------+------+---------+------+------+----------------+
|    1 | SIMPLE      | Scores | ALL  | NULL          | NULL | NULL    | NULL |    6 | Using filesort |
+------+-------------+--------+------+---------------+------+---------+------+------+----------------+

-- 优化写法
MariaDB [MYISAM_TEST]> explain select a.* from Scores a inner join (select Id from Scores order by id limit 2 offset 1) b on a.id=b.id;
+------+-------------+------------+-------+---------------+----------+---------+------+------+-------------------------------------------------+
| id   | select_type | table      | type  | possible_keys | key      | key_len | ref  | rows | Extra                                           |
+------+-------------+------------+-------+---------------+----------+---------+------+------+-------------------------------------------------+
|    1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL     | NULL    | NULL |    3 |                                                 |
|    1 | PRIMARY     | a          | ALL   | id_index      | NULL     | NULL    | NULL |    6 | Using where; Using join buffer (flat, BNL join) |
|    2 | DERIVED     | Scores     | index | NULL          | id_index | 5       | NULL |    3 | Using index                                     |
+------+-------------+------------+-------+---------------+----------+---------+------+------+-------------------------------------------------+

2.6.7.2 思路二⚓

把LIMIT查询转换成某个位置的查询。

explain select * from scores order by score limit 410,10;

select score from scores order by score desc limit 400,10;
-- 取出最后一条记录作为查询条件
explain select * from scores where score < 4000 order by socre desc limit 10;

2.6.8 使用 SQL 提示⚓

简单来说就是在 SQL 语句中加入一些人为的提示（hint）来达到优化操作的目的。

2.6.8.1 USE INDEX⚓

在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。

MariaDB [MYISAM_TEST]> EXPLAIN SELECT * FROM CHILD USE INDEX (PRIMARY) WHERE ID > 1 \G;

2.6.8.2 IGNORE INDEX⚓

如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。

2.6.8.3 FORCE INDEX⚓

为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。