MySQL优化

为什么要进行SQL优化

sql查询缓慢

慢SQL出现原因

  1. 单表数据量过大
  2. 表设计不合理
  3. sql语句写的不好
  4. 没有合理使用索引

单表数据量过大

单表数据量大小判断,有两种说法:

  1. 单表超过2000万,性能会急剧下降。
  2. 阿里巴巴开发者手册:单表超过500万或2GB。

单表空间占用查询:

1
2
3
4
5
6
7
use information_schema;

select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
from tables where
table_schema='product'
and table_name = 'user_account_info';
1
2
3
4
5
+----------------+-----------------+  
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 2581.52MB | 2739.69MB |
+----------------+-----------------+

解决方案

  • 方案一:优化现有mysql数据库。优点:不影响现有业务,源程序不需要修改代码,成本最低。缺点:有优化瓶颈,数据量过亿就玩完了。
  • 方案二:升级数据库类型,换一种100%兼容mysql的数据库。优点:不影响现有业务,源程序不需要修改代码,你几乎不需要做任何操作就能提升数据库性能,缺点:多花钱。
  • 方案三:一步到位,大数据解决方案,更换newsql/nosql数据库。优点:没有数据容量瓶颈,缺点:需要修改源程序代码,影响业务,总成本最高。

方案一:优化现有数据库

  1. 数据库设计和表创建时就要考虑性能

    设计表时要注意:

    • 表字段避免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越大,离散程度越高:
  2. 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来分页,每页数量也不要太大
  3. 分区

    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. */
  4. 分表

    分表就是把一张大表,按照如上过程都优化了,还是查询卡死,那就把这个表分成多张表,把一次查询分成多次查询,然后把结果组合返回给用户。

    分表分为垂直拆分和水平拆分,通常以某个字段做拆分项。比如以id字段拆分为100张表: 表名为 tableName_id%100

    但:分表需要修改源程序代码,会给开发带来大量工作,极大的增加了开发成本,故:只适合在开发初期就考虑到了大量数据存在,做好了分表处理,不适合应用上线了再做修改,成本太高!!!而且选择这个方案,都不如选择我提供的第二第三个方案的成本低!故不建议采用。

  5. 分库

    把一个数据库分成多个,建议做个读写分离就行了,真正的做分库也会带来大量的开发成本,得不偿失!不推荐使用。

方案二:升级数据库,换一个100%兼容mysql的数据库

  1. 开源选择

  2. 云数据选择

    • 阿里云POLARDB:POLARDB 是阿里云自研的下一代关系型分布式云原生数据库,100%兼容MySQL,存储容量最高可达 100T,性能最高提升至 MySQL 的 6 倍。POLARDB 既融合了商业数据库稳定、可靠、高性能的特征,又具有开源数据库简单、可扩展、持续迭代的优势,而成本只需商用数据库的 1/10。支持免费mysql的数据迁移,无操作成本,性能提升在10倍左右,价格跟rds相差不多,是个很好的备选解决方案!
    • 阿里云OcenanBase
    • 阿里云HybridDB for MySQL (原PetaData)
    • 腾讯云DCDB:一种兼容MySQL协议和语法,支持自动水平拆分的高性能分布式数据库——即业务显示为完整的逻辑表,数据却均匀的拆分到多个分片中;每个分片默认采用主备架构,提供灾备、恢复、监控、不停机扩容等全套解决方案,适用于TB或PB级的海量数据场景。

方案三:去掉mysql,换大数据引擎处理数据

  1. 开源解决方案

    hadoop家族。hbase/hive怼上就是了。但是有很高的运维成本,一般公司是玩不起的,没十万投入是不会有很好的产出的!

  2. 云解决方案

    • 这个就比较多了,也是一种未来趋势,大数据由专业的公司提供专业的服务,小公司或个人购买服务,大数据就像水/电等公共设施一样,存在于社会的方方面面。
    • 国内做的最好的当属阿里云。
    • 我选择了阿里云的MaxCompute配合DataWorks,使用超级舒服,按量付费,成本极低。
    • MaxCompute可以理解为开源的Hive,提供sql/mapreduce/ai算法/python脚本/shell脚本等方式操作数据,数据以表格的形式展现,以分布式方式存储,采用定时任务和批处理的方式处理数据。DataWorks提供了一种工作流的方式管理你的数据处理任务和调度监控。
    • 当然你也可以选择阿里云hbase等其他产品,我这里主要是离线处理,故选择MaxCompute,基本都是图形界面操作,大概写了300行sql,费用不超过100块钱就解决了数据处理问题。

