索引

索引概述

索引类似于书的目录，利用好索引技术可以很大的提升查询效率，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现

优点

降低服务需要扫描的数据量，减少了IO次数
帮助服务器避免排序和使用临时表
帮助将随机I/O转为顺序I/O

缺点

占用额外空间，影响插入速度
如果表内容修改(如表中间插入内容)，则索引也会随之而更新

索引类型

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引(在主键上自动建立索引)、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

B+Tree索引

按顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据
根节点和枝节点只存储索引不存储数据，叶子节点既存储索引又存储数据
每个叶子节点中还包含列表指针会指向下一个叶子节点的位置，因此还可以实现叶子节点间一定范围的查询而无需从根节点重新开始查找

可以使用B+Tree索引的查询类型：

全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30
匹配最左前缀：即只使用索引的第一列，如：姓wang
匹配列前缀：只匹配一列值开头部分，如：姓以w开头的
匹配范围值：如：姓ma和姓wang之间
精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的
只访问索引的查询

B+Tree索引的限制：

如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾
不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

特别提示

索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

创建索引

语法

更多语法帮助：help CREATE INDEX;
UNIQUE：表示唯一索引，如手机号不能重复但是允许为空

CREATE [UNIQUE] INDEX index_name ON tbl_name (index_col_name[(length)],...);
#或
ALTER TABLE tbl_name ADD INDEX index_name(index_col_name[(length)]);

范例

索引的名称通常建议为**idx_**开头

#创建索引前
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| StuID     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)      | NO   |     | NULL    |                |
| Age       | tinyint unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')    | NO   |     | NULL    |                |
| ClassID   | tinyint unsigned | YES  |     | NULL    |                |
| TeacherID | int unsigned     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+


#为students的name字段创建索引，(name(10))匹配name左侧的前十个字符作为索引
mysql> CREATE INDEX idx_name ON students(name(10));


#创建索引后，key字段就代表索引的意思
mysql> desc students;
+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| StuID     | int unsigned     | NO   | PRI | NULL    | auto_increment |
| Name      | varchar(50)      | NO   | MUL | NULL    |                |
| Age       | tinyint unsigned | NO   |     | NULL    |                |
| Gender    | enum('F','M')    | NO   |     | NULL    |                |
| ClassID   | tinyint unsigned | YES  |     | NULL    |                |
| TeacherID | int unsigned     | YES  |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+


#查看表中索引的相关信息
mysql> SHOW INDEX FROM students\G;
...
*************************** 2. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name #索引名称
 Seq_in_index: 1
  Column_name: Name
    Collation: A
  Cardinality: 25
     Sub_part: 10
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

创建复合索引
注意：当创建下面的复合索引后上面的针对name的索引就没有意义了相当于冗余索引了

#将name和age字段作为复合索引，name在前，age在后
mysql> CREATE INDEX idx_name_age ON students(name(10),age);

#查看创建的复合索引信息
mysql> SHOW INDEX FROM students\G
...
*************************** 4. row ***************************
        Table: students
   Non_unique: 1
     Key_name: idx_name_age
 Seq_in_index: 2
  Column_name: Age
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

删除索引

语法

DROP INDEX index_name ON tbl_name;
#或
ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引

语法

SHOW INDEXES FROM [db_name.]tbl_name;

范例

key字段就代表索引的意思

mysql> show index from hellodb.students\G
*************************** 1. row ***************************
        Table: students
   Non_unique: 0
     Key_name: PRIMARY #主键索引，即创建主键时自动添加的索引
 Seq_in_index: 1
  Column_name: StuID
    Collation: A
  Cardinality: 25
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE #实际上就是B+TREE索引
      Comment: 
Index_comment: 
      Visible: YES
   Expression: NULL

查看索引的使用情况

粗略查看索引的使用情况，更详细了解需要使用EXPLAIN工具
此命令主要用于统计所有利用索引的行数

语法

Mariadb部分版本可用，MySQL最新版不支持

#先将此变量设为1，表示打开
SET GLOBAL userstat=1;

#然后查看索引的使用情况
SHOW INDEX_STATISTICS;

EXPLAIN 查看索引的使用情况

更详细的了解索引的使用情况
主要用来分析每条sql语句是否有效的利用了索引，获取查询执行计划信息，用来查看查询优化器如何执行查询
参考资料： https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

语法

EXPLAIN SELECT ...

范例

#在name字段加索引后：
#未利用索引的情况，因为B+TREE索引不支持左模糊和全模糊
mysql> EXPLAIN select * from students where name like '%w'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 25
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

#利用索引的显示
mysql> EXPLAIN select * from students where name like 'w%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: students
   partitions: NULL
         type: range
possible_keys: index_name
          key: index_name
      key_len: 20
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.00 sec)

EXPLAIN输出信息说明

深色字段更需留意

