索引设计的基本原则
选择唯一性索引
唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。
例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话,可能存在同名现象,从而降低查询速度。
主键索引和唯一键索引,在查询中使用是效率最高的。
注意:如果非要在唯一性低的建立索引 ,可以考虑基于多个列建立联合索引来降低重复
为经常需要排序、分组和联合操作的字段建立索引
经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。
为常作为查询条件的字段建立索引
如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。
注:如果经常作为条件的列,重复值特别多,可以建立联合索引。
尽量使用前缀来索引
如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。
限制索引的数目
索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。
尽量使用数据量少的索引
如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。
删除不再使用或者很少使用的索引
表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。
开发规范
没有查询条件,或者查询条件没有建立索引
select * from tab; -- 全表扫描。
select * from tab where 1=1;
这类查询是非常有问题的。全表扫描非常慢,其次基本用户不会有这个需求,对服务器也是灾难性的,比如有几千万行的数据,要是全表扫描…非常可怕
改:换成用有索引的列所谓查询条件,避免全表扫描
查询结果集是原表中的大部分数据,应该是30%以上。 查询的结果集,超过了总数行数30%,优化器觉得就没有必要走索引了。
假如:tab表 id,name id:1-100w ,id列有索引
select * from tab where id>50000;
改:
- 如果业务允许,可以使用limit控制。
- 尽量不要在MySQL存放这个数据了。放到Redis里面。
索引本身失效,统计数据不真实
引有自我维护的能力。
对于表内容变化比较频繁的情况下,有可能会出现索引失效。
改:重建索引
查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,*,/,! 等)
错误的例子:select * from test where id-1=9;
正确的例子:select * from test where id=10;
隐式转换导致索引失效
这一点应当引起重视.也是开发中经常会犯的错误.
由于表的字段tu_mdn
定义为varchar2(20)
,但在查询时把该字段作为number
类型以where
条件传给数据库,
这样会导致索引失效.
错误的例子:select * from test where tu_mdn=13333333333;
正确的例子:select * from test where tu_mdn=‘13333333333’;
<> ,not in 不走索引
单独的>,<,in 有可能走,也有可能不走,和结果集有关,尽量结合业务添加limit
,尽量改成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 “%_” 百分号在最前面不走
可以用百分号 但是不能再最前面使用百分号
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '31%'
走range索引扫描
EXPLAIN SELECT * FROM teltab WHERE telnum LIKE '%110'
不走索引
复合索引
比如有个表,索引为a,b,c三列的复合索引
这时候查询where
条件为abc,ab,a,ac,这些走全部索引或者部分索引。而bc,c,b这些就不走索引