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个SQLmysqldumpslow -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=8000net.ipv4.tcp_tw_reuse=1net.ipv4.tcp_tw_recycle=1net.ipv4.tcp_fin_timeout=10

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

soft nofile 65335hard 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. 数据库随机读写效率很高