mysql之优化

1、性能优化器profile
性能优化器profile可以查询执行SQL使用的资源消耗。使用之前先查看是否启动，如果没有则开启，如果要关闭就使profiling=0。

mysql> select @@profiling;
+-------------+
| @@profiling |
+-------------+
|           0 |
+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> select @@profiling ;
+-------------+
| @@profiling |
+-------------+
|           1 |
+-------------+
1 row in set, 1 warning (0.00 sec)

开启后就可以使用了show profile 只能显示最近的15条SQL语句的资源消耗

mysql> show profiles;
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00015500 | select @@profiling         |
|        2 | 0.00044775 | select * from email        |
|        3 | 0.00019650 | select id,Email from email |
+----------+------------+----------------------------+
3 rows in set, 1 warning (0.00 sec)

查看具体的资源消耗指定到行：

mysql> show profile for query 2;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000085 |
| checking permissions | 0.000007 |
| Opening tables       | 0.000019 |
| init                 | 0.000016 |
| System lock          | 0.000008 |
| optimizing           | 0.000003 |
| statistics           | 0.000030 |
| preparing            | 0.000026 |
| executing            | 0.000004 |
| Sending data         | 0.000118 |
| end                  | 0.000008 |
| query end            | 0.000010 |
| closing tables       | 0.000007 |
| freeing items        | 0.000093 |
| cleaning up          | 0.000017 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

其中query后的是根据查询出结果的行数，
查看执行操作的时间：
2、慢查询
使用mysql记录下查询超过指定时间的语句，将超过制定时间的SQL语句查询成为“慢查询”。
查看慢查询的时间定义

mysql> show variables like 'long%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set, 1 warning (0.00 sec)

设置慢查询的时间定义

mysql> set long_query_time=5;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'long%';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 5.000000 |
+-----------------+----------+
1 row in set, 1 warning (0.00 sec)

开启慢查询记录功能

mysql> show variables like 'slow%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | OFF                      |
| slow_query_log_file | DESKTOP-CD7TB77-slow.log |
+---------------------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

mysql> set global slow_query_log = ON;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow%';
+---------------------+--------------------------+
| Variable_name       | Value                    |
+---------------------+--------------------------+
| slow_launch_time    | 2                        |
| slow_query_log      | ON                       |
| slow_query_log_file | DESKTOP-CD7TB77-slow.log |
+---------------------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

3、使用explain分析MySQL
explain显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和更优化的查询语句。使用方法在select语句前加上explain。

explain select id from seat;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | seat  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.02 sec)

1、id：select查询地序列号，id值相同从上到下，id值不同从大到小。
2、select_type 表示查询中每个select语句的类型有：
simple（最简单的select）
primary（嵌套在最外层的select语句，在union中前面的select语句）
subquery:子查询中的第一个select
union（union中第二个以及后面的select语句）
derived（派生表中select语句中from子句中的select语句）
union result（从union表中获取的select）
3、table：数据是关于哪张表的
4、type：比较重要，显示查询使用了哪种类型。其中system>const>eq_ref>ref>range>index>all
system:表中只有一行记录，这是特殊的const，也可以忽略。
const：针对主键或唯一索引的等值查询扫描，最多返回一行数据。查询速度非常快，仅仅读取一次。

mysql> explain select id from seat where id=1;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | seat  | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select id from seat;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | seat  | NULL       | index | NULL          | PRIMARY | 4       | NULL |    5 |   100.00 | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

index：表示全索引扫描，和all类似，而all是全表扫描，而index仅仅扫描所有的索引，而不扫描数据。
all：全表扫描，查询性能最差之一。
eq_ref：通常出现在join查询，对于前表的每一个结果，都只能匹配到后表的一行结果，并且查询的比较操作通常都是=，查询效率较高。
ref：多出现在多表join查询，针对于非唯一或非主键索引，或者使用了最左前缀规则索引的查询。
range:只检索指定范围内的行
5、possible_keys和key
possible_key表示mysql在查询时，能够真正用到的索引。
key：mysql在当前查询时所真正使用到的索引。
6、key_len：表示查询优化器使用了索引的字节数，可以评估组合索引是否被完全使用还是只有最左部分字段使用。
7、rows：估计SQL查询到结果需要扫描的数据行数。
8、Extra分为：
using filesort：表示需要额外的排序操作，不能通过索引顺序达到排序效果，占用CPU资源消耗大。
using index：查询在索引树中就可以查找所需的数据，不用扫描表数据文件。
using temporary：查询有使用临时表，一般出现于排序，分组和多表join的情况，建议优化。
3、统计信息
表统计信息：show table 表名;
索引统计信息：show index from table;