解释mysql中什么是索引？它的作用是什么？

索引的意义 ·索引在数据库中的作用相当于目录在书籍中的作用类似，都用来提高查找信息的速度。 ·索引是一个表中所包含值的列表，其中注明了表中包含各个值的行所在的存储位置，使用索引查找数据时，先从索引对象中获得相关列的存储位置，然后再直接去其存储位置查找所需信息，这样就无需对这个表进行扫描，从而可以快速的找到所需数据。

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换，MySQL5.7中InnoDB可以支持HASH索引；MEMORY/HEAP存储引擎：支持HASH和BTREE索引。索引可划分为单列索引（其中包括普通索引、唯一索引、主键索引）、组合索引、全文索引、空间索引，其中单列索引是一个索引只包含单个列，但一个表中可以有多个单列索引。

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

索引列中的值必须是唯一的，但是允许为空值，

是一种特殊的唯一索引，不允许有空值。

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大牛，神人 ..." 通过大牛，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。

只有在MyISAM引擎上才能使用，空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

创建空间索引的列，必须将其声明为NOT NULL。。

SPATIAL INDEX spatIdx(g)

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

参考： <u>https://blog.csdn.net/zjy15203167987/article/details/81812370</u>

参考： <u>https://www.jianshu.com/p/d5b2f645d657</u>

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1) 索引项通常比记录要小，所以MySQL访问更少的数据；

(2) 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3) 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4) 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。

产品中有一张图片表，数据量将近100万条，有一条相关的查询语句，由于执行频次较高，想针对此语句进行优化。表结构很简单，主要字段：

user_id 用户ID

picname 图片名称

smallimg 小图名称

一个用户会有多条图片记录，现在有一个根据user_id建立的索引：uid，查询语句也很简单。取得某用户的图片集合

执行查询语句（为了查看真实执行时间，强制不使用缓存）

执行了10次，平均耗时在40ms左右。使用explain进行分析

使用了user_id的索引，并且是const常数查找，表示性能已经很好了

因为这个语句太简单，sql本身没有什么优化空间，就考虑了索引。修改索引结构，建立一个(user_id,picname,smallimg)的联合索引：uid_pic。重新执行10次，平均耗时降到了30ms左右。使用explain进行分析

看到使用的索引变成了刚刚建立的联合索引，并且Extra部分显示使用了'Using Index'

'Using Index'的意思是“覆盖索引”，它是使上面sql性能提升的关键。一个包含查询所需字段的索引称为“覆盖索引”，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表 *** 作，减少IO，提高了效率。

例如上面的sql，查询条件是user_id，可以使用联合索引，要查询的字段是picname smallimg，这两个字段也在联合索引中，这就实现了“覆盖索引”，可以根据这个联合索引一次性完成查询工作，所以提升了性能

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些，但是在整体并发处理能力方面是要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不比MyISAM高，甚至可能会更差。

建议：

（1）尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定

（2）合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行

（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录

（4）尽量控制事务的大小，减少锁定的资源量和锁定时间长度

（5）在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

我们可以通过查看索引的属性来判断创建索引的方法。

查看索引的语法格式如下：

SHOW INDEX FROM <表名>[ FROM <数据库名>]

语法说明如下：

<表名>：指定需要查看索引的数据表名。

<数据库名>：指定需要查看索引的数据表所在的数据库，可省略。比如，SHOW INDEX FROM student FROM test语句表示查看 test 数据库中 student 数据表的索引。

示例

使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_info2 数据表的索引信息，SQL 语句和运行结果如下所示。

mysql>SHOW INDEX FROM tb_stu_info2\G

1. row

Table: tb_stu_info2

Non_unique: 0

Key_name: height

Seq_in_index: 1

Column_name: height

Collation: A

Cardinality: 0

Sub_part: NULL

Packed: NULL

Null: YES

Index_type: BTREE

Comment:

Index_comment:

1 row in set (0.03 sec)

其中各主要参数说明如下：

参数 说明

Table 表示创建索引的数据表名，这里是 tb_stu_info2 数据表。

Non_unique 表示该索引是否是唯一索引。若不是唯一索引，则该列的值为 1；若是唯一索引，则该列的值为 0。

Key_name 表示索引的名称。

Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。

Column_name 表示定义索引的列字段。

Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。

Cardinality 索引中唯一值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。

Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。

Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。

Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。

Index_type 显示索引使用的类型和方法（BTREE、FULLTEXT、HASH、RTREE）。

Comment 显示评注。

---