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聚集列存储索引

  之前已经写过两篇介绍列存储索引的文章,但是只有非聚集列存储索引,今天再来简单介绍一下聚集的列存储索引,也就是可更新列存储索引。在SQL Server 201...

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

MySQL优化思路及框架

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

36110
来自专栏数据和云

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

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

4197
来自专栏python3

mysql-视图

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

903
来自专栏学习有记

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

1272
来自专栏从ORACLE起航,领略精彩的IT技术。

Oracle数据库该如何着手优化一个SQL

3174
来自专栏Spark学习技巧

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

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

2509
来自专栏文渊之博

SQL Server 2014聚集列存储索引

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

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

MySQL中使用pt-osc的一些小结

Percona的pt-osc工具算是DBA的一个福利工具。想想一个数据量有些大的表,在上面做DDL操作真是一种煎熬,我们也基本理解了这是一种空间换时间的...

1231
来自专栏aoho求索

MySQL探秘(五):InnoDB锁的类型和状态查询

 锁是数据库系统区分于文件系统的一个关键特性。数据库使用锁来支持对共享资源进行并发访问,提供数据的完整性和一致性。此外,数据库事务的隔离性也是通过锁实现的。In...

871

扫码关注云+社区