MySQL 性能优化

MYSQL优化.png

注

本文是对于sqlercn在慕课上发表的“MySQL 性能优化”课程的学习笔记，内容并非原创。

前言

MYSQL性能优化主要包含三种方法：

1. 查询优化
2. 表结构优化
3. 系统优化
   大部分的性能问题能通过查询优化解决，其次是表结构优化，最后才是系统优化。因此查询优化也会占据大量的篇幅。

查询优化

慢查询日志

如何开启慢查询：

mysql> show variables like 'slow_query_log'
mysql> set global slow_query_log = ON;
mysql> set global slow_query_log_file='D:/download/mysql-8.0.19-winx64/mysql-8.0.19-winx64/mysql-slow.log';
mysql> set global log_queries_not_using_indexes=on;
mysql> set global long_query_time=1;   //超过1s的查询都会被记入慢查询日志

TCP Port: 3306, Named Pipe: MySQL
Time                 Id Command    Argument
# Time: 2020-02-15T08:21:23.220194Z
# User@Host: root[root] @ localhost [::1]  Id:    13
# Query_time: 0.001607  Lock_time: 0.000536 Rows_sent: 599  Rows_examined: 599
use sakila;
SET timestamp=1581754883;
select * from customer;

可以使用一些工具来分析慢查询：
1.mysqldumpslow
2.pt-quary-slow
其中mysqldumpslow是mysql自带的分析工具，可以汇总慢查询记录并进行排序

mysqldumpslow you-slow-log.log ##对you-slow-log.log进行分析

而pt-quary-slow能分析出更多的统计信息

哪些慢查询需要引起关注

1. 查询次数多且用时长
2. IO大的SQL
3. 没有使用索引的SQL

explain

mysql> explain select * from customer ;
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | customer | NULL       | ALL  | NULL          | NULL | NULL    | NULL |  599 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------+

table: 显示这行数据是关于哪张表

type： 显示连接使用了什么类型，包含：const，eq_reg，ref，range，index，all
const->当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量（where上用到唯一索引或默认索引）

select * from table where id  = 1;

eq_reg->类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件
ref->基于索引的查找

select * from table where catalog  = 1;

range->基于索引的范围查找

select * from table where catalog  > 1;

index->对于索引的扫描

select catalog   from table;

all->全表扫描

select * from table;

possible_keys:可能使用的索引
key：实际使用的索引
key_len：使用索引的长度（理论上越短越好）
ref：列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
rows：mysql认为必须检查的用来返回请求的行数
extra：
using filesort，using temporary。尽量优化！

索引优化

1. 在需要索引的地方加上索引，并保证索引能被MYSQL正常使用上
2. 在满足使用的情况下，索引要尽可能的小
3. 对于联合索引，离散度高的索引加在前，离散度低的加在后
4. 减少重复，冗余和不使用的索引

合适的数据类型

1. 最小的数据类型
2. 最简单的数据类型
3. 少用null
4. 少用text，如果要使用，将它提出到另一张附加表中

三大范式

1. 所有列不可拆分
2. 别的列必须依赖与主键而不能只依赖主键的一部分
3. 别的列必须直接依赖主键，而不能间接依赖主键

反范式

用一些冗余的数据来达到减少查询开销的目的，空间换时间

垂直拆分

根据表的字段进行拆分，比如一个表中字段很多，其中有一部分字段很少被用到，有一部分字段经常被用到，还有一部分字段是text或很大的数据结构，那么我们可以将它们根据这三种特点进行拆分

水平拆分

如果表的数据太多，则可以将数据分配到多张表中。使用hash的方法对id进行索引，同时表可以分为业务表和统计表。业务表指平时程序调用的表，对业务表采用水平拆分达到性能的提升，而对统计表，由于被用到的次数比较少，同时又会设计到表中大部分的数据，因此选择不对其进行拆分，避免使用join。
业务表和统计表同时存在，