MySQL优化笔记-整理版

《MySQL优化笔记-整理版》由会员分享，可在线阅读，更多相关《MySQL优化笔记-整理版（82页珍藏版）》请在装配图网上搜索。

1、High Performance Memcached第 2版错误！未找到目录项。Q: 为什么别人问你 MySQL优化的知识 总是没有底气 .A: 因为你只是回答一些大而化之的调优原则 , 比如 : ”建立合理索引” ( 什么样的索引合理 ?) “分表分库” ( 用什么策略分表分库 ?)“主从分离” ( 用什么中间件 ?)并没有从细化到定量的层面去分析.如 qps 提高了 %N? 有没有减少文件排序?语句的扫描行数减少了多少?没有大量的数据供测试, 一般在学习环境中数据量小 , 看不出语句之间的明确区别., 只是手工添加几百上万条数据,Q: 如何提高 MySQL的性能 ?A: 需要优化 , 则说

2、明效率不够理想 .因此我们首先要做的 , 不是优化 , 而是 - 诊断 .治病的前提 , 是诊病 , 找出瓶颈所在 . CPU, 内存 ,IO? 峰值 , 单条语句 ?准备环境1、安装确保以下系统相关库文件gccgcc-c+autoconfautomakezlib*libxml*ncurses-devellibtool*(libtool-ltdl-devel*)#yum yinstallgccgcc-c+autoconfautomakezlib*libxml*libmcrypt* libtool* cmake2、建立 mysql 安装目录及数据存放目录# mkdir /usr/local/my

3、sql# mkdir -p /data/mysql3、创建用户和用户组# groupadd mysql# useradd -g mysql mysql4、赋予数据存放目录权限# chown mysql.mysql R /data/mysql二、安装1、获取解压在 mysql. 官网或国内镜像下载源码#2、编译 # cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -D

4、WITH_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/my-f /etc/f

5、4、初始化数据库执行前需赋给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/mys

6、ql# chkconfig mysql on6、为 MySQL配置环境变量libmcrypt*ncurses-devel将 mysqlmyslq/bin的 bin 目录加到 PATH 中，有利于以后管理和维护，在，同时增加两个别名方便操作：/etc/profile中加入# export PATH=/usr/local/mysql/bin:$PATH# alias mysql_start=mysqld_safe &# alias mysql_stop=mysqladminu root -p shutdown7、启动mysql服务# /etc/init.d/mysql start启动完成之后用ps

7、 -ef |grep mysql8、登陆 mysql命令查看是否启动#mysql -uroot -p提示在自行编译mysql,并连接本地机时, 常出现找不到mysqld.sock的错误 .rootlfqb data# mysql -urootERROR 2002(HY000):CantconnecttolocalMySQL serverthroughsocket/tmp/mysqld.sock (2)错误原因 : mysql客户端默认去找/tmp/mysqld.sock此处 .比如在 /var/lib/mysql/mysql.sock解决 :1: mysql -S /sock/path指定真实

8、的路径做连接, 而mysqld.sock有可能不在mysql -S /var/lib/mysql/mysql.sock2: 在 /tmp 下做一个链接 , 链接到真实 sock 文件 .# ln /var/lib/mysql/mysql.sock /tmp/mysqld.sock3: sock文件在 linux你也可以强行指定用环境中连接本地机才能使用IP 来连接 ., 速度比用本机IP要快 .Sysbench 的编译下载解压 :#cd /path/to/sysbench# ./autogen.sh注 :出现如下错误 ,则 yum 安装出现如下问题 drv_mysql.c:,则说明需要安装my

9、sql-devel, 即在函数 mysql_drv_done 中 :mysql的头文件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

10、-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.7210stota

11、l number of events:10000total time taken by event execution: 114.5518per-request statistics:min:2.17msavg:11.46msmax:51.54msapprox. 95 percentile:17.27msThreads fairness:events (avg/stddev):2500.0000/6.96execution time (avg/stddev):28.6380/0.01注意 :服务器类型 , 有偏运算型的, 有偏存储 ,所需要的指标不一样.偏运算的 ( 如视频转码服务器) 要求

