MySql数据库索引

最新推荐文章于 2024-07-23 13:22:23 发布

爱学习的小奶狗

最新推荐文章于 2024-07-23 13:22:23 发布

阅读量148

点赞数

分类专栏： MySQL数据库文章标签： mysql

本文链接：https://blog.csdn.net/qq_28384023/article/details/116046633

版权

MySQL数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

第一章：索引的基本概念及常见问题

1.1 什么是索引，索引有哪些优缺点？？

什么是索引？？

索引是一个单独的、存储在磁盘上的数据库结构，包含对数据表中所有记录的引用指针。使用索引可以快速找出某个或多个列中有一特定值的行，所有Mysql列类型都可以别索引，对相关列使用索引是提高查询操作速度的最佳操作。
索引和存储引擎

1 . MySQL中所有的索引都是在存储引擎中实现的。
3 . MySQL中的索引数据结构类型主要包括两种B+树和HASH
2 . 每种存储引擎所支持的索引数据结构是不同的。
4. MylSAM和InnoDB存储引擎只支持B+树索引、MEMORY/HEAP存储引擎可以支持HASH和B+树索引。

索引的优缺点

索引的优点：
	1. 索引可以加快数据的查询速度，这是创建索引的主要原因。
	2. 在实现数据的参照完整性方面，可以加速表和表之间的连接
	3. 在使用分组和排序语句进行数据查询时，可以减少查询中分组和排序的时间。
	4. 通过创建唯一索引，可以保证数据库表中每一行数据的唯一性，
 
索引的缺点：
	1.创建索引和维护索引要耗费时间，随着数据量的增加所耗费的时间
	   也会增加。
	2. 索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引
	   还要占一定的物理空间，如果有大量的索引，索引文件可能比数
	   据文件更快达到最大文件尺寸。
	3. 当对表中的数据进行增加，删除和修改的时候，索引页需要维护，
	   会降低数据维护的速度。

1.2 在MySQL中索引主要有哪几种。

以索引的数据结构划分：B+树索引结构、HASH索引结构

以索引功能划分：

1. 普通索引、唯一索引、主键索引：
 	普通索引：是MySQL中的基本索引类型，允许在定义索引的列中插
 	          入重复值和空值。
 	唯一索引：所索引列的值必须唯一，允许有空值。如果是组合索引，
 	         则列值得组合必须唯一。
 	主键索引：必须保证唯一性，且不允许有空值。

2. 单列索引和组合索引：
	单列索引：一个索引只能包含表中的单个列，一张表可以有多个
	          单列索引。
	组合索引：可以将表中多个字段联合起来作为一个索引，只有在查询
             条件中使用了这些字段的左边字段时，索引才会被使用。使用
             组合索引是遵循最左前缀集合。

3. 全文索引：
 	全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找，允许在这些
 	索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建。
 	MySQL中只有MyISAM存储引擎支持全文索引。

4. 空间索引
    空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有4种，
    分别是GEOMETRY、POINT、LINESTRING和POLYGON。MySQL使用SPATIAL
    关键字进行扩展，使得能够用创建正规索引类似的语法创建空间索引。创建
    空间索引的列，必须将其声明为NOT NULL，
    空间索引只能在存储引擎为MyISAM的表中创建。

1.3 如何在MySQL中创建索引

方式一：在创建表的时候添加索引

语法格式：
	CREATE TABLE table_name [col_name data_type]
      [UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] 
        (col_name [length]) [ASC|DESC]
     其中，UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、
       全文索引和空间索引；INDEX与KEY为同义词，两者作用相同，用来
       指定创建索引。
  如：创建表t1时，在id字段上使用UNIQUE关键字创建唯一索引：
 		         CREATE TABLE t1 (
 				    id INT NOT NULL,
 				    name CHAR(30) NOT NULL,
 				    UNIQUE INDEX UniqIdx(id)
 					);

方式二：在已存在的表中添加索引

语法格式一：使用ALTER TABLE
       ALTER TABLE table_name ADD 
 	[UNIQUE|FULLTEXT|SPATIAL] [INDEX|KEY] [index_name] 
 		(col_name[length],...) [ASC|DESC]
 	如，在bookId字段上建立名称为UniqidIdx的唯一索引：
 		ALTER TABLE book ADD UNIQUE INDEX UniqidIdx (bookId);
 
 语法格式二：CREATE INDEX创建索引
 	CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
 	ON table_name (col_name [length],...) [ASC|DESC]
     如，在bookId字段上建立名称为UniqidIdx的唯一索引：
 		CREATE UNIQUE INDEX UniqidIdx ON book (bookId);

1.4 索引中常见的基础问题

怎么判断表中某个字段是否要添加索引

  1. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引
     需能确保定义的列的数据完整性，以提高查询速度。
  2. 在频繁进行排序或分组（即进行group by或order by操作）的列上建
      立索引，如果待排序的列有多个，可以在这些列上建立组合索引。

