mysql 第16章 性能优化
一、SQL 执行分析
1.1、SQL 执行时间分析
通过找到执行时间长的 SQL 语句，可以直观的发现数据层的效率问题。
①、通过 show processlist 来查看系统的执行情况
②、通过 profiling 来进行查看
这个命令是查看 SQL 的执行时间，能很直观的看出快慢。
A 查看 profiling 是否开启
0 代表还是关闭着分析功能
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 0 |
+-------------+
B 打开工具
mysql> set profiling=1;
Query OK, 0 rows affected (0.00 sec)
mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
| 1 |
+-------------+
1 row in set (0.00 sec)
C 查看 SQL 的执行时间
mysql> show profiles;
+----------+------------+--------------------+
| Query_ID | Duration | Query |
+----------+------------+--------------------+
| 1 | 0.00032000 | select @@profiling |
+----------+------------+--------------------+
D 查看 SQL 执行耗时详细信息
show profile for query Query_ID
mysql> show profile for query 1;
+----------------------+----------+
| Status | Duration |
+----------------------+----------+
| starting | 0.000073 |
| checking permissions | 0.000031 | ---检查是否在缓存中
| Opening tables | 0.000207 | ---打开表
| init | 0.000067 | ---初始化
| System lock | 0.000040 | ---锁系统
| optimizing | 0.000005 | ---优化查询
| statistics | 0.000021 |
| preparing | 0.000015 | ---准备
| executing | 0.000003 | ---执行
| Sending data | 0.000993 |
| end | 0.000006 |
| query end | 0.000007 |
| closing tables | 0.000011 |
| freeing items | 0.000169 |
| cleaning up | 0.000089 |
+----------------------+----------+
以上具体的信息都是从 INFORMATION_SCHEMA.PROFILING 这张表中取得的。这张表记录了所有的各个步骤的执行时间及相关信息。语法：
select * from INFORMATION_SCHEMA.PROFILING where query_id = Query_ID;
③、慢查询日志
MySQL 的慢查询日志，顾名思义就是把执行时间超过设定值（默认为10s）的 SQL 记录到日志中。这项功能需要手动开启，但是开启后会造成一定的性能损耗。
A 查看慢日志是否开启
默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启。
mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | F:\Program Files\phpStudy\PHPTutorial\MySQL\data\DESKTOP-G2ERVHA-slow.log |
+---------------------+---------------------------------------------------------------------------+
mysql> set global slow_query_log=1;
Query OK, 0 rows affected (0.00 sec)
mysql> show variables like "%slow_query_log%";
+---------------------+---------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+---------------------------------------------------------------------------+
| slow_query_log | ON |
| slow_query_log_file | F:\Program Files\phpStudy\PHPTutorial\MySQL\data\DESKTOP-G2ERVHA-slow.log |
+---------------------+---------------------------------------------------------------------------+
B 设置超时时间
设置语法：set global long_query_time=4
查看语法：show variables like "long_query_time"
注意：修改后，需要重新连接或新开一个会话才能看到修改值。
永久生效，修改 my.cnf
slow_query_log=1
long_query_time=10
slow_query_log_file=/path/mysql_slow.log
C 其他参数
log_output
参数是指定日志的存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。
log-queries-not-using-indexes
未使用索引的查询也被记录到慢查询日志中（可选项）。如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。
log_slow_admin_statements
表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
④、分析工具 mysqldumpslow
MySQL 提供了慢日志分析工具 mysqldumpslow。
-s 表示按照何种方式排序；
c: 访问计数
l: 锁定时间
r: 返回记录
t: 查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
-t 是top n的意思，即为返回前面多少条的数据；
-g 后边可以写一个正则匹配模式，大小写不敏感的；
命令示例
得到返回记录集最多的 10 个 SQL：mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
得到访问次数最多的 10 个 SQL：mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g “left join” /database/mysql/mysql06_slow.log
另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现刷屏的情况：mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more
1.2、SQL 执行情况分析
使用 explain 分析 SQL 执行情况。
①、创建 user 表
user表
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`account` varchar(255) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
`address` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
②、插入记录
INSERT INTO user(account, name, address) VALUES ("user1", "user1", "user1");
③、explain 使用
explain select * from user;
语法：
EXPLAIN tbl_name
EXPLAIN [EXTENDED] SELECT select_options
④、explain 返回参数说明
A、id：这是SELECT的查询序列号
B、select_type：select语句的类型，主要包括
SIMPLE:简单SELECT(不使用UNION或子查询)
PRIMARY:最外面的SELECT
UNION:UNION中的第二个或后面的SELECT语句
DEPENDENT UNION:UNION中的第二个或后面的SELECT语句,取决于外面的查询
UNION RESULT:UNION 的结果
SUBQUERY:子查询中的第一个SELECT
DEPENDENT SUBQUERY:子查询中的第一个SELECT,取决于外面的查询
DERIVED:导出表的SELECT(FROM子句的子查询)
C、table：查询的表
D、type：联接类型。这个参数很重要。下面给出比较常用的几种连接类型
const:常数查找，如:主键，唯一索引，会很快，因为它们只读取一次!
eq_ref:对于每个来自于前面的表的行组合,从该表中读取一行。这可能是最好的联接类型,除了const类型。
ref:基于连接的查找
range:基于索引的范围查找
index:基于索引的扫描。该联接类型与ALL相同,除了只有索引树被扫描。这通常比ALL快,因为索引文件通常比数据文件小。
ALL:对于每个来自于先前的表的行组合,进行完整的表扫描。
E、possible_keys：指出MySQL能使用哪个索引在该表中找到行
F、key：显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL
G、key_len：使用索引的长度
H、ref：显示使用哪个列或常数与key一起从表中选择行。
I、rows：扫描的行
J、Extra：该列包含MySQL解决查询的详细信息,这个参数也很重要，主要情况有：
Distinct:MySQL发现第1个匹配行后,停止为当前的行组合搜索更多的行。
Not exists:MySQL能够对查询进行LEFT JOIN优化,发现1个匹配LEFT JOIN标准的行后,不再为前面的的行组合在该表内检查更多的行。
range checked for each record (index map: #):MySQL没有发现好的可以使用的索引,但发现如果来自前面的表的列值已知,可能部分索引可以使用。
Using filesort:文件排序，需要利用额外的空间。
Using index:从只使用索引树中的信息而不需要进一步搜索读取实际的行来检索表中的列信息。
Using temporary:需要用临时表来容纳结果。
Using where:WHERE 子句用于限制哪一个行匹配下一个表或发送到客户。
Using sort_union(...), Using union(...), Using intersect(...):这些函数说明如何为index_merge联接类型合并索引扫描。
Using index for group-by:类似于访问表的Using index方式,Using index for group-by表示MySQL发现了一个索引,
可以用来查 询GROUP BY或DISTINCT查询的所有列,而不要额外搜索硬盘访问实际的表。
K、filtered
除此以外，explain 的extended 扩展能够在原本explain的基础上额外的提供一些查询优化的信息。
explain extended select * from user;
二、mysql 的性能优化方法
2.1、创建表时的性能优化
①、永远为每张表设置一个 ID
每张表都应该设置一个 ID 字段为主键，该主键应为 INT 或 UNSIGNED 类型，并设置上自动增加的 AUTO_INCREMENT 标志。因为使用 VARCHAR 类型的主键，会使得性能下降。
这里，只有一个情况是例外，那就是 “关联表” 的 “外键”，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做 “外键”。比如：有一个 “学生表” 有学生的 ID，有一个 “课程表” 有课程 ID，那么，“成绩表” 就是 “关联表” 了，其关联了学生表和课程表，在成绩表中，学生 ID 和课程 ID 叫 “外键” 其共同组成主键。
②、为搜索字段建索引
将 where 中用的比较频繁的字段建立索引，联合索引
③、使用 ENUM 而不是 VARCHAR
ENUM 类型是非常快和紧凑的。在实际上，其保存的是 TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。
如果你有一个字段，比如 “国家”，你知道这些字段的取值是有限而且固定的，那么，你应该使用 ENUM 而不是 VARCHAR。
ENUM 是 MySQL 数据库特有的字段类型，使用后会影响迁移到其它数据库。所以，如果以后又改数据库的情况，一定要慎用。
④、尽可能的使用 NOT NULL
应该总是让你的字段保持 NOT NULL，因为这样节省空间（NULL 也是需要空间的）。
⑤、把IP地址存成 UNSIGNED INT
如果使用整形来存放 IP 而不是 VARCHAR(15) 字段，节省了很多的空间（需要写一个 IP 转换的函数）。
2.2、设计表时的性能优化
①、选择正确的存储引擎
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要 update 一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。
InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。
②、固定长度的表会更快
表中没有如下类型的字段： VARCHAR，TEXT，BLOB。只要你包括了其中一个这些字段，那么这个表就不是“固定长度静态表”了，这样，MySQL 引擎会用另一种方法来处理。
固定长度的表会提高性能，因为 MySQL 搜寻得会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。
并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。
使用“垂直分割”技术（见下一条），你可以分割你的表成为两个一个是定长的，一个则是不定长的。
③、垂直分割
“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的。（以前，在银行做过项目，见过一张表有100多个字段，很恐怖）
示例一：在Users表中有一个字段是家庭地址，这个字段是可选字段，相比起，而且你在数据库操作的时候除了个人信息外，你并不需要经常读取或是改写这个字段。那么，为什么不把他放到另外一张表中呢？ 这样会让你的表有更好的性能，大家想想是不是，大量的时候，我对于用户表来说，只有用户ID，用户名，口令，用户角色等会被经常使用。小一点的表总是会有好的性能。
示例二： 你有一个叫 “last_login” 的字段，它会在每次用户登录时被更新。但是，每次更新时会导致该表的查询缓存被清空。所以，你可以把这个字段放到另一个表中，这样就不会影响你对用户ID，用户名，用户角色的不停地读取了，因为查询缓存会帮你增加很多性能。
另外，你需要注意的是，这些被分出去的字段所形成的表，你不会经常性地去Join他们，不然的话，这样的性能会比不分割时还要差，而且，会是极数级的下降。
2.3、优化 SQL 语句
①、使用查询缓存
A、查看是否开启缓存
mysql> select @@query_cache_type;
+--------------------+
| @@query_cache_type |
+--------------------+
| ON |
+--------------------+
1 row in set (0.00 sec)
开启缓存，修改 my.cnf，在末尾加入，重启MySQL生效：
query_cache_type = 1
query_cache_size = 600000
B、为查询缓存优化你的查询
// 查询缓存不开启
r = mysql_query("SELECT username FROM user WHERE signup_date >= CURDATE()");
// 开启查询缓存
today = date("Y-m-d");
r = mysql_query("SELECT username FROM user WHERE signup_date >= %s" % today);
上面两条 SQL 语句的差别就是 CURDATE()，MySQL 的查询缓存对这个函数不起作用。所以，像 NOW() 和 RAND() 或是其它的诸如此类的 SQL 函数都不会开启查询缓存，因为这些函数的返回是会不定的易变的。所以，你所需要的就是用一个变量来代替 MySQL 的函数，从而开启缓存。
②、当只要一行数据时使用 LIMIT 1
在这种情况下，加上 LIMIT 1 可以增加性能。这样一样，MySQL 数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。
③、在 JOIN 表的时候使用相当类型的例，并将其索引
如果有很多 JOIN 的操作，JOIN 的字段应该加索引，同时保证这些字段的类型一致。
④、避免 SELECT *
从数据库里读出越多的数据，那么查询就会变得越慢。所以，应该养成需要什么就取什么的好的习惯。
⑤、拆分大的 DELETE 或 INSERT 语句
如果你需要在一个在线的网站上去执行一个大量的 DELETE 或 INSERT 查询，你需要非常小心，要避免你的操作让你的整个网站停止相应。因为这两个操作是会锁表的，表一锁住了，别的操作都进不来了。
执行这种大量的 DELETE 和 INSERT，可以分成几部分执行，没执行一部分就暂停一下再执行。
2.4、其它
①、EXPLAIN 你的 SELECT 查询
使用 EXPLAIN 关键字可以让你知道 MySQL 是如何处理你的 SQL 语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。
查看 rows 列可以让我们找到潜在的性能问题。
②、从 PROCEDURE ANALYSE() 取得建议
PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并会给你一些有用的建议（只是建议）。只有表中有实际的数据，这些建议才会变得有用，因为要做一些大的决定是需要有数据作为基础的。
mysql> select * from charac procedure analyse()
*************************** 1. row ***************************
Field_name: world.charac.charac
Min_value: A
Max_value: E
Min_length: 1
Max_length: 1
Empties_or_zeros: 0
Nulls: 0
Avg_value_or_avg_length: 1.0000
Std: NULL
Optimal_fieldtype: ENUM("A","B","C","D","E") NOT NULL
1 row in set (0.00 sec)
③、使用连接池和 ORM
避免使用长连接
④、保证单表数据不超过 200W，适时分割表
⑤、修改 my.cnf 里面的各项参数
比如最大连接数，查询缓存等。
根据你的服务器内存来最大化调节那些配置参数。
