1.1 什么是索引？

索引有点像书本中的目录，你可以通过目录很快地找到你想读的章节，那么在数据库中，索引就类似于书本的目录，它的存在就是为了能更快的访问数据，索引本质上也是一张表。

在MySQL中访问行数的方式有两种，第一种是顺序访问，所谓顺序访问就是访问整张表的数据，从头到尾的遍历、查询，直到找到符合条件的目标数据，但是当一个表中的数据量很大的时候，查找会变得很慢，效率低下。

第二种方式就是索引访问，索引访问不是直接的遍历整张表的数据，而是先去遍历索引，找到数据的位置，然后再通过数据的位置去表中查找数据，使用索引访问的前提是要先建立索引。

我们以书本举例的话，如果我们想找到某个章节的内容，我们可以直接看里面的内容，但是不是很方便，效率不高，但是我们可以先去查看目录，然后根据目录的结果去查询对应的章节，就会很快，直接访问和索引访问，它们的访问速度可能会相差几十倍。

2.1 索引的分类

在MySQL中，按照用途来划分的话，索引主要分为如下三类：普通索引、唯一索引、主键索引。

普通索引： 普通索引是最基本的索引类型，唯一的作用就是对数据的访问速度，没有任何限制，如果一个列的值既不是唯一的，也不是主键，就可以添加一个普通索引。唯一索引： 如果一个列的值是不会重复的， 我们就可以为该列添加一个唯一索引，唯一索引的查询效率要更高一些。主键索引： 主键索引，不需要我们管，只要我们在创建表的时候，创建了主键约束，那么它会自动地创建一个主键索引。2.3 创建索引

创建索引的语法如下：

CREATE INDEX  索引名称 ON 表名(列名); 

示例代码如下：

