2016年11月16日 (三) 05:03的版本

相关文章：高性能MySQL笔记（下）

前言

淘宝的MySQL博客月报 首页
MySQL 5.7数据库参数优化

书中代码

下载：文件:Mysql高性能 随书源码.zip 官网链接： http://www.highperfmysql.com/#downloads

mysql示例数据库

http://dev.mysql.com/doc/index-other.html

mysqlreport

#安装
yum install mysqlreport
yum install perl-DBD-MySQL
#运行
mysqlreport --user root --password YourPassword --socket=/tmp/mysql.sock

文件:Mysqlreport20160901.log

准备

环境变量（我的LINUX服务器），若要长期有效可将以下脚本追加在 /etc/profile中

export MYSQL_HOME=/usr/local/mysql/
export C_INCLUDE_PATH=$MYSQL_HOME/include
export PATH=$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=$MYSQL_HOME/lib

我的MAC:

 export PATH=/Applications/XAMPP/xamppfiles/bin:$PATH

第二章 MySQL基准测试

2.3 基准测试方法

2.3.3 获取系统性能和状态

收集MySQL测试数据的Shell脚本（P43） gather.sh：

#!/bin/sh
INTERVAL=5
PREFIX=$INTERVAL-sec-status
RUNFILE=/home/benchmarks/running
mysql -e 'SHOW GLOBAL VARIABLES' >> mysql-variables
while test -e $RUNFILE; do
  file=$(date +%F_%I)
  sleep=$(date +%s.%N | awk "{print $INTERVAL - (\$1 % $INTERVAL)}")
  sleep $sleep
  ts="$(date +"TS %s.%N %F %T")"
  loadavg="$(uptime)"
  echo "$ts $loadavg" >> $PREFIX-${file}-status
  mysql -e 'SHOW GLOBAL STATUS' >> $PREFIX-${file}-status &
  echo "$ts $loadavg" >> $PREFIX-${file}-innodbstatus
  mysql -e 'SHOW ENGINE INNODB STATUS\G' >> $PREFIX-${file}-innodbstatus &
  echo "$ts $loadavg" >> $PREFIX-${file}-processlist
  mysql -e 'SHOW FULL PROCESSLIST\G' >> $PREFIX-${file}-processlist &
  echo $ts
done
echo Exiting because $RUNFILE does not exist.

2.3.5 运行基准测试并分析结果

以下脚本实现了从前脚本输出的数据中抽取时间维度的信息 analyze.sh： ./analyze.sh 5 -sec-status-2016-08-31

#!/bin/sh
# This script converts SHOW GLOBAL STATUS into a tabulated format, one line
# per sample in the input, with the metrics divided by the time elapsed
# between samples.
awk '
  BEGIN {
    printf "#ts date time load QPS";
    fmt = " %.2f";
  }
  /^TS/ { # The timestamp lines begin with TS.
    ts = substr($2, 1, index($2, ".") - 1);
    load = NF - 2;
    diff = ts - prev_ts;
    prev_ts = ts;
    printf "\n%s %s %s %s", ts, $3, $4, substr($load, 1, length($load)-1);
  }
  /Queries/ {
    printf fmt, ($2-Queries)/diff;
    Queries=$2
  }
  ' "$@"

2.4 MySQL的BENCHMARK()函数(P52)

 SET @INPUT = 'hello world';
 SELECT benchmark(1000000, md5(@INPUT));
 SELECT benchmark(1000000, sha1(@INPUT));

2.5基准测试案例

2.5.1 http_load

安装： install_http_load.sh

wget http://www.acme.com/software/http_load/http_load-12mar2006.tar.gz
tar xzvf http_load-12mar2006.tar.gz
cd http_load-12mar2006/
make && make install

测试：

http_load -p 1 -seconds 10 urls.txt
http_load -parallel 1 -seconds 10 -f 10 urls.txt
 
#其中urls.txt如下：
http://www.jb51.net/category/php/
http://www.jb51.net/php/iterate-algorithm.html
http://www.jb51.net/tag/web-safe/

2.5.2 MySQL基准测试套件

 cd /usr/local/mysql/sql-bench/
 ./run-all-tests  --user=root --password=YourPassword --socket=/tmp/mysql.sock

以下是输出(/usr/local/mysql/sql-bench/output-20160824)：

-rw-r--r-- 1 root root  772 8月  24 10:11 alter-table-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
-rw-r--r-- 1 root root 1.1K 8月  24 10:12 ATIS-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
-rw-r--r-- 1 root root  854 8月  24 10:13 big-tables-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
-rw-r--r-- 1 root root 1.7K 8月  24 10:14 connect-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
-rw-r--r-- 1 root root  900 8月  24 10:24 create-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64
-rw-r--r-- 1 root root 6.7K 8月  24 13:06 insert-mysql-Linux_2.6.32_431.23.3.el6.x86_64_x86_64

2.5.3 sysbench命令

#CPU test
sysbench --test=cpu --cpu-max-prime=20000 run
 
 
 
#IO test
sysbench --test=fileio --file-total-size=15G prepare
sysbench --test=fileio --file-total-size=15G --file-test-mode=rndrw --init-rng=on --max-time=300 --max-requests=0 run
sysbench --test=fileio --file-total-size=15G cleanup
 
 
#OLTP test
sysbench --test=oltp --mysql-db=test --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock  --mysql-user=root --mysql-password=YourPassword --mysql-table-engine=innodb --oltp-table-size=1000000  prepare
 
sysbench --test=oltp --mysql-db=test --db-driver=mysql --mysql-host=localhost --mysql-socket=/tmp/mysql.sock  --mysql-user=root --mysql-password=YourPassword --mysql-table-engine=innodb --oltp-table-size=1000000  --max-time=60 --oltp-read-only=on --max-requests=0 --num-threads=8 run

2.5.5 Persona 的TCPP-MySQL测试工具

安装(在shell中执行)

wget https://github.com/Percona-Lab/tpcc-mysql 
unzip master.zip
 
#准备环境变量
export MYSQL_HOME=/usr/local/mysql/
export C_INCLUDE_PATH=$MYSQL_HOME/include
export PATH=$MYSQL_HOME/bin:$PATH
export LD_LIBRARY_PATH=$MYSQL_HOME/lib
 
cd tpcc-mysql-master/src
make all
cd ..

测试(在自己的测试服务器上运行了3个小时)

mysqladmin -pYourPassword create tpcc1000
mysql -pYourPassword tpcc1000 < create_table.sql
mysql -pYourPassword  tpcc1000 < add_fkey_idx.sql
 
./tpcc_load -h127.0.0.1 -d tpcc1000 -u root -p "YourPassword" -w 10
#可以备个份，方便下次测试
/usr/local/mysql/bin/mysqldump -uroot -pYourPassword  tpcc1000> tpcc1000.sql
 
./tpcc_start -h127.0.0.1 -P3306 -dtpcc1000 -uroot -w10 -c32 -r10 -l10800 -pYourPassword

查看数据库统计情况（自己总结的语句）

SELECT TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 AS INDEX_MB ,(data_length+index_length)/1024/1024 AS TOTAL_MB,TABLE_COLLATION   FROM information_schema.TABLES WHERE table_schema='tpcc1000' ORDER BY TABLE_ROWS DESC;

第三章 服务器性能剖析

mysql示例数据库

安装Persona Toolkit

#参考 http://www.cnblogs.com/qiyebao/p/4766416.html
#参考 http://blog.chinaunix.net/uid-20639775-id-3206802.html
#安装
yum install perl-DBI
yum install perl-DBD-MySQL
yum install perl-Time-HiRes
yum install perl-IO-Socket-SSL
#yum install perl-TermReadKey
 
rpm -ivh  percona-toolkit-2.2.19-1.noarch.rpm

3.2对应用程序进行性能剖析

• New Relic
• xhprof

3.3 剖析MySQL查询

打开慢查询

编辑 my.conf 在[mysqld]下方加入慢查询的配置语句：

slow_query_log = true
long_query_time = 1
slow_query_log_file = /data/mysql.slow.log

注： 在我的MAC上则是如下路径：slow_query_log_file = /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log

在MySQL5.6之前还可用如下方式（5.6以后废弃）

log-slow-queries = /data/mysql.slow.log
long_query_time = 1

参考

http://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html 
http://blog.csdn.net/lwprain/article/details/8802379

如果要长期开启慢查询日志，注意要部署日志轮换（log rotation）工具

分析慢查询

pt-query-digest slow.log

tcpdump

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt

文件:Pt-query-digest--tcpdump20160901.log

3.3.2 剖析单条性能

使用SHOW PROFILE （未来可能被Performance Schema取代）

ShowProfile示例

在会话级别使用

SET profiling = 1;
SELECT * FROM sakila.nicer_but_slower_film_list;
SHOW profiles;
SHOW profile FOR query 2;

以上是默认输出，若要灵活显示则执行以下语句（结果如右图--> ）：

SET profiling = 1;
SELECT * FROM sakila.nicer_but_slower_film_list;
SET  @query_id = 2;
SELECT state ,SUM(duration) AS total_r ,
      round(
        100 * SUM(duration) /
          (SELECT SUM(duration)
           FROM information_schema.profiling
           WHERE query_id = @query_id
        ),2 ) AS pct_r ,
      COUNT(*) AS calls,
      SUM(duration) / COUNT(*) AS "R/Call"
     FROM information_schema.profiling
     WHERE query_id =  @query_id
     GROUP BY state
     ORDER BY total_r DESC;

使用SHOW STATUS

show status 示例

SHOW STATUS示例（见右图-->）

FLUSH STATUS;
SELECT * FROM sakila.nicer_but_slower_film_list;
SHOW STATUS WHERE Variable_name LIKE 'Handler%'
OR Variable_name LIKE 'Created%';

注：show status 也会创建一个临时表，从而影响结果中的数字（而且不同版本可能的行为也不尽相同）。比较前面通过show profiles获得的查询的执行计划的结果来看，至少临时表的计数器多加了2.

附：查看sakila数据库中各表行数

SELECT TABLE_NAME, TABLE_ROWS,data_length/1024/1024 AS DATA_MB,index_length/1024/1024 AS INDEX_MB ,(data_length+index_length)/1024/1024 AS TOTAL_MB,TABLE_COLLATION   FROM information_schema.TABLES WHERE table_schema='sakila' ORDER BY TABLE_ROWS DESC;

使用Performance Schema (>= MySQL5.5)

以下显示系统等待的主要原因

SELECT event_name, count_star, sum_timer_wait  FROM `performance_schema`.events_waits_summary_global_by_event_name ORDER BY sum_timer_wait DESC LIMIT 5;

结果

3.4诊断间歇性问题

3.4.1 单条问题还是服务器问题

使用SHOW GLOBAL STATUS 可用于监控

结果

以下脚本没个一秒执行一次，第一列为每秒查询数，第二列为Threads_connected，第三列为Threads_running（正在执行的查询数）：

mysqladmin ext -i1 | awk '
BEGIN{print "Q/Sec  Thread_connected Thread_running"}
/Queries/{q=$4-qp;qp=$4}
/Threads_connected/{tc=$4}
/Threads_running/{printf "%5d %5d %5d\n", q, tc, $4}'

这三个数据对数据库偶尔停顿敏感度比较高。