只要创建了索引，就一定会走索引吗？

不一定。
比如，在使用组合索引的时候，如果没有遵从“最左前缀”的原则进行搜索，则索引是不起作用的。
举例，假设在id、name、age字段上已经成功建立了一个名为MultiIdx的组合索引。索引行中按id、name、age的顺序存放，索引可以搜索id、（id,name）、（id, name, age）字段组合。如果列不构成索引最左面的前缀，那么MySQL不能使用局部索引，如（age）或者（name,age）组合则不能使用该索引查询。

如何判断数据库的索引有没有生效？

 可以使用EXPLAIN语句查看索引是否正在使用。
 举例，假设已经创建了book表，并已经在其year_publication字段上建立了
   普通索引。执行如下语句：
      EXPLAIN SELECT * FROM book WHERE year_publication=1990;
   EXPLAIN语句将为我们输出详细的SQL执行信息，其中：

   possible_keys行给出了MySQL在搜索数据记录时可选用的各个索引。
   key行是MySQL实际选用的索引。
   如果possible_keys行和key行都包含year_publication字段，则说明在查询时使用了该索引。

如何评估一个索引创建的是否合理？

 建议按照如下的原则来设计索引：

 1. 避免对经常更新的表进行过多的索引，并且索引中的列要尽可能少。应该经常
    用于查询的字段创建索引，但要避免添加不必要的字段。
 2. 数据量小的表最好不要使用索引，由于数据较少，查询花费的时间可能比遍历
    索引的时间还要短，索引可能不会产生优化效果。
 3. 在条件表达式中经常用到的不同值较多的列上建立索引，在不同值很少的列上
    不要建立索引。比如在学生表的“性别”字段上只有“男”与“女”两个不同值，因此就
    无须建立索引，如果建立索引不但不会提高查询效率，反而会严重降低数据更新速度。
 4. 当唯一性是某种数据本身的特征时，指定唯一索引。使用唯一索引需能确保定
 	义的列的数据完整性，以提高查询速度。
 5. 在频繁进行排序或分组（即进行group by或order by操作）的列上建立索引，
    如果待排序的列有多个，可以在这些列上建立组合索引。

索引是越多越好吗？

索引并非越多越好，一个表中如有大量的索引，不仅占用磁盘空间，还会影响INSERT、DELETE、UPDATE等语句的性能，因为在表中的数据更改时，索引也会进行调整和更新。

数据库索引失效了怎么办？

 1. 使用组合索引时，需要遵循“最左前缀”原则；
 2. 不在索引列上做任何操作，例如计算、函数、类型转换，会导致索引失效而转向全表扫描；
 3. 尽量使用覆盖索引（之访问索引列的查询），减少 select * 覆盖索引能减少回表次数；
 4. MySQL在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描；
 5. LIKE以通配符开头（%abc）MySQL索引会失效变成全表扫描的操作；
 6. 字符串不加单引号会导致索引失效（可能发生了索引列的隐式转换）；
 	少用or，用它来连接时会索引失效。

所有的字段都适合创建索引吗？

 不是。		
 下列几种情况，是不适合创建索引的：	
 1. 频繁更新的字段不适合建立索引；
 2. where条件中用不到的字段不适合建立索引；
 3. 数据比较少的表不需要建索引；
 4. 数据重复且分布比较均匀的的字段不适合建索引，例如性别、真假值；
 5. 参与列计算的列不适合建索引。

第二章：MySQL索引的原理和数据结构

在MySQL中，索引是在存储引擎层实现的，不同存储引擎对索引的实现方式是不同的，下面我们探讨一下MyISAM和InnoDB两个存储引擎的索引实现方式。

2.1 MylSAM存储引擎索引原理

使用的数据结构：B+树
数据的存储：叶节点的data域存放的是数据记录的地址

如下表所示：这里假设表一共有三列，假设我们以Col1为主键，可以看MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。
在这里插入图片描述
如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示。同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
在这里插入图片描述

2.2 InnoDB存储引擎索引原理

	注意：在MySQL中创建一张表时会出现如下描述;
		1. 若采用MylSAM存储引擎创建表时会生成三个文件分别为：
				表名.frm  ：存放的表的结构
				表名.MYD ：存放的是表中数据
				表名.MYI  :存放的索引(以B+树为例)
				
		2. 若采用InnoDB存储引擎创建表时会生成两个文件分别为：
				表名.frm  ：存放的表的结构
				表名.ibd  ：存放的是数据和索引
	
	重点：在InnoDB存储引擎中，要求表必须有主键
		在默认情况下:
		   当表中没有主键时，若表中某个字段有唯一性约束时，MySQL在存储数据时默
			  认将该字段作为主键，然后使用B+树（这里采用B+树）数据结构将表中数据存储。
		   
		   当表中没有主键且没有唯一性约束字段时，MySQL会自动生成一个主键(此主键无法查看)，然后存储数据。
		  
		 因此：在使用InnoDB存储引擎时要求表中必须有主键，且建议使用自增长主键。

