MySQL系列优化（一）

MySQL轻松学

发布于 2018-03-09 14:32:21

8730

发布于 2018-03-09 14:32:21

文章被收录于专栏：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. 将中间表删除