博客

MySQL性能优化浅析

  • Post author:
  • Post category:mysql




1. 硬件



1.1 CPU

IO密集型，提升CPU核心数

计算密集型，提升CPU频率



1.2 磁盘

机械硬盘在随机访问时，由于受磁针移动速度的限制，性能会大幅降低。使用固态硬盘可以大幅提升随机访问的能力。按需选择。



1.3 其他

带宽、内存频率



2. 数据库设置



2.1 InnoDB Buffer Pool

Buffer Pool是内存中的一块区域，用于缓存索引、行数据、自适应hash索引、Change Buffer、锁信息等，同时，InnoDB也通过Buffer Pool来实现延迟写，这样就能通过批量写入来提升性能。可以说，Buffer Pool的运行状况会直接影响到InnoDB的性能表现。

理论上来说，Buffer Pool越大，运行时性能越好，当所有数据都在Buffer Pool中时性能达到最优。但是，这显然是不行的。原因如下：

  1. 数据的访问并不是均匀的，所以内存跟性能并不是一个线性的关系。一个缓存了热点数据的Buffer Pool跟一个缓存了所有数据的Buffer Pool，它们在性能上的表现差异并不大。最后，Buffer Pool本身也有最大值限制，在32位跟64位系统分别为：



    2

    32

    −

    1

    2^{32} -1







    2











    32












    −








    1





    、



    2

    64

    −

    1

    2^{64}-1







    2











    64












    −








    1




  2. 磁盘中的数据往往是TB甚至PB级的，内存的价格远高于磁盘
  3. 更大的Buffer Pool会产生更多的脏页，shut down的速度会严重受影响

所以，Buffer Pool的大小应该通过测量实际的工作负载（最常访问的数据的总大小）得出。

Buffer Pool由Buffer Pool Instance组成。instance的数量跟大小分别由

innodb_buffer_pool_instances

、

innodb_buffer_pool_chunk_size


来控制。Buffer Pool的总大小等于

innodb_buffer_pool_instances

*

innodb_buffer_pool_chunk_size

或者直接指定

innodb_buffer_pool_size

，单位是Byte。

另外Buffer Pool 支持自适应大小，通过设置

innodb_dedicated_server

为enable来开启，它会根据服务器的内存自动设置Buffer Pool的大小

Server Memory Buffer Pool Size
Less than 1GB 128MB (the default value)
1GB to 4GB server memory * 0.5
Greater than 4GB server memory * 0.75

注意，官方建议只有在MySQL的专用服务器上才适合开启

innodb_dedicated_server

默认情况下，MySQL在shut down时会将Buffer Pool中的数据dump一个快照保存到磁盘中，当再次启动时，又会将数据从磁盘中加载回来。这种预热的方式可以立即让Buffer Pool回到shut down之前的状态，避免了因重启而导致热数据需要从磁盘中读的问题。该行为通过

innodb_buffer_pool_dump_at_shutdown

、

innodb_buffer_pool_load_at_startup

、

innodb_buffer_pool_dump_pct

控制。

Buffer Pool的实现算法是LRU的一个变种，通过一个Mid Point将LRU列表分成了young跟old两个部分，young最近最多访问的数据，old存新加载到Buffer Pool的以及最近最少访问的数据。

如果系统中



Log Buffer

在事物并发比较高时，可以通过增加Log Buffer的大小来使每次的Flush操作尽可能包含更多已提交的事物。

Log Buffer的大小通过

innodb_log_buffer_size

控制，默认为16MB。这个值也应该根据每次可以Flush的最大数据量来设置，太大没有意义。

另外，也可以通过牺牲一点持久性来换取性能。将

innodb_flush_log_at_trx_commit

设置为2或者0。0拥有更好的性能，相应的丢数据的风险也更大。具体区别可参考

MySQL之InnoDB架构浅析

《高性能MySQL》里的推荐做法是将

innodb_flush_log_at_trx_commit

设置为1，并将日志文件放在一个有电池支持的写缓存和SSD的RAID卷上，写缓存用于提高写入的性能，电池则为数据提供crash-safe的能力，SSD提高随机访问的能力。



2.2 MVCC

在

REPEATABLE READ

