索引

一、索引的介绍

数据库中专门用于帮助用户快速查找数据的一种数据结构。类似于字典中的目录，查找字典内容时可以根据目录查找到数据的存放位置吗，然后直接获取。

二 、索引的作用

约束和加速查找

三、常见的几种索引：

单列：普通索引，唯一索引，主键索引

多列：联合索引（多列），比如：联合主键索引、联合唯一索引、联合普通索引

联合索引，也称之为组合索引。

总结：

  1. 单列：
  2. 唯一索引：
  3.   加速查找 + unique(约束)可以为空
  4. 普通索引：
  5.    仅有一个功能:加速查找
  6.    create index ix_name on userinfo(name);
  8. 主键索引：
  9.    加速查找+约束（不为空）
  10. 多列：
  11. 组合索引

主键索引比普通索引快

无索引和有索引的区别以及建立索引的目的

无索引： 从前往后一条一条查询

有索引：创建索引的本质，就是创建额外的文件（某种格式存储，查询的时候，先去格外的文件找，定好位置，然后再去原始表中直接查询。但是创建索引越多，会对硬盘也是有损耗。

建立索引的目的：

a.额外的文件保存特殊的数据结构

b.查询快，但是插入更新删除依然慢

c.创建索引之后，必须命中索引才能有效

索引的种类

hash 索引和 BTree 索引

（1）hash 类型的索引：查询单条快，范围查询慢

（2）btree 类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为 innodb 默认支持它）

总结：

  1. Hash 索引
  2. 优点：单条数据查询速度要快

缺点： > < like 查询速度不一定快，因为 hash 索引生成 hash 值的是无序的，所以不能使用排序

BTREE 索引

innodb 引擎 默认是 Btree 索引，这个是根据二分查找查询

3.1 普通索引

作用：仅有一个加速查找

创建表+普通索引

  1. create table userinfo(
  2.    nid int not null auto_increment primary key,
  3.    name varchar(32) not null,
  4.    email varchar(64) not null,
  5.    index ix_name(name)
  6. );

普通索引

  1. create index 索引的名字 on 表名(列名)

删除索引

  1. drop index 索引的名字 on 表名

查看索引

  1. show index from 表名

3.2 唯一索引

唯一索引有两个功能：加速查找和唯一约束（可含 null）

创建表+唯一索引

  1. create table userinfo(
  2.    id int not null auto_increment primary key,
  3.    name varchar(32) not null,
  4.    email varchar(64) not null,
  5.    unique  index  ix_name(name)
  6. );

唯一索引

  1. create unique index 索引名 on 表名(列名)

删除唯一索引

  1. drop unique index 索引名 on 表名

3.3 主键索引

主键索引有两个功能： 加速查找和唯一约束（不含 null）

创建表+主键索引

  1. create table userinfo(
  2.     id int not null auto_increment primary key,
  3.     name varchar(32) not null,
  4.     email varchar(64) not null,
  5.     unique  index  ix_name(name)
  6. );
  7. 或者
  8. create table userinfo(
  9.     id int not null auto_increment,
  10.     name varchar(32) not null,
  11.     email varchar(64) not null,
  12.     primary key(id),
  13.     unique  index  ix_name(name)
  14. );

主键索引

  1. alter table 表名 add primary key(列名);

删除主键索引

  1. alter table 表名 drop primary key;
  2. alter table 表名  modify  列名 int, drop primary key;

3.4 组合索引

组合索引是将 n 个列组合成一个索引

其应用场景为：频繁的同时使用 n 列来进行查询，如：where name = ‘alex’ and email = ‘alex@qq.com’。

  1. create index 索引名 on 表名(列名 1,列名 2);

四、索引名词

  1. #覆盖索引：在索引文件中直接获取数据
  2. 例如：
  3. select name from userinfo where name = 'alex50000';
  5. #索引合并：把多个单列索引合并成使用
  6. 例如：
  7. select * from  userinfo where name = 'alex13131' and id = 13131;

