性能优化之MySQL优化

第1章 数据库优化简介

数据库优化的目的

避免出现页面访问错误

• 由于数据库联接timeout产生页面5xx错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据无法提交

增加数据库的稳定性

• 很多数据库问题都是由于低效的查询引起的

优化用户体验

• 流畅页面的访问速度
• 良好的网站功能体验

数据库优化的方式

1 SQL及索引（提升性能最大，效率最高）
2 数据库表结构（根据范式设计表结构）
3 系统配置（linux对mysql打开文件数的限制等）
4 硬件（提高文件io的速度，但是成本最高）
io的优化 并不能减少阻塞 ，但sql索引没有优化好，则会产生大量的慢查询或阻塞，这是由于mysql的内部锁机制造成，所以硬件再好效果也不大
mysql对内核是有限制的

第2章 SQL语句优化

SELECT语句 - 语法顺序：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

SELECT语句 - 执行顺序：

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

2-1 数据准备

演示数据库说明：
使用MySQL提供的sakila数据库，可以通过以下URL获取这个演示数据库
http://dev.mysql.com/doc/index-other.html
sakila数据库的表结构信息可以通过以下网站查看
http://dev.mysql.com/doc/sakila/en/sakila-installation.html
数据库基于MySQL5.5版本,不同MySQL版本的优化器有一定的差别。
导入数据:
1.下载sakila数据库
http://dev.mysql.com/doc/index-other.html
解压:

2.导入
CMD 命令窗口连接MySQL $> mysql -u root -p

创建数据库结构
mysql> source /home/tom/mysql/sakila-schema.sql
将数据写入到数据库
mysql> source /home/tom/mysql/sakila-db/sakila-data.sql

Linux下


mysql> select @@version;
±----------+
| @@version |
±----------+
| 5.7.23 |
±----------+

mysql> USE sakila;
Database changed

mysql> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

2-2 MySQL慢查日志的开启方式和存储格式

SQL及索引优化
相关参数说明:

• show_query_log_file：慢查日志存储位置
• log_queries_not_use_indexes：是否把没有使用索引的查询记录到慢查询日志中
• long_query_time：超过多少秒的查询记录到慢查询日志中

使用MySQL慢查日志对有效率问题的SQL进行监控

• show variables like ‘slow_query_log’; //查看是否开启慢查日志
• set global slow_query_log_file = ‘xxx’ //设置慢查日志的文件地址
• set global log_queries_not_using_indexes=on; //是否把没有使用sql索引记录到慢查日志中
• set global long_query_time=1; //设置慢查日志的时间，查寻超过多少秒记录(单位：秒)

案例:
show variables like ‘slow_query_log’; //查看是否开启慢查日志

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   | 值是OFF,说明没有开启该功能
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

查看是否将没有建立索引的查询列入慢查询记录:
show variables like ‘%log%’;
显示的结果中有一项"log_queries_not_using_indexes"值为off, 说明没有开启 使用下面的命令开启:
set global log_queries_not_using_indexes=on; //是否把没有使用sql索引记录到慢查日志中

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

查看慢查询时间设置:
show variables like ‘long_query_time’;

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

设置慢查询时间设置:
set global long_query_time=0;
set global long_query_time=0; 为啥无效呢，再查还是原来的10
修改之后，先关闭数据库连接，再重新连接，再次查询就可以看到实际上是修改了的。
开启慢查询日志
set global slow_query_log=on;

mysql> set global slow_query_log=on;

查看慢查询日志文件位置
show variables like ‘slow%’;

mysql> show variables like 'slow%';
+---------------------+-------------------------------------------------------+
| Variable_name       | Value                                                 |
+---------------------+-------------------------------------------------------+
| slow_launch_time    | 2                                                     |
| slow_query_log      | ON                                                    |
| slow_query_log_file | D:\development\MySQL5.7\data\LAPTOP-9GHMNQJ6-slow.log | 存放位置
+---------------------+-------------------------------------------------------+

# Linux下
mysql> show variables like 'slow%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_launch_time    | 2                                   |
| slow_query_log      | ON                                  |
| slow_query_log_file | /var/lib/mysql/cjbCentos01-slow.log |
+---------------------+-------------------------------------+
3 rows in set (0.00 sec)

2-3 MySQL慢查日志分析工具之mysqldumpslow

Windows下安装使用mysqldumpslow
不建议安装使用,可能会导致某些系统问题

Windows下使用

D:\development\Strawberry\perl\bin>perl mysqldumpslow.pl -h

D:\development\Strawberry\perl\bin>perl mysqldumpslow.pl -h