分页和索引

MySQL 除了对表的简单增删改查操作之外，还有很多高级的使用技巧，例如分页查询，或者索引（单列和组合索引等）有效提升查询效率。

MySQL 分页查询

任务描述

本关任务：能分页读取表中数据，针对大数据量进行简单优化。

相关知识

为了完成本关任务，你需要掌握：

  1. 如何根据页数和每页数据量进行分页查询；
  2. 大数据量级进行优化分页查询。

什么是分页查询

当一个表中有很多符合查询条件的数据的时候，我们往往不需要把他们全部一次性取出来，那样对查询效率或者服务器性能来说都会是一个极大的挑战:例如最简单的商城，假设商城中有一万个数据，但我们在前端可能只会每次看到一页10个左右的数据，当我们浏览完这十个商品后，点击下一页浏览其他的商品，这样的分页浏览，对实际存储商品数据的 MySQL 数据库来说，实际上就是使用了 MySQL 的分页查询功能。

那么我们该怎么实现分页查询呢？

limit 和 offset 实现的分页查询

limit在之前的学习中应该有学习到，它可以返回符合条件的limit条数据：
select * from table where xxx="xxx" limit 10;
输出： 检索记录行符合条件的10条数据

那么我们也可以利用limit和offset来进行分页查询:
select * from table where xxx="xxx" limit 10 offset 10;
输出：检索记录行符合条件的11-20条数据

limit之后的数字代表偏移量，offset代表返回记录的最大值，可以通俗的理解为，从table中取出第limit+1行到limit+offset+1行数据( MySQL 偏移值从0开始计算)。

在实际使用中，我们可以直接把offset直接省略掉：
select * from table where xxx="xxx" limit 10,10;
该语句效果与带offset实际上是一模一样的。

通过分页查询，我们可以精确的定位到行数直接取出符合条件的多条数据，但是这也同样存在着性能问题。

大数据量下分页查询优化

1
2
3
4
5
select * from table where xxx="xxx" limit 100,100;
select * from table where xxx="xxx" limit 1000,100;
select * from table where xxx="xxx" limit 10000,100;
select * from table where xxx="xxx" limit 100000,100;
select * from table where xxx="xxx" limit 1000000,100;

以上语句在实际使用中实际上查询速度会是越来越慢的，这是因为分页查询方式会从数据库第一条记录开始扫描，所以越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

如果可以直接定位到偏移量所在记录，那么查询速度肯定能得到一个巨大的提升，所以可以考虑添加一个嵌套子查询，先查询到偏移量位置，再进行分页：

1
2
3
select * from table where xxx="xxx" and 
id>=(select id from table where xxx="xxx"limit 100000,1)
limit 100;

编程要求

根据提示，在右侧编辑器补充代码:

  1. 按prod_id升序查询表products中第6-10行数据,只要求prod_id字段即可（prod_id从1开始）；

  2. 利用子查询优化查询出按prod_id升序的第10-15行数据，只要求prod_id字段即可。
    数据表结构如下：

测试说明

我们会根据表中数据对你的结果进行测试：

预期输出：

1
2
3
4
5
6
7
8
9
10
11
12
13
prod_id
6
7
8
9
10
prod_id
10
11
12
13
14
15

我的代码

1
2
3
4
5
6
7
8
9
10
USE Products;
#请在此处添加实现代码
########## Begin ##########

#1.分页查询
select prod_id from products limit 5,5;

#2.用子查询优化分页查询语句
select prod_id from products where prod_id >= (select prod_id from products limit 10,1 )limit 5;
########## End ##########

索引（单列索引）

任务描述

本关任务：了解索引并创建索引。

相关知识

为了完成本关任务，你需要掌握：

  1. 索引的定义和作用；
  2. 如何创建索引。

什么是索引？

索引是一张特殊的表，该表保存了主键与索引字段，并指向实体表的记录。

先假设有一张表student,表的数据有100万条数据,其中有一条数据是 name="xiaoming"，如果要拿这条数据的话需要写的sql是
select * from student where name='xiaoming'

一般情况下,在没有建立索引的时候，MySQL 需要扫描全表及扫描 100 万条数据找这条数据，这个效率实际上是非常慢的，那么有什么优化方法呢？答案就是索引。

如果我在name字段上建立索引，那么 MySQL 只需要扫描一行数据及为我们找到这条name='xiaoming'的数据,是不是感觉性能提升了好多咧….

单列索引分类和创建

我们使用最常见的是单列索引，分为主键索引、普通索引和唯一索引。

1.主键索引

主键索引一般在建表时创建，不允许有空值并且值唯一，最好是与表的其他字段不相关的列或者是业务不相关的列。一般会设为 int 而且是 AUTO_INCREMENT 自增类型的，例如一般表的 id 字段。

创建主键索引一般建表时使用 primary 关键字，例如如下语句:

