《MySQL数据库》分页查询及其优化、视图、索引

一、分页查询

在做查询操作时，如果记录的数量比较大，在一页内查看起来特别不方便，此时我们可以进行分页查询。

关键字：limit

1、基本语法：

select  *  from  tableName  limit  [begin,]  count;
解析：
begin：记录的开始行数， 即偏移量(可以理解为下标)；可以不写，默认从0开始，而不是1.
count：每页的最大记录数。

需求1：每页10条记录，查询第3页的数据。 分析：第3页的数据是从第21条记录开始的，而第21条记录的偏移量是20。

mysql > select ename, job, sal from emp limit 20,10;

需求2：每页10条记录，查询第一页的数据。

写法1：
mysql > select ename, job,  sal from emp limit 10;
写法2：
mysql > select ename, job, sal from emp limit 0,10;

2、排序后分页

在实际开发中，我们的需求都是按照某字段排序后，再分页的，很少使用数据库默认的插入顺序排序。排序有个好处就是确定数据的稳定性，不然有可能发生这样的情况(如某次返回1,2,3,另外的一次返回2,1,3)。

需求1：按照工资降序，查询第一页的数据，每页5条记录。

mysql > select empno, ename, job, sal from emp order by sal desc limit 5;

3、分页查询优化

一个数据表的记录数在百/千级别的情况下，我们不会追求分页查询的效率，因此普通的分页语法就够用了。但是，当记录数量达到上万级别，尤其是百万级别以上的表中，普通分页查询语句因为会从头遍历数据，因此分页查询的效率会会随着偏移量的增加而明显降低。

所以，我们要优化分页查询语句。这里有个前提，表中必须有主键id,而且是递增的，即自增长的。

我看了其他网友整理的分页优化，方法好多种(7种的也有，八种的也有)。其实有些方法看似不同，其实原理是一样的，因此我重写整理了以下四种方法。

1) 使用主键id来优化

语句样式1：SELECT * FROM tableName WHERE id_pk > ((pageNum-1) * pageSize) ORDER BY id_pk ASC LIMIT pageSize解析：因为依赖主键自增，可直接定位主键偏移量，避免了从头遍历，因此效率非常高。但是只支持升序，不支持降序。

语句样式2：SELECT * FROM tableName WHERE id_pk between 100000 and 10200 ORDER BY id_pk ASC LIMIT pageSize解析：照比样式1，就是确定了主键id的范围，也不需要从头遍历，效率高。

2）使用主键id同时使用prepare。

语句样式：prepare pre_d from 'select * from testpage where tid> (?*?) order by tid limit ?';

set @a = 9;

set @b = 10;

execute pre_d using @a,@b,@b;

Prepare的原理
Prepare SQL产生的原因。首先从mysql服务器执行sql的过程开始讲起，SQL执行过程包括以下阶段 词法分析->语法分析->语义分析->执行计划优化->执行。词法分析->语法分析这两个阶段我们称之为硬解析。词法分析识别sql中每个词，语法分析解析SQL语句是否符合sql语法，并得到一棵语法树（Lex）。对于只是参数不同，其他均相同的sql，它们执行时间不同但硬解析的时间是相同的。而同一SQL随着查询数据的变化，多次查询执行时间可能不同，但硬解析的时间是不变的。对于sql执行时间较短，sql硬解析的时间占总执行时间的比率越高。而对于淘宝应用的绝大多数事务型SQL，查询都会走索引，执行时间都比较短。因此淘宝应用db sql硬解析占的比重较大。
Prepare的出现就是为了优化硬解析的问题。Prepare在服务器端的执行过程如下
1） Prepare 接收客户端带”?”的sql, 硬解析得到语法树(stmt->Lex), 缓存在线程所在的preparestatement cache中。此cache是一个HASH MAP. Key为stmt->id. 然后返回客户端stmt->id等信息。
2） Execute 接收客户端stmt->id和参数等信息。注意这里客户端不需要再发sql过来。服务器根据stmt->id在preparestatement cache中查找得到硬解析后的stmt, 并设置参数，就可以继续后面的优化和执行了。
注意：Prepare在execute阶段可以节省硬解析的时间。如果sql只执行一次，且以prepare的方式执行，那么sql执行需两次与服务器交互（Prepare和execute）, 而以普通（非prepare）方式，只需要一次交互。这样使用prepare带来额外的网络开销，可能得不偿失。我们再来看同一sql执行多次的情况，比如以prepare方式执行10次，那么只需要一次硬解析。这时候 额外的网络开销就显得微乎其微了。因此prepare适用于频繁执行的SQL。
Prepare的另一个作用是防止sql注入，不过这个是在客户端jdbc通过转义实现的，跟服务器没有关系。

