前言

书中代码

下载：文件: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

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

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;

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

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>

第六章查询性能优化

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

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

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

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

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

6.3重构查询的方式

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

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

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

这里有两个MySQL5.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 可以安全而又简单的完成这项工作

Mysql 高性能笔记

目录

前言

书中代码

mysql示例数据库

mysqlreport

准备

第二章 MySQL基准测试

2.3 基准测试方法

2.3.3 获取系统性能和状态

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

2.4 MySQL的BENCHMARK()函数(P52)

2.5基准测试案例

2.5.1 http_load

2.5.2 MySQL基准测试套件

2.5.3 sysbench命令

2.5.5 Persona 的TCPP-MySQL测试工具

第三章 服务器性能剖析

安装Persona Toolkit

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

3.3 剖析MySQL查询

打开慢查询

tcpdump

3.3.2 剖析单条性能

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

使用SHOW STATUS

使用Performance Schema (>= MySQL5.5)

3.4诊断间歇性问题

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

使用SHOW GLOBAL STATUS 可用于监控

使用SHOW PROCESSLIST

使用查询日志

3.4.2 捕获诊断数据

捕获freeing items状态的数目

几个PT工具及依赖关系

oprofile

gdb

pt-pmp 堆栈信息

统计临时表

3.5其它工具 strace

第四章 Schema与数据类型优化

4.1选择要优化的数据类型

4.4缓存表和汇总表

4.4.1物化视图 TODO:练习Flexviews P134

4.4.2计数器表

4.5ALTER TABLE提速

第五章创建高性能索引

索引基础

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

关于三星索引 P152

5.3高性能索引策略

前缀索引和索引选择性

5.3.4选择合适的索引顺序

5.3.6覆盖索引

5.3.7使用索引扫描来做排序

5.3.9冗余和重复索引

5.3.11索引和锁

5.4索引案例学习

优化排序

5.5维护索引和表

5.5.2更新索引统计信息

5.5.3减少索引和数据碎片

第六章 查询性能优化

6.3重构查询的方式

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

6.3.2切分简单的查询

导航菜单

搜索

第三章服务器性能剖析

第六章查询性能优化