MySQL性能优化

蓝色记忆
1,229 阅读8分钟

MySQL性能优化在开发中非常重要，包括在面试的时候也是必问的。本文从6个不同角度去说说这件事。

 1.MySQL配置优化 
 2.MySQL表结构优化 
 3.索引优化 
 4.sql语句优化 
 5.架构优化 
 6.服务器配置优化

MySQL配置优化

  • 表示缓冲池字节大小。
    推荐值为物理内存的50%~80%。
    innodb_buffer_pool_size

  • 用来控制redo log刷新到磁盘的策略。
    innodb_flush_log_at_trx_commit=1

  • 每提交1次事务同步写到磁盘中，可以设置为n。
    sync_binlog=1

  • 脏页占innodb_buffer_pool_size的比例时，触发刷脏页到磁盘。 推荐值为25%~50%。
    innodb_max_dirty_pages_pct=30

  • 后台进程最大IO性能指标。
    默认200，如果SSD，调整为5000~20000
    innodb_io_capacity=200

  • 指定innodb共享表空间文件的大小。
    innodb_data_file_path

  • 慢查询日志的阈值设置，单位秒。
    long_qurey_time=0.3

  • mysql复制的形式，row为MySQL8.0的默认形式。
    binlog_format=row

  • 调高该参数则应降低interactive_timeout、wait_timeout的值。
    max_connections=200

  • 过大，实例恢复时间长；过小，造成日志切换频繁。
    innodb_log_file_size

  • 全量日志建议关闭。
    默认关闭。
    general_log=0

  • key_buffer_size：索引缓冲区的大小，它决定索引处理的速度，尤其是索引读的速度。

  • max_connections

      最大连接数。默认值是151，最多2000。如果服务器的并发连接请求量比较大，建议调高        此值，以增加并行连接数量。但是如果连接数越多，介于MySQL会为每个连接提供连接缓        冲区，就会开销越多的内存，所以要适当调整该值


相关配置很多，这里只是简单说几个。


MySQL表结构优化

在建表的时候：

一张表建议不要超过20个字段

建议使用InnoDB引擎，支持事务，行级锁，更好的恢复性，高并发下性能更好，对多核，大内存，ssd等硬件支持更好。

字段必须使用合适的类型，1.避免MySQL进行隐式类型转化。因为MySQL进行隐式类型转化之后，可能会将索引字段类型转化成=号右边值的类型，导致使用不到索引，原因和避免在索引字段中使用函数是类似的。2.能用TINYINT就不用SMALLINT，能用SMALLINT就不用INT，道理你懂的，磁盘和内存消耗越小越好嘛  3. ip地址使用int存不要varchar，时间使用TIMESTAMP不用DATETIME，因为TIMESTAMP只有4个字节，而DATETIME8个字节

建议字段定义为NOT NULL，因为NULL值会影响优化器对索引的选择，索引效率会下降很多，而且值的比较变得更复杂

不要过多的建索引，第一索引是占磁盘空间的，第二过多索引会导致update，insert，delete的性能下降，因为在UPDATE、DELETE、INSERT的时候需要对b+tree进行调整，过多的索引会减慢更新的速度

为每一张表都建个自增的主键id，因为b+tree是一种排好序的快速查找结构，id正好是自增的，维护性高。如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键（类似oracle中的RowId）来作为聚簇索引。这些都是没有顺序的，维护起来复杂。参考文章

//待补充。。。。


索引优化 

索引设计原则：

  1. 最适合索引的列是在where子句中的列，或连接子句中的列，而不是出现在select关键字后的列
  2. 使用唯一索引。考虑某列中值的分布。索引列的基数越大，效果越好（一列中相同的数据越少，索引越好），比如性别字段只有男女，不适合做索引
  3. 使用短索引。如果对字符串列进行索引，应该指定一个前缀长度。这样可以节省索引空间和磁盘IO。（alter tableName add key indexName (columnName(7)) --给表tableName的columnName字段的前7位建立前缀做引，索引名字为indexName）
  4. 利用最左前缀。比如创建了一个多列索引 index_c1_c2_c3 (c1,c2,c3)，相当于创建了(c1)单列索引，(c1,c2)的组合做引以及(c1,c2,c3)的组合索引。根据这个原则，在创建多列索引时，要根据业务需求 ，where子句中使用最频繁的一列要放在索引的最左边。
  5. 数据量少的表，不建议建索引
  6. 更新非常频繁的数据不适宜建索引，因为要维护索引的成本
  7. 不要在索引列上做任何的操作，包括计算、函数、自动或者手动类型的转换，这样都会导致索引失效。
  8. 不要过度索引。索引过多，会导致磁盘占用较高，insert和update操作耗时增加，查询优化效率会变低。

