MySQL优化(DBA级优化）
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; 查询结果中对应的数字越大,说明离散度越大)
6.2索引优化sql的方法
1.重复索引
2.冗余索引
3.如何查询出重复索引和冗余索引?
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME,
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME
4.查找重复/冗余索引的工具:pt-duplicate-key-checker 工具检查重复及冗余索引
下载地址：http://www.percona.com/redir/downloads/percona-toolkit/percona-toolkit-1.0.1.tar.gz
安装方法：
先安装依赖:yum install -y perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-Digest-MD5 perl-ExtUtils-MakeMaker
然后进入解压后的目录 执行:
perl Makefile.PL；make；make install
使用:pt-duplicate-key-checker --host=localhost --user=root --password=123456 --database=ordering 进行查询,根据查询出的结果和工具给出的建议进一步优化索引.
4.索引维护
pt-index-usage --host=localhost --user=root --password=123456 /var/lib/mysql/mylinux-slow.log
删除那些不经常使用的索引.
作者：老汉健身
链接：https://www.jianshu.com/p/4623692d6567
來源：简书
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。