表设计不合理

  1. 建表时,就要根据规划中不同表的用途和查询方式建立合适的索引;

  2. 编写SQL时,要清楚的知道表都有哪些索引,如何才能最大化的利用索引查询,降低查询速度,尤其是数据量超大(超过100万条),或者需要进行多表关联时,本条规则就尤其重要;

  3. SQL编写后,要养成使用explain命令查看索引使用情况的习惯,避免一些本应该使用索引的SQL并没有使用索引,或者因为数据问题,使用索引可能还没有不用来的快;

  4. 定时查看数据库执行情况,使用show processlist或者开启慢查询,查看慢查询日志,分析执行效率低的SQL;

  5. 当发生数据查询慢,或者数据库IO开销很高的情况,要学会使用show processlist或show full processlist命令查看数据库当前在做什么,哪些SQL执行慢,问题出现在哪里,等等。

SQL语句写的不好

  1. 查询语句中不要使用 *

  2. 尽量减少子查询,使用关联查询(left join,right join,inner join)替代

  3. 减少使用IN或者NOT IN ,使用exists,not exists或者关联查询语句替代

  4. or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时,union all会更好)

  5. 合理的增加冗余的字段(减少表的联接查询)

  6. 增加中间表进行优化(这个主要是在统计报表的场景,后台开定时任务将数据先统计好,尽量不要在查询的时候去统计)

  7. 建表的时候能使用数字类型的字段就使用数字类型(type,status…),数字类型的字段作为条件查询比字符串的快

  8. 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的最末尾

没有合理使用索引

索引设计使用原则

  1. 为了提升数据检索速度,降低查询延时和IO消耗,在建表或者编写SQL语句时,应该首先设计及检查索引和数据分布情况,下面是常见的索引设计或使用原则:

  2. 尽量选择惟一性索引;

  3. 为经常需要排序、分组和联合操作的字段建立索引;

  4. 为常作为查询条件的字段建立索引;

  5. 限制索引的数目,索引越多,更新和插入的效率越低;

  6. 尽量使用数据量少的索引;

  7. 组合索引中的尽量把能过滤掉更多数据的字段放在前面;

  8. 尽量使用前缀来索引,针对长文本,TEXT等类型;

  9. 删除不再使用或者很少使用的索引;

  10. 数据是动态变化中的,索引的使用也需要根据数据的变化而变化。

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代码无需改动。

缺点:

  1. 分区表,分区键设计不太灵活,如果不走分区键,很容易出现全表锁
  2. 一旦数据量并发量上来,如果在分区表实施关联,就是一个灾难
  3. 分区表,研发写了一个sql,都不确定mysql是怎么玩的,不太可控。

所以这个方案被pass掉了。

分表

概念:垂直拆分和水平拆分,水平拆分指的是对数据表行进行拆分,可以把一张表的数据拆分为多张表来存放。

改动点:

  1. 对业务有侵入,不过可以再持久化层做一层封装,查找对应的分表,屏蔽上层对其的感知
  2. 在业务层面屏蔽聚合操作,如需查询通过指定后台进行查询,比如以用户id维度进行分表,那么在查询的时候需要先定位一个具体的用户,再进行查询
  3. 对于需要聚合查询的操作,在数据部门添加一张聚合表(通过读取bin-log生成),通过查询这张表来完成

可以看到这个方案实施起来也比较费劲。

数据备份

通过研究数据发现,业务确保了2天之前的业务都是成功的,所以这里的一个方案就是每周一备成功的数据,生产库中保留三个月的备份数据,超过三个月的备份到备份数据库。

这样的操作对业务是无侵入的。

0%