使用SHOW PROCESSLIST

以下命令用来计算State列值出现的次数 如果大量的线程处于 “freeing items” 状态是出现了大量有问题查询的很明显的特征和指示

mysql -uroot -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -nr

结果

使用查询日志

以下命令也可以通过 tcpdump 和 pt-query-digest来模拟替代

awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}' slow-query.log
 
#MAC上为：
sudo awk '/^# Time:/{print $3, $4, c;c=0}/^# User/{c++}'  /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log

结果

3.4.2 捕获诊断数据

捕获freeing items状态的数目

$ mysql -e 'SHOW PROCESSLIST\G' | grep -c "State: freeing items"
 36

几个PT工具及依赖关系

pt-collect  ->  pt-stalk  ->  gdb + profile
说明：
* 需要有root权限       
* 需要用nm mysqld|wc -l 检查一下是否有调试符号信息
* 要在 pt-stalk 中配置gdb 和 profile

 pt-summary #输出操作系统和硬件的信息
 pt-mysql-summary --user=root --password=YourPassword  #输出MySQL的状态和配置信息

文件:Pt-mysql-summary20160901.log
文件:Pt-summary20160901.log

 #pt-sift - Browses files created by pt-stalk. 这个工具会轮流导航到所有的样本数据，得到每个样本的汇总信息
 #用法
 pt-sift FILE|PREFIX|DIRECTORY

oprofile

安装
yum install oprofile
 
初始化 
opcontrol --no-vmlinux # 指示oprofile启动检测后，不记录相关统计数据
opcontrol --init # 加载oprofile模块、oprofile驱动程序
检测控制
opcontrol --start # 指示oprofile启动检测
opcontrol --dump # 指示将oprofile检测到的数据写入文件
opcontrol --reset # 清空之前检测的数据记录
opcontrol -h # 关闭oprofile进程
查看检测结果
opreport # 以镜像(image)的角度显示检测结果，进程、动态库、内核模块属于镜像范畴
opreport -l # 以函数的角度显示检测结果
opreport -l test # 以函数的角度，针对test进程显示检测结果
opannotate -s test # 以代码的角度，针对test进程显示检测结果
opannotate -s /lib64/libc-2.4.so # 以代码的角度，针对libc-2.4.so库显示检测结果

结果

gdb

结果

gdb命令参考连接 http://blog.chinaunix.net/uid-20696246-id-1892093.html

pidof mysqld #得到mysqld的pid为 1504
gdb
(gdb) attach 1504
(gdb) bt
(gdb) c 


(gdb) detach
(gdb) q

pt-pmp 堆栈信息

比上面gdb的要强大

pt-pmp -l 5
或
pt-pmp -l 5 stacktraces.txt

统计临时表

其中lsof.txt是lsof每5秒采集一次的汇总数据。 以下命令对以上文件中临时表的数据进行了加总输出

$ awk ' /mysqld.*tmp/ {
total += $7; }
/^Sun Mar 28/ && total {
printf "%s %7.2f MB\n", $4, total/1024/1024; total = 0;
}' lsof.txt

3.5其它工具 strace

通过strace统计mysql系统调用的情况

$ strace -cfp $(pidof mysqld)

pt-ioprofile 这个工具依赖于strace

第四章 Schema与数据类型优化

4.1选择要优化的数据类型

更小的通常更好、简单就好、尽量避免NULL

整形：TINYINT 8位 , SMALLINT 16, MEDIUMINT 24, INT 32, BIGINT 64
布尔类型的技巧：用CHAR(0) 表示，true/false分别用 NULL/空字符串 来表示
要避免使用BIT类型

几个常用的函数

FROM_UNIXTIME()  --把Unix时间戳转换成日期 P122
UNIX_TIMESTAMP() --日期转换成时间戳
 
--存储UUID的一个技巧：移除“-”号，用UNHEX()转换成16字节的数字，并存储在BINARY(16)的列中。
UNHEX()  --转换成10进制P126
HEX()       --转换成16进制
 
--IPv4和INT间的转换
INET_NTOA()
INET_ATON()
 
--存储UUID的一个技巧：移除“-”号, 然后转换成10进制
SELECT conv(REPLACE(uuid(),'-',''),16,10);

4.4缓存表和汇总表

4.4.1物化视图 TODO:练习Flexviews P134 P274

4.4.2计数器表

使用独立的表可以帮助避免查询缓存失效

例1：网站计数，思路添加100行，每次随机找一行将cnt++

#建表：
CREATE TABLE hit_counter (
  slot tinyint UNSIGNED NOT NULL PRIMARY KEY, 
  cnt INT UNSIGNED NOT NULL
) ENGINE=InnoDB;
 
#添加计数时如下：
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
 
#统计访问量时：
SELECT SUM(cnt) FROM hit_counter;

例2：按天计数。这里更新计数时用到了一个技巧： ON DUPLICATE KEY UPDATE

#建表： 
CREATE TABLE daily_hit_counter ( DAY DATE NOT NULL,
  slot tinyint UNSIGNED NOT NULL,
  cnt INT UNSIGNED NOT NULL, 
  PRIMARY KEY(DAY, slot)
) ENGINE=InnoDB;
 
#添加计数时如下：
INSERT INTO daily_hit_counter(DAY, slot, cnt)
 VALUES(CURRENT_DATE, RAND() * 100, 1)
 ON DUPLICATE KEY UPDATE cnt = cnt + 1;
 
 
#如果希望减少表的行数，以避免表变得太大，可以写一个周期执行的任务，合并所有结果到0号槽，并删除所有其它的槽
UPDATE daily_hit_counter AS c 
   INNER JOIN (
      SELECT DAY, SUM(cnt) AS cnt, MIN(slot) AS mslot FROM daily_hit_counter
      GROUP BY DAY
   ) AS x USING(DAY)
SET c.cnt = IF(c.slot = x.mslot, x.cnt, 0),
c.slot = IF(c.slot = x.mslot, 0, c.slot);
 
DELETE FROM daily_hit_counter WHERE slot <> 0 AND cnt = 0;

4.5ALTER TABLE提速

假设要改变一个列的默认值，则可用ALTER COLUMN，此方法可以防止表重建：

ALTER TABLE sakila.film '''ALTER COLUMN''' rental_duration SET DEFAULT 5;

第五章创建高性能索引

索引基础

创建自定义哈希索引，示例：

--创建表
CREATE TABLE pseudohash (
   id INT UNSIGNED NOT NULL AUTO_INCREMENT, url VARCHAR(255) NOT NULL,
   url_crc INT UNSIGNED NOT NULL DEFAULT 0, 
   PRIMARY KEY(id)
);
 
--创建触发器，在插入和更新时维护url_crc列：
DELIMITER //
CREATE TRIGGER pseudohash_crc_ins BEFORE INSERT ON pseudohash FOR EACH ROW BEGIN 
SET NEW.url_crc=crc32(NEW.url);
END;
//
CREATE TRIGGER pseudohash_crc_upd BEFORE UPDATE ON pseudohash FOR EACH ROW BEGIN 
SET NEW.url_crc=crc32(NEW.url);
END;
//
DELIMITER ;
 
--验证触发器
INSERT INTO pseudohash (url) VALUES ('http://www.mysql.com'); 
SELECT * FROM pseudo hash;
 
UPDATE pseudohash SET url='http://www.mysql.com/' WHERE id=1;
SELECT * FROM pseudo hash;
 
 
--查询：
SELECT * FROM pseudohash WHERE url="http://www.mysql.com/"  AND url_crc=CRC32("http://www.mysql.com/");

自己定一个HA64位函数：

SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64;

关于三星索引 P152

一星索引：索引将相关的记录放到一起
二星索引：索引中的数据顺序和查找中的排列顺序一致
三星索引：索引中的列包含了查询中需要的全部列

5.3高性能索引策略

前缀索引和索引选择性

缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ，也无法使用前缀索引做覆盖扫描

--计算不同的选择性
SELECT COUNT(DISTINCT city)/COUNT(*) FROM sakila.city_demo;
SELECT
  COUNT(DISTINCT LEFT(city, 3))/COUNT(*) AS sel3,
  COUNT(DISTINCT LEFT(city, 4))/COUNT(*) AS sel4,
  COUNT(DISTINCT LEFT(city, 5))/COUNT(*) AS sel5,
  COUNT(DISTINCT LEFT(city, 6))/COUNT(*) AS sel6,
  COUNT(DISTINCT LEFT(city, 7))/COUNT(*) AS sel7
FROM sakila.city_demo;
 
--创建前缀索引
ALTER TABLE sakila.city_demo ADD KEY (city(7));

而对于后缀索引，可以把字符串反转后做成前缀索引，并用触发器来维护。

5.3.4选择合适的索引顺序

经验法则：将选择性最高的列放到索引的最前列。但通常不如避免随机IO和排序那么重要

联合索引的选择性预测（ 从结果可见，应把customer_id放前面）：

 SELECT SUM(staff_id = 2), SUM(customer_id = 584) FROM payment\G
 *************************** 1. ROW ***************************
 SUM(staff_id = 2): 7992 
 SUM(customer_id = 584): 30
 
 --然后再看看针对这个customer_id的条件值对应的staff_id列的选择性如何：
 SELECT SUM(staff_id = 2) FROM payment WHERE customer_id = 584\G
 *************************** 1. ROW ***************************
 SUM(staff_id = 2): 17

以上是个例（一般是从诸如pt-query-digest中提取出的最差查询），
一个通用的方法是计算选择性：

 SELECT 
   COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity, 
   COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity, 
   COUNT(*)  FROM payment\G

5.3.6覆盖索引

• 如果一个索引包含（或者说覆盖）所有需要查询的字段值，我们就称之为“覆盖索引”
• 覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能覆盖查询，则可以避免对主键的二次查询
• 当发起一个被索引覆盖的查询（也叫做索引覆盖查询）时，在EXPLAIN的Extra列可以看到“Using Index”的信息。

坑： 假设索引覆盖了WHERE，但不是整个查询涉及的字段。如果条件为假（false），MYSQL5.5和更早的版本也总是会回表获取数据行，尽管不需要这一行
MySQL5.6已经解决该问题，因为它已经实现了索引条件下推，参见：http://mdba.cn/2014/01/21/index-condition-pushdownicp索引条件下推/

以下查询中索引无法覆盖查询，有两个原因：

• 没有任何索引能覆盖这个查询
• MySQL不能在索引中执行LIKE操作

EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY'  AND title LIKE '%APOLLO%'\G

以下是改进： 先将索引扩展至覆盖三个数据列：( artist, title, prod_id )
我们把这种方式叫做延迟关联（deffered join）。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到prod_id，然后外层再根据这些prod_id在外层查询匹配获取需要的所有列值。
是否需要这种改进要看应用场景，对照测试的结果参见教材P174
MySQL5.6已经无需这种方式了，因为它已经实现了索引条件下推 即ICP MySQL5.6之Index Condition Pushdown(ICP,索引条件下推) 如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown

EXPLAIN SELECT * FROM products
 JOIN (
     SELECT prod_id
     FROM products
     WHERE actor='SEAN CARREY' AND title LIKE '%APOLLO%'
 ) AS t1 ON (t1.prod_id=products.prod_id)\G

