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

MySQL 优化

上一篇:EXTJS 4.2 资料 控件之Window窗体相关属性的用法


下一篇:win7鼠标莫名失去焦点解决