SQL索引建立原则和使用

雨点打透心脏的1/2处 2021-11-10 07:40 962阅读 1赞

之前在网上看到过很多关于mysql联合索引最左前缀匹配的文章，自以为就了解了其原理，发现遗漏了些东西，这里自己整理一下这方面的内容。

# 1 前言 #

SQL索引有两种，聚集索引和非聚集索引  
   
聚集索引存储记录是物理上连续存在，而非聚集索引是逻辑上的连续，物理存储并不连续  
   
字典的拼音查询法就是聚集索引，字典的部首查询就是一个非聚集索引.  
   
聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致  
   
聚集索引一个表只能有一个，而非聚集索引一个表可以存在多个。

# 2 建立索引的原则：     #

1) 定义主键的数据列一定要建立索引。

2) 定义有外键的数据列一定要建立索引。

3) 对于经常查询的数据列最好建立索引。

4) 对于需要在指定范围内的快速或频繁查询的数据列;

5) 经常用在WHERE子句中的数据列。

**6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。**

**    如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。**

7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

8) 对于定义为text、image和bit的数据类型的列不要建立索引。

9) 对于经常存取的列避免建立索引

10) 限制表上的索引数目。**对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。**

索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

11) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。

对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。

因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

## 2.1另外原则 ##

**1.最左前缀匹配原则**

非常重要的原则，mysql会一直向右匹配直到遇到**范围查询(>、<、between、like)就停止匹配**，比如a = 1 and b = 2 and c > 3 and d = 4.  如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

**2.=和in可以乱序**

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

**3.尽量选择区分度高的列作为索引**