直接用索引字段查询，这种行为叫做覆盖索引

组合索引查询速度 > 索引合并查询速度

覆盖索引和索引合并，面试会问道。

五、正确使用索引的情况

数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。

使用索引，我们必须知道：

（1）创建索引

（2）命中索引

（3）正确使用索引

准备 300w 条数据：

  1. #1. 准备表
  2. create table userinfo(
  3. id int,
  4. name varchar(20),
  5. gender char(6),
  6. email varchar(50)
  7. );
  10. #2. 创建存储过程，实现批量插入300万条记录
  11. delimiter $$ #声明存储过程的结束符号为$$
  12. create procedure auto_insert1()
  13. BEGIN
  14.     #设置变量1，默认值为1
  15.     declare i int default 1;
  16.     while(i<=3000000)do
  17.        #concat，字符串拼接。当i为1时，那么concat('alex',i)表示为alex1
  18.         insert into userinfo values(i,concat('alex',i),'male',concat('egon',i,'@oldboy'));
  19.         set i=i+1;
  20.     end while;
  21. END$$ #$$结束
  22. delimiter ; #重新声明分号为结束符号
  25. #3. 查看存储过程
  26. show create procedure auto_insert1\G 
  29. #4. 调用存储过程
  30. call auto_insert1();

等待几个小时，300 万条数据，就会执行完成。

上面这种方式太慢了，下面介绍利用 python 脚本，使用协程插入 300 万条数据，只需要 80 秒

  1. #!/usr/bin/env python
  2. # coding: utf-8
  3. import pymysql
  4. import gevent
  5. import time
  10. class MyPyMysql:
  11.     def __init__(self, host, port, username, password, db, charset='utf8'):
  12.         self.host = host          # mysql主机地址
  13.         self.port = port          # mysql端口
  14.         self.username = username  # mysql远程连接用户名
  15.         self.password = password  # mysql远程连接密码
  16.         self.db = db              # mysql使用的数据库名
  17.         self.charset = charset    # mysql使用的字符编码,默认为utf8
  18.         self.pymysql_connect()    # __init__初始化之后，执行的函数
  21.     def pymysql_connect(self):
  22.         # pymysql连接mysql数据库
  23.         # 需要的参数host,port,user,password,db,charset
  24.         self.conn = pymysql.connect(host=self.host,
  25.                                     port=self.port,
  26.                                     user=self.username,
  27.                                     password=self.password,
  28.                                     db=self.db,
  29.                                     charset=self.charset
  30.                                )
  31.         # 连接mysql后执行的函数
  32.         self.asynchronous()
  35.     def run(self, nmin, nmax):
  36.         # 创建游标
  37.         self.cur = self.conn.cursor()
  39.         # 定义sql语句,插入数据id,name,gender,email
  40.         sql = "insert into userinfo(id,name,gender,email) values (%s,%s,%s,%s)"
  43.         # 定义总插入行数为一个空列表
  44.         data_list = []
  45.         for i in range(nmin, nmax):
  46.             # 添加所有任务到总的任务列表
  47.             result = (i, 'alex' + str(i), 'male', 'egon' + str(i) + '@oldboy')
  48.             data_list.append(result)
  50.         # 执行多行插入，executemany(sql语句,数据(需一个元组类型))
  51.         content = self.cur.executemany(sql, data_list)
  52.         if content:
  53.              print('成功插入第{}条数据'.format(nmax-1))
  55.         # 提交数据,必须提交，不然数据不会保存
  56.         self.conn.commit()
  61.     def asynchronous(self):
  62.         # g_l 任务列表
  63.         # 定义了异步的函数: 这里用到了一个gevent.spawn方法
  64.         max_line = 10000  # 定义每次最大插入行数(max_line=10000,即一次插入10000行)
  65.         g_l = [gevent.spawn(self.run, i, i+max_line) for i in range(1, 3000001, max_line)]
  68.         # gevent.joinall 等待所以操作都执行完毕
  69.         gevent.joinall(g_l)
  70.         self.cur.close()  # 关闭游标
  71.         self.conn.close()  # 关闭pymysql连接
  76. if __name__ == '__main__':
  77.     start_time = time.time()  # 计算程序开始时间
  78.     st = MyPyMysql('192.168.11.102', 3306, 'py123', 'py123', 'db20')  # 实例化类，传入必要参数
  79.     print('程序耗时{:.2f}'.format(time.time() - start_time))  # 计算程序总耗时

