查看“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的时候可以使用视图,使得在修改底层表结构的时候,应用代码还可以不报错的运行 *可以使用视图实现基于列的权限控制 获取视图定义的一个窍门: <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; SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( REPLACE(REPLACE(REPLACE(REPLACE(REPLACE( SUBSTRING_INDEX(LOAD_FILE('/usr/local/mysql/var/sakila/nicer_but_slower_film_list.frm'), '\nview_body_utf8=', -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 高性能笔记
。
导航菜单
个人工具
创建账户
登录
命名空间
页面
讨论
变种
视图
阅读
查看源代码
查看历史
更多
搜索
相关站点
站长博客
分类
DB
WEB
iOS
Android
深度学习
技术收集
素材收集
历史
常用网址
导航
首页
最近更改
随机页面
帮助
常用管理页面
五笔字根表
工具
链入页面
相关更改
特殊页面
页面信息