5.3.7使用索引扫描来做排序

• 如果EXPLAIN出来的type值为“index”，则说明MySQL用了索引扫描来排序（不要和Extra的“Using index”搞混了）
• 如果索引不能覆盖查询所有的列，则按索引顺序读取数据的速度通常要比顺序的全表扫描慢
• 只有当索引的列顺序和ORDER BY的子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序（如果要按不同的方向排序，一个技巧时存储该列值的反转串或者相反数）
• 如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序
• ORDER BY子句和查找限制是一样的：需要满足索引的最左前缀的要求。（有一种例外，就是前导列为常量，如下例子： ）

--表中有索引 PRIMARY KEY (rental_id),   UNIQUE KEY  rental_date (rental_date,inventory_id,customer_id)
EXPLAIN SELECT rental_id, staff_id FROM sakila.rental  WHERE rental_date = '2005-05-25'
 ORDER BY inventory_id, customer_id\G
--以下两个也可以
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC;
... WHERE rental_date > '2005-05-25' ORDER BY rental_date, inventory_id;

而以下则不行：

... WHERE rental_date = '2005-05-25' ORDER BY inventory_id DESC, customer_id ASC;
... WHERE rental_date = '2005-05-25' ORDER BY inventory_id, staff_id;
... WHERE rental_date = '2005-05-25' ORDER BY customer_id;
... WHERE rental_date > '2005-05-25' ORDER BY inventory_id, customer_id; --因为where子句里是用的范围
... WHERE rental_date = '2005-05-25' AND inventory_id IN(1,2) ORDER BY customer_ id; --这里inventory_id也相当于范围查询

5.3.9冗余和重复索引

对于InnoDB来说任何二级索引都不需要添加主键。如索引：（A, ID)，其中ID是主键，对于InnoDB来说主键已经包含在二级索引中了，所以这也是冗余的。

大多数情况下都不需要冗余索引，但有时候出于对性能的考虑需要冗余索引。见P179
检查冗余索引的一个工具：
pt-duplicate-key-checker --user=root --password=YourPassword

另一个工具： pt-upgrade 用来检查计划中的索引变更

以下命令打印索引的使用情况
pt-index-usage

5.3.11索引和锁

InnoDB支持的两种显示锁定语法：

 SELECT ... LOCK IN SHARE MODE
 SELECT ... FOR UPDATE

• Extra列出现“Using where”，这表示MySQL服务器将存储引擎返回行以后再应用WHERE过滤条件
• 即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能更糟，MySQL会做全表扫描并锁住所有的行P182
• InnoDB在二级索引上使用共享（读）锁，但访问主键索引需要排他（写）锁。

5.4索引案例学习

• 查询只能使用最左前缀，直到遇到第一个范围条件。所以原则是：尽可能将需要做范围查询的列放到后面，以便优化器能使用尽可能多的列 P184
• 要避免多个范围查询。例如：将last_online转换成等值比较，为实现这一点，我们要事计算好一个active列，这个字段由定时任务来维护。当用户每次登录时，将对应的值设置为1，并将过去连续七天未曾登录的用户的值设置为0
• MySQL松散索引扫描与紧凑索引扫描 http://www.tuicool.com/articles/fIF3ey

优化排序

如下语句，偏移量很大，MySQL需要花大量的时间来扫描需要丢弃的数据：

 SELECT <cols> FROM profiles WHERE sex='M' ORDER BY rating LIMIT 100000, 10;

1. 反范式化、预先计算和缓存可能是解决这类问题的仅有策略
2. 限制用户有这么多的分页
3. 另一个比较好的策略是使用延迟关联：

SELECT <cols> FROM profiles INNER JOIN (
  SELECT <PRIMARY KEY cols> FROM profiles
  WHERE x.sex='M' ORDER BY rating LIMIT 100000, 10
) AS x USING(<PRIMARY KEY cols>);

5.5维护索引和表

CHECK TABLE film;
repair TABLE film;

如果存储引擎不支持以上命令，也可通过一个不做任何操作的ALTER来重建表，以下是会对InnoDB表的例子：

ALTER TABLE innodb_tbl ENGINE=INNODB;

如果InnoDB锁坏，可以通过设置innodb_force_recovery参数进入InnoDB的强制恢复模式。另外可以使用InnoDB数据恢复工具箱：https://www.percona.com/software/mysql-innodb-data-recovery-tools

5.5.2更新索引统计信息

• InnoDB通过抽样的方式来计算统计信息。可以修改innodb_stats_sample_pages来设置样本页的数量，值越大越能生成更准确的统计信息
• InnoDB会在 表首次打开、执行ANALYZE TABLE、表大小发生非常大变化时（大小变化超1/16或新插入了20亿行）计算索引统计信息
• InnoDB在打开某些INFORMATION_SCHEMA表、SHOW TABLE STATUS、SHOW INDEX、客户端打开自动补全功能 时自动更新统计信息
• 另外MySQL5.6可以通过设置 innodb_analyze_is_persistent来实现是否持久化这些统计信息（更稳定的执行计划，并在重启时快速的生成这些统计信息）

5.5.3减少索引和数据碎片

可以通过执行'''OPTIMIZE TABLE''' 或者再导入的方式重新整理数据
InnoDB可以通过重建索引来消除碎片化
通过什么都不做的ALTER重建表（InnoDB只能重建数据碎片，实际是就是聚蔟索引）：ALTER TABLE <table> ENGINE=<engine> 

第六章 查询性能优化

EXPLAIN里type的含义

• 扫描行数/返回行数 一般在 1/1 和 10/1之间
• Extra里的Using Where 表示MySQL将通过WHERE条件来筛选存储引擎返回的记录

一般MySQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：

1. 从索引中使用WHERE来过滤不匹配的记录。这是在存储引擎中来完成的
2. 使用 索引覆盖扫描（在Extra中使用Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器中完成的，但无需再返回表查询记录
3. 从数据表中返回数据，然后过滤不满足条件的记录（在Extra中出现Using Where）。这在MySQL服务器层来完成的，MySQL需要先从数据表中读出记录然后过滤

如果发现查询要扫描大量数据，但只返回少量的行，那么通常可以尝试下面的技巧去优化它：

• 使用索引覆盖扫描，这样存储引擎无需回表获取对应行就可以返回结果了
• 改变库表结构。如使用汇总表
• 重写复杂的查询。（本章后续会讨论）

6.3重构查询的方式

6.3.1一个复杂查询还是多个简单查询

• MySQL从设计上让连接和断开连接都很轻量级
• 即使在一个能用服务器上，也能够运行每秒超过10万的查询
• 即使一个前兆网卡也能轻松完成每秒2000次的查询

所以运行多个小查询现在已经不是大问题了。
MySQL内部每秒能扫描内存中上百万行数据

这里有两个MySQL5.7的性能测试：

• MySQL性能：使用 MySQL 5.7 实现每秒 50 万查询
• MySQL Performance: over 1M QPS with InnoDB Memcached Plugin in MySQL 5.7

6.3.2切分简单的查询

如下例子，当删除大表时，一次只删除10000行。好处是：不会一次锁住很多资源；节省系统资源；不会阻塞很多小的但重要的查询
一次只删除一万行是比较高效且对服务器影响最小的做法

rows_affected = 0 
do {
   rows_affected = do_query(
      "DELETE FROM messages WHERE created < DATE_SUB(NOW(),INTERVAL 3 MONTH) LIMIT 10000")
  } while rows_affected > 0

另：pt-archiver 可以安全而又简单的完成这项工作

6.3.3分解关联查询

将每一个表进行单表查询，而不是联表
这样做的好处：

• 让缓存效率更高
• 将查询分解后，执行单个查询可以减少锁的竞争
• 在应用层进行拆分，可以更容易的对数据库进行拆分
• 查询本身性能也有可能提升
• 可以减少冗余记录的查询。
• 更进一步，这样做相当于在应用层做了哈希关联，而不是MySQL的嵌套循环关联

6.4查询执行的基础

6.4.3查询优化处理

查询优化器

优化策略可分两种：P210

静态优化

直接对解析树进行分析，并完成优化，静态优化不依赖特别的数值，如WHERE中带的一些常数等，静态优化在第一次完成后就一直有效，即使带着不同参数重复查询

动态优化

和查询的上下文有关，也可能和其它因素有关，可以认为这是一种“运行时优化”。对查询的动态优化在每次执行的时候都需要重新评估。有时在执行过程中也会重新优化。例如：

在关联操作中，范围检查的执行计划会针对每一行重新评估索引。可以通过EXPLAIN执行计划中的Extra列是否含有“reange check for each record”来确认这一点。该执行计划还会增加select_full_range_join这个服务器变量的值

MySQL能够处理的一些优化类型：P210

重新定义表的连接顺序

将外连接转换成内连接

使用等价变换规则

优化COUNT(), MAX(), MIN()  

例如要找到某列最小值，只需要查询B-Tree索引第一行记录记录。在优化器生成执行计划的时候就可以利用这一点，在B-Tree索引中优化器会将这个表达式当做一个常数对待。如果MySQL利用了这种类型的优化，那么在EXPLAIN中就可以看到“Select tables optimize away”（优化器已经从执行计划中移除了该表，并以一个常数代之） 。

类似的没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化（例如：MyISAM维护了一个变量来存放数据表的行数）

预估，并转换成常量表达式

覆盖索引扫描

子查询优化

提前终止查询。 如

EXPLAIN SELECT film.film_id FROM sakila.film WHERE film_id = −1;

另一个例子，找到没有演员的所有电影：

SELECT film.film_id FROM sakila.film LEFT OUTER JOIN sakila.film_actor USING(film_id) WHERE film_actor.film_id IS NULL;

列表IN()比较

MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件，这是一个O(log n) 复杂度的操作，等价换成OR查询的复杂度为O(n),对于IN()列表中有大量的取值的时候，MySQL的处理速度会更快

数据库如何执行关联查询

从本质上说MySQL对所有的类型的查询都以同样的方式运行。例如，在MySQL在FROM子句中遇到子查询时，先执行子查询，并将其结果放到一个临时表中，然后将这个临时表当成一个普通表对待
MySQL的临时表是没有任何索引的

STRAIGHT_JOIN 手动指定关联顺序

--让查询优化器自动确认关联顺序
EXPLAIN SELECT film.film_id, film.title, film.release_year, actor.actor_id,
  actor.first_name, actor.last_name
  FROM sakila.film
  INNER JOIN sakila.film_actor USING(film_id)
  INNER JOIN sakila.actor USING(actor_id);
--查看查询预估成本 输出为：241
SHOW STATUS LIKE '%Last_query_cost%';
 
--通过添加 STRAIGHT_JOIN 关键字手动指定关联顺序
EXPLAIN SELECT STRAIGHT_JOIN  film.film_id, film.title, film.release_year, actor.actor_id,
  actor.first_name, actor.last_name
  FROM sakila.film
  INNER JOIN sakila.film_actor USING(film_id)
  INNER JOIN sakila.actor USING(actor_id);
--查看查询预估成本 输出为：1154
SHOW STATUS LIKE '%Last_query_cost%';

如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回一个最优的执行计划

排序优化

MySQL4.1之后采用单次排序

先读取所需要的所有列，然后再根据给定的列进行排序。当查询需要所有列的总长度不超过参数max_length_for_sort_data时（一般为1024），MySQL使用“单次传输排序”

