数据库 之 Mysql优化的问题

nawawa001

　　1  概述
　　数据库优化的方法有四个：
　　索引策略，通过explain来判断索引的有效性，移除多余索引，保留有效的索引
　　单机缓存，mysql内键的缓存
　　旁挂缓存，如使用redis或者memcached，由程序自行决定是否将结果进行缓存
　　修改相关参数，提升mysql服务器的性能
　　本文主要介绍mysql的单机缓存以及相关参数的介绍
　　2  单机缓存
　　mysql的内键缓存机制，query  cache(查询缓存),mysql缓存功能是自带的，但是需要启用，为了足够高效命中，mysql在内存中开辟缓存空间，缓存中的数据是kv值，kv值也是缓存提高效率的原因之一。
　　这里的问题是要把什么当做键,键是hash值，所以需要将查询结果做hash运算。Mysql的缓存是非预先分配的，可能会造成碎片，降低效率
　　为了提高缓存命中率，要使得程序员使用同一种分隔来写语句，如都是用大写，或者某些关键字都是小写，原因是mysql会将查询语句做hash运算，大小写不一样的话，hash值就不同。如果hash值不同，将不能被命中。通常是把关键词大小。
　　mysql的查询缓存是单机的，缓存是放在本地主机上。考虑到缓存命中率，在前端调度的时候，可以基于语句做路由，同一语句调度到同一主机上。但是proxysql没有这种调度能力，haproxy可以实现七层协议调度，但是mysql不支持七层协议，mysql是列表语句，因此不能通过haproxy来做这种调度
　　查询缓存：
　　缓存：k/v
　　key：查询语句的hash值
　　value：查询语句的执行结果
　　如何判断缓存是否命中：
　　通过查询语句的哈希值判断：哈希值考虑的因素包括
　　查询本身、要查询数据库、客户端使用的协议版本、...
　　SELECT Name FROM students WHERE StuID=3;
　　Select Name From students where StuID=3;
　　哪些查询可能不会被缓存？如下添加不变缓存
　　1 查询语句中包含UDF(User-Defined Functions)
　　2 存储函数
　　3 用户自定义变量
　　4 临时表
　　5 mysql系统表或者是包含列级别权限的查询
　　6 有着不确定结果值的函数(now())；
　　7 select 语句，如果已经启用按需缓存，那么在select 语句中，[SQL_CACHE | SQL_NO_CACHE]这两个选项则明确表明该语句需要缓存或者不缓存，如明确使用sql_no_cache则不缓存
　　查看缓存相关的选项
　　MariaDB [(none)]> show global variables like '%cache%';
　　查询缓存相关的服务器变量介绍：
　　query_cache_limit：能够缓存的最大查询结果；（单语句结果集大小上限）
　　有着较大结果集的语句，显式使用SQL_NO_CACHE，以避免先缓存再移出；默认是1M
　　query_cache_min_res_unit：内存块的最小分配单位；缓存过小的查询结果集会浪费内存空间；
　　较小的值会减少空间浪费，但会导致更频繁地内存分配及回收操作；
　　较大值的会带来空间浪费；
　　query_cache_size：查询缓存空间的总共可用的大小；单位是字节，必须是1024的整数倍；0表示没有启用 。这个数字建议不要随便调整，因为每次调整这个值，之前的缓存项都会被删除，缓存需要重新生成
　　如设置为64M,命令如下
　　MariaDB [(none)]> set @@global.query_cache_size=67108864;
　　query_cache_strip_comments
　　query_cache_type：缓存功能启用与否；
　　ON：启用；能缓存的数据就缓存，处理以上提到的7类不能缓存的查询
　　OFF：禁用；
　　DEMAND：按需缓存，仅缓存SELECT语句中带SQL_CACHE的查询结果；如果确定结果大小比缓存空间大，明确不缓存，即no_cache。
　　query_cache_wlock_invalidate：如果某表被其它连接锁定，是否仍然可以从查询缓存中返回查询结果；默认为OFF，表示可以；ON则表示不可以；默认是OFF(允许的,双重否定invalidate OFF所以是启用的，读多写少的环境，缓存基本是有效的)
　　查看状态变量：计数器，查询名字或者插入的次数。
　　mysql> SHOW GLOBAL STATUS LIKE 'Qcache%';
　　+-------------------------+----------+
　　| Variable_name           | Value    |
　　+-------------------------+----------+
　　| Qcache_free_blocks      | 1 空闲的块数       |
　　| Qcache_free_memory      | 16759688 |查询缓存尚且剩余的空间
　　| Qcache_hits             | 0  表示命中缓存的次数    |
　　| Qcache_inserts          | 0  表示查询插入的次数    |
　　| Qcache_lowmem_prunes    | 0  这个值很大，说明缓存空间太小      |
　　| Qcache_not_cached       | 0   本来要放入缓存空间但是实际没有被缓存的数量     |
　　| Qcache_queries_in_cache | 0   在缓存系统中查询的个数     |
　　| Qcache_total_blocks     | 1    查询总的块数    |
　　+-------------------------+----------+
　　评估select缓存命中率公式如下：
　　Qcache_hits/Com_select
　　当读写操作差不多的时候，建议关闭该功能，但是如果是大量查询,即读多写少的环境中，建议启用缓存
　　注意，第一次查询时生成缓存，接下去的查询才会命中缓存
　　查看Com_select的总数
　　MariaDB [sunny]> show global status like 'Com%select%';
　　3  修改相关参数
　　Innodb存储引擎相关的参数查看命令如下
　　MariaDB [sunny]> show global variables like 'innodb%';
　　一般不建议使用默认参数，
　　优化相关参数，一般以下的参数不使用默认数值运行，而是要根据实际情况调整后才能使用服务，相关参数介绍如下
　　innodb_buffer_pool_size这个参数的相关解释如下：
　　innodb的缓存池的大小，通常用来缓存索引，数据，插入数据时的缓冲区。innodb_buffer_pool_size这个参数一般要根据实际情况调大，不支持动态修改，需要修改配置文件，默认是128M,这个值一般要调大，提高性能的优化，这个是占用内存空间，这个根据服务器是专用或者组合使用的，来确定这个数值调整的大小。
　　如果innodb引擎为主的主机，建议innodb_buffer_pool_size设置40%~60%的内存空间。
　　如果msyql是专用主机，内存空间为32G以上，innodb_buffer_pool_size一般建议设置为70--80%的内存空间。这个值对mysql性能提升很有帮助。但是不能设置太大，如剩余的内存空间不够系统正常运转，可能会带来更多的问题，所以这个值要适当设置。
　　如果数据集本身较小，可以根据数据变化幅度及规划的在线时长来设定合理的innodb_buffer_pool_size值，如比预估的目标值略大。
　　5.7版本后innodb_buffer_pool_size可以动态调整，但是，建议将innodb_buffer_pool_size写入配置文件中，而且建议不要在系统繁忙时调整该值
　　修改配置如下
　　[root@node71 ~]#vim /etc/my.cnf.d/server.cnf
　　[server]
　　innodb_buffer_pool_size = 512M
　　然后重启mysql服务后生效
　　innodb_buffer_pool_instances：buffer_pool的区段（实例）数量， 表示把内存空间大小切割成几个空间字段,锁定的单位会变小，可以提高mysql运行效率，但是数量建议不要太多
　　innodb_log_files_in_group : 一组的日志文件数量，至少两个
　　innodb_log_file_size：日志文件大小，不能太大，默认是5M;根据实际情况来调整。建议调大，一般可以设定为50--100M
　　innodb_flush_logs_at_trx_commit:事务提交刷写磁盘的设置参数，值为0 1 2
　　0:log_buffer(内存中)每秒1次同步到log_file中，且同时会进行log file 到data file的同步操作，0最多丢失1s的事务
　　1：每次提交时，log buffer同步到log file,,同时进行log file 到data file的同步操作，1最多丢失1次事务
　　2:  每次提交时，log buffer同步到log file,,但是不会同时进行log file 到data file的同步操作，由mysql的线程自行决定什么时候进行刷新数据。2的性能最高，如果事务的安全性不是很大，建议设置为2
　　建议：关闭autocommit,而后将此值innodb_flush_logs_at_trx_commit设置为1或2；
　　innodb_file_per_table:innodb的诸多高级特性都依赖此参数，建议开启
　　innodb_file_io_threads:文件读写的io线程数，如果CPU核心够多，建议调大，默认是4。这个值是要根据并发量和CPU核心数适量调整
　　innodb_open_files:innodb可打开的文件数量上限，按需调整此值
　　innodb_flush_method：innodb刷写磁盘的方式
　　innodb_thread_concurrency:innodb的线程并发数，即内核级可以同时运行的线程数，一般是CPU核心数量*2
　　skip_name_resolve:忽略主机名称解析，网络优化相关，禁止调用外部dns进行名称解析
　　max_connections:最大并发连接数