深入浅出MySQL之优化
最近经常问别人MySQL优化,发现大家对这块熟悉,但是不够了解。因此反思总结下,MySQL优化,会有哪些方面呢?本文将其分成应用优化,查询缓存优化,内存管理优化,并发参数调整和锁问题,以及常用MySQL技巧
- 应用优化
在实际生产环境中,由于数据库本身的性能局限,就必须要对 前台的应用进行一些优化,来降低数据库的访问压力。
-
- 使用连接池
对于访问数据库来说,建立连接的代价是比较昂贵的,因为我们频繁的创建关闭连接,是比较耗费资源的,我们有 必要建立 数据库连接池,以提高访问的性能。
-
- 减少MySQL的访问
避免对数据进行重复检索,比如查一次能获取所需字段,就不要分多次查询,减少对数据库的无用重复请求。
增加cache层
我们在应用中可以增加缓存来减轻数据库负担,缓存层有多种,也有很多实现方式。因此可以部分数据从数据库中抽取出来放到应用端以文本方式存储, 或者使用框架(Mybatis, Hibernate)提供的一 级缓存/二级缓存,或者使用redis数据库来缓存数据。
-
- 负载均衡
负载均衡是应用中使用非常普遍的一种优化方法,它的机制就是利用某种均衡算法,将固定的负载量分布到不同的服务器上, 以此来降低单台服务器的负载,达到优化的效果。
利用MySQL复制分流查询。通过MySQL的主从复制,实现读写分离,使增删改操作走主节点,查询操作走从节点,从而可以降低单台服务器的 读写压力。
采用分布式数据库架构。分布式数据库架构适合大数据量、负载高的情况,它有良好的拓展性和高可用性。通过在多台服务器之间分布数 据,可以实现在多台服务器之间的负载均衡,提高访问效率。
- MySQL中查询缓存优化
- 开启Mysql的查询缓存,当执行完全相同的SQL语句的时候,服务器就会直接从缓存中读取结果,当数据被修改,之前的缓存会失效,修改比较频繁的表不适合做查询缓存。MySQL的查询缓存默认是关闭的,需要手动配置参数 query_cache_type , 来开启查询缓存。
查询缓存select选项。可以在SELECT语句中指定两个与查询缓存相关的选项 :
SQL_CACHE : 如果查询结果是可缓存的,并且 query_cache_type 系统变量的值为ON或 DEMAND ,则缓存查询结果 。
SQL_NO_CACHE : 服务器不使用查询缓存。它既不检查查询缓存,也不检查结果是否已缓存,也不缓存查询结果。
例子:
查询缓存失效有下列几种:
- SQL语句不一致。
- 查询语句有些不确定时,不会缓存。如:now(),uuid(),current_date()等部分函数。
- 不使用任何表查询。
- 查询mysql,information_schema或者performance_schema数据库中的表,不会走缓存。
- 在存储过程,触发器或者事件主体中执行查询。
- 如果表更改,则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。
- 内存管理优化
MySQL内存优化原则:
1) 将尽量多的内存分配给MySQL做缓存,但要给操作系统和其他程序预留足够内存。
2) MyISAM 存储引擎的数据文件读取依赖于操作系统自身的IO缓存,因此,如果有MyISAM表,就要预留更多的 内存给操作系统做IO缓存。
3) 排序区、连接区等缓存是分配给每个数据库会话(session)专用的,其默认值的设置要根据最大连接数合理 分配,如果设置太大,不但浪费资源,而且在并发连接较高时会导致物理内存耗尽。
InnoDB内存优化:
nnodb用一块内存区做IO缓存池,该缓存池不仅用来缓存innodb的索引块,而且也用来缓存innodb的数据块。
Innodb_buffer_pool_size
该变量决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下,innodb_buffer_pool_size 的值越大,缓存命中率越高,访问InnoDB表需要的磁盘I/O 就越少,性能也就越高。
Innodb_log_buffer_size
决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size的大小,可以避免innodb在事务提交前就执行不必要的日志写入磁盘操作。
- 并发参数调整和锁问题
从实现上来说,MySQL Server是多线程结构,包括后台线程和客户服务线程。多线程可以有效利用服务器资源,提高数据库的并发性能。在Mysql中,控制并发连接和线程的主要参数包括 max_connections、back_log、 thread_cache_size、table_open_cahce。
-
- max_connections
采用max_connections 控制允许连接到MySQL数据库的最大数量,默认值是 151。如果状态变量 connection_errors_max_connections 不为零,并且一直增长,则说明不断有连接请求因数据库连接数已达到允许最大值而失败,这是可以考虑增大max_connections 的值。
Mysql 最大可支持的连接数,取决于很多因素,包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度,期望的响应时间等。在Linux 平台下,性能好的服务器,支持 500-1000 个连接不是难事,需要根据服务器性能进行评估设定。
-
- back_log
back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时, 新来的请求将会被存在堆栈中,以等待某一连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过 back_log,将不被授予连接资源,将会报错。5.6.6 版本之前默认值为 50 , 之后的版本默认为 50 +(max_connections / 5), 但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求。可以考虑适当增大back_log 的值。
-
- table_open_cache
该参数用来控制所有SQL语句执行线程可打开表缓存的数量, 而在执行SQL语句时,每一个SQL执行线程至少要打开 1个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 :
max_connections x N ;
-
- thread_cache_size
为了加快连接数据库的速度,MySQL 会缓存一定数量的客户服务线程以备重用,通过参数 thread_cache_size 可 控制 MySQL 缓存客户服务线程的数量。
-
- innodb_lock_wait_timeout
该参数是用来设置InnoDB 事务等待行锁的时间,默认值是50ms,可以根据需要进行动态设置。对于需要快速反馈的业务系统来说,可以将行锁的等待时间调小,以避免事务长时间挂起;对于后台运行的批量处理程序来说,可以将行锁的等待时间调,以避免发生大的回滚操作。
- 锁问题
MySQL中的锁要详细了解,篇幅较大,本节大致概过。
锁是计算机协调多个进程或线程并发访问某一资源的机制(避免争抢)。在数据库中,除传统的计算资源(如 CPU、RAM、I/O 等)的争用以外,数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题,锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说,锁对数据库而言显得尤其重要,也更加复杂。
锁分类:
从对数据操作的粒度分 :
1) 表锁:操作时,会锁定整个表。
2) 行锁:操作时,会锁定当前操作行。
从对数据操作的类型分:
- 读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响。
- 写锁(排它锁):当前操作没有完成之前,它会阻断其他写锁和读锁。
- Innodb行锁
行锁特点 :
偏向InnoDB 存储引擎,开销大,加锁慢;会出现死锁;锁定粒度最小,发生锁冲突的概率最低,并发度 也最高。InnoDB 与 MyISAM 的最大不同有两点:一是支持事务;二是采用了行级锁。
InnoDB 实现了以下两种类型的行锁。
共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他 锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行 读取和修改。对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X); 对于普通SELECT语句,InnoDB不会加任何锁;
-
- 锁概述
InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是 在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM 相比就会有比较明显的优势。
但是,InnoDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不 能比MyISAM高,甚至可能会更差。
优化建议:
1)尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级为表锁。
2)合理设计索引,尽量缩小锁的范围。
3)尽可能减少索引条件,及索引范围,避免间隙锁。
4)尽量控制事务大小,减少锁定资源量和时间长度。
5)尽可使用低级别事务隔离(但是需要业务层面满足需求)。
6. 常用MySQL技巧
SQL的执行顺序。举例如下:
编写顺序:
ELECT DISTINCT
<select list>
FROM
<left_table> <join_type> JOIN
<right_table> ON <join_condition> WHERE
<where_condition>
GROUP BY
<group_by_list>
HAVING
<having_condition>
ORDER BY
<order_by_condition>
LIMIT
<limit_params>
执行顺序:
FROM <left_table>
ON <join_condition> <join_type>
JOIN <right_table>
WHERE <where_condition>
GROUP BY <group_by_list>
HAVING <having_condition>
SELECT DISTINCT <select list>
ORDER BY <order_by_condition>
LIMIT <limit_params>
正则表达式的运用,以及常用的函数,如数字函数,字符串函数和日期函数等。
总结:本篇文章大致说到一些工作中常常用到的优化技巧,可做参考,更细致请关注公众号,后续陆续推送。