12、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 ：顺序写

13、入seqrewq ：顺序重写seqrd ：顺序读取rndrd ：随机读取rndwr ：随机写入rndrw ：混合随机读写测试顺序读rootlocalhostsysbench#./bin/sysbench-test=fileio-file-test-mode=seqrd runsysbench 0.5: multi-threaded system evaluation benchmark-file-total-size=10GRunning the test with following options:Number of threads: 1Random number generator se

14、ed 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: 10

15、000 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.7770stotal number of events:10000total time taken by event execution: 0.7595sresponse time:min:0.01msavg:0.08msmax:15.58msapprox.

16、 95 percentile:0.02msThreads fairness:events (avg/stddev):10000.0000/0.00execution time (avg/stddev):0.7595/0.00测试随机读rootlocalhostsysbench#./bin/sysbench-test=fileio-file-test-mode=rndrd runsysbench 0.5: multi-threaded system evaluation benchmark-file-total-size=10GRunning the test with following op

17、tions: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 bined random IO test: 1.50Periodic FSYNC enabled, calling fsync() each 100 requests.Calling f

18、sync() 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.0530sto

19、tal number of events:10000total time taken by event execution: 28.0305sresponse time:min:0.01msavg:2.80msmax:76.69msapprox. 95 percentile:11.42msThreads fairness:events (avg/stddev):10000.0000/0.00execution time (avg/stddev):28.0305/0.00通过上两例对比,顺序读与随机读之间的速度合理的索引 +where 语句会尽量达成顺序读.作业 : 测试随机写与顺序写的速度差异

20、, 差了40 倍!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 转机械硬盘rootlocalhostsysbecn#./bin/sysbench-test=/pa

21、th/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: 1Rando

22、m number generator seed is 0 and will be ignoredThreads started!OLTP test statistics:queries performed:read:140000write:40000other:20000total:200000transactions: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

23、 statistics:total time:324.2651stotal number of events:10000total time taken by event execution: 324.2226sresponse time:min:22.56msavg:32.42msmax:453.49msapprox. 95 percentile:34.75msThreads fairness:events (avg/stddev):10000.0000/0.00execution time (avg/stddev):324.2226/0.00Awk脚本Awk是一个简便的直译式的文本处理工具

24、擅长处理 - 多行多列的数据.处理过程 :While( 还有下一行 ) 1: 读取下一行 , 并把下一行赋给2:用指定的命令来处理该行$0, 各列赋给$1,$2.$N变量如何处理1 行数据 ?答:分 2 部分 ,pattern (条件 ) + action(处理动作)第 1 个简单 awk 脚本awk printf“ %sn”, $1xx.txt /把xx.txt的每一行进行输出第 2 个简单 awk 脚本统计 mysql 服务器信息mysqladmin -uroot ext|awkQueries/q=$4/Threads_connected/c=$4/Threads_running/r=$4

25、ENDprintf(%d % d %dn,q,c,r)mysql 性能调优的思路0: 最好的优化 - 不查询 ! 这不是开玩笑 .如果一台服务器出现长时间负载过高 / 周期性负载过大 , 或偶尔卡住如何来处理 ?答: 大的思路 -是周期性的变化还是偶尔问题 ?是服务器整体性能的问题 , 还是某单条语句的问题 ?具体到单条语句 , 这条语句是在等待上花的时间 , 还是查询上花的时间 . 唯一的办法 - 监测并观察服务器的状态 .1: 观察服务器状态 , 一般用如下 2 个命令Show status; Show processlist;例: mysql show status; #mysqladm

