MySQL 数据库优化
本文为个人慕课网《性能优化之MySQL优化》课堂笔记
一、简介
1.1 目的
- 避免出现页面访问错误
- 由于数据库连接
timeout产生5xx错误 - 由于慢查询造成页面无法加载
- 由于阻塞造成数据无法提交
- 由于数据库连接
- 增加稳定性
- 很多数据库问题都是由于低效的查询引起的
- 优化用户体验
- 流畅页面的访问速度
- 良好的网站功能体验
1.2 优化方面
-
表结构设计：有益于 SQL 查询、有益于 SQL 查询的写法。
-
系统配置：MySQL 大多运行在 Linux 中，TCP/IP 连接数的限制、打开文件数的限制 （主要） 和安全性的限制。
- MySQL 数据库是镜像的、是基于文件的，查询一个表就是打开一个文件。如果文件数达到限制，文件就无法打开，就会进行频繁的I/O操作。
-
硬件优化：选择更适合数据库的CPU、更快的I/O和更多的内存。
- 数据库在内存中对数据进行操作，因此内存越大，性能可能越好。
- 但是CPU数目越多，可能并不会对数据库操作造成更多的影响，因为MySQL对CPU的利用有限制，并不会利用太多的核数，有些查询只能用到单核。
- I/O设备，SSD、RAID组合和级别。但是并不能减少阻塞——锁，是数据库中保存数据完整性的一种机制。因此说硬件的优化是成本最高但是效果最差的。
如果SQL和索引没有优化好，产生大量的慢查询、阻塞，这由于MySQL内部的锁机制造成的，再好的硬件也是没有办法优化的。
二、数据和工具准备
2.1 数据准备
数据库基于 MySQL 5.7，使用sakila数据库：http://dev.mysql.com/doc/index-other.html
这个数据库结构基于影片租赁商店业务，记录了影片的相关信息、连锁商店的信息和客户信息等。
2.2 慢日志的开启方式和存储格式
2.2.1 慢日志的开启方式
如何发现有问题的SQL？
使用MySQL慢查询日志对有效率问题的SQL进行监控
-- 查询慢查询日志
show variables like 'slow_query_log';
-- 指定慢查询日志存储文件位置
set global slow_query_log_file = '/home/mysql/sql_log/mysql-slow.log';
-- 是否要把没有使用索引的SQL语句记录到慢查询日志中。通常情况下优化数据库，主要是为了优化表的索引和查询方式
set global log_queries_not_using_indexes = on;
-- 把超过long_query_time秒的查询记录到慢查询日志中，对于业务繁忙的环境，通常设置为100ms（0.01s）
set global long_query_time = 1;
操作步骤：
mysql> show variables like 'slow_query_log'; # 查询慢查询日志
+----------------+-------+
| Variable_name | Value |
+----------------+-------+
| slow_query_log | ON |
+----------------+-------+
mysql> show variables like '%log%'; # 查询所有日志
...
log_queries_not_using_indexes | OFF
...
mysql> set global log_queries_not_using_indexes = on;
Query OK, 0 rows affected (0.01 sec)
mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
mysql> set global slow_query_log = on; # 开启慢查询日志
Query OK, 0 rows affected (0.00 sec)
接下来是进行数据库的查询：
mysql> use sakila;
Database changed
mysql> show tables;
+----------------------------+
| Tables_in_sakila |
+----------------------------+
| actor |
| actor_info |
| address |
| category |
| city |
| country |
| customer |
| customer_list |
| film |
| film_actor |
| film_category |
| film_list |
| film_text |
| inventory |
| language |
| nicer_but_slower_film_list |
| payment |
| rental |
| sales_by_film_category |
| sales_by_store |
| staff |
| staff_list |
| store |
+----------------------------+
23 rows in set (0.00 sec)
mysql> select * from store limit 10;
+----------+------------------+------------+---------------------+
| store_id | manager_staff_id | address_id | last_update |
+----------+------------------+------------+---------------------+
| 1 | 1 | 1 | 2006-02-15 04:57:12 |
| 2 | 2 | 2 | 2006-02-15 04:57:12 |
+----------+------------------+------------+---------------------+
2 rows in set (0.00 sec)
mysql> show variables like 'slow%';
+---------------------+------------------------------------+
| Variable_name | Value |
+---------------------+------------------------------------+
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | /home/mysql/sql_log/mysql-slow.log |
+---------------------+------------------------------------+
3 rows in set, 1 warning (0.00 sec)
2.2.2 慢日志的存储格式
查看mysql-slow.log文件
# Time: 2021-07-11T09:25:28.885390Z
# User@Host: root[root] @ localhost [::1] Id: 804
# Query_time: 0.002543 Lock_time: 0.002367 Rows_sent: 2 Rows_examined: 2
use sakila;
SET timestamp=1625995528;
select * from store limit 10;
- 执行SQL的主机信息：
User@Host: root[root] @ localhost [::1] Id: 804 - SQL的执行信息：
Query_time: 0.002543 Lock_time: 0.002367 Rows_sent: 2 Rows_examined: 2 - SQL的执行时间：
SET timestamp=1625995528; - SQL的内容：
select * from store limit 10;
2.3 慢查询日志分析
2.3.1 mysqldumpslow
分析工具：
mysqldumpslow优点：MySQL 自带
缺点：信息少
[root@sample ~]# mysqldumpslow
Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
Parse and summarize the MySQL slow query log. Options are
--verbose verbose
--debug debug
--help write this text to standard output
-v verbose
-d debug
-s ORDER what to sort by (al, at, ar, c, l, r, t), 'at' is default
al: average lock time
ar: average rows sent
at: average query time
c: count
l: lock time
r: rows sent
t: query time
-r reverse the sort order (largest last instead of first)
-t NUM just show the top n queries
-a don't abstract all numbers to N and strings to 'S'
-n NUM abstract numbers with at least n digits within names
-g PATTERN grep: only consider stmts that include this string
-h HOSTNAME hostname of db server for *-slow.log filename (can be wildcard),
default is '*', i.e. match all
-i NAME name of server instance (if using mysql.server startup script)
-l don't subtract lock time from total time
[root@sample ~]# mysqldumpslow -t 3 /home/msyql/data/mysql-slow.log | more
# 分析出较慢的前三个sql
Count: 1 Time=13.23s (13s) Lock=0.00s (0s) Rows=2844047.0 (2844047), root[root]@localhost
SELECT /*!N SQL_NO_CACHE */ * FROM `salaries`
Count: 1 Time=0.01s (0s) Lock=0.01s (0s) Rows=463.0 (463), root[root]@localhost
select * from info
Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=42.0 (42), root[root]@localhost
select * from subscription
# 包含执行时间、锁定时间、发送的行数、由谁通过什么服务器执行、sql的具体内容
2.3.2 pt-query-digest
分析工具：pt-query-digest
优点：显示信息多
缺点：需要额外下载
# 使用方法
Usage: pt-query-digest [OPTIONS] [FILES] [DSN]
Options:
（省略）
--limit=A Limit output to the given percentage or count (
default 95%:20) # 与mysqldumpslow的-t相同，限定分析（默认95%）的sql
--explain=d Run EXPLAIN for the sample query with this DSN
and print results # 查询SQL执行计划
# 输出到文件
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
操作步骤：
[root@VM-0-8-centos bin]# pt-query-digest /www/server/data/mysql-slow.log | more
# 150ms user time, 10ms system time, 22.49M rss, 187.16M vsz
# Current date: Sun Jul 11 18:44:22 2021
# Hostname: VM-0-8-centos
# Files: /www/server/data/mysql-slow.log
# Overall: 25 total, 8 unique, 0.00 QPS, 0.00x concurrency _______________ （这里包含25条查询，其中不同的查询有8条，即抽象出来8条）
# Time range: 2021-06-23T09:03:40 to 2021-07-11T10:26:26
# Attribute total min max avg 95% stddev median
# ============ ======= ======= ======= ======= ======= ======= =======
# Exec time 13s 176us 13s 530ms 2ms 3s 260us （执行时间）
# Lock time 20ms 0 15ms 812us 1ms 3ms 103us （锁定时间）
# Rows sent 2.71M 3 2.71M 111.12k 40.45 524.78k 2.90 （发送的行数）
# Rows examine 2.71M 3 2.71M 111.12k 40.45 524.78k 2.90 （扫描的行数，若其远远大于Rows send，说明索引不是很好）
# Query size 4.06k 18 238 166.24 202.40 74.00 202.40 （）
# Profile
# Rank Query ID Response time Calls R/Call V/M
# ==== =================================== ============= ===== ======= ===
# 1 0xE3C753C2F267B2D767A347A2812914DF 13.2295 99.8% 1 13.2295 0.00 SELECT salaries
# MISC 0xMISC 0.0306 0.2% 24 0.0013 0.0 <7 ITEMS>
# Query 1: 0 QPS, 0x concurrency, ID 0xE3C753C2F267B2D767A347A2812914DF at byte 0 （具体的sql）
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2021-06-23T09:03:40
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 4 1 （执行了1次，占总共查询次数的4%）
# Exec time 99 13s 13s 13s 13s 13s 0 13s
# Lock time 0 0 0 0 0 0 0 0
# Rows sent 99 2.71M 2.71M 2.71M 2.71M 2.71M 0 2.71M （insert查询该行为0）
# Rows examine 99 2.71M 2.71M 2.71M 2.71M 2.71M 0 2.71M （insert查询该行为0）
# Query size 1 49 49 49 49 49 0 49
# String:
# Databases employees
# Hosts localhost
# Users root
# Query_time distribution
# 1us
# 10us
# 100us
--More--
2.4 通过慢查询日志发现有问题的 SQL
- 查询次数多且每次查询占用时间长的 SQL
- 通常为
pt-query-digest分析的前几个查询
- 通常为
- I/O 大的 SQL
- 注意
pt-query-digest分析中的Rows examine项 - 一个SQL的扫描行数越多，I/O消耗越大
- 注意
- 未命中索引的 SQL
- 注意
pt-query-digest分析中的Rows examine和Rows send的对比 - 若
Rows examine远远大于Rows send，说明索引命中率不高，基本用索引扫描或表扫描的方式来查询
- 注意
2.5 通过explain查询分析SQL的执行计划
数据库中，SQL都是先进行执行计划的分析，然后再进行SQL的具体查询。执行计划从侧面反映出SQL执行效率。
explain 返回各列的含义
table：显示这一行的数据是关于哪张表的type：重要的列，显示连接使用了何种类型。性能从最好到最差的连接类型为const > eq_reg > ref > range > index > ALLconst是常数查找，一般是对于主键、唯一索引的查找eq_reg是范围的查找，一般是唯一索引、主键的范围查找ref常见于连接的查询中，比如一个表是基于某个索引的查找range是基于索引的范围查找index通常是对于索引的扫描来进行操作ALL的操作是表扫描。上图中的SQL没有WHERE从句，执行的是表扫描操作。
prossible_keys：显示可能应用在该表中的索引。若为空，则没有可以用到的索引key：实际使用的索引。若为NULL，则没有使用索引。上图中是表扫描操作，没有实际用到索引，因此prossible_keys和key都为空key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好。这是因为 MySQL 的每次读取都是以页为单位的，而如果页中索引长度越短，相应地，可以存储的索引数量越大，它的查询效率就越高。ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。即常数的索引，执行效果是最好的rows：表扫描的行数Using filesort（扩展信息）：查询结果用到了文件排序的方式来执行优化，在ORDER BY中比较常见- 看到这个的时候，查询就需要优化了
- MySQL 需要进行额外的步骤来发现如何对返回的行来进行排序
- 它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
Using temporary（扩展信息）：查询用到了临时表- 看到这个的时候，查询就需要优化了
- 这里，MySQL 需要创建一个临时表来存储结果，这通常发生在对不同的列集进行
ORDER BY上，而不是GROUP BY上
看到了最后两种扩展信息结果的都是需要优化的，因为用到了外部文件或临时表来进行数据的转储，这种SQL通常会出现在
GROUP BY和ORDER BY从句中
三、SQL及索引优化
3.1 SQL 优化
3.1.1 COUNT() 和 MAX() 的优化
1. MAX() 查询优化
MAX() 经常会被用于查找出最大值或最后的某一个事件的时间，通常可以用建立索引来优化。
查找最后支付时间 —— 优化MAX()函数
显示出SQL的执行计划，是表扫描，效率低。如何优化这种SQL呢？
通常情况下，可以在payment_date上建立索引
create index idx_paydate on payment(payment_date);
再次查询
看到Select tables optimized away，表示并不需要实际查询表的数据，只需要通过索引就可以完全知道SQL的执行结果，这种索引称作“覆盖索引”。
因为索引是顺序排列的，通过索引的统计信息就可以知道最后一个
payment_date的值。这样就大大优化了SQL的执行效率，同时尽可能减少了I/O操作。不管数据量有多大，执行频率有多高，它的执行效率基本上是恒定的。
2. COUNT() 查询优化
在一条SQL中同时查出2006年和2007年电影的数量 —— 优化 COUNT() 函数
- 错误的方式：
SELECT COUNT(release_year='2006' OR release_year='2007')
FROM film;
-- 无法分开计算2006年和2007年的电影数量
SELECT COUNT(*)
FROM film
WHERE release_year='2006' AND release_year='2007';
-- release_year不可能同时为2006和2007，有逻辑错误
- 正确的方式：
SELECT COUNT(release_year = '2006' OR NULL) AS '2006年电影数量',
COUNT(release_year = '2007' OR NULL) AS '2007年电影数量'
FROM film;
# 结果集
+---------------------+---------------------+
| 2006年电影数量 | 2007年电影数量 |
+---------------------+---------------------+
| 1000 | 0 |
+---------------------+---------------------+
1 row in set (0.01 sec)
注：COUNT(*) 和 COUNT(某一列)的区别
# 新建临时表t
mysql> create table t(id int);
Query OK, 0 rows affected (0.16 sec)
mysql> insert into t values(1), (2), (null);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from t;
+------+
| id |
+------+
| 1 |
| 2 |
| NULL |
+------+
3 rows in set (0.00 sec)
mysql> select count(*), count(id) from t;
+----------+-----------+
| count(*) | count(id) |
+----------+-----------+
| 3 | 2 |
+----------+-----------+
1 row in set (0.00 sec)
可以看出：count(id) 的结果不包含空值的这一行；而count(*) 是表中所有的行数，包含空值的这一行。
3.1.2 子查询的优化
通常情况下，需要把子查询优化为
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'
)
);
explain SELECT DISTINCT title, release_year, LENGTH
FROM film
JOIN film_actor ON film_actor.film_id = film.film_id
JOIN actor ON actor.actor_id = film_actor.actor_id
WHERE actor.first_name = 'sandra';
-
子查询执行计划：
-
连接查询执行计划：
3.1.3 GROUP BY 的优化
-- 未优化语句
explain SELECT actor.first_name, actor.last_name, COUNT(*)
FROM sakila.film_actor
INNER JOIN sakila.actor USING(actor_id)
GROUP BY film_actor.actor_id;
执行计划：
虽然对一个表中的列进行了GROUP BY操作，但是同样会出现使用临时表和文件排序的方式。另外，由于没有where语句，该查询对actor表进行表扫描的操作。
优化语句：
explain SELECT actor.first_name, actor.last_name, c.cnt
FROM sakila.actor INNER JOIN (
SELECT actor_id, COUNT(*) AS cnt
FROM sakila.film_actor
GROUP BY actor_id
) AS c USING(actor_id);
执行计划：
这里已经没有使用临时表或文件排序的方式了，取而代之的是使用索引，进行了一个汇总操作。如果数据量非常大，这就能节省大量的I/O操作时间。
当然这并不是绝对的，如果在未优化的语句中增加一些过滤条件，这种改写方式也要相应进行改变，即在子查询里增加过滤条件，而不是查询完之后，再在外层增加过滤条件。
有时候又会利用子查询，在连接中增加效率。因此对SQL优化要使用灵活的方式。
3.1.4 LIMIT 查询优化
LIMIT常用于分页处理，时常会伴随ORDER BY从句使用，因此大多时候会使用Filesorts，这样会造成大量的I/O问题。
explain SELECT film_id, description
FROM sakila.film
ORDER BY title
LIMIT 50, 5;
执行计划：
可以看到，该SQL语句使用了表扫描的操作，同时使用了文件排序的方式，因此在数据量非常大的情况下会造成I/O问题。
优化
步骤1：使用有索引的列或主键进行 ORDER BY 操作
explain SELECT film_id, description
FROM sakila.film
ORDER BY film_id
LIMIT 50, 5;
执行计划：
改写之后的语句并没有使用文件排序的方式了，取而代之的是主键的排序。这里扫描了55行，即50行后的5行——50 + 5
但是，如果50改成500呢？
可以看到，扫描了505行，I/O操作越来越大。如果翻页翻到后面的时候，几千行，它的响应速度就会很慢，所以有必要对其进行进一步的优化。
步骤2：记录上次返回的主键，在下次查询时使用主键过滤
explain SELECT film_id, description
FROM sakila.film
WHERE film_id > 55 AND film_id <= 60
ORDER BY film_id
LIMIT 1, 5;
* 避免了数据量大时扫描过多的记录
但是有一个缺点，必须要求主键是顺序排序且连续的。如果主键中间空缺了某几列，那么列出的记录可能不足5行。
=> 解决方法：建立一个附加的列，例如index_id列，这一列的数据是自增的，并且加上索引。
3.2 索引优化
3.2.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)
-
如果某列在SQL查询中执行的频率非常高，并且查询中所包含的列相对比较少，那么就可以通过覆盖索引的方式来进行优化。
-
索引字段越小越好。上文说过，这是因为在数据库中数据的存储单位是以“页”为单位，如果索引字段小，那么存储的数据量越多，一次I/O操作获取的数据量就越大。
-
判断列的离散程度，需要对列进行统计操作，唯一值越多，离散度越好
mysql> select count(distinct customer_id), count(distinct staff_id) from payment;
+-----------------------------+--------------------------+
| count(distinct customer_id) | count(distinct staff_id) |
+-----------------------------+--------------------------+
| 599 | 2 |
+-----------------------------+--------------------------+
1 row in set (0.02 sec)
# customer_id有599个，离散度比staff_id大
3.2.2 索引的优化
过多的索引不但会影响写入效率，同时也会影响查询。这是因为数据库在进行查询分析的时候，首先要选择使用哪一个索引来进行查询，如果索引越多，这个分析的过程就越慢，同样会减小查询的效率，因此需要优化和维护索引。
重复及冗余索引
重复索引是指相同的列以相同的顺序建立的同类型索引，如下表中
PRIMARY kEY和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)就是一个冗余索引
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;
# 结果集
+--------------+------------------+-------------+-------------+--------------+
| 数据库名 | 表名 | 索引1 | 索引2 | 重复列名 |
+--------------+------------------+-------------+-------------+--------------+
| db_mail_send | services_content | FK_id_6_idx | FK_id_5_idx | service_id |
| db_mail_send | services_content | FK_id_5_idx | FK_id_6_idx | service_id |
+--------------+------------------+-------------+-------------+--------------+
2 rows in set (0.02 sec)
只检查了前缀，并没有检查哪些索引是包含主键的。
方法二：使用 pt-duplicate-key-checker 工具检查
下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/
安装命令：perl Makefile.PL; make; make install
[root@VM-0-8-centos ~]# pt-duplicate-key-checker -uroot -p '123456' -h 127.0.0.1
# ########################################################################
# db_mail_send.services_content
# ########################################################################
# FK_id_6_idx is a duplicate of FK_id_5_idx
# Key definitions:
# KEY `FK_id_6_idx` (`service_id`),
# KEY `FK_id_5_idx` (`service_id`),
# Column types:
# `service_id` bigint(20) not null comment '????'
# To remove this duplicate index, execute:
ALTER TABLE `db_mail_send`.`services_content` DROP INDEX `FK_id_6_idx`;
# ########################################################################
# Summary of indexes
# ########################################################################
# Size Duplicate Indexes 8
# Total Duplicate Indexes 1
# Total Indexes 135
删除不用索引
目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在MySQL中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析。
pt-index-usage -uroot -p '123456' mysql-slow.log
若是一主多从的情况，如果在不同的从上负载的业务不一样，那么我们在搜集慢查日志的时候，要搜集所有主从的慢查日志进行统一的分析，这样才能分析出来哪些索引是真正不使用的。
四、数据库表结构的优化
4.1 选择合适的数据类型
何为合适？
- 使用可以存下数据的最小数据类型。
- 如时间类型，既可以使用
VARCHAR，又可以使用DATATIME，还可以使用TIMESTAMP以及整型INT来存储，选择需求中最小的一个数据类型即可 => 使用INT或TIMESTAMP（MySQL中二者占用字节数相同）。
- 如时间类型，既可以使用
- 使用简单的数据类型。
INT要比VARCHAR类型在MySQL处理上简单。 - 尽可能地使用
NOT NULL定义字段。- 这是由于INNODB的存储特定所决定的，对于非NOT NULL的表，它可能需要一些额外的字段进行存储，同时也会增加I/O和存储的开销。所以设计表时，尽可能把每个字段设计成NOT NULL并给出默认值。
- 尽量少用
TEXT类型。- 非用不可时最好考虑分表——单独提出来放到另一张附加表中；
- 一方面提高主表的查询效率，另一方面在需要的时候通过附加表的方式来进行大数据的查询。
使用 INT 存储日期时间
利用 FROM_UNIXTIME(), UNIX_TIMESTAMP() 两个函数来进行转换：
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('2021-07-11 23:38:05'));
SELECT FROM_UNIXTIME(timestr) FROM test;
使用 BIGINT 存储 IP 地址
IP 地址大多数情况下使用
VARCHAR，需要约 15 Byte；另外一种方法可以使用BIGINT，只需要使用约 8 Byte。
利用 INET_ATON(), INET_NTOA() 两个函数来进行转换：
INET_ATON()：IP 地址格式到整型INET_NTOA()：整型到 IP 地址格式
CREATE TABLE sessions (
id INT AUTO_INCREMENT NOT NULL,
ipaddredss BIGINT,
PRIMARY KEY(id)
);
INSERT INTO session(ipaddredss) VALUES (INET_ATON('192.168.0.1'));
SELECT INET_NTOA(ipaddress) FROM sessions;
4.2 表的范式化设计
范式化是指数据库设计的规范，目前说到范式化一般是指第三设计范式 3FN，即不存在非关键字段对任意候选关键字段的传递函数依赖。
| 商品名称 | 价格 | 重量 | 有效期 | 分类 | 分类描述 |
|---|---|---|---|---|---|
| 可乐 | 3.00 | 250ml | 2021.12 | 饮料 | 碳酸饮料 |
| 雪碧 | 3.00 | 250ml | 2021.11 | 饮料 | 碳酸饮料 |
存在传递函数依赖关系，不符合3FN：(商品名称) -> (分类) -> (分类描述)
不符合3FN要求的表存在下列问题：
- 数据冗余：(分类，分类描述) 对于每一个商品都会记录
- 数据的插入异常
- 如果表中没有饮料商品，就没有必要记录饮料分类的相关信息
- 数据的更新异常
- 只想更新某一个商品的描述信息，就需要更新这个表中所有关于该商品的描述信息
- 数据的删除异常
- 删除了所有商品，就无法查询所有的分类
解决问题：表的拆分
上述表拆分为如下形式：
| 商品名称 | 价格 | 重量 | 有效期 |
|---|---|---|---|
| 可乐 | 3.00 | 250ml | 2021.12 |
| 苹果 | 8.00 | 500g |
| 分类 | 分类描述 |
|---|---|
| 酒水饮料 | 碳酸饮料 |
| 生鲜食品 | 水果 |
| 分类 | 商品名称 |
|---|---|
| 酒水饮料 | 可乐 |
| 生鲜食品 | 苹果 |
4.3 表的反范式化
反范式化是指为了查询效率的考虑把原本符合3FN的表适当地增加冗余，以达到优化查询效率的目的，该操作是以空间换时间。
如果表结构是完全符合范式化的，那么在查询的时候势必需要关联很多的表。而对于大多数数据库来说，如果表的关联非常多的话，就会影响查询效率。
这四张表完全符合3FN，如果在这种情况下要查询出订单的基本信息（收货人地址、电话，订单的金额等等），如何构造SQL呢？至少要关联三张表：用户表、订单表和订单商品表。如果要反映出商品信息，还要关联商品表。得到SQL语句：
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;
这里使用GROUP BY就会使用临时表，增加I/O操作，减小SQL效率，因此这个SQL并不很好。而以当前的表结构来说，要优化SQL几乎是不可能的。为了优化SQL，就要对表结构进行优化操作。如下图所示：
反范式化后再查询订单信息
订单表中增加了订单价格、用户名、电话、地址，那么SQL语句变为：
SELECT a.用户名, a.电话, a.地址, a.订单ID, a.订单价格
FROM `订单表` a;
这样可以提高SQL效率，尽量少关联一些表，因此表结构的设计对优化SQL起到关键性的作用。
4.4 表的垂直拆分
把原来的一个有很多列的表拆分成多个表，这解决了表的宽度问题。
拆分原则：
- 把不用的字段单独存放到一个表中
- 把大字段独立存放到一个表中
- 把经常一起使用的字段放到一起
前面说过，在表中尽量不要使用TEXT类型，若非用不可，就尽量将其单独放到另一张附加表中。因为在查询过程中，是很少使用description这种大列的，而title和description又经常在一起使用，所以把这两列单独提出来放到一张附加表中。得到拆分后的表结构：
以上为表垂直拆分的一个例子。
4.5 表的水平拆分
该方法是为了解决单表的数据量过大的问题，水平拆分后的每一个表的结构都是完全一致的。
如果单表中的数据量达到了上亿条，尽管加了索引，但是查询效率可能会非常低，并且写入效率也会减少。这个时候考虑对表进行水平拆分。
-
常用的拆分方法：
- 对
customer_id进行 hash运算，如果要拆分成5个表则使用mod(customer_id, 5)取出 0 到 4 个值 - 针对不同的 hashID 把数据存到不同的表中
- 对
-
拆分后面临的挑战：
- 跨分区表进行数据查询
- 统计及后台报表操作
-
解决：
- 把前后台的查询分开，前端查询用拆分后的表，后端用汇总表。
五、配置优化
5.1 数据库系统配置优化
数据库是基于操作系统的，目前大多数MySQL都是安装在Linux系统之上，所有对于操作系统的一些参数配置也会影响到MySQL的性能，如最大内存大小不超过4GB，最大文件大小不超过2GB。下面是常用的优化配置：
网络方面的配置需修改 /etc/sysctl.conf 文件
# 增加TCP支持的队列数
net.ipv4.tcp_max_syn_backlog = 65535
# 减少断开连接时，资源回收，加快timewait状态连接的回收
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 文件
MySQL数据库的每一个表都是一个文件，对于INNODB，表查询的时候都会去打开表文件，就会占用一个打开文件的数量；如果是MyISAM表，可能会占用三个打开文件的数量。而系统对打开文件数本身是有限制的，默认情况下是1024，如果数据库表非常多的话，就有必要增加打开文件数的限制。
# 对limtis.conf文件增加以下内容
sort nofile 65535
hard nofile 65535
除此之外最好在MySQL服务器上关闭iptables, selinux等防火墙软件，特别是大型网站，因为使用iptables等不可避免有一些网络损耗，最好使用硬件防火墙来进行代替。另外还需要考虑文件分区是什么类型的，需要在实际中考虑。
配置文件优化
MySQL 可以通过启动时指定配置参数和使用配置文件两种方法进行配置
在大多数情况下配置文件位于/etc/my.cnf 或 /etc/mysql/my.cnf ，在Windows系统中配置文件可以位于C:/windows/my.ini文件，MySQL查找配置文件的顺序可以通过以下方法获得：
$ /usr/sbin/mysqld --verbose --help | grep -A 1 'Default options'
注意：如果有多个位置存在配置文件，并且涉及到相同的文件参数，则后面文件的参数会覆盖前面的。 即，越往后的查找越起作用。
常用参数说明
innodb_buffer_pool_size
非常重要的一个参数，用于配置InnoDB的缓冲池，如果数据库中只有InnoDB表，则推荐配置量为总内存的 75%，如果还有MyISAM，就适当减少配置量大小。
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表，一般建议缓冲池的最小大小要大于所有InnoDB表数据加索引的和
innodb_buffer_pool_size >= Total MB
innodb_buffer_pool_instances
MySQL 5.5 中新增加参数，可以控制缓冲池的个数（增加并发性）。默认情况下只有一个缓冲池，在一定程度下会增加阻塞的频率（顺序使用资源）。
innodb_log_buffer_size
innodb log 缓冲的大小，由于日志最长每秒钟刷新，所以一般不用太大。（先把日志写到日志缓存区，然后再提交到磁盘）
innodb_flush_log_at_trx_commit
决定了数据库在多长时间把变更刷新到磁盘。关键参数，对InnoDB的I/O效率影响很大。默认值为1，取值范围为0, 1, 2，一般建议设为2，但如果数据安全性要求比较高则使用默认值1，保证事务不丢失。
- 0：每次提交不刷新，每一秒才把变更刷新到磁盘一次。
- 1：每次提交把变更刷新到磁盘，最安全。
- 2：每次提交到缓冲区，每一秒才把缓冲区刷新到磁盘。
innodb_read_io_threads 和 innodb_write_io_threads
以上两个参数决定了InnoDB读写的I/O进程数，默认为4。根据CPU的核数来分别调整读、写负载，以增加读写并发的线程数
innodb_file_per_table
关键参数，控制InnoDB每一个表使用独立的表空间，默认为OFF，即所有表都会建立在共享表空间中。共享表空间一个文件，并发写入的效率就会大大降低。另外共享表空间不能单独收缩，如果读取了一个很大的日志表，想要收缩共享表空间，只能先把所有的数据导出来之后再导入进去才能收缩。因此建议设为ON——每一个表使用独立的表空间。
innodb_stats_on_metdata
决定了MySQL在什么情况下会刷新InnoDB表的统计信息。通常，需要刷新统计信息以保持优化器能正确使用到正确的索引，刷新频率高也会影响数据库的性能。默认情况下，查询系统表都会对统计信息进行刷新，这个时候是不必要的，设为OFF。
5.2 第三方配置工具
MySQL Configuration Wizard，现在该工具已经集成在了 Percona Toolkit 里面
5.3 服务器硬件优化
5.3.1 如何选择CPU
是选择单核更快的CPU还是选择核数更多的CPU？
- MySQL有一些工作只能使用到单核CPU
- 一条SQL语句的执行
- Replicate 复制过程中的复制进程
- MySQL对CPU核数的支持并不是越多越快
- MySQL 5.5 使用的服务器不要超过32核（有国外的网站进行过测试，当服务器CPU超过32核，5.5版本的性能反而有所下降）
5.3.2 磁盘I/O优化
常用RAID级别：
-
RAID0：条带，把多个磁盘链接成一个磁盘使用，这个级别I/O效果最好。但是这种方式的安全性不好，一旦某一个条带损坏，那么整个数据就会丢失。
-
RAID1：镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
-
RAID5：把至少3个硬盘合并成1个逻辑盘使用，数据读写时会建立奇偶校验信息，并且该信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据损坏，利用剩下的数据和相应的奇偶校验信息可以恢复数据。
-
RAID1+0：即RAID1和RAID0的结合，同时具备两个级别的优缺点，既保证了安全性，又增加了磁盘的读写效率。一般建议数据库使用这个级别。
SNA（网络区域存储） 和 NAT（存储区域网络） 是否适合数据库？
- 常用于高可用解决方案
- 如果一台服务器死机了，那么另一台服务器接管这个磁盘矩阵来进行访问。
- 顺序读写效率很高，但是随机读写不如人意
- 数据库大部分进行的随机读写，因此使用这种优化对I/O效率并没有好处
- 数据库随机读写比率很高
需要刷新统计信息以保持优化器能正确使用到正确的索引，刷新频率高也会影响数据库的性能。默认情况下，查询系统表都会对统计信息进行刷新，这个时候是不必要的，设为OFF。
5.2 第三方配置工具
MySQL Configuration Wizard，现在该工具已经集成在了 Percona Toolkit 里面
5.3 服务器硬件优化
5.3.1 如何选择CPU
是选择单核更快的CPU还是选择核数更多的CPU？
- MySQL有一些工作只能使用到单核CPU
- 一条SQL语句的执行
- Replicate 复制过程中的复制进程
- MySQL对CPU核数的支持并不是越多越快
- MySQL 5.5 使用的服务器不要超过32核（有国外的网站进行过测试，当服务器CPU超过32核，5.5版本的性能反而有所下降）
5.3.2 磁盘I/O优化
常用RAID级别：
-
RAID0：条带，把多个磁盘链接成一个磁盘使用，这个级别I/O效果最好。但是这种方式的安全性不好，一旦某一个条带损坏，那么整个数据就会丢失。
-
RAID1：镜像，要求至少有两个磁盘，每组磁盘存储的数据相同。
-
RAID5：把至少3个硬盘合并成1个逻辑盘使用，数据读写时会建立奇偶校验信息，并且该信息和相对应的数据分别存储于不同的磁盘上。当RAID5的一个磁盘数据损坏，利用剩下的数据和相应的奇偶校验信息可以恢复数据。
-
RAID1+0：即RAID1和RAID0的结合，同时具备两个级别的优缺点，既保证了安全性，又增加了磁盘的读写效率。一般建议数据库使用这个级别。
SNA（网络区域存储） 和 NAT（存储区域网络） 是否适合数据库？
- 常用于高可用解决方案
- 如果一台服务器死机了，那么另一台服务器接管这个磁盘矩阵来进行访问。
- 顺序读写效率很高，但是随机读写不如人意
- 数据库大部分进行的随机读写，因此使用这种优化对I/O效率并没有好处
- 数据库随机读写比率很高
