mySQL数据库性能优化pdf.pdf

所需积分/C币:50 2019-07-17 12:35:45 3.33MB PDF
收藏 收藏
举报

针对面试经常问的sql优化问题,进行mySQL数据库性能优化
b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 第一章 MySQL数据库优化 1.1.准备工作 慢查询日志 当查询超过一定的时间没有返回结果的时侯,才会记录到慢查询日志中。默认不开启。 釆样的时候手工开启。可以帮助我们找出执行慢的SQL语句 查看慢SQL日志是否启用(on表示启用) show variables like 'slow query log 查看执行慢于多少秒的SQL会记录到日志文件中 show variables like long query time 可以使用模糊搜索,查看所有含有 query的变量信息 show variables like %query% 1 show variables like 'tquerys'i 信息结果1概况状态 Variable name Value binlog_rows_query _log_events OFF ft query expansion limit have query cache YES long query time 10.000000 query alloc block size a192 query cache limit 1048576 query cache min res unit 4096 query cache size 0 query cache type OFF query cache lock invalidate CFF query realloc_sIze 8192 slow query log ON slow query log file DESKTOP-XFSQRD-slowlog b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 1.1.2.修改mysq配置参数 my.InI( Linux下文件名为 my cnf),查找到 mysqld区段,增加日志的配置。 Window下路径一般为C∴ ProgramData\MySQL MySQL Server5.7my.ni",可以在 启动参数中查看使用的是那个配置文件。 服务(本地 MySQL57 状态 hmeR) Rapid Storage Te:hold MysQLS7的届性本计机 ChInleltR) WiDi Suflide Asbel 暂使此服务 ChIntelUSBoverP 常规登录恢复依存关系 x正在运行 启动此 C, Internet Connection Sharrg(Id 手动 服务名称 MySQL57 护P转换配置服务 MySQL57 手动 IPSec Policy Agent 金证正在运行 手动 c, KtmRm for Distributed Transacti描述: 手动 ch Link-Layer Topology Discovery c Local Session Manager t不正在运行 C Messag reService 99553 可执行文件 Msd)诊断中心收 efaults-file"C\ProgramDatalMy SQL\MySQL Server7 my in"MySQL57 手动泪 C Microsoft Account Sign in/ci启动受型(D 。正在运行手动 o Microsoft SCSI Initiator C Microsoft Office ClickTcFun Sei 使.正在远行 ch Microsoft Passport 动 Microsoft Passport Cortainer 服务状卷 动 Microsoft software Shadow ChMicrosaft Storage Spaces SMP 停让m 暂停(P 手动 c Microsoft Windows SMS路击器 当从此处启动报务时,你可指定听适用的动参数 手动 正在运行 Net Tcp Port shanng Service启多数 o,Netlogon c Network Corrected Devices At 亭止正在运行 CANetwor'k Connection Broke 取消 正在运行 手动 c Network Connections 王。正在运行 手动 C Network Connectivity Assistant 提供U组件的 CirectAccess状态通知 G:Network Lst Service 识別计算机已连接的网络,收集和储这些网络的属性,并在更改这些属性正在运行手动 常用的参数详解 是合开启慢查询日志 slow query log= -指定保存路径及文件名,默认为数据文件目录 slow query log file=bxg mysql slow.log #-指定多少秒返回查询的结果为慢查询 long query time-1 #--记录所有没有使用到索引的查询语句 log queries not using indexes=1 #--记录那些由于查找了多于1000次而引发的慢查询 nin examined row limit=1000 #-记汞那些慢的 optimize table, analyze tab e和 alter table语句 log slow admin statements- #--记录由Save所产生的慢查询 log slow slave statements=1 b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 datadir= C: /Program Data/ MySQL/ MySQL Server5.7Data-数据文件日录 注意:修改以下参数,需要重新启动数据库服务才会生效。 1.1.3.命令行修改慢查询配置 命令行修改配置方式不需要不重启目可生效,但重启之后会自动失效。 set clobal slow query log=1; set global slow query log file=bxg mysql slow. log set long query time=1; set global log queries not using indexes=1; set clobal min examined row limit= 1000 set global log slow admin statements-1 set clobal log slow slave state ments=1 其他参数可道过以下命令查阅: show variables like query 9 show variables like %slow 1.1.4.慢日志格式 时间、主机信息、执行信息、执行时间、执行内容 73-n17:4:76.77 096832 Luck Lim=: 0, 000Cc0 Rwy s-r:0 RowN exdnLin-d: o INSBII INTO lianjia oity, title, proc pcrpricc, uapaisr1jian, jiaoyiuanehu, shang=ijiaoy: yo fangbenDeijian, tangpxciebi (2,房于小区林,位置方便,户型进,310万,3725元/平米,自付及贷悟况语容询经纪人,0年建板,南国奥林匹克尼园,雷 40125428,1厨1卫',中楼层(共4 客户偿还,传房两厂,元7年 32-21’,商品房',2013-04-09’,普通住主!,满五年’,'共有',"有低押7万 1.1.5.查询缓存 Query Cache会缓存 select查询,安装时默认是开启的,但是如果对表进行 INSERT UPDATE DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, Or DROP DATABASE SR1E 时,之前的缓存会无效并且删除。这样一定程度上也会影响我门数据库的性能。所以对一 些频繁的变动表的情况开启缓存是不明智的。还有一种情况我们测试数据库性能的时候也 要关闭缓存,避免缓存对我们测试数据的影响 show Variables like %cache% b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 Variable name Value innodb disable sort file cache OFF innodb ft cache size 8000000 innodb ft result cache limit 2000000000 innodb ft total cache size 640000000 key cache age threshold 300 key cache block size 1024 key cache division limit max bin og cache size 18446744073709547520 max bin og stmt cache sizc 18446744073709547520 metadata locks cache size 1024 query cache limit 1048576 query cache min res unit 4096 query cache size 0 >query_cache_type OFF query cache lock invalida OFF stored program cache 256 table definition cache 1400 table e open cache 2000 table open cache instances 16 thread cache size 10 查看缓存命中情况 select count()FROM test; select count( )FROM test; show status like cache% 信息结果1结果2结果3概况状态 Variable name value Cache free blocks Cache free memory 31438688 >Cache hits Ocache inserts Cache lowmem prunes Ocache not cached 18 Ocache queries in cache Cache total blocks 关闭缓存有两种放法,一种临时的,一种永久的。临时的直接在命令行执行 set global query_ cache size=0 b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 set global query cache type=0;-如果配置文件中为关闭缓存的话,不能通过命令开启缓存 永久的修改配置文件 my cnf添加下面的配置即可。 query cache type=o query cache size=0 另外,我们还可以通过 sql no cache关键字在sq语句中直接禁用缓存,在开启缓存 的情况下我们对sq语句做一些改动 Select sql no cache count(*) from pythonlearn lianjia;--不缓存 Select sql cache count(*) from pythonlearn lianjia;-缓存(也可以不加,默认缓存已经开启∫) 1.1.6.准备测试数据 创建测试表 用户表 CREATE TABLE Person( id bigint(20) unsigned NOT NULL, fname varchar(100) NOT NULL Iname varchar(100) NOT NULL age tiny nt(3) unsigned NOT NULL, sex tinyint(1) unsigned NOT NULL, PRIMARY KEY (id) ) ENGINE-INNODB DEFAULT CHARSET=utf8 -用户部门表 CREATE TABLE cepartment d bigint(20)unsigned NOT NULL, department varchar(100) NOT NULL, PRIMARY KEY (id )ENGINE-INNODB DEFAULT CHARSE T-utf8 用户住址表 CREATE TABLE address( id bigint(20)unsigned NOT NULL, address varchar(100)NOT NULL PRIMARY KEY Cid) ENGINE=INNODB DEFAULT CHARSET=utf8 创建存储过程,用于批量添加测试数据 delimiter Ss drop procedure if exists generate CREATE DEFINER-root' @localhost PRoCedURE generate(IN num INT) BEGIN b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 DECLARE Chars VARCHAR(100) DEFAULT abcdefghijkImnopgrstuvwXy7ABCDEFGH JKLMNOPQRSTUVWXY70123456789 DECLARE trame VARCHAR(O) DEFAULT DECLARE Iname VARCHAR(25) DEFAULT DECLARE id int UNSIGNED DECLARE len int t id=1 DELETE from person WHILE id <= num DO set len=FLOOR(1+ RANDo10 set tname WH|LE|en>CD○ SET tname-CONCAI(fname, Substring(chars, FLOOR(1+ RAND(*62),1) sEt len = len END WHILE set len= FLOOR(1+RANDo*25) set Iname WHILE en >0 Do SET Iname- CONCAT(rame, SUBSTR(Chars, FLCOR(1 RANDO*62),1) SET len= len-1 END WHILE NSERT into person VALUES (id, fname, Iname, FLOOR(RANDO* 100), FLOOR(RANDO)) set id=id +1 END WHILE END SS delimiter Ss drop procedure if exists genDepAdd CREATE DEFINER=root localhost PROCEDURE genDep Add(IN num INT) BEGIN DECLARE Chars VARCH∧R(100)DEF∧ULT'行政技术研发财务人事开发公关推广营销咨询客服 堂测试 DECLARE Chars2∨ ARCHAR(10) DEFAULT'北京上海青岛重庆成都安徽福建浙江杭州深圳温 州闪蒙古天津河北西安三期 DECLARE depart VARCHAR(TO) DEFAULT DECLARE addreSs VARCHAR(25) DEFAULT DECLARE id int UNSIGNeD DECLARE len int set id=1 WHILE id < num Do set len- FLOor(2 +RANDo*2). set depart WH|LEen>CD○ b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 SET depart= CONCAT(depart, substring(chars, FLOOR(1+ RANDO*26),1) SET Jen=len-1 END WHILE set depart=CONCAT(depart, aB) et len= FLOOR(6+RANDO*18) et address WHILE en>0 Do SET address=CONCAT(address, SUBSTR(Chars2, FLOOR(1 + RANDO 33),1) SET len= len-1 END WHILE NSERT into cepartment VALUES (id, depart INSERT into address VALUES(id, address et id = id +1 END WHILE END $S 为了提高速度,可以暂停事务。测试添加100万随机数据,大概6005左右时间。 停掉事务 set autocom=0 调用存储过程 call generate(1000000) call genDepAdd(1000000 重启事务 et autocom=1. sQu]-掉事务 set autocommit =0. 受影响的行:0 时间:0.001s ISQL 调用存储过程 call generate(1000000) 受影响的行:1 时间:601388s ISQL 重启事务 set autocommit 1. 受影啊的行:0 时间:35665 对比 Mylar:当创建表时选择 Mylsam格式,插入数据会很慢,仅仅3000条数据就 需要2分钟的时间,由此可见 Mylsam和 nodE的差距还是很大的。另外在执行过程中 b博学谷 传智播客旗下 高端IT在线教育平台 改变中国忏教育,我们正在行动 可以发现 Mylsam插入的数据可以在表中实时看到,而 nodE做了事务最终一次提交 所以数据不能实时看到,只有存储过程全部执行完成后才可以看到数据 1 调用存储过程 2 call generate(3000): 信息概况状态 ISQu]-调用存储过程 call generate (3000) 受影响的行;1 时:134.058s 1.2.常用工具 2.1.分析工具 1. 2. 1. Mysqldumpslow mysgldumpslow是mysq自带的用来分析慢查询的工具,基于per开发。 indows不需要下载安装pe编译器,下载地址 http://pan.baiducom/s/13glkap :https://www.cnblogs.com/mosstanjun/p/8025504.htm C:\ Program Files\ MySQL\MySQL Server 5.6\bin> perl mysq dumpslowpl --help

