索引设计的基本原则

矫情吗；* 2022-04-23 10:10 271阅读 0赞

# 索引设计的基本原则 #

### 选择唯一性索引 ###

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

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

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