区分度的公式是count(distinct col)/count(\*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

**4.索引列不能参与计算**

保持列“干净”，比如from\_unixtime(create\_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create\_time = unix\_timestamp(’2014-05-29’);

**5.尽量的扩展索引，不要新建索引。**

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

**6．选择唯一性索引**

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

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

经常需要**ORDER BY、GROUP BY、DISTINCT和UNION**等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

**8．为常作为查询条件的字段建立索引**

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

**9．限制索引的数目**

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

**10．尽量使用数据量少的索引**

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

**11．尽量使用前缀来索引**

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

**12．删除不再使用或者很少使用的索引**

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

## **2.2 最左前缀匹配原则** ##

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例：  
对列col1、列col2和列col3建一个联合索引

KEY test_col1_col2_col3 on test(col1,col2,col3);

联合索引 test\_col1\_col2\_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。

SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”

上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。

### **注意** ###

索引的字段可以是任意顺序的，如：

SELECT * FROM test WHERE col1=“1” AND clo2=“2”
    SELECT * FROM test WHERE col2=“2” AND clo1=“1”

这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。

有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都包含索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。

## **2.3 为什么要使用联合索引** ##

*  **减少开销**。**建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引**。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
 *  **覆盖索引**。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
 *  **效率高**。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select *from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W*10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w*10% *10% \*10%=1w，效率提升可想而知！

## **引申** ##

对于联合索引(col1,col2,col3)，查询语句SELECT \* FROM test WHERE col2=2;是否能够触发索引？  
大多数人都会说NO，实际上却是YES。  
**原因**：

EXPLAIN SELECT * FROM test WHERE col2=2;
    EXPLAIN SELECT * FROM test WHERE col1=1;

观察上述两个explain结果中的type字段。查询中分别是：

1.  type: index
2.  type: ref

**      index**：这种类型表示mysql会对整个该索引进行扫描。要想用到这种类型的索引，对这个索引并无特别要求，只要是索引，或者某个**联合索引的一部分**，mysql都可能会采用index类型的方式扫描。但是呢，缺点是效率不高，mysql会从索引中的第一个数据一个个的查找到最后一个数据，直到找到符合判断条件的某个索引。所以，上述语句会触发索引。  
**      ref**：这种类型表示mysql会根据特定的算法快速查找到某个符合条件的索引，而不是会对索引中每一个数据都进行一一的扫描判 断，也就是所谓你平常理解的使用索引查询会更快的取出数据。而要想实现这种查找，索引却是有要求的，要实现这种能快速查找的算法，索引就要满足特定的数据结构。简单说，**也就是索引字段的数据必须是有序的，才能实现这种类型的查找，才能利用到索引。**

# 3 聚集索引和非聚集索引 #

聚集索引和非聚集索引的根本区别是数据记录的排列顺序和索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后，从而缩小了搜索范围，对于返回某一范围的数据效果最好。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。  
　　非聚集索引指定了表中记录的逻辑顺序，数据记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B树的结构，但非聚集索引的叶子层顺序并不与实际的数据页相同，而采用指向表中的记录在数据页中位置的方式。非聚集索引比聚集索引层次多，添加记录不会引起数据顺序的重组。在有大量不同数据的列上建立非聚集索引，可以提高数据的查询和修改速度。

在对聚集索引列查询时，聚集索引的速度要比非聚集索引速度快。

在对聚集索引列排序时，聚集索引的速度要比非聚集索引速度快。但是如果数据量比较大时，如10万以上，则二者的速度差别不明显。

## 3.1 聚集索引和非聚集的建立原则 ##

在创建索引时要做到三个适当，即在适当的表上、适当的列上创建适当数量的索引。虽然这可以通过一句话来概括优化的索引的 基本准则，但是要做到这一点的话，需要做出很大的努力。具体的来说，要做到这个三个适当有如下几个要求。

### **3.1.1   ****根据表的大小来创建索引。** ###

虽然给表创建索引，可以提高查询的效率。但是需要注意的是，索引也需要一定的开销的。为此并不是说给所有的表都创建索引，那么就可以提高数据库的性能。这个认识是错误的。给所有的表都创建了索引，那么其反而会给数据库的性能造成负面的影响。因为此时滥用索引的开销可能已经远远大于由此带来的性能方面的收益。所以，数据库管理员首先需要做到，为合适的表来建立索引，而不是为所有的表建立索引。

一般来说，不需要为比较小的表创建索引。因为即使建立了索引，其性能也不会得到很大的改善。相反索引建立的开销，如维护成本等等，要比这个要大。也就是说，付出的要比得到的多，显然违反常理。

另外，就是对于超大的表，也不一定要建立索引。有些表虽然比较大，记录数量非常的多。但是此时为这个表建立索引并一定的合适。对于一些超大的表，建立索引有时候往往不能够达到预计的效果。而且在大表上建立索引，其索引的开销要比普通的表大的多。那么到底是否给大表建立索引呢？主要是看两个方面的内容。首先是需要关注一下，在这张大表中经常需要查询的记录数量。一般来说，如果经常需要查询的数据不超过10%到15%的话，那就没有必要为其建立索引的必要。因为此时建立索引的开销可能要比性能的改善大的多。如果数据库管理员需要得出一个比较精确的结论，那么就需要进行测试分析。

### **3.1.2   ****根据列的特征来创建索引** ###

列的特点不同，索引创建的效果也不同。需要了解为哪些列创建索引可以起到事半功倍的效果。同时也需要了解为哪些列创建索引反而起到的是事倍功半的效果。

索引设置的是否恰当，不仅跟数据库设计架构有关，而且还跟企业的经济业务相关。虽然一开始已经做了索引的优化工作。但是随着后来经济数据的增加，这个索引的效果会越来越打折扣。所以需要隔一段时间，对数据库的索引进行优化。该去掉的去掉，该调整的调整，以提高数据库的性能。

### **3.1.3  ****在一个表上创建多少索引合适** ###

通常来说，表的索引越多，其查询的速度也就越快。但是，表的更新速度则会降低。这主要是因为表的更新同时也是索引的更新。到底在表中创建多少索引合适，就需要在这个更新速度与查询速度之间取得一个均衡点。如对于一些数据仓库或者决策型数据库系统，其主要用来进行查询。相关的记录往往是在数据库初始化的时候导入。此时，设置的索引多一点，可以提高数据库的查询性能。同时因为记录不怎么更新，所以索引比较多的情况下，也不会影响到更新的速度。相反，如果那些表中经常需要更新记录，如一些事务型的应用系统，数据更新操作是家常便饭的事情。此时如果在一张表中建立过多的索引，则会影响到更新的速度。由于更新操作比较频繁，所以对其的负面影响，要比查询效率提升要大的多。此时就需要限制索引的数量，只在一些必要的字段上建立索引。

# 4 索引的不足之处 #

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。  
   
      建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。  
   
      索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

# 5 使用索引时，有以下一些技巧和注意事项：   5.1 索引不会包含有NULL值的列 #

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。

所以我们在数据库设计时不要让字段的默认值为NULL。

## 5.2 使用短索引 ##

对列进行索引，如果可能应该指定一个前缀长度。

例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。

短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

## 5.3 索引列排序 ##

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。

因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

## 5.4 like语句操作 ##

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。

like “%aaa%” 不会使用索引，而like “aaa%”可以使用索引。

## 5.5 不要在列上进行运算 ##

select \* from users where YEAR(adddate)<2007;

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

select \* from users where adddate<‘2007-01-01’;

## 5.6 不使用NOT IN和<>操作 ##

#    7 索引的建立时机 #

一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此，

因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。

因为在以通配符%和\_开头作查询时，MySQL不会使用索引。

# 8 索引的使用 #

## 8.1 建立索引  ##

create \[UNIQUE|FULLTEXT\] index index\_name on tbl\_name (col\_name \[(length)\] \[ASC | DESC\] , …..);

示例：

alter table table\_name ADD INDEX \[index\_name\] (index\_col\_name,...)

CREATE INDEX paywayid\_index ON pay\_order\_trade (paywayid)

## 8.2 组合索引 ##

CREATE INDEX idx\_example ON table1 (col1 ASC, col2 DESC, col3 ASC)

示例：

ALTER TABLE people ADD INDEX lname\_fname\_age (lame,fname,age);

最左前缀：顾名思义，就是最左优先，

上例中我们创建了lname\_fname\_age多列索引,相当于创建了(lname)单列索引，(lname,fname)组合索引以及(lname,fname,age)组合索引。

## 8.3 删除索引  ##

DROP INDEX index\_name ON tbl\_name;

alter table table\_name drop index index\_name;

## 8.4 删除主键(索引)比较特别: ##

alter table t\_b drop primary key;

## 8.5 查询索引(均可)  ##

show index from table\_name;

show keys from table\_name;

desc table\_Name;

# 9查询慢分析 #

## 9.1 回到开始的慢查询 ##

根据最左匹配原则，最开始的sql语句的索引应该是status、operator\_id、type、operate\_time的联合索引；其中status、operator\_id、type的顺序可以颠倒，所以我才会说，把这个表的所有相关查询都找到，会综合分析；比如还有如下查询：

select * from task where status = 0 and type = 12 limit 10;

select count(*) from task where status = 0 ;

那么索引建立成(status,type,operator\_id,operate\_time)就是非常正确的，因为可以覆盖到所有情况。这个就是利用了索引的最左匹配的原则

## 9.2 查询优化神器 - explain命令 ##

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网[explain-output][]，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

## 9.3 慢查询优化基本步骤 ##

1.  先运行看看是否真的很慢，注意设置SQL\_NO\_CACHE
2.  where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
3.  explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
4.  order by limit 形式的sql语句让排序的表优先查
5.  了解业务方使用场景
6.  加索引时参照建索引的几大原则
7.  观察结果，不符合预期继续从0分析
8.

# 索引的优化方法 #

1，何时使用聚簇索引或非聚簇索引：

<table> 
 <tbody> 
 <tr> 
 <td> 动作描述 </td> 
 <td> 使用聚集索引 </td> 
 <td> 使用非聚集索引 </td> 
 </tr> 
 <tr> 
 <td> 列经常被分组排序 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 返回某范围内的数据 </td> 
 <td> 应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 一个或极少不同值 </td> 
 <td> 不应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 小数目的不同值 </td> 
 <td> 应 </td> 
 <td> 不应 </td> 
 </tr> 
 <tr> 
 <td> 大数目的不同值 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 频繁更新的列 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 外键列 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 主键列 </td> 
 <td> 应 </td> 
 <td> 应 </td> 
 </tr> 
 <tr> 
 <td> 频繁修改索引列 </td> 
 <td> 不应 </td> 
 <td> 应 </td> 
 </tr> 
 </tbody> 
</table>

2，索引不会包含有NULL值的列：只要列中包含有NULL值，都将不会被包含在索引中，组合索引中只要有一列有NULL值，那么这一列对于此条组合索引就是无效的。所以我们在数据库设计时，不要让索引字段的默认值为NULL。

3，使用短索引：假设，如果有一个数据类型为CHAR(255)的列，在前10个或20个字符内，绝大部分数据的值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省I/O操作。

4，索引列排序：MySQL查询只使用一个索引，因此如果WHERE子句中已经使用了索引的话，那么ORDER BY中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下，不要使用排序操作；尽量不要包含多个列的排序，如果需要，最好给这些列也创建组合索引。

5，LIKE语句操作：一般情况下，不建议使用LIKE操作；如果非使用不可，如何使用也是一个研究的课题。LIKE "%aaaaa%"不会使用索引，但是LIKE "aaa%"却可以使用索引。

6，不要在索引列上进行运算：在建立索引的原则中，提到了索引列不能进行运算，这里就不再赘述了。

# mysql索引失效情况 #

1、最佳左前缀原则——如果索引了多列，要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。

前提条件：表中已添加复合索引（username,password,age）

分析：该查询缺少username，查询条件复合索引最左侧username缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描

分析：查询条件缺少username，password，查询条件复合索引最左侧username，password缺少，违反了最佳左前缀原则，导致索引失效，变为ALL，全表扫描

分析：该查询只有一个username条件，根据最佳左前缀原则索引能够被使用到，但是是部分使用

2、不在索引列上做任何操作（计算，函数，（自动或者手动）类型装换），会导致索引失效而导致全表扫描

分析：第一个图索引列不使用函数，遵循左前缀原则，能够使用索引。第二张图索引列上使用了函数，即使遵循左前缀原则，索引还是失效

3、存储引擎不能使用索引中范围条件右边的列，范围之后索引失效。（< ,> between and）

分析：图一索引全部使用到。图二索引使用到username和age，但是username是使用索引检索，而age着重索引排序，这时age为范围查找，password索引将失效

4、mysql使用不等于(!= 或者<>)的时候，无法使用索引，会导致索引失效

5、mysql中使用is not null 或者 is null会导致无法使用索引

分析：对username列做了普通索引，查询带is not null，结果索引不生效

6、mysql中like查询是以%开头，索引会失效变成全表扫描，覆盖索引。

分析：对username列做了普通索引，以%开头进行查询，结果索引失效被覆盖

7、mysql中，字符串不加单引号索引会失效。正确写法：select \* from t\_user where username = 'lujin';

8、mysql中，如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

9、如果mysql使用全表扫描要比使用索引快,则不会使用到索引

参考：

https://blog.csdn.net/fly910905/article/details/78690074   
https://tech.meituan.com/2014/06/30/mysql-index.html  
https://segmentfault.com/a/1190000015416513  
https://www.cnblogs.com/Jessy/p/3543063.html  
https://blog.csdn.net/qq\_34258346/article/details/80272198

[explain-output]: http://dev.mysql.com/doc/refman/5.5/en/explain-output.html