1. 数据库优化的意图

防止呈现页面拜访错误

  • 因为数据库连接timeout产生页面5xx错误
  • 因为慢查询形成页面无法加载
  • 因为堵塞形成数据无法提交

添加数据库的稳定性

  • 许多数据库问题都是因为低效的查询引起的

优化用户体验

  • 流畅页面的拜访速度
  • 杰出的网站功用体验

1.1 数据库优化方向

  • 硬件(本钱最高，作用最低)
  • 系统装备
  • 数据库表结构
  • SQL及索引(本钱最低，作用最高)

2. SQL优化

2.1 怎么发现有问题的SQL？

运用MySQL慢查询日志对有功率问题的SQL进行监控

2.1.1 全局变量设置敞开慢查询日志

  1. 查询MySQL慢查询日志是否敞开
show VARIABLES LIKE 'slow_query_log';
  1. 设置慢查询日志的文件位置
# F:/tools/develop-tools/mysql5.7/mysql_log/mysql-slow.log为日志文件位置
set global slow_query_log_file = 'F:/tools/develop-tools/mysql5.7/mysql_log/mysql-slow.log';
  1. 设置是否对未运用索引的SQL进行记载
set global log_queries_not_using_indexes = on;
  1. 设置只需SQL履行超越n秒的就记载
# 此处设置0.001秒是为了方便测试，一般状况比这大
set global long_query_time = 0.001 ;
  1. 启用MySQL慢查询日志
set global slow_query_log = on;

2.1.2 装备文件办法设置慢查询

  • 修正装备文件my.cnf,在[mysqld]下的下方参加以下内容
[mysqld]
slow_query_log = ON
log_queries_not_using_indexes = ON;
slow_query_log_file = F:\tools\develop-tools\mysql5.7\mysql_log\mysql-slow.log
long_query_time = 1
  • 检查设置后的参数
show variables like 'slow_query%';
show variables like 'long_query__time';

2.1.3 慢查询日志包括的内容

# 履行这条句子的时刻
# Time: 2020-06-01T01:59:18.368780Z
# 履行SQL的主机信息
# User@Host: root[root] @ localhost [::1]  Id:     3
# SQL的履行信息
# Query_time: 0.006281  Lock_time: 0.000755 Rows_sent: 2  Rows_examined: 1034
use test;
# SQL履行时刻
SET timestamp=1590976758;
# SQL履行内容
SHOW VARIABLES LIKE 'slow_query%';

5款慢查询日志的比照

东西/功用 一般计算信息 高档计算信息 脚本 优势
mysqldumpslow 支撑 不支撑 perl mysql官方自带
mysqlsla 支撑 支撑 perl 功用强大，数据报表齐全，定制化能力强
mysql-explain-slow-log 支撑 不支撑 perl 无
mysql-log-filter 支撑 部分支撑 perl 不失功用的前提下，保持输出简洁
myprofl支撑 不支撑 php 十分精简
### 2.1.4 怎么通过慢查询日志发现有问题的SQL
  1. 查询次数多且每次查询占用时刻长的SQL

一般未pt-query-digest剖析的前几个查询

  1. IO大的SQL

留意pt-query-digest剖析中的Rows examine项

  1. 未命中索引的SQL

留意pt-query-digest剖析中的Rows examine和Rows Send的比照

2.2 怎么剖析SQL查询

2.2.1 运用explain查询SQL履行的方案

# sql履行句子
EXPLAIN SELECT customer_id,first_name,last_name FROM customer;

性能优化之MySQL优化

2.2.2 explain：返回各列的意义

  1. table：表名
  2. type: 显现连接运用哪种类型。例如：system(系统表，少量数据，往往不需要进行磁盘IO)、const(常量连接)、eq_ref(主键索引或非空仅有索引)、ref(非主键、非仅有索引)、range(范围扫描)、index(索引树扫描)、ALL(全表扫描)

