MySQL数据库优化

数据库优化的目的

避免出现页面访问错误

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

增加数据库的稳定性

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

优化用户体验

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

可以从几个方面进行数据库优化

SQL及索引优化

如何分析SQL查询

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

show variables like ‘slow_query_log‘; # 查看日志是否开启
show variables like  ‘%log%‘;
set global log_queries_not_using_indexes=on;  # 开启日志
show variables like ‘log_query_time‘;  # 查看查询多长时间会被记录到慢查日志
set global long_query_time=1 # 设置插入到慢查日志的时间阈值  0.01s
show variables like ‘slow%‘; # 查看慢查日志记录的位置
set global show_query_log_file=‘/home/mysql/sql_log/mysql_slow.log‘  # 设置慢查日志的位置

慢查日志所包含的内容

• 执行SQL的主机信息
• SQL的执行信息
• SQL执行时间
• SQL的内容

慢查日志分析工具

mysqldumpslow

下载mysql自带的工具

mysqldumpslow -h  # 查看帮助文档

mysqldumpslow -t 3 /home/mysql/sql_log/mysql_slow.log | more  # 查询最慢的前三条数据

输出结果

pt-query-digest

#输出到文件
pt-query-digest slow-log > slow_log.report
输出到数据库表
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

pt-query-digest /home/mysql/sql_log/mysql_slow.log | more

查询结果

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

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

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

2. IO大的SQL

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

3. 未命中索引的SQL

   注意pt-query-digest分析中的Rows examine和Rows Send的对比

如何分析SQL查询

使用explain查询SQL的执行计划

explain返回各列的含义

table:显示这一行的数据是关于哪张表的

type:这是重要的列,显示连接使用了何种类型,从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

possible_keys:显示可能应用在这张表中的索引。如果为空,没有可能的索引。

key:实际使用的索引。如果为NULL，则没有使用索引。

key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

rows:MySQL任务必须检查的用来返回请求数据的行数

extra列需要注意的返回值

Using filesort:看到这个得时候，查询就需要优化了。MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型记忆存储排序键值和匹配条件的全部行的行指针来排序全部行

Using temporary 看到这个的时候，查询需要优化了。这里，MySQL需要创建一个临时表来存储结构，者通常发生在对不同的列集进行ORDER BY上，而不是GROUP BY上

Count()和Max()的优化方法

查询最后支付时间——优化max()函数

select max(payment_date) from payment;

# 优化操作:给pay_date建立索引
create index idx_paydate on payment(payment_date);

在一条SQL中同时出啊出2006和2007年电影的数量——优化count()函数

错误的方式:

# 无法分开计算2006和2007年的电影数量
SELECT COUNT(release_year="2006" OR release_year="2007") FROM film;

# 逻辑错误
SELECT COUNT(*) FROM film WHERE release_year="2006" AND release_year="2007";

正确的方式

SELECT 
COUNT(release_year="2006" OR NULL) AS "2006年电影数量",
COUNT(release_year="2007" OR NULL) AS "2007年电影数量" 
FROM film; 

子查询的优化(?)

通常情况下，需要把子查询优化为join查询，但在优化时要注意关联表是否有一对多关系，要注意重复数据（distinct去重）

(查询xxx出演的所有影片)

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 = "xxx"))

优化group by查询

原始语句

explain SELECT actor.firts_name,actor.last_name,COUNT(*) FROM film_actor INNER JOIN actor USING(actor_id) GROUP BY film_actor.actor_id;

优化方式：现在子查询中过滤条件并分组，然后在连表操作

优化语句

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);

优化Limit查询

limit常用于分页处理，时常会伴随order by 从句使用，因此大多时候会使用Filesorts 这样会造成大量的IO问题。

explain SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

优化步骤1：使用有索引的列或主键进行Order by 操作

explain SELECT film_id,description FROM film ORDER BY film_id LIMIT 50,5;

上述需要查询55条记录

但是对于大量的数据如10000,5则需要查询10005行也需要进行大量的IO，所以需要进一步优化

优化步骤2：记录上次返回的主键，在下次查询时使用主键过滤

explain SELECT film_id,description FROM film WHERE film_id >55 AND film_id<=60 ORDER BY film_id LIMIT 1,5;

避免了数据量大时扫描过多的记录

