MySQL之索引

索引是数据库中用来提高性能的最常用的工具,本次博客就来介绍一下索引,mysql版本5.7.19。

 

索引概述

所有MySQL列类型都可以被索引,对相关的列使用索引是可以提高SELECT操作性能的最佳途径。MyISAM和InnoDB存储引擎默认是BTREE索引。其实索引就像是一个字典的目录,你可以通过索引快速的定位到行的位置,索引会保存到额外的文件中。

 

索引的存储分类和作用

索引是在MySQL的存储引擎层中实现的,而不是在服务器层实现的,所以每种存储引擎的索引不一定完全相同,也不是所有的存储引擎都支持所有的索引类型。

MySQL目前支持以下4种索引:

B-tree索引:最常见的索引类型,大部分存储引擎都支持BTREE索引
HASH索引:只有MEMORY存储引擎支持,使用的场景比较简单
R-tree索引(空间索引):空间索引是MyISAM的一个特殊索引类型,主要用于地理空间数据类型,使用的较少
Full-text(全文索引):全文索引也是MyISAM的一个特殊索引类型,主要用于全文索引

三个常用引擎支持的索引:

B-tree索引和HASH索引是比较常用的索引,HASH比较简单,也只有Memory和Heap引擎支持,Hash索引适合键-值的查询,且比B-Tree索引更快,但是hash索引不支持范围的查询,即如果Memory和heap引擎在where后面如果不使用“=”号的话,就不会使用Hash索引去查找,索引Memory和Heap只有在“=”的条件下才会使用Hash索引。

B-tree索引构造类似于二叉树,能根据键值提供一行或者一个行集的快速访问,通常只需要很少的读操作就可以找到正确的行。B-tree的B不代表一个二叉树,而是一个平衡树(balanced),结构如下:

 

 索引的存在可以加速查找,有的时候可以起到约束的作用。

 

索引的创建,删除和修改

创建索引

CREATE INDEX index_name ON table(column1,column2,...columnN); --创建普通的索引
CREATE UNIQUE INDEX index_name ON table(column1,column2,...columnN); --创建唯一索引
ALTER TABLE table ADD PRIMARY KEY(column); --增加主键索引

删除索引

DROP INDEX index_name ON table  --删除普通的索引
ALTER TABLE tabel DROP INDEX index_name --删除索引
DROP UNIQUE INDEX index_name ON table --删除唯一索引
ALTER TABLE table DROP PRIMARY KEY; --删除主键索引
ALTER TABLE table MODIFY column INT,DROP PRIMARY KEY; --删除主键索引

修改

对于MySQL5.7及以上版本,可以使用RENAME:

ALTER TABLE table_name RENAME INDEX old_index_name TO new_index_name;

对于MySQL5.7以前的版本,只能先删除再增加了:

ALTER TABLE table_name DROP INDEX old_index_name;
ALTER TABLE table_name ADD INDEX new_index_name(column_name);

举例:

mysql> create index name_index on t3(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> show index from t3 \G;
*************************** 1. row ***************************
        Table: t3
   Non_unique: 1
     Key_name: name_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)
mysql>
mysql>
mysql> alter table t3 rename index name_index to new_name_index;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t3 \G;
*************************** 1. row ***************************
        Table: t3
   Non_unique: 1
     Key_name: new_name_index
 Seq_in_index: 1
  Column_name: name
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: YES
   Index_type: BTREE
      Comment:
Index_comment:
1 row in set (0.00 sec)
修改索引名称

 

通过EXPLAIN分析低效SQL的执行计划

现在有表如下:

mysql> show create table t1 \G;
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` char(20) DEFAULT NULL,
  `email` char(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1000001 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> select count(*) from t1;
+----------+
| count(*) |
+----------+
|  1000000 |
+----------+
1 row in set (1.25 sec)

id列为主键索引,都说索引可以加速查找,那么来测试一下他是否可以加速查找:

mysql> select * from t1 where id=8888;
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
| 8888 | test8888 | test8888@qq.com |
+------+----------+-----------------+
1 row in set (0.00 sec)

mysql> select * from t1 where name='test8888';
+------+----------+-----------------+
| id   | name     | email           |
+------+----------+-----------------+
| 8888 | test8888 | test8888@qq.com |
+------+----------+-----------------+
1 row in set (1.24 sec)

通过以上例子完全可以看出索引的存在可以加速行数据的查找。

这里可以通过explain命令来分析SQL的执行计划:

mysql> explain select * from t1 where id=8888;
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

各个字段的意思:

id:数字越大越先执行,当数字相同的时候,就从上往下执行,如果为null就表示是一个结果集,不需要使用它来进行查询
select_type:常见的如下
    simple:简单表,即不使用表连接或者子查询,有连接查询时,外层的查询为simple,有且只有一个;
    primary:需要union操作或者含有子查询的select,位于最外层的单位查询的select_type即为primary,有且只有一个;
    union:UNiON中的第二个或者后面的查询语句;
    subquery:除了from字句中包含的子查询外,其他地方出现的子查询都可能是subquery;
    除以上之外还有:dependent union,union result,dependent subquery,derived。
table:显示查询表名,如果使用的是别名,那么这里就是别名;
type:表示MySQL在表中找到所需行的方式,或者叫访问类型,常见的如下:
    +-----+--------+-------+------+--------+---------------+-------+
    | ALL | index  | range | ref  | eq_ref | const,system  | NULL  |
    +-----+--------+-------+------+--------+---------------+-------+
    从左至右,性能由最差到最好。
possible_keys:表示查询时可能使用的索引;
key:表示实际使用的索引;
partitions:显示SQL所需要访问的分区名字;
key_len:使用到所以字段的长度;
rows:预估扫描行的数量;
ref:如果是使用的常数等值查询,这里会显示const;
filtered:表示存储引擎返回的数据在server层过滤后,剩下多少满足查询的记录数量的比例,注意是百分比;
extra:常见的如下:
    distinct:在select部分使用了distinc关键字;
    no tables used:不带from字句的查询;
    using filesort:排序时无法使用到索引时;
    using index:查询时不需要回表查询,直接通过索引就可以获取查询的数据;
    using temporary:表示使用了临时表存储中间结果;
    using where5.6之前:存储引擎只能根据限制条件扫描数据并返回,然后再回表进行过滤返回真正的查询的数据;
        5.6之后:支持ICP特性,把条件限制都下推到存储引擎层来完成,这样就能降低不必要的IO访问。
filtered:
explain各字段的意思

 

最左前缀匹配

创建索引如下:

mysql> create index index1 on t1(name,email,type);
Query OK, 0 rows affected (17.45 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc t1;
+-------+-----------+------+-----+---------+----------------+
| Field | Type      | Null | Key | Default | Extra          |
+-------+-----------+------+-----+---------+----------------+
| id    | int(11)   | NO   | PRI | NULL    | auto_increment |
| name  | char(20)  | YES  | MUL | NULL    |                |
| email | char(100) | YES  |     | NULL    |                |
| type  | int(11)   | YES  |     | NULL    |                |
| dep   | int(11)   | YES  |     | NULL    |                |
+-------+-----------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

那么最左前缀匹配是什么意思呢?

这里创建了一个名为index1的索引,包含三列,从左至右为:name,email,type,最左前缀匹配的意思就是,查询的时候条件必须包含name列才会使用索引去查找,否则就会全文去查询。

举例:

mysql> explain select * from t1 where name='test8888' and email='test8888@qq.com' and type=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index1        | index1 | 367     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql>
mysql> explain select * from t1 where name='test8888' and email='test8888@qq.com';
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index1        | index1 | 362     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where name='test8888' and  type=1;
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index1        | index1 | 61      | const |    1 |    10.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where name='test8888';
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index1        | index1 | 61      | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where  email='test8888@qq.com' and type=1;  --当不包含name的时候,就不会使用索引查找
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 990448 |     1.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where  email='test8888@qq.com';
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | t1    | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 990448 |    10.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t1 where  email='test8888@qq.com' and  name='test8888';  --name不必在条件语句的最左边
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key    | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | t1    | NULL       | ref  | index1        | index1 | 362     | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+--------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
最左前缀匹配例子
这里引出一个小概念:
组合索引和索引合并
组合索引:比如之前例子中create index index1 on t1(name,email,type),index1就是一个组合索引;
索引合并:索引合并,拿上一个例子来看,创建了一个索引包含了3个列,这个叫组合索引,如果我们针对每一个列创建一个索引,在使用查询语句的时候使用多个索引,即把多个单列索引合并使用,这就叫索引的合并。

那么它们的效率如何呢?

如果在查询语句经常使用的是多个列一起查询,建议使用组合索引,如果经常只查单个列,建议使用索引合并这种形式,针对单个列创建索引。

还有一个名称是覆盖索引,意思是在索引文件中直接获取数据。

 

正确的命中索引

数据库中添加了索引的确会使查询的速度提高,但是也要避免以下情况,即使建立了索引也不会生效,如上面介绍到的不使用最左匹配也是一种:

like '%xx':以%开头的LIKE查询不能够使用索引;
使用函数:比如select * from tb1 where reverse(name) = 'test8888';
or:当or条件中有未建立索引的列才失效;
类型不一致:如果列是字符串类型,传入条件是必须用引号引起来;
!=:使用不等于的时候,特殊情况:如果是主键还是会走索引;
范围查询:如果是主键或者索引是整数类型,则还是会走索引;
order by:当根据索引排序的时候,选择的映射如果不是索引,则不走索引,特殊情况,如果对主键排序,则还是走索引;
最左前缀匹配。
可能不会命中索引的情况

其他还需要注意的:

避免使用select *
count(1)或count(列) 代替 count(*)
创建表时尽量时 char 代替 varchar
表的字段顺序固定长度的字段优先
组合索引代替多个单列索引(经常使用多个条件查询时)
尽量使用短索引
使用连接(JOIN)来代替子查询(Sub-Queries)
连表时注意条件类型需一致
索引散列值(重复少)不适合建索引,例:性别不适合
避免事项

 

show status命令

show status可以了解各种SQL的执行频率。

下面的命令显示当前session中所有的统计参数的值:
mysql> show status like 'com_%';   --如果想查看全局的,可以在status前面加上global
+-----------------------------+-------+
| Variable_name               | Value |
+-----------------------------+-------+
| Com_admin_commands          | 0     |
| Com_assign_to_keycache      | 0     |
| Com_alter_db                | 0     |
| Com_alter_db_upgrade        | 0     |
| Com_alter_event             | 0     |
| Com_alter_function          | 0     |
| Com_alter_instance          | 0     |
| Com_alter_procedure         | 0     |
| Com_alter_server            | 0     |
| Com_alter_table             | 5     |
| Com_alter_tablespace        | 0     |
| Com_alter_user              | 0     |
| Com_analyze                 | 0     |
| Com_begin                   | 0     |
| Com_binlog                  | 0     |
| Com_call_procedure          | 0     |
| Com_change_db               | 1     |
| Com_change_master           | 0     |
| Com_change_repl_filter      | 0     |
| Com_check                   | 0     |
| Com_checksum                | 0     |
| Com_commit                  | 0     |
| Com_create_db               | 0     |
| Com_create_event            | 0     |
| Com_create_function         | 0     |
| Com_create_index            | 4     |
| Com_create_procedure        | 0     |
| Com_create_server           | 0     |
| Com_create_table            | 1     |
| Com_create_trigger          | 0     |
| Com_create_udf              | 0     |
| Com_create_user             | 0     |
| Com_create_view             | 0     |
| Com_dealloc_sql             | 0     |
| Com_delete                  | 0     |
| Com_delete_multi            | 0     |
| Com_do                      | 0     |
| Com_drop_db                 | 0     |
| Com_drop_event              | 0     |
| Com_drop_function           | 0     |
| Com_drop_index              | 2     |
| Com_drop_procedure          | 0     |
| Com_drop_server             | 0     |
| Com_drop_table              | 0     |
| Com_drop_trigger            | 0     |
| Com_drop_user               | 0     |
| Com_drop_view               | 0     |
| Com_empty_query             | 0     |
| Com_execute_sql             | 0     |
| Com_explain_other           | 0     |
| Com_flush                   | 0     |
| Com_get_diagnostics         | 0     |
| Com_grant                   | 0     |
| Com_ha_close                | 0     |
| Com_ha_open                 | 0     |
| Com_ha_read                 | 0     |
| Com_help                    | 0     |
| Com_insert                  | 2     |
| Com_insert_select           | 0     |
| Com_install_plugin          | 0     |
| Com_kill                    | 0     |
| Com_load                    | 0     |
| Com_lock_tables             | 0     |
| Com_optimize                | 0     |
| Com_preload_keys            | 0     |
| Com_prepare_sql             | 0     |
| Com_purge                   | 0     |
| Com_purge_before_date       | 0     |
| Com_release_savepoint       | 0     |
| Com_rename_table            | 0     |
| Com_rename_user             | 0     |
| Com_repair                  | 0     |
| Com_replace                 | 0     |
| Com_replace_select          | 0     |
| Com_reset                   | 0     |
| Com_resignal                | 0     |
| Com_revoke                  | 0     |
| Com_revoke_all              | 0     |
| Com_rollback                | 0     |
| Com_rollback_to_savepoint   | 0     |
| Com_savepoint               | 0     |
| Com_select                  | 42    |
| Com_set_option              | 0     |
| Com_signal                  | 0     |
| Com_show_binlog_events      | 0     |
| Com_show_binlogs            | 0     |
| Com_show_charsets           | 0     |
| Com_show_collations         | 0     |
| Com_show_create_db          | 0     |
| Com_show_create_event       | 0     |
| Com_show_create_func        | 0     |
| Com_show_create_proc        | 0     |
| Com_show_create_table       | 1     |
| Com_show_create_trigger     | 0     |
| Com_show_databases          | 1     |
| Com_show_engine_logs        | 0     |
| Com_show_engine_mutex       | 0     |
| Com_show_engine_status      | 0     |
| Com_show_events             | 0     |
| Com_show_errors             | 0     |
| Com_show_fields             | 3     |
| Com_show_function_code      | 0     |
| Com_show_function_status    | 0     |
| Com_show_grants             | 0     |
| Com_show_keys               | 5     |
| Com_show_master_status      | 0     |
| Com_show_open_tables        | 0     |
| Com_show_plugins            | 0     |
| Com_show_privileges         | 0     |
| Com_show_procedure_code     | 0     |
| Com_show_procedure_status   | 0     |
| Com_show_processlist        | 0     |
| Com_show_profile            | 0     |
| Com_show_profiles           | 0     |
| Com_show_relaylog_events    | 0     |
| Com_show_slave_hosts        | 0     |
| Com_show_slave_status       | 0     |
| Com_show_status             | 3     |
| Com_show_storage_engines    | 0     |
| Com_show_table_status       | 0     |
| Com_show_tables             | 1     |
| Com_show_triggers           | 0     |
| Com_show_variables          | 0     |
| Com_show_warnings           | 0     |
| Com_show_create_user        | 0     |
| Com_shutdown                | 0     |
| Com_slave_start             | 0     |
| Com_slave_stop              | 0     |
| Com_group_replication_start | 0     |
| Com_group_replication_stop  | 0     |
| Com_stmt_execute            | 0     |
| Com_stmt_close              | 0     |
| Com_stmt_fetch              | 0     |
| Com_stmt_prepare            | 0     |
| Com_stmt_reset              | 0     |
| Com_stmt_send_long_data     | 0     |
| Com_truncate                | 0     |
| Com_uninstall_plugin        | 0     |
| Com_unlock_tables           | 0     |
| Com_update                  | 2     |
| Com_update_multi            | 0     |
| Com_xa_commit               | 0     |
| Com_xa_end                  | 0     |
| Com_xa_prepare              | 0     |
| Com_xa_recover              | 0     |
| Com_xa_rollback             | 0     |
| Com_xa_start                | 0     |
| Com_stmt_reprepare          | 0     |
| Compression                 | OFF   |
+-----------------------------+-------+
149 rows in set (0.00 sec)
show status like 'com_%'

比较关心的几个参数:

Com_select:执行SELECT操作的次数,一次查询只累加1;
Com_insert:执行INSERT操作的次数,对于批量插入的INSERT操作,只累加一次;
Com_update:执行UPDATE操作的次数。
Com_delete:执行delete操作的次数。
下面的几个是针对InnoDB的:
Innodb_rows_read:SELECT查询返回的行数;
Innodb_rows_insert:执行INSERT操作插入的行数;
Innodb_rows_updated:执行UPDATE操作更新的行数;
Innodb_rows_deleted:执行DELETE操作删除的行数。
以下几个可以了解数据库的基本情况:
Connections:试图连接MySQL服务器的次数;
Uptime:服务器工作时间;
Slow_queries:慢查询的次数。

可以通过以下命令查看索引使用的情况:

mysql> show status like 'Handler_read%';
+-----------------------+----------+
| Variable_name         | Value    |
+-----------------------+----------+
| Handler_read_first    | 24       |
| Handler_read_key      | 32       |
| Handler_read_last     | 2        |
| Handler_read_next     | 13094235 |
| Handler_read_prev     | 0        |
| Handler_read_rnd      | 0        |
| Handler_read_rnd_next | 3000042  |
+-----------------------+----------+
7 rows in set (0.00 sec)

如果索引正确的工作,那么Handler_read_key的值应该会很高,Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引。这个值的含义是在数据文件中读取下一行的请求数。如果正进行大量的表扫描,就会较高,则通常说明表索引不正确或写入的查询没有利用索引。

 

优化分页查询

显示:
min_id 数据
... 数据 max_id 数据

第一种:只显示【上一页】或者【下一页】:

【下一页】:select * from table_name where id > max_id limit 10;
【上一页】:select * from table_name where id < min_id order by id desc limit 10;

示例:

下一页:
mysql> select * from t1 where id >0 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
|  1 | test1  | test1@qq.com  |    1 |    2 |
|  2 | test2  | test2@qq.com  |    1 |    2 |
|  3 | test3  | test3@qq.com  |    1 |    2 |
|  4 | test4  | test4@qq.com  |    1 |    2 |
|  5 | test5  | test5@qq.com  |    1 |    2 |
|  6 | test6  | test6@qq.com  |    1 |    2 |
|  7 | test7  | test7@qq.com  |    1 |    2 |
|  8 | test8  | test8@qq.com  |    1 |    2 |
|  9 | test9  | test9@qq.com  |    1 |    2 |
| 10 | test10 | test10@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >10 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 11 | test11 | test11@qq.com |    1 |    2 |
| 12 | test12 | test12@qq.com |    1 |    2 |
| 13 | test13 | test13@qq.com |    1 |    2 |
| 14 | test14 | test14@qq.com |    1 |    2 |
| 15 | test15 | test15@qq.com |    1 |    2 |
| 16 | test16 | test16@qq.com |    1 |    2 |
| 17 | test17 | test17@qq.com |    1 |    2 |
| 18 | test18 | test18@qq.com |    1 |    2 |
| 19 | test19 | test19@qq.com |    1 |    2 |
| 20 | test20 | test20@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id >30 limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 31 | test31 | test31@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 40 | test40 | test40@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

上一页:
mysql> select * from t1 where id < 41 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 40 | test40 | test40@qq.com |    1 |    2 |
| 39 | test39 | test39@qq.com |    1 |    2 |
| 38 | test38 | test38@qq.com |    1 |    2 |
| 37 | test37 | test37@qq.com |    1 |    2 |
| 36 | test36 | test36@qq.com |    1 |    2 |
| 35 | test35 | test35@qq.com |    1 |    2 |
| 34 | test34 | test34@qq.com |    1 |    2 |
| 33 | test33 | test33@qq.com |    1 |    2 |
| 32 | test32 | test32@qq.com |    1 |    2 |
| 31 | test31 | test31@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)

mysql> select * from t1 where id < 31 order by id desc limit 10;
+----+--------+---------------+------+------+
| id | name   | email         | type | dep  |
+----+--------+---------------+------+------+
| 30 | test30 | test30@qq.com |    1 |    2 |
| 29 | test29 | test29@qq.com |    1 |    2 |
| 28 | test28 | test28@qq.com |    1 |    2 |
| 27 | test27 | test27@qq.com |    1 |    2 |
| 26 | test26 | test26@qq.com |    1 |    2 |
| 25 | test25 | test25@qq.com |    1 |    2 |
| 24 | test24 | test24@qq.com |    1 |    2 |
| 23 | test23 | test23@qq.com |    1 |    2 |
| 22 | test22 | test22@qq.com |    1 |    2 |
| 21 | test21 | test21@qq.com |    1 |    2 |
+----+--------+---------------+------+------+
10 rows in set (0.00 sec)
上下页示例

之后将查询到的结果,倒序一下即可。

 
第二种:除了【上一页】和【下一页】之外,中间还有页码,例如每页最多10行,现在页面停留在100页,我们想跳到103页:
往上跳页:
select * from t1 where id in (select id from (select id from t1 where id < 当前页的最小id limit 30) as N limit 10)

往下跳页:

select * from t1 where id in (select id from (select id from t1 where id > 当前页的最大id limit 30) as N order by N.id desc limit 10)

 

参考书籍:《深入浅出MySQL》
参考链接:
http://www.cnblogs.com/wupeiqi/articles/5716963.html
posted @ 2017-09-27 01:10  liubinsh  阅读(362)  评论(0编辑  收藏  举报