注意：

  1. 一般插入表数据是这样的
  1. insert into userinfo(id,name,gender,email) values ('1','alex1','male','egon1@oldboy')

使用协程插入数据，是这样的

  1. insert into userinfo(id,name,gender,email) values ('1','alex1','male','egon1@oldboy'),('2','alex2','male','egon2@oldboy'),('3','alex3','male','egon3@oldboy')...后面有 1 万个元组

单从插入速度来讲，一次性插入 1 万的效率是高于一次只插入一条的。ps: 不用 1 秒就可以插入 1 万条数据

  1. 协程执行时，遇到 I/O 就切换了，那么它的执行速度是很快的。

基于这 2 点，就能保证在 2 分钟内，完成 300 万的数据插入。

测试：

  1. - like '%xx'
  2.     select * from userinfo where name like '%al';
  3. - 使用函数
  4.     select * from userinfo where reverse(name) = 'alex333';
  5. - or
  6.     select * from userinfo where id = 1 or email = 'alex122@oldbody';
  7.     特别的：当or条件中有未建立索引的列才失效，以下会走索引
  8.             select * from userinfo where id = 1 or name = 'alex1222';
  9.             select * from userinfo where id = 1 or email = 'alex122@oldbody' and name = 'alex112';
  10. - 类型不一致
  11.     如果列是字符串类型，传入条件是必须用引号引起来，不然...
  12.     select * from userinfo where name = 999;
  13. - !=
  14.     select count(*) from userinfo where name != 'alex';
  15.     特别的：如果是主键，则还是会走索引
  16.         select count(*) from userinfo where id != 123;
  17. - >
  18.     select * from userinfo where name > 'alex'
  19.     特别的：如果是主键或索引是整数类型，则还是会走索引
  20.         select * from userinfo where id > 123;
  21. - order by
  22.     select email from userinfo order by name desc;
  23.     当根据索引排序时候，选择的映射如果不是索引，则不走索引
  24.     特别的：如果对主键排序，则还是走索引：
  25.         select * from userinfo order by id desc;
  27. - 组合索引最左前缀
  28.     如果组合索引为：(name,email)
  29.     name and email       -- 使用索引
  30.     name                 -- 使用索引
  31.     email                -- 不使用索引

尽量使用组合索引

面试重点，列举 3 个不走索引的场景

什么是最左前缀呢？

  1. #最左前缀匹配：
  2. #创建组合索引，name和email组合
  3. create index ix_name_email on userinfo(name,email);
  4. #执行下面3个sql
  7. select * from userinfo where name = 'alex';
  8. select * from userinfo where name = 'alex' and email='alex@oldBody';
  9. select * from userinfo where  email='alex@oldBody';
  12. name和email组合索引之后，查询：
  13. （1）name        ---使用索引
  14. （2）name和email ---使用索引
  15. （3）email       ---不使用索引，因为没有name或者email字段
  16. 对于同时搜索n个条件时，组合索引的性能好于多个单列索引
  17. ******组合索引的性能>索引合并的性能*********

