MySQL系列优化(一)

MYSQL优化是一个非常大的课题,这篇文章主要介绍了跟MYSQL相关的4个方面,如果想深入研究可以查下相关资料。


一、服务器级别优化

二、操作系统级别优化

三、MYSQL级别优化

四、SQL级别优化


一、服务器级别优化

1. 服务器选型

SUN小型机、DELL730xd、HPDL380、IBM3850、云服务等

2. CPU个数、内存大小

大内存,高IO,是现代基于web的数据库的必备

3. 磁盘:SAS、SSD、FIO卡

减小寻道时间、旋转时间、传输时间

4. RAID卡电池,RAID级别

WriteBack, ReadAheadNone,Direct,NoWrite Cache if Bad BBU

5. 其他:网卡等

二、操作系统级别优化

1. I/O调度策略

NOOP、CFQ、Deadline、Anticipatory

临时生效:echo “dadline” >/sys/block/sda/queue/scheduler

永久生效:/etc/grub.conf中kernel后加elevator=deadline(需要重启)

2. SWAP使用策略

echo"vm.swappiness=10">>/etc/sysctl.conf

https://www.percona.com/blog/2014/04/28/oom-relation-vm-swappiness0-new-kernel/

3. 文件系统

ext3、ext4还是使用xfs

4. 避免NUMA问题

numactl --interleave=all即是允许所有的处理器可以交叉访问所有的内存

5. /tmp分区

tmpfs /dev/shm tmpfs defaults 00

设置tmpdir=/tmp之后,某些习惯性把文件写到tmp下的人要改一改习惯了,因为这些文件占用的是内存不是磁盘,而且如果不重启的话是一直占用

6. CPU

关闭服务器的节能模式

查看kondemand进程运行情况:

ps -ef |grepkondemand

三、MYSQL级别优化

1. 版本的选择,除官方版本外

2. 最重要的参数选项调整

    default-storage-engine=innodb
    innodb_buffer_pool_size、key_buffer_size
    innodb_flush_log_at_trx_commit、sync_binlog
    innodb_file_per_table
    long_query_time
    max_connection

3. Schema设计规范及SQL使用

设计自增列做主键

字段属性尽量都加上NOT NULL约束

尽可能不使用TEXT/BLOB类型

多表联接查询时,结果集小的作为驱动表

复合索引的选择

4. 其他建议(pt-toolkit、orzdba等工具使用)

pt-duplicate-key-checker 检查并删除重复的索引 pt-index-usage 检查并删除使用频率很低的索引 pt-query-digest 进行慢查询分析 pt-kill 杀掉超长时间的SQL请求 pt-online-schema-change 来完成大表的ONLINE DDL需求 pt-table-checksum、pt-table-sync 来检查并修复mysql主从复制的数据差异

四、Sql级别优化

案例一:URL列索引优化

T_VIDEO表的SQL操作缓慢,出现性能问题,抓取慢查询,发现主要由大量如下类似的SQL语句执行缓慢:

select … … (这里是表的所有字段)
fromT_VIDEO video0_  where video0_.VIDEO_PATH='http://www.youtube.com/watch?v=ZjxzF3fNQuI'limit 1;

咨询开发同学,这个是为了确认某条数据是否已经存在,需要查询全部字段并逐一比较。并且表中只有ID列主键,无其他索引。

那么如何缓解这种情况呢?如何确认某条数据是否存在?

制定方案:

1)通过“主键(或者唯一约束)”来判断该行数据是否存在,存在的话直接覆盖更新。

2)坚决不建议逐个字段查询出来一一比较!因为首先,查询语句执行时的Sending Data的时间会加长,当数据量达到一定程度的时候还会产生大量的临时表;其次需要消耗CPU和时间来做比较,性价比不高。

存在问题:

1)存储的URL前n位基本相同或者只有几种,其次URL可能会很长;

2)如果还是使用传统的B-tree索引的话,索引会变得非常大且效率不高

解决方案:

