1. 数据库优化的目的

避免出现页面访问错误

• 由于数据库连接timeout产生页面5xx错误
• 由于慢查询造成页面无法加载
• 由于阻塞造成数据无法提交

增加数据库的稳定性

• 很多数据库问题都是由于低效的查询引起的

优化用户体验

• 流畅页面的访问速度
• 良好的网站功能体验

1.1 数据库优化方向

• 硬件(成本最高，效果最低)
• 系统配置
• 数据库表结构
• SQL及索引(成本最低，效果最高)

2. SQL优化

2.1 如何发现有问题的SQL？

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

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

1. 查询MySQL慢查询日志是否开启

show VARIABLES LIKE 'slow_query_log';

2. 设置慢查询日志的文件位置

# 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';

3. 设置是否对未使用索引的SQL进行记录

set global log_queries_not_using_indexes = on;

4. 设置只要SQL执行超过n秒的就记录

# 此处设置0.001秒是为了方便测试，一般情况比这大
set global long_query_time = 0.001 ;

5. 启用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款慢查询日志的对比

2.1.4 如何通过慢查询日志发现有问题的SQL

1. 查询次数多且每次查询占用时间长的SQL

通常未pt-query-digest分析的前几个查询

2. IO大的SQL

注意pt-query-digest分析中的Rows examine项

3. 未命中索引的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; 

btt4aQ.png

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

3. possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。
4. key：实际使用的索引。如果为NULL则没有使用索引。
5. key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。
6. ref：显示索引的哪一列被使用了，如果可能的话是一个常数。
7. rows：MySQL认为必须检查的用来返回请求数据的行数。

2.2.3 extra列需要注意的返回值

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

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

查询payment的时候发现使用的是全表查询

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

结果如下图：

btrHzQ.png

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

效果如下图：

btsswq.png

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;

结果为：

bt2A5q.png

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

btfyJ1.png

优化后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

bthUfI.png

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进行优化。

baeqjH.png

优化策略：

• 使用有索引的列或主键进行Order by 操作

  SELECT film_id,description FROM film ORDER BY film_id limit 50, 5;
  

  查看该SQL的执行计划发现，使用的是主键索引查询,一共扫描了55条数据且没有使用文件排序,因此优化了很大。
  
  banYLj.png
  
  但是当我们将限制条件改为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

结果如下：

bw7TYV.png

方法二：用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优化