六、索引的注意事项（重点）

  1. (1)避免使用select *
  2. (2)count(1)或count(列) 代替count(*)
  3. (3)创建表时尽量使用char代替varchar
  4. (4)表的字段顺序固定长度的字段优先
  5. (5)组合索引代替多个单列索引（经常使用多个条件查询时）
  6. (6)尽量使用短索引 （create index ix_title on tb(title(16));特殊的数据类型 text类型）
  7. (7)使用连接（join）来代替子查询
  8. (8)连表时注意条件类型需一致
  9. (9)索引散列（重复少）不适用于建索引，例如：性别不合适

关于第 7 点，目前 mysql5.7 版本，没有区别。它和子查询速度是一样的。

关于第 8 点，假设有 2 个表，a 和 b。查询语句如下：

  1. select * from a left join b on b.pid=a.id

务必保证 on 后面等式的字段类型是一致的。

七、执行计划

explain + 查询 SQL - 用于显示 SQL 执行信息参数，根据参考信息可以进行 SQL 优化

  1. mysql> explain select * from userinfo;
  2. +----+-------------+----------+------+---------------+------+---------+------+---------+-------+
  3. | id | select_type | table    | type | possible_keys | key  | key_len | ref  | rows    | Extra |
  4. +----+-------------+----------+------+---------------+------+---------+------+---------+-------+
  5. |  1 | SIMPLE      | userinfo | ALL  | NULL          | NULL | NULL    | NULL | 2973016 | NULL  |
  6. +----+-------------+----------+------+---------------+------+---------+------+---------+-------+
  9. mysql> explain select * from (select id,name from userinfo where id <20) as A;
  10. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  11. | id | select_type | table      | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
  12. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  13. |  1 | PRIMARY     | <derived2> | ALL   | NULL          | NULL    | NULL    | NULL |   19 | NULL        |
  14. |  2 | DERIVED     | userinfo   | range | PRIMARY       | PRIMARY | 4       | NULL |   19 | Using where |
  15. +----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
  16. rows in set (0.05 sec)

参数说明：

  1. select_type：
  2. 查询类型
  3.     SIMPLE          简单查询
  4.     PRIMARY         最外层查询
  5.     SUBQUERY        映射为子查询
  6.     DERIVED         子查询
  7.     UNION           联合
  8.     UNION RESULT    使用联合的结果
  10. table：
  11.     正在访问的表名
  12. type：
  13.     查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
  14.     ALL 全表扫描，对于数据表从头到尾找一遍
  15.     select * from userinfo;
  16.     特别的：如果有limit限制，则找到之后就不在继续向下扫描
  17.        select * from userinfo where email = 'alex112@oldboy'
  18.        select * from userinfo where email = 'alex112@oldboy' limit 1;
  19.        虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
  22. INDEX ： 全索引扫描，对索引从头到尾找一遍
  23.     select nid from userinfo;
  26. RANGE： 对索引列进行范围查找
  27.     select *  from userinfo where name < 'alex';
  28.     PS:
  29.         between and
  30.         in
  31.         >   >=  <   <=  操作
  32.         注意：!= 和 > 符号
  35. INDEX_MERGE： 合并索引，使用多个单列索引搜索
  36.     select *  from userinfo where name = 'alex' or nid in (11,22,33);
  39. REF： 根据索引查找一个或多个值
  40.     select *  from userinfo where name = 'alex112';
  43. EQ_REF： 连接时使用primary key 或 unique类型
  44.     select userinfo2.id,userinfo.name from userinfo2 left join tuserinfo on userinfo2.id = userinfo.id;
  47. CONST：常量
  48.     表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
  49.     select id from userinfo where id = 2 ;
  52. SYSTEM：系统
  53.     表仅有一行(=系统表)。这是const联接类型的一个特例。
  54.     select * from (select id from userinfo where id = 1) as A;
  57. possible_keys：可能使用的索引
  60. key：真实使用的
  63. key_len： MySQL中使用索引字节长度
  66. rows： mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
  69. extra：
  70.     该列包含MySQL解决查询的详细信息
  71.     "Using index"
  72.         此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
  73.     "Using where"
  74.         这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益于不同的索引。
  75.     "Using temporary"
  76.         这意味着mysql在对查询结果排序时会使用一个临时表。
  77.     "Using filesort"
  78.         这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
  79.     "Range checked for each record(index map: N)"
  80.         这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的