1)大家知道hash索引性能要比B-tree索引好,且基于数字类型的索引性能要比基于字符串的索引好,那么如果我们将URL做一个hash然后在这个hash值上做索引,查询的时候将URL和hash作为where条件,既实现了基于索引的查询,又降低了索引的大小。

2)我们可以使用CRC32函数来实现。

在数据库中建立冗余列URL_CRC,用于存储URL的hash值,这里在插入的时候使用CRC32(“……”)函数,返回值是数字类型

3)在这一列上建立索引

查询的时候使用WHEREURL_CRC=CRC32(“……”) AND URL=”……”,查询优化器会自动使用索引列URL_CRC,即使有重复值,还可以通过URL列二次筛选

案例二:百万级数据分页

项目中数据量已经动辄百万,且会使用到分页。

开发同学在代码中进行分页一般会这么写:

select *from `table` order by iddesc limit 1000000,50;

可是当数据量到达百万、千万或者更多的时候,很可能会出现分页查询性能下降明显的情况,可能从之前的毫秒到现在的几秒或者几十秒。这是为什么呢?

select * from `table` order by id desc limit100,50;         0.016秒
select * from `table` order by id desc limit1000,50;       0.047秒
select * from `table` order by id desc limit10000,50;      0.094秒
select *from `table` order by iddesc limit 100000,50;    0.43秒
select *from `table` order by iddesc limit 1000000,50;  2.23秒

其实limit在实际执行的时候是“查询1000050行数据,然后丢掉前面的1000000行,返回剩下的50行”,是不是发现了很惊悚的问题了呢?! 浪费了大量的I/O性能啊。

如何优化?

代码级:

程序里维护一个变量,用于记录当前要显示的页的数据起始值,SQL语句中使用这个变量的值;

数据库级(SQL级)

利用覆盖索引

selectid fromFROM `tablle`  order by id desclimit 1000000,50;

或者

SELECT* FROM`table` WHERE id <= (SELECT id FROM `table` ORDER BY id desc LIMIT1000000,1) ORDER BY id desc LIMIT 50;

或者

select* FROM`table` AS t1 JOIN (SELECT id FROM `table` ORDER BY id desc LIMIT1000000,1) ASt2 WHERE t1.id<=t2.id order by t1.id desc limit 50;

原理就是记录住当前页id的最大值和最小值,计算跳转页面和当前页相对偏移,由于页面相近,这个偏移量不会很大,这样的话大大减少扫描的行数。

或者

select* from`table` where id between 1000001 and 1000050; 

原理和上面类似,直接定位需要扫描的数据(页),但是如果这个跨度区间内的ID有缺失,那么查询出的数据就小于50条了,这一点一定要注意。

案例三:使用简单SQL去完成复杂功能

原来的执行脚本:

INSERTINTOT_APP_APK_ID_DOWNLOAD
(APK_ID,APP_UPDATE_TIME,DOWNLOAD_NUM)
selecta.APK_ID,a.UPDATE_TIME,IFNULL(b.TOTAL_NUM,0) 
from
(selectMAX(id)id,max(UPDATE_TIME) UPDATE_TIME,APK_ID from T_APP GROUP BY APK_ID) as a
LEFTJOIN
T_APP_DOWNLOAD_STATIbon a.id=b.APP_ID;

4000W数据,所需时间15min+

简化SQL语句的重要方法就是采用临时表暂存中间结果,但是,临时表的好处远远不止这些,将临时结果暂存在临时表,后面的查询就在temptable中了,这可以避免程序中多次扫描主表,也大大减少了程序执行中“共享锁”阻塞“更新锁”,减少了阻塞,提高了并发性能。

分拆后执行计划步骤:

1. 建立中间表