缺点：要求主键顺序排序的（中间没有断掉），如果主键中间有断掉，可以做一个辅助列，是自增并且加上索引，使用这个来进行检索。

如何选择合适的列建立索引？

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

SELECT * FROM payment WHERE staff_id = 2 AND customer_id = 548;

基于上面的语句是建立index(staff_id,customer_id)好？还是 index(customer_id,staff_id)好？

由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id)好

索引的维护及优化——重复及冗余索引

由于索引过多会影响数据库写入的效率，而且有时候也会影响查询的效率（由于数据库在查询时会分析选择哪个索引去进行查询，索引过多增加分析的过程）

这就要求不仅要会添加索引，有时候也要删除索引如重复、冗余索引

重复索引指相同的列以相同的顺序建立的同类型的索引，如下表primary key 和 id 列上的索引就是重复索引

create table test(
	id int not null primary key,
    name varchar(10) not null,
    title varchar(50) not null,
    unique(id)
)engin=innoDB;

冗余索引是指多个索引的前缀列相同，或是在联合索引中包含了主键的索引，下面这个列子中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;

查找重复及冗余索引的语句

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

使用第三方工具pt-duplicate-key-checker 工具检查重复及冗余索引

pt-duplicate-key-checker -uroot -p ‘123456‘ -h 127.0.0.1

索引的维护及优化——删除不用索引

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在MySQL中目前只能通过慢查日志配置pt-index-usage工具来进行索引使用情况的分析。

pt-index-usage -uroot -p ‘123456‘ mysql-slow.log

数据库结构优化

选择合适的数据类型

数据类型的选择，重点在于合适二字，如何确定选择数据类型是否合适？

1. 使用可以存下你的数据的最小的数据类型
2. 使用简单的数据类型。int要比varchar类型在MySQL处理上简单。
3. 尽可能的使用not null定义字段
4. 尽量少用text类型，非用不可时最好考虑分表。

比如说时间类型的数据，我们既可以用varchar存储，也可以使用时间日期型存储，还可以使用时间戳类型存储，还能用整型存储，那么选择哪个进行存储呢？要看哪种类型是最小的。

首先int是相对来说最小的数据类型，而在MySQL中int和时间戳占用字节数和int是一样的，下面要选择尽量简单的数据类型，int要比varchar简单的多，int可能也要比时间戳简单。

所以使用int来存储日期时间。

这里就要用到FROM_UNIXTIME(),UNIX_TIMESTAMP()两个函数来进行转化

CREATE TABLE test(
    id int auto_increment not null,
    timestr int,
	PRIMARY KEY(id)
);

INSERT INTO test(timestr) VALUES(UNIX_TIMESTAMP("2020-02-20 20:02:20"));

SELECT FROM_UNIXTIME(timestr) FROM test;

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

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;

表的范式化和反范式化

范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式，也就是要求数据表中不存在非关键字段对任意候选关键字段的传递函数依赖则符合第三范式

存在以下传递函数依赖关系：

(商品名称) -> （分类）-> （分类描述）

也就是说存在非关键字段"分类描述"对关键字段"商品名称"的传递函数依赖。

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

1. 数据冗余：（分类，分类描述）对于每一个商品都会进行记录
2. 数据的插入异常
3. 数据的更新异常
4. 数据的删除异常

解决办法——分表

反范式化是指为了查询效率的考虑把原本符合第三范式的表适当的增加冗余，以达到优化查询效率的目的，反范式化是一种以空间来换取时间的操作。

如何查询订单信息？

SELECT b.用户名,b.电话,b.地址,a.订单ID,sum(c.商品价格*c.商品数量) as 订单价格 
FROM `订单表` a 
JOIN `用户表` b ON a.用户ID=b.用户ID 
JOIN `订单商品表` c ON c.订单ID=b.订单ID
GROUP BY b.用户名,b.电话,b.地址,a.订单ID

因为使用了inner join 和group by 会生成第三张表,使得IO操作会大大增加所以进行反范式化

反范式化后

SELECT a.用户名,a.电话,a.地址,a.订单ID,a.订单价格 FROM `订单表` a

表的垂直拆分

所谓表的垂直拆分，就是把原来一个有很多列的表拆分成多个表，这解决了表的宽度问题。通常垂直拆分可以按以下原则进行。

