数据库常见性能问题调优
1.死锁
1.查看死锁，⼀般来说使⽤ show processlist
2.重点关注state和info列
3.重点状态 locked，如果是长时间locked 则有可能锁住
4.Copying to tmp table：由于临时结果集⼤于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。IO和CPU可能会飙升，表⽰数据量和查询相当多和复杂.索引等问题。
5.Waiting for net ，reading from net，writing to net：长时间出现，⼀般是⽹络数据量⼤，检查⽹卡。或者把数据流量压缩，⼤数据拆⼩给应⽤处理
2.SQL优化
主要通过索引，慢查询-mysqldumpslow-explain执⾏计划，查看分析慢SQL
主要解决⽅案
建⽴合适的索引
SQL语句结构调整
⼤SQL语句拆分，由应⽤程序分担压⼒
调整参数
重点关注
type
表⽰MySQL在表中到所需⾏的⽅式，⼜称“访问类型”
all、index、range、ref、eq_ref、const，system、null
key
显⽰MySQL在查询中实际使⽤的索引，若没有使⽤索引，显⽰为NULL
rows
表⽰mysql根据表统计信息以及索引选⽤情况，估算到所需记录需要读取的⾏数
extra
• 如果是Only index，这意味着信息只⽤索引就到了
• 如果是where used，就是使⽤上了where限制。
• 如果是impossible where 表⽰⽤不着where，⼀般就是没查出来啥。
• 如果此信息显⽰Using filesort或者Using temporary的话会很吃⼒，WHERE和ORDER BY的索引经常⽆法兼顾，如果按照WHERE 来确定索引，那么在ORDER BY时，就必然会引起Using filesort，这就要看是先过滤再排序划算，还是先排序再过滤划算。
• 这⾥我们只需要关⼼是否存在using filesort或using temporary即可。
主要分析
using filesort
1.排序时⽆法使⽤到索引时，就会出现这个。常见于order by和group by语句中
2.所以主要考虑优化索引和sql结构解决
using temporary
1.为了解决查询，MySQL需要创建⼀个临时表来容纳结果。典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY ⼦句时
2.如果说进⾏filesort时，sort buffer不⾜，则会调⽤temporary来处理
3.所以主要考虑修改索引buffer和排序buffer 查询cache ，减少查询字段等⽅式解决
则有如下优化⽅向：
1. 加⼤ max_length_for_sort_data 参数的设置
2. 去掉不必要的返回字段
3. 增⼤ sort_buffer_size 参数设置（session变量，建议不要超过4M）
4.设置较⼤的key_buffer_size和query_cache_size的值（全局参数）
using filesort和using temporary同时出现时：
1.⼀般先优化using filesort
2.当using filesort⽆法避免时，调整参数优化using temporary
当type为all或者rows字段很⼤时：
3.通常通过优化sql语句或者索引来完成
using index条件：
说明：即使⽤索引对数据进⾏排序，不需要进⾏回表。取出满⾜过滤条件作为排序条件的字段，以及可以直接定位到⾏数据的⾏指针信息，在 Sort Buffer 中进⾏实际的排序操作，然后利⽤排好序的数据根据⾏指针信息返回表中取得客户端请求的其他字段的数据，再返回给客户端.
1.select 返回选择的字段包含在索引中
查看mysql索引
2.只有当ORDER BY中所有的列必须包含在相同的索引，并且索引的顺序和order by⼦句中的顺序完全
⼀致，并且所有列的排序⽅向（升序或者降序）⼀样才有，（混合使⽤ASC模式和DESC模式则不使⽤索引）
3.where 语句与ORDER BY语句组合满⾜最左前缀
4.如果查询联接了多个表，只有在order by⼦句的所有列引⽤的是第⼀个表的列（驱动表）才可以
using filesort情况：
说明：⽆法使⽤索引进⾏排序，需要回表取出所有数据在sort buffer中进⾏排序。MySQL需要将数据在内存中进⾏排序，所使⽤的内存区域也就是我们通过sort_buffer_size 系统变量所设置的排序区。这个排序区是每个Thread 独享的，所以说可能在同⼀时刻在MySQL 中可能存在多个 sort buffer 内存区域
1）  where语句与order by语句，使⽤了不同的索引
2）检查的⾏数过多，且没有使⽤覆盖索引
3）  ORDER BY中的列不包含在相同的索引，也就是使⽤了不同的索引
4）对索引列同时使⽤了ASC和DESC
5）  where语句或者ORDER BY语句中索引列使⽤了表达式，包括函数表达式
6）  where 语句与ORDER BY语句组合满⾜最左前缀，但where语句中使⽤了条件查询。虽然where与order by构成了索引最左有缀的条件，但是where⼦句中使⽤的是条件查询。
7）当使⽤left join，使⽤右边的表字段排序
3.参数优化
增⼤连接数：
1.Max_used_connections / max_connections 值在85%左右
2.max_connections⼀般设置500-1000
3.典型报错MySQL: ERROR 1040: Too many connections
调整超时时间：
wait_timeout
interactive_timeout
默认28800秒 8⼩时，2个参数需要⼀起改
典型报错
现象：当应⽤程序和数据库建⽴连接时，如果超过了8个⼩时，应⽤程序不去访问数据库，数据库就会出现断掉连接的现象。这时再次访问就会抛出异常
调整⽇志级别：
innodb_flush_log_at_trx_commit
表⽰⽇志记录级别
0      log thread 每隔1秒钟会将log buffer中的数据写⼊到⽂件，还通知⽂件系统进⾏同步flush操作，保证⽂件写⼊物理⽂件
1      每次事务结束，log thread都会写⼊，并通知⽂件系统flush
2      每次事务结束，log thread都会写⼊到⽂件系统，但这只是调⽤了操作系统的⽂件写⼊，没有调⽤flush操作，⽂件系统什么时候flush写⼊磁盘⽂件，不由log thread控制
所以，1安全性最好，2其次，0最低。性能正好相反，安全性越⾼越是牺牲性能
innodb_log_buffer_size
为尚未执⾏的事务（等待中）提供的缓存
默认值1MB，单位MB
判断 show variables like 'innodb_log_waits' 如果不是0，则考虑增加innodb_log_buffer_size
调整各类缓冲缓存⼤⼩：
加⼤buffer缓冲池⼤⼩  innodb_buffer_pool_size
show variables like 'innodb_buffer_pool_size';
缓冲池是存放数据和索引的地⽅，加⼤这个值，减少IO操作，使数据操作是在内存中⽽不是硬盘
典型的值是5-6GB(8GB内存)，20-25GB(32GB内存)，100-120GB(128GB内存)。
注意单位是MB
增⼤索引缓存区⼤⼩    key_buffer_size
MyISAM表中的索引缓存
show variables like 'key_buffer_size';
查看key_buffer_size参数设置的⼤⼩，单位B
计算索引未命中缓存的概率:
• key_cache_miss_rate ＝ Key_reads / Key_read_requests * 100%
• 需要的值可以从该语句获取：show global status like 'key_read%';
• key_cache_miss_rate在0.1%以下都很好。如果key_cache_miss_rate在0.01%以下的话，⽽key_buffer_size分配的过多，可以适当减少。
调整事务等待缓存    innodb_log_buffer_size
为尚未执⾏的事务（等待中）提供的缓存
默认值1MB，单位MB
判断 show variables like 'innodb_log_waits' 如果不是0，则考虑增加innodb_log_buffer_size
设置最⼤查询长度加⼤ max_length_for_sort_data 参数的设置
增加查询缓存区⼤⼩增⼤ sort_buffer_size 参数设置
增加查询cache⼤⼩      query_cache_size
减少SQL中using temporary
1. 加⼤ max_length_for_sort_data 参数的设置
2. 去掉不必要的返回字段
3. 增⼤ sort_buffer_size 参数设置（session变量，建议不要超过4M）
4.设置较⼤的key_buffer_size和query_cache_size的值（全局参数）
4.存储引擎优化
如果使⽤MyISAM的数据库，造成⼤量死锁慢查询，尽量改⽤innodb数据库
看你的mysql现在已提供什么存储引擎:
mysql> show engines;
看你的mysql当前默认的存储引擎:
mysql> show variables like '%storage_engine%';
你要看某个表⽤了什么引擎(在显⽰结果⾥参数engine后⾯的就表⽰该表当前⽤的存储引擎):
mysql> show create table 表名;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。