列名	说明
id	执行编号，标识select所属的行。如果在语句中没子查询或关联查询，只有唯一的select，每行都将显示1。否则，内层的select语句一般会顺序编号，对应于其在原始语句中的位置
select_type	简单查询：SIMPLE 复杂查询：PRIMARY（最外面的SELECT）、DERIVED（用于FROM中的子查询）、UNION（UNION语句的第一个之后的SELECT语句）、UNIONRESUlT（匿名临时表）、SUBQUERY（简单子查询）
table	访问引用哪个表（引用某个查询，如“derived3”）
type	关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式
possible_keys	查询可能会用到的索引
key	显示mysql决定采用哪个索引来优化查询
key_len	显示mysql在索引里使用的字节数
ref	根据索引返回表中匹配某单个值的所有行
rows	为了找到所需的行而需要读取的行数，估算值，不精确。通过把所有rows列值相乘，可粗略估算整个查询会检查的行数
Extra	额外信息 Using index：MySQL将会使用覆盖索引，以避免访问表 Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤 Using temporary：MySQL对结果排序时会使用临时表 Using filesort：对结果使用一个外部索引排序

type字段的详细说明

type显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL ，一般来说，得保证查询至少达到range级别，最好能达到ref

type	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生（高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味着mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

—

索引

索引是数据库中一种用于提高查询性能的数据结构。它类似于书中的目录，能够加速数据库系统在表中查找特定数据行的速度。索引的创建基于表的一列或多列，可以大幅度提高数据的检索速度，但同时也可能导致写入（插入、更新、删除）性能的下降。

以下是有关索引的详细解释：

索引的类型：
- 单列索引（Single-column Index）： 只包含一个列的索引。
```
CREATE INDEX idx_example ON example_table (column_name);
```
- 复合索引（Composite Index）： 包含多个列的索引，用于覆盖多个列的查询。
```
CREATE INDEX idx_example ON example_table (column1, column2);
```
- 唯一索引（Unique Index）： 确保索引列的值唯一。
```
CREATE UNIQUE INDEX idx_unique_example ON example_table (column_name);
```
- 全文索引（Full-text Index）： 用于全文搜索的特殊类型索引。
```
CREATE FULLTEXT INDEX idx_fulltext_example ON example_table (column_name);
```

如何创建索引：

在创建表时，可以在列定义的末尾添加 INDEX 关键字来创建索引。

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    INDEX idx_name (name)
);

也可以使用 CREATE INDEX 语句在已存在的表上创建索引。

CREATE INDEX idx_name ON example_table (name);

如何使用索引：
- 索引通常会加速与索引列相关的查询，如 SELECT 和 JOIN。
```
SELECT * FROM example_table WHERE name = 'John';
```
- 但是，对于小表或者频繁进行写操作的表，索引可能导致性能下降，因此在决定创建索引时需要权衡。
注意事项：
- 索引提高了读取性能，但在写入操作时可能导致性能下降，因为每次写入都要维护索引。
- 需要根据实际查询的类型和频率来决定是否创建索引，过多或不必要的索引可能影响性能。
删除索引：
- 使用 DROP INDEX 语句可以删除已存在的索引。
```
DROP INDEX idx_name ON example_table;
```
- 注意删除索引不会删除表中的数据，只是删除了索引结构。

总体而言，索引是数据库中用于提高查询性能的强大工具，但需要根据具体场景和需求来合理设计和使用。在大型数据库系统中，索引的创建和维护是数据库性能优化的一个重要方面。

频繁出现在 where 子句中的列可以考虑使用索引

这句话的意思是，当某一列经常用作查询条件的 WHERE 子句中的过滤条件时，考虑为该列创建索引。通过在这样的列上创建索引，可以加速查询操作，因为数据库引擎能够更快速地定位并检索符合条件的数据。

例如，考虑一个包含用户信息的表，其中有一个列 username 存储用户的用户名。如果在应用中经常需要根据用户名来查询用户信息，那么在 username 列上创建索引将提高查询的性能。查询可能类似于：

SELECT * FROM users WHERE username = 'john_doe';

在这种情况下，如果 username 列上有索引，数据库引擎可以更快速地找到匹配条件的行，而不必逐行扫描整个表。

一般来说，考虑在以下情况下为列创建索引：

列经常用作查询的过滤条件（特别是使用了等值条件，如 =）。
列用于连接操作（在 JOIN 操作中）。
列用于排序和分组操作。
列是唯一约束或主键列，因为这些列经常用于唯一标识行。

需要注意的是，并不是所有列都适合创建索引，而且在创建索引时需要权衡，因为索引的维护会占用额外的存储空间，并在写入操作时引起性能开销。因此，根据实际使用情况和数据库的性能特征来决定是否创建索引，以及在哪些列上创建索引。

主键本身就是索引吗？

是的，主键本身就是一种索引。主键是一种用于唯一标识表中每条记录的机制，而数据库系统通常会在主键列上创建索引以提高检索性能。

