MySQL的一些优化措施

lae_2
208 阅读7分钟

连接配置优化

服务端配置

1.增加可用连接数，修改环境变量max_connections,默认是151个连接数
2.即时释放不活动的连接，客户端默认超时时间是28800秒(8小时),可以修改wait_timeout将值调小一点

客户端优化

客户端能做的就是尽量减少和服务端建立连接的次数，已经建立的连接尽量复用。最典型的方案就是使用连接池。

常见的连接池有：DBCP、C3P0、Druid(阿里)、Hikari。前两者已经很少使用，后两者如日中天。

不要盲目增加连接池的大小，建议设置为比较通用的：CPU核心数*2+1，即服务器CPU核心数是4，连接池设置为9

架构优化

使用缓存

使用诸如redis之类的缓存技术，将实效性不强，但数量大或查询复杂的数据放入缓存系统中，减轻数据库压力提高查询效率。

读写分离

主从复制

单台服务器承担的并发量有限，针对这个问题，可以同时使用多个数据库服务器，将其中一台设置为主节点(master)，其余节点设置为子节点(slave)。用户写数据只往master节点写，读请求分摊到各个slave节点上。

很多开发者不满master-slave这种词汇，发起了更名运动。MySQL也会逐渐停用master、slave术语，转用source和replica替代。

集群

将master及slave组成的小团体命名，即是集群。

集群的问题：

多节点如何保持数据的一致性？

MySQL通过binlog这个核心组件实现主从复制功能。master节点将所有的写操作记录到binlog中，salve节点会有专门的I/O线程读取master节点的binlog，将写操作同步到当前所在的salve节点。

分库分表

读写分离的架构减轻了主数据库服务器压力，但随着业务数据的增多，某张表数据量急剧增加，单表查询性能会大幅下降。读写分离无法解决这种问题，毕竟所有节点的数据一样，单表性能差即所有节点性能都差。为了解决这个问题，引入了分库分表的概念。

垂直分库分表

垂直分库

在单体数据库的基础上垂直切几刀，按照业务拆分为不同数据库

垂直分表

即在单表的基础上垂直切分，将单表字段拆成若干个表，需要根据具体业务来进行判断，通常将经常使用的字段(热字段)分成一个表，不常用或不立即使用的字段(冷字段)分成一个表，提高查询速度。

水平分库分表

水平分库

将单个数据库水平切分，往往伴随着水平分表

水平分表

将单表数据按一定规则(行话叫分片规则)保存到多个数据表上，将数据横切一刀或几刀。

垂直分主要是为了减轻并发压力，水平分主要为了解决存储的瓶颈。最好不要直接进行水平拆分，先考虑缓存、索引等措施优化

消息队列

通常情况，用户的请求会直接访问数据库，如果同一时刻在线用户数量非常庞大，很有可能压垮数据库。这种情况可以通过使用消息队列降低数据库的压力，不管同时有多少个用户请求，先存入消息队列，然后系统有条有序的从消息队列中消费请求。

SQL分析与优化

慢查询

优化器也无法优化的SQL语句

可通过slow_query_log表示当前慢查询日志是否开启，slow_query_log_file表示慢查询日志的保存位置，long_query指定多长时间算慢查询。

1.改配置文件my.cnf，此种修改方式系统重启后依然有效

# 是否开启慢查询日志
​
slow_query_log=ON
​
long_query_time=2
​
slow_query_log_file=/var/lib/mysql/slow.log

2.动态修改参数（重启后失效）

mysql> set @@global.slow_query_log=1;
​
Query OK, 0 rows affected (0.06 sec)
​
mysql> set @@global.long_query_time=2;
​
Query OK, 0 rows affected (0.00 sec)
慢日志查询的工具mysqldumpslow

查看运行中的线程

可以运行show full processlist查看MySQL中运行的所有线程，查看其状态和运行时间，kill掉有问题的

查看服务器运行状态

使用SHOW STATUS查看MySQL服务器运行状态，有session和global两种作用域，一般使用like+通配符进行过滤。

查看存储引擎运行状态

使用SHOW ENGINE展示存储引擎的当前运行信息，包括事务持有的表锁、行锁信息；事务的锁等待情况，线程信号量等待，文件IO请求，Buffer pool统计信息等数据。

如：SHOW ENGINE INNODB STATUS；

EXPLAIN执行计划

通过慢查询知道哪些SQL语句执行慢，为什么慢？慢在哪里？可以使用执行计划的查询命令EXPLAIN，通过此命令查看SQL执行计划，通常用在SELECT语句上。

SQL与索引优化

SQL优化

sql本身语法没有问题，但是有实现相同目的的更好写法。

  • 小表驱动大表，用join改写子查询，or改成union
  • 连接查询，尽量减少驱动表的记录数，访问被驱动表的成本要尽量低。尽量在被驱动表的连接列上建立索引，降低访问成本。被驱动表的连接列最好是主键或者唯一索引列
  • 大偏移量的limit，先过滤再排序
索引优化

为慢查询创建适当的索引是个非常常见且有效的方法，但是索索引是否被高效使用？

存储引擎与表结构

选择存储引擎

一般情况下我们选择默认的InnoDB，但对数据库性能精益求精的时候，存储引擎的选择也是一个关键的影响因素

  • 查询、插入操作多的业务表，推荐使用MyISAM
  • 临时表使用Memory
  • 并发数量大，更新多的业务选择InnoDB
  • 不知道选啥直接默认

优化字段

字段优化的最终原则是：使用可以正确存储数据的最小数据类型

整数类型

tinyint、smallint、mediumint、int、integer、bigint

是否被删除的标识列，建议选用tinyint

字符类型

不确定长度的字段，肯定要选varchar，但如果字段的长度是固定的，尽量选用char

非空

非空字段尽量设置成NOT NULL，并提供默认值，或使用特殊值代替NULL

因为NULL类型的存储和优化都会存在性能不佳的问题

不要用外键、触发器和视图功能
  1. 降低可读性，检查代码的同时还得查看数据库的代码
  2. 计算的工作应该交给程序，数据库只需要做好存储的工作
  3. 数据的完整性校验应该由开发者完成，而不是依赖于外键，一旦用了外键，测试时删垃圾数据都会变得异常艰难
图片、音频、视频存储

不要直接存储大文件，而是存储大文件的访问地址

大字段拆分和数据冗余

大字段拆分其实就是上述的垂直分表，字段冗余原则上不符合数据库设计范式，但非常利于快速检索。例如合同表中存储客户ID的同时可以冗余存储客户姓名，查询就不需要再额外查询用户姓名了。

业务上的优化

严格来讲，业务上的优化已经不属于MySQL的优化范畴了，但是业务的优化却能有效的减轻数据库访问压力。参考淘宝的案例

  1. 双十一活动，仅几年将预售战线拉长，提前半月就开始，各种定金红包模式层出不穷，这种方法叫预售分流。可以分流客户的服务请求，不必等到双十一凌晨一股脑下单
  2. 双十一的的凌晨想查询当天之外的订单，但是查询失败，甚至支付宝的小鸡口粮都被延迟发放，这是一种降级策略，季节不重要的服务计算资源，用来保证当前最核心的业务。
  3. 双十一极力推荐花呗支付而不是银行卡支付，一方面虽然是提高软件粘性，但是另一方面是，使用阿里内部服务器，访问速度快，而是用银行卡需要调用银行接口，相比之下要慢许多。
avatar
文章
阅读
粉丝