MySQL从入门到应用 第九天

1. MySQL查询缓存优化

    1.1 查询缓存的有关配置

SHOW VARIABLES LIKE 'have_query_cache';    --  查看当前mysql是否支持查询缓存；

参数值	含义
OFF 或 0	查询缓存功能关闭
ON 或 1	查询缓存功能打开， SELECT 的结果符合缓存条件即会缓存，否则，不予缓存，显式指定 SQL_NO_CACHE，不予缓存
DEMAND 或 2	查询缓存功能按需进行，显式指定 SQL_CACHE 的 SELECT 语句才会缓存；其它均不予缓存

SHOW VARIABLES LIKE 'query_cache_type';    -- 查看当前是否开启查询缓存；

SHOW VARIABLES LIKE 'query_cache_size';     --  查看查询缓存的最大容量；

SHOW STATUS LIKE 'Qcache%';     --  查看缓存的状态变量；

缓存状态变量的含义：

参数	含义
Qcache_free_blocks	查询缓存中的可用内存块数
Qcache_free_memory	查询缓存的可用内存量
Qcache_hits	查询缓存命中数
Qcache_inserts	添加到查询缓存的查询数
Qcache_lowmen_prunes	由于内存不足而从查询缓存中删除的查询数
Qcache_not_cached	非缓存查询的数量（由于 query_cache_type 设置而无法缓存或未缓存）
Qcache_queries_in_cache	查询缓存中注册的查询数
Qcache_total_blocks	查询缓存中的块总数

    1.2 查询缓存的失效

        1.2.1 SQL 语句不一致的情况， 要想命中查询缓存，查询的SQL语句必须一致；

        1.2.2 当查询语句中有一些不确定的时，则不会缓存。如 ： now() , current_date() , curdate() , curtime() , rand() , uuid() , user() , database() 。

        1.2.3 不使用任何表查询语句。 eg:select 'A';

        1.2.4 查询 mysql， information_schema或 performance_schema 数据库中的表时，不会走查询缓存。

        1.2.5 在存储的函数，触发器或事件的主体内执行的查询。

        1.2.6 如果表更改，则使用该表的所有高速缓存查询都将变为无效并从高速缓存中删除。这包括使用 MERGE 映射到已更改表的表的查询。

        1.2.7 查询sql中指定不使用缓存。eg: SELECT SQL_NO_CACHE id, name FROM customer;

 2. 两种存储引擎的内存优化

    2.1 MyISAM的内存优化

参数	含义
key_buffffer_size	决定 MyISAM 索引块缓存区的大小，直接影响到 MyISAM表的存取效率。对于一般 MyISAM 数据库，建议至少将 1/4 可用内存分配给 key_buffffer_size 。
read_buffffer_size	如果需要经常顺序扫描 myisam 表，可以通过增大 read_buffffer_size 的值来改善性能。但需要注意的是它是每个 session 独占的，如果默认值设置太大，就会造成内存浪费
read_rnd_buffffer_size	对于需要做排序的 myisam 表的查询，如带有 order by 子句的 sql ，适当增加 read_rnd_buffffer_size 的值，可以改善此类的sql性能。但需要注意的是它是每个 session 独占的，如果默认值设置太大，就会造成内存浪费

 

     2.2 InnoDB的内存优化

        innodb用一块内存区做IO缓存池，该缓存池不仅用来缓存innodb的索引块，而且也用来缓存innodb的数据块。

参数	含义
innodb_buffffer_pool_size	决定了 innodb 存储引擎表数据和索引数据的最大缓存区大小。在保证操作系统及其他程序有足够内存可用的情况下，innodb_buffffer_pool_size 的值越大，缓存命中率越高，访问 InnoDB 表需要的磁盘 I/O 就越少，性能也就越高。
innodb_log_buffffer_size	决定了 innodb 重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加 innodb_log_buffffer_size 的大小，可以避免innodb 在事务提交前就执行不必要的日志写入磁盘操作。

 

 

 

 

3. MySQL 并发参数

    3.1 最大连接数 max_connections

        max_connections 控制允许连接到MySQL数据库的最大数量，默认值是 151。如果状态变量 connection_errors_max_connections 不为零，并且一直增长，则说明不断有连接请求因数据库连接数已达到允许最大值而失败，这是可以考虑增大max_connections 的值。

        Mysql 最大可支持的连接数，取决于很多因素，包括给定操作系统平台的线程库的质量、内存大小、每个连接的负荷、CPU的处理速度，期望的响应时间等。在Linux 平台下，性能好的服务器，支持 500-1000 个连接不是难事，需要根据服务器性能进行评估设定。

    3.2 请求缓存栈大小 back_log

        back_log 参数控制MySQL监听TCP端口时设置的积压请求栈大小。如果MySql的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，该堆栈的数量即back_log，如果等待连接的数量超过back_log，将不被授予连接资源，将会报错。5.6.6 版本之前默认值为 50 ， 之后的版本默认为 50 +（max_connections / 5）， 但最大不超过900。如果需要数据库在较短的时间内处理大量连接请求， 可以考虑适当增大back_log 的值。

    3.3 table_open_cache

        该参数用来控制所有SQL语句执行线程可打开表缓存的数量， 而在执行SQL语句时，每一个SQL执行线程至少要打 开 1 个表缓存。该参数的值应该根据设置的最大连接数 max_connections 以及每个连接执行关联查询中涉及的表的最大数量来设定 ：max_connections x N ；

    3.4 连接池 thread_cache_size

        为了加快连接数据库的速度，MySQL 会缓存一定数量的客户服务线程以备重用，通过参数 thread_cache_size 可控制 MySQL 缓存客户服务线程的数量。

    3.5 行锁等待时长 innodb_lock_wait_timeout

        该参数是用来设置InnoDB 事务等待行锁的时间，默认值是50ms ， 可以根据需要进行动态设置。对于需要快速反 馈的业务系统来说，可以将行锁的等待时间调小，以避免事务长时间挂起； 对于后台运行的批量处理程序来说，可以将行锁的等待时间调大， 以避免发生大的回滚操作。