临时排序的空间

MySQL进行文件排序的时候需要使用的临时存储空间可能比想象的要大很多。原因在于MySQL在排序时，对每一个排序记录都会分配一个足够长的定长空间来存放。这定长空间必须足够长以容纳其中最长的字符串，例如，如果是VARCHAR列则需要分配其完整长度；如果使用UTF-8字符集，那么MySQL将会为每个字符串预勿留三个字节。

关联查询时的排序

MySQL会分两种情况来处理这样的文件排序。

• 如果ORDER BY子句中的所有列都来自关联的第一个表，那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样，那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”
• 除此之外的所有情况，MySQL都会先将关联的结果存放到一个临时表中，然后在所有的关联都结束后，再进行文件排序。这种情况下，在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary; Using filesort”。如果查询中的LIMIT的话，LIMIT会在排序之后应用，所以即使返回较少的数据，临时表和需要排序的数据量仍然会非常大。

MySQL5.6在这里做了很多重要的改进。当只需返回部分排序结果的时候，例如使用了LIMIT子句，MySQL不再对所有结果进行排序，而是根据实际情况选择抛弃不满足条件的结果，然后再进行排序

6.5MySQL查询优化器的局限性

UNION的限制

要在每个子句中都加上LIMIT

(SELECT first_name, last_name FROM sakila.actor
ORDER BY last_name
LIMIT 20)
UNION ALL
(SELECT first_name, last_name
FROM sakila.customer ORDER BY last_name LIMIT 20)
LIMIT 20;

索引合并优化

在>=5.0版本中，当WHERE包含多个复杂条件的时候，MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行

松散索引扫描（MySQL5.6已支持 可通过SHOW VARIABLES LIKE '%optimizer_switch%';查看）

在EXPLAIN的Extra字段显示“Using index for group-by”，表示这里将使用松散索引扫描。

mysql> EXPLAIN SELECT actor_id, MAX(film_id)  FROM sakila.film_actor GROUP BY actor_id\G
*************************** 1. row *************************** 
id: 1
select_type: SIMPLE 
table: film_actor
type: range
possible_keys: NULL
key: PRIMARY 
key_len: 2
ref: NULL
rows: 396
Extra: Using index for group-by

6.6查询优化器的提示（hint）

HIGH_PRIORITY和LOW_PRIORITY，注意千万不能在InnoDB中使用

HIGH_PRIORITY用于SELECT时，会将语句放在修改之前

DELAYED (InnoDB不支持 DELAYED仅适用于MyISAM, MEMORY和ARCHIVE表)

这个提示对于INSERT和REPLACE有效。MySQL会将使用该提示的语句立即返回给客户端，并将插入的行数据放到缓冲区，然后在表空闲时指将数据写入。日志系统使用这样的提示非常有效，或者是其它需要大量写入但客户不需要等待单条语句完成的I/O应用。限制：并不是所有的存储引擎都支持这样做法；并且该提示会导致LAST_INSERT_ID()无法正常工作

STRAIGHT_JOIN

这个提示可以放在SELECT语句的SELECT关键字之后，也可以放在任意两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联。第二个用法是固定其前后两个表的关联顺序

SQL_SMALL_RESULT和SQL_BIG_RESULT

这两个提示只对SELECT有效。它告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序。SQL_SMALL_RESULT告诉优化器结果集很小，可以将结果集放在内存中索引临时表，以避免排序操作。如果是SQL_BIG_RESULT,则告诉优化器结果集可能非常大，建议使用磁盘临时表做排序操作

SQL_BUFFER_RESULT

这个提示告诉优化器将查询结果集放到一个临时表，然后尽可能快的释放表锁。这和前面提到的由客户端缓存结果不同。当你无法使用客户端缓存的时候，使用服务器端缓存通常很 有效。代价是服务器需要消耗更多的内存

SQL_CACHE和SQL_NO_CACHE

这个提示告诉MySQL这个结果集是否应该缓存在查询缓存中，下一章将详细介绍

SQL_CALC_FOUND_ROWS

加上该提示MySQL会计算除去LIMIT之后的这个查询要返回的结果集总数，而实际上只返回LIMIT要求的结果集。可以通过FOUND_ROW()获得这个值。优化见6.7.6节

FOR UPDATE和LOCK IN SHARE MODE

USE INDEX、IGNORE INDEX 和 FORCE INDEX

这几个提示会告诉优化器使用或者不使用哪些索引来查询记录。在5.0之前版本不会影响排序和分组的索引使用，但5.1及之后的版本可以通过选项FOR ORDER BY 和 FOR GROUP BY 来指定是否对排序和分组有效

在5.0及之后，新增了一些参数来控制优化器的行为：

optimizer_search_depth

这个参数控制优化器在穷举执行计划时的限度。如果查询长时间处于“Statistics”状态，那么可以考虑调低此参数

optimizer_prune_level

默认打开的。这让优化器根据扫描的行数来决定是否跳过某些执行计划

optimizer_switch

包含了一些开头标志位

在MySQL升级时可用 pt-upgrade来检查SQL在新老版本中是否一样

6.7优化特定类型的查询

6.7.1优化COUNT()查询

COUNT()有两种非常不同的作用：

• 统计某个列值的数量。如果COUNT里指定的列或列的表达式，则统计的是有值的结果数（非NULL）
• 统计行数，一般COUNT(*)

使用近似值：可以用EXPLAIN里的估算行数

6.7.2优化关联查询

• 一般来说，除非有其他理由，否则只需要在关联顺序中的第二个表的相应列上建索引
• 确保任何的GROUP BY 和 ORDER BY 中的表达式只涉及到一个表中的列，这样才能使用索引优化这一过程
• 当升级MySQL的时候要注意：关联语法、运算符优先级等可能发生变化的地方。因为以前是变通关联现在有可能变成笛卡尔积

6.7.3优化子查询

如果是MySQL5.6或以上，则忽略子查询相关优化建议。否则使用关联查询代替子查询

6.7.4优化GROUP BY和DISTINCE

• 在很多场景下，MySQL都用同样的办法优化这两种查询
• 在MySQL中当无法使用索引的时候，GROUP BY使用两种策略来完成：使用临时表或文件分组来完成排序。
• 如果对关联列做分组（GROUP BY）,并且是按查找表中的某个列来分组，那么通常采用查找表的标识列分组的效率会比其它列高。如(这个查询利用了演员的姓名和ID直接相关的特点)：

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, COUNT(*) FROM sakila.film_actor INNER JOIN sakila.actor USING(actor_id) GROUP BY actor.first_name, actor.last_name;

可以通过把SQL_MODE设置成ONLY_FULL _GROUP_BY禁止第一种写法

6.7.5优化LIMIT分页

优化分页查询的一个简单的办法是尽可能的使用索引覆盖扫描，而不是查询所有的列。 考虑如下查询：

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

如果这个表非常大，那么这个查询最好改写成下面的样子（延迟关联）：

SELECT film.film_id, film.description FROM sakila.film
    INNER JOIN (
        SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5
    ) AS lim USING(film_id);

结果

6.7.6优化SQL_CALC_FOUND_ROWS

SQL_CALC_FOUND_ROWS会同时返回总行数
但一个更好的设计是将具体的页数换成“下一页”按钮。假设每页显示20条，那么我们每次都用LIMIT返回21条并只显示20条，如果第21条存在那么我们就显示下一页按钮。

6.7.7UNION查询

MySQL总是利用临时表的方式执行UNION查询。所有要经常手工地将WHERE、LIMIT、ORDER BY等子名下推到UNION的各个子查询中去。
除非确实需要服务器消除重复行，否则就一定要使用UNION ALL，如果没有ALL则MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表做唯一性检查，这样做代价非常高。

6.7.8静态查询分析

pt-query-advisor

能解析查询日志、分析查询模式，然后给出所有存在潜在问题的查询

6.7.9使用自定义变量

例1：变量示意：

mysql> SET @one := 1;
mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor); 
mysql> SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK;

例2：优化排名语句：

SET @curr_cnt := 0, @prev_cnt := 0, @rank := 0; 
 
SELECT actor_id,
   @curr_cnt := cnt AS cnt,
   @rank := IF(@prev_cnt <> @curr_cnt, @rank + 1, @rank) AS rank, 
   @prev_cnt := @curr_cnt AS dummy
FROM (
   SELECT actor_id, COUNT(*) AS cnt FROM sakila.film_actor
   GROUP BY actor_id
   ORDER BY cat DESC
   LIMIT 10
) AS der;

例3：统计更新和插入的数量（ INSERT ON DUPLICATE KEY UPDATE）P248：

INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1) 
ON DUPLICATE KEY UPDATE
   c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) );

例4：编写偷懒的UNION：

SELECT GREATEST(@found := -1, id) AS id, 'users' AS which_tbl FROM users WHERE id = 1
UNION ALL
SELECT id, 'users_archived' FROM users_archived WHERE id = 1 AND @found IS NULL 
UNION ALL
SELECT 1, 'reset'               FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

以上语句中GREATEST函数只有WHERE id=1成功找到数据才会执行。第三个reset子句是将@found重置为NULL且查询条件永远为假

第七章 MySQL高级特性

7.1分区表

在下列场景中分区表有很大的作用：

• 表非常大以至于无法全部放到内存中，或者只在表的最后部分有热点数据，其他均是历史数据
• 分区表更容易维护。如删除，对独立分区进行优化、检查、修复等操作
• 分区表的数据可以分布在不同的物理设备上
• 避免某些特殊的瓶颈，如InnoDB的单个索引互斥的访问、ext3的inode锁竞争等
• 如需要，可以备份还恢复独立的分区。

分区表也有一些限制：

• 一个表最多有1024个分区
• 在MySQL5.1中，分区表达式必须是整数，或者是返回整数的表大家式。在MySQL5.5中，某些场景中可以直接使用列来进行分区
• 如果分区字段中有主键或者唯一索引的列，那么所有主键列和唯一索引列都必须包含进来。
• 分区表中无法使用外键约束

7.1.5查询优化

使用EXPLAIN PARTITIONS 可以观察优化器是否使用了分区：

EXPLAIN PARTITIONS SELECT * FROM sales \G

7.2视图

创建视图：

CREATE VIEW Oceania AS SELECT*FROM country WHERE continent ='Oceania' WITH CHECK OPTION;

下面是使用临时表模拟视图的方法：

 CREATE TEMPORARY TABLE TMP_Oceania_123 AS SELECT * FROM Country WHERE Continent = 'Oceania';
 SELECT Code, Name FROM TMP_Oceania_123 WHERE Name = 'Australia';

临时表的数据是会被清空的,你断开了连接就会被自动清空
实现视图有两种算法：

• 合并算法（MERGE）：一般会使用这种算法
• 临时表算法（TMPTABLE）：如果视图中出现GROUP BY、DISTINCT、任何聚合函数、UNION、子查询等，只要无法在原表记录和视图记录中建立一一映射的场景中，MySQL都将使用临时表算法来实现视图。如果在EXPLAIN中的select_type为“DERIVED”,说明该视图是使用临时表实现的。

也可以在创建视图时指定临时表算法：

CREATE ALGORITHM=TEMPTABLE VIEW v1 AS SELECT * FROM sakila.actor;

