[大数据]性能优化之MySQL优化

第1章 数据库优化简介

数据库优化的目的

避免出现页面访问错误

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

增加数据库的稳定性

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

优化用户体验

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

数据库优化的方式

1 SQL及索引（提升性能最大，效率最高）
2 数据库表结构（根据范式设计表结构）
3 系统配置（linux对mysql打开文件数的限制等）
4 硬件（提高文件io的速度，但是成本最高）
io的优化 并不能减少阻塞 ，但sql索引没有优化好，则会产生大量的慢查询或阻塞，这是由于mysql的内部锁机制造成，所以硬件再好效果也不大
mysql对内核是有限制的

第2章 SQL语句优化

SELECT语句 - 语法顺序：

1. SELECT 
2. DISTINCT <select_list>
3. FROM <left_table>
4. <join_type> JOIN <right_table>
5. ON <join_condition>
6. WHERE <where_condition>
7. GROUP BY <group_by_list>
8. HAVING <having_condition>
9. ORDER BY <order_by_condition>
10.LIMIT <limit_number>

SELECT语句 - 执行顺序：

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join...> 
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
# 数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

2-1 数据准备

演示数据库说明：
使用MySQL提供的sakila数据库，可以通过以下URL获取这个演示数据库
http://dev.mysql.com/doc/index-other.html
sakila数据库的表结构信息可以通过以下网站查看
http://dev.mysql.com/doc/sakila/en/sakila-installation.html
数据库基于MySQL5.5版本,不同MySQL版本的优化器有一定的差别。
导入数据:
1.下载sakila数据库
http://dev.mysql.com/doc/index-other.html
解压:

2.导入
CMD 命令窗口连接MySQL $> mysql -u root -p

创建数据库结构
mysql> source /home/tom/mysql/sakila-schema.sql
将数据写入到数据库
mysql> source /home/tom/mysql/sakila-db/sakila-data.sql

Linux下


mysql> select @@version;
±----------+
| @@version |
±----------+
| 5.7.23 |
±----------+

mysql> USE sakila;
Database changed