1
2
3
4
CREATE TABLE `student` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

2.普通索引

普通索引实际上是我们最常见的，比如上述提到的例子，我们给name增加一个普通索引如下:

1
CREATE INDEX name_index ON `student`(`name`);

注意这里的 ` 不是单引号，而是键盘 1数字左边的符；

或者是：

1
ALTER TABLE student ADD INDEX name_index(`name`);

3.唯一索引

唯一索引和主键索引类似，要求字段唯一，但是可以允许字段为空，创建语句如下:

1
CREATE UNIQUE  INDEX name_index ON `student`(`name`);

唯一索引可以用来对数据进行强制性要求，可以禁止某表的某个字段出现重复数据。

编程要求

根据提示，在右侧编辑器创建如下student表结构，并创建id为主键索引，name为唯一索引(索引名name_index)，score为普通索引(索引名score_index)。

student结构如下:

字段名 类型、属性
id int(11) ，非空，自增长
name varchar(20) ， 非空
score int(10)

测试说明

平台会对你编写的代码进行测试：

预期输出：

1
2
3
4
Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment
student    0    PRIMARY    1    id    A    0    NULL    NULL        BTREE        
student    0    name_index    1    name    A    0    NULL    NULL        BTREE        
student    1    score_index    1    score    A    0    NULL    NULL    YES    BTREE

我的代码

1
2
3
4
5
6
7
8
9
10
#1.创建student表结构并且设置id为主键索引
create table student (id int(11) not null auto_increment,name varchar(20) not null,score int(10),primary key(id));

#2.对name建立唯一索引
create unique index name_index on student(name);

#3.对score建立普通索引
create index score_index on student(score);
SHOW INDEX FROM student;
########## End ##########

索引（组合索引）

任务描述

本关任务：了解组合索引并创建组合索引。

相关知识

为了完成本关任务，你需要掌握：

  1. 什么是组合索引和单列索引区别；
  2. 如何创建组合索引。

什么是组合索引？

上一关卡中介绍了单列索引，很多同学可能会觉得组合索引实际上就是一个表中有多个单列索引，实际上远非看上去这么简单。

例如上节的例子中，student表有如下字段:id，name，city，score。
我们在name上和city上建立了分别的单列索引。假如这个表数据极大，我们在进行查询的时候，会有很多重名的人，比如执行以下 sql :
select * from student where name="xiaoming" and city = "北京" and score="60"

在进行查询的时候，因为有单列索引在，所以我们会先得到名字为xiaoming的一百条记录，然后在这一百条记录中查找出城市为北京的记录，最后再查找分数为 60 的记录。

这样快不快？有了单列索引，不需要扫描全表，当然快！但是，这还不够，MySQL还可以为我们提供更强大的查询效率，因为以上只会使用效率最高的一个单列索引，其余字段的索引不会生效，所以这个时候就轮到了组合索引出场。

组合索引是同时包含多个字段但是只有一个索引名称，可以直接根据组合索引查询符合字段条件的记录！

例如我们建立一个name，city，core的组合索引，可以将满足条件的记录筛选到极为有限的几条，因为如果只有单列索引的话，可能索引查出来还有上万甚至更多记录，再根据后续条件挨个筛选，一步到位的组合索引明显在性能上会快上很多。

创建组合索引

那组合索引是怎么创建，创建和查询时又有什么需要注意的地方呢？
创建示例如下：
ALTER TABLE student ADD INDEX name_city_score (name,city,score);

实际上直接创建了如下三个索引：

1
2
3
name
name city
name city score

查询时也会根据查询语句以上三个索引进行匹配，即查询名字或者同时查询名字城市又或者查询名字城市分数时都可以使用组合索引，但是查询城市分数等就不能使用，这是因为遵循了最左匹配原则，必须从左开始进行匹配。

编程要求

根据提示，在右侧编辑器补充 sql 代码。

有如下表person，在name、age、address上创建组合索引，索引名name_city_score (无需建表，只增加组合索引即可)。

表person结构如下所示:

字段 类型
id int(11)
name varchar(20)
age int
address varchar(30)

测试说明

平台会对你编写的代码进行测试：

预期输出：

1
2
3
4
5
Table    Non_unique    Key_name    Seq_in_index    Column_name    Collation    Cardinality    Sub_part    Packed    Null    Index_type    Comment    Index_comment
person    0    PRIMARY    1    id    A    0    NULL    NULL        BTREE        
person    1    name_city_score    1    name    A    0    NULL    NULL    YES    BTREE        
person    1    name_city_score    2    age    A    0    NULL    NULL    YES    BTREE        
person    1    name_city_score    3    address    A    0    NULL    NULL    YES    BTREE

我的代码

1
2
3
4
5
6
#1.增加组合索引
create index name_city_score on person(name,age,address);


########## End ##########
SHOW INDEX FROM person;