前言
书中代码
下载:文件: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
准备
环境变量(我的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;
第三章 服务器性能剖析
安装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取代)
在会话级别使用
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示例(见右图-->)
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 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;
- 反范式化、预先计算和缓存可能是解决这类问题的仅有策略
- 限制用户有这么多的分页
- 另一个比较好的策略是使用延迟关联:
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 可以安全而又简单的完成这项工作
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.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;