mysql> SHOW FULL TABLES;
+----------------------------+------------+
| Tables_in_sakila           | Table_type |
+----------------------------+------------+
| actor                      | BASE TABLE |
| actor_info                 | VIEW       |
| address                    | BASE TABLE |
| category                   | BASE TABLE |
| city                       | BASE TABLE |
| country                    | BASE TABLE |
| customer                   | BASE TABLE |
| customer_list              | VIEW       |
| film                       | BASE TABLE |
| film_actor                 | BASE TABLE |
| film_category              | BASE TABLE |
| film_list                  | VIEW       |
| film_text                  | BASE TABLE |
| inventory                  | BASE TABLE |
| language                   | BASE TABLE |
| nicer_but_slower_film_list | VIEW       |
| payment                    | BASE TABLE |
| rental                     | BASE TABLE |
| sales_by_film_category     | VIEW       |
| sales_by_store             | VIEW       |
| staff                      | BASE TABLE |
| staff_list                 | VIEW       |
| store                      | BASE TABLE |
+----------------------------+------------+
23 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM film;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT COUNT(*) FROM film_text;
+----------+
| COUNT(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

2-2 MySQL慢查日志的开启方式和存储格式

SQL及索引优化
相关参数说明:

• show_query_log_file：慢查日志存储位置
• log_queries_not_use_indexes：是否把没有使用索引的查询记录到慢查询日志中
• long_query_time：超过多少秒的查询记录到慢查询日志中

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

• show variables like ‘slow_query_log’; //查看是否开启慢查日志
• set global slow_query_log_file = ‘xxx’ //设置慢查日志的文件地址
• set global log_queries_not_using_indexes=on; //是否把没有使用sql索引记录到慢查日志中
• set global long_query_time=1; //设置慢查日志的时间，查寻超过多少秒记录(单位：秒)

案例:
show variables like ‘slow_query_log’; //查看是否开启慢查日志

mysql> show variables like 'slow_query_log';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| slow_query_log | OFF   | 值是OFF,说明没有开启该功能
+----------------+-------+
1 row in set, 1 warning (0.00 sec)

查看是否将没有建立索引的查询列入慢查询记录:
show variables like ‘%log%’;
显示的结果中有一项"log_queries_not_using_indexes"值为off, 说明没有开启 使用下面的命令开启:
set global log_queries_not_using_indexes=on; //是否把没有使用sql索引记录到慢查日志中

mysql> set global log_queries_not_using_indexes=on;
Query OK, 0 rows affected (0.00 sec)

查看慢查询时间设置:
show variables like ‘long_query_time’;

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

设置慢查询时间设置:
set global long_query_time=0;
set global long_query_time=0; 为啥无效呢，再查还是原来的10
修改之后，先关闭数据库连接，再重新连接，再次查询就可以看到实际上是修改了的。
开启慢查询日志
set global slow_query_log=on;

mysql> set global slow_query_log=on;

查看慢查询日志文件位置
show variables like ‘slow%’;

mysql> show variables like 'slow%';
+---------------------+-------------------------------------------------------+
| Variable_name       | Value                                                 |
+---------------------+-------------------------------------------------------+
| slow_launch_time    | 2                                                     |
| slow_query_log      | ON                                                    |
| slow_query_log_file | D:\development\MySQL5.7\data\LAPTOP-9GHMNQJ6-slow.log | 存放位置
+---------------------+-------------------------------------------------------+

# Linux下
mysql> show variables like 'slow%';
+---------------------+-------------------------------------+
| Variable_name       | Value                               |
+---------------------+-------------------------------------+
| slow_launch_time    | 2                                   |
| slow_query_log      | ON                                  |
| slow_query_log_file | /var/lib/mysql/cjbCentos01-slow.log |
+---------------------+-------------------------------------+
3 rows in set (0.00 sec)

2-3 MySQL慢查日志分析工具之mysqldumpslow

Windows下安装使用mysqldumpslow
不建议安装使用,可能会导致某些系统问题

Windows下使用

D:\development\Strawberry\perl\bin>perl mysqldumpslow.pl -h

D:\development\Strawberry\perl\bin>perl mysqldumpslow.pl -h
Option h requires an argument
ERROR: bad option

 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（返回前面n条数据）
   -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

Linux 下使用

mysqldumpslow -h 查看分析工具的参数
mysqldumpslow工具的使用：
1、mysqldumpslow -t 3 慢查询日志文件路径 | more //表示使用mysqldumpslow工具分析3条慢查询语句
mysqldumpslow -t 3 /var/lib/mysql/cjbCentos01-slow.log | more

[root@cjbCentos01 mysql]# mysqldumpslow -t  3 /var/lib/mysql/cjbCentos01-slow.log | more

Reading mysql slow query log from /var/lib/mysql/cjbCentos01-slow.log
Count: 2  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=2.0 (4), root[root]@localhost
  show variables like 'S'

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=1.0 (1), root[root]@localhost
  select @@version_comment limit N

Count: 1  Time=0.00s (0s)  Lock=0.00s (0s)  Rows=0.0 (0), root[root]@localhost
  mysqldumpslow -h

2、报表内容：
Count：执行的行数
Time: 执行的时间
Lock: 锁定时间
Rows: 行数
服务器信息
SQL内容等

2-4 MySQL慢查日志分析工具之pt-query-digest

MySQL慢查询（二） - pt-query-digest详解慢查询日志

安装pt-query-digest

下载pt-query-disgest 、 授权 、 将其放到/usr/bin下：

wget percona.com/get/pt-query-digest
chmod u+x pt-query-digest
mv pt-query-digest /usr/bin/

可能需要的安装依赖工具：

yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
yum install perl-Digest-MD5 #我的需要是这个

或者安装 percona-toolkit 套件

需要安装。最好安装这个套件：
wget http://www.percona.com/downloads/percona-toolkit/2.2.4/percona-toolkit-2.2.4.tar.gz
tar -zxvf percona-toolkit-2.2.4.tar.gz
cd percona-toolkit-2.2.4
perl Makefile.PL
make && make install

如果报错(Can't locate Time/HiRes.pm in @INC (@INC contains....)的话，
yum -y install perl-Time-HiRes 或者 yum install -y perl-CPAN perl-Time-HiRes

使用 pt-query-digest

使用帮助: pt-query-digest --help

pt-query-digest语法及重要选项

pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table  当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table  当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter  对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit    限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host  mysql服务器地址
--user  mysql用户名
--password  mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
--until 截止时间，配合—since可以分析一段时间内的慢查询。

案例:
pt-query-digest /var/lib/mysql/cjbCentos01-slow.log | more
输出到文件：pt-query-digest show-log > slow_log.report
输出到数据库表 pt-query-digest show.log -review
h=127.0.0.1,D=test,p=root.P=3306,u=root,t=query_review
–create-reviewtable
–review-history t=hostname_show
pt-query-digest慢查询日志的输出有三部分

分析pt-query-digest输出结果

第一部分：总体统计结果
Overall：总共有多少条查询
Time range：查询执行的时间范围
unique：唯一查询数量，即对查询条件进行参数化以后，总共有多少个不同的查询
total：总计 min：最小 max：最大 avg：平均
95%：把所有值从小到大排列，位置位于95%的那个数，这个数一般最具有参考价值
median：中位数，把所有值从小到大排列，位置位于中间那个数

# 该工具执行日志分析的用户时间，系统时间，物理内存占用大小，虚拟内存占用大小
# 340ms user time, 140ms system time, 23.99M rss, 203.11M vsz
# 工具执行时间
# Current date: Fri Nov 25 02:37:18 2016
# 运行分析工具的主机名
# Hostname: localhost.localdomain
# 被分析的文件名
# Files: slow.log
# 语句总数量，唯一的语句数量，QPS，并发数
# Overall: 2 total, 2 unique, 0.01 QPS, 0.01x concurrency ________________
# 日志记录的时间范围
# Time range: 2016-11-22 06:06:18 to 06:11:40
# 属性               总计      最小    最大    平均    95%  标准    中等
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# 语句执行时间
# Exec time             3s   640ms      2s      1s      2s   999ms      1s
# 锁占用时间
# Lock time            1ms       0     1ms   723us     1ms     1ms   723us
# 发送到客户端的行数
# Rows sent              5       1       4    2.50       4    2.12    2.50
# select语句扫描行数
# Rows examine     186.17k       0 186.17k  93.09k 186.17k 131.64k  93.09k
# 查询的字符数
# Query size           455      15     440  227.50     440  300.52  227.50

第二部分：查询分组统计结果
Rank：所有语句的排名，默认按查询时间降序排列，通过–order-by指定
Query ID：语句的ID，（去掉多余空格和文本字符，计算hash值）
Response：总的响应时间
time：该查询在本次分析中总的时间占比
calls：执行次数，即本次分析总共有多少条这种类型的查询语句
R/Call：平均每次执行的响应时间
V/M：响应时间Variance-to-mean的比率
Item：查询对象

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0xF9A57DD5A41825CA  2.0529 76.2%     1 2.0529  0.00 SELECT
#    2 0x4194D8F83F4F9365  0.6401 23.8%     1 0.6401  0.00 SELECT wx_member_base

第三部分：每一种查询的详细统计结果
由下面查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
ID：查询的ID号，和上图的Query ID对应
Databases：数据库名
Users：各个用户执行的次数（占比）
Query_time distribution ：查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables：查询中涉及到的表
Explain：SQL语句

# Query 1: 0 QPS, 0x concurrency, ID 0xF9A57DD5A41825CA at byte 802 ______
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2016-11-22 06:11:40
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count         50       1
# Exec time     76      2s      2s      2s      2s      2s       0      2s
# Lock time      0       0       0       0       0       0       0       0
# Rows sent     20       1       1       1       1       1       0       1
# Rows examine   0       0       0       0       0       0       0       0
# Query size     3      15      15      15      15      15       0      15
# String:
# Databases    test
# Hosts        192.168.8.1
# Users        mysql
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms
#    1s  ################################################################
#  10s+
# EXPLAIN /*!50100 PARTITIONS*/
select sleep(2)\G

解决:
1.查询时间长,查询次数多
2.IO大的sql,分析Rows Examine项,扫描的行数
3.未命中索引的sql,分析Rows Examine与Rows send发送的行数的对比

2-5 如何通过慢查日志发现有问题的SQL

1.查询次数多且每次查询占用时间长的SQL
通常为pt-query-digest分析的前几个查询
2.IO大的SQL
注意pt-query-digest分析中的Rows examine项
3.未命中索引的SQL
注意pt-query-digest分析中的Rows examine和Row send 的对比
当Rows examine>>Row send 基本上是使用了索引扫描或者表扫描的方式来进行查询，需要进行优化

2-6 通过explain查询和分析SQL的执行计划

mysql> explain select customer_id,first_name,last_name from customer;
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
| id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | customer | ALL  | NULL          | NULL | NULL    | NULL |  599 | NULL  |
+----+-------------+----------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)

explain 返回各列的含义
select_type：
表示 select 查询的类型，主要是用于区分各种复杂的查询，例如：普通查询、联合查询、子查询等。

1. SIMPLE：表示最简单的 select 查询语句，在查询中不包含子查询或者交并差集等操作。
2. PRIMARY：查询中最外层的SELECT（存在子查询的外层的表操作为PRIMARY）。
3. SUBQUERY：子查询中首个SELECT。
4. DERIVED：被驱动的SELECT子查询（子查询位于FROM子句）。
5. UNION：在SELECT之后使用了UNION。

table：显示这一行的数据时关于哪张表的
type：显示连接使用了何种类型 const、eq_reg、ref、range、index和ALL；const：主键、索引；eq_reg：主键、索引的范围查找；ref：连接的查找（join），range：索引的范围查找；index：索引的扫描；
possible_keys：显示可能应用在这张表中的索引，如果为空，没有可能的索引
key：实际使用的索引，如果为null，没有使用索引
key_len：使用的索引的长度，在不损失精确性的情况下，长度越短越好
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
rows：mysql认为必须检查的用来返回请求数据的行数；
extra列需要注意的返回值
using filesort：mysql需要进行额外的步骤来发现如果对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行
using temporary：MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行order by上，而不是group by 上

其中type是重要的列，以下从最好到最差：
const：是一个常数查找，一般是主键和唯一索引查找
eq_reg：主键和唯一索引的范围查找
ref：连接的查找，一般一个表是基于某一个索引的查找
range：基于索引的范围查找
index：基于索引的扫描
all：基于表扫描

2-7 Count()和Max()的优化

1.对max()查询，可以为表创建索引

利用max方法查询最后一笔交易的时间
explain select max(payment_date) from payment \G
\g: 定界符，等价于“；”
\G: 将查询到的横向表格纵向输出，方便阅读

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
   partitions: NULL
         type: ALL	# 全表扫描
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 16086
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

这里就是一个表扫描操作，一共扫描了16086行数据。如果数据表很大，这里的IO效率就会很差
优化方法：max(field)可以通过为field建立索引 来优化
语法: create index index_name on table_name(column_name 规定需要索引的列)
create index idx_paydate on payment(payment_date);
优化后：

mysql> explain select max(payment_date) from payment \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
   partitions: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
     filtered: NULL
        Extra: Select tables optimized away
1 row in set, 1 warning (0.00 sec)

优化之后并不需要查询表中的数据，而是通过索引就可以知道执行的结果了。
因为索引是顺序排列的，只需要查最后一个数据。这样就尽可能减少了IO操作。
而且这时候，不管表数据量有多大，查询max所需要的时间是基本固定的

2.count()对多个关键字进行查询

比如在一条SQL中同时查出2006年和2007年电影的数量，语句：

select count(release_year='2006' or null) as '2006年电影数量',
       count(release_year='2007' or null) as '2007年电影数量'
from film;

这里解释一下为什么要加’or null’：
count()函数只有count(NULL)是才不计数，即count(NULL)=0；
而’release_year=‘2006’'是个比较运算符，结果为1或者0，不管是不是2006 count函数都会计数。
当加上or null以后，当值不为2006，release_year='2006’的结果为0, '0 or null’结果为null，这样就排除了2006以外的结果
count(*) 和 count(field) 的区别
count(*) 查询的结果中，包含了该列值为null的结果

2-8 子查询的优化

在子查询的优化中：
通常做法是把需要的子查询优化为join查询，但在优化时要注意是否有数据的重复，因为在关联语句中的可能存在一对多的关系，从而造成数据冗余。
join语句是相当于将多个表进行关联，在关联条件上一一进行条件匹配查询，因此返回值不仅取决于原始表中的数据个数，还取决于其他表中与之匹配的数据的个数。
所以要加上distinct

select distinct t.id from t join t1 on t.id=t1.tid;

2-9 group by的优化

group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。
可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io

1. using()用于两张表的join查询，要求using()指定的列在两个表中均存在，并使用之用于join的条件;例如: select a., b. from a left join b using(colA);
2. group by 的列尽量要使用在有索引的列上,否则就会使用临时表和文件

改写前
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;

改写后
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);