7.2.1可更新视图

UPDATE Oceania SET Population = Population * 1.1 WHERE Name = 'Australia';

注意：

• 如果视图定义中包含了GROUP BY、UNION、聚合函数，以及其他一些情况，就不能被更新了
• 更新也可以是一关联语句，但是有一个限制，被更新的列必须来自同一个表
• 所有使用临时表算法的视图不能被更新
• MySQL的视图不支持触发器

7.2.2视图对性能的影响

视图在某些情况下是可以提升性能的：

• 在重构schema的时候可以使用视图，使得在修改底层表结构的时候，应用代码还可以不报错的运行
• 可以使用视图实现基于列的权限控制

获取视图定义的一个窍门（亲测，不好用，LOAD_FILE不能加载文件）：

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(
SUBSTRING_INDEX(LOAD_FILE('/Applications/XAMPP/xamppfiles/var/mysql/world/oceania.frm'),
'\nsource=', -1),
'\\_','\_'), '\\%','\%'), '\\\\','\\'), '\\Z','\Z'), '\\t','\t'), '\\r','\r'), '\\n','\n'), '\\b','\b'), '\\\"','\"'), '\\\'','\''), '\\0','\0')
 AS SOURCE;

7.3外键约束

在某些场景下，外键会提升一些性能。如果想确保两个表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高很多，此外，外键在相关数据的删除和更新上，也比在应用程序中检查一致性要高很多，不过外键维护操作是逐行进行的。

7.4在MySQL内部存储代码

缺点：

• 它和基于语句的二进制日志复制合作的并不好
  

以下是一个例子：

DROP PROCEDURE IF EXISTS insert_many_rows;
delimiter //
CREATE PROCEDURE insert_many_rows (IN loops INT) 
BEGIN
   DECLARE v1 INT; SET v1=loops; 
   WHILE v1 > 0 DO
      INSERT INTO test_table VALUES(NULL,0, 
                'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt', 
                'qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt');
      SET v1 = v1 - 1; 
   END WHILE;
END; //
delimiter ;

7.4.2触发器

要注意以下几点：

• 对每一个表的每一个事件，最多只能定义一个触发器（换句话说不能在AFTER INSERT上定义两个触发器）
• MySQL只支持基于行的触发
• 触发器可能掩盖服务器背后的工作
• 触发器的问题很难排查
• 触发器可能导致死锁和等待。如果触发器失败那么原来的SQL语句也会失败。如果没有意识到这是触发器在搞鬼，那么很难理解服务器抛出的错误代码是什么意思

在InnoDB上的触发器是在同一个事务中完成的，所以它们执行的操作是原子的（MyISAM不是）

这里有个绕过“基于行的触发”的技巧（P281）：

CREATE TRIGGER fake_statement_trigger 
BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
    DECLARE v_row_count INT DEFAULT ROW_COUNT(); 
    IF v_row_count <> 1 THEN
        -- Your code here 
    END IF;
END;

7.4.3事件

• 它类似于Linux的定时任务，可以指定MySQL在某个时候执行一段SQL代码，或者每隔一段时间执行一段SQL代码。通常我们把复杂的SQL封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。
  
• 事件在一个独立的事件调度线程里初始化，这个线程和处理连接的线程没有任何关系。它不接收任何参数，也没有任何返回值。可能在MySQL的日志中看到命令的执行日志，还可以在INFORMATION_SCHEMA.EVENTS中看到各个事件的状态，如这个事件最后一次被执行的时间。

例1：以下事件每周一次针对某个数据库运行一个存储过程（optimize_tables见7.6.2）：

CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK 
DO
CALL optimize_tables('somedb');

例2：另外可以以使用GET_LOCK()来确保当前只有一个事件在被执行（这里的CONTINUE HANLDER用来确保，即使当事件执行出现了异常，仍然会释放持有的锁）：

CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK 
DO
BEGIN
    DECLARE CONTINUE HANLDER FOR SQLEXCEPTION 
        BEGIN END;
    IF GET_LOCK('somedb', 0) THEN
        DO CALL optimize_tables('somedb');
    END IF;
    DO RELEASE_LOCK('somedb'); 
END

MySQL中有一个事件调度线程，默认是关闭的，必须在MySQL配置文件中设置，或者使用下面的命令来设置：

SET GLOBAL event_scheduler := 1;

• 你可以通过MySQL的错误日志来了解事件的执行情况。
• 虽然事件是一个单独的线程，但是事件来身是可以并行执行的。MySQL会创建一个新的进程用于事件执行

7.4.4在存储程序中保存注释

技巧：用版本号的注释且用一个非常大的数字，如99999

CREATE TRIGGER fake_statement_trigger 
BEFORE INSERT ON sometable
FOR EACH ROW
BEGIN
    DECLARE v_row_count INT DEFAULT ROW_COUNT();
    /*!99999 ROW_COUNT() is 1 except for the first row, so this executes only once per statement. */
    IF v_row_count <> 1 THEN 
         -- Your code here
    END IF;
END;

7.6绑定变量

基于如下原因，MySQL在使用绑定变量时可以更高效的执行大量重复的语句：

• 在服务器端只需要解析一次SQL语句
• 在服务器端某些优化器的工作只需要执行一次，因为它会缓存一些执行计划。
• 以二进制方式只发送参数和句柄，比起每次都发送ASCII码广西效率更高，一个二进制日期字段只需要三个字节，但如果是ASCII码则需要10个字节。不过最大的节省还是来自BLOB和TEXT，二进制协议不仅节省了网络开销还，另外还节省了将数据从存储格式转换成文本格式的开销
• 仅仅是参数——而不是整个语句，要发送到服务端
• MySQL在存储参数的时候，直接将其放到缓存中，不再需要在内存中多次复制

绑定变量相对也更安全

7.6.2SQL接口的绑定变量

SET @SQL := 'SELECT actor_id, first_name, last_name  FROM sakila.actor WHERE first_name = ?';
PREPARE stmt_fetch_actor FROM @SQL;
SET @actor_name := 'Penelope';
EXECUTE stmt_fetch_actor USING @actor_name;

存储过程：optimize_tables：
调用方式 mysql> CALL optimize_tables('sakila');

DROP PROCEDURE IF EXISTS optimize_tables;
DELIMITER //
CREATE PROCEDURE optimize_tables(db_name VARCHAR(64)) 
BEGIN
    DECLARE t VARCHAR(64); 
    DECLARE done INT DEFAULT 0; 
    DECLARE c CURSOR FOR
        SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
        WHERE TABLE_SCHEMA = db_name AND TABLE_TYPE = 'BASE TABLE'; 
    DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; 
    OPEN c;
    tables_loop: LOOP
        FETCH c INTO t; 
        IF done THEN
            LEAVE tables_loop; 
        END IF;
        SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t); 
        PREPARE stmt FROM @stmt_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt; 
    END LOOP;
    CLOSE c; 
END//
DELIMITER ;

还有另和种实现存储过程循环的办法是：

REPEAT
    FETCH c INTO t; 
    IF NOT done THEN
        SET @stmt_text := CONCAT("OPTIMIZE TABLE ", db_name, ".", t); 
        PREPARE stmt FROM @stmt_text;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
    END IF;
UNTIL done END REPEAT;

7.7用户自定义函数

最大的UDF仓库

7.8插件

存储过程插件

后台插件

INFORMATION_SCHEMA插件

全文解析插件

审计插件

认证插件

7.9字符集和校对

使用如下语句检查数据库的字符集设置：

show variables like '%character%';

修改MySQL字符集的方法（参考）：

[mysql]
default-character-set=utf8
[mysqld]
character-set-server=utf8

服务端和客户端的通信设置：

MySQL服务端和客户端的通信设置

• 假设使用latin1字符集（这是默认字符集）打开一个连接，并使用 SET NAMES utf8 来告诉服务器客户端将使用UTF-8字符集来传输数据。这样就创建了一个不匹配的字符集，可能会导致一些错误甚至出现一些安全性问题。应当先设置客户端字符集之后再使用函数 mysql_real_escape_string() 在需要的时候进行转义。在PHP中可以使用mysql_set_charset() 来修改客户端的字符集
• 如果比较的两个字符串的字符集不同，MySQL会先将其转换成同一个字符集再进行比较

还可以使用前缀和COLLATE子句来指定字符串的字符集或者校对字符集：

mysql> SELECT _utf8 'hello world' COLLATE utf8_bin; 
+--------------------------------------+
| _utf8 'hello world' COLLATE utf8_bin | 
+--------------------------------------+
| hello world | 
+--------------------------------------+

LOAD DATA INFILE

当使用LOAD DATA INFILE的时候，数据库总是将文件中的字符按照字符集 character_set_database来解析。我们发现指定字符集最好的方式是先使用USE指定数据库，再执行SET NAMES来设定字符集，然后再添加数据

LENGTH和CHAR_LENGTH

这两个是用来计算字符器的长度，在多字节字符集中，这两个函数返回的结果不同。例如在用SUBSTRING操作的时候要用CHAR_LENGTH

7.10全文索引

目前只有MyISAM支持全文索引，5.6及之后InnoDB也支持全文索引

自然语言的全文索引

--例1
SELECT film_id, title, RIGHT(description, 25),  MATCH(title, description) AGAINST('factory casualties') AS relevance  FROM sakila.film_text  WHERE MATCH(title, description) AGAINST('factory casualties');
 
--例2:如果要想使用全文索引查询某个关键词是否出现在某一列，可以使用如下技技巧P301~302：
 
ALTER TABLE film_text ADD FULLTEXT KEY(title) ;
 
SELECT film_id, RIGHT(description, 25),
 ROUND(MATCH(title, description) AGAINST('factory casualties'), 3)
    AS full_rel,
 ROUND(MATCH(title) AGAINST('factory casualties'), 3) AS title_rel
 FROM sakila.film_text
 WHERE MATCH(title, description) AGAINST('factory casualties')
 ORDER BY (2 * MATCH(title) AGAINST('factory casualties'))
 + MATCH(title, description) AGAINST('factory casualties') DESC;

布尔全文索引

如：

SELECT film_id, title, RIGHT(description, 25)
 FROM sakila.film_text
 WHERE MATCH(title, description)
 AGAINST('+factory +casualties' IN BOOLEAN MODE);
 
--也可以用短语搜索（因为短语搜索一般会回表过滤，所以速度会很慢）：
SELECT film_id, title, RIGHT(description, 25)
 FROM sakila.film_text
 WHERE MATCH(title, description)
 AGAINST('"spirited casualties"' IN BOOLEAN MODE);

限制和替代方案

全文索引的同时查找根据作者ID过滤

可以在author列的值前面加上一个不常见的前缀，然后将这个前缀放到一个单独的filter列中，并单独维护该列（也许可以使用触发器来维护），SQL如：... WHERE MATCH(content) AGAINST ('High Performance MySQL') AND author = 123;

全文索引有时还可以实现一些简单的“边框”搜索 P304

如，希望搜索某个坐标范围时，将坐标按某种方式转换成文本再进行全文索引。假设某条记录的坐标为X=123, Y=456。可以按这样的方式交错存储坐标: XY142536, 然后对此进行全文索引。这时，希望查询某矩形——X[100, 199], Y[400, 499]——范围时，可以在查询直接搜索“+XY14*” ,这比WHERE 的条件过滤要高很多

