MySQL 优化
开启mysql慢查询
show variables like 'slow_query_log'
set global slow_query_log_file='/home/payzulla/data/mysql-slow.log '
set global log_queries_not_using_index=on;
set global long_query_time=1
1. mysqldumpslow 是mysql自带的。
mysqldumpslow -t 3 /home/payzulla/data/mysql-slow.log | more
2. 工具 pt-query-digest
pt-query-digest /home/payzulla/data/mysql-slow.log | more
1)注意查询次数多且查询占用时间长的sql. 通常情况下 pt-query-digest 分析的前几个查询。
2)IO大的sql, 需要注意pt-query-digest分析中的Rows Send(实际发送的行数), Rows Examine (扫描的行数)项。
扫描行数/发送行数 比例越大说明,扫描了很多行,需查看索引是否命中等。
3.优化Limit 查询 :
使用有索引的列或主键进行order by 操作, 因为这样就不会进行 file sort(即文件操作)
4. 用 explain 查询执行计划。 注意:避免大量扫描。
5.如何选择建立索引合适的列。
是在where, group by, order by, on 等从句中出现的列上建立。
6.索引字段越小越好,因为存储是以页为单位的,索引字段小,那页存储的数据多,这样IO操作会变少。(性能消耗一般在IO层面是最常见的,计算机组成原理里都学过,CPU和IO执行频率差距巨大。此外数据总线上也会有或多或少的消耗)
7.离散度大的列放在联合索引的前面。(即唯一值更多的列)
8.注意避免冗余索引(指:多个索引的前辍列相同,或者联合索引中包含了主键)
注意:因为InnoDB 会在索引中自动加上主键,因此联合索引中不能有主键。
9.工具分析索引:
1)重复索引:pt-duplicate-key-checker -uroot -p '13456' -h 127.0.0.1
2)查询索引使用:pt-index-usage
MySQL 配置优化:
1.innodb_buffer_pool_size 最重要的参数之一。
若只有innodb 表, 则推荐使用总内存的75%
2.innodb_buffer_pool_instance
3.innodb_log_buffer_size 一般不用设置太大。(因为最长也会每秒刷新一次)
4.innodb_flush_log_at_trx_commit 关键参数
对IO 效率影响大,默认值为1, 可取值范围:0,1,2 三个值。
一般建议设置:2, 即每秒提交缓冲。当然若对数据安全要求高,则使用1。(值为1时,每次事务提交时会把log buffer的数据写入log file,且flush即刷到磁盘)
5.innodb_read_io_threads 默认为4
6.innodb_write_io_threads
7.innodb_file_per_table 关键参数
控制Innodb 每个表使用独立的表空间,默认为OFF, 也就是所有的表都会建立在空闲表空间。
推荐:ON, 单独表空间,有助于并发 和 回收空间等。
数据库优化
1.表结构上的优化(如字段的使用)
2.表拆分-垂直拆分(解决了表过于宽的问题,即减少列),水平拆分-(减少数据量,一般用Hash 取模运算,根据不同的值存到不同的数据库中)
3.数据分片维度:
1)固定路由位:如user_id 后一位若是1, 则指定到A库,若是2指定到B库
2)时间自增分片: 如 日期从1-30 号的数据在A库中 等类似分片方式。