隔离级别下，长事物的更新会导致更多的查询事物需要通过undo log来查看旧版本的数据，所以，减小事物的粒度（范围，时间）、将更新操作后移可以有效的减少这种情况。



2.3自增模式

在MySQL8之前的版本可以通过将

innodb_autoinc_lock_mode

设置为2，前提是MySQL的主从复制模式是基于Row的。否则可能会导致自增id不一致。



2.4 IO

如果硬盘的实际性能比较好，可以相应的增大

innodb_io_capacity

的值，这可以提升Buffer Pool后台进程flush的性能。该参数默认为200



2.5 BinLog

据说阿里的

BinLog In Redo

有用，暂时没仔细研究



3. 设计



3.1 表设计



尽量避免NULL列

对MySQL来说，引用可空列的查询更难优化，因为它们使索引、索引统计和值比较更加复杂。可空列使用更多的存储空间，并需要在MySQL内部进行特殊处理。



尽量使用较小或者简单的数据类型

较小的数据类型通常会更快，因为它们在磁盘、内存和CPU缓存中使用的空间更少。它们通常也需要更少的CPU周期来处理。另外，整数要比字符串的比较成本更低，因为字符集和排序规则使字符的比较变得很复杂



冗余&汇总

对于部分经常需要聚合函数进行统计的表，可以通过创建汇总表来降低查询成本。

对于高频的多表查询或者表连接，可以考虑冗余部分字段



选择合适的数据类型

  1. 避免使用字符串作为主键，尤其是UUID、MD5这类函数生成的值。原因如下：

    • 字符串相对数值来说，需要更多的存储空间，且处理起来更复杂
    • UUID产生的值具备随机性，这会导致页分裂、随机磁盘访问、聚集索引碎片，同时还会减慢SELECT查询的速度，因为逻辑上相邻的行会在磁盘内存中不一定是相邻的，最后，这种随机性也破坏了局部性原理，这可能会使热点数据很分散进而导致热点数据被频繁的刷出跟miss

  2. 对于固定的几种数据值，比如性别、状态等，可以考虑使用ENUM类型存储。ENUM会将字符串存储为数值，并保留数值跟字符串的映射关系。这能很有效的节省存储空间。


  3. varchar

    跟

    char

    varchar需要用额外的空间来存储数据的长度，同时，对varchar列数据的更新操作可能会产生页分裂的情况，这会产生额外的性能消耗。所以：

    • 在数据的最大长度跟平均长度相差比较大时可以考虑使用varchar，这样可以节省存储空间。另外，需要注意的是，不要为了一时方便将varchar的大小设置得过大，一定要与实际相符，因为在临时表以及排序中，varchar是按照设置的最大长度来分配内存的
    • 数据的长度很小或者基本相等的情况下，使用char。char不需要额外的字节来存储数据的长度


  4. DATETIME

    和

    TIMESTAMP

    。

    • TIMESTAMP：4字节存储空间，支持时区，允许自动更新，取值范围是1970-2038。
    • DATETIME ：8字节存储空间，取值范围1000-9999

    TIMESTAMP具备更好的性能以及功能性，但它也存在只能存储到2038年的问题。在选择时，可以从以下几个点思考：

    • 需要向后支持多远的时间？
    • 日期、时间和时区处理转移到MySQL，还是在代码中处理？
    • 存储空间对数据存储有多大影响？

    正常情况下，像跟当前时间相关的列比如说数据的创建&更新时间，

    TIMESTAMP是不二之选。一方面是因为2038到现在还比较遥远（很多小公司能不能活到那个时候都是个问题…），眼下的性能以及功能性的提升相对于未来可能发生的迁移在很多场景是都是高性价比的。另一方面，个人觉得MySQL团队在未来会针对TIMESTAMP的范围局限性作出相应更新。

  5. 存正数可以在类型前加上UNSIGNED，这可以使正数的范围增加一倍



索引&SQL

索引是存储引擎用来快速查找行的数据结构，也是提高查询性能的最有力的方法。同时，索引的好坏对性能的影响也是非常巨大的。

