索引总结大全（史上最详细）

落日映苍穹つ 2022-12-21 04:55 314阅读 0赞

1.索引知识点思维导图  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center]

**2.导致索引失效的几种情况**

1.使用<>不等于号
    2.单独使用><的时候
    3.模糊查询时百分号在前
    4.使用符合索引时，第一位置并非索引列
    5.字符型字段为数字时在where 条件不加引号
    6.当变量采用times变量，而表的字段采用date变量时
    7.使用not in， not exists
    8.对索引列进行计算时，需要建立函数索引
    9.使用or可能会使索引失效，从而全表扫描。

**3.常见的索引种类及创建方法**

--如何创建索引
    create  index 索引名 on  表名（列名）
    create index ind_name on student(name);
     --如何删除索引
    drop index ind_name;
    --重命名索引：
    alter index index_old rename to index_new;--重新命名索引
    --查看表的索引：
    show index from table_name（表名）
    --索引分类：
    普通索引：normal
    create index 索引名 on 表名（列名）;
    create index ind_name on student(name);
    --唯一性索引：unique
    create unique index 索引名 on 表名（列名）;
    create unique index ind_name on school(phone);
    --位图（分类）索引：bitmap
    --数据量比较大，基数比较小     比如：男/女
    create bitmap index 索引名 on 表名（列名）;
    create bitmap index ind_sid on student(sid);
    --函数索引：
    create index ind_email on student(length(email));

MySQLS.7 版本后，可以通过查询 sys 库的 schemal_r dundant_indexes 表来查看冗余索引
    
    Mysql如何为表字段添加索引？
    1.添加PRIMARY KEY（主键索引）
    
    ALTER TABLE table_name ADD PRIMARY KEY ( column ) 
    2.添加UNIQUE(唯一索引)
    
    ALTER TABLE table_name ADD UNIQUE ( column ) 
    3.添加INDEX(普通索引)
    
    ALTER TABLE table_name ADD INDEX index_name ( column )
    4.添加FULLTEXT(全文索引)
    
    ALTER TABLE table_name ADD FULLTEXT ( column) 
    5.添加多列索引
    
    ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )
    
    index_name :索引名字
    table_name：表名

MYSQL查看查询是否走了索引

mysql数据库创建索引优化之后，在查询时想看下是否使用到索引，
    使用执行计划查看：explain  +SQL语句
    解释：
    EXPLAIN列的解释：
    
    table：显示这一行的数据是关于哪张表的
    
    type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL
    type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ,一般来说，得保证查询至少达到range级别，最好能达到ref。
    
    possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
    
    key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引
    
    key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
    
    ref：显示索引的哪一列被使用了，如果可能的话，是一个常数
    
    rows：MYSQL认为必须检查的用来返回请求数据的行数
    
    Extra：关于MYSQL如何解析查询的额外信息。将在表4.3中讨论，但这里可以看到的坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢

**4.创建索引的优点**

能够更快的查询数据，有效的提高查询效率  
增删改数据的时候，数据库会浪费资源去维护索引  
数据量少的尽量不要添加索引  
数据量多，查询多，增删改少的列适合添加索引

**5.何时创建索引：**

1）：表的主键和包含唯一约束的列自动创建索引，所以在建立唯一约束时，可以考虑该列是否必要建立，是否要作为查询条件。  
2）：如果某个表的数据量较大（十几二十万以上），某列经常作为where的查询条件，并且检索的出来的行数经常是小于总表的5%，那该列可以考虑建立索引。  
3）对于两表连接的字段，应该考虑建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

**6、为什么索引能提高查询速度**  
先从 MySQL 的基本存储结构说起，MySQL的基本存储结构是页(记录都存在页里边)：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 1]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 2]

MySQL的基本存储结构是页

各个数据页可以组成一个双向链表

每个数据页中的记录又可以组成一个单向链表

每个数据页都会为存储在它里边儿的记录生成一个页目录，在通过主键查找某条记录的时候可以在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录

以其他列(非主键)作为搜索条件：只能从最小记录开始依次遍历单链表中的每条记录。

所以说，如果我们写select \* from user where indexname = 'xxx’这样没有进行任何优化的sql语句，默认会这样做：

定位到记录所在的页:需要遍历双向链表，找到所在的页

从所在的页内中查找相应的记录:由于不是根据主键查询，只能遍历所在页的单链表了

很明显，在数据量很大的情况下这样查找会很慢！这样的时间复杂度为O（n）。

**使用索引之后**  
索引做了些什么可以让我们查询加快速度呢？其实就是将无序的数据变成有序(相对)：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 3]

要找到id为8的记录简要步骤：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 4]

很明显的是：没有用索引我们是需要遍历双向链表来定位对应的页，现在通过 “目录” 就可以很快地定位到对应的页上了！（二分查找，时间复杂度近似为O(logn)）

其实底层结构就是B+树，B+树作为树的一种实现，能够让我们很快地查找出对应的记录。

**关于索引其他重要的内容补充**  
以下内容整理自：《Java工程师修炼之道》

**什么是最左前缀原则**  
MySQL中的索引可以以一定顺序引用多列，这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city)o而最左前缀原则指的是，如果查询的时候查询条件精确匹配索引的左边连续一列或几列，则此列就可以被用到。如下：

select * from user where name=xx and city=xx ; ／／可以命中索引 
    
    select * from user where name=xx ; // 可以命中索引 
    
    select * from user where city=xx; // 无法命中索引

这里需要注意的是，查询的时候如果两个条件都用上了，但是顺序不同，如 city= xx and name ＝xx，那么现在的查询引擎会自动优化为匹配联合索引的顺序，这样是能够命中索引的.

由于最左前缀原则，在创建联合索引时，索引字段的顺序需要考虑字段值去重之后的个数，较多的放前面。ORDERBY子句也遵循此规则。

注意避免冗余索引  
冗余索引指的是索引的功能相同，能够命中 就肯定能命中 ，那么 就是冗余索引如（name,city ）和（name ）这两个索引就是冗余索引，能够命中后者的查询肯定是能够命中前者的 在大多数情况下，都应该尽量扩展已有的索引而不是创建新索引。

ORACLE(常用的数据查询)
    --查看索引被索引的字段:
    select * from user_ind_columns where index_name=upper('&index_name');
     --查看索引个数和类别:
    select * from user_indexes where table='表名' ;
     --如何查看当前用户下有哪些索引
     select table_name,index_name,uniqueness,status from user_indexes;
    --查看索引详细信息
    select t.INDEX_NAME,--索引名字
           t.index_type,--索引类型
           t.TABLESPACE_NAME,--表空间
           t.status,--状态
           t.UNIQUENESS--是否唯一索引
      from all_indexes T 
      where  t.INDEX_NAME='index_name';

**注：**  
用user\_indexes和user\_ind\_columns系统表查看已经存在的索引  
user\_indexes: 系统视图存放是索引的名称以及该索引是否是唯一索引等信息。  
user\_ind\_column: 系统视图存放的是索引名称，对应的表和列等。

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/e846d63816444c6e8584ae41daf5db68.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/f17787880c7d4e4d8fe8dcf851023a65.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/70954904d1c94b0eac0bb88522278df1.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/9a8c968d9c1e4fe095c1a0312113a5ba.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3NoYXNzZA_size_16_color_FFFFFF_t_70_pic_center 4]: /images/20221120/171c81ec209d43879dac8eed4a9cd103.png