MySQL索引与SQL语句优化(下)

10.6 生产使用explain命令优化SQL语句流程


1、现场抓慢查询SQL语句并优化方法


1)适合临时紧急救火场景,数据库此时此刻慢。
2)登录数据库后,以间隔超过1秒的间隔连续执行show full processlist;,非交互语句:
        mysql -uroot -poldboy123 -e "show full processlist;"|egrep -vi "sleep"
3)找到慢查询语句,可以执行mysql> kill id;杀死前面一些select语句。
        因为慢查询堵了数据库,mysql> kill 84; 84是ID,kill语句如果是insert,update可能要丢数据(注意)。
4)使用explain语句检查,抓到的慢语句的索引执行情况。
        explain select * from test where name='oldboy'\G
        explain select SQL_NO_CACHE * from test where name='oldboy'\G
5)根据返回结果,对需要建索引的列(where后的条件列、多表连接的列、分组、排序列)建立索引,并核查创建索引效果。
        alter table test add index index_name(name); #提示:最好测试环境执行。
        explain select * from test where name='oldboy'\G
        生产场景,高峰期尽量不要在大表上建立索引,例如:100万+条记录。
6)无论怎么创建索引,MySQL就是不按你的要求使用索引,如何办?
(1)使用use index强制。
(2)1条长SQL语句无法使用索引,能不能变成2条短的SQL语句让它分别使用上索引。
(3)子查询变成join查询
(4)like '%老男孩%' 转移到es集群操作。
(5)对SQL功能拆分和修改,由运维(DBA)和开发交流(确认),共同确定如何改,最终由DBA执行。
(6)调整网站架构,重复没法使用索引的,数据库前端增加redis,
(7)将合适数据迁移到NOSQL(redis),例如:投票、统计、粉丝关注。
提示:
1、中小企业常规数据库超过2秒+即为慢查询。
2、门户级别核心数据库,毫秒级别就定义为慢查询,例如0.5秒。

2、日常慢SQL语句的优化方法:


1)未雨绸缪:重要不紧急:
        超过2秒的SQL语句记录到日志里,然后开启定时任务分析慢查询日志。
        配置参数记录慢查询语句
                long_query_time = 2
                log_queries_not_using_indexes
                log-slow-queries = /data/3306/slow.log
                min_examined_row_limit = 1000
2)按天切割慢查询日志,如果并发太大可以按小时,去重分析后发给大家。
切割慢查询的命令:

[root@db01 ~]# mv /data/3306/slow.log /opt/$(date +%F)_slow.log
[root@db01 ~]# mysqladmin -uroot -poldboy123 flush-logs
[root@MySQL scripts]# cat /server/scripts/cut_slow_log.sh
cd /data/3306/ &&\
/bin/mv slow.log slow.log.$(date +%F)&&\
mysqladmin -uroot -poldboy123 -S /data/3306/mysql.sock flush-log
[root@MySQL scripts]# tail -2 /var/spool/cron/root
#cut mysql slow log
00 00 * * * /bin/sh /server/scripts/cut_slow_log.sh >/dev/null 2>&1


3)使用慢查询日志分析工具分析mysqlsla或pt-query-digest
4)分析结果发给邮件列表(相关人员)
提示:当下流行方案是把慢查询日志收集ELK集群,并展示。


11. MySQL索引优化


11.1 建立索引的原则(DBA运维规范)


(1) 必须要有主键,必须业务无关的列。
(2) 经常做为where条件列、order by、group by、join on、distinct条件建立索引(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引列,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,使用前缀索引.
        select count(distinct left(name,10)) from city; #截取前多少个字符看唯一值情况。
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 索引维护要避开业务繁忙期,8.0后可以在线,但建议用pt-osc。
(7) 联合索引最左原则

11.2 不走索引的情况(开发规范)


11.2.1 没有查询条件,或者查询条件没有建立索引
        select * from t1 ;
        select * from t1 where id=1001 or 1=1; ##SQL注入

11.2.2 有索引不走
        查询结果集是原表中的大部分数据,应该是15-25%以上。
        查询的结果集,超过了总数行数25%,优化器觉得就没有必要走索引了。
        MySQL的预读功能有关。
        解决:
        1.可以通过精确查找范围,达到优化的效果,比如limit。
        2.强制使用索引。

11.2.3 索引本身失效,统计信息不真实(过旧)
索引有自我维护的能力。
对于表内容变化比较频繁的情况下,有可能会出现索引失效。
解决:
1.立即更新索引统计信息表
mysql> ANALYZE TABLE world.city;
2. 删除重建索引。

11.2.4 查询条件使用函数在索引列或者对索引列进行运算,运算包括(+,-,*,/,! 等)
索引列进行运算例子:
错误的例子:select * from test where id-1=9;
正确的例子:select * from test where id=10;
算术运算、函数运算、子查询 不走索引。

11.2.5 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误.
mysql> desc select * from b where telnum=110; ##相当于计算了
mysql> desc select * from b where telnum='110';
11.2.6 <> ,not in 不走索引(辅助索引)
11.2.7 like "%_" 百分号在最前面不走索引



12.SQL语句优化

0)从招聘的时候确定DBA技能,不行不招.
1)对开发人员进行数据库知识培训,确保对数据库应用更规范更专业。
2)参与开发项目中数据库的设计,确保从源头减少不规范使用数据库情况。
3)对开发人员加KPI绩效考核,如果代码上线有多少个慢SQL,扣钱.

4)索引优化
a)白名单机制--百度,项目开发,DBA参与,减少上线后的慢SQL数量。
b)记录慢SQL,或者用ELK集群收集分析展示。
c)使用慢查询日志分析工具mysqlsla或pt-query-digest分析慢sql。
d)每天晚上0点定时分析慢查询,发到核心开发,DBA分析,及高级运维,
CTO的邮箱里,或用ELK收集,展示提醒开发。
e)优化流程:DBA分析给出优化建议-->核心开发确认更改-->DBA线上操作处理。
f)定期使用pt-duplicate-key-checker检查并删除重复的索引,也可用sqlyog工具。
g)定期使用pt-index-usage工具检查并删除使用频率很低的索引;
h)使用pt-online-schema-change来完成大表的ONLINE DDL需求;
i)有时候MySQL会使用错误的索引,可使用USE INDEX强制。
j)使用explain及set profile查看SQL语句执行计划,并根据计划进行优化。

5)大的复杂的SQL语句拆分成多个小的SQL语句。
6)数据库是存储数据的地方,不是计算数据的地方。
对数据计算,应用类处理,都要拿到前端应用解决。禁止在数据库上处理。
7)搜索功能,like '%老男孩%',不要用MySQL数据库,采用ES或其他工具。
8)使用连接(JOIN)来代替子查询(Sub-Queries)
9)避免在整个表上使用count(*),它可能锁住整张表。
10)多表联接查询时,关联字段类型尽量一致,并且都要有索引;
11)在WHERE子句中使用UNION代替子查询。
12)多表连接查询时,把结果集小的表(注意,这里是指过滤后的结果集,不一定是全表数据量小的)作为驱动表
13)多表联接并且有排序时,排序字段必须是驱动表里的,否则排序列无法用到索引;
14)where条件中尽量去掉"IN"、"OR" "<>"
15)类似分页功能的SQL,建议先用主键关联,然后返回结果集,效率会高很多;

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值