MySQL优化
为什么要进行SQL优化
sql查询缓慢
慢SQL出现原因
- 单表数据量过大
- 表设计不合理
- sql语句写的不好
- 没有合理使用索引
单表数据量过大
单表数据量大小判断,有两种说法:
- 单表超过2000万,性能会急剧下降。
- 阿里巴巴开发者手册:单表超过500万或2GB。
单表空间占用查询:
1 | use information_schema; |
1 | +----------------+-----------------+ |
解决方案
- 方案一:优化现有mysql数据库。优点:不影响现有业务,源程序不需要修改代码,成本最低。缺点:有优化瓶颈,数据量过亿就玩完了。
- 方案二:升级数据库类型,换一种100%兼容mysql的数据库。优点:不影响现有业务,源程序不需要修改代码,你几乎不需要做任何操作就能提升数据库性能,缺点:多花钱。
- 方案三:一步到位,大数据解决方案,更换newsql/nosql数据库。优点:没有数据容量瓶颈,缺点:需要修改源程序代码,影响业务,总成本最高。
方案一:优化现有数据库
数据库设计和表创建时就要考虑性能
设计表时要注意:
- 表字段避免null值出现,null值很难查询优化且占用额外的索引空间,推荐默认数字0代替null。
- 尽量使用INT而非BIGINT,如果非负则加上UNSIGNED(这样数值容量会扩大一倍),当然能使用TINYINT、SMALLINT、MEDIUM_INT更好。
- 使用枚举或整数代替字符串类型
- 尽量使用TIMESTAMP而非DATETIME
- 单表不要有太多字段,建议在20以内
- 用整型来存IP
索引
- 索引并不是越多越好,要根据查询有针对性的创建,考虑在WHERE和ORDER BY命令上涉及的列建立索引,可根据EXPLAIN来查看是否用了索引还是全表扫描
- 应尽量避免在WHERE子句中对字段进行NULL值判断,否则将导致引擎放弃使用索引而进行全表扫描
- 值分布很稀少的字段不适合建索引,例如”性别”这种只有两三个值的字段
- 字符字段只建前缀索引
- 字符字段最好不要做主键
- 不用外键,由程序保证约束
- 尽量不用UNIQUE,由程序保证约束
- 使用多列索引时主意顺序和查询条件保持一致,同时删除不必要的单列索引
选择合适的数据类型
- 使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob。
- 使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数。
- 使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar。
- 尽可能使用not null定义字段。
- 尽量少用text,非用不可最好分表。
选择合适的索引列
- 查询频繁的列,在where,group by,order by,on从句中出现的列
- where条件中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列
- 长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好
- 离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count越大,离散程度越高:
sql的编写需要注意优化
- 使用limit对查询结果的记录进行限定
- 避免select *,将需要查找的字段列出来
- 使用连接(join)来代替子查询
- 拆分大的delete或insert语句
- 可通过开启慢查询日志来找出较慢的SQL
- 不做列运算:SELECT id WHERE age + 1 = 10,任何对列的操作都将导致表扫描,它包括数据库教程函数、计算表达式等等,查询时要尽可能将操作移至等号右边
- sql语句尽可能简单:一条sql只能在一个cpu运算;大语句拆小语句,减少锁时间;一条大sql可以堵死整个库
- OR改写成IN:OR的效率是n级别,IN的效率是log(n)级别,in的个数建议控制在200以内
- 不用函数和触发器,在应用程序实现
- 避免%xxx式查询
- 少用JOIN
- 使用同类型进行比较,比如用’123’和’123’比,123和123比
- 尽量避免在WHERE子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描
- 对于连续数值,使用BETWEEN不用IN:
SELECT id FROM t WHERE num BETWEEN 1 AND 5
- 列表数据不要拿全表,要使用LIMIT来分页,每页数量也不要太大
分区
MyISAM,MyISAM引擎是MySQL 5.1及之前版本的默认引擎,它的特点是:
- 不支持行锁,读取时对需要读到的所有表加锁,写入时则对表加排它锁
- 不支持事务
- 不支持外键
- 不支持崩溃后的安全恢复
- 在表有读取查询的同时,支持往表中插入新纪录
- 支持BLOB和TEXT的前500个字符索引,支持全文索引
- 支持延迟更新索引,极大提升写入性能
- 对于不会进行修改的表,支持压缩表,极大减少磁盘空间占用
InnoDB,InnoDB在MySQL 5.5后成为默认索引,它的特点是:
- 支持行锁,采用MVCC来支持高并发
- 支持事务
- 支持外键
- 支持崩溃后的安全恢复
- 不支持全文索引
MyISAM适合SELECT密集型的表,而InnoDB适合INSERT和UPDATE密集型的表。
MySQL在5.1版引入的分区是一种简单的水平拆分,用户需要在建表的时候加上分区参数,对应用是透明的无需修改代码。
对用户来说,分区表是一个独立的逻辑表,但是底层由多个物理子表组成,实现分区的代码实际上是通过对一组底层表的对象封装,但对SQL层来说是一个完全封装底层的黑盒子。MySQL实现分区的方式也意味着索引也是按照分区的子表定义,没有全局索引。
用户的SQL语句是需要针对分区表做优化,SQL条件中要带上分区条件的列,从而使查询定位到少量的分区上,否则就会扫描全部分区,可以通过EXPLAIN PARTITIONS来查看某条SQL语句会落在那些分区上,从而进行SQL优化,我测试,查询时不带分区条件的列,也会提高速度,故该措施值得一试。
分区的好处
- 可以让单表存储更多的数据
- 分区表的数据更容易维护,可以通过清楚整个分区批量删除大量数据,也可以增加新的分区来支持新插入的数据。另外,还可以对一个独立分区进行优化、检查、修复等操作
- 部分查询能够从查询条件确定只落在少数分区上,速度会很快
- 分区表的数据还可以分布在不同的物理设备上,从而搞笑利用多个硬件设备
- 可以使用分区表赖避免某些特殊瓶颈,例如InnoDB单个索引的互斥访问、ext3文件系统的inode锁竞争
- 可以备份和恢复单个分区
分区的限制和缺点
- 一个表最多只能有1024个分区
- 如果分区字段中有主键或者唯一索引的列,那么所有主键列和唯一索引列都必须包含进来
- 分区表无法使用外键约束
- NULL值会使分区过滤无效
- 所有分区必须使用相同的存储引擎
分区的类型
- RANGE分区:基于属于一个给定连续区间的列值,把多行分配给分区。
- LIST分区:类似于按RANGE分区,区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
- HASH分区:基于用户定义的表达式的返回值来进行选择的分区,该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。
- KEY分区:类似于按HASH分区,区别在于KEY分区只支持计算一列或多列,且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。
1
2
3
4
5
6
7
8
9首先根据月份把上网记录表RANGE分区了12份,查询效率提高6倍左右,效果不明显,故:换id为HASH分区,分了64个分区,查询速度提升显著。问题解决!
结果如下:PARTITION BY HASH (id)PARTITIONS 64
select count() from readroom_website; --11901336行记录
/ 受影响行数: 0 已找到记录: 1 警告: 0 持续时间 1 查询: 5.734 sec. /
select * from readroom_website where month(accesstime) =11 limit 10;
/ 受影响行数: 0 已找到记录: 10 警告: 0 持续时间 1 查询: 0.719 sec. */分表
分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次查询分成多次查询,然后把结果组合返回给用户。
分表分为垂直拆分和水平拆分,通常以某个字段做拆分项。比如以id字段拆分为100张表: 表名为 tableName_id%100
但:分表需要修改源程序代码,会给开发带来大量工作,极大的增加了开发成本,故:只适合在开发初期就考虑到了大量数据存在,做好了分表处理,不适合应用上线了再做修改,成本太高!!!而且选择这个方案,都不如选择我提供的第二第三个方案的成本低!故不建议采用。
分库
把一个数据库分成多个,建议做个读写分离就行了,真正的做分库也会带来大量的开发成本,得不偿失!不推荐使用。
方案二:升级数据库,换一个100%兼容mysql的数据库
开源选择
- tiDB https://github.com/pingcap/tidb
- Cubrid https://www.cubrid.org/
- 开源数据库会带来大量的运维成本且其工业品质和MySQL尚有差距,有很多坑要踩,如果你公司要求必须自建数据库,那么选择该类型产品。
云数据选择
- 阿里云POLARDB:POLARDB 是阿里云自研的下一代关系型分布式云原生数据库,100%兼容MySQL,存储容量最高可达 100T,性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征,又具有开源数据库简单、可扩展、持续迭代的优势,而成本只需商用数据库的 1/10。支持免费mysql的数据迁移,无操作成本,性能提升在10倍左右,价格跟rds相差不多,是个很好的备选解决方案!
- 阿里云OcenanBase
- 阿里云HybridDB for MySQL (原PetaData)
- 腾讯云DCDB:一种兼容MySQL协议和语法,支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表,数据却均匀的拆分到多个分片中;每个分片默认采用主备架构,提供灾备、恢复、监控、不停机扩容等全套解决方案,适用于TB或PB级的海量数据场景。
方案三:去掉mysql,换大数据引擎处理数据
开源解决方案
hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本,一般公司是玩不起的,没十万投入是不会有很好的产出的!
云解决方案
- 这个就比较多了,也是一种未来趋势,大数据由专业的公司提供专业的服务,小公司或个人购买服务,大数据就像水/电等公共设施一样,存在于社会的方方面面。
- 国内做的最好的当属阿里云。
- 我选择了阿里云的MaxCompute配合DataWorks,使用超级舒服,按量付费,成本极低。
- MaxCompute可以理解为开源的Hive,提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据,数据以表格的形式展现,以分布式方式存储,采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。
- 当然你也可以选择阿里云hbase等其他产品,我这里主要是离线处理,故选择MaxCompute,基本都是图形界面操作,大概写了300行sql,费用不超过100块钱就解决了数据处理问题。
表设计不合理
建表时,就要根据规划中不同表的用途和查询方式建立合适的索引;
编写SQL时,要清楚的知道表都有哪些索引,如何才能最大化的利用索引查询,降低查询速度,尤其是数据量超大(超过100万条),或者需要进行多表关联时,本条规则就尤其重要;
SQL编写后,要养成使用explain命令查看索引使用情况的习惯,避免一些本应该使用索引的SQL并没有使用索引,或者因为数据问题,使用索引可能还没有不用来的快;
定时查看数据库执行情况,使用show processlist或者开启慢查询,查看慢查询日志,分析执行效率低的SQL;
当发生数据查询慢,或者数据库IO开销很高的情况,要学会使用show processlist或show full processlist命令查看数据库当前在做什么,哪些SQL执行慢,问题出现在哪里,等等。
SQL语句写的不好
查询语句中不要使用 *
尽量减少子查询,使用关联查询(left join,right join,inner join)替代
减少使用IN或者NOT IN ,使用exists,not exists或者关联查询语句替代
or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时,union all会更好)
合理的增加冗余的字段(减少表的联接查询)
增加中间表进行优化(这个主要是在统计报表的场景,后台开定时任务将数据先统计好,尽量不要在查询的时候去统计)
建表的时候能使用数字类型的字段就使用数字类型(type,status…),数字类型的字段作为条件查询比字符串的快
那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾
没有合理使用索引
索引设计使用原则
为了提升数据检索速度,降低查询延时和IO消耗,在建表或者编写SQL语句时,应该首先设计及检查索引和数据分布情况,下面是常见的索引设计或使用原则:
尽量选择惟一性索引;
为经常需要排序、分组和联合操作的字段建立索引;
为常作为查询条件的字段建立索引;
限制索引的数目,索引越多,更新和插入的效率越低;
尽量使用数据量少的索引;
组合索引中的尽量把能过滤掉更多数据的字段放在前面;
尽量使用前缀来索引,针对长文本,TEXT等类型;
删除不再使用或者很少使用的索引;
数据是动态变化中的,索引的使用也需要根据数据的变化而变化。
show processlist 命令
输入show processlist命令后可以看到数据库中所有连接中的session,以及相应SQL的执行情况。
其中:
字段 | 说明 |
---|---|
id | 是session id,可以使用kill xxxx 的方式来杀死session; |
user,host,db | 是session操作的用户,主机和数据库; |
command | 是命令类型; |
info | 是详细的SQL语句; |
time | 是相应命令执行时间; |
state | 是命令执行状态; |
state状态意义:
state | 含义 |
---|---|
Checking table | 正在检查数据表(这是自动的) |
Closing tables | 正在将表中修改的数据刷新到磁盘中,同时正在关闭已经用完的表。这是一个很快的操作,如果不是这样的话,就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。 |
Connect Out | 复制从服务器正在连接主服务器。 |
Copying to tmp table on disk | 由于临时结果集大于tmp_table_size,正在将临时表从内存存储转为磁盘存储以此节省内存。 |
Creating tmp table | 正在创建临时表以存放部分查询结果。 |
deleting from main table | 服务器正在执行多表删除中的第一部分,刚删除第一个表。 |
deleting from reference tables | 服务器正在执行多表删除中的第二部分,正在删除其他表的记录。 |
Flushing tables | 正在执行FLUSH TABLES,等待其他线程关闭数据表 |
Killed | 发送了一个kill请求给某线程,那么这个线程将会检查kill标志位,同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位,不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了,那么kill请求会在锁释放时马上生效。 |
Locked | 被其他查询锁住了 |
Sending data | 正在处理SELECT查询的记录,同时正在把结果发送给客户端。 |
Sorting for group | 正在为GROUP BY做排序。 |
Sorting for order | 正在为ORDER BY做排序。 |
Opening tables | 这个过程应该会很快,除非受到其他因素的干扰。例如,在执ALTER TABLE或LOCK TABLE语句行完以前,数据表无法被其他线程打开。正尝试打开一个表。 |
Removing duplicates | 正在执行一个SELECT DISTINCT方式的查询,但是MySQL无法在前一个阶段优化掉那些重复的记录。因此,MySQL需要再次去掉重复的记录,然后再把结果发送给客户端。 |
Reopen table | 获得了对一个表的锁,但是必须在表结构修改之后才能获得这个锁。已经释放锁,关闭数据表,正尝试重新打开数据表。 |
Repair by sorting | 修复指令正在排序以创建索引。 |
Repair with keycache | 修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。 |
Searching rows for update | 正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。 |
Sleeping | 正在等待客户端发送新请求 |
System lock | 正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表,那么可以通过增加–skip-external-locking参数来禁止外部系统锁。 |
Upgrading lock | INSERT DELAYED正在尝试取得一个锁表以插入新记录。INSERT DELAYED正在尝试取得一个锁表以插入新记录。 |
Updating | 正在搜索匹配的记录,并且修改它们。 |
User Lock | 正在等待GET_LOCK()。 |
Waiting for tables | 该线程得到通知,数据表结构已经被修改了,需要重新打开数据表以取得新的结构。然后,为了能的重新打开数据表,必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知:FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。 |
waiting for handler insert | INSERT DELAYED已经处理完了所有待处理的插入操作,正在等待新的请求。 大部分状态对应很快的操作,只要有一个线程保持同一个状态好几秒钟,那么可能是有问题发生了,需要检查一下。 还有其他的状态没在上面中列出来,不过它们大部分只是在查看服务器是否有存在错误是才用得着。 |
Explain 命令
在所执行的SQL前加explain命令,即可查看相应SQL的执行计划,例如一个很简单的like查询,是否使用了索引,可以通过possible_keys和rows很容易能看出来。
实际项目操作
单表数据量过大
背景
ast_mq_task表单表超过了3千万,对于一些核心的查询,虽然索引设计合理,但是查询还是慢(超过1s)。
解决思路
分区
概念:所有数据还在一个表中,但物理存储根据一定的规则放在不同的文件中。这个是mysql支持的功能,业务rd代码无需改动。
缺点:
- 分区表,分区键设计不太灵活,如果不走分区键,很容易出现全表锁
- 一旦数据量并发量上来,如果在分区表实施关联,就是一个灾难
- 分区表,研发写了一个sql,都不确定mysql是怎么玩的,不太可控。
所以这个方案被pass掉了。
分表
概念:垂直拆分和水平拆分,水平拆分指的是对数据表行进行拆分,可以把一张表的数据拆分为多张表来存放。
改动点:
- 对业务有侵入,不过可以再持久化层做一层封装,查找对应的分表,屏蔽上层对其的感知
- 在业务层面屏蔽聚合操作,如需查询通过指定后台进行查询,比如以用户id维度进行分表,那么在查询的时候需要先定位一个具体的用户,再进行查询
- 对于需要聚合查询的操作,在数据部门添加一张聚合表(通过读取bin-log生成),通过查询这张表来完成
可以看到这个方案实施起来也比较费劲。
数据备份
通过研究数据发现,业务确保了2天之前的业务都是成功的,所以这里的一个方案就是每周一备成功的数据,生产库中保留三个月的备份数据,超过三个月的备份到备份数据库。
这样的操作对业务是无侵入的。