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;

表的范式化和反范式化

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

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

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

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

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

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

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

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;