不会使用到索引情况：

  1. 以%开头的like查询不能使用索引，like '%XX'或者like '%XX%'不能使用到索引
  2. 数据类型出现隐式转换的不能使用索引。数据INT类型，而用varchar查询（select * from user where age=12   比如age是字符串类型，这条语句索引就失效了，应该是age='12'）
  3. 复合索引的情况下，假如查询条件不包含索引列最左边部分，不使用索引
  4. 如果MySQL估计使用索引比全表扫描慢，不使用索引
  5. 用or分隔开的条件，如果or前的列中有索引，而后边的列中没有索引，不会使用索引。（or的所有条件必须全部使用索引字段才会走索引
  6. 在索引字段上做任何计算，函数，类型转换等操作，都会导致索引失效
  7. !=,<>，is null,is not null 都会导致索引失效
  8. 等待补充。。。。。。

sql语句优化 

  •  避免select *
  • 结果只有一条的时候加LIMIT 1
  • 大表之间最好不要联表，会进行笛卡尔积生成更大数据的中间表
  • 不要过多使用in的子查询
  • 适当的增加冗余字段，冗余表，来消除联表的情况
  • 同一条sql语句尽量保持一致，可以被查询缓存命中
  • 不要在for循环里面写select等语句
  • 区分in和existsIN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况
  • 待补充。。。。

架构优化 

随着业务量增大，单台数据库压力越来越大，表的数据也越来越大，MySQL成为系统瓶颈，单机存储容量、连接数、处理能力都有限。这个时候分库分表，读写分离就出现了。

分库分表

分库分表包含：垂直分库(表)，水平分库(表)

垂直分库(表)

垂直分库：就是根据业务耦合性，将关联度低的不同表存储在不同的数据库。做法与大系统拆分为多个小系统类似，按业务分类进行独立划分。与"微服务治理"的做法相似，每个微服务使用单独的一个数据库。

垂直分表：是基于数据库中的"列"进行，某个表字段较多，可以新建一张扩展表，将不经常用或字段长度较大的字段拆分出去到扩展表中。

垂直分库(表)的优点：

  • 解决业务系统层面的耦合，业务清晰
  • 与微服务的治理类似，也能对不同业务的数据进行分级管理、维护、监控、扩展等
  • 高并发场景下，垂直切分一定程度的提升IO、数据库连接数、单机硬件资源的瓶颈

垂直分库(表)缺点：

  • 部分表无法join，只能通过接口聚合方式解决，提升了开发的复杂度
  • 分布式事务处理复杂
  • 依然存在单表数据量过大的问题（需要水平切分）

水平分库(表)

是根据表内数据内在的逻辑关系，将同一个表按不同的条件分散到多个数据库或多个表中，每个表中只包含一部分数据，从而使得单个表的数据量变小，达到分布式的效果。

水平切分的优点：

  • 不存在单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力
  • 应用端改造较小，不需要拆分业务模块

缺点：

  • 跨分片的事务一致性难以保证
  • 跨库的join关联查询性能较差
  • 数据多次扩展难度和维护量极大

读写分离

分库分表结合读写分离是MySQL减压的一种措施，主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作，大大减轻了数据库的运行负担。

缓存

对于一些表数据，几乎不会去修改新增的，可以考虑放入redis里面

考虑引入缓存集群，从服务器读取数据如果也遇到性能瓶颈，一直加从服务器成本也高，所以在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。


服务器配置优化

升级服务器，CPU，内存，磁盘都升级


参考文章：

www.cnblogs.com/butterfly10…


avatar
文章
阅读
粉丝