重点：

查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const

尽量使用主键索引，它的查询速度是最快的。

预估 sql 语句的查询性能

八、慢日志记录

开启慢查询日志，可以让 MySQL 记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

  1. (1) 进入MySql 查询是否开了慢查询
  2.     show variables like 'slow_query%';
  3.     参数解释：
  4.      slow_query_log 慢查询开启状态  OFF 未开启 ON 为开启
  5.     slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
  8. （2）查看慢查询超时时间
  9.     show variables like 'long%';
  10.     ong_query_time 查询超过多少秒才记录   默认10秒 
  13. （3）开启慢日志（1）（是否开启慢查询日志，1表示开启，0表示关闭。）
  14.     set global slow_query_log=1;
  15. （4）再次查看
  16.     show variables like '%slow_query_log%';
  19. （5）开启慢日志（2）：（推荐）
  20.     在my.cnf 文件中
  21.     找到[mysqld]下面添加：
  22.     slow_query_log =1
  23.     slow_query_log_file=C:\mysql-5.6.40-winx64\data\localhost-slow.log
  24.     long_query_time = 1
  27.     参数说明：
  28.     slow_query_log 慢查询开启状态  1 为开启
  29.     slow_query_log_file 慢查询日志存放的位置
  30.     long_query_time 查询超过多少秒才记录   默认10秒 修改为1秒

修改配置文件之后，需要重启 mysql 服务

  1. #如果是windows系统，以管理员身份打开cmd，运行下面的命令：
  2. C:\WINDOWS\system32>net stop mysql
  3. MySQL 服务正在停止..
  4. MySQL 服务已成功停止。
  7. C:\WINDOWS\system32>net start mysql
  8. MySQL 服务正在启动 .
  9. MySQL 服务已经启动成功。

执行一个超过 1 秒的 sql，查看慢日志文件

  1. #执行慢sql，超过1秒的
  2. mysql> select * from userinfo where name = 999;
  3. Empty set, 65535 warnings (1.77 sec)
  6. #查看慢日志文件路径
  7. mysql> show variables like '%slow_query_log%';
  8. +---------------------+--------------------------------------------------------------------------+
  9. | Variable_name       | Value                                                                    |
  10. +---------------------+--------------------------------------------------------------------------+
  11. | slow_query_log      | ON                                                                       |
  12. | slow_query_log_file | D:\Program Files (x86)\mysql-5.7.22-winx64\data\DESKTOP-CFMVJ8G-slow.log |
  13. +---------------------+--------------------------------------------------------------------------+
  14. rows in set, 1 warning (0.00 sec)
  17. #打开文件DESKTOP-CFMVJ8G-slow.log，内容如下：
  20. MySQL, Version: 5.7.22 (MySQL Community Server (GPL)). started with:
  21. TCP Port: 3306, Named Pipe: MySQL
  22. Time                 Id Command    Argument
  23. MySQL, Version: 5.7.22-log (MySQL Community Server (GPL)). started with:
  24. TCP Port: 3306, Named Pipe: (null)
  25. Time                 Id Command    Argument
  26. # Time: 2018-06-19T12:19:53.239515Z
  27. # User@Host: root[root] @ localhost [::1]  Id:     2
  28. # Query_time: 1.767427  Lock_time: 0.003748 Rows_sent: 0  Rows_examined: 3000000
  29. use db1;
  30. SET timestamp=1529410793;
  31. select * from userinfo where name = 999;
  34. 可以看到Query_time的时间为1.767427秒

九、分页性能相关方案

