MySQL运维—索引(二)

一、面试题
题目意思:我们公司业务慢,请你从数据库的角度分析原因
1,mysql出现性能问题,我总结有两种情况:
1)应急性的慢!突然夯住
应急情况:数据库hang(卡了,资源耗尽)
处理过程:
(l)show processlist; 获取到导致数据库hang的语句
(2)Explain分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

2)一段时间慢:
(1)记录忄曼日,分析slowlog
(2)explain分析SQL的执行计划,有没有走索引,索引的类型情况
(3)建索引,改语句

图片[1]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

图片[2]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

二、索引优化刚才压力测试的语句

1、查看查询情况,走的全表扫描

图片[3]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

2、创建索引

图片[4]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

3、执行结果对比,显而易见

图片[5]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

三、索引建立原则

1、建表时一定要有主键,如果相关列不可以作为主,做一个无关列

2、选择唯一性素引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可很快的确定某个学生的信息
如果使用姓名的话,可能存在同名现象,从而降低查询速度。

主键索引和唯一键索引,在查询中使用是效率最高的。

  select count(*) from world.city:
  select count(distinct countrycode)from world.city;
  select count(distinct countrycode,population) from world.city;

注意:如果重复值较多,可以考虑采用联合索引

3、为经常需要where、ORDERBY、GROUPBY,join in 等操作的字段,排序操作会浪费很多时间。
如果为其建立索引,可以有效地避免排序操作。

4、为常作为ere查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。
为这样的字段建立索引,可以提高整个表的查询速度。
4.1经常查询
4.2列值的重复值少(业务层面调整)
注:如果经常作为条件的列,重复值特别多,可以建立联合索引

5、尽量使用前缀来素引
如果索引字段的值很长,最好使用值的前缀来索引。

—以上的是重点关注的,以下是能保证则保证的—-

6、限制索引的数目
素引的数目不是越多越好。每个素引都需要占用磁盘空间,素引越多,需要的磁盘空间就越大。
修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。

7、删除不再使用或者很少硬的素引(percona tootkit)
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理
员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

8、大表加索引,要在业务不繁忙期间操作

9、少在经常更新值的列上建索引
建索引原则
(1)必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2)经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
(3)最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4)列值长度较长的索引列,我们建议使用前缀索引,
(5)降低索引条目,一方面不要创建没用索引,不常使用的索引清理,perconatoolkit
(6)索引维护要避开业务繁忙期

四、不走索引的情况
1、不走索引的情况(开发规范)
重点关注:

(1)没有查询条件,或者查询条件没有建立索引

    select * from tab;全表扫描:
    select * from tab where1=1;

在业务数据库中,特别是数据量比较大的表:

是没有全表扫描这种需求。
(2)对用户查看是非常痛苦的:
(3)《对服务器来讲毁灭性的0
(4)select*fromtab,

SQL改写成以下语句:

  select * from tab order by price limit 10      需要在price列上建立索引
    setect * from tab where name='zhangsan'        name列没有索引

改:
1)换成有索引的列作为查询条件
2)将name列建立索引

2、查询结果集是原表中的大部分数据,应该是25%以上。
查询的结果集,超过了总数行数25‰优化器觉得就没有必要走索引了。
假如:tab表文id,name id:1-100w,id列有素引

      setect * from tab where id>50000;

如果业务允许,可以使用lim文t控制。
怎么改写?
结合业务判断,有没有更好的方式:如果没有更好的改写方案
尽量不要在mysql放这个数据了。放到redis里面。

3、索引本身失效,统计数据不真实
索引有自我维护的能力。
对于表内容变化比较频繁的情况下,有可能会出现索引夫效。

4、查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,
例子:

错误的例子:setect * from test where id-1=9;
正确的例子:select * from test where id=10;

算术运算
函数运算
子查询
5|隐式转换导致索.引.夫效《这一点应当讲起重视杰也是开发中经常会犯的错、
这样会导致索引失效,错误的例子:

图片[6]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引(二) - 众客华禹众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

6、<>,not in不走索引

EXPLAIN SELECT * FROM tettab WHERE telnum <> '10';
EXPLAIN SELECT * FROM teltab WHERE telnum NOT IN('110','119');
select * from tab where telnum <> '1555555';
explain select * from tab where telnum <> '1555555';

单独的>,<,in有可能走,也有可能不走,和结果集有关,尽量结合业务添加limit
or或in尽量改成union

EXPLAIN SELECT * FROM tettab WHERE tetnum IN('110','119');
改写成:
EXPLAIN SELECT * FROM teltab WHERE telnum = '110'
UNION ALL
SELECT * FROM tettab WHERE telnum='119';

7、like “%_”百分号在最前面不走

EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'; 走range索引扫描
EXPLAIN SELECT * FROM tettab WHERE tetnum LIKE '%110'; 不走索引

%linux%类的搜索需求,可以使用etasticsearch专门做搜索服务的数据库产品

8、单独引用联合索引里韭第一位置的素引列,作为条件查询时不走索引,
列子:
复合索引:

DROP TABLE t1
CREATE TABL tl(id INT,NAME VARCHAR(20),age INT,sex ENUM('m','f'),money INT);
ALTER TABLE tl ADD INDEX tl_idx(money,age,sex);
DESC tl
SHOW INDEX FROM tl

走索引的情况测试:

EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND age=30;
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE money=30 AND sex='m'; --->部分走索引
不走索引的:
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE age=20
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE age=30 AND sex='m';
EXPLAIN SELECT NAME,age,sex,money FROM tl WHERE sex='m';

总之:任何一个不以第一索引列做为查询条件的setect,都是不走索引的

 

 

© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片

    暂无评论内容