百度360必应搜狗淘宝本站头条
nodejsbufferorg.jsoupsetnocountoncv2.bitwise_nottime.strptime
当前位置：网站首页 > 编程字典 > 正文

MySQL 索引和开窗函数

toyiye 2024-06-21 12:12 10 浏览 0 评论

1.1 什么是索引？

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

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

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

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

2.1 索引的分类

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

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

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: 0

mysql>

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

 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: 0

mysql>

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

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)
);

2.4 查询索引

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

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)

2.5 删除索引

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

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

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

2.6 索引的注意事项

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

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

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;

结果如下：

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

相关推荐

为何越来越多的编程语言使用JSON（为什么编程）

JSON是JavascriptObjectNotation的缩写，意思是Javascript对象表示法，是一种易于人类阅读和对编程友好的文本数据传递方法，是JavaScript语言规范定义的一个子...

何时在数据库中使用 JSON（数据库用json格式存储）

在本文中，您将了解何时应考虑将JSON数据类型添加到表中以及何时应避免使用它们。每天?分享?最新?软件?开发?，Devops，敏捷?，测试?以及?项目?管理?最新?，最热门?的?文章?，每天?花?...

MySQL 从零开始:05 数据类型（mysql数据类型有哪些,并举例）

前面的讲解中已经接触到了表的创建，表的创建是对字段的声明，比如：上述语句声明了字段的名称、类型、所占空间、默认值和是否可以为空等信息。其中的int、varchar、char和decimal都...

JSON对象花样进阶（json格式对象）

一、引言在现代Web开发中，JSON（JavaScriptObjectNotation）已经成为数据交换的标准格式。无论是从前端向后端发送数据，还是从后端接收数据，JSON都是不可或缺的一部分。...

深入理解 JSON 和 Form-data（json和formdata提交区别）

在讨论现代网络开发与API设计的语境下，理解客户端和服务器间如何有效且可靠地交换数据变得尤为关键。这里，特别值得关注的是两种主流数据格式：...

JSON 语法（json 语法 priority）

JSON语法是JavaScript语法的子集。JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔花括号保存对象方括号保存数组JS...

JSON语法详解（json的语法规则）

JSON语法规则JSON语法是JavaScript对象表示法语法的子集。数据在名称/值对中数据由逗号分隔大括号保存对象中括号保存数组注意：json的key是字符串,且必须是双引号，不能是单引号...

MySQL JSON数据类型操作（mysql的json）

概述mysql自5.7.8版本开始，就支持了json结构的数据存储和查询，这表明了mysql也在不断的学习和增加nosql数据库的有点。但mysql毕竟是关系型数据库，在处理json这种非结构化的数据...

JSON的数据模式（json数据格式示例）

像XML模式一样，JSON数据格式也有Schema，这是一个基于JSON格式的规范。JSON模式也以JSON格式编写。它用于验证JSON数据。JSON模式示例以下代码显示了基本的JSON模式。{"...

前端学习——JSON格式详解（后端json格式）

JSON(JavaScriptObjectNotation)是一种轻量级的数据交换格式。易于人阅读和编写。同时也易于机器解析和生成。它基于JavaScriptProgrammingLa...

什么是 JSON:详解 JSON 及其优势（什么叫json）

现在程序员还有谁不知道JSON吗？无论对于前端还是后端，JSON都是一种常见的数据格式。那么JSON到底是什么呢？JSON的定义...

PostgreSQL JSON 类型:处理结构化数据

PostgreSQL提供JSON类型，以存储结构化数据。JSON是一种开放的数据格式，可用于存储各种类型的值。什么是JSON类型？JSON类型表示JSON(JavaScriptO...

JavaScript:JSON、三种包装类（javascript 包）

JOSN:我们希望可以将一个对象在不同的语言中进行传递，以达到通信的目的，最佳方式就是将一个对象转换为字符串的形式JSON（JavaScriptObjectNotation）-JS的对象表示法...

Python数据分析 只要1分钟 教你玩转JSON 全程干货

Json简介：Json，全名JavaScriptObjectNotation,JSON(JavaScriptObjectNotation(记号、标记))是一种轻量级的数据交换格式。它基于J...

比较一下JSON与XML两种数据格式?（json和xml哪个好）

JSON（JavaScriptObjectNotation）和XML（eXtensibleMarkupLanguage）是在日常开发中比较常用的两种数据格式，它们主要的作用就是用来进行数据的传...

取消回复欢迎 你 发表评论:

请填写验证码
一周热门
最近发表
标签列表