查看“Mysql 高性能笔记”的源代码
←
Mysql 高性能笔记
跳转至：
导航
、
搜索
因为以下原因，你没有权限编辑本页：
该页面已被保护以防止编辑和其他操作。
您可以查看并复制此页面的源代码：
[[category:MySQL]] =前言= ==书中代码== 下载：[[文件:Mysql高性能 随书源码.zip|缩略图|mysql高性能_随书源码]] 官网链接： http://www.highperfmysql.com/#downloads ==mysql示例数据库== http://dev.mysql.com/doc/index-other.html ==mysqlreport== <pre style='white-space: pre-wrap; word-wrap: break-word;'> #安装 yum install mysqlreport yum install perl-DBD-MySQL #运行 mysqlreport --user root --password YourPassword --socket=/tmp/mysql.sock </pre> [[文件:Mysqlreport20160901.log|缩略图|mysqlreport20160901.log]] ==准备== 环境变量（我的LINUX服务器），若要长期有效可将以下脚本追加在 /etc/profile中 <pre> 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 </pre> 我的MAC: export PATH=/Applications/XAMPP/xamppfiles/bin:$PATH =第二章 MySQL基准测试= ==2.3 基准测试方法== ===2.3.3 获取系统性能和状态=== 收集MySQL测试数据的Shell脚本（P43） gather.sh： <pre> #!/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. </pre> ===2.3.5 运行基准测试并分析结果=== 以下脚本实现了从前脚本输出的数据中抽取时间维度的信息 analyze.sh： '''./analyze.sh 5 -sec-status-2016-08-31''' <pre> #!/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 } ' "$@" </pre> ==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 <pre> 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 </pre> 测试： <pre> 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/ </pre> ===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)： <pre> -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 </pre> ===2.5.3 sysbench命令=== <pre style='white-space: pre-wrap; word-wrap: break-word;'> #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 </pre> ===2.5.5 Persona 的TCPP-MySQL测试工具=== 安装(在shell中执行) <pre> 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 .. </pre> 测试(在自己的测试服务器上运行了3个小时) <pre> 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 </pre> 查看数据库统计情况（自己总结的语句） <pre style='white-space: pre-wrap; word-wrap: break-word;'> 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; </pre> =第三章 服务器性能剖析= [http://dev.mysql.com/doc/index-other.html mysql示例数据库] ===安装Persona Toolkit=== <pre> #参考 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 </pre> ===3.2对应用程序进行性能剖析=== * New Relic * xhprof ==3.3 剖析MySQL查询== ===打开慢查询=== 编辑 my.conf 在[mysqld]下方加入慢查询的配置语句： <pre> slow_query_log = true long_query_time = 1 slow_query_log_file = /data/mysql.slow.log </pre> 注： 在我的MAC上则是如下路径：slow_query_log_file = /Applications/XAMPP/xamppfiles/var/mysql/mysql_slow.log 在MySQL5.6之前还可用如下方式（5.6以后废弃） <pre> log-slow-queries = /data/mysql.slow.log long_query_time = 1 </pre> 参考 http://jingyan.baidu.com/article/0aa223755476db88cc0d6492.html http://blog.csdn.net/lwprain/article/details/8802379 如果要长期开启慢查询日志，注意要部署'''日志轮换（log rotation）'''工具 分析慢查询 pt-query-digest slow.log === tcpdump=== <pre> tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt pt-query-digest --type tcpdump mysql.tcp.txt </pre> [[文件:Pt-query-digest--tcpdump20160901.log|缩略图|居中|pt-query-digest--tcpdump20160901.log]] ===3.3.2 剖析单条性能=== ====使用SHOW PROFILE （未来可能被Performance Schema取代）==== [[文件:ShowProfileExample_new.png|缩略图|ShowProfile示例|right|500px]] 在会话级别使用 <pre> set profiling = 1; SELECT * FROM sakila.nicer_but_slower_film_list; show profiles; show profile for query 2; </pre> 以上是默认输出，若要灵活显示则执行以下语句（结果如右图--> ）： <pre> 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; </pre> ====使用SHOW STATUS==== [[文件:Show status example.jpeg|350px|缩略图|有框|右|show status 示例]] SHOW STATUS示例（见右图-->） <pre> FLUSH STATUS; SELECT * FROM sakila.nicer_but_slower_film_list; SHOW STATUS WHERE Variable_name LIKE 'Handler%' OR Variable_name LIKE 'Created%'; </pre> 注：show status 也会创建一个临时表，从而影响结果中的数字（而且不同版本可能的行为也不尽相同）。比较前面通过show profiles获得的查询的执行计划的结果来看，至少临时表的计数器多加了2. 附：查看sakila数据库中各表行数 <pre style='white-space: pre-wrap; word-wrap: break-word;'> 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; </pre> ====使用Performance Schema (>= MySQL5.5)==== 以下显示系统等待的主要原因 <pre style='white-space: pre-wrap; word-wrap: break-word;'> 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; </pre> [[文件:Performance schema result.png|center|450px|缩略图|结果]] ==3.4诊断间歇性问题== ===3.4.1 单条问题还是服务器问题=== ====使用SHOW GLOBAL STATUS 可用于监控==== [[文件:ShowGlobalStatusStatResult.png|150px|缩略图|right|结果]] 以下脚本没个一秒执行一次，第一列为每秒查询数，第二列为Threads_connected，第三列为Threads_running（正在执行的查询数）： <pre> 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}' </pre> 这三个数据对数据库偶尔停顿敏感度比较高。 ====使用SHOW PROCESSLIST==== 以下命令用来计算State列值出现的次数 如果大量的线程处于 “freeing items” 状态是出现了大量有问题查询的很明显的特征和指示 mysql -uroot -e 'SHOW PROCESSLIST\G' | grep State: | sort | uniq -c | sort -nr [[文件:ShowProcessListResult.png|450px|缩略图|居中|结果]] ====使用查询日志==== 以下命令也可以通过 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 [[文件:SlowLogStatResult.png|450px|缩略图|居中|结果]] ===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-mysql-summary20160901]]<br /> [[文件:Pt-summary20160901.log|缩略图|pt-summary20160901.log]] #pt-sift - Browses files created by pt-stalk. 这个工具会轮流导航到所有的样本数据，得到每个样本的汇总信息 #用法 pt-sift FILE|PREFIX|DIRECTORY ====oprofile==== <pre> 安装 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库显示检测结果 </pre> [[文件:OpfileExample.png|500px|居中|缩略图|结果]] ====gdb==== [[文件:GdbBtOfMysqldExample.png|600px|缩略图|右|结果]] gdb命令参考连接 http://blog.chinaunix.net/uid-20696246-id-1892093.html <pre> pidof mysqld #得到mysqld的pid为 1504 gdb (gdb) attach 1504 (gdb) bt (gdb) c (gdb) detach (gdb) q </pre> ====pt-pmp 堆栈信息==== 比上面gdb的要强大 <pre> pt-pmp -l 5 或 pt-pmp -l 5 stacktraces.txt </pre> ===统计临时表=== 其中lsof.txt是lsof每5秒采集一次的汇总数据。 以下命令对以上文件中临时表的数据进行了加总输出 <pre> $ awk ' /mysqld.*tmp/ { total += $7; } /^Sun Mar 28/ && total { printf "%s %7.2f MB\n", $4, total/1024/1024; total = 0; }' lsof.txt </pre> ==3.5其它工具 strace == 通过strace统计mysql系统调用的情况 $ strace -cfp $(pidof mysqld) pt-ioprofile 这个工具依赖于strace =第四章 Schema与数据类型优化= ==4.1选择要优化的数据类型== 更小的通常更好、简单就好、尽量避免NULL<br /> 整形：TINYINT 8位 , SMALLINT 16, MEDIUMINT 24, INT 32, BIGINT 64<br /> 布尔类型的技巧：用CHAR(0) 表示，true/false分别用 NULL/空字符串 来表示<br /> 要避免使用BIT类型<br /> 几个常用的函数 <pre> FROM_UNIXTIME() #把Unix时间戳转换成日期 P122 UNIX_TIMESTAMP() #日期转换成时间戳 #存储UUID的一个技巧：移除“-”号，用UNHEX()转换成16字节的数字，并存储在BINARY(16)的列中。 UNHEX() #转换成10进制P126 HEX() #转换成16进制 #IPv4和INT间的转换 INET_NTOA() INET_ATON() </pre> #存储UUID的一个技巧：移除“-”号, 然后转换成10进制 select conv(replace(uuid(),'-',''),16,10); ==4.4缓存表和汇总表== ===4.4.1物化视图 TODO:练习Flexviews P134 P274=== ===4.4.2计数器表=== 使用独立的表可以帮助避免查询缓存失效 例1：网站计数，思路添加100行，每次随机找一行将cnt++ <pre> #建表： 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; </pre> 例2：按天计数。这里更新计数时用到了一个技巧： '''ON DUPLICATE KEY UPDATE''' <pre> #建表： 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; </pre> ==4.5ALTER TABLE提速== 假设要改变一个列的默认值，则可用ALTER COLUMN，此方法可以防止表重建： ALTER TABLE sakila.film '''ALTER COLUMN''' rental_duration SET DEFAULT 5; =第五章创建高性能索引= ==索引基础== ===创建自定义哈希索引，示例：=== <pre> #创建表 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/"); </pre> 自己定一个HA64位函数： SELECT CONV(RIGHT(MD5('http://www.mysql.com/'), 16), 16, 10) AS HASH64; ==关于三星索引 P152== 一星索引：索引将相关的记录放到一起<br /> 二星索引：索引中的数据顺序和查找中的排列顺序一致<br /> 三星索引：索引中的列包含了查询中需要的全部列 ==5.3高性能索引策略== ===前缀索引和索引选择性=== 缺点：MySQL无法使用前缀索引做ORDER BY 和 GROUP BY ，也无法使用前缀索引做覆盖扫描 <pre> #计算不同的选择性 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)); </pre> 而对于'''后缀索引'''，可以把字符串反转后做成前缀索引，并用触发器来维护。 ===5.3.4选择合适的索引顺序=== 经验法则：将选择性最高的列放到索引的最前列。但通常不如避免随机IO和排序那么重要 联合索引的选择性预测（ 从结果可见，应把customer_id放前面）： <pre> 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 </pre> 以上是个例（一般是从诸如pt-query-digest中提取出的最差查询），<br /> 一个通用的方法是计算选择性： 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”的信息'''。 <big>坑：</big> 假设索引覆盖了WHERE，但不是整个查询涉及的字段。如果条件为假（false），MYSQL5.5和更早的版本也总是会回表获取数据行，尽管不需要这一行<br /> '''MySQL5.6已经解决该问题，因为它已经实现了索引条件下推，参见：'''http://mdba.cn/2014/01/21/index-condition-pushdownicp索引条件下推/<br /> 以下查询中索引无法覆盖查询，有两个原因： *没有任何索引能覆盖这个查询 *MySQL不能在索引中执行LIKE操作 <pre> EXPLAIN SELECT * FROM products WHERE actor='SEAN CARREY' AND title like '%APOLLO%'\G </pre> 以下是改进： 先将索引扩展至覆盖三个数据列：( artist, title, prod_id )<br /> 我们把这种方式叫做延迟关联（deffered join）。在查询的第一阶段MySQL可以使用覆盖索引，在FROM子句的子查询中找到prod_id，然后外层再根据这些prod_id在外层查询匹配获取需要的所有列值。<br /> 是否需要这种改进要看应用场景，'''对照测试的结果参见教材P174'''<br /> '''MySQL5.6已经无需这种方式了，因为它已经实现了索引条件下推 即ICP''' [http://mdba.cn/2014/01/21/index-condition-pushdownicp索引条件下推/ MySQL5.6之Index Condition Pushdown(ICP,索引条件下推)] 如果执行计划中的Extra信息为“using index condition”，表示优化器使用的index condition pushdown <pre> 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 </pre> ===5.3.7使用索引扫描来做排序=== *如果EXPLAIN出来的type值为“index”，则说明MySQL用了索引扫描来排序（不要和Extra的“Using index”搞混了） *如果索引不能覆盖查询所有的列，则按索引顺序读取数据的速度通常要比顺序的全表扫描慢 *只有当索引的列顺序和ORDER BY的子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，MySQL才能够使用索引来对结果做排序（如果要按不同的方向排序，一个技巧时存储该列值的反转串或者相反数） *如果查询需要关联多张表，则只有当ORDER BY子句引用的字段全部为第一个表时，才能使用索引做排序 *ORDER BY子句和查找限制是一样的：需要满足索引的最左前缀的要求。（有一种例外，就是前导列为常量，如下例子： ） <pre> #表中有索引 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; </pre> 而以下则'''不行'''： <pre> ... 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也相当于范围查询 </pre> ===5.3.9冗余和重复索引=== 对于InnoDB来说任何二级索引都不需要添加主键。如索引：（A, ID)，其中ID是主键，对于InnoDB来说主键已经包含在二级索引中了，所以这也是冗余的。<br /> 大多数情况下都不需要冗余索引，但有时候出于对性能的考虑需要冗余索引。见P179<br /> 检查冗余索引的一个工具：<br /> '''pt-duplicate-key-checker --user=root --password=YourPassword''' 另一个工具： pt-upgrade 用来检查计划中的索引变更 以下命令打印索引的使用情况<br /> '''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; #反范式化、预先计算和缓存可能是解决这类问题的仅有策略 #限制用户有这么多的分页 #另一个比较好的策略是使用延迟关联： <pre> 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>); </pre> ==5.5维护索引和表== <pre> check table film; repair table film; </pre> 如果存储引擎不支持以上命令，也可通过一个不做任何操作的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减少索引和数据碎片== <PRE> 可以通过执行'''OPTIMIZE TABLE''' 或者再导入的方式重新整理数据 InnoDB可以通过重建索引来消除碎片化 通过什么都不做的ALTER重建表（InnoDB只能重建数据碎片，实际是就是聚蔟索引）：ALTER TABLE <table> ENGINE=<engine> </PRE> =第六章 查询性能优化= [http://bbs.csdn.net/topics/390735307#post-396983386 | EXPLAIN里type的含义] *扫描行数/返回行数 一般在 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次的查询 所以运行多个小查询现在已经不是大问题了。<br /> MySQL内部每秒能扫描内存中上百万行数据<br /> 这里有两个MySQL5.7的性能测试：<br /> *[http://www.oschina.net/translate/the-road-to-500k-qps-with-mysql-5-7| MySQL性能：使用 MySQL 5.7 实现每秒 50 万查询 ] *[http://dimitrik.free.fr/blog/archives/2013/11/mysql-performance-over-1m-qps-with-innodb-memcached-plugin-in-mysql-57.html|MySQL Performance: over 1M QPS with InnoDB Memcached Plugin in MySQL 5.7 ] ===6.3.2切分简单的查询=== 如下例子，当删除大表时，一次只删除10000行。好处是：不会一次锁住很多资源；节省系统资源；不会阻塞很多小的但重要的查询<br /> 一次只删除一万行是比较高效且对服务器影响最小的做法 <pre> 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 </pre> '''另：pt-archiver 可以安全而又简单的完成这项工作''' ===6.3.3分解关联查询=== 将每一个表进行单表查询，而不是联表<br /> 这样做的好处： *让缓存效率更高 *将查询分解后，执行单个查询可以减少锁的竞争 *在应用层进行拆分，可以更容易的对数据库进行拆分 *查询本身性能也有可能提升 *可以减少冗余记录的查询。 *更进一步，这样做相当于在应用层做了哈希关联，而不是MySQL的嵌套循环关联 ==6.4查询执行的基础== ===6.4.3查询优化处理=== <big>'''查询优化器'''</big><br /> <big>''优化策略可分两种：P210''</big> ;静态优化:直接对解析树进行分析，并完成优化，静态优化不依赖特别的数值，如WHERE中带的一些常数等，静态优化在第一次完成后就一直有效，即使带着不同参数重复查询 ;动态优化:和查询的上下文有关，也可能和其它因素有关，可以认为这是一种“运行时优化”。对查询的动态优化在每次执行的时候都需要重新评估。有时在执行过程中也会重新优化。例如： :在关联操作中，范围检查的执行计划会针对每一行重新评估索引。可以通过EXPLAIN执行计划中的Extra列是否含有“reange check for each record”来确认这一点。该执行计划还会增加select_full_range_join这个服务器变量的值 <big>''MySQL能够处理的一些优化类型：P210''</big> ;重新定义表的连接顺序: ;将外连接转换成内连接 ;使用等价变换规则 ;优化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的处理速度会更快 <big>''数据库如何执行关联查询''</big><br /> 从本质上说MySQL对所有的类型的查询都以同样的方式运行。例如，在MySQL在FROM子句中遇到子查询时，先执行子查询，并将其结果放到一个临时表中，然后将这个临时表当成一个普通表对待<br /> MySQL的临时表是没有任何索引的 ====STRAIGHT_JOIN 手动指定关联顺序==== <pre> #让查询优化器自动确认关联顺序 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%'; </pre> 如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行计划树的成本，最后返回一个最优的执行计划 ====排序优化==== ;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 <pre> (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; </pre> ;索引合并优化:在>=5.0版本中，当WHERE包含多个复杂条件的时候，MySQL能够访问单个表的'''多个索引以合并和交叉过滤'''的方式来定位需要查找的行 ;松散索引扫描（MySQL5.6已支持 可通过SHOW VARIABLES LIKE '%optimizer_switch%';查看）:在EXPLAIN的Extra字段显示“'''Using index for group-by'''”，表示这里将使用松散索引扫描。 <pre> 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 </pre> ==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 来指定是否对排序和分组有效 <br /> <big>在5.0及之后，新增了一些参数来控制优化器的行为：</big> ;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; :而同样功能，以下是使用分组列进行分组，且效率不如以上语句好<font color="red">（但极力建议这样写）</font>： :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; 如果这个表非常大，那么这个查询最好改写成下面的样子（延迟关联）： <pre> 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); </pre> [[文件:Limit test result.png|900px|缩略图|居中|结果]] ===6.7.6优化SQL_CALC_FOUND_ROWS=== SQL_CALC_FOUND_ROWS会同时返回总行数<br /> 但一个更好的设计是将具体的页数换成“下一页”按钮。假设每页显示20条，那么我们每次都用LIMIT返回21条并只显示20条，如果第21条存在那么我们就显示下一页按钮。 ===6.7.7UNION查询=== MySQL总是利用临时表的方式执行UNION查询。所有要经常手工地将WHERE、LIMIT、ORDER BY等子名下推到UNION的各个子查询中去。<br /> 除非确实需要服务器消除重复行，否则就一定要使用UNION ALL，如果没有ALL则MySQL会给临时表加上DISTINCT选项，这会导致对整个临时表做唯一性检查，这样做代价非常高。 ===6.7.9使用自定义变量=== 例1：变量示意： <pre> mysql> SET @one := 1; mysql> SET @min_actor := (SELECT MIN(actor_id) FROM sakila.actor); mysql> SET @last_week := CURRENT_DATE-INTERVAL 1 WEEK; </pre> 例2：优化排名语句： <pre> 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; </pre> 例3：统计更新和插入的数量（ INSERT ON DUPLICATE KEY UPDATE）P248： <pre> INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1) ON DUPLICATE KEY UPDATE c1 = VALUES(c1) + ( 0 * ( @x := @x +1 ) ); </pre> 例4：编写偷懒的UNION： <pre> 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; </pre> 以上语句中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'; 临时表的数据是会被清空的,你断开了连接就会被自动清空<br /> <big>实现视图有两种算法：</big> *合并算法（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不能加载文件）： <pre> 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; </pre> ==7.3外键约束== 在某些场景下，外键会提升一些性能。如果想确保两个表始终有一致的数据，那么使用外键比在应用程序中检查一致性的性能要高很多，此外，外键在相关数据的删除和更新上，也比在应用程序中检查一致性要高很多，不过外键维护操作是逐行进行的。 ==7.4在MySQL内部存储代码== 缺点： *它和基于语句的二进制日志复制合作的并不好<br /> 以下是一个例子： <pre> 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 ; </pre> ===7.4.2触发器=== 要注意以下几点： *对每一个表的每一个事件，最多只能定义一个触发器（换句话说不能在AFTER INSERT上定义两个触发器） *MySQL只支持基于行的触发 *触发器可能掩盖服务器背后的工作 *触发器的问题很难排查 *触发器可能导致死锁和等待。如果触发器失败那么原来的SQL语句也会失败。如果没有意识到这是触发器在搞鬼，那么很难理解服务器抛出的错误代码是什么意思 '''在InnoDB上的触发器是在同一个事务中完成的，所以它们执行的操作是原子的（MyISAM不是）'''<br /> 这里有个绕过“基于行的触发”的技巧（P281）： <pre> 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; </pre> ===7.4.3事件=== *它类似于Linux的定时任务，可以指定MySQL在某个时候执行一段SQL代码，或者每隔一段时间执行一段SQL代码。通常我们把复杂的SQL封装到一个存储过程中，这样事件在执行的时候只需要做一个简单的CALL调用。<br /> *事件在一个独立的事件调度线程里初始化，这个线程和处理连接的线程没有任何关系。它不接收任何参数，也没有任何返回值。可能在MySQL的日志中看到命令的执行日志，还可以在INFORMATION_SCHEMA.EVENTS中看到各个事件的状态，如这个事件最后一次被执行的时间。 例1：以下事件每周一次针对某个数据库运行一个存储过程（optimize_tables见7.6.2）： <pre> CREATE EVENT optimize_somedb ON SCHEDULE EVERY 1 WEEK DO CALL optimize_tables('somedb'); </pre> 例2：另外可以以使用GET_LOCK()来确保当前只有一个事件在被执行（这里的CONTINUE HANLDER用来确保，即使当事件执行出现了异常，仍然会释放持有的锁）： <pre> 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 </pre> MySQL中有一个事件调度线程，默认是关闭的，必须在MySQL配置文件中设置，或者使用下面的命令来设置： '''SET GLOBAL event_scheduler := 1;''' *你可以通过MySQL的错误日志来了解事件的执行情况。 *虽然事件是一个单独的线程，但是事件来身是可以并行执行的。MySQL会创建一个新的进程用于事件执行 ===7.4.4在存储程序中保存注释=== 技巧：用版本号的注释且用一个非常大的数字，如99999 <pre> 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; </pre> ==7.6绑定变量== 基于如下原因，MySQL在使用绑定变量时可以更高效的执行大量重复的语句： *在服务器端只需要解析一次SQL语句 *在服务器端某些优化器的工作只需要执行一次，因为它会缓存一些执行计划。 *以二进制方式只发送参数和句柄，比起每次都发送ASCII码广西效率更高，一个二进制日期字段只需要三个字节，但如果是ASCII码则需要10个字节。不过最大的节省还是来自BLOB和TEXT，二进制协议不仅节省了网络开销还，另外还节省了将数据从存储格式转换成文本格式的开销 *仅仅是参数——而不是整个语句，要发送到服务端 *MySQL在存储参数的时候，直接将其放到缓存中，不再需要在内存中多次复制 绑定变量相对也更安全<br /> ===7.6.2SQL接口的绑定变量=== <pre> 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; </pre> 存储过程：optimize_tables：<br /> 调用方式 mysql> '''CALL optimize_tables('sakila');''' <pre> 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 ; </pre> 还有另和种实现存储过程循环的办法是： <pre> 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; </pre> ==7.7用户自定义函数== [http://www.mysqludf.org 最大的UDF仓库] ==7.9字符集和校对== 使用如下语句检查数据库的字符集设置： show variables like '%character%'; 修改MySQL字符集的方法（[http://jingyan.baidu.com/article/647f0115937be97f2148a894.html 参考]）： <pre> [mysql] default-character-set=utf8 [mysqld] character-set-server=utf8 </pre> <big>服务端和客户端的通信设置：</big><br /> [[文件:Mysql charset connection.png|600px|缩略图|居中|MySQL服务端和客户端的通信设置]] *假设使用latin1字符集（这是默认字符集）打开一个连接，并使用 SET NAMES utf8 来告诉服务器客户端将使用UTF-8字符集来传输数据。这样就创建了一个不匹配的字符集，可能会导致一些错误甚至出现一些安全性问题。应当先设置客户端字符集之后再使用函数 mysql_real_escape_string() 在需要的时候进行转义。在PHP中可以使用mysql_set_charset() 来修改客户端的字符集 *如果比较的两个字符串的字符集不同，MySQL会先将其转换成同一个字符集再进行比较 还可以使用前缀和COLLATE子句来指定字符串的字符集或者校对字符集： <pre> mysql> SELECT _utf8 'hello world' COLLATE utf8_bin; +--------------------------------------+ | _utf8 'hello world' COLLATE utf8_bin | +--------------------------------------+ | hello world | +--------------------------------------+ </pre> ;LOAD DATA INFILE:当使用LOAD DATA INFILE的时候，数据库总是将文件中的字符按照字符集 character_set_database来解析。我们发现指定字符集最好的方式是先使用USE指定数据库，再执行SET NAMES来设定字符集，然后再添加数据 ;LENGTH和CHAR_LENGTH:这两个是用来计算字符器的长度，在多字节字符集中，这两个函数返回的结果不同。例如在用SUBSTRING操作的时候要用CHAR_LENGTH ==7.10全文索引== 目前只有MyISAM支持全文索引，5.6及之后InnoDB也支持全文索引 <big>自然语言的全文索引</big><br /> <pre style='white-space: pre-wrap; word-wrap: break-word;'> #例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; </pre> <big>布尔全文索引</big><br /> {| class="wikitable" |- ! Example !! Meaning |- | dinosaur || 包含“dinosaur”的更高rank值（Rows containing “dinosaur” rank higher.） |- | ~dinosaur || 包含“dinosaur”的更低rank值（Rows containing “dinosaur” rank lower.） |- | +dinosaur || 行记录必须包含“dinosaur” |- | -dinosaur || 行记录不可以包含“dinosaur” |- | dino* || Rows containing words that begin with “dino” rank higher. |} 如： <pre> 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); </pre> <big>限制和替代方案</big><br /> ;全文索引的同时查找根据作者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）事务== <big>内部XA事务</big><br /> innodb_support_xa要打开（默认），才保证二进制日志安全<br /> 如果希望数据尽可能的安全，最好还要将sync_binlog打开。查看当前值的查询如下： show variables like '%sync_binlog%'; <big>外部XA事务 应尽量避免</big><br /> ==7.12查询缓存== 如果表发生变化，那么这个表相关的所有缓存都将失效<br /> '''很多时候我们认为还是应该关闭查询缓存，如果查询缓存作用很大的话，那就配置一个很小的查询缓存（如几十兆）'''。后面我们将解释如果判断在你的系统压力下打开查询缓存是否有好处。 ===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配置的工作原理== [http://tools.percona.com 作者维护的一个调优工具]<br /> [http://www.52codes.net/article/4634.html 一个参数配置示例]<br /> 如果不知道当前配置文件的使用路径，可以尝试下面的操作： <pre> $ 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 </pre> ;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配置文件== '''以下是基础配置文件：''' {| |- | [mysqld] || |- | # GENERAL || |- | matador || = /var/lib/mysql |- | socket || = /var/lib/mysql/mysql.sock |- | pid_file || = /var/lib/mysql/mysql.pid |- | user || = mysql |- | port || = 3306 |- | storage_engine || = InnoDB |- | # INNODB || |- | innodb_buffer_pool_size || = <value> |- | innodb_log_file_size || = <value> |- | innodb_file_per_table || =1 |- | innodb_flush_method || = O_DIRECT |- | # MyISAM || |- | key_buffer_size || = <value> |- | # LOGGING || |- | log_error || = /var/lib/mysql/mysql-error.log |- | log_slow_queries || = /var/lib/mysql/mysql-slow.log |- | # OTHER || |- | tmp_table_size || = 32M |- | max_heap_table_size || = 32M |- | query_cache_type || = 0 |- | query_cache_size || = 0 |- | max_connections || = <value> |- | thread_cache_size || = <value> |- | table_cache_size || = <value> || #5.1之后为 table_open_cache |- | open_files_limit || = 65535 |- | [client] || |- | socket || = /var/lib/mysql/mysql.sock |- | port || = 3306 |- |} 建议： *如果是简单的工作负载，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配置内存使用== 按如下步骤来配置内存： #确定可用内存上限 #确定每个连接MySQL需要使用多少内存，例如排序缓冲和临时表 #确定操作系统要多少内存。包括同机器上的其它程序要多少内存，如定时任务 #把剩下的全部给MySQL的缓存，例如InnoDB的缓冲池，这样做很有意义 ;为操作系统保留内存:应当至少为操作系统保留1~2G的内存——如果机器内存更多就多留一些。建议2G或总内存的5%，以较大者为准。<br /> ===8.4.4为缓存分配内存=== 下面是我们认为对大部分情况来说最重要的缓存： *InnoDB缓存 *InnoDB日志文件和MyISAM数据的操作系统缓存 *MyISAM键缓存 *查询缓存 *无法手工配置的缓存，例如二进制日志和表定义文件的操作系统缓存 ===8.4.5InnoDB缓存池=== InnoDB不仅仅缓存索引：它还会缓存行数据、自适应哈希索引、插入缓冲（Insert Buffer）、锁，以及其他内部数据结构。InnoDB还使用缓冲池来帮助延迟写入，这样就能合并多个写入操作，然后一起顺序的响应。总之，InnoDB严重依赖缓冲池。可能通过SHOW命令或innotop这样的工具来监控InnoDB缓冲池的使用<br /> InnoDB的监控工具innotop: <pre> #参考 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 </pre> 很大的缓冲池了会带来一些挑战： *如预热和关闭都会花费很长时间。 *如果有很多脏页在缓冲池里，InnoDB可能会花费很长时间。也可以强制关闭，但重启时就要花费很长时间。如果事先知道何时关闭InnoDB，可以在运行时修改 '''innodb_max_dirty_pages_pct''' 变量，将值改小等待刷新线程清理缓冲池，然后在脏页比较少时关闭。可以监控 '''Innodb_buffer_pool_page_dirty'''来监控脏页的刷新量。 [http://www.myexception.cn/mysql/1582053.html MySQL5.6：高速预热Buffer_Pool缓冲池]<br /> [http://www.linuxidc.com/Linux/2012-07/66262.htm MySQL5.6新特性快速预热Buffer_Pool缓冲池]<br /> ===8.4.6MyISAM键缓存（Key Caches）=== 不像其它存储引擎，MyISAM自身只缓存索引，不缓存数据（依赖操作系统缓存数据）。如果大部分表是MyISAM表，就应该为键缓存分配较多的内存。<br /> 查看MyISAM实际占用了多少空间的两种方法： * SELECT SUM(INDEX_LENGTH) FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE='MYISAM'; * $ '''du -sch `find /path/to/mysql/data/directory/ -name "*.MYI"`''' ;应该把键缓存设置的多大？:不要超过索引的总大小，或者不超过操作系统缓存保留总内存的25%~50%，以更小为准 配置多缓冲区： <pre> key_buffer_1.key_buffer_size = 1G key_buffer_2.key_buffer_size = 1G </pre> 加载缓冲区 *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变量<br /> 一个好的办法是观察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）=== 表缓存的设计是设计服务器和存储引擎之间分离不彻底的产物，属于历史问题 *对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有自己的表缓存，可以称为表定义或者数据字典<br /> 相比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的日志文件'''<br /> ;'''innodb_log_buffer_size''' 用于控制日志缓冲区的大小:通常不需要把日志缓冲区设置的非常大。推荐范围是1MB~8MB。有时分配大一点也是有好处的。如一台大内存服务器，可以简单的分配32M~128M日志缓冲区。（避免MUTEX竞争）<br /> 监控： : '''mysqladmin ext -ri1 |grep Innodb_os_log_written''' :若以上峰值是100KB/S, 那么 :*1MB 的日志缓冲区就足够了 :*256MB的文件足够存储2560秒的日志记录，这看起来也足够了。 :'''作为一个经验法则，日志文件的全部大小应该足够容纳服务器一个小时的活动内容。'''<br /> InnoDB怎样刷新日志缓冲，可以通过配置如下变量来实现：<br /> ;innodb_flush_log_at_trx_commit 来控制刷新的频繁程度： :0: 把日志缓冲写到日志文件，并且每秒刷新一次，但是事务提交时不做任何事。 :1: 把日志缓冲写到日志文件，并且每次事务提交都刷新到持久化存储（默认设置，也是最安全的设置） :2: 每次提交时 把日志缓冲写到日志文件，但并不刷新。InnoDB每秒刷新一次。 :三者区别：在0时MySQL挂了会丢失一些事务。在2时只有机器挂了才会有可能丢失一些事务。而1时不会丢失任何事务<br /> :注：“把日志缓冲写到日志文件” 和 “把日志刷新到持久化存储” 的不同是：前者只是简单的写到了操作系统的缓冲区，并没有把数据写到持久化存储。 :show variables like '%innodb_flush_log_at_trx_commit%'; ;高性能事务处理的最佳配置:把innodb_flush_log_at_trx_commit设置为1且把日志文件放到一个有电池保护的写缓存的RAID卷中。这兼顾了安全和速度 ====InnoDB怎样打开和刷新日志及数据文件==== '''innodb_flush_method''':配置InnoDB如何跟文件系统相互作用。它的可能值：<br /> ;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并且给共享表空间设置大小范围。 行的旧版本和表空间<br /> 有个例子： <pre> 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 </pre> 通过以上结果：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. '''双写缓冲，默认是打开的'''<br /> ;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表都使用延迟写入 :<br /> ;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 :但是在实践中，使用更小一点的值会更好一点。必须实验来找出更适合系统的更好值。 <pre> 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 </pre> ===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<br /> 一个最重要的注意事项是，服务器不能内存临时表中存储BLOB值，因此，如果一个查询涉及BLOB值，又需要临时表——不管它多小——它都会在磁盘上创建临时表。这样效率很低，尤其是对小而快的查询。临时表可能是查询中最大的开销<br /> 有两种方法可以减轻这个不得的情况：通过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.<br /> 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 <big>'''8 KB'''</big>), 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% :[http://blog.csdn.net/a351945755/article/details/23457389 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 新的名称为table_open_cache 见8.4.8节:一般不需要配置。 :对InnoDB影响比较小。
返回
Mysql 高性能笔记
。
导航菜单
个人工具
创建账户
登录
命名空间
页面
讨论
变种
视图
阅读
查看源代码
查看历史
更多
搜索
相关站点
站长博客
分类
DB
WEB
iOS
Android
深度学习
技术收集
素材收集
历史
常用网址
导航
首页
最近更改
随机页面
帮助
常用管理页面
五笔字根表
工具
链入页面
相关更改
特殊页面
页面信息