3）使用子查询

语法样式：select * from orders_history where id>=(select id from orders_history order by id limit 100000,1) limit 100;

4） 存储过程

适应场景: 大数据量.

原因: 把操作封装在服务器，相对更快一些。会存储过程的童鞋支持这种用法。

二、视图view

1、概念

视图被称之为虚表，即虚拟的表，本身并不包含任何数据，它只包含映射到基表的一个查询语句。当基表的数据发生变化，视图的逻辑数据可能会产生变化。(说的通俗点，就是表的部分投影)

2、作用

a.简单化。使用视图的用户完全不需要关心后面对应的表的结构、关联条件和筛选条件，对用户来说已经是过滤好的复合条件的结果集。

b.安全化。使用视图的用户只能访问他们被允许查询的结果集，对表的权限管理并不能限制到某个行某个列，但是通过视图就可以简单的实现(可以隐藏基表中的某些字段)。

c.数据独立。一旦视图的结构确定了，可以屏蔽表结构变化对用户的影响，源表增加列对视图没有影响；源表修改列名，则可以通过修改视图来解决，不会造成对访问者的影响。

总而言之，使用视图的大部分情况是为了保障数据安全性，提高查询效率。

3、创建视图

语法：create [or replace] view ViewName as select语句;

删除语法：drop view viewName;

4、视图的分类

a.简单视图

映射的select语句基于单表查询，不包含任何函数运算的，叫简单视图

b.复杂视图

映射的select语句基于单表查询，但是包含函数运算、分组查询等复杂操作，叫复杂视图

c.连接视图

映射的select语句基于多表查询，叫连接视图

5、对视图的DQL操作

与查询表操作一致。

6、对视图的DML操作

视图本身并不包含数据，只是基表数据的一个逻辑映射；因此对视图进行DML操作时，实际上是对基表进行DML操作，因此需要注意基表的相关约束条件限制。PS:只能对简单视图进行DML操作。

1）如insert操作时，基表中定义了非空约束的字段，但是视图映射的select子句中并不包含此字段，即相关字段对视图不可见，因此无法完成insert操作
2）再如delete操作，也只能删除视图中能查到的数据。

7、对视图DML操作的影响

a.通过视图进行DML操作，一定会对基表有影响。    
b.通过对基表进行DML操作，可能会对视图有影响。

8、with check option:对视图的一种检查约束选项

如果在创建视图有此选项时，表示，只能对视图DML操作可见数据。反之，对视图不可见的数据，是不可以通过视图进行DML操作的。

三、索引(Index)

1.概念

索引是一种允许直接访问数据表中某一记录的树形结构，是为了提高查询效率而引入，是独立于表的对象。索引记录中存有索引关键字和指向表中数据的指针(地址) ps:相当于一本书的目录。

2.特点

a.索引一旦被创建，将由数据库自动维护，查询语句中不需要指定使用哪个索引
b.表中的每个字段都可以设置相应的索引

3.优缺点

优点:提高查询速度。
缺点:占空间,每次进行DML操作时，数据库都要(自动)重新维护索引，降低效率.

总体来说，表中有索引可以提高效率，但不是索引越多越好。当表中的数据量比较小时，无需索引(因为直接查询可以比使用索引更快)，当某个字段的值比较少时，也不需要索引，如性别字段只有'f','m'.只有当数据量比较大，和字段值多时，可以使用索引。

4、索引的应用

当where子句中的字段 有相关的索引时，就会自动应用索引。

5、索引的创建

方式1：

create index indexName on tableName(fieldName);

方式2：建表时指定索引

        create table t_301(
            tid int,
            tname varchar(20),
            gender varchar(1),
            index [indexName] (fieldName)
         );

方式3：

alter table tableName add unique index indexName (fieldName);