26、in extMySQL周期性波动试验实验目的 : 模拟数据库高低峰时的压力波动 , 并会观察绘制波动曲线实验思路 : 反复查询数据库并缓存入 memcached, 缓存定期失效 , 观察记录服务器参数 , 并作图表 .实验准备 : nginx+php+memcached+awk+ab1: index.php (随机访问 3W条热数据 , 并储存在 memcached中2: memcached ( 储存查询结果 )3: ab 压力测试工具4: awk 脚本编译 PHP-memcache扩展 ( 此步骤适合任意PHP扩展 )以为例 ( 注意 , 这是 PHP连接 memcached的一个扩展 )解

27、压后 , 假设路径 /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 个并发 , 每秒请求5

28、00-1000 次请求结果缓存在memcache,生命周期为60 秒 ,( 生命周期要结合请求周期来制定, 比如 3 万条数据随机周期可设为60 秒 )观察 mysql 连接数 , 每秒请求数的周期变化., 每秒1000条 ,30秒能走一遍, 生命看上图 ,mysql 的每秒请求数, 随着缓存失效, 有短时间的高峰.解决办法 :1: 减少无关请求 ( 业务逻辑层面 , 暂不讨论 , 但其实是最有效的手段 )2: 如果请求数是一定的 , 不可减少的 . 我们要尽量让请求数平稳 , 不要有剧烈波动 .很多时候 , 不是服务器撑不住总的查询量, 而是在某个时间段撑不住高峰请求.该实际问题最后的解决:

29、- 夜间负载低时 , 集中失效 .短时间内会有波峰 , 但夜间访问量少 , 因此波峰并不剧烈 , 当到上午 10 点左右人多时 , 缓存已经建立了一部分 . 白天时 , 波峰也不剧烈 .或者让缓存的生命周期在一定范围内随机, 也可以减缓波峰剧烈的情况我们把实验中的生命周期由80 秒 , 改为 40-120秒, 其他实验条件不变.得到如下曲线可以看出 , 稳定运行后 , 请求在 1000-1500之间波动 ,而固定缓存周期是, 请求在 500-1700 之间波动 .实验附件 :bench.php status.txtsleep 1done对于不规则的延迟现象的观察不规则的延迟现象往往是由于效率低

30、下的语句造成的可以用 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

31、时储存中间结果 , 说明索引建的不好 )Copying to tmp table on disk把内存临时表复制到磁盘( 索引不好 , 表字段选的不好 )locked被其他查询锁住( 一般在使用事务时易发生, 互联网应用不常发生 )logging slow query记录慢查询mysql 5.5以后加了一个 profile设置 , 可以观察到具体语句的执行步骤.0: 查看 profile 是否开启 Show variables likeprofiling +-+-+| Variable_name | Value |+-+-+| profiling| OFF|+-+-+1: set profil

32、ing=on;+-+-+| Variable_name | Value |+-+-+| profiling| On|+-+-+mysql show profiles;+-+-+-+|Query_ID|Duration|Query|+-+-+-+|1|0.00034225 | selectcat_id,avg(shop_price)from goods groupby cat_id|+-+-+-+1 row in set (0.00 sec)mysql show profile for query 1;+-+-+| Status| Duration |+-+-+| starting| 0.000

33、058 | 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如何使

34、用内部临时表官方 :http:/dev.mysql./doc/refman/5.5/en/internal-temporary-tables.html在处理请求的某些场景中 , 服务器创建内部临时表 . 即表以 MEMORY引擎在内存中处理 , 或以 MyISAM 引擎储存在磁盘上处理 . 如果表过大 , 服务器可能会把内存中的临时表转存在磁盘上.用户不能直接控制服务器内部用内存还是磁盘存储临时表临时表在如下几种情况被创建:如果 group by的列没有索引, 必产生内部临时表,如果 order by与 group by为不同列时 , 或多表联查时order by ,group by包含的列不

35、是第一张表的列 , 将会产生临时表( 实验 1)mysql explain select goods_id,cat_id from goods group by cat_id G* 1. row *id: 1select_type: SIMPLEtable: goodstype: ALLpossible_keys: NULLkey: NULLkey_len: NULLref: NULLrows: 20Extra: Using temporary; Using filesort1 row in set (0.00 sec)mysql alter table goods add index cat

