MySQL优化(DBA级优化)

作者: 老汉健身 | 来源:发表于2018-06-15 15:18 被阅读291次

1.数据库优化的可以从如下几个方面着手:

1.>sql语句和索引.

2.>数据库表结构.

3.>系统配置.

4.>硬件

2.使用MySQL的慢查询日志对效率有问题的sql进行监控

1.>查看慢查询日志是否开启:show variables like 'slow_query_log'

2.>根据查看出来的日志存放路径,开启慢查询日志set global slow_query_log_file='/var/lib/mysql/mylinux-slow.log' (file后面的地址可以通过show variables like '%log%';获取)

3.>开启没有用索引的查询记录在日志中:set global log_queries_not_using_indexes=on;

4.>设置最长的查询时间,如果超过该时间,则被记录到日志中.set global long_query_time=1;

3.SQL及索引优化.

3.1MySQL自带的慢查询日志分析工具:

mysqldumpslow -h 可以查看相关参数.

mysqldumpslow -t 3 /var/lib/mysql/mylinux-slow.log | more 查看前3条性能比较差的sql.

3.2专业的慢查询分析工具:pt-query-digest.

3.3使用该工具进行查看慢查询日志:pt-query-digest /var/lib/mysql/mylinux-slow.log | more 查看详情.

4.通过慢查询日志发现有问题的SQL

5.SQL优化

5.1 max和count查询的优化

explain select max(box_lunch_time) from cart_personal /G (竖着显示) 如果查询数据较多,比较慢的话,可以为box_lunch_time做索引,eg:

create index idxboxlunchtime on cart_personal(box_lunch_time);

count函数中值得注意的: count*会把空值也都计算进去,而count id则不会

5.2子查询优化

当采用子查询效率上不如连接查询时,将子查询改为连接查询,但有一点需要值得注意,就是连接查询不会屏蔽一对多时出现数据重复的情况,此时如果业务需要,可以采用distinct关键字来处理.

eg: select * from t where t.id in (select t1.tid from t1);

可以用: select t.id from t join t1 on t.tid =t1.tid;代替,当t1中的tid有两条相同的数据时,那么第一条查询语句查询出来的结果包只含1条数据,而第二条查询语句能查出2条数据,此时可以改为:

select distinct t.id from t join t1 on t.tid =t1.tid; 这样查询结果就与第一条sql查询结果一致了!

5.3group by 优化

优化前:

优化后:

5.4 limit查询优化

优化前:

优化步骤一:

在order by操作中,尽量使用主键或者索引列来进行order by,这样效率高.

优化步骤二:

如果数据量比较大时,Limit50000就要扫描500000次,IO比较大,因此需要进一步优化.值得注意的是,在这种情况下,id必须是连续的,顺序增长的,如果Id不连续,需要添加一列,idindex,保证它是自增且连续的,为它添加索引,即可.

6.索引优化

6.1如何选择合适的列建立索引?

1.在where从句,group by从句,order by从句 on从句中出现的列建立索引

2.索引字段越小越好

3.离散度大的列放到联合索引的前面.(如何判断离散度? 可以使用count语句,比如两个字段staff_id和customer_id,可以使用select count distinct(staff_id), count distinct(customer_id) from payment; 查询结果中对应的数字越大,说明离散度越大)