MYSQL SQL优化思路和方法
一、优化SQL的一般步骤
1.1 了解各种SQL执行频率
Mysql客户端成功连接之后，通过show [global | session]命令可以提供服务器状态信息，也可以在操作系统上使用mysqladmin extended-status命令来获得这些消息。
mysql> show status like '%Com_%';
+-----------------------------+-------+
| Variable_name | Value |
+-----------------------------+-------+
| Com_admin_commands | 0 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_instance | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 0 |
| Com_alter_tablespace | 0 |
| Com_alter_user | 0 |
| Com_analyze | 0 |
| Com_begin | 0 |
| Com_binlog | 0 |
| Com_call_procedure | 0 |
| Com_change_db | 0 |
| Com_change_master | 0 |
| Com_change_repl_filter | 0 |
| Com_check | 0 |
| Com_checksum | 0 |
| Com_commit | 0 |
| Com_create_db | 0 |
| Com_create_event | 0 |
| Com_create_function | 0 |
| Com_create_index | 0 |
| Com_create_procedure | 0 |
| Com_create_server | 0 |
| Com_create_table | 0 |
| Com_create_trigger | 0 |
| Com_create_udf | 0 |
| Com_create_user | 0 |
| Com_create_view | 0 |
| Com_dealloc_sql | 0 |
| Com_delete | 0 |
| Com_delete_multi | 0 |
| Com_do | 0 |
| Com_drop_db | 0 |
| Com_drop_event | 0 |
| Com_drop_function | 0 |
| Com_drop_index | 0 |
| Com_drop_procedure | 0 |
| Com_drop_server | 0 |
| Com_drop_table | 0 |
| Com_drop_trigger | 0 |
| Com_drop_user | 0 |
| Com_drop_view | 0 |
| Com_empty_query | 0 |
| Com_execute_sql | 0 |
| Com_explain_other | 0 |
| Com_flush | 0 |
| Com_get_diagnostics | 0 |
| Com_grant | 0 |
| Com_ha_close | 0 |
| Com_ha_open | 0 |
| Com_ha_read | 0 |
| Com_help | 0 |
| Com_insert | 0 |
| Com_insert_select | 0 |
| Com_install_plugin | 0 |
| Com_kill | 0 |
| Com_load | 0 |
| Com_lock_tables | 0 |
| Com_optimize | 0 |
| Com_preload_keys | 0 |
| Com_prepare_sql | 0 |
| Com_purge | 0 |
| Com_purge_before_date | 0 |
| Com_release_savepoint | 0 |
| Com_rename_table | 0 |
| Com_rename_user | 0 |
| Com_repair | 0 |
| Com_replace | 0 |
| Com_replace_select | 0 |
| Com_reset | 0 |
| Com_resignal | 0 |
| Com_revoke | 0 |
| Com_revoke_all | 0 |
| Com_rollback | 0 |
| Com_rollback_to_savepoint | 0 |
| Com_savepoint | 0 |
| Com_select | 0 |
| Com_set_option | 0 |
| Com_signal | 0 |
| Com_show_binlog_events | 0 |
| Com_show_binlogs | 0 |
| Com_show_charsets | 0 |
| Com_show_collations | 0 |
| Com_show_create_db | 0 |
| Com_show_create_event | 0 |
| Com_show_create_func | 0 |
| Com_show_create_proc | 0 |
| Com_show_create_table | 0 |
| Com_show_create_trigger | 0 |
| Com_show_databases | 0 |
| Com_show_engine_logs | 0 |
| Com_show_engine_mutex | 0 |
| Com_show_engine_status | 0 |
| Com_show_events | 0 |
| Com_show_errors | 0 |
| Com_show_fields | 0 |
| Com_show_function_code | 0 |
| Com_show_function_status | 0 |
| Com_show_grants | 0 |
| Com_show_keys | 0 |
| Com_show_master_status | 0 |
| Com_show_open_tables | 0 |
| Com_show_plugins | 0 |
| Com_show_privileges | 0 |
| Com_show_procedure_code | 0 |
| Com_show_procedure_status | 0 |
| Com_show_processlist | 0 |
| Com_show_profile | 0 |
| Com_show_profiles | 0 |
| Com_show_relaylog_events | 0 |
| Com_show_slave_hosts | 0 |
| Com_show_slave_status | 0 |
| Com_show_status | 3 |
| Com_show_storage_engines | 0 |
| Com_show_table_status | 0 |
| Com_show_tables | 0 |
| Com_show_triggers | 0 |
| Com_show_variables | 0 |
| Com_show_warnings | 0 |
| Com_show_create_user | 0 |
| Com_shutdown | 0 |
| Com_slave_start | 0 |
| Com_slave_stop | 0 |
| Com_group_replication_start | 0 |
| Com_group_replication_stop | 0 |
| Com_stmt_execute | 0 |
| Com_stmt_close | 0 |
| Com_stmt_fetch | 0 |
| Com_stmt_prepare | 0 |
| Com_stmt_reset | 0 |
| Com_stmt_send_long_data | 0 |
| Com_truncate | 0 |
| Com_uninstall_plugin | 0 |
| Com_unlock_tables | 0 |
| Com_update | 0 |
| Com_update_multi | 0 |
| Com_xa_commit | 0 |
| Com_xa_end | 0 |
| Com_xa_prepare | 0 |
| Com_xa_recover | 0 |
| Com_xa_rollback | 0 |
| Com_xa_start | 0 |
| Com_stmt_reprepare | 0 |
| Compression | OFF |
| Flush_commands | 1 |
| Handler_commit | 0 |
+-----------------------------+-------+
151 rows in set (0.00 sec)
针对的是所有存储引擎的表：
Com_select：执行select操作的次数，1次查询累计加1；
Com_insert：执行insert操作的次数，批量insert操作，只累加1；
Com_update：执行update操作的次数；
Com_delete：执行delete操作的次数。
针对Innodb存储引擎的表：
innodb_rows_read：select查询返回的行数；
innodb_row_inserted：执行insert操作插入的行数；
innodb_rows_updated：执行update操作更新的行数；
innodb_rows_deleted：执行deleted操作删除的行数。
针对事务型的应用：
Com_commit：事务提交的次数
Com_rollback：事务回滚的次数
了解数据库基本情况
Connections：视图连接Mysql服务器的次数；
Uptime：服务器工作时间；
Slow_queries：慢查询的次数。
1.2 定位执行效率较低SQL
可以通过如下两种方式定位效率较低的SQL语句：
- show processlist查看当前运行的线程，包括线程状态，是否锁表等；
- 通过慢查询日志定位低效率SQL。
1.3 Explain分析低效SQL执行计划
mysql> EXPLAIN
-> SELECT
-> c.name AS CustomerName,
-> COUNT(o.id) AS NumberOfOrders,
-> SUM(o.amount) AS TotalSpent,
-> MAX(o.order_date) AS LastOrderDate
-> FROM
-> customers c
-> JOIN
-> orders o ON c.id = o.customer_id
-> WHERE
-> o.order_date >= DATE_SUB(CURDATE(), INTERVAL 1 YEAR)
-> GROUP BY
-> c.id, c.name
-> HAVING
-> SUM(o.amount) > 1000
-> ORDER BY
-> TotalSpent DESC;
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+-------+----------+----------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+-------+----------+----------------------------------------------+
| 1 | SIMPLE | o | NULL | ALL | customer_id | NULL | NULL | NULL | 10294 | 33.33 | Using where; Using temporary; Using filesort |
| 1 | SIMPLE | c | NULL | eq_ref | PRIMARY | PRIMARY | 4 | sbtest.o.customer_id | 1 | 100.00 | NULL |
+----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+-------+----------+----------------------------------------------+
2 rows in set, 1 warning (0.00 sec)
select_type：表示select的类型，常见的取值有：
SIMPLE：简单表，不使用表连接或者子查询
PRIMARY：主查询，即外层的查询
UNION：UNION中的第二个或者后面的查询语句
SUBQUERY：子查询中的第一个SELECTtable：输出结果集的表
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：对于前面的每一行，都通过全表扫描来获得数据possible_keys：表示查询时，可能使用的索引
key：表示实际使用的索引
key_len：索引字段的长度
rows：扫描行的数量
extra：执行情况的说明和描述
1.4 确定问题并采取相应的优化措施
经过上述步骤，基本就可以确认问题出现的原因，此时可以根据情况采取相应的措施，并进行优化以提高效率。
二、索引问题
2.1 索引的存储分类
myisam存储引擎的表数据和索引是分开存储的，各自是一个独立的文件，innodb存储引擎的表数据和索引是存储在同一个表空间里边，但可以有多个文件组成。
mysql中索引的存储类型目前只有2种，BTREE和HASH，具体和表的存储引擎相关。
myisam和innodb存储引擎都支持BTREE索引，memory/heap存储引擎可以支持hash和btree索引。
mysql目前还不支持函数索引，但是可以对列的前面某一部分进行索引（前缀索引），该特性可以大大减少索引文件的大小。
2.2 如何使用索引
索引可以快速找出在某个列中有一特定值的行，对相关列使用索引是提高select操作性能的最佳途径。
查询要使用索引最主要的条件是查询条件中需要使用索引关键字，如果是多列索引，那么只有查询条件使用了多列关键字最左边的前缀时，才可以使用索引，否则不能使用索引。
2.2.1 使用索引
- 创建的多列索引，只要查询条件中使用到了最左边的列，索引一般就会被使用。如果只选取其中的某个非最左列做条件查询，那么索引则不会用到。
mysql> create index idx_t1 on orders(order_date,amount);
mysql> show index from orders;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| orders | 0 | PRIMARY | 1 | id | A | 10294 | NULL | NULL | | BTREE | | |
| orders | 1 | idx_cid | 1 | customer_id | A | 6305 | NULL | NULL | | BTREE | | |
| orders | 1 | idx_t1 | 1 | order_date | A | 730 | NULL | NULL | | BTREE | | |
| orders | 1 | idx_t1 | 2 | amount | A | 10003 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)
mysql> explain select * from orders where order_date = '2024-5-10';
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| 1 | SIMPLE | orders | NULL | ref | idx_t1 | idx_t1 | 3 | const | 13 | 100.00 | NULL |
+----+-------------+--------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from orders where amount = 1851.69;
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
| 1 | SIMPLE | orders | NULL | ALL | NULL | NULL | NULL | NULL | 10294 | 10.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+-------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 对于使用like的查询，后边如果是常量并且只有%号不在第一个字符的时候，索引才可能会被使用
mysql> create index idx_name on customers(name);
mysql> explain select * from customers where name like '%1';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 9809 | 11.11 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from customers where name like 'Customer_101%';
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | customers | NULL | ALL | idx_name | NULL | NULL | NULL | 9809 | 49.99 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
- 如果对于大的文本进行搜索，使用全文索引而不是用like ‘%…%’
- 如果列名是索引，使用column_name is null将使用索引
2.2.2 存在索引但不使用索引
- 如果mysql认为使用索引比全表扫描更慢，则不使用索引。
- 如果使用memory/heap表并且where条件中不使用“=”进行索引列，那么不会用到索引。
- 用or分隔开的条件，如果or前的条件列有索引，而后边的列没有索引，那么涉及的索引都不会被用到。
- 如果不是索引列的第一部分
- 如果like是%开始
- 如果列类型是字符串，那么一定记得在where条件中把字符常量用引号引起来，否则即使该列有索引也不会用到
2.2.3 查看索引使用情况
如果索引正在工作，handler_read_key的值将会很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
handler_read_rnd_next的值很高，说明查询运行抵消，应该创建索引补救，这个值的含义是在数据文件中读下一行的请求数。如果正在进行大量的表扫描，Handler_read_rnd_ndext的值较高，则通常说明表索引不正确或写入的查询没有利用索引。
mysql> show status like 'handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 4 |
| Handler_read_key | 4 |
| Handler_read_last | 0 |
| Handler_read_next | 10003 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 10030 |
+-----------------------+-------+
2.3 简单实用的优化方法
2.3.1 定期分析表和检查表
如下语句可以用于分析和存储表的关键字分布，分析的结果将可以使系统得到准确的统计信息，使用SQL能够生成正常的执行计划。
在分析期间，使用一个读取锁定对表进行锁定，这时对myisam、bdb和innodb表有作用，但是对于myisam表，这条语句与myisamchk -a相当。
analyze [local | no_write_to_binlog] table table_name1 [, table_name2]...
mysql> analyze table orders;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| sbtest.orders | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.01 sec)
mysql> analyze no_write_to_binlog table orders;
+---------------+---------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+---------+----------+----------+
| sbtest.orders | analyze | status | OK |
+---------------+---------+----------+----------+
1 row in set (0.02 sec)
·· 检查一个表有没有问题
mysql> check table orders;
+---------------+-------+----------+----------+
| Table | Op | Msg_type | Msg_text |
+---------------+-------+----------+----------+
| sbtest.orders | check | status | OK |
+---------------+-------+----------+----------+
1 row in set (0.03 sec)
检查表的作用是检查一个或多个表是否有错误，check table 对myisam表和innodb表有作用，对于myisam表，关键字统计数据被更新。
mysql> check table orders;
±--------------±------±---------±---------+
| Table | Op | Msg_type | Msg_text |
±--------------±------±---------±---------+
| sbtest.orders | check | status | OK |
±--------------±------±---------±---------+
1 row in set (0.03 sec)
2.3.2 定期优化表
如果已经删除了表的一大部分，或者如果已经对含有可变长度的表（含varchar、blob或text列的表）进行了很多更改，则应使用optimize table命令进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但optimize table只对myisam、bob和innodb表起作用。
mysql> optimize table orders;
+---------------+----------+----------+-------------------------------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+---------------+----------+----------+-------------------------------------------------------------------+
| sbtest.orders | optimize | note | Table does not support optimize, doing recreate + analyze instead |
| sbtest.orders | optimize | status | OK |
+---------------+----------+----------+-------------------------------------------------------------------+
2 rows in set (0.41 sec)
2.4 常用SQL优化
2.4.1 大批量插入数据
当用load命令导入数据的时候，可以用如下方式提高导入速度。
- myisam：
disable keys和enable keys用来打开或者关闭myisam表非唯一索引的更新。
在导入大量的数据到一个非空的myisam表时，可以通过设置这两个命令，可以提高导入的效率。
在导入大量数数据到一个空的myisam表时，默认就是先导入数据然后才创建索引，此时就不需要设置。
alter table table_name disable keys;
loading the data...
alter table table_name enable keys;
- innodb：
innodb类型的表是按照主键顺序进行保存的，所以将导入的数据按照主键的顺序进行排列；
导入数据之前执行set unique_checks=0，关闭唯一性校验，导入结束之后在执行set unique_checks=1恢复唯一性校验；
应用使用自动提交的方式，建议在导入前执行set autocommit=0关闭自动提交，导入结束后再执行set autocommit=1打开自动提交。
2.4.2 优化INSERT语句
- 如果同时从同一客户端插入很多行，尽量使用多个值表的insert语句，这种方式将大大减少客户端与数据库之间的连接、关闭等消耗；
- 如果从不同客户端插入很多行，能通过使用insert delayed语句得到更高的速度；delayed的含义是让insert语句马上执行；其实数据都被放在内存的队列中，并没有真正写入磁盘，比每条语句分别插入要快的多；low_priority刚好相反，在所有其他用户对表的读写完后才进行插入；
- 将索引文件和数据文件分在不同的磁盘上存放（利用建表的选项）；
- 如果进行批量插入，可以增加bulk_insert_buffer_size变量值的方法来提高速度，但是这只对myisam表使用；
- 当从一个文本文件装载一个表时，使用load data infile，这通常比insert语句快20倍。
2.4.3 优化GROUP BY语句
默认情况下，mysql对所有group by col1,col2…的字段进行排序，这与在查询中指定order by col1,col2…类似。如果查询包括group by，但是想要避免排序结果的消耗，可以指定order by null禁止排序；
mysql> explain select address from customers group by address;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 9809 | 100.00 | Using temporary; Using filesort |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select address from customers group by address order by null;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
| 1 | SIMPLE | customers | NULL | ALL | NULL | NULL | NULL | NULL | 9809 | 100.00 | Using temporary |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-----------------+
1 row in set, 1 warning (0.00 sec)
2.4.4 优化ORDER BY语句
在某些情况中，mysql使用一个索引来满足order by子句，而不需要额外的排序，where条件和order by使用相同的索引。并且order by的顺序和索引顺序相同，并且order by的字段都是升序或者都是降序。
2.4.5 优化嵌套查询
mysql 4.1开始支持sql子查询，这个技术可以使用 SELECT 语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性地完成很多逻辑上需要多个步骤才能完成的 SQL 操作，同时也可以避免事务或者表锁死，并且写起来也很容易。
但是，有些情况下，子查询可以被更有效率的连接（JOIN）替代。
2.4.6 优化OR条件
对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；
如果没有索引，则应该考虑增加索引。
2.4.6 使用SQL提示
SQL 提示（SQL HINT）是优化数据库的一个重要手段，简单来说就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
下面是一个使用 SQL 提示的例子：
SELECT SQL_BUFFER_RESULTS * FROM...
这个语句将强制 MySQL 生成一个临时结果集。只要临时结果集生成后，所有表上的锁定均被释放。
这能在遇到表锁定问题时或要花很长时间将结果传给客户端时有所帮助，因为可以尽快释放锁资源。
- use index
在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。- ignore_index
如果用户只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。同样是上面的例子，这次来看一下查询过程忽略索引 ind_sales2_id 的情况：- force_index
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。
例如，当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引。
但是，当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。
三、小结
SQL 优化问题是数据库性能优化最基础也是最重要的一个问题，实践表明很多数据库性能问题都是由不合适的 SQL 语句造成。本章通过实例描述了 SQL 优化的一般过程，从定位一个有性能问题的 SQL 语句到分析产生性能问题的原因，最后到采取什么措施优化 SQL 语句的性能。另外还介绍了优化 SQL 语句经常需要考虑的几个方面，比如索引、表分析、排序等。
