索引设计的基本原则

索引设计的基本原则

选择唯一性索引

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。
如果使用姓名的话，可能存在同名现象，从而降低查询速度。
主键索引和唯一键索引，在查询中使用是效率最高的。
注意：如果非要在唯一性低的建立索引 ，可以考虑基于多个列建立联合索引来降低重复

为经常需要排序、分组和联合操作的字段建立索引

经常需要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这些就不走索引