索引失效和注意事项总结

小鱼儿 2022-03-17 08:46 583阅读 0赞

[https://blog.csdn.net/hehexiaoxia/article/details/54312130][https_blog.csdn.net_hehexiaoxia_article_details_54312130]

## 索引失效的情况 ##

如果是同样的sql如果在之前能够使用到索引，那么现在使用不到索引，以下几种主要情况:

> 1.  随着表的增长，where条件出来的数据太多，大于15%，使得索引失效（会导致CBO计算走索引花费大于走全表）
> 2.  统计信息失效 需要重新搜集统计信息
> 3.  索引本身失效 需要重建索引

具体情况：

**1.单独引用复合索引里非第一位置的索引列**

> 假如有INDEX(a,b,c)，   
> 当条件为a或a,b或a,b,c时都可以使用索引，   
> 但是当条件为b,c时将不会使用索引。
> 
> **复合索引遵守“最左前缀”原则，即在查询条件中使用了复合索引的第一个字段，索引才会被使用。**因此，在复合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引。

2.**对索引列运算**，运算包括（+、-、\*、/、！、<>、%、**like’%\_’（%放在前面）**、or、in、exist等），导致索引失效。

> 错误的例子：select \* from test where id-1=9;   
> 正确的例子：select \* from test where id=10;   
> **注意！！**   
> mysql sql 中如果使用了 **not in ， not exists ， （<> 不等于 ！=） 这些不走**   
> < 小于 > 大于 <= >= 这个根据实际查询数据来判断，**如果全盘扫描速度比索引速度要快则不走索引** 。

3.**对索引应用内部函数**，这种情况下应该建立基于函数的索引。

> select \* from template t where ROUND(t.logicdb\_id) = 1   
> 此时应该建ROUND(t.logicdb\_id)为索引。

4、**类型错误**，如字段类型为varchar，where条件用number。

> 例：template\_id字段是varchar类型。
> 
> 错误写法：select \* from template t where t.template\_id = 1
> 
> 正确写法：select \* from template t where t.template\_id = ‘1’

5.**如果MySQL预计使用全表扫描要比使用索引快，则不使用索引**   
6.**like的模糊查询以%开头，索引失效**   
7.**索引列没有限制 not null**，索引不存储空值，如果不限制索引列是not null，[oracle][]会认为索引列有可能存在空值，所以不会按照索引计算

--------------------

## 索引注意事项 ##

[https://blog.csdn.net/qq\_16239775/article/details/79665972][https_blog.csdn.net_qq_16239775_article_details_79665972]

**1.索引不会包含有NULL值的列**

**只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。**所以我们在数据库设计时**不要让字段的默认值为NULL。应该用0、一个特殊的值或者一个空串代替空值。**

**2.复合索引**

比如有一条语句是这样的：`select * from users wherearea=’beijing’ and age=22;`如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area,age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该**将最常用作限制条件的列放在最左边，依次递减**。

**3.使用短索引**

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。**短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。**

**4.排序的索引问题**

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

**5.like语句操作**

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。**like“%aaa%” 不会使用索引而like“aaa%”可以使用索引。**

**6.不要在列上进行运算**

select\* from users where YEAR(adddate)

**7.不使用NOT IN操作**

NOT IN操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替

--------------------

# 索引的优缺点 #

**优点：**

> 第一，通过创建**唯一性索引**，可以保证数据库表中每一行**数据的唯一性**。   
> 第二，可以大大**加快 数据的检索速度**，这也是创建索引的最主要的原因。   
> 第三，可以**加速表和表之间的连接**，特别是在实现数据的**参考完整性**方面特别有意义。   
> 第四，在使用分组和排序子句进行数据检索时，同样可以显著**减少查询中分组和排序的时间。**   
> 第五，通过使用索引，可以在查询的过程中，使用**优化隐藏器，提高系统的性能**。

**覆盖索引的好处**

> **如果一个索引包含所有需要的查询的字段的值，直接根据索引的查询结果返回数据，而无需读表，能够极大的提高性能**。因此，可以定义一个让索引包含的额外的列，即使这个列对于索引而言是无用的。

**缺点：**

> 第一，**创建索引和维护索引要耗费时间**，这种时间随着数据量的增加而增加；
> 
> 第二，**索引需要占物理空间**，**除了数据表占数据空间之外，每一个索引还要占一定的物理空间**，如果要建立聚簇索引，那么需要的空间就会更大，如果非聚集索引很多，一旦聚集索引改变，那么所有非聚集索引都会跟着变；
> 
> 第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，一旦一个数据改变，并且改变的列比较多，可能会引起好几个索引跟着改变，这样就**降低了数据的维护速度**。
> 
> 第四、每个索引都有统计信息，索引越多统计信息越多，**过多索引会导致优化器优化过程需要评估的组合增多**。创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。

--------------------

## 如何选择合适的列创建索引 ##

1 在**经常需要搜索的列**上，可以加快搜索的速度；

2 在**作为主键的列**上，**强制该列的唯一性和组织表[中数据][Link 1]的排列结构**；

3 在**经常用在连接的列**上，这些列主要是一些外键，可以**加快连接的速度**；

4 在**经常需要根据范围进行搜索的列上创建索引**，因为索引已经排序，其指定的范围是连续的；这样查询可以利用索引的排序，加快排序查询时间；

5 在**经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度**。当增加索引时，会提高检索性能，但是会降低修改性能

6 **唯一性很差的字段不合适做索引**，如性别

7 **更新频繁的字段不适合**，耗时且影响性能

--------------------

## 索引分类 ##

索引可以分为簇索引和非簇索引

> 簇索引通过重排表中的数据来提高数据的访问速度，   
> 非簇索引则通过维护表中的数据指针来提高数据的索引。

聚簇索引的体系结构：

索引的结构类似于树状结构，树的顶部称为叶级，树的其它部分称为非叶级，树的根部在非叶级中。同样，在聚簇索引中，聚簇索引的叶级和非叶级构成了一个树状结构，索引的最低级是叶级。在聚簇索引中，表中的数据所在的数据页是叶级，在叶级之上的索引页是非叶级，索引数据所在的索引页是非叶级。在聚簇索引中，数据值的顺序总是按照升序排列。应该在表中经常搜索的列或者按照顺序访问的列上创建聚簇索引。

当创建聚簇索引时，应该考虑这些因素：

（1）**每一个表只能有一个聚簇索引**，因为表中数据的物理顺序只能有一个；

（2）**表[中行][Link 2]的物理顺序和索引中行的物理顺序是相同的**，**在创建任何非聚簇索引之前创建聚簇索引**，这是因为聚簇索引改变了表中行的物理顺序，数据行 按照一定的顺序排列，并且自动维护这个顺序；

（3）关键值的唯一性要么使用UNIQUE关键字明确维护，要么由一个内部的唯一标识符明确维护，这些唯一性标识符是系统自己使用的，用户不能访问；

（4）聚簇索引的平均大小大约是数据表的百分之五，但是，实际的聚簇索引的大小常常根据索引列的大小变化而变化；

（5）在索引的创建过程中，SQL Server临时使用当前数据库的磁盘空间，当创建聚簇索引时，需要1.2倍的表空间的大小，因此，一定要**保证有足够的空间来创建聚簇索引**。

--------------------

## 主键约束与主键索引 ##

> 主键约束是一种保持数据完整性的逻辑，它限制表中的记录有相同的主键记录。   
> 在**创建主键约束时，系统自动创建了一个唯一性的聚簇索引**。

虽然，在逻辑上，主键约束是一种重要的结构，但是，**在物理结构上，与主键约束相对应的结构是唯一性的聚簇索引**。换句话说，在物理实现上，不存在主键约束，而只存在唯一性的聚簇索引。同样，在创建唯一性键约束时，也同时创建了索引，这种索引则是唯一性的聚簇索引。因此，当使用约束创建索引时，索引的类型和特征基本上都已经确定了，由用户定制的余地比较小。

当在表上定义主键或者唯一性键约束时，如果表中已经有了使用CREATE INDEX语句创建的标准索引时，那么主键约束或者唯一性键约束创建的索引覆 盖以前创建的标准索引。也就是说，主键约束或者唯一性键约束创建的索引的优先级高于使用CREATE INDEX语句创建的索引。当创建唯一性索引时，应 该认真考虑这些规则：

当在表中创建主键约束或者唯一性键约束时，SQL Server自动创建一个唯一性索引；

如果表中已经包含有数据，那么当创建索引时，SQL Server检查表中已有数据的冗余性；

每当使用插入语句插入数据或者使用修改语句修改数据时，SQL Server检查数据的冗余性：如果有冗余值，那么SQL Server取消该语句的执行，并且返回一个错误消息；确保表中的每一行数据都有一个唯一值，这样可以确保每一个实体都可以唯一确认；

只能在可以保证实体完整性的列上创建唯一性索引。

**复合索引**

索引可以包含一个、两个或更多个列。两个或更多个列上的索引被称作复合索引

*  1

当创建复合索引时，应该考虑这些规则：

> （1）最多可以把16个列合并成一个单独的复合索引，构成复合索引的列的总长度不能超过900字节，也就是说**复合列的长度不能太长**；
> 
> （2）在**复合索引中，所有的列必须来自同一个表**中，不能跨表建立复合列；
> 
> （3）在复合索引中，列的排列顺序是非常重要的，因此要认真排列列的顺序，原则上，**应该首先定义最唯一的列**，例如在（COL1，COL2）上的索引与在（COL2，COL1）上的索引是不相同的，因为两个索引的列的顺序不同；
> 
> （4）为了使查询优化器使用复合索引，**查询语句中的WHERE子句必须参考复合索引中第一个列**；
> 
> （5）**当表中有多个关键列时，复合索引是非常有用**的；使用复合索引可以提高查询性能，减少在一个表中所创建的索引数量。

--------------------

## 让or使用索引 ##

[https://blog.csdn.net/hguisu/article/details/7106159][https_blog.csdn.net_hguisu_article_details_7106159]

**1 .where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。**

1)myisam表：   
CREATE TABLE IF NOT EXISTS `a` (   
`id` int(1) NOT NULL AUTO\_INCREMENT,   
`uid` int(11) NOT NULL,   
`aNum` char(20) DEFAULT NULL,   
PRIMARY KEY (`id`),   
KEY `uid` (`uid`)   
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO\_INCREMENT=6 ;

2)innodb表：

CREATE TABLE IF NOT EXISTS `a` (   
`id` int(1) NOT NULL AUTO\_INCREMENT,   
`uid` int(11) NOT NULL,   
`aNum` char(20) DEFAULT NULL,   
PRIMARY KEY (`id`),   
KEY `uid` (`uid`)   
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO\_INCREMENT=6 ;

**2 .必须所有的or条件都必须是独立索引：**   
\+——-+———————————————————————————————————————-   
| Table | Create Table   
\+——-+———————————————————————————————————————-   
| a | CREATE TABLE `a` (   
`id` int(1) NOT NULL AUTO\_INCREMENT,   
`uid` int(11) NOT NULL,   
`aNum` char(20) DEFAULT NULL,   
PRIMARY KEY (`id`)   
) ENGINE=MyISAM AUTO\_INCREMENT=6 DEFAULT CHARSET=latin1 |   
\+——-+———————————————————————————————————————-   
1 row in set (0.00 sec)

全表扫描了。

**3. 用UNION替换OR (适用于索引列)**   
通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描.   
注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低.

在下面的例子中, LOC\_ID 和REGION上都建有索引.

//高效
    select loc_id , loc_desc , region from location where loc_id = 10 
    union 
    select loc_id , loc_desc , region  from location where region = "melbourne" 
    //低效
    select loc_id , loc desc , region from location where loc_id = 10 or region = "melbourne"

*  1
 *  2
 *  3
 *  4
 *  5
 *  6

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

**4. 用in来替换or**   
这是一条简单易记的规则，但是实际的执行效果还须检验，在oracle8i下，两者的执行路径似乎是相同的．

//低效: 
    select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30 
    //高效 
    select… from location where loc_in  in (10,20,30);

*  1
 *  2
 *  3
 *  4

--------------------

## IN vs Exists ##

作者：IronM   
链接：[https://www.jianshu.com/p/f212527d76ff][https_www.jianshu.com_p_f212527d76ff]   
來源：简书   
简书著作权归作者所有，任何形式的转载都请联系作者获得授权并注明出处。

select * from A where id in (select id from B);
    
    select * from A where exists (select 1 from B where A.id=B.id);

*  1
 *  2
 *  3

对于以上两种情况，**in是在内存里遍历比较，而exists需要查询数据库**，所以当B表数据量较大时，exists效率优于in。

**1、IN()语句内部工作原理**   
IN()只执行一次，它查出B表中的所有id字段并缓存起来。之后，检查A表的id是否与B表中的id相等，如果相等则将A表的记录加入结果集中，直到遍历完A表的所有记录。   
它的查询过程类似于以下过程：

List resultSet={};
    Array A=(select * from A);
    Array B=(select id from B);
    
    for(int i=0;i<A.length;i++) {
      for(int j=0;j<B.length;j++) {
          if(A[i].id==B[j].id) {
            resultSet.add(A[i]);
            break;
          }
      }
    }
    return resultSet;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9
 *  10
 *  11
 *  12
 *  13

可以看出，当B表数据较大时不适合使用in()，因为它会B表数据全部遍历一次

**2、EXISTS()语句内部工作原理**   
exists()会执行A.length次，它并不缓存exists()结果集，因为exists()结果集的内容并不重要，重要的是其内查询语句的结果集空或者非空，空则返回false，非空则返回true。   
它的查询过程类似于以下过程：

List resultSet={};
    Array A=(select * from A);
    
    for(int i=0;i<A.length;i++) {
       if(exists(A[i].id) {  //执行select 1 from B where B.id=A.id是否有记录返回
           resultSet.add(A[i]);
       }
    }
    return resultSet;

*  1
 *  2
 *  3
 *  4
 *  5
 *  6
 *  7
 *  8
 *  9

当B表比A表数据大时适合使用exists()，因为它没有那么多遍历操作，只需要再执行一次查询就行。

> 例1：A表有10000条记录，B表有1000000条记录，那么exists()会执行10000次去判断A表中的id是否与B表中的id相等。   
> 例2：A表有10000条记录，B表有100000000条记录，那么exists()还是执行10000次，因为它只执行A.length次，可见B表数据越多，越适合exists()发挥效果。   
> 例3：A表有10000条记录，B表有100条记录，那么exists()还是执行10000次，还不如使用in()遍历10000\*100次，因为in()是在内存里遍历比较，而exists()需要查询数据库，我们都知道查询数据库所消耗的性能更高，而内存比较很快。

结论：EXISTS()适合B表比A表数据大的情况

**3、使用情况分析**

当A表数据与B表数据一样大时，in与exists效率差不多，可任选一个使用。

在插入记录前，需要检查这条记录是否已经存在，只有当记录不存在时才执行插入操作，可以通过使用 EXISTS 条件句防止插入重复记录。

insert into A (name,age) select name,age from B where not exists (select 1 from A where A.id=B.id);

*  1

EXISTS与IN的使用效率的问题，**通常情况下采用exists要比in效率高**，因为IN不走索引。但要看实际情况具体使用：

> **IN适合于外表大而内表小的情况；**   
> **EXISTS适合于外表小而内表大的情况。**

**4、关于EXISTS：**

EXISTS用于检查子查询是否至少会返回一行数据，该子查询实际上并不返回任何数据，而是返回值True或False。

EXISTS 指定一个子查询，检测行的存在。

EXISTS(包括 NOT EXISTS )子句的返回值是一个boolean值。 EXISTS内部有一个子查询语句(SELECT … FROM…),我将其称为EXIST的内查询语句。其内查询语句返回一个结果集, EXISTS子句根据其内查询语句的结果集空或者非空，返回一个布尔值。

一种通俗的可以理解为：**将外查询表的每一行，代入内查询作为检验，如果内查询返回的结果取非空值，则EXISTS子句返回TRUE，这一行行可作为外查询的结果行，否则不能作为结果**。

--------------------

## MySQL中InnoDB的一级索引、二级索引 ##

[https://blog.csdn.net/q936889811/article/details/79751965][https_blog.csdn.net_q936889811_article_details_79751965]   
[https://blog.csdn.net/jijianshuai/article/details/79084874][https_blog.csdn.net_jijianshuai_article_details_79084874]

> innodb引擎是索引组织表，**所有记录都放在聚集索引里**，   
> 因此其**辅助索引中的记录地址存放的主键的键值**。

通过二级索引查询记录仅能得到主键值，要查询完整的记录还需要再通过一次聚集索引查询,这种查询方式为**书签查找**（bookmark lookup）。

> **聚集索引**：例如查字典，每个字所在的位置有一个页码，如果你知道一个字的所在具体页数便可直接翻到相应的页，此刻可以把**字典中的页码看成主键**。字典的正文就是一个查询目录而不需要在通过其他目录来查找。这种正文本身具有一定的规则排序的目录称为聚集索引。   
> **二级索引**：此操作就如查字典，先找到生字的**偏旁部首查找到字在哪一页(获得主键的位置)**，然后再翻到生字的具体章节叶。

**1. 聚簇索引**

> **索引和数据存储在一块**（ 都存储在同一个B\*tree 中）。   
> **一般主键索引都是聚餐索引**   
> Mysql中InnoDB引擎的主键索引为聚簇索引，MyISAM存储引擎采用非聚集索引

**2. 非聚簇索引**

> **索引数据和存储数据是分离的。**
> 
> **二级索引（辅助索引） 二级索引存储的是记录的主键，而不是数据存储的地址**。

以Mysql的InnoDB为例   
主键是聚集索引   
唯一索引、普通索引、前缀索引等都是二级索引（辅助索引）

![这里写图片描述][70]   
![这里写图片描述][70 1]   
![这里写图片描述][70 2]  
**结论**

> select id, plname, ranking from pl\_ranking where id=16;   
> 根据主键查找只需要查找3个磁盘块   
> select id, plname, ranking from pl\_ranking where   
> plname=’Java’; 根据编程语言名称查询需要读取5个磁盘块

**结论一**

> 通过上面的主键索引和非主键索引的例子我们可以得出：   
> **主键索引（聚餐索引）查询效率比非主键索引查询效率更高**。如果能使用主键查找的，就尽量使用主键索引进行查找。

**结论二**

> 从上面图中我们还可以分析得出以下结论：   
> **主键定义的长度越小，二级索引的大小就越小，这样每个磁盘块存储的索引数据越多，查询效率就越高。**

[https_blog.csdn.net_hehexiaoxia_article_details_54312130]: https://blog.csdn.net/hehexiaoxia/article/details/54312130
[oracle]: https://www.baidu.com/s?wd=oracle&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[https_blog.csdn.net_qq_16239775_article_details_79665972]: https://blog.csdn.net/qq_16239775/article/details/79665972
[Link 1]: https://www.baidu.com/s?wd=%E4%B8%AD%E6%95%B0%E6%8D%AE&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[Link 2]: https://www.baidu.com/s?wd=%E4%B8%AD%E8%A1%8C&tn=24004469_oem_dg&rsv_dl=gh_pl_sl_csd
[https_blog.csdn.net_hguisu_article_details_7106159]: https://blog.csdn.net/hguisu/article/details/7106159
[https_www.jianshu.com_p_f212527d76ff]: https://www.jianshu.com/p/f212527d76ff
[https_blog.csdn.net_q936889811_article_details_79751965]: https://blog.csdn.net/q936889811/article/details/79751965
[https_blog.csdn.net_jijianshuai_article_details_79084874]: https://blog.csdn.net/jijianshuai/article/details/79084874
[70]: /images/20220317/7535e9dea9fa4598aa3490df9164ac98.png
[70 1]: /images/20220317/c3a8d7debcf84680a0191c82d4d287cc.png
[70 2]: /images/20220317/b120a03687324d7f94218f8bd8d8af2a.png