MySQL优化

发表于 更新于 分类于 Disqus：
在实际生产过程中，如何进行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%