mysql> desc goods;+-------------+-----------+------+-----+---------+-------+| Field       | Type      | Null | Key | Default | Extra |+-------------+-----------+------+-----+---------+-------+| g_id        | int       | NO   | PRI | NULL    |       || g_name      | char(255) | NO   |     | NULL    |       || classify_id | int       | YES  | MUL | NULL    |       |+-------------+-----------+------+-----+---------+-------+3 rows in set (0.01 sec)mysql> create index g_name_index on goods(g_name);Query OK, 0 rows affected (0.02 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> 

我们上面创建索引的时候，是直接创建索引，我们也可以通过修改表结构来创建索引，语法如下：

 ALTER TABLE 表名 ADD INDEX 索引名字(列名); 

示例代码如下：

mysql> desc goods1;+-------------+-----------+------+-----+---------+-------+| Field       | Type      | Null | Key | Default | Extra |+-------------+-----------+------+-----+---------+-------+| g_id        | int       | NO   | PRI | NULL    |       || g_name      | char(255) | NO   |     | NULL    |       || classify_id | int       | YES  | MUL | NULL    |       |+-------------+-----------+------+-----+---------+-------+3 rows in set (0.00 sec)mysql> alter table goods1 add index g_name_index(g_name);Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0mysql> 

除此之外，我们也可以在创建表的时候指定索引，示例如下：

CREATE TABLE good3(  g_id INT PRIMARY KEY,  g_name CHAR(255) NOT NULL,  classify_id INT,  INDEX g_name_index(g_name));

创建唯一索引的时候，只需要加上UNIQUE 关键字即可,语法如下：

-- 方式一CREATE UNIQUE INDEX  索引名称 ON 表名(列名); -- 方式二ALTER TABLE 表名 ADD UNIQUE INDEX 索引名字(列名); -- 方式三CREATE TABLE good3(  g_id INT PRIMARY KEY,  g_name CHAR(255) NOT NULL,  classify_id INT,  UNIQUE INDEX g_name_index(g_name));

查询表中所有的索引,示例代码如下：

SHOW INDEX FROM good3;

结果如下：

查询数据库中的所有索引，示例代码如下：

mysql> SELECT * FROM mysql.`innodb_index_stats` where database_name ='study';+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+| database_name | table_name  | index_name      | last_update         | stat_name    | stat_value | sample_size | stat_description                  |+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+| study         | apple       | PRIMARY         | 2021-10-17 20:59:07 | n_diff_pfx01 |          3 |           1 | m_id                              || study         | apple       | PRIMARY         | 2021-10-17 20:59:07 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | apple       | PRIMARY         | 2021-10-17 20:59:07 | size         |          1 |        NULL | Number of pages in the index      || study         | areastuinfo | GEN_CLUST_INDEX | 2021-09-12 16:18:27 | n_diff_pfx01 |          0 |           1 | DB_ROW_ID                         || study         | areastuinfo | GEN_CLUST_INDEX | 2021-09-12 16:18:27 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | areastuinfo | GEN_CLUST_INDEX | 2021-09-12 16:18:27 | size         |          1 |        NULL | Number of pages in the index      || study         | classify    | PRIMARY         | 2021-10-31 14:48:06 | n_diff_pfx01 |          4 |           1 | c_id                              || study         | classify    | PRIMARY         | 2021-10-31 14:48:06 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | classify    | PRIMARY         | 2021-10-31 14:48:06 | size         |          1 |        NULL | Number of pages in the index      || study         | classify1   | PRIMARY         | 2021-10-31 13:04:59 | n_diff_pfx01 |          0 |           1 | c_id                              || study         | classify1   | PRIMARY         | 2021-10-31 13:04:59 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | classify1   | PRIMARY         | 2021-10-31 13:04:59 | size         |          1 |        NULL | Number of pages in the index      || study         | good2       | PRIMARY         | 2021-10-31 17:51:15 | n_diff_pfx01 |          0 |           1 | g_id                              || study         | good2       | PRIMARY         | 2021-10-31 17:51:15 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | good2       | PRIMARY         | 2021-10-31 17:51:15 | size         |          1 |        NULL | Number of pages in the index      || study         | good2       | g_name_index    | 2021-10-31 17:51:15 | n_diff_pfx01 |          0 |           1 | g_name                            || study         | good2       | g_name_index    | 2021-10-31 17:51:15 | n_diff_pfx02 |          0 |           1 | g_name,g_id                       || study         | good2       | g_name_index    | 2021-10-31 17:51:15 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | good2       | g_name_index    | 2021-10-31 17:51:15 | size         |          1 |        NULL | Number of pages in the index      || study         | good3       | PRIMARY         | 2021-10-31 20:41:55 | n_diff_pfx01 |          0 |           1 | g_id                              || study         | good3       | PRIMARY         | 2021-10-31 20:41:55 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | good3       | PRIMARY         | 2021-10-31 20:41:55 | size         |          1 |        NULL | Number of pages in the index      || study         | good3       | g_name_index    | 2021-10-31 20:41:55 | n_diff_pfx01 |          0 |           1 | g_name                            || study         | good3       | g_name_index    | 2021-10-31 20:41:55 | n_diff_pfx02 |          0 |           1 | g_name,g_id                       || study         | good3       | g_name_index    | 2021-10-31 20:41:55 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | good3       | g_name_index    | 2021-10-31 20:41:55 | size         |          1 |        NULL | Number of pages in the index      || study         | goods       | PRIMARY         | 2021-10-31 17:41:12 | n_diff_pfx01 |          6 |           1 | g_id                              || study         | goods       | PRIMARY         | 2021-10-31 17:41:12 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods       | PRIMARY         | 2021-10-31 17:41:12 | size         |          1 |        NULL | Number of pages in the index      || study         | goods       | fk_cid          | 2021-10-31 17:41:12 | n_diff_pfx01 |          3 |           1 | classify_id                       || study         | goods       | fk_cid          | 2021-10-31 17:41:12 | n_diff_pfx02 |          6 |           1 | classify_id,g_id                  || study         | goods       | fk_cid          | 2021-10-31 17:41:12 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods       | fk_cid          | 2021-10-31 17:41:12 | size         |          1 |        NULL | Number of pages in the index      || study         | goods       | g_name_index    | 2021-10-31 17:41:12 | n_diff_pfx01 |          6 |           1 | g_name                            || study         | goods       | g_name_index    | 2021-10-31 17:41:12 | n_diff_pfx02 |          6 |           1 | g_name,g_id                       || study         | goods       | g_name_index    | 2021-10-31 17:41:12 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods       | g_name_index    | 2021-10-31 17:41:12 | size         |          1 |        NULL | Number of pages in the index      || study         | goods1      | PRIMARY         | 2021-10-31 17:48:17 | n_diff_pfx01 |          0 |           1 | g_id                              || study         | goods1      | PRIMARY         | 2021-10-31 17:48:17 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods1      | PRIMARY         | 2021-10-31 17:48:17 | size         |          1 |        NULL | Number of pages in the index      || study         | goods1      | fk_classify_id  | 2021-10-31 17:48:17 | n_diff_pfx01 |          0 |           1 | classify_id                       || study         | goods1      | fk_classify_id  | 2021-10-31 17:48:17 | n_diff_pfx02 |          0 |           1 | classify_id,g_id                  || study         | goods1      | fk_classify_id  | 2021-10-31 17:48:17 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods1      | fk_classify_id  | 2021-10-31 17:48:17 | size         |          1 |        NULL | Number of pages in the index      || study         | goods1      | g_name_index    | 2021-10-31 17:48:17 | n_diff_pfx01 |          0 |           1 | g_name                            || study         | goods1      | g_name_index    | 2021-10-31 17:48:17 | n_diff_pfx02 |          0 |           1 | g_name,g_id                       || study         | goods1      | g_name_index    | 2021-10-31 17:48:17 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | goods1      | g_name_index    | 2021-10-31 17:48:17 | size         |          1 |        NULL | Number of pages in the index      || study         | mobile      | PRIMARY         | 2021-09-21 16:17:47 | n_diff_pfx01 |          8 |           1 | m_id                              || study         | mobile      | PRIMARY         | 2021-09-21 16:17:47 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | mobile      | PRIMARY         | 2021-09-21 16:17:47 | size         |          1 |        NULL | Number of pages in the index      || study         | people      | PRIMARY         | 2021-09-20 15:15:34 | n_diff_pfx01 |          0 |           1 | id                                || study         | people      | PRIMARY         | 2021-09-20 15:15:34 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | people      | PRIMARY         | 2021-09-20 15:15:34 | size         |          1 |        NULL | Number of pages in the index      || study         | person      | PRIMARY         | 2021-09-20 15:44:24 | n_diff_pfx01 |          0 |           1 | id                                || study         | person      | PRIMARY         | 2021-09-20 15:44:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | person      | PRIMARY         | 2021-09-20 15:44:24 | size         |          1 |        NULL | Number of pages in the index      || study         | person      | name            | 2021-09-20 15:44:24 | n_diff_pfx01 |          0 |           1 | name                              || study         | person      | name            | 2021-09-20 15:44:24 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | person      | name            | 2021-09-20 15:44:24 | size         |          1 |        NULL | Number of pages in the index      || study         | student     | PRIMARY         | 2021-09-05 14:09:59 | n_diff_pfx01 |          3 |           1 | id                                || study         | student     | PRIMARY         | 2021-09-05 14:09:59 | n_leaf_pages |          1 |        NULL | Number of leaf pages in the index || study         | student     | PRIMARY         | 2021-09-05 14:09:59 | size         |          1 |        NULL | Number of pages in the index      |+---------------+-------------+-----------------+---------------------+--------------+------------+-------------+-----------------------------------+63 rows in set (0.00 sec)

删除索引，有两种方式，具体语法如下：

-- 方式一DROP INDEX 索引名称  ON 表名;--方式二DROP INDEX 索引名称  ON 表名;ALTER TABLE 表名 DROP INDEX 索引名称;

索引虽然能加快数据的访问速度，但是有一些使用原则和注意事项，具体如下：

创建索引和维护索引(因为当数据表中的数据发生变化的时候，索引也需要动态的变化)需要时间，这种时间随着数据量的增加而增加,除此之外索引也会占据一定的物理空间。对于在查询中很少使用的列，不要创建索引，因为很少使用到，所以添加索引的效果并不明显，反而提高系统的维护速度和物理空间。3.1 开窗函数

在MySQL的8.0版本后，增加了对开窗函数的支持，在MySQL中的开窗函数有很多，这里我们先感受一下：假如此时有一张手机表，如下图：

此时我们有如下一个需求，我们想要对各个品牌的手机进行相应的排名，在之前我们先看下三个开窗函数：

row_number():不管排名是否相同，都按照1,2,3,4...N排名rank(): 排名相同的名次有几个一样，后面排名就会跳过几个dense_rank()：排名相同的名字一样，后面的排名不跳过 示例代码如下：

SELECT *, row_number ( ) over ( PARTITION BY p_belong ORDER BY m_price DESC ) AS row_number1 FROM mobile;

结果如下：

我们也可以不分组，对全表进行排序，打编号，只需要去掉PARTITION BY p_belong即可，代码如下：

SELECT *, row_number ( ) over (ORDER BY m_price DESC ) AS row_number1 FROM mobile;

结果如下：

下面我们再看一个需求，我们想要取每组里面价格最贵的手机，示例代码如下：

SELECT * FROM ( SELECT *, row_number ( ) over ( PARTITION BY p_belong ORDER BY m_price DESC ) AS row_number1 FROM mobile ) t WHERE t.row_number1 = 1;

结果如下：

你这么好看，麻烦可以点个关注嘛，谢谢