MySQL 性能优化

2020-02-15 本文已影响0人 ssochi

MYSQL优化.png

注

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

前言

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

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

查询优化

慢查询日志

如何开启慢查询：

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能分析出更多的统计信息

哪些慢查询需要引起关注

查询次数多且用时长
IO大的SQL
没有使用索引的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。尽量优化！

索引优化

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

合适的数据类型

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

三大范式

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

反范式

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

垂直拆分

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

水平拆分

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