36、_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: 1select_type: SIMPLEtable: goodstype: indexpossible_keys: NULLkey: cat_idkey_len: 2ref: NULLrows: 20Extra: Using indexmysql explain sele

37、ct goods_id,cat_id from goods group by cat_id order by 1 G* 1. row *id: 1select_type: SIMPLEtable: goodstype: indexpossible_keys: NULLkey: PRIMARYkey_len: 3ref: NULLrows: 20Extra: Using temporarydistinct与 order by一起使用可能会产生临时表( 实验2)mysql explain select distinct cat_id from goods order by 1 G * 1. row

38、 *id: 1select_type: SIMPLEtable: goodstype: indexpossible_keys: NULLkey: cat_idkey_len: 2ref: NULLrows: 20Extra: Using index1 row in set (0.00 sec)mysql explain select distinct cat_id from goods order by goods_id G * 1. row *id: 1select_type: SIMPLEtable: goodstype: indexpossible_keys: NULLkey: PRIM

39、ARYkey_len: 3ref: NULLrows: 20Extra: Using temporary1 row in set (0.00 sec)mysql explain select distinct cat_id from goods order by click_countG * 1. row *id: 1select_type: SIMPLEtable: goodstype: indexpossible_keys: NULLkey: cat_idkey_len: 2ref: NULLrows: 20Extra: Using temporary; Using filesort1 r

40、ow in set (0.00 sec)如果使用 SQL_SMALL_RESULT,MySQL会使用内存临时表 , 除非查询中有一些必须要把临时表建立在磁盘上 .union 合并查询时会用到临时表某些视图会用到临时表, 如使用 temptable方式建立 , 或使用 union 或聚合查询的视图想确定查询是否需要临时表 , 可以用 EXPLAIN 查询计划 , 并查看 Extra 列 , 看是否有 Using temporary.如果一开始在内存中产生的临时表变大, 会自动转化为磁盘临时表.内存中临时表的最大值为 tmp_table_size 和 max_heap_size 中较小值 .这和

41、createtable时显示指定的内存表不一样: 这些表只受max_heap_table_size系统参数影响 .当服务器创建内部临时表( 无论在内存还是在磁盘),create_tmp_tables如果创建了在磁盘上内部临时表( 无论是初始创建还是由in-memorycreate_tmp_disk_tables变量都会增加 .变量都会增加转化 ),.一些情况下限制了内存临时表的使用, 而使用磁盘临时表:( 使用了内部临时表的前提下)语句中存在BLOB或 TEXT列 ( 实验在 GROUP BY或 DISTINCT 子句中有大于512 字节的 string列在 UNION或 UNION ALL

42、时 ,SELECT语句里有大于512 字节的 string3)列 .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

43、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)通过前面实验 , 可以看

44、出 , 数据库的优化是一个系统工程:建表 :表结构的拆分 , 如核心字段都用 int,char,enum 等定长结构非核心字段 , 或用到 text, 超长的 varchar, 拆出来单放一张表 .建索引 :合理的索引可以减少内部临时表( 索引优化策略里详解)写语句 :不合理的语句将导致大量数据传输以及内部临时表的使用.表的优化与列类型选择表的优化 :1: 定长与变长分离如 id int,占 4 个字节 , char(4)占 4 个字符长度即每一单元值占的字节是固定的.核心且常用字段, 宜建成定长 , 放在一张表 ., 也是定长, time而varchar, text,blob,这种变长字段,

45、 适合单放一张表,用主键与核心表关联起来.2: 常用字段和不常用字段要分离.需要结合网站具体的业务来分析, 分析字段的查询场景, 查询频度低的字段, 单拆出来 .3: 合理添加冗余字段 . 看如下 BBS的效果每个版块里 , 有 N 条帖子 ,在首页 , 显示了版块信息, 和版块下的帖子数.这是如何做的 ?boardidBoardnamepostnum8开班信息29每日视频及代码1postidBoardedtitle1238论坛开张了1298灌水1339来一帖如果再查board 表只有前2 列 , 则需要取出版块后,post 表 ,select count(*) from post group by board_id,得出每