7.11分布式（XA）事务

内部XA事务
innodb_support_xa要打开（默认），才保证二进制日志安全
如果希望数据尽可能的安全，最好还要将sync_binlog打开。查看当前值的查询如下：

show variables like '%sync_binlog%';

外部XA事务 应尽量避免

7.12查询缓存

如果表发生变化，那么这个表相关的所有缓存都将失效
很多时候我们认为还是应该关闭查询缓存，如果查询缓存作用很大的话，那就配置一个很小的查询缓存（如几十兆）。后面我们将解释如果判断在你的系统压力下打开查询缓存是否有好处。

7.12.1MySQL如何判断缓存命中

• MySQL判断缓存是否命中时使用原始信息，不会解析、“正规化”或参数化，例如空格、注释——任何不同——都会导致不命中
• 当查询中包含一些不确定的函数时，则不会被缓存。例如包含函数NOW()或者CURRENT_DATE()的查询不会被缓存。类似的有 CURRENT_USER或CONNECTION_ID()的查询也不会被缓存。包含任何用记自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，或者任何包含列级别权限的表，都不会被缓存

如果查询中包含任何不确定函数，那么在查询缓存中是不可能找到缓存结果的，以下是个例子：

... DATE_SUB(CURRENT_DATE, INTERVAL 1 DAY) -- Not cacheable!
... DATE_SUB('2007-07-14’, INTERVAL 1 DAY) -- Cacheable

查询缓存命中率公式：

Qcache_hits / (Qcache_hits + Com_select)

另一个指标，“命中和写入”的比值，即 Qcache_hits / Qcache_inserts的比值。根据经验来看这个比值大于 3:1时通常查询缓存是有效的，不过这个值最好能达到10:1

7.12.4如何配置和维护查询缓存

query_cache_type

可以设成OFF、ON或DEMAND。DEMAND表示只有在查询语句中明确写明SQL_CACHE才放入查询缓存

query_cache_size

查询缓存使用的总空间，单位是字节。这个值必须为1024的整数倍。

query_cache_min_res_unit

在查询缓存中分配内存块时的最小单位

query_cache_limit

MySQL能够缓存的最大结果。如果查询结果大于这个值，则不会被缓存。如果超出，MySQL则增加状态值 Qcache_not_cached。如果事先知道有很多这样的情况发生，那么建议在查询语句中加入SQL_NO_CACHE来避免缓存带来的额外消耗

query_cache_wlock_invalidate

如果某个数据表被其它连接锁住，是否仍然从查询缓存中返回结果。默认为OFF

第八章优化服务器配置

8.1MySQL配置的工作原理

作者维护的一个调优工具

一个参数配置示例

如果不知道当前配置文件的使用路径，可以尝试下面的操作：

$ which mysqld
/usr/local/mysql/bin/mysqld
$ /usr/local/mysql/bin/mysqld --verbose --help | grep -A 1 'Default options' 
Default options are read from the following files in the given order: /etc/mysql/my.cnf ~/.my.cnf /usr/etc/my.cnf

DEFAULT

这是个特殊值，可以通过SET命令赋值给变量 := DEFAULT 。把这个值赋给会话级变量可以把变量值改为全局值；把它赋给全局变量可以设置这个变量为编译内置的默认值（不是在配置文件中指定的值）。

sort_buffer_size

MySQL只会在需要排序时才会为该缓存分配内存，然后，一旦需要排序，MySQL就会立即分配该缓存指定大小的全部内存，而不管排序后是否需要这么大的内存。如果查询必须使用一个更大的排序缓存才能比较好的执行，可以在查询执行前增加sort_buffer_size的大小，执行完后再恢复为DEFAULT

SET @@session.sort_buffer_size := <value>;

-- Execute the query...

SET @@session.sort_buffer_size := DEFAULT;

如果有需要也可以仲保存并还原原来的自定义值，可以像下面这样做(变量的生命周期是会话级的)：

SET @saved_<unique_variable_name> := @@session.sort_buffer_size;

SET @@session.sort_buffer_size := <value>;

-- Execute the query...

SET @@session.sort_buffer_size := @saved_<unique_variable_name>;

工具： pt-log-player

重放查询。比如用pt-log-player重放慢查询，然后用pt-query-digest来分析输出报告

8.3创建MySQL配置文件

以下是基础配置文件：

建议：

• 如果是简单的工作负载，MySQL的内存负载是非常小的——大约256K的每个连接。但是使用临时表、排序、存储过程等的复杂查询，可能使用更多的内存
• 我们建议，当配置内存缓冲区的时候，宁可谨慎，而不是把它们配置得过大。如果把缓冲池配置的比它可以设置的值少了20%，很可能只会对性能产生小的影响，也许就只影响几个百分点。如果设置的大了20%，则可能会造成更严重的问题：内存交换、磁盘抖动，甚至内存耗尽和硬件死机。

8.3.1检查MySQL服务器状态变量

以下命令使用了SHOW GLOBAL STATUS，每隔60秒来查看状态变量的增量变化：

 mysqladmin ext -ri60 #或者是  mysqladmin extended-status -ri60

有一些实用的工具，如：pt-mext 或 pt-mysql-summary可以简洁地显示状态计数器的变化

8.4配置内存使用

按如下步骤来配置内存：

1. 确定可用内存上限
2. 确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表
3. 确定操作系统要多少内存。包括同机器上的其它程序要多少内存，如定时任务
4. 把剩下的全部给MySQL的缓存，例如InnoDB的缓冲池，这样做很有意义

为操作系统保留内存

应当至少为操作系统保留1~2G的内存——如果机器内存更多就多留一些。建议2G或总内存的5%，以较大者为准。

8.4.4为缓存分配内存

下面是我们认为对大部分情况来说最重要的缓存：

• InnoDB缓存
• InnoDB日志文件和MyISAM数据的操作系统缓存
• MyISAM键缓存
• 查询缓存
• 无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存

8.4.5InnoDB缓存池

InnoDB不仅仅缓存索引：它还会缓存行数据、自适应哈希索引、插入缓冲（Insert Buffer）、锁，以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序的响应。总之，InnoDB严重依赖缓冲池。可能通过SHOW命令或innotop这样的工具来监控InnoDB缓冲池的使用

InnoDB的监控工具innotop:

#参考 http://www.tuicool.com/articles/J7NBRbN
wget https://github.com/innotop/innotop/archive/v1.11.zip
unzip v1.11.zip
cd innotop-1.11

./innotop -uroot -pYourPassword -d1

很大的缓冲池了会带来一些挑战：

• 如预热和关闭都会花费很长时间。
• 如果有很多脏页在缓冲池里，InnoDB可能会花费很长时间。也可以强制关闭，但重启时就要花费很长时间。如果事先知道何时关闭InnoDB，可以在运行时修改 innodb_max_dirty_pages_pct 变量，将值改小等待刷新线程清理缓冲池，然后在脏页比较少时关闭。可以监控 Innodb_buffer_pool_page_dirty来监控脏页的刷新量。

MySQL5.6：高速预热Buffer_Pool缓冲池
MySQL5.6新特性快速预热Buffer_Pool缓冲池

8.4.6MyISAM键缓存（Key Caches）

不像其它存储引擎，MyISAM自身只缓存索引，不缓存数据（依赖操作系统缓存数据）。如果大部分表是MyISAM表，就应该为键缓存分配较多的内存。
查看MyISAM实际占用了多少空间的两种方法：

• SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM';
• $ du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`

应该把键缓存设置的多大？

不要超过索引的总大小，或者不超过操作系统缓存保留总内存的25%~50%，以更小为准

配置多缓冲区：

key_buffer_1.key_buffer_size = 1G 
key_buffer_2.key_buffer_size = 1G

加载缓冲区

• mysql> CACHE INDEX t1, t2 IN key_buffer_1;
• mysql> LOAD INDEX INTO CACHE t1, t2;
• 任何没有指定映射到哪个缓冲区的索引，在MySQL第一次访问.MYI文件的时候，都会被分配到默认缓冲区

键缓冲的使用率

100 - ( (Key_blocks_unused * key_cache_block_size) * 100 / key_buffer_size )

但键缓冲的使用率意义不大，更常见的是每秒缓冲未命中次数，公式

Key_read / Uptime

可以用如下命令来监控每10秒状态值的变化(每秒缓冲未命中次数变化率)：

mysqladmin extended-status -r -i 10 | grep Key_reads

记住，MyISAM使用操作系统缓存来缓存数据文件，通常数据文件比索引要大。因此，把更多内存保留给操作系统缓存而不是键缓存是有意义的

8.4.7线程缓存

thread_cache_size 变量指定了MySQL可以保存在内存中的线程数。可以查看Threads_created变量
一个好的办法是观察Threads_created来设置thread_cache_size

• 若Threads_created通常保持在100~200之间，则可以设置缓存大小为20
• 若Threads_created通常保持在500~700之间，200的线程缓存应该足够大了

每个缓存中的线程通常使用256KB左右的内存

8.4.8表缓存（table_open_cahce 老版本为table_cache_size）

RDS上：table_open_size=100 ， table_definition_cache=512
默认上：table_open_size=64 ， table_definition_cache=400

表缓存的设计是设计服务器和存储引擎之间分离不彻底的产物，属于历史问题

• 对MyISAM来说，表缓存的真正好处是，可以让服务器避免修改MyISAM文件头来标记表“正在使用中”
• 表缓存对InnoDB的影响就小多了，因为InnoDB不依赖它来做那么多的事（比如持有文件描述符，InnoDB有自己的表缓存版本）。但也能从缓存解析的.frm中获益
• 在MySQL5.1之后分离成两部分：一是打开表的缓存，一是表定义缓存（table_open_size 和 table_definition_cache）

建议：

• 通常可以把table_definition_cache设置的足够高，以缓存所有的表定义
• 可以监控Opened_tables的每秒变化，如果变化比较大，那么可以增加table_open_cache（5.1之前为table_cache）。然而当创建和删除临时表时，要注意这个计数器的增长，如果经常要创建和删除临时表，那第计数器就会不停的增长
• 如果遇到MySQL无法打开更多文件错误（可用perror工具来查看错误代号的含义），那么可以在my.cnf中设置open_files_limit服务器变量来实现

8.4.9InnoDB数据字典（Data Dictionary）

InnoDB有自己的表缓存，可以称为表定义或者数据字典
相比MyISAM，InnoDB没有将统计信息持久化，而是每次打开表时重新计算，打开之后每隔一段时间或遇到突发事件也会重新计算统计信息�。如果有很多表，可能会花费数小时来启动并完成预热。可以在MySQL5.6中打开innodb_analyze_is_persistent来持久化统计信息到磁盘

8.5配置MySQL的I/O行为

8.5.1InnoDB的I/O配置

InnoDB的事务日志

本小节涉及三个配置项：

• innodb_log_file_size
• innodb_log_files_in_group
• innodb_flush_log_at_trx_commit

整体的日志文件大小受控于 innodb_log_file_size 和 innodb_log_files_in_group 这两个参数，这对写性能非常重要。日志文件的总大小是每个文件之和。默认情况下，只有两个5M的文件，总共10M。对高性能工作来说这太小了。至少需要几百MB，或者上G的日志文件

innodb_log_buffer_size 用于控制日志缓冲区的大小

通常不需要把日志缓冲区设置的非常大。推荐范围是1MB~8MB。有时分配大一点也是有好处的。如一台大内存服务器，可以简单的分配32M~128M日志缓冲区。（避免MUTEX竞争）

监控：

mysqladmin ext -ri1 |grep Innodb_os_log_written

若以上峰值是100KB/S, 那么

• 1MB 的日志缓冲区就足够了
• 256MB的文件足够存储2560秒的日志记录，这看起来也足够了。

作为一个经验法则，日志文件的全部大小应该足够容纳服务器一个小时的活动内容。

InnoDB怎样刷新日志缓冲，可以通过配置如下变量来实现：

innodb_flush_log_at_trx_commit 来控制刷新的频繁程度：

0: 把日志缓冲写到日志文件，并且每秒刷新一次，但是事务提交时不做任何事。

1: 把日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储（默认设置，也是最安全的设置）

2: 每次提交时 把日志缓冲写到日志文件，但并不刷新。InnoDB每秒刷新一次。

三者区别：在0时MySQL挂了会丢失一些事务。在2时只有机器挂了才会有可能丢失一些事务。而1时不会丢失任何事务

注：“把日志缓冲写到日志文件” 和 “把日志刷新到持久化存储” 的不同是：前者只是简单的写到了操作系统的缓冲区，并没有把数据写到持久化存储。

show variables like '%innodb_flush_log_at_trx_commit%';

高性能事务处理的最佳配置

把innodb_flush_log_at_trx_commit设置为1且把日志文件放到一个有电池保护的写缓存的RAID卷中。这兼顾了安全和速度

InnoDB怎样打开和刷新日志及数据文件

innodb_flush_method:配置InnoDB如何跟文件系统相互作用。它的可能值：

fdatasync

使用sync()系统调用。它的缺点是系统至少会在自己的缓存空间缓存一些数据。因为InnoDB也会缓冲数据，所以有时这种双重缓存是浪费的。但有时这种系统缓存的特性也是有帮助的。

innodb_file_per_table 选项会导致每个文件独立地做fund()

O_DIRECT

InnoDB使用 O_DIRECT标记，或 directio()，这取决于系统。它同样会使用finca()函数，但会告诉系统不要缓存数据。

当InnoDB和实际的存储设置之间没有缓冲时使用O_DIRECT选项，例如当RAID卡没有写缓存时，可能导致严重的性能问题。

如果不用innodb_file_per_table，当使用O_DIRECT时，可能由于产生一些顺序I/O而遭受性能损失

O_DSYNC

这个选项使日志文件调用 open()函数时设置O_SYNC标记。它便利所有的写同步（只有写到磁盘后才返回，但不禁用操作系统缓存）。

建议：

• 如果用类UNIX，且RAID控制器带有电池保护的写缓存，我们建议使用O_DIRECT
• 如果不是以上这种情况，默认值或O_DIRECT都可能是最好的选项

InnoDB表空间

innodb_file_per_table

我们建议使用innodb_file_per_table并且给共享表空间设置大小范围。

行的旧版本和表空间
有个例子：

show engine innodb status\G;

其中TRANSACTIONS的前两行为：
------------
TRANSACTIONS
------------
Trx id counter 446725
Purge done for trx's n:o < 443862 undo n:o < 0 state: running but idle

通过以上结果：446725 - 443862 = 2863 个”潜在的“没有被清理的事务。这里说“潜在的”是因为这跟很多没有清理的行是有区别的。只有改变了数据的事务才会创建旧版本的行（相反的，一个事务可能修改很多行）

innodb_max_purge_lag（新版本的MySQL的清理过程已经显著提升，所以这个配置基本用不到）

如果有个很大的回滚日志，并且表空间因此长的很快，可以强制MySQL减速来使InnoDB的清理线程可以跟得上。

为了控制写入速度，可以设置这个变量大于0，This value indicates the maximum number of transactions that can be waiting to be purged before InnoDB starts to delay further queries that update data.

双写缓冲，默认是打开的

innodb_doublewrite

可以设置该配置项为0来关闭双写缓冲

其它I/O配置项

sync_binlog

控制MySQL怎么刷新二进制日志到磁盘。默认是0

像InnoDB日志文件一样，把二进制日志放到一个带有电池保护的写缓存RAID卷，可以极大的提升性能。事实上，写和刷新二进制日志要比InnoDB事务日志要昂贵多了，因为不像InnoDB事务日志，每次写二进制日志都会增加它们的大小。这需要每次写入文件系统都要更新元信息。所以设置 sync_binlog = 1 可能比 innodb_flush_log_at_trx_commit = 1 对性能的损害要大得多，尤其是网络文件系统，如NFS。

8.5.2MyISAM的I/O配置

delay_key_write 配置延迟写入，延迟写入在某些场景下可能有帮助，但通常不会带来很大的性能提升。

当打开时修改的缓冲块直到表被关闭才会刷新。可能的配置如下:

OFF: MyISAM每次写操作后刷新键缓冲（Key Buffer）中的脏块到磁盘，除非表被LOCK TABLES锁定了。

ON: 打开延迟键写入，但是只对用DELAY_KEY_WRITE选项创建的表有效。

ALL: 所有的MyISAM表都使用延迟写入

myisam_recover（我们建议打开这个选项，尤其是只有一些小的MyISAM表时）

可以在配置文件配置，它控制MyISAM怎样尝试从损坏中恢复。可能的值有 DEFAULT(默认)、BACKUP、FORCE、QUICK。可以用多个设置，如“ BACKUP, FORCE”

myisam_use_mmap

打开数据文件的内存映射（MMAP）访问，内存映射使得MyISAM直接通过操作系统的页面缓存访问.MYD文件，避免系统调用的开销

8.6配置MySQL并发

8.6.1InnoDB迸发配置

innodb_thread_concurrency

并发值 = CPU数量 * 磁盘数量 * 2

但是在实践中，使用更小一点的值会更好一点。必须实验来找出更适合系统的更好值。

SHOW VARIABLES LIKE '%_concurrency%';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| innodb_commit_concurrency  | 0     |
| innodb_concurrency_tickets | 500   |
| innodb_thread_concurrency  | 0     |
| thread_concurrency             | 10    |
+----------------------------+-------+

这是RDS上的配置：
innodb_commit_concurrency	0
innodb_concurrency_tickets	5000
innodb_thread_concurrency	0
thread_concurrency	10

8.6.2MyISAM迸发配置

concurrent_insert

0: MyISAM不允许并发插入，所有插入都会对表加互斥锁

1: 这是默认值。只要表中没有空洞，MyISAM就允许并发插入

2: 强制并发插入到表的未尾，即使表中的空洞

如果合并操作可以更加高效，也可以配置MySQL对一些操作进行延迟。举个例子，可以通过delay_key_write变量延迟写索引（正如这一章前面提到的8.5.1）。

low_priority_updates

可以让INSERT、REPLACE、DELETE、以及UPDATE语句的优先级比SELECT更低，这让SELECT语句获得相当好的并发度

8.7基于工作负载的配置

8.7.1优化BLOB和TEXT的场景

本节将BLOB和TEXT统称为BLOB
一个最重要的注意事项是，服务器不能内存临时表中存储BLOB值，因此，如果一个查询涉及BLOB值，又需要临时表——不管它多小——它都会在磁盘上创建临时表。这样效率很低，尤其是对小而快的查询。临时表可能是查询中最大的开销
有两种方法可以减轻这个不得的情况：通过SUBSTRING() 把值转为VARCHAR类型 P123, 或让临时表更快一些

服务器设置里控制临时表文件放在哪的是tmpdir

也许可以调大InnoDB日志缓冲的大小

For long variable-length columns (e.g., BLOB, TEXT, and long character columns), InnoDB stores a 768-byte prefix in-page with the rest of the row. If the column’s value is longer than this prefix length, InnoDB might allocate external storage space outside the row to store the rest of the value.

Note that we said InnoDB might allocate external storage. If the total length of the row, including the full value of the long column, is shorter than InnoDB’s maximum row length (a little less than 8 KB), InnoDB will not allocate external storage even if the long column’s value exceeds the prefix length.

8.7.2优化排序

max_length_for_sort_data： 默认1KB。决定 two-pass排序 还是 single-pass排序

如果查询中所有需要的列和ORDER BY的列总大小超过max_length_for_sort_data字节，则采用two-pass算法。P368 P221

（可采用SUBSTRING把这些列换一下，就可以使用single-pass算法了 P118）

如果增加了max_length_for_sort_data变量的值，磁盘使用率上升了，CPU使用率下降了，并且Sort_merge_passes状态变量相对于修改之前开始很快的上升，也许是强制让很多排序使用了single-pass算法

max_sort_length 默认1K

当MySQL必须排序BLOB、TEXT字段时，它只会使用前缀，该变量定义了前缀的长度

8.8完成基本配置

tmp_table_size 和 max_heap_table_size

默认16M, 建议32M。每个线程都要分配。两者应该一致

Created_tmp_disk_tables / Created_tmp_tables < 5%

mysql的tmp_table_size和max_heap_table_size

max_connections

最大值不能超过16384，即使超过也以16384为准；

龙图2048；而RDS化3万（大半年）可以从2K升到16K

可以监控 Max_used_connections 状态随时间的变化

thread_cache_size

可设置成波动范围的两到三位大小，例如 Threads_connected 状态从150变化到175，则可以设置线程缓存为75

不用设置太大，256上限是个不错的估值

查看Thread_cached查看多少线程在缓存中了

一个相关的变量是 Slow_launch_threads。如果这个值过大，说明服务器出现问题了，但不能明确是哪的问题（这不是说你增加线程缓存就可以了，需要诊断）

table_cache_size （5.1之后拆分为table_open_size 和 table_definition_cache） 见8.4.8节

这个值从max_connections的10倍开始设置是比较有道理的，但是再次说明，在大部分场景下最好保持在10000以下甚至更低

8.9安全和稳定性的配置

expire_logs_days

建议设置为7~14天

max_allowed_packet

建议16M

max_connect_errors

1000000

skip_name_resolve

建议打开，默认是关闭的 见9.9

以下可以控制复制行为：

read_only

默认OFF。这个选项禁止没有特权的用户在备库做变更

skip_slave_start

这个选项试图阻止MySQL试图自动启动复制

slave_net_timeout

默认3600S，太长了，应该设置成60。发现断开并重新连接的等待时间

sync_master_info, sync_relay_log, and sync_relay_log_info

RDS上均为10000

这些选项，在MySQL5.5及更新版本中可用，解决复制中备库长期存在的问题：不把它们的状态同步到磁盘，所以崩溃后需要人工人猜测复制的位置实际在主库的什么位置。它们默认是不打开的。如果复制中出现fsync()造成的延时问题，就应该关闭它们

8.10高级InnoDB设置

innodb_io_capacity

默认200，太小了。InnoDB有时需要把这个设置的相当高（在像PCI-E SSD这样极快的存储设备上需要设置为上万）。

innodb_old_blocks_time

设置为1000比较好。

第九章操作系统和硬件优化

9.1什么限制了MySQL的性能

最常见的两个瓶颈是CPU和I/O

9.2如何选择CPU

快速CPU还是多CPU，有两个目标：

低延时（快速响应）

要做到这一点就需要高速CPU

高吞吐

如果能同时运行很多查询，则可以从多CPU中受益

MySQL复制，也能在高速CPU下工作的非常好，而多CPU对复制的帮助不大。而备库的瓶颈通常是I/O子系统而不是CPU（因为在备库上已经被简化成了串行化任务）

9.3平衡内存和磁盘资源

顺序I/O比随机I/O快

内存随机访问比磁盘随机访问快了2500倍，但顺序读只比磁盘快了10倍左右

假设每次读取100字节：

• 磁盘 随机寻道：100次/S ； 50M/S的顺序读
• 内存 随机访问：25万次/S； 顺序访问：500万次/S。内存的顺序读写速度一般都在10G/S以上

缓存命中率

缓存命中率实际上也会决定使用了多少CPU，所以评估缓存命中率的最好方法是看CPU的使用率。

例如，若CPU使用了99%的时间工作，用了1%时间等待I/O，那缓存命中率还是不错的。（REF: 7.12节论述了查询缓存，不要和这里的概念混了）

9.4固态存储

闪存最重要的两个特性：1、提升随机I/O；2、提高并发操作
写放大及垃圾收集 对性能影响较大（尤其是空间快满了的时候）

9.4.2闪存技术

有两种闪存类型：

1. 单层单元（SLC）：每个单元存储一个比特（0或1）。SLC相对更昂贵，但非常快，并且擦写寿命高达100000个写周期（20年左右）缺点是存储密度相对较低
2. 多层单元（MLC）：每个单元存储2个比特（3个比特设置也正在进入市场中）。这使得存储密度提高了，但速度和耐擦写性能降低了。一个不错的MLC设备可能被定为10000个写循环周期（2年）

9.4.3闪存的基准测试

闪存的三阶段模式，我们称为A-B-C模式：

1. A：开始阶段，通常运行比较快
2. B：过度状态状态，此时垃圾回收开始工作
3. C：稳定状态，此时最慢

作者的基准测试：

http://www.ssdperformanceblog.com   http://www.mysqlperformanceblog.com

9.4.4固态硬盘

SSD是否配置电容或者电池是我们必须关注的特性

9.4.5PCIe设备

没有什么能比得上PCIe设备上获得的性能。缺点是它们太贵了。

9.4.6什么时候应该使用闪存

1. 一些事务或插入繁忙的工作负载
2. 单线程负载是另一个闪存的潜在应用场景。MySQL复制是单线程工作的典型例子，它可以从低延迟中获得很多收益
3. 闪存也可以为服务器整合提供巨大的帮助，尤其是PCIe方式的。我们已经看到了机会，把很多实例整合到一台物理服务器——有时高达10或15倍的整合都是有可能的（详见11章）

然而缓存也可能不一定是你要的答案。一个很好的例子是，像InnoDB日志文件这样的顺序写的工作负载，闪存不能提供多少性能优势，因为这种情形下，闪存连续写方面不比标准硬盘快多少。这样的工作负载也是高吞吐的，会耗尽缓存的寿命。在标准硬盘上存放日志文件通常是一个更好的主意，用具有电池保护的写缓存的RAID控制器。
有时答案在内存/磁盘的比例，而不只是磁盘。

9.4.7使用Flashcache

Flashcache创建了一个块设备，并且可以被分区，也可以像其他块设备一样创建文件系统，特点是这个块设置是由闪存和磁盘共同支撑的。闪存设备用作读取和写入的告诉缓存。

• 尽管理论上Flashcache可能更高效，但最终性能并不如低层闪存那么好。
• 从测试来看，尚不清楚对写负载有多大好处，但对读肯定有好处。于是它适合这样的情况：有大量的读I/O，并且工作集比内存大的多。
• 除了实验，这里有个生产环境的实例经验：有个4T的数据库，这个数据库遇到了很大的复制延迟。我们给系统加了半个TB的Virident PCIe卡作为存储。然后安装了Flashcache，并且把PCIe作为绑定设备的闪存部分，复制速度就翻了一倍

Flashcache的IOPS思想实验

磁盘IPOS: 3,5" 15.000 rpm FC 150 IOPS（参考中列出的最好的一个）参考常见硬盘IOPS参考值 磁盘性能指标--IOPS 理论

假设闪存支持100000IPOS，未命中率为10%，则按磁盘150IPOS计算，则本Flashcache设备仅可以支持 150/10% = 1500IPOS，受制于磁盘！

9.4.9优化固态存储上的MySQL

关于插入缓冲：innodb insert buffer 插入缓冲区的理解InnoDB三大特性之-插入缓冲

禁用双缓冲

在闪存上禁用双缓冲差不多能提高性能50%。

建议用innodb_file_per_table

1、并把数据文件目录放到闪存设备。

2、然后移动ibdata1和日志文件，以及其它所有日志文件（二进制日志、复制日志、等等）到RAID卷

这会把随机I/O集中到缓存设备上，然后把大部分顺序写入的压力尽可能的移出闪存

9.6RAID性能优化

案例：RAID10 （硬件RAID1，软件RAID0 ReiserFS）获5倍性能提升

请求串行的发送到一个10块盘的RAID10卷中的每个设备，使用ReiserFS文件系统，InnoDB打开了inondb_file_pertable选项。尝试RAID1的基础上用RAID0做成的RAID10的方式，获得了5倍多的吞吐

基准测试

最后，当准备上线新服务器时不要忘了做基准测试！这能让你获得期望的性能。如。若一个硬盘能每秒做200个随机读，一个有8个硬盘驱动器的RAID10卷应该接近每秒1600个随机读。

RAID配置和缓存

除非控制器有电池备份单元（BBU）或其它非易失性存储，否则不应该启用RAID缓存。不带BBU的缓冲写，在断电时，有可能损坏数据库，甚至是事务性文件系统。

9.8使用多磁盘卷

InnoDB事务日志包含redo（ib_logfile0 ib_logfile1）日志和undo日志（存储于表空间 ibdata中MySQL · 引擎特性 · InnoDB undo log 漫游）

分离临时表目录

最好把它们入在内存文件系统，如tmpfs

9.9网络配置

skip_name_resolve

建议打开，默认是关闭的 见8.9

损坏或者变慢的的DNS解析对很多应用程序都是个问题。对MySQL尤其严重

若启用，则用户账户必须在host列使用具有唯一性的IP地址，“localhost”或者IP地址通配符。

back_log

默认50秒是不够的,建议3000

控制MySQL传入的TCP连接队列的大小。 设置不够的症状是，在3秒超时规则下，客户会看到零星的“连接被拒绝”的错误。在繁忙的系统中这个选项通常应加大。把这个选项增加到数百甚至数千，似乎没有任何副作用。事实上你应该看的更远一些，可能还需要配置操作系统的TCP网络设置。在GNU/Linux上需要增加somaxconn限制，并且需要检查tcp_max_syn_back_log设置

网络监控

MRTG 其它的还有Smokeping 、Cacti

内核调优

echo 1024 65535 > /proc/sys/net/ipv4/ip_local_port_range #改变本地端口范围

echo 4096 > /proc/sys/net/ipv4/tcp_max_syn_backlog #允许更多的连接进入队列

echo <value> > /proc/sys/net/ipv4/tcp_fin_timeout

风行网TK服务器内核调优实例：高并发情况下Linux服务器内核配置

如果需要更大带宽，可以使用网络链路聚合（NICs）（或叫多块网卡并行绑定）

9.11选择文件系统

在/etc/fstab中添加noatime、nodiratime挂载选项可禁用文件的访问时间这样有时可以提高5%~10%的性能

/dev/sda2 /usr/lib/mysql ext3 noatime,nodiratime,data=writeback 0 1

使用O_DIRECT选项会自动禁用预读。例如，InnoDB有自己的预读策略

9.12选择文件调度策略

Deadline对数据库环境(ORACLE RAC,MYSQL等)是最好的选择：浅谈linux性能调优之六:IO调度算法的选择

可按如下命令查询文件调度策略

cat /sys/block/vda/queue/scheduler

输出（其中方括号表示当前使用的调度策略）： noop anticipatory deadline [cfq]

cfg

完全公平队列（默认选项），这个用在个人电脑是最好的，但用在服务器则是有问题的

noop

适合那么自己在后台实现了高度算法的设备。如RAID和SAN

deadline

则对RAID和直接使用磁盘更好。我们的基准测试表明这两者差别很小（noop），重要的是别使用cfq

9.14内存交换区

用vmstat查看，我们喜欢si和so列的值为0，并且要保证它们低于每秒10个块
对交换空间利用率要做的监控和报警

以下设置告诉内核，除非虚拟内存满了，否则不要使用交换区

cat /proc/sys/vm/swappiness

echo 0 > /proc/sys/vm/swappines

另外一个选项是修改存储引擎怎么读取和写入数据

使用innodb_flush_method=O_DIRECT ，减轻I/O压力

Direct I/O并不缓存，因此操作系统并不能把MySQL视为增加文件缓存的原因。

另一个选择是使用MySQL的memlock配置项

可以把MySQL锁定在内存。这可以避免交换，但是也可能带来危险：如果没有足够的可锁定内存，MySQL在分配更多的内存时会崩溃。这也可能导致锁定的内存太多而没有足够的内存留给操作系统

9.15操作系统状态

安装iostat: yum install sysstat

9.15.1如何阅读vmstat的输出

以下是第5秒打印的一个报告：

结果

procs

r这一列表示多少进程正在等待CPU

b列显示多少进程正在不可中断的休眠（通常意味着正在等待I/O，如磁盘、网络、用户输入，等等）

memory

swiped列显示多少块被换出到了磁盘（页面交换）。剩下三列表示了多少块是空闲的（未被使用）、多少块正在被用作缓冲，以及多少正在用作操作系统的缓存

swap

显示了每秒有多少块正在被换入（si 从磁盘）和换出（so 到磁盘）

大部分时间我们喜欢看到si和so列的值为0，并且要保证它们低于每秒10个块。突发性的高峰一样很糟糕

io

显示有多少块设备的读取（bi）和写入（bo）。这通常反映了硬盘I/O

system

这些列显示了很秒中断（in）和上下文切换（cs）的数量

cpu

显示了各类操作的百分比。包括 执行用户代码（非内核）、执行系统代码（内核）、空闲、以及等待I/O

9.15.1如何阅读iostat的输出

以下是5秒一输出：

结果

rrq/s和wrq/s

每秒合并的读和写的请求。“合并的”意味着操作系统从队列中拿出多个逻辑请求合并为一个请求到实际磁盘

r/s和w/s

每秒发送到设备的读和写的请求

rsec/s和wsec/s

每秒读和写的扇区数。

avgrq-sz

请求的扇区数

avgqu-sz

在设备队列中等待的请求数

await，r_await, w_await

磁盘上排队花费的毫秒数

svctm

服务请求花费的毫秒数，不包括排队时间

%util

至少有一个活跃请求所占时间的百分比

可以利用以上输出服务器的并发数

concurrency = ( r/s + w/s ) * ( svctm/1000 )

其它工具：

mpstat

观察CPU统计

pt-diskstats

增强了hostat