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

相关文章

来自专栏文渊之博

SQL Server 2014聚集列存储索引

 转发请注明引用和原文博客(https://cloud.tencent.com/developer/user/1217611/activities) 简介   ...

2029
来自专栏数据和云

循序渐进:Oracle 12c新特性Sharding技术解读

引言 数据库构架设计中主要有 Shared Everthting、Shared Nothing 和 Shared Disk: Shared Everthting...

3867
来自专栏友弟技术工作室

MySQL优化思路及框架

MySQL优化框架 1. SQL语句优化 2. 索引优化 3. 数据库结构优化 4. InnoDB表优化 5. MyISAM表优化 6. Memory表优化 7...

35210
来自专栏逸鹏说道

SQL Server 重新组织生成索引

概述 无论何时对基础数据执行插入、更新或删除操作,SQL Server 数据库引擎都会自动维护索引。随着时间的推移,这些修改可能会导致索引中的信息分散在数据库...

3438
来自专栏学习有记

包含列的索引:SQL Server索引进阶 Level 5

1152
来自专栏文渊之博

参数化(四):处理非均匀数据分布

    前面我们了解了参数嗅探可能是好的也可能是坏的。当数列的分布不均匀的时候参数嗅探就是不好的事情。例如,考虑“Status”列在Orders表中有总共10M...

2038
来自专栏沈唁志

谈谈在SQL语句中的优化技巧

1674
来自专栏Spark学习技巧

SparkSql 中外连接查询中的谓词下推规则

SparkSql SparkSql是架构在spark计算框架之上的分布式Sql引擎,使用DataFrame和DataSet承载结构化和半结构化数据来实现数据复杂...

2409
来自专栏python3

mysql-视图

视图是虚拟表或逻辑表,它被定义为具有连接的SQL SELECT查询语句。因为数据库视图与数据库表类似,它由行和列组成,因此可以根据数据库表查询数据。其内容由查询...

853
来自专栏.NET技术

经典SQL语句大全之提升

1、说明:复制表(只复制结构,源表名:a 新表名:b) (Access可用) 法一:select * into b from a where 1<>1(仅用于S...

1143

扫码关注云+社区