mysql 低版本实现开窗函数

转载

卫斯理 2024-09-07 18:07:21

文章标签 mysql 低版本实现开窗函数 mysql 数据库 sql d3 文章分类 MySQL 数据库

文章目录

1.1 什么是索引？
2.1 索引的分类
2.3 创建索引
2.4 查询索引
2.5 删除索引
2.6 索引的注意事项
3.1 开窗函数

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: 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 低版本实现开窗函数_mysql

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

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 索引的注意事项

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

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

3.1 开窗函数

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

mysql 低版本实现开窗函数_数据库_02

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

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;

结果如下：

mysql 低版本实现开窗函数_mysql 低版本实现开窗函数_03

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

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

结果如下：

mysql 低版本实现开窗函数_sql_04

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

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;

结果如下：

mysql 低版本实现开窗函数_数据库_05