性能优化

  • 1.应用优化
  • 1.1 使用数据库连接池
  • 1.2 减少对MySQL的访问
  • 1.2.1 避免数据重复检索
  • 1.2.2 增加cache层
  • 1.3负载均衡
  • 1.3.1 MySQL读写分离
  • 1.3.2 采用分布式数据库架构
  • 2.MySQL内存管理及优化
  • 2.1 内存优化原则
  • 2.2 MyISAM内存优化
  • 2.3 InnoDB内存优化
  • 3. MySQL并发参数调整
  • 4. MySQL锁
  • 4.1 锁概述
  • 4.2 锁分类
  • 4.3 MySQL锁


1.MySql高级1–索引2.MySQL高级2–SQL优化3.MySQL高级3–性能优化

1.应用优化

1.1 使用数据库连接池

使用数据库连接池，避免数据库连接频繁的创建和销毁，进而减少资源的消耗，提高数据库的访问性能。

1.2 减少对MySQL的访问
1.2.1 避免数据重复检索

能一次检索获取到结果，就不要进行俩次检索，减少对数据库的无用重请求。

1.2.2 增加cache层

增加缓存层来减轻数据库负担。可以使用orm框架提供的一级/二级缓存，或者使用redis来缓存数据。

1.3负载均衡
1.3.1 MySQL读写分离

通过MySQL的主从复制实现读写分离，增删改走主服务器 ，读走从服务器，降低单台MySQL服务器的读写压力。

1.3.2 采用分布式数据库架构

可以利用mycat等数据库中间件对数据库进行分库分表。

2.MySQL内存管理及优化

2.1 内存优化原则
  1. 在保证其它应用内存预留足够的情况下，将尽量多的内存分配给mysql做缓存。
  2. 对于MyISAM的表，要预留更多的内存给操作系统做IO缓存，因为MyISAM存储引擎数据文件的读取依赖于操作系统自身的IO缓存。
  3. 排序区和连接区的缓存是给每个数据库会话专用的,其默认值需要根据最大连接数合理分配，如过分配过大不但会消耗资源，在高并发下会使物理机内存资源耗尽。
2.2 MyISAM内存优化

MyISAM存储引擎使用key_buffer缓存索引块，加速索引的读写速度。MyISAM的数据块完全依赖于操作系统的IO缓存。

  • key_buffer_size : 决定MyISAM索引块缓存区的大小，直接影响到MyISAM表的存取效率。
  • read_buffer_size : 可以适当增大该参数值的大小来改善经常被顺序扫描的MyISAM表的性能。
  • read_rnd_buffer_size : 对于需要做排序的MyISAM表的查询，可以增大该参数的值，来提升sql性能。
2.3 InnoDB内存优化

InnoDB用一块内存区做IO缓存池，该缓存池不仅缓存InnoDB索引块，还缓存InnoDB的数据块。

  • innodb_buffer_pool_size : 该参数决定了InnoDB表数据和索引数据的最大缓存区大小，该参数越大，缓存命中率越高，磁盘IO越少，性能也就越高。
  • innodb_log_buffer_size ：决定了重做日志缓存的大小，增加该参数的大小，可以避免在事务提交之前执行不必要的日志写入操作（磁盘IO），提高性能。

3. MySQL并发参数调整

MySQL是多线程结构，包括后台线程和客户服务线程。多线程可以有效利用服务资源，提高数据库并发性能。

  • max_connections : 控制连接到mysql数据库的请求最大数量，默认是151。如果状态变量connection_errors_max_connections不为零，并且一直增大，则说明不断有请求因请求连接数已经达到最大而连接失败，此时可以增大max_connections的值。
  • back_log : 控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySQL的请求数量超过max_connections，新来的MySQL请求会先放在back_log堆栈中，来等待已连接资源的释放。当等待的请求数量超过back_log的允许值，将不授予连接资源而报错。5.6.6版本之前back_log默认值是50，之后版本默认值是50+(max_connections/5),最大不超过900 。如果需要在短时间内处理大量请求，可以适当增大back_log的值。
  • table_open_cache : 该参数用来控制所有sql语句执行线程可以打开表缓存的数量。由于每一个sql执行线程至少打开一个表缓存，所以该参数值的设置要根据最大连接数（max_connectios）以及每个连接执行关联查询中涉及到表的最大数量来设定。
  • thread_cache_size : 控制MySQL缓存客户服务线程的数量。
  • innodb_lock_wait_timeout : 该参数用来设置innodb事务获取行锁的最大等待时间，默认值是50ms。对于需要快速返回结果的业务系统来说可以适当调小该参数的值，以避免事务被挂起，对于后台运行的批处理程序来说，可以适当调大该参数的值，以避免事务发生大的回滚操作。

4. MySQL锁

4.1 锁概述

锁是计算机协调多个进程和多个线程并发访问某一共享资源的机制。

4.2 锁分类

数据库操作的粒度：

  • 表锁 ：操作时，会锁住整张表。
  • 行锁 ：操作时，会锁住操作行。

数据的操作类型：

  • 共享锁（读锁）：对同一份数据，多个读操作可以同时进行。
  • 排它锁（写锁）：当前写操作执行完成前，其它读写操作将会因获取不到读写锁而被阻断。
4.3 MySQL锁

在MySQL数据库中，不同的存储引擎有不同的锁机制。

mysql双击高性能 mysql高性能优化_MySQL

MySQL三种锁的特性：

mysql双击高性能 mysql高性能优化_MySQL_02


表级锁使用于以查询为主，有少量按索引条件并发更新数据的系统；行级锁适合大量按索引条件进行并发更新少量不同数据的系统。