当你定义一个主键时，数据库系统会自动为主键列创建索引。这个索引确保了主键列的值是唯一的，并且通常是按照升序或降序排列的，以便更快地检索数据。

以下是一个创建主键并自动创建索引的示例：

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

在上述示例中，id 列被定义为主键，这就意味着数据库系统会自动在 id 列上创建一个唯一的、排序的索引。这个索引不仅确保了 id 列的唯一性，还提高了在该列上进行检索的速度。

因此，可以说主键是一种特殊的索引，它具有唯一性和非空性的特性，并且用于标识表中每一行的唯一标识符。

索引就是空间换时间吗？

是的，索引的创建和使用可以被看作是一种空间换时间的策略。这是因为索引在数据库中占用一定的存储空间，但它可以极大地提高查询的性能，减少数据检索所需的时间。

具体来说，通过在表的列上创建索引，数据库系统可以创建一种数据结构，该结构能够更快速地定位和检索特定值。这种提高检索速度的优势是通过使用额外的存储空间来实现的。

一些关于索引的空间换时间的要点包括：

更快的查询速度： 索引允许数据库引擎直接定位和访问符合查询条件的数据，而不必扫描整个表。这可以显著减少查询所需的时间。
附加存储空间： 索引需要额外的存储空间来存储索引数据结构。这可能导致数据库占用更多的磁盘空间。
写入性能开销： 索引的维护可能会在插入、更新和删除数据时引起性能开销。每次对表进行写操作时，数据库系统可能需要更新索引以保持一致性。
选择合适的索引： 在创建索引时，需要权衡性能和存储空间的需求。不是所有的列都适合创建索引，因此选择哪些列创建索引是一个需要慎重考虑的决策。

总体而言，索引是一种重要的数据库优化工具，通过提供更快的查询速度来改善性能，但在使用时需要注意平衡索引带来的存储空间占用和写入性能开销。

InnoDB中，数据文件本身就是按主键索引存储的，叶子节点中保存的就是数据记录

在 InnoDB 存储引擎中，数据文件本身是按照聚簇索引（Clustered Index）的方式组织和存储数据的。而默认情况下，主键索引就是聚簇索引。聚簇索引的特点是数据行的物理存储顺序与索引的逻辑顺序一致。这意味着主键索引的叶子节点中保存的就是实际的数据记录。

具体来说，如果你在表定义中没有显式地指定主键索引，InnoDB 存储引擎会默认使用一个被称为 “聚簇索引” 的结构，该索引是按照主键的顺序组织的。如果表定义中有明确的主键，则主键索引就是聚簇索引。在这种情况下，InnoDB 将使用主键的值来对表中的数据行进行排序，并将数据存储在主键索引的叶子节点上。

举例说明：

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50)
) ENGINE=InnoDB;

在上述示例中，id 列是主键，而主键索引就是聚簇索引。在 InnoDB 中，数据行的物理存储顺序将按照 id 列的值进行排序。

需要注意的是，聚簇索引的特性也意味着如果你在表中使用了主键作为查询条件，那么查询的性能通常会很高，因为相应的数据行在物理存储上是连续的。然而，如果你在表中使用非主键列进行查询，而非主键列没有单独的索引，可能会导致全表扫描，性能下降。

总的来说，在 InnoDB 存储引擎中，了解主键索引是聚簇索引的概念对于理解数据存储和查询性能是很重要的。

如果在其他字段上定义B+Tree索引，叶子节点的数据记录的是主键，这种称之为辅助索引

是的，你描述的情况属于辅助索引（Secondary Index）的典型行为。在 InnoDB 存储引擎中，辅助索引和聚簇索引（通常是主键索引）的工作方式略有不同。

在辅助索引中，B+Tree 的叶子节点并不直接包含完整的数据行，而是包含对应数据行的主键值。这种结构使得在使用辅助索引进行查询时，首先根据辅助索引定位到相应的主键值，然后再通过主键值去聚簇索引中找到完整的数据行。

具体来说，假设有如下的表结构：

CREATE TABLE example_table (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,
    INDEX idx_age (age)
) ENGINE=InnoDB;

在上述示例中，id 列是主键，idx_age 是在 age 列上创建的辅助索引。当你查询 age 列时，InnoDB 使用 idx_age 辅助索引找到相应的主键值，然后再使用主键值在聚簇索引中找到完整的数据行。

这种设计的好处是，辅助索引的 B+Tree 结构更加紧凑，可以容纳更多的索引节点在内存中，提高查询效率。但在使用辅助索引时，需要经过两次查找过程，可能引起额外的 I/O 操作，因为首先要查找辅助索引，然后再查找聚簇索引。

需要注意，辅助索引的设计和使用需要根据具体的查询模式和性能需求来考虑，因为在某些情况下，辅助索引可能导致额外的开销。