改写前

mysql> 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 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 100.00
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
   partitions: NULL
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 27
     filtered: 100.00
        Extra: Using index
2 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

改写后

mysql> 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) \G;
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: actor
   partitions: NULL
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 27
     filtered: 100.00
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film_actor
   partitions: NULL
         type: index
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5462
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.00 sec)

ERROR: 
No query specified

2-10 Limit查询的优化

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题
1.使用有索引的列或主键进行order by操作
2.记录上次返回的主键，在下次查询时使用主键过滤
优化limit查询
例如: select film_id,description from sakila.film order by title limit 50,5; 语句的优化
这条语句的意思根据 title 字段升序，然后取从第51-55行的数据

mysql> explain select film_id,description from sakila.film order by title limit 50,5 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

• 优化步骤1:使用有索引的列或这件进行order by操作
  • select film_id,description from sakila.film order by film_id limit 50,5;
  • (优化前是order by title非主键，优化后是order by film_id,是主键)

mysql> explain select film_id ,description from sakila.film order by film_id limit 50,5 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 55
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.01 sec)

• 优化步骤2:记录上次返回的主键,在下次查询时用 主键过滤(避免了数据量大时扫描过多的记录)
  • select film_id,description from sakila.film where film_id >50 order by film_id limit 5;

mysql> explain select film_id,description from sakila.film where film_id >50 order by film_id limit 5 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 500
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了
【推荐】使用下面这种方式:

【推荐】利用延迟关联或者子查询优化超多分页场景。
说明：MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
正例：先快速定位需要获取的id段，然后再关联：
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id

SELECT a.film_id,a.description FROM 
sakila.film a, (SELECT film_id from sakila.film LIMIT 500,5) b where a.film_id=b.film_id;

第3章 索引优化

3-1 如何选择合适的列建立索引

选择合适的索引列

1.在where，group by，order by，on从句中出现的列
2.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )
3.离散度大的列放在联合索引前面
select count(distinct customer_id), count(distinct staff_id) from payment;
是index(sftaff_id,customer_id)好？还是index(customer_id,staff_id)好呢？

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.01 sec)

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

离散度的判断

查看表的数据结构信息： desc 表名；
离散度：数据唯一值越多，离散度越高。
判断某一列的离散度：select count(distinct 字段名）from 表名；返回的结果值越大，说明离散度越大，建立联合索引时，应该放到前面；
例如：select count(distinct id),count(distinct real_name) from user;
id 是唯一值，所以离散度更高。而 real_name 会出现重复—>会有同名同姓的情况，所以离散度低。

3-2 索引优化SQL的方法

重复及冗余索引

过多的索引不但影响写入，而且影响查询，索引越多，分析越慢
如何找到重复和多余的索引，主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了
冗余索引，是指多个索引的前缀列相同，innodb会在每个索引后面自动加上主键信息

冗余索引查询工具
pt-duplicate-key-checker

查找重复及冗余索引

通过统计信息库 information_schma 查找一些重复冗余的索引
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-checher 工具检查重复及冗余索引

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

3-3 索引维护的方法

索引的维护及优化—删除不用索引
目前MySQL中还没有记录索引的使用情况,但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来查看那些索引未使用,但在MySQL中目前只能通过慢查日志配合 pt-index-usage 工具来进行索引使用情况的分析。

第4章 数据库结构优化

4-1 选择合适的数据类型

1.使用可存下数据的最小的数据类型
2.使用简单地数据类型，Int要比varchar类型在mysql处理上更简单
3.尽可能使用not null定义字段，这是由innodb的特性决定的，因为非not null的数据可能需要一些额外的字段进行存储，这样就会增加一些IO。可以对非null的字段设置一个默认值
4.尽量少用text，非用不可最好分表，将text字段存放到另一张表中，在需要的时候再使用联合查询，这样可提高查询主表的效率

例子1、用Int存储日期时间

from_unixtime() 可将Int类型的时间戳转换为时间格式
select from_unixtime(1392178320); 输出为 2014-02-12 12:12:00
unix_timestamp() 可将时间格式转换为Int类型
select unix_timestamp(‘2014-02-12 12:12:00’); 输出为1392178320

mysql> create table testtime(id int auto_increment not null,timestr int,PRIMARY KEY(id));
Query OK, 0 rows affected (0.01 sec)

mysql> desc testtime;
+---------+---------+------+-----+---------+----------------+
| Field   | Type    | Null | Key | Default | Extra          |
+---------+---------+------+-----+---------+----------------+
| id      | int(11) | NO   | PRI | NULL    | auto_increment |
| timestr | int(11) | YES  |     | NULL    |                |
+---------+---------+------+-----+---------+----------------+
2 rows in set (0.01 sec)
mysql> insert into testtime(timestr) values(UNIX_TIMESTAMP('2014-06-01 21:00:00'));
Query OK, 1 row affected (0.00 sec)

mysql> select FROM_UNIXTIME(timestr) from testtime;
+------------------------+
| FROM_UNIXTIME(timestr) |
+------------------------+
| 2014-06-01 21:00:00    |
+------------------------+
1 row in set (0.00 sec)

例子2、存储IP地址——bigInt

利用 inet_aton() , inet_ntoa() 转换
select inet_aton(‘192.169.1.1’); 输出为3232301313
select inet_ntoa(3232301313); 输出为192.169.1.1

4-2 数据库表的范式化优化

表的范式化即数据库设计的规范化：数据表不存在非关键字段对任意关键字段的传递函数依赖，则符合第三范式。
可以将一张数据表进行拆分，来满足第三范式的要求。
设计表的时候符合范式化是为了：减少数据冗余、减少表的插入、更新、删除异常
设计表的时候使用反范式化是为了：以空间换时间、增强代码的可编程性和可维护性
不符合第三范式要求的表存在以下问题：
1.数据冗余:（分类、分类描述）对于每一个商品都会进行记录
2.数据插入异常
3.数据更新异常
4.数据删除异常(如果删除了所有饮料类的商品，就查询不到分类，分类描述了)

分类描述依赖于分类，分类又依赖于商品名称，而商品名称为主键——关键字段
当不符合第三范式的时候，可以拆分表为两份表，用之前的非关键字列作为新表的关键字，然后建立起新表和旧表的关联表

4-3 数据库表的反范式化优化

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

为提高io读的效率，牺牲一些存储空间的代价。但是提高了读取数据的效率。
如用户常常会大量查询订单的信息，那么把用户名，电话，地址和订单价格放入一个订单表中，虽然违反了第三范式，因为订单id，用户名，电话，地址等，存在传递函数依赖，但是由于数据都是在一张表中，方便了sql语句实现，订单信息的读取，从而优化了io性能；

4-4 数据库表的垂直拆分

表的垂直拆分的原则
所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表解决表的宽度问题，通常拆分原则如下：
1、把不常用的字段单独存放到一个表中
2、把大字段独立存放到一个表中
3、把经常一起使用的字段放到一起
当表的宽度过宽的时候，我们需要对表进行垂直拆分，具体的建议如下（原则上是人以群分，列以表分）：一表变多表，物理上不在一起，逻辑上是在一起的！

拆分后
把 title 和 description 通过 film_id 建立一个新表

4-5 数据库表的水平拆分

表的水平拆分: 主要是解决数据量过大的问题,水平拆分每个表的表结构都是完全一致的(当单表的数据大于一亿时,尽管加了索引,还是会比较慢);

常用的方法是：hash取模拆分

1、对大表的主键id值进行hash运算，比如要拆分为5张表，可以使用mod(主键id，5）取出0-4个值
2、针对不同的hashID把数据存到不同的表中。
挑战：

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

垂直拆分与水平拆分区别:

水平拆分 解决数据量的问题 垂直拆分解决表的宽度问题，后台用总表，前台用拆分表
垂直拆分是拆字段 水平拆分是表结构不变 把数据给分开了

第5章 系统配置优化

5-1 数据库系统配置优化

1. 网络方面

• 增加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

2. 打开文件数的限制

修改/etc/security/limits.conf文件，增加一下内容以修改打开文件数量的限制。
关闭iptables，selinux等防火墙软件

5-2 MySQL配置文件优化

/usr/sbin/mysqld - -verbose - -help | grep -A 1 'Default options ’
查看MySQL配置文件路径，如果存在多个配置文件，则后面的会覆盖前面的

• innodb_buffer_pool_size

重要，缓冲池的大小 推荐总内存量的75%，越大越好。

select engine,
round(sum(data_length+index_length)/1024/1024,1) as "Total MB"
FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN 
("inoformation_schema","performance_schema")
Group by engine;

mysql> select engine,
    -> round(sum(data_length+index_length)/1024/1024,1) as "Total MB"
    -> FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA NOT IN 
    -> ("inoformation_schema","performance_schema")
    -> Group by engine;
+--------+----------+
| engine | Total MB |
+--------+----------+
| NULL   |     NULL |
| CSV    |      0.0 |
| InnoDB |      9.0 |
| MEMORY |      0.0 |
| MyISAM |      0.3 |
+--------+----------+
5 rows in set (0.11 sec)

• innodb_buffer_pool_instances

默认只有一个缓冲池，如果一个缓冲池中并发量过大，容易阻塞，此时可以分为多个缓冲池；

• innodb_log_buffer_size

log缓冲的大小，一般最常1s就会刷新一次，故不用太大；
?

• innodb_flush_log_at_trx_commit

重要，对io效率影响较大。
0: 1s刷新一次到磁盘；
1：每次提交都会刷新到磁盘；
2：每次提交刷新到缓冲区，1s刷新到磁盘；默认为1。

?

• innodb_read_io_threads
• innodb_write_io_threads

读写的io进程数量，默认为4

• innodb_file_per_table

重要，控制每个表使用独立的表空间，默认为OFF，即所有表建立在一个共享的表空间中。造成IO瓶颈。推荐设置ON
?

• innodb_stats_on_metadata

mysql在什么情况下会刷新表的统计信息，一般为OFF。
?

5-3 第三方配置工具使用

mysql第三方配置工具:
https://tools.percona.com/wizard

第6章 服务器硬件优化

6-1 服务器硬件优化

服务器硬件优化:
1.cpu选择:mysql一个sql的执行只能用到单核的cpu,其次,在复制进程的时候也是只能用到单核的cpu,所以cpu并不是越多越好,mysql5.5是的服务器不要超过32核,偏向选择单核频率更快的cpu;
2.Disk IO 优化

常用的RAID 级别简介
RAID0: 也称为条带,多个磁盘接成一个使用,io最好(但是磁盘坏了,数据没了,安全性差)
RAID1: 也称镜像,要求至少两个磁盘,每组磁盘存储的数据相同;
RAID1+0: 就是RAID1和RAID0的结合,同时具备两个级别的优缺点,推荐数据库使用这个级别;
目前可能还有更好的存储设备:比如ssd卡