在InnoDB中，索引按B+树构造(如图)，分为聚集索引跟二级索引。聚集索引的叶子节点中存储了具体的行数据，二级索引的叶子节点中则存储了主键的值。对于通过二级索引查找完整行数据时，需要先通过二级索引找到主键，再通过主键定位到行数据，这个过程也称为回表。

在这里插入图片描述

由于B+树是按序构建的，这种有序性一方面避免了低效的随机IO访问（相同的值或者相近的值会被放到一起），另一方面对于Order By 这类语句也有着很好的加速作用。

在查找的时候，索引按照从左到右的顺序进行数据匹配（最左前缀），这适用于索引的内容顺序以及索引的顺序（多列索引）。

索引的性能还与索引值的重复度（区分度）息息相关，重复度越高，性能越低。

接下来看看索引相关的优化操作


  • count统计行数避免传入大列，多索引列

    总行数统计，索引数据越小，统计速度越快。

    除非需要统计某列不为空的行数

    。否则

    应该使用count(*)这类不指定列名

    的方式来统计，

    此时MySQL优化器会自动选择最小的那个索引进行扫描

    ，如果是带条件的，count(*)也会自动选择合适的索引来扫描。

    可通过explain的

    key_len

    查看，值越小，性能越高


  • 设置主键

    设置主键或者至少一个唯一索引（在没有主动设置主键的情况下，MySQL会使用该索引作为主键/聚集索引），否则MySQL会为表生成一个隐式主键。这会导致两个问题：

    1. 无法享受主键带来的高性能（无需回表）
    2. 隐式主键的值分配是引擎层面的，所有表共享，存在互斥量的竞争


  • 覆盖索引

    InnoDB在获取数据时，如果二级索引里包含了所需的数据，就不会再回表去获取完整的行数据。这个过程也称为

    Index condition pushdown


  • 最左前缀匹配

    • 按索引顺序组织where子句中的查询条件，避免出现”跳列“的情况，比如按A、B、C三列组织索引，查询条件为A、C。则C不会影响索引的遍历路线。但是Index condition pushdown在这里依然是生效的。
    • 模糊匹配不应为’前’模糊。虽然’前’模糊也能走索引扫描来定位行数据或者享受覆盖索引带来的性能优势，但是无法利用索引的顺序性，简而言之就是得扫描整棵索引树才能完成结果集的查找（explain里的type为index）。


  • 避免在组合索引的非最右索引中使用范围查找

    对于多列索引，比如以A、B、C三列建立索引，如果对A或者B进行模糊匹配或者其他范围查找，这时候，对C的搜索无法应用索引。这就意味着存储引擎会把所有只满足A、B的行数据返回给server，由server来做进一步的筛选。


  • 选择合适的索引长度

    在为BLOB或TEXT列或很长的VARCHAR列建立索引时，必须定义前缀索引，因为MySQL无法为其全长建立索引。

    列的前缀通常是有选择性的，这种选择性指的是列数据之间数据的重复度，重复度越低，选择性越好，高选择性意味着索引的高性能。

    在空间与选择性之间衡量，选择一个足够长的前缀，既能提供良好的选择性，又足够短以节省空间。当选择的前缀长度使得所有前缀都互不相同时，这时从功能上说，在前缀上建立索引跟在整列建立索引是等效的。更小的空间消耗意味着更多的索引值以及更少的IO次数，所以大部分时候（查多列数据时），前缀索引的性能要更优。


  • 选择合适的索引顺序

    将重复度更低的列放在组合索引的更左边，这能更高效的过滤掉不符合条件的数据。


  • 尽量保证索引值有序插入

    有序的插入可以尽量避免页分裂，页分裂需要移动数据，同时回造成更多的空间浪费以及随机磁盘访问（数据不集中，change buffer刷盘效率大幅降低），尤其是对聚集索引来说（需要移动更多的数据）。


  • 避免滥用索引

    索引列的数据发生变化时，不仅要维护行数据，也要维护索引数据。索引会拖慢CUD的速度。对于某些可能没有使用到的索引，可以通过如下语句查找： 

    SELECT * FROM sys.schema_unused_indexes;


  • 运行

    ANALYZE TABLE

    由于InnoDB只会在表第一次打开、表大小发生显著变化、以及执行ANALYZE TABLE时才会更新表的统计信息，所以，有时候可以手动执行ANALYZE TABLE来帮助优化器能做出更优选择。


  • 运行

    OPTIMIZE TABLE


    或者


    ALTER TABLE <table> ENGINE=InnoDB;

    减少磁盘碎片


    数据不紧凑时，索引的访问、全表扫描都会变慢，因为要加载很多无用的数据（空间）到内存。

  • update或者delete语句的where子句里的条件对应的列最好加上索引，尤其是在Repeatable Read隔离级别下，全表扫描会锁住所有扫描过的记录，直到更新提交或回滚



