MySQL优化笔记-整理版.doc

第2版High Performance Memcached 第 83 页自学it网 高端PHP培训 http:/www.zixue.it错误！未找到目录项。Q: 为什么别人问你MySQL优化的知识 总是没有底气.A: 因为你只是回答一些大而化之的调优原则, 比如:”建立合理索引”(什么样的索引合理?)“分表分库”(用什么策略分表分库?)“主从分离”(用什么中间件?)并没有从细化到定量的层面去分析.如qps提高了%N? 有没有减少文件排序?语句的扫描行数减少了多少?没有大量的数据供测试,一般在学习环境中,只是手工添加几百上万条数据,数据量小,看不出语句之间的明确区别.Q: 如何提高MySQL的性能?A: 需要优化,则说明效率不够理想. 因此我们首先要做的,不是优化,而是-诊断. 治病的前提,是诊病,找出瓶颈所在. CPU,内存,IO? 峰值,单条语句? 准备环境1、安装确保以下系统相关库文件gcc gcc-c+ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool*(libtool-ltdl-devel*)# yum y install gcc gcc-c+ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool* cmake2、 建立mysql安装目录及数据存放目录# mkdir /usr/local/mysql# mkdir -p /data/mysql3、 创建用户和用户组# groupadd mysql# useradd -g mysql mysql4、 赋予数据存放目录权限# chown mysql.mysql R /data/mysql二、安装MySQL 5.5.351、 获取解压mysql-5.5.35.tar.gz在官网或国内镜像下载源码# wget /mysql/MySQL-5.5/mysql-5.5.35.tar.gz# tar zxvf mysql-5.5.35.tar.gz# cd mysql-5.5.352、 编译mysql-5.5.35# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk -DWITH_MYISAM_STORAGE_ENGINE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_MEMORY_STORAGE_ENGINE=1 -DWITH_READLINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql -DMYSQL_USER=mysql -DMYSQL_TCP_PORT=3306# make# make install3、 复制配置文件# cp support-files/f /etc/f4、 初始化数据库执行前需赋给scripts/mysql_install_db文件执行权限# chmod 755 scripts/mysql_install_db# scripts/mysql_install_db -user=mysql -basedir=/usr/local/mysql/ -datadir=/data/mysql/注：basedir：mysql安装路径 datadir：数据库文件储存路径5、 设置mysqld的开机启动# cp support-files/mysql.server /etc/init.d/mysql# chmod 755 /etc/init.d/mysql# chkconfig mysql on6、 为MySQL配置环境变量将mysql的bin目录加到PATH中，有利于以后管理和维护，在/etc/profile中加入myslq/bin，同时增加两个别名方便操作：# export PATH=/usr/local/mysql/bin:$PATH# alias mysql_start=mysqld_safe &# alias mysql_stop=mysqladmin u root -p shutdown7、 启动mysql服务# /etc/init.d/mysql start启动完成之后用ps -ef |grep mysql 命令查看是否启动8、 登陆mysql#mysql -uroot -p提示 在自行编译mysql,并连接本地机时,常出现找不到mysqld.sock的错误.rootlfqb data# mysql -urootERROR 2002 (HY000): Cant connect to local MySQL server through socket /tmp/mysqld.sock (2)错误原因: mysql客户端默认去找 /tmp/mysqld.sock 做连接,而mysqld.sock有可能不在此处.比如在 /var/lib/mysql/mysql.sock解决: 1: mysql -S /sock/path 指定真实的路径 mysql -S /var/lib/mysql/mysql.sock2: 在/tmp下做一个链接,链接到真实sock文件.# ln /var/lib/mysql/mysql.sock /tmp/mysqld.sock3: sock文件在linux环境中连接本地机才能使用,速度比用本机IP要快. 你也可以强行指定用IP来连接.Sysbench的编译下载:/sysbench-developers/sysbench/0.5/files解压: #cd /path/to/sysbench# ./autogen.sh注:出现如下错误,则yum安装automake,libtoolizeautomake 1.10.x (aclocal) wasnt found, exitinglibtoolize 1.4+ wasnt found, exiting出现如下问题,则说明需要安装mysql-devel,即mysql的头文件drv_mysql.c: 在函数mysql_drv_done中:drv_mysql.c:851: 警告：隐式声明函数mysql_library_end#./configure -prefix=/usr/local/sysbench -with-mysql-includes=/usr/include/mysql/ -with-mysql-libs=/usr/lib64/mysql/#make & make installSysbench测试CPU性能4线程查找20000以内的素数rootlocalhost # /usr/bin/sysbench -test=cpu -cpu-max-prime=20000 -num-threads=4 runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 4Doing CPU performance benchmarkThreads started!Done.Maximum prime number checked in CPU test: 20000Test execution summary: total time: 96.7210s total number of events: 10000 total time taken by event execution: 114.5518 per-request statistics: min: 2.17ms avg: 11.46ms max: 51.54ms approx. 95 percentile: 17.27msThreads fairness: events (avg/stddev): 2500.0000/6.96 execution time (avg/stddev): 28.6380/0.01注意: 服务器类型,有偏运算型的,有偏存储, 所需要的指标不一样.偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备.测试的数据,孤立起来看,是没有意义的.数据要有比较才有意义, 比如多台服务器的测试数据,比较CPU性能.Sysbench测试磁盘IO性能Sysbench -test=fileio -file-total-size=10G prepare解释: 创建10G的内容,供测试用Sysbench -test=fileio -file-total-size=10G -file-test-mode=rndrw run解释:针对10G文件,做随机读写,测试IO-file-test-mode 还可以为seqwr：顺序写入seqrewq：顺序重写seqrd：顺序读取rndrd：随机读取rndwr：随机写入rndrw：混合随机读写测试顺序读rootlocalhost sysbench# ./bin/sysbench -test=fileio -file-total-size=10G -file-test-mode=seqrd run sysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredExtra file open flags: 0128 files, 80Mb each10Gb total file sizeBlock size 16KbPeriodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing sequential read testThreads started!Operations performed: 10000 reads, 0 writes, 0 Other = 10000 TotalRead 156.25Mb Written 0b Total transferred 156.25Mb (201.09Mb/sec)12869.92 Requests/sec executedGeneral statistics: total time: 0.7770s total number of events: 10000 total time taken by event execution: 0.7595s response time: min: 0.01ms avg: 0.08ms max: 15.58ms approx. 95 percentile: 0.02msThreads fairness: events (avg/stddev): 10000.0000/0.00execution time (avg/stddev): 0.7595/0.00测试随机读rootlocalhost sysbench# ./bin/sysbench -test=fileio -file-total-size=10G -file-test-mode=rndrd run sysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredExtra file open flags: 0128 files, 80Mb each10Gb total file sizeBlock size 16KbNumber of IO requests: 10000Read/Write ratio for combined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling fsync() at the end of test, Enabled.Using synchronous I/O modeDoing random read testThreads started!Operations performed: 10000 reads, 0 writes, 0 Other = 10000 TotalRead 156.25Mb Written 0b Total transferred 156.25Mb (5.5698Mb/sec) 356.47 Requests/sec executedGeneral statistics: total time: 28.0530s total number of events: 10000 total time taken by event execution: 28.0305s response time: min: 0.01ms avg: 2.80ms max: 76.69ms approx. 95 percentile: 11.42msThreads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 28.0305/0.00通过上两例对比, 顺序读与随机读之间的速度,差了40倍!合理的索引+where语句会尽量达成顺序读.作业:测试随机写与顺序写的速度差异Sysbench测试事务性能 #sysbench -test=/path/to/sysbench-source/tests/db/oltp.lua -mysql-table-engine=innodb -mysql-user=root -db-driver=mysql -mysql-db=test -oltp-table-size=3000 -mysql-socket=/var/lib/mysql/mysql.sock prepare实测结果: 双核CPU,8G内存,7200转机械硬盘rootlocalhost sysbecn# ./bin/sysbench -test=/path/to/sysbench-source/tests/db/oltp.lua -mysql-table-engine=innodb -mysql-user=root -db-driver=mysql -mysql-db=test -oltp-table-size=3000 -mysql-socket=/var/lib/mysql/mysql.sock runsysbench 0.5: multi-threaded system evaluation benchmarkRunning the test with following options:Number of threads: 1Random number generator seed is 0 and will be ignoredThreads started!OLTP test statistics: queries performed: read: 140000 write: 40000 other: 20000 total: 200000 transactions: 10000 (30.84 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 180000 (555.10 per sec.) other operations: 20000 (61.68 per sec.)General statistics: total time: 324.2651s total number of events: 10000 total time taken by event execution: 324.2226s response time: min: 22.56ms avg: 32.42ms max: 453.49ms approx. 95 percentile: 34.75msThreads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 324.2226/0.00Awk脚本Awk是一个简便的直译式的文本处理工具.擅长处理-多行多列的数据处理过程: While(还有下一行) 1:读取下一行,并把下一行赋给$0,各列赋给$1,$2.$N变量 2: 用指定的命令来处理该行如何处理1行数据?答: 分2部分, pattern (条件) + action(处理动作)第1个简单awk脚本awk printf “%sn” , $1 xx.txt / 把xx.txt的每一行进行输出第2个简单awk脚本 统计mysql服务器信息mysqladmin -uroot ext|awk Queries/q=$4/Threads_connected/c=$4/Threads_running/r=$4ENDprintf(%d %d %dn,q,c,r)mysql性能调优的思路0: 最好的优化-不查询!这不是开玩笑.如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住如何来处理?答:大的思路-是周期性的变化还是偶尔问题?是服务器整体性能的问题, 还是某单条语句的问题?具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.唯一的办法-监测并观察服务器的状态.1:观察服务器状态, 一般用如下2个命令Show status; Show processlist;例: mysql show status; #mysqladmin ext MySQL周期性波动试验实验目的: 模拟数据库高低峰时的压力波动,并会观察绘制波动曲线实验思路: 反复查询数据库并缓存入memcached, 缓存定期失效,观察记录服务器参数,并作图表.实验准备: nginx+php+memcached+awk+ab1: index.php (随机访问3W条热数据,并储存在memcached中2: memcached (储存查询结果)3: ab 压力测试工具4: awk脚本编译PHP-memcache扩展 (此步骤适合任意PHP扩展)以memcache-2.2.7为例(注意,这是PHP连接memcached的一个扩展)解压后,假设路径/path/to/memcachestep1 : /path/to/memcached/# /path/to/php/bin/phpize #作用是根据PHP的版本生成编译文件此步骤后,memcache目录下,产生configure文件step2: configure -with-php-config =/path/to/php/bin/php-configstep3: make & make install此步骤编译出一个memcache.so文件step4: 修改php.ini引入memcache.so实验步骤: 总数据3W以上,50个并发,每秒请求500-1000次请求结果缓存在memcache,生命周期为60秒,(生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒)观察mysql连接数,每秒请求数的周期变化.看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.解决办法: 1: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.该实际问题最后的解决:-夜间负载低时,集中失效. 短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分. 白天时,波峰也不剧烈. 或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况我们把实验中的生命周期由80秒,改为40-120秒,其他实验条件不变.得到如下曲线可以看出,稳定运行后,请求在1000-1500之间波动,而固定缓存周期是,请求在500-1700之间波动.实验附件:bench.php status.txtsleep 1done对于不规则的延迟现象的观察不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.可以用show processlist命令长期观察,或用慢查询.Show processlist;这个命令是显示当前所有连接的工作状态.#!/bin/bashwhile truedomysql -uroot -e show processlistG|grep State:|uniq -c|sort -rnecho -sleep 1Done如果观察到以下状态,则需要注意converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)create tmp table 创建临时表(如group时储存中间结果,说明索引建的不好)Copying to tmp table on disk 把内存临时表复制到磁盘 (索引不好,表字段选的不好)locked 被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生) logging slow query 记录慢查询 mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.0:查看profile是否开启 Show variables like profiling +-+-+| Variable_name | Value |+-+-+| profiling | OFF |+-+-+1: set profiling=on; +-+-+| Variable_name | Value |+-+-+| profiling | On |+-+-+mysql show profiles;+-+-+-+| Query_ID | Duration | Query |+-+-+-+| 1 | 0.00034225 | select cat_id,avg(shop_price) from goods group by cat_id |+-+-+-+1 row in set (0.00 sec)mysql show profile for query 1;+-+-+| Status | Duration |+-+-+| starting | 0.000058 | checking permissions | 0.000008 |.| Sorting result | 0.000004 | Sending data | 0.000120 | end | 0.000005 | query end | 0.000006 | closing tables | 0.000008 | freeing items | 0.000023 | logging slow query | 0.000003 | cleaning up | 0.000004 |+-+-+疑问; 如何定位到有问题的语句?答: 1: 开启服务器慢查询2: 了解临时表的使用规则 3: 经验MySQL如何使用内部临时表官方:/doc/refman/5.5/en/internal-temporary-tables.html在处理请求的某些场景中,服务器创建内部临时表. 即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.用户不能直接控制服务器内部用内存还是磁盘存储临时表临时表在如下几种情况被创建:如果group by 的列没有索引,必产生内部临时表,如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表(实验1)mysql explain select goods_id,cat_id from goods group by cat_id G* 1. row * id: 1 select_type: SIMPLE table: goods type: ALLpossible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 20 Extra: Using temporary; Using filesort1 row in set (0.00 sec)mysql alter table goods add index cat_id(cat_id);Query OK, 0 rows affected (0.18 sec)Records: 0 Duplicates: 0 Warnings: 0mysql explain select goods_id,cat_id from goods group by cat_id G* 1. row * id: 1 select_type: SIMPLE table: goods type: indexpossible_keys: NULL key: cat_id key_len: 2 ref: NULL rows: 20 Extra: Using indexmysql explain select goods_id,cat_id from goods group by cat_id order by 1 G* 1. row * id: 1 select_type: SIMPLE table: goods type: indexpossible_keys: NULL key: PRIMARY key_len: 3 ref: NULL rows: 20 Extra: Using temporarydistinct 与order by 一起使用可能会产生临时表(实验2)mysql explain select distinct cat_id from goods order by 1 G* 1. row * id: 1 select_type: SIMPLE table: goods type: indexpossible_keys: NULL key: cat_id key_len: 2 ref: NULL rows: 20 Extra: Using index1 row in set (0.00 sec)mysql explain select distinct cat_id from goods order by goods_id G* 1. row * id: 1 select_type: SIMPLE table: goods type: indexpossible_keys: NULL key: PRIMARY key_len: 3 ref: NULL rows: 20 Extra: Using temporary1 row in set (0.00 sec) mysql explain select distinct cat_id from goods order by click_countG* 1. row * id: 1 select_type: SIMPLE table: goods type: indexpossible_keys: NULL key: cat_id key_len: 2 ref: NULL rows: 20 Extra: Using temporary; Using filesort1 row in set (0.00 sec)如果使用SQL_SMALL_RESULT,MySQL会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.union合并查询时会用到临时表某些视图会用到临时表,如使用temptable方式建立,或使用union或聚合查询的视图想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using temporary.如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值.这和create table时显示指定的内存表不一样:这些表只受max_heap_table_size系统参数影响.当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables变量都会增加.如果创建了在磁盘上内部临时表(无论是初始创建还是由in-memory转化),create_tmp_disk_tables 变量都会增加.一些情况下限制了内存临时表的使用,而使用磁盘临时表:(使用了内部临时表的前提下) 语句中存在BLOB或TEXT列(实验3)在GROUP BY 或 DISTINCT子句中有大于512字节的string列在UNION或UNION ALL时,SELECT语句里有大于512字节的string列.mysql create table t1 (num int,intro text(1000);mysqlinsert into t1 values (3,this is USA , 4,China);mysql show status like %tmp%;+-+-+| Variable_name | Value |+-+-+| Created_tmp_disk_tables | 5 | Created_tmp_files | 9 | Created_tmp_tables | 74 |+-+-+3 rows in set (0.00 sec)mysql select * from t1 group by num;+-+-+| num | intro |+-+-+| 3 | this is USA | 4 | China |+-+-+2 rows in set (0.00 sec)mysql show status like %tmp%;+-+-+| Variable_name | Value |+-+-+| Created_tmp_disk_tables | 6 | Created_tmp_files | 9 | Created_tmp_tables | 75 |+-+-+3 rows in set (0.00 sec)通过前面实验,可以看出,数据库的优化是一个系统工程:建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构 非核心字段,或用到text,超长的varchar,拆出来单