CREATETABLE `T_APP_TMP` (
`ID`int(11) NOT NULL AUTO_INCREMENTCOMMENT '主键', 
`APP_ID`int(11) NOT NULL DEFAULT'0' COMMENT 'APK 唯一标识',
`UPDATE_TIME`datetime NOT NULLDEFAULT '2000-01-01 00:00:00' COMMENT 'APK更新时间',
`APK_ID`varchar(150) NOT NULLDEFAULT '' COMMENT 'APK 唯一标识',
   PRIMARY KEY (`ID`),
KEY`idx_app_appid_code` (`APP_ID`)
)ENGINE=InnoDBAUTO_INCREMENT=1DEFAULT CHARSET=utf8 COMMENT='应用表';

2. 将数据插入中间表

INSERTINTOT_APP_TMP(APP_ID,UPDATE_TIME,APK_ID) select MAX(id)id,max(UPDATE_TIME)UPDATE_TIME,APK_ID from T_APP GROUP BY APK_ID;

3. 将最终结果插入结果表

INSERT INTOT_APP_APK_ID_DOWNLOAD (APK_ID,APP_UPDATE_TIME,DOWNLOAD_NUM)
selecta.APK_ID,a.UPDATE_TIME,IFNULL(b.TOTAL_NUM,0) 
fromT_APP_TMP as a LEFT JOINT_APP_DOWNLOAD_STATI b 
ona.APP_ID=b.APP_ID;

4. 将中间表删除

DROP TABLET_APP_TMP;

按照这个步骤执行,总共不超过5min钟。

原文发布于微信公众号 - MYSQL轻松学(learnmysql)

原文发表时间:2016-09-01

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏杨建荣的学习笔记

MySQL和Oracle对比学习之事务(r5笔记第4天)

MySQL中的存储引擎很是丰富,常用的有InnoDB,MyISAM等,也查看了不少的资料,基本也有所了解,从一些参考书中看MySQL中的sql部分也是一扫而过,...

4088
来自专栏杨建荣的学习笔记

关于收缩数据文件的尝试(r5笔记第34天)

在数据库中对于数据文件都是提前规划,不够就加的情况,很少会留意到其实有些数据文件那么大,其实条件允许也是可以收缩收缩的。 这种情况在本地测试环境中尤为突出,本来...

3346
来自专栏电光石火

mysql 找回误删表的数据办法

有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可。

17810
来自专栏杨建荣的学习笔记

物化视图中的统计信息导致的查询问题分析和修复 (r7笔记第47天)

今天开发的同事下午反馈给我一个问题,说有操作直接卡住了,听这个描述,感觉很可能是查询慢了。 于是连接到环境中,查看了一下正在执行的sql语句情况,发现下面的语句...

3155
来自专栏技术博文

从MyISAM转到InnoDB需要注意什么

转自 MySql中文网 http://mp.weixin.qq.com/s?__biz=MjM5NzAzMTY4NQ==&mid=200910426&idx=1...

33514
来自专栏lgp20151222

MySQL中有关TIMESTAMP和DATETIME的对比

1> 两者都可用来表示YYYY-MM-DD HH:MM:SS[.fraction]类型的日期。

602
来自专栏杨建荣的学习笔记

根据时间字段导入数据的问题总结 (r6笔记第6天)

在之前的博文中介绍过如何通过exchange partition,split partition达到快速的数据切换,对于上百G的大表来说,速度都在秒级完成 对...

2586
来自专栏杨建荣的学习笔记

MySQL中的Online DDL(第一篇)(r11笔记第3天)

记得有一天快下班的时候,一位开发同事找到我说,需要对一个表做变更,数据量据说有上千万,而当时是使用的MySQL版本是5.5,这可如何是好,对于在线业务要求高的情...

3339
来自专栏杨建荣的学习笔记

一条insert语句导致的性能问题分析(二)(r8笔记第43天)

今天对之前描述的问题一条insert语句导致的性能问题分析(一) 进行了进一步的补充。 有一条insert语句的主要性能瓶颈在于insert子句中的查询语句,查...

2885
来自专栏电光石火

mysql 找回误删表的数据办法

有备份的话很简单,只需要生成一个最近备份的数据 然后用mysqlbinlog找回备份时间点之后的数据 再恢复到现网即可。 要是没有备份 可能就会比较麻...

2206

扫描关注云+社区