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 条评论
登录 后参与评论

相关文章

来自专栏牛肉圆粉不加葱

【源码剖析】- Spark 新旧内存管理方案(下)

上一篇文章【源码剖析】- Spark 新旧内存管理方案(上)介绍了旧的内存管理方案以及其实现类 StaticMemoryManager 是如何工作的,本文将通过...

532
来自专栏牛客网

京东提前批研发面经

【每日一语】真实人生中,我们往往在大势底定无可更改时才迟迟进场,却又在胜败未分的浑沌中提早离席。——翁贝托·埃科《开头与结尾》

1022
来自专栏逸鹏说道

我为NET狂官方面试题-数据库篇

求结果:select "1"? 查找包含"objs"的表?查找包含"o"的数据库? 求今天距离2002年有多少年,多少天? 请用一句SQL获取最后更新的事...

3348
来自专栏xingoo, 一个梦想做发明家的程序员

第一章 对象导言 第二章 对象的创建与使用

1 抽象过程 2 对象接口 3 实现隐藏 4 重用 5 继承:重用接口 6 多态性:互换对象 7 创建销毁对象 8 异常处理:应对错误 9 分析...

16810
来自专栏程序员阿凯

JDK10 揭秘

1245
来自专栏跟着阿笨一起玩NET

.NET深入解析LINQ框架(六:LINQ执行表达式)

在看本篇文章之前我假设您已经具备我之前分析的一些原理知识,因为这章所要讲的内容是建立在之前的一系列知识点之上的,为了保证您的阅读顺利建议您先阅读本人的LINQ系...

371
来自专栏andychai

MySQL模糊查询性能优化

根据模糊查找的业务场景,比对一下上面列出的6种条件,如果你的场景是全都要支持,并且是 大用户量, 接口qps高,海量的数据检索量,那就不要在数据库上做任何挣扎了...

4474
来自专栏决胜机器学习

有趣的算法(十) ——归并排序思想解决大量用户数据清洗

有趣的算法(十)——归并排序思想解决用户数据清洗 (原创内容,转载请注明来源,谢谢) 一、问题阐述 近期工作中接触到一个很有趣的算法,在此进行分享。 当前有...

3379
来自专栏开发 & 算法杂谈

Zookeeper C API学习总结

客户端使用C语言开发,zookeeper提供了两个库,zookeeper_st(单线程库)以及zookeeper_mt(多线程库)。

3315
来自专栏决胜机器学习

数据库专题(三) ——Mysql ID生成器

数据库专题(三)——Mysql ID生成器 (原创内容,转载请注明来源,谢谢) 注:本文是我对ID生成器的见解,如果有偏差欢迎指正。 一、需求 在数据库中,...

3038

扫码关注云+社区