layout: post
title: Mysql性能优化
date: 2018-05-13
tags: [“Mariadb”,”软件服务”]
一、通过mysql缓存进行优化
1、缓存原理
热点数据放在内存中优化性能（cache缓存读数据、buffers缓冲写数据，达到一定量级触发一次写磁盘）
查询缓存（query cache）原理：
缓存select操作或预处理查询的结果集和sql语句，当有新的select语句或预处理查询语句请求，先去查询缓存，判断是否存在可用的记录集，判断标准：与缓存的sql语句（对sql语句做hash运算，对比hash值），是否完全一样，区分大小写。
优缺点：
不需要对sql语句做任何解析和执行，当然语法解析必须通过在先，直接从query cache中获得查询结果，提高查询性能。查询缓存的判断规则，不够智能，也即提高了查询的使用门槛，降低其效率；
有一些查询是不能够被缓存的：
select语句中有sql_no_cache参数；now()函数;LOCK insharemode、for update语句；查询特定列级别的查询语句不会被缓存。
2、查询缓存的服务器变量：
query_cache_min_res_unit: 查询缓存中内存块的最小分配单位，默认4k，较小值会减少浪费，但会导致更频繁的内存分配操作，较大值会带来浪费，会导致碎片过多，内存不足
query_cache_limit：单个查询结果能缓存的最大值，默认为1M，对于查询结果过大而无法缓存的语句，建议使用 SQL_NO_CACHE
query_cache_size：查询缓存总共可用的内存空间；单位字节，必须是1024的整数倍，最小值40KB，低于此值有警报
query_cache_wlock_invalidate：如果某表被其它的会话锁定，是否仍然可以从查询缓存中返回结果， 默认值为OFF，表示可以在表被其它会话锁定的场景中继续从缓存返回数据；ON则表示不允许
query_cache_type: 取值为ON, OFF, DEMAND
https://dev.mysql.com/doc/refman/5.7/en/query-cache-configuration.html
SELECT语句的缓存控制
SQL_CACHE: 显式指定存储查询结果于缓存之中
SQL_NO_CACHE: 显式查询结果不予缓存
query_cache_type参数变量：
query_cache_type的值为OFF或0时，查询缓存功能关闭
query_cache_type的值为ON或1时，查询缓存功能打开，SELECT的结果符合缓存条件即会缓存，否则，不予缓存，显式指定SQL_NO_CACHE，不予缓存，此为默认值
query_cache_type的值为DEMAND或2时，查询缓存功能按需进行，显式指定SQL_CACHE的SELECT语句才会缓存；其它均不予缓存
3、查询缓存的状态变量：
SHOW GLOBAL STATUS LIKE ‘Qcache%’;
Qcache_free_blocks：处于空闲状态 Query Cache中内存 Block 数
Qcache_free_memory：处于空闲状态的 Query Cache 内存总量
Qcache_hits：Query Cache 命中次数
Qcache_inserts：向 Query Cache 中插入新的 Query Cache 的次数，即没有命中的次数
Qcache_lowmem_prunes：当 Query Cache 内存容量不够，需要删除老的 Query Cache 以给新的 Cache 对象使用的次数
Qcache_not_cached：没有被 Cache 的 SQL 数，包括无法被Cache 的 SQL 以及由于 query_cache_type 设置的不会被 Cache的 SQL语句
Qcache_queries_in_cache：在 Query Cache 中的 SQL 数量
Qcache_total_blocks：Query Cache 中总的 Block
4、命中率和内存使用率估算
query_cache_min_res_unit ≈(query_cache_size - Qcache_free_memory) / Qcache_queries_in_cache
查询缓存命中率 ≈ (Qcache_hits - Qcache_inserts) / Qcache_hits 100%
查询缓存内存使用率 ≈ (query_cache_size - qcache_free_memory) / query_cache_size 100%
二、Innodb存储引擎的缓存
1、InnoDB存储引擎的缓冲池:
通常InnoDB存储引擎缓冲池的命中不应该小于99%，查看相关状态变量：
show global status like ‘innodb%read%’\G
Innodb_buffer_pool_reads: 表示从物理磁盘读取页的次数
Innodb_buffer_pool_read_ahead: 预读的次数
Innodb_buffer_pool_read_ahead_evicted: 预读页，但是没有读取就从缓冲池中被替换的页数量，一般用来判断预读的效率
Innodb_buffer_pool_read_requests: 从缓冲池中读取页次数
Innodb_data_read: 总共读入的字节数
Innodb_data_reads: 发起读取请求的次数，每次读取可能需要读取多个页
2、InnoDB缓冲池命中率计算：
Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests+Innodb_buffer_pool_read_ahead+Innodb_buffer_pool_reads)
3、更改innodb_buffer_pool_dump_pct变量的值，如果使用InnoDB作为内存数据库时，想保证所有的数据都在内存驻留，并且可以在不读取磁盘的情况下访问，就要将它设为100。
说明：只转储每个缓冲池中最热的N％，默认为100，直到MariaDB 10.2.1。由于MariaDB 10.2.2，随着对innodb_buffer_pool_dump_at_shutdown和innodb_buffer_pool_load_at_startup的更改，默认为25％。
三、使用索引
索引是特殊数据结构：定义在查找时作为查找条件的字段，优点：提高查询速度，缺点：占用额外空间，影响插入速度，索引实现在存储引擎
1、Innodb索引
索引实现在存储引擎
索引类型：
聚簇（集）索引、非聚簇索引：数据是否与索引存储在一起
主键索引、辅助索引
稠密索引、稀疏索引：是否索引了每一个数据项
B+ TREE、HASH、R TREE
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高
InnoDB支持聚集索引和B+TREE索引
B+ Tree索引：顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
索引优点：
索引可以降低服务需要扫描的数据量，减少了IO次数
索引可以帮助服务器避免排序和使用临时表
索引可以帮助将随机I/O转为顺序I/O
