1 常见问题

1. 阻塞
2. 慢查问

2 能够从以下几个方面动手

1. 服务器硬件优化
2. 系统配置优化
3. 数据库构造优化
4. SQL 和索引

通常优化 3 和 4

3 慢查问日志

show variables like 'slow_query_log';
# 开启慢查问日志
set global slow_query_log=on;
# 查问慢查问日志文件寄存的地位
show variables like 'slow_query_log_file';
# 设置慢查问日志文件寄存的地位
set global slow_query_log_file= '/home/mysql/sql_log/mysql-slow.log'
# 设置是否将没有应用索引的 SQL 记录到慢查问日志中
set global log_queries_not_using_indexes=on;
# 设置是否将查问事件超过 0 秒的 SQL 记录到慢查问日志中，通产设置 0.01 秒
set global long_query_time=0;

4 实操

4.1 默认不开启慢查问日志

4.2 慢查问日志存储的地位

查问日志，这里我是 Docker 容器部署 MySQL，映射在服务器上 /data/mysql 目录下

4.3 日志格局

第一行：执行 SQL 的主机信息

第二行：SQL 的执行信息

第三行：SQL 的执行事件

第四行：SQL 的内容

5 慢查问日志剖析工具

5.1 mysqldumpslow（官网工具）

5.1.1 参数解释

-s 是 order 的程序
al 均匀锁定工夫
ar 均匀返回记录时间
at 均匀查问工夫（默认）c 计数
l 锁定工夫
r 返回记录
t 查问工夫
-t 是 top n 的意思，即为返回后面多少条的数据
-g 后边能够写一个正则匹配模式，大小写不敏感的

5.1.2 根本应用

# 失去返回记录集最多的 10 个 SQL。mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log
# 失去拜访次数最多的 10 个 SQL
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log
# 失去依照工夫排序的前 10 条外面含有左连贯的查问语句。mysqldumpslow -s t -t 10 -g“left join”/database/mysql/mysql06_slow.log
# 另外倡议在应用这些命令时联合 | 和 more 应用，否则有可能呈现刷屏的状况。mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

5.2 pt-query-digest

5.2.1 疾速装置

wget https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm && yum localinstall -y  percona-toolkit-2.2.16-1.noarch.rpm

5.2.2 参数解释

pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table  当应用 --review 参数把剖析后果输入到表中时，如果没有表就主动创立。--create-history-table  当应用 --history 参数把剖析后果输入到表中时，如果没有表就主动创立。--filter  对输出的慢查问按指定的字符串进行匹配过滤后再进行剖析
--limit    限度输入后果百分比或数量，默认值是 20, 行将最慢的 20 条语句输入，如果是 50% 则按总响应工夫占比从大到小排序，输入到总和达到 50% 地位截止。--host  mysql 服务器地址
--user  mysql 用户名
--password  mysql 用户明码
--history 将剖析后果保留到表中，剖析后果比拟具体，下次再应用 --history 时，如果存在雷同的语句，且查问所在的工夫区间和历史表中的不同，则会记录到数据表中，能够通过查问同一 CHECKSUM 来比拟某类型查问的历史变动。--review 将剖析后果保留到表中，这个剖析只是对查问条件进行参数化，一个类型的查问一条记录，比较简单。当下次应用 --review 时，如果存在雷同的语句剖析，就不会记录到数据表中。--output 剖析后果输入类型，值能够是 report(规范剖析报告)、slowlog(Mysql slow log)、json、json-anon，个别应用 report，以便于浏览。--since 从什么工夫开始剖析，值为字符串，能够是指定的某个”yyyy-mm-dd [hh:mm:ss]”格局的工夫点，也能够是简略的一个工夫值：s(秒)、h(小时)、m(分钟)、d(天)，如 12h 就示意从 12 小时前开始统计。--until 截止工夫，配合—since 能够剖析一段时间内的慢查问。

5.2.3 根本应用

# 输入到文件
pt-query-digest slow-log > slow_log.report
# 输入到数据库 --create-reviewtable 意思是慢查问日志输入到某一张表中
pt-query-digest slow.log -review h=127.0.0.1,D=test,p=root,P=3306,u=root.t=query_review --create-reviewtable --review-history t= hostname_slow

6 SQL 优化

6.1 须要优化的 SQL 特色

1. 查问次数多且每次查问占用工夫长的 SQL
2. IO 大的 SQL（SQL 中扫描行数越多，IO 越大）
3. 未命中索引的 SQL

6.2 应用 explain 查问 SQL 的执行打算

6.2.1 解释阐明

table 显示这一行数据时对于哪张表的
type 这是重要的列，显示连贯应用了那种类型。从最好到最差的连贯类型为 const,eq_reg,ref,range,index,ALL。const 常见于主键 / 惟一索引查找，eq_reg 常见于主键的范畴查找，ref 常见于连贯查问,range 常见于索引的范畴查找，index 常见于索引的扫描，ALL 常见于表扫描
possible_keys 显示可能利用在这张表中的索引。如果为空，没有可能的索引。key 理论应用的索引。如果为 NULL, 则没有应用索引。key_len 应用的索引长度。在不损失精确性的状况下，长度越短越好
ref 显示索引的那一列被应用了，如果可的话，是一个常数
rows MySQL 认为必须查看的用来返回申请数据的行数

6.3 SQL 优化

1. 在常常查问的字段上适当加索引
2. 防止子查问，可优化为连贯查问，留神是否存在一对多关系，可能会呈现数据反复

6.4 如何抉择正当的列建设索引

1. 在 where 从句，group by 从句，order by 从句，on 从句中呈现的列
2. 索引字段越小越好
3. 离散度大的列放在联结索引的后面