使用的数据结构：B+树
数据的存储：叶节点的data域存放的是数据记录

如下图所示：虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

下图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。
注意：InnoDB存储引擎中为什么非主键索引(辅助索引)叶子节点中存储的是主键值：为了保持一致性和节省存储空间。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。下图为定义在Col3上的一个辅助索引。这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
在这里插入图片描述

总结：假设此时存在一张表t1，表中有字段id、name、age  其中id为主键,name为普通索引
        （辅助索引）。
      执行 select * from t1 where id = 10;
           select * from t1 where name=zhangsan;
     
      情况一：若此时表是以MylSAM存储引擎存储
      	  1.当使用语句select * from t1 where id = 10;  直接去B+树上寻找id = 10 .
      	      然后根据data域中的地址(偏移量)去取出数据。
      	  2.当使用语句  select * from t1 where name=zhangsan;直接在辅助索引上查找，
      	      当查找道数据时直接拿到地址（偏移量）取出数据。
      
      情况二：若此时表是以InnoDB存储引擎存储
      		1.当使用语句select * from t1 where id = 10;  直接去B+树上寻找id = 10 然后直接取出data域数据。
      		2.当使用语句  select * from t1 where name=zhangsan；直接在辅助索引上查找,找到 后
      		  取出data域中的主键值（InnoDB存储引擎中辅助索引的data存放的是主键值） 然后
      		  拿主键值去主键索引上找。(即需要查找两次)

常见面试题：

一个表中可以有多个索引吗？？：一张表可以有多个索引，可以根据用户需求来创建
如果一张表有n个索引，数据存储几份？？:一张表中无论有多少个索引，数据只存储一份
如果数据只存储一份的话，那么其他索引怎么检索到数据呢？？回表：在InnoDB存储引擎中，数据在进行插入的时候，是必须要跟其他索引存储在一起的，如果表中有主键，那么数据更主键放在一起，如果没有主键，跟唯一键放在一起，如果没有唯一键会生成一个6字节的rowid，数据和rowid放在一起，无论如何，总是需要一个索引（即其他索引中存放的是主键值）.

2.3 聚簇索引和非聚簇索引

聚簇索引：根据主键创建的一棵B+树，聚簇索引的叶子节点存放了表中的所有记录。（参照InnoDB存储引擎）
非聚簇索引：根据主键创建的一棵B+树，非聚簇索引的叶子节点存放了表中的所有记录的地址值(偏移量)。（参照MylSAM存储引擎）

2.4 回表、索引覆盖、最左匹配、索引下推。

假设此时存在一张表t1，表中有字段id、name、age  其中id为主键,name为普通索引

回表：执行语句select * from t1 where name=zhangsan
    查找过程：第一次查询name的b+树，根据name获取到id，在根据id去id的b+树上找到行记录
	这个过程称为回表。
       回表的效率不高，要进行多次IO
  
索引覆盖：执行语句select id,name from t1 where name=zhangsan
  在进行检索的时候，直接根据id去name的b+树上回去到id,name两个列的值，此时不需要回表，效率高。
  在实际的开发过程中，应尽可能多的使用索引覆盖来代替回表，在复杂的sql中可以考
   虑将不相关的列都设置为索引列。
  如：select id.name,age,from t1 where name=zhangsan；可以考虑将name.age作为
         一个联合索引。

最左匹配：在查询语句时，若查询的字段是联合索引的话必须先查询最左的索引。

索引下推：执行语句select * from t1 where name=? and age=?;
			在没有索引下推之前，先根据name去存储引擎中拉去符合结果的数据l.fanhui到server层，在server层中对age的条件进行过滤
			有了索引下推之后，根据name，age两个条件直接从存储引擎中拉取结果，不需要哎server层做条件过滤。
			在mysql5.7中默认自动开启。

爱学习的小奶狗

关注

0
点赞
踩
2

收藏

觉得还不错? 一键收藏
0
评论
MySql数据库索引

第一章：索引基础及常见问题1.1 什么是索引，索引有哪些优缺点？？什么是索引？？索引是一个单独的、存储在磁盘上的数据库结构，包含对数据表中所有记录的引用指针。使用索引可以快速找出某个或多个列中有一特定值的行，所有Mysql列类型都可以别索引，对相关列使用索引是提高查询操作速度的最佳操作。索引和存储引擎1 . MySQL中所有的索引都是在存储引擎中实现的。3 . MySQL中的索引数据结构类型主要包括两种B+树和HASH2 . 每种存储引擎所支持的索引数据结构是不
复制链接

扫一扫