SQL&锁&应用层

  • 只查询确实需要的数据

    • SELECT * 是否值得，否则应将*换成需要的列名
    • 是否有重复的执行

  • 避免长事物

    长事物会加剧锁的竞争，在Repeatable Read隔离级别下，还会导致MVCC的成本增加。避免长事物的方案有：

    • 拆分事物，减小事物的粒度。包括

      • 将影响范围大的Update SQL拆分成影响范围小的SQL
      • 影响范围大的Update条件拆分成影响范围小的条件分批执行，

        比如通过limit来限制影响的行数
      • 控制业务代码的事物粒度（比如在Spring构建的应用里，将标注Transactional的方法拆分或者使用编程式事物来缩小事物的粒度）
    • 将update操作延后，将耗时更长的操作延后

  • 避免重复查询

    比如，同样或者类似的SQL，参数不同。这会增加MySQL的解析成本以及IO成本（数据在磁盘时）。所以，合并这类查询，将数据拿到应用层来过滤也是一种提升性能的方法

  • or 改为 in

    in 在MySQL里会先排序再二分查找，时间复杂度是



    O

    (

    log

    ⁡

    n

    )

    O(\log n)






    O


    (


    lo

    g





    n


    )





    ，n是参数的数量

    or 会对条件一个一个查找，时间复杂度是



    O

    (

    n

    )

    O(n)






    O


    (


    n


    )




  • join

    • 非驱动表的on 或者 using指定的列建立索引
    • 行数更少的表作为驱动表，MySQL大多数情况下会自动做出最优选择。前提是连接的表数量不超过

      optimizer_search_depth

      指定的值，该值默认是62，过大的值会导致MySQL在join的表数量很大时会在优化上消耗大量时间。可以通过将

      optimizer_search_depth

      设置为0来让MySQL自动确定是否进行join的全部可能性的搜索优化或者使用贪心算法来优化。
    • 避免order by 或者group by 里的列跨表

  • 避免深分页

    像select * from <table> limit m, n这样的操作，MySql会取出m+n行记录，然后丢弃掉前m行。所以，当m很大的时候，性能就会非常低。

    可以先通过索引找到第m行到第m+n行的id，然后根据id取出后面的n行即可。 

    select * from <table> INNER JOIN (
    SELECT id FROM <table>
    ORDER BY <index column> LIMIT m, n
    ) AS tmp USING(id);

    或者事先知道第m行的id，且排序也是按主键的顺序，则SQL可以直接写为： 

    select * from <table> where id>=<id from m.th>,n


  • union

    vs

    union all

    union会过滤重复数据，如果业务上不会有重复数据的情况，则使用union all可以节省判断重复数据时产生的性能损耗

  • 避免在where子句中的索引列上做函数运算或者类型转换，比如concat、cast、或者算数运算等

    这会导致索引失效



3.2 数据分片

  • 将行数很大的表按行将数据拆分到小表中，可以降低B树的高度提升索引的效率，另外，如果将表拆分到不同的数据库实例中，IO跟CPU性能也能得到提升
  • 将表里一些不常用的大字段拆分到其他表中，可以增加聚集索引页字节点的数据紧凑度，从而更少的IO能获取更多的数据。



4. 指标

MySQL通过存储引擎提供的索引的页数、表和索引的重复度、行和索引键的长度，以及键分布信息来生成执行计划。通过explain可以看到相关的统计信息。相关指标参考**

8.8.2 EXPLAIN Output Format

**



5. 参考

《High Performance MySQL》 4th edition


MySQL Official Documentation


版权声明：本文为scientificCommunity原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
Tags: