MySQL优化

来源： https://www.imooc.com/video/3688

数据库优化目的

• 避免出现页面错误
• 增加DB稳定性
• 提高网站整体性能

DB优化方向

数据准备

MySQL慢查询日志开启方式和存储格式

如何发现有问题的SQL 答：使用慢查询日志或EXPLAIN关键字进行语句分析

mysql> show variables like "%query_log%";
+---------------------+----------------------------------+
| Variable_name       | Value                            |
+---------------------+----------------------------------+
| slow_query_log      | ON                               |
| slow_query_log_file | D:/MySQL_data/slow_query_log.txt |
+---------------------+----------------------------------+
2 rows in set (0.00 sec)
 
mysql> show variables like "long_query_time";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 3.000000 |
+-----------------+----------+
1 row in set (0.00 sec)

慢查询日志分析工具 —— mysqldumpslow

慢查询日志分析工具 —— pt-query-digest

安装连接：

问题SQL分析

SQL以及索引优化 - EXPLAIN

COUNT()和MAX()优化

• MAX()

mysql> use sakila;
Database changed
mysql> select max(payment_date) from payment;
+---------------------+
| max(payment_date)   |
+---------------------+
| 2006-02-14 15:16:03 |
+---------------------+
1 row in set (0.12 sec)
 
mysql> explain select max(payment_date) from payment;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | payment | ALL  | NULL          | NULL | NULL    | NULL | 16451 |       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.03 sec)
 
mysql> create index idx_payment_date on payment(payment_date);
Query OK, 0 rows affected (0.40 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> explain select max(payment_date) from payment;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

• COUNT()

COUNT(*)和COUNT(1)都会将 null 统计在内

 
mysql> select * from tmp;
+------+
| id   |
+------+
| NULL |
|    2 |
|    3 |
|    0 |
+------+
4 rows in set (0.00 sec)
 
mysql> select count(*) from tmp;
+----------+
| count(*) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)
 
mysql> select count(1) from tmp;
+----------+
| count(1) |
+----------+
|        4 |
+----------+
1 row in set (0.00 sec)

使用COUNT的正确案例
eg: 查出2006年电影数量

SELECT COUNT(release_year='2006' OR NULL) 
FROM film;

子查询优化

group by查询优化

limit查询优化

mysql> EXPLAIN select film_id, description from film order by title limit 50;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL |  949 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
1 row in set (0.00 sec)

• 优化1： 使用有索引的列或主键进行ORDER BY操作

mysql> EXPLAIN select film_id, description from film order by film_id limit 50;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 2       | NULL |   50 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
1 row in set (0.04 sec)

• 优化2： 记录上次返回的主键，在下一次查询时使用主键过滤

mysql> EXPLAIN select film_id, description from film where film_id > 55 and film_id <= 60 order by film_id limit 1, 5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | range | PRIMARY       | PRIMARY | 2       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
1 row in set (0.05 sec)
 

避免了数据量大时扫描过多的记录（保证索引有序）

建立合适的索引

• 如何判断指定字段的离散程度

mysql> select count(distinct customer_id), count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
|                         599 |                        2 |
+-----------------------------+--------------------------+
1 row in set (0.01 sec)
customer_id的离散程度 高于 staff_id

索引优化SQL

• 如何查找重复以及冗余索引？
  
  

数据库结构优化

数据库表范式优化`

反范式优化

数据库表的垂直拆分

数据库表的水平拆分

数据库系统配置优化

MySQL配置文件优化