先回顾一下，如何取当前表中的前 10 条记录，每十条取一次…….

  1. 第1页：
  2. select * from userinfo limit 0,10;
  3. 第2页：
  4. select * from userinfo limit 10,10;
  5. 第3页：
  6. select * from userinfo limit 20,10;
  7. 第4页：
  8. select * from userinfo limit 30,10;
  9. ......
  10. 第2000010页
  11. select * from userinfo limit 2000000,10;
  14. PS:会发现，越往后查询，需要的时间约长，是因为越往后查，全文扫描查询，会去数据表中扫描查询。

最优的解决方案

(1) 只有上一页和下一页

语法：

下一页：

  1. select * from userinfo where id>max_id limit 10;

上一页：

  1. select * from userinfo where id<min_id order by id desc limit 10;

举例

  1. 下一页：
  2. mysql> select * from userinfo where id > 20010 limit 10;
  3. +-------+-----------+--------+------------------+
  4. | id    | name      | gender | email            |
  5. +-------+-----------+--------+------------------+
  6. | 20011 | alex20011 | male   | egon20011@oldboy |
  7. | 20012 | alex20012 | male   | egon20012@oldboy |
  8. | 20013 | alex20013 | male   | egon20013@oldboy |
  9. | 20014 | alex20014 | male   | egon20014@oldboy |
  10. | 20015 | alex20015 | male   | egon20015@oldboy |
  11. | 20016 | alex20016 | male   | egon20016@oldboy |
  12. | 20017 | alex20017 | male   | egon20017@oldboy |
  13. | 20018 | alex20018 | male   | egon20018@oldboy |
  14. | 20019 | alex20019 | male   | egon20019@oldboy |
  15. | 20020 | alex20020 | male   | egon20020@oldboy |
  16. +-------+-----------+--------+------------------+
  17. rows in set (0.00 sec)
  20. 上一页：
  21. mysql> select * from userinfo where id<20011 order by id desc limit 10;
  22. +-------+-----------+--------+------------------+
  23. | id    | name      | gender | email            |
  24. +-------+-----------+--------+------------------+
  25. | 20010 | alex20010 | male   | egon20010@oldboy |
  26. | 20009 | alex20009 | male   | egon20009@oldboy |
  27. | 20008 | alex20008 | male   | egon20008@oldboy |
  28. | 20007 | alex20007 | male   | egon20007@oldboy |
  29. | 20006 | alex20006 | male   | egon20006@oldboy |
  30. | 20005 | alex20005 | male   | egon20005@oldboy |
  31. | 20004 | alex20004 | male   | egon20004@oldboy |
  32. | 20003 | alex20003 | male   | egon20003@oldboy |
  33. | 20002 | alex20002 | male   | egon20002@oldboy |
  34. | 20001 | alex20001 | male   | egon20001@oldboy |
  35. +-------+-----------+--------+------------------+
  36. rows in set (0.33 sec)

因为使用 where id<min_id，默认是从 1 开始的。但是 min_id 是一个中间值，所以需要 order by id desc，才能得到想要的 id，最后使用 limit 取出指定的长度，就是最终的结果。

(2) 中间有页码的情况

语法：

  1. select * from (select * from userinfo where id > pre_max_id limit (cur_max_id-pre_max_id))*10) as A order by id desc limit 10;