留意：type扫描办法的快慢：system > const > eq_ref > ref > range > index > ALL

  1. possible_keys：显现或许应用在这张表中的索引。假如为空，没有或许的索引。
  2. key：实际运用的索引。假如为NULL则没有运用索引。
  3. key_len：运用的索引的长度。在不损失精确性的状况下，长度越短越好。
  4. ref：显现索引的哪一列被运用了，假如或许的话是一个常数。
  5. rows：MySQL以为必须检查的用来返回恳求数据的行数。

2.2.3 extra列需要留意的返回值

Using filesort、Using temporary：看到这个时，查询就需要优化。

2.2.4 为表的某个字段创建索引

查询payment的时分发现运用的是全表查询

# 查询句子
explain select max(payment_date) from payment;

成果如下图：

性能优化之MySQL优化
为payment_date字段添加索引

# 在payment表的payment_date字段上添加索引idx_paydate
CREATE index idx_paydate on payment(payment_date;)

作用如下图：

性能优化之MySQL优化

2.3 Count()和Max()的优化办法

需求：在一条SQL中一同查出2006年和2007年电影的数量–优化count()函数

SELECT count(release_year = '2006' or NULL) as '2006年电影数量',COUNT(release_year='2007' or NULL) as '2007年电影数量' from film;

2.3.1 count(*)和count(字段)的差异

count(字段)不包括该字段为NULL的状况，而count(*)包括。

# 查询staff表
SELECT count(*),count(picture) from staff;

成果为：

性能优化之MySQL优化

2.4 子查询的优化

一般状况下，夸耀吧子查询优化为join查询，但在优化时要留意关联键是否有一对多的关系(join会呈现重复数据,运用distinct函数进行去重); **例如：**查询sandra出演的一切影片

explain select title,release_year,LENGTH FROM film where film_id IN(select film_id from film_actor where actor_id IN(select actor_id from actor where first_name = 'sandra'));

2.5 group by查询优化

优化前SQL