例如：

select * from payment where staff_id = 2 and customer_id = 584;

因为 customer_id 的离散度更大，所以应该应用 index(customer_id,staff_id)

6.4.1 如何判断列的离散水平

select count(distinct customer_id),count(distinct staff_id) from payment

列的惟一值越高，离散水平越大，可选择性越高。

6.4.2 索引的保护和优化

反复及冗余索引，反复索引是指雷同的列以雷同的程序建设的同类型的索引，如上面 parmary key 和ID 列上的索引就是反复索引

create table test(id int not null primart key),name vachar(10) not null,title varchar(50) not null,unique(id) ) engine=innodb

应用 pt-duplicate-key-checker 工具查看反复及冗余索引

pt-duplicate-key-checker -uroot -p'' -h 127.0.0.1

7 数据库及表构造优化

7.1 抉择适合的数据类型

1. 应用能够存下你的数据的最小的数据类型
2. 应用简略数据类型，int 要比 varchar 类型在 MySQL 解决上简略
3. 尽可的应用 not null 定义字段
4. 尽量少用 text 类型，非用不可时最好思考分表

例如

应用 bigint 来存储 IP 地址，利用 INET_ATON(),INET__NTOA() 两个函数进行转换

insert into sessions(ipaddress) values (INET_ATON('127.0.0.1'));
select INET__NTOA('127.0.0.1') from sessions;

7.2 范式化和反范式化

7.2.1 范式化

1. 第一范式，强调原子性，要求属性具备原子性，不可再合成
2. 第二范式，强调主键，要求记录有惟一标识，即实体的唯一性，级不存在局部依赖
3. 第三范式，强调外键，要求任何字段不能由其余字段派生进去，要求字段没有冗余，即不存在依赖传递

7.2.2 反范式化

为了查问效率的思考，把本来合乎第三范式的表适当的减少冗余，以达到优化查问效率的目标，反范式化是一种以控件来换工夫的操作。

7.3 数据库构造的优化

7.3.1 表的垂直拆分

把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题，通常垂直拆分能够按以下准则进行：

1. 把不罕用的字段独自寄存在一个表中
2. 把大字段独立存在在一个表中
3. 把常常一起应用的字段放在一起

7.3.2 表的程度拆分

为了解决单表的数据量大的问题，程度拆分的表每一个表的构造都是完全一致的

8 系统配置优化

数据库是基于操作系统的，目前大多数 MySQL 都是装置在 Linux 上，所以对于操作系统的一些参数配置也会影响 MySQL 的性能，上面列出罕用的系统配置

8.1 网络配置方面

批改 /etc/sysctl.coonf 文件

# 减少 tcp 反对的队列数
net.ip4.tcp_max_syn_backlog=65535
# 缩小断开连接时，资源回收
net.ipv4.tcp_max_tw_buckets=8000
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=10

关上文件数的限度，能够应用 ulimit - a 查看目录的各位限度，能够批改 /etc/security/limits.conf 文件，减少以下内容以批改关上文件数量的限度

soft nofile 65335
hard nofile 65535

除此之外最好在 MySQL 服务器上敞开 iptables,selinux 等防火墙软件

8.2 MySQL 配置文件

8.2.1 配置文件门路

Linux：/etc/my.cnf

8.2.2 参数

innodb_buffer_pool_size：十分重要的参数，用于配置 innodb 的缓冲池如果数据库中只有 innodb 表，则举荐配置量为总内存的 75%

innodb_buffer_pool_instances：MySQL5.5 中新增的参数，能够管制缓存池的个数，默认状况下只有一个缓存池

innodb_log_buffer_size：innodb log 缓存的大小，因为日志最长每秒钟就会刷新所以个别不必太大

innodb_flush_log_at_trx_commit：要害参数，对 innodb 的 IO 效率影响最大，默认值为 1，能够取 0，1，2 三个值，个别倡议设置为 2，但如果数据安全性要求比拟高则应用默认值 1

innodb_read_io_threads/innodb_write_io_threads：决定 innodb 读写 IO 的过程数，默认为 4

innodb_file_per_table：要害参数，管制 innodb 没一个表应用独立的表空间，默认为 OFF，也就是所有表都会建设在共享表空间中

innodb_stats_on_metadata：决定 MySQL 在什么状况下会刷新 innodb 表的统计信息

8.2.3 第三方配置工具

Percon Configuration Wizard

9 服务器硬件的优化

9.1 CPU

抉择适合的 CPU，单个频率更快的 CPU

1. MySQL 一些工作只能应用到单核
2. MySQL 对 CPU 的核数反对并不是越快越好，MySQL5.5 应用的服务器不要超过 32 核

9.2 disk IO 优化

罕用 RAID 级别简介

RAID0：也成为条带，就是把多个磁盘连接成一个硬盘应用，这个级别 IO 最好

RAID1：也称为镜像，要求至多有两个磁盘，每组磁盘存储的数据雷同

RAID5：也是把多个（起码 3 个）硬盘合并成一个逻辑盘应用，数据读写时会建设奇偶校验信息，并且奇偶校验信息和绝对应的数据别离存储于不同的硬盘上。当 RAID5 的一个磁盘数据产生损坏后，就剩下的数据和相应的奇偶校验信息去复原被损坏的数据。

RAID1+0：就是 RAID1 和 RAID0 的联合，同时具备两个级别的优缺点。个别倡议数据库应用这个级别。

SNA 和 NAT 是否适宜数据库

1. 罕用于高可用的解决方案
2. 程序读写效率很高，然而随机读写不如人意
3. 数据库随机读写效率很高