...展开详情
试读 87P mySQL数据库性能优化pdf.pdf
立即下载 低至0.43元/次 身份认证VIP会员低至7折
    抢沙发
    一个资源只可评论一次,评论内容不能少于5个字
    img
    BuG_D_Bug

    关注 私信 TA的资源

    上传资源赚积分,得勋章
    最新推荐
    mySQL数据库性能优化pdf.pdf 50积分/C币 立即下载
    1/87
    mySQL数据库性能优化pdf.pdf第1页
    mySQL数据库性能优化pdf.pdf第2页
    mySQL数据库性能优化pdf.pdf第3页
    mySQL数据库性能优化pdf.pdf第4页
    mySQL数据库性能优化pdf.pdf第5页
    mySQL数据库性能优化pdf.pdf第6页
    mySQL数据库性能优化pdf.pdf第7页
    mySQL数据库性能优化pdf.pdf第8页
    mySQL数据库性能优化pdf.pdf第9页
    mySQL数据库性能优化pdf.pdf第10页
    mySQL数据库性能优化pdf.pdf第11页
    mySQL数据库性能优化pdf.pdf第12页
    mySQL数据库性能优化pdf.pdf第13页
    mySQL数据库性能优化pdf.pdf第14页
    mySQL数据库性能优化pdf.pdf第15页
    mySQL数据库性能优化pdf.pdf第16页
    mySQL数据库性能优化pdf.pdf第17页
    mySQL数据库性能优化pdf.pdf第18页
    mySQL数据库性能优化pdf.pdf第19页
    mySQL数据库性能优化pdf.pdf第20页

    试读已结束,剩余67页未读...

    50积分/C币 立即下载 >