# using 等价于join中的on 例如：USING(id) 等价于 on a.id = b.id
explain select actor.first_name,actor.last_name,count(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

成果如下图:发现extra中呈现Using filesort、Using temporary

性能优化之MySQL优化

优化后SQL

explain select actor.first_name,actor.last_name,c.cnt FROM actor INNER JOIN (SELECT actor_id,count(*) as cnt from film_actor GROUP BY actor_id) as c USING(actor_id);

成果如下图：extra没有呈现Using filesort、Using temporary

性能优化之MySQL优化

2.6 Limit查询优化

limit一般用于分页处理，时常随同order by从句运用，因而大多时分会运用Filesorts这样会形成大量的IO问题

# 例如:其间limit 50, 5表明从第51条数据开端查询5条数据。order by表明升序摆放(默许)，order by xxx desc 表明降序摆放
SELECT film_id,description FROM film ORDER BY title limit 50, 5;

检查该SQL的履行方案发现，运用的是文件排序(fileSort)且运用的是全表查询(type=ALL),总共扫描了1000条数据,因而当数据量大的时分会形成很大的IO问题，需要对SQL进行优化。

性能优化之MySQL优化

优化战略：

  • 运用有索引的列或主键进行Order by 操作 
    SELECT film_id,description FROM film ORDER BY film_id limit 50, 5;
    检查该SQL的履行方案发现，运用的是主键索引查询,总共扫描了55条数据且没有运用文件排序,因而优化了很大。
    性能优化之MySQL优化
    可是当咱们将约束条件改为limit 500,5会发现扫描的表为505行。因而还需要对其进行优化
  • 记载上一次返回的主键在下次查询时运用主键过滤 
    SELECT film_id,description FROM film where film_id>55 and film_id<=50 ORDER BY film_id limit 1, 5;
    此时只扫描了5条数据且没有运用文件排序,因而优化更大，但有前提条件：(1)主键要求次序排序，当呈现次序空缺(例如：1,2,5这种状况或许导致数据没有得到所要查询的数，即把空缺的行也算进去).(2)假如呈现(1)的状况，能够在该表再建一列且该次序自增再设置为索引。

3. 索引优化

3.1 怎么挑选适宜的列树立索引

  • 在where 从句,group by从句,order by 从句, on从句中呈现的列
  • 索引字段越小越好
  • 离散度大的列放到联合索引的前面(离散度：该字段在数据库中的值有许多种)

例如：select * from payment where staff_id = 2 and customer_id = 584;是index(staff_id,customer_id)好?仍是index(customer_id,staff_id)好? 答案: 因为customer_id的离散度更大，所以应该运用index(customer_id,staff_id)

3.2 索引优化SQL的办法

  • 重复索引 重复索引指相同的列以相同的次序树立的同类型的索引，如下表中primary key和ID列上的索引便是重复索引(即在主键上再加索引)
# id添加 primary key和unique(id)为重复索引
create table test(
  id int not null primary key,
  name varchar(10) not null,
  title varchar(50) not null,
  unique(id) 
)engine=innodb;
  • 冗余索引 冗余索引指多个索引的前缀列相同，或是在联合索引中包括了主键的索引，下面这个列子中key(name,id)便是一个冗余索引
# id添加 primary key和 key(name,id)为冗余 索引
create table test(
  id int not null primary key,
  name varchar(10) not null,
  title varchar(50) not null,
  key(name,id) 
)engine=innodb;
  • 查找从重复及冗余的索引

办法一：通过MySQL的information_schema数据库 查找重复与冗余索引

USE information_schema;
SELECT a.table_schema AS '数据库', a.table_name AS '表名', a.index_name AS '索引1', b.index_name AS '索引2', a.column_name AS '重复列名'
FROM information_schema.statistics a
    JOIN statistics b ON a.table_schema = b.table_schema
        AND a.table_name = b.table_name
        AND a.seq_in_index = b.seq_in_index
        AND a.column_name = b.column_name
WHERE a.seq_in_index = 1
    AND a.index_name != b.index_name

成果如下：

性能优化之MySQL优化
咱们发现attendace-system的s_admin表中有重复索引id。

办法二：用pt-duplicate-key-checker 东西检查重复及冗余索引（Windows上好像运用不了） 运用办法 pt-duplicate-key-checker -hxxx(host) -uxxx(username) -pxxx(password)

3.3 索引维护的办法

  • 删去不必索引 现在MySQL中还没有记载索引的运用状况，可是在PerconMySQL和MariaDB中能够通过INDEX_STATISTICS表来检查那些索引未运用，但在MySQL中现在只能通过慢查询日志配合pt-index-usage东西来进行索引运用状况的剖析。

4. 数据库结构优化

4.1 挑选适宜的数据类型

数据类型的挑选，要点在于适宜二字，怎么确认挑选的数据类型是否适宜？

  • 运用能够存下你的数据的最小的数据类型
  • 运用简单的数据类型。Int要比varchar类型在MySQL处理上简单
  • 尽或许的运用not null界说字段
  • 尽量少用text类型，非用不可时最好考虑分表。

4.1.1 存储日期

运用int来存储日期时刻，运用FROM_UNIXTIME():将int类型时刻戳转换为日期时刻格局,UNIX_TIMESTAMP():将日期时刻格局转换为int类型的时刻戳。两个函数来进行转换。

# 创建表
create table test(id int AUTO_INCREMENT NOT NULL,timestr INT,primary key(id));
# 刺进数据
insert into test(timestr) values(unix_timestamp('2014-06-01 13:12:00'));
select FROM_UNIXTIME(timestr) from test;

4.1.2 存储IP地址

运用bigint来存储IP地址，运用INET_ATON():把ip转为无符号整型(4-8位),INER_NTOA():把整型的ip转为字符串式的地址 两个函数来进行转换

create table sessions(id int AUTO_INCREMENT NOT NULL,ipaddress BIGINT,primary key(id));
# 刺进数据
insert into sessions(ipaddress) values(INET_ATON('192.168.0.1'));
select INET_NTOA(ipaddress) from sessions;

4.2 数据库表的范式化优化

范式化是指数据库规划的规范，现在说到范式化一般是指第三规划范式，也便是要求数据表中的不存在非要害字段对恣意候选要害字段的传递函数依赖则契合第三范式。

不契合第三范式要求的表存在下列问题：

  • 数据冗余
  • 数据的刺进反常
  • 数据额更新反常
  • 数据的删去反常

反范式化是指未来查询功率的考虑把原本契合第三范式的表恰当的添加冗余，以达到优化查询功率的意图，反范式化时一种以空间换时刻的操作。

垂直拆分是指把原来一个有许多列的表拆分红多个表，这处理了表的宽度问题。一般垂直拆分能够按以下准则进行。

  • 把不常用的字段独自存放到一个表中
  • 把大字段独立存放到一个表中
  • 把经常一同运用的字段放到一同

水平拆分是为了处理单表的数据量过大问题，水平拆分的表每一个表的结构都完全一致。 水平拆分的应战：1.跨分区表进行数据查询。2.计算及后台报表操作。

5. 系统装备优化

5.1 数据库系统装备优化

5.1.1 操作系统装备优化

数据库是基于操作系统的现在大多数MySQL都是安装在Linux系统之上，所以对于操作系统的一些装备参数也会影响到MySQL的功能，下面就列出一些常到的系统装备

  • 网络方面的装备，要修正 /etc/sysctl.conf文件
# 添加tcp支撑的行列数
net.ipv4.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 65535
# hard nofile 65535

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

5.2 MySQL装备文件优化

MySQL能够通过启动时指定装备参数和运用装备文件两种办法进行装备，在大多数状况下装备文件坐落 /etc/my.cnf或/etc/mysql/my.cnf在Windows系统装备文件能够是坐落 C:/windows/my.ini文件，MySQL查找装备文件的次序能够通过以下办法获得

$ /usr/sbin/mysld --verbose --help | grep -A 1 'Default options'

留意： 假如存在多个位置存在装备文件，则后边的会掩盖前面的。

5.2.1 常用参数说明

  • innodb_buffer_pool_size 十分重要的一个参数，用于装备Innodb的缓冲池假如数据库中只要Innodb表，则引荐装备量为总内存的75%
  • innodb_buffer_pool_instances 能够控制缓冲池的个数，默许状况下只要一个缓冲池
  • 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表的计算信息

5.3 第三方装备东西运用

  • Percon Configuration Wizard

6. 服务器硬件优化

6.1 怎么挑选CPU

**考虑：**是挑选单核更快的CPU仍是挑选核数更多的CPU?

  • MySQL有些作业只能运用到单核CPU。比如Replicate,SQL
  • MySQL对CPU核数的支撑并不是越多越快。MySQL5.5运用的1服务器不要超越32核

6.2 磁盘IO优化

常用RAID等级简介

  • RAID0:也称条带，便是把多个磁盘链接成一个硬盘运用，这个等级IO最好。
  • RAID1:也称镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
  • RAID5:也是把多个(最少3个)硬盘合并成一个逻辑盘运用，数据读写时会树立奇偶效验信息，而且奇偶效验信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据产生损坏后，运用剩下的数据和相对应的奇偶效验信息去康复被损坏的数据
  • RAID1+0:便是RAID1和RAID0结合，一同具备两个等级的优缺点。一般主张数据库运用这个等级。 考虑： SNA和NAT是否合适数据库?
  1. 常用于高可用处理方案
  2. 次序读写功率很高，可是随机读写不如人意
  3. 数据库随机读写比率很高

文档对应视频：功能优化之MySQL优化