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库中 等类似分片方式。