走向DBA之不走索引的情况
2019-06-22 本文已影响0人
国王12
前文介绍了合理的设定和使用索引的好处，本篇介绍查询时不走索引的几种情况
一、没有查询条件，或者查询条件没有建立索引造成全表扫描
select * from tab; 没有查询条件造成全表扫描
select * from tab where qq=480070779; qq列没有设置索引造成全表扫描
1.1全表扫描的影响
在业务数据库中，特别是数据量较大的表，是完全没有全表扫描这种需求的。
1.对用户查看是非常痛苦的
2.对服务器来讲也是毁灭性的
1.2全表扫描解决方法：
1.select * from tab; 没有条件的查询
SQL语句更改为：
select * from tab order by price limit 10; 根据实际业务需要，对某列设定索引并排序，并取范围值，列如前十行数据
2.select * from tab where qq=480070779; qq列没有做索引
SQL语句更改为：
select * from tab where name='zhangsan'; 把经常作为条件匹配的列设定索引，或尽量把有索引的列作为匹配条件
二、查询结果急是原表数据的25%以上
select * from tab where id>500000; 表总数据行1000000行
2.1大量数据查询影响：
1.当一次性读取数据为原表数据的25%及以上，优化器就会认为没有必要走索引了
2.对服务器是毁灭性的
2.2解决方法：
1.在业务允许的情况下，可以使用limit控制
2.尽量不要在mysql里存放这类数据，放在redis里面
三、索引本身失效，统计数据不真实
3.1索引为什么会失效呢？
虽然索引有自我维护（更新）功能，但是速度并不是特别快（时时），因此当你插入一条新的数据，索引还未来得及更新时，你便要查询这条数据，
那么就会导致查询到的数据不真实，优化器发现数据不真实，就再也不会使用该索引了。
3.2是什么导致索引失效？
对于表内容变化比较频繁的情况下，就会导致索引失效
3.3索引失效的现象
有一条select语句平时查询很快，突然有一天就很慢，原因可能有两种
1.索引失效
2.DML 锁冲突
3.4解决方法
删除重建改列的索引，让其重新生效
四、where查询条件匹配，要加单引号。否则可能不走索引
加引号：
mysql> explain select * from tab where telnum='1333333';
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
| 1 | SIMPLE | tab | ref | inx_tel | inx_tel | 63 | const | 1 | Using index condition |
+----+-------------+-------+------+---------------+---------+---------+-------+------+-----------------------+
不加引号：
mysql> explain select * from tab where telnum=1333333;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | tab | ALL | inx_tel | NULL | NULL | NULL | 2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
可以明显看到，不加引号，虽然possible_keys显示可能走的索引时inx_tel，但实际key列显示并没有走索引
4.1不加引号导致不走索引的原因：
隐式转换
4.2解决方法：
谁写的SQL语句，锤他一顿，让他加上单引号！
五、查询条件使用函数在索引列上，或者对索引列进行运算(+，-，*，/，! 等)
select * from test where id-1=9; 条件进行了运算
5.1解决方法：
select * from test where id-1=9; 更改为：
select * from test where id=10;
六、辅助索引列出现不等值查询(<> ，not in)
SELECT * FROM teltab WHERE telnum <> '110';
SELECT * FROM teltab WHERE telnum NOT IN ('110','119');
6.1解决方法：
当不等值查询出现在辅助列上，会不走索引
单独的> , < , in 可能走索引，可能不走索引，和结果集有关。尽量结合业务添加limit进行控制
or 或 in 尽量改成union
EXPLAIN SELECT * FROM teltab WHERE telnum IN ('110','119');
改写成：
EXPLAIN SELECT * FROM teltab WHERE telnum='110'
UNION ALL
SELECT * FROM teltab WHERE telnum='119';
七、like "% xxx" 百分号在前边不走索引
select * from tab where telnum like "%110"； 不走索引
7.1解决：
1.模糊匹配尽量不要让%出现在前边
2.%linux%类的搜索需求，可以使用elasticsearch+mongodb 专门做搜索服务的数据库产品