举例：

  1. #比如现在是2001页
  2. mysql> select * from userinfo where id > 20010 limit 10;
  3. +-------+-----------+--------+------------------+
  4. | id    | name      | gender | email            |
  5. +-------+-----------+--------+------------------+
  6. | 20011 | alex20011 | male   | egon20011@oldboy |
  7. | 20012 | alex20012 | male   | egon20012@oldboy |
  8. | 20013 | alex20013 | male   | egon20013@oldboy |
  9. | 20014 | alex20014 | male   | egon20014@oldboy |
  10. | 20015 | alex20015 | male   | egon20015@oldboy |
  11. | 20016 | alex20016 | male   | egon20016@oldboy |
  12. | 20017 | alex20017 | male   | egon20017@oldboy |
  13. | 20018 | alex20018 | male   | egon20018@oldboy |
  14. | 20019 | alex20019 | male   | egon20019@oldboy |
  15. | 20020 | alex20020 | male   | egon20020@oldboy |
  16. +-------+-----------+--------+------------------+
  17. #现在需要跳转到2003页
  18. mysql> select * from userinfo where id > 20030 limit 10;
  19. +-------+-----------+--------+------------------+
  20. | id    | name      | gender | email            |
  21. +-------+-----------+--------+------------------+
  22. | 20031 | alex20031 | male   | egon20031@oldboy |
  23. | 20032 | alex20032 | male   | egon20032@oldboy |
  24. | 20033 | alex20033 | male   | egon20033@oldboy |
  25. | 20034 | alex20034 | male   | egon20034@oldboy |
  26. | 20035 | alex20035 | male   | egon20035@oldboy |
  27. | 20036 | alex20036 | male   | egon20036@oldboy |
  28. | 20037 | alex20037 | male   | egon20037@oldboy |
  29. | 20038 | alex20038 | male   | egon20038@oldboy |
  30. | 20039 | alex20039 | male   | egon20039@oldboy |
  31. | 20040 | alex20040 | male   | egon20040@oldboy |
  32. +-------+-----------+--------+------------------+
  33. rows in set (0.00 sec)
  36. #根据语法计算，就是下面这种。
  37. select * from (select * from userinfo where id > 20010 limit (20040-20010))*10) as A order by id desc limit 10;
  40. #计算减法和乘法之后，就是下面的sql
  41. mysql> select * from (select * from userinfo where id > 20010 limit 30) as A order by id desc limit 10;
  42. +-------+-----------+--------+------------------+
  43. | id    | name      | gender | email            |
  44. +-------+-----------+--------+------------------+
  45. | 20040 | alex20040 | male   | egon20040@oldboy |
  46. | 20039 | alex20039 | male   | egon20039@oldboy |
  47. | 20038 | alex20038 | male   | egon20038@oldboy |
  48. | 20037 | alex20037 | male   | egon20037@oldboy |
  49. | 20036 | alex20036 | male   | egon20036@oldboy |
  50. | 20035 | alex20035 | male   | egon20035@oldboy |
  51. | 20034 | alex20034 | male   | egon20034@oldboy |
  52. | 20033 | alex20033 | male   | egon20033@oldboy |
  53. | 20032 | alex20032 | male   | egon20032@oldboy |
  54. | 20031 | alex20031 | male   | egon20031@oldboy |
  55. +-------+-----------+--------+------------------+
  58. #如果觉得id排序，页面展示有问题时，可以对上面的sql，增加一个排序
  59. select * from (
  60. select * from (select * from userinfo where id > 20010 limit 30) as A order by id desc limit 10) as foo
  61. ORDER BY id asc;
  64. #结果输出：
  65. +-------+-----------+--------+------------------+
  66. | id    | name      | gender | email            |
  67. +-------+-----------+--------+------------------+
  68. | 20031 | alex20031 | male   | egon20031@oldboy |
  69. | 20032 | alex20032 | male   | egon20032@oldboy |
  70. | 20033 | alex20033 | male   | egon20033@oldboy |
  71. | 20034 | alex20034 | male   | egon20034@oldboy |
  72. | 20035 | alex20035 | male   | egon20035@oldboy |
  73. | 20036 | alex20036 | male   | egon20036@oldboy |
  74. | 20037 | alex20037 | male   | egon20037@oldboy |
  75. | 20038 | alex20038 | male   | egon20038@oldboy |
  76. | 20039 | alex20039 | male   | egon20039@oldboy |
  77. | 20040 | alex20040 | male   | egon20040@oldboy |
  78. +-------+-----------+--------+------------------+
  79. rows in set (0.00 sec)