1. 把不常用的字段单独存放到一个表中
2. 把大字段独立存放到一个表中
3. 把经常一起使用的字段放到一起

create table film(
	film_id SMALLINT(5) UNSIGNED not null auto_increment,
	title VARCHAR(255) not null,
	description TEXT,
	release_year year(4) DEFAULT null,
	lagnuage_id TINYINT(3) UNSIGNED not null,
	original_language_id TINYINT(3) UNSIGNED DEFAULT null,
	rental_duration TINYINT(3) UNSIGNED not null DEFAULT 3,
	rental_rate DECIMAL(4,2) not null DEFAULT 4.99,
	length SMALLINT(5) UNSIGNED DEFAULT null,
	replacement_cost DECIMAL(5,2) not null DEFAULT 19.99,
	rating VARCHAR(5) DEFAULT ‘G‘,
	special_features VARCHAR(10) DEFAULT null,
	last_update TIMESTAMP,
	PRIMARY KEY(film_id)
)

拆分之后

create table film(
	film_id SMALLINT(5) UNSIGNED not null auto_increment,
	release_year year(4) DEFAULT null,
	lagnuage_id TINYINT(3) UNSIGNED not null,
	original_language_id TINYINT(3) UNSIGNED DEFAULT null,
	rental_duration TINYINT(3) UNSIGNED not null DEFAULT 3,
	rental_rate DECIMAL(4,2) not null DEFAULT 4.99,
	length SMALLINT(5) UNSIGNED DEFAULT null,
	replacement_cost DECIMAL(5,2) not null DEFAULT 19.99,
	rating VARCHAR(5) DEFAULT ‘G‘,
	special_features VARCHAR(10) DEFAULT null,
	last_update TIMESTAMP,
	PRIMARY KEY(film_id)
)

CREATE table file_text(
	film_id SMALLINT(5) UNSIGNED not null,
	title VARCHAR(255) not null,
	description TEXT,
	PRIMARY KEY(film_id)
)

表的水平拆分

表的水平拆分是为了解决单表的数据量过大的问题，水平拆分的表每一个表的结构都是完全一致的。以下面的payment表为例

CREATE TABLE payment(
	payment_id SMALLINT(5) UNSIGNED NOT NULL auto_increment,
	customer_id SMALLINT(5) UNSIGNED NOT NULL,
	staff_id TINYINT(3) UNSIGNED NOT NULL,
	rental_id int(11) DEFAULT NULL,
	amount DECIMAL(5,2) NOT NULL,
	payment_date DATETIME NOT NULL,
	last_update TIMESTAMP,
	PRIMARY KEY(payment_id)
)ENGINE=INNODB DEFAULT CHARSET=utf8

常用的水平拆分方法为：

1. 对customer_id进行hash运算，如果要拆分成5个表则使用mod(customer_id,5)取出0-4个值
2. 针对不同的hashID把数据存到不同的表中

挑战：

1. 跨分区表进行数据查询
2. 统计及后台报表操作

系统配置优化

操作系统配置优化

数据库时基于操作系统的，目前大多数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等防火墙软件。

MySQL配置文件

MySQL可以通过启动时指定配置参数和使用配置文件两种方式进行配置，大多数情况下配置文件位于/etc/my.cnf或/etc/mysql/my.cnf在windows系统配置文件是位于安装路径下的my.ini文件，MySQL查找配置文件的顺序可以通过以下方法获得

$/user/sbin/mysqld --verbose --help | grep -AA 1 ‘Default options‘

注意：如果存在多个位置的配置文件，则后面的会覆盖前面的。

MySQL配置文件——常用参数说明

innodb_buffer_pool_size

非常重要的一个参数，用户配置innodb的缓冲池，如果数据库中只有innodb表，则推荐配置为总内存的75%。

SELECT ENGINE, ROUND(SUM(data_length+index_length)/1024/1024,1) AS "Total MB" 
FROM information_schema.`TABLES` WHERE table_schema not in ("information_schema","performance_schema")
GROUP BY ENGINE;

innodb_buffer_pool_size >= Total MB

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表的统计信息，可以设置为OFF，人为的找一个时间段去刷新。

MySQL数据库优化

标签：iptable   种类型   页面   离散   var   字节   nod   针对   数据安全