(索引)使用索引进行查询优化；常用mysql优化

https://blog.csdn.net/qq_22238021/article/details/80922166

索引有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

建索引的几大原则

1、最左前缀匹配原则，非常重要的原则

对于多列索引，总是从索引的最前面字段开始，接着往后，中间不能跳过。比如创建了多列索引(name,age,sex)，会先匹配name字段，再匹配age字段，再匹配sex字段的，中间不能跳过。mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a = 1 and b = 2 and c > 3 and d = 4，如果建立（a,b,c,d）顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

2、尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录。（比如，我们会选择学号做索引，而不会选择性别来做索引。）

3、=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立（a,b,c）索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。

4、索引列不能参与计算，保持列“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

例如：select * from users where YEAR(adddate)<2007，将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成：select * from users where adddate<'2007-01-01'。

比如：Flistid+1>‘2000000608201108010831508721‘。原因很简单，假如索引列参与计算的话，那每次检索时，都会先将索引计算一次，再做比较，显然成本太大。

5、尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

6. 索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

7. 使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

8. 索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

9. like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

最后总结一下，MySQL只对以下操作符才使用索引：<,<=,=,>,>=,between,in,以及某些时候的like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引，不过除非是数据量真的很多，否则过多的使用索引也不是那么好玩的，比如我刚才针对text类型的字段创建索引的时候，系统差点就卡死了。

使用索引优化查询

使用索引的典型场景

1、匹配全值

对索引中所有列都指定具体值，即对索引中的所有列都有等值匹配的条件。

1. #设置组合索引（rental_date,inventory_id,customer_id）为唯一索引。

2. EXPLAIN

3. SELECT

4. *

5. FROM

6. rental

7. WHERE rental_date = '2005-05-25 17:22:10'

8. AND inventory_id = 373

9. AND customer_id = 343 ;

2、匹配值的范围查询

对索引的值能够进行范围查找。

1. #设置索引idx_fk_customer_id(customer_id)

2. EXPLAIN

3. SELECT

4. *

5. FROM

6. rental

7. WHERE customer_id >= 373

8. AND customer_id < 400 ;

类型type为range说明优化器选择范围查询，索引key为idx_fk_customer_id说明优化器选择索引idx_fk_customer_id来加速访问，Extra为using where说明优化器除了利用索引加速访问外，还需要根据索引回表查询数据。

3、匹配最左前缀

仅仅使用索引中的最左边列进行查询。比如组合索引（col1,col2,col3）能够被col1，col1+col2，col1+col2+col3的等值查询利用到的。

1. #创建索引idx_payment_date(payment_date,amount,last_update);

2. EXPLAIN

3. SELECT

4. *

5. FROM

6. payment

7. WHERE payment_date = '2006-02-14 15:16:03'

8. AND last_update = '2006-02-15 22:12:32' ;

从结果可以看出利用了索引，但又row为182行，所有只使用了部分索引。

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. payment

6. WHERE amount = 3.98

7. AND last_update = '2006-02-15' ;

从结果看出，这次查询没有利用索引，进行了全表查找。

4、仅对索引进行查询

当查询列都在索引字段中。即select中的列都在索引中。

1. EXPLAIN

2. SELECT

3. last_update

4. FROM

5. payment

6. WHERE payment_date = '2005-08-19 21:21:47'

7. AND amount = 4.99 ;

extra部分Using index,说明不需要通过索引回表，Using index就是平时说的覆盖索引扫描（即找到索引，就找到了要查询的结果，不用再回表查找了）。

5、匹配列前缀

仅仅使用索引的第一列，并且只包含索引第1列的开头部分进行查找。

1. #创建索引idx_title_desc_part(title(10)，description(20));

2. EXPLAIN

3. SELECT

4. title

5. FROM

6. film_text

7. WHERE title LIKE 'AFRICAN%' ;

6、索引部分等值匹配，部分范围匹配

1. EXPLAIN

2. SELECT

3. inventory_id

4. FROM

5. rental

6. WHERE rental_date = '2006-02-14 15:16:03'

7. AND customer_id >= 300

8. AND customer_id <= 400 ;

type=ref，说明使用了索引。key为idx_rental_date说明优化器选择使用索引加速查询，同时由于只查询索引字段inventory_id，故Extra部分有using index，表示查询使用了覆盖索引扫描。

7、若列名是索引，则使用column_name is null就会使用索引

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. payment

6. WHERE rental_id IS NULL ;

索引存在但不能使用索引的典型场景

1、以%开头的like查询

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. actor

6. WHERE last_name LIKE '%NI%' ;

因为B-Tree索引的结构，所以以%开头的查询自然没法使用索引。InnoDB的表都是聚簇表，一般索引都会比表小，扫描索引比扫描表更快，而InnoDB表上二级索引idx_last_name实际上存储字段last_name和主键actor_id，故先扫描二级索引idx_last_name获得满足条件last_name like '%NI%'的主键actor_id列表，之后根据主键回表去检索记录，这样避免了全表扫面演员表actor产生的大量IO请求

1. #优化

2. EXPLAIN

3. SELECT

4. *

5. FROM

6. (SELECT

7. actor_id

8. FROM

9. actor

10. WHERE last_name LIKE '%NI%') a,

11. actor b

12. WHERE a.actor_id = b.actor_id ;

2、数据类型出现隐式转化，不会使用索引

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. actor

6. WHERE last_name = 1 ;

1. #使用索引

2. EXPLAIN

3. SELECT

4. *

5. FROM

6. actor

7. WHERE last_name = '1' ;

3、组合索引，不满足最左原则，不使用符合索引

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. payment

6. WHERE amount = 3.98

7. AND last_update = '2006-02-15 22:12:32' ;

4、估计使用索引比全表扫描还慢，则不要使用索引

update film_text set title=concat('S',title);

如查询以“S”开头的标题的电影，返回记录比例比较大，mysql预估索引扫描还不如全表扫描。

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. film_text

6. WHERE title LIKE 'S%' ;

5、用or分割条件，若or前后只要有一个列没有索引，就都不会用索引

1. EXPLAIN

2. SELECT

3. *

4. FROM

5. payment

6. WHERE customer_id = 203

7. OR amount = 3.96 ;

 

应尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：
低效：select * from t_credit_detail where Flistid = '2000000608201108010831508721' or Flistid = '10000200001';

可以用下面这样的查询代替上面的 or 查询：
高效：select from t_credit_detail where Flistid = '2000000608201108010831508721' union all select from t_credit_detail where Flistid = '10000200001';

6 使用！= 或 <> 操作符时

尽量避免使用！= 或 <>操作符，否则数据库引擎会放弃使用索引而进行全表扫描。使用>或<会比较高效。

select * from t_credit_detail where Flistid != '2000000608201108010831508721'\G

7 对字段进行null值判断

应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：
低效：select * from t_credit_detail where Flistid is null ;

可以在Flistid上设置默认值0，确保表中Flistid列没有null值，然后这样查询：
高效：select * from t_credit_detail where Flistid =0;

避免select *

在解析的过程中，会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的，这意味着将耗费更多的时间。

所以，应该养成一个需要什么就取什么的好习惯。

优化子查询

使用连接查询（join）代替子查询可以提高查询效率。

MySQL从4.1版开始支持子查询（一个查询的结果作为另一个select子句的条件），子查询虽然灵活但执行效率不高，因为使用子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录，查询完毕后 再撤销这些临时表，因此子查询的速度会相应的受到影响。而连接查询不需要建立临时表其查询速度快于子查询！

优化插入记录的速度

innoDB引擎的表常见的优化方法

（1）、禁用唯一性检查

插入数据时，MySQL会对插入的记录进行唯一性校验。这种唯一性校验会降低插入记录的速度。为了降低这种情况对查询速度的影响可以在插入记录之前禁用唯一性检查，等到记录插入完毕后再开启。

Set  unique_check=0; 开启唯一性检查的语句如下：set  unique_checks=1;

（2）、禁用外键检查

插入数据之前禁止对外键的检查，数据插入完成之后再恢复对外键的检查。

Set foreign_key_checks=0; 恢复外键检查:set foreign_key_checks=1;

（3）、禁止自动提交

插入数据之前禁止事务的自动提交，数据导入之后，执行恢复自动提交操作。

禁止自动提交的语句 set autocommit=0;恢复自动提交:set autocommit=1;

Myisam引擎表常见的优化方法

（1）、禁用索引 alter table table_name disable keys

            导入数据  loading the data

    开启索引 alter table table_name enable keys

（2）禁用唯一性检查

（3）使用批量插入

（4）当需要批量导入数据时，使用load data infile

优化insert语句

1. 一条SQL语句插入多条数据。
常用的插入语句如：

1 2 3 4

INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1);

 

修改成：

1 2	INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('0','userid_0','content_0',0),('1','userid_1','content_1',1);

 

修改后的插入操作能够提高程序的插入效率。这里第二种SQL执行效率高的主要原因是合并后日志量（MySQL的binlog和innodb的事务让日志）减少了，降低日志刷盘的数据量和频率，从而提高效率。通过合并SQL语句，同时也能减少SQL语句解析的次数，减少网络传输的IO。
这里提供一些测试对比数据，分别是进行单条数据的导入与转化成一条SQL语句进行导入，分别测试1百、1千、1万条数据记录。

2. 在事务中进行插入处理。

把插入修改成：

1 2 3 4 5 6 7

START TRANSACTION; INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('0', 'userid_0', 'content_0', 0); INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`)     VALUES ('1', 'userid_1', 'content_1', 1); ... COMMIT;

 

使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。
这里也提供了测试对比，分别是不使用事务与使用事务在记录数为1百、1千、1万的情况。

3. 数据有序插入。
数据有序的插入是指插入记录在主键上是有序排列，例如datetime是记录的主键：

1 2 3 4 5 6

INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('1','userid_1','content_1',1); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('2','userid_2','content_2',2);

 

修改成：

1 2 3 4 5 6

INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('0','userid_0','content_0',0); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('1','userid_1','content_1',1); INSERTINTO`insert_table`(`datetime`,`uid`,`content`,`type`)     VALUES('2','userid_2','content_2',2);

 

由于数据库插入时，需要维护索引数据，无序的记录会增大维护索引的成本。我们可以参照innodb使用的B+tree索引，如果每次插入记录都在索引的最后面，索引的定位效率很高，并且对索引调整较小；如果插入的记录在索引中间，需要B+tree进行分裂合并等处理，会消耗比较多计算资源，并且插入记录的索引定位效率会下降，数据量较大时会有频繁的磁盘操作。

优化order by语句

 

ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

MySQL支持二种方式的排序，FileSort和Index，后者效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足以下情况，会使用Index方式排序:

a)ORDER BY 语句使用索引最左前列。参见第1句
b)使用Where子句与Order BY子句条件列组合满足索引最左前列。参见第2句.

 

以下情况，会使用FileSort方式的查询

 

a)检查的行数过多，且没有使用覆盖索引。

第3句，虽然跟第2句一样，order by使用了索引最左前列uid，但依然使用了filesort方式排序，因为status并不在索引中，所以没办法只扫描索引。

b)使用了不同的索引，MySQL每回只采用一个索引.

第4句,order by出现二个索引，分别是uid_fuid和聚集索引(pk)

c)对索引列同时使用了ASC和DESC。 通过where语句将order by中索引列转为常量，则除外。

第5句,和第6句在order by子句中，都出现了ASC和DESC排序,但是第5句却使用了filesort方式排序,是因为第6句where uid取出排序需要的数据,MySQL将其转为常量,它的ref列为const。

d)where语句与order by语句，使用了不同的索引。参见第7句。
e)where语句或者ORDER BY语句中索引列使用了表达式，包括函数表达式。参见第8，9句

f)where 语句与ORDER BY语句组合满足最左前缀，但where语句中使用了条件查询。

查见第10句,虽然where与order by构成了索引最左有缀的条件，但是where子句中使用的是条件查询。

g)order by子句中加入了非索引列,且非索引列不在where子句中。

h)order by或者它与where组合没有满足索引最左前列。

参见第11句和12句,where与order by组合，不满足索引最左前列. （uid, fsex)跳过了fuid

i)当使用left join，使用右边的表字段排序。

参见第13句，尽管user.uid是pk，依然会使用filesort排序。

FileSort排序算法

算法一：双路排序算法

只利用ORDERBY子句中包括的列对象进行排序（适用于有BLOB、TEXT类型的列对象参与的排序）

MySQL4.1之前的排序算法，完整实现过程如下：

1) 按索引键或全表扫描的方式，读取所有的元组，不匹配WHERE子句的元组被跳过；第一步需要从存储读入数据，引发I/O操作。

2) 对于每一行，在缓冲区中存储一对值（对值，包括排序关键字和元组指针）。缓冲区的大小是系统变量的sort_buffer_size设定的值。

3) 当缓冲区已满，运行快排算法（快速排序，qsort）对一个块中的数据进行排序，将结果存储在一个临时文件。保存一个指向排序后的块的指针（如果第二步所说的对值都能被缓冲区容纳，则不会创建临时文件）。

4) 重复上述步骤，直到所有的行已经被读取。

5) 执行一个多路归并操作（操作对象是第三步生成的每一个有序的块）汇集到“MERGEBUFF域”，然后存放到在第二个临时文件中。重复操作，直到第一个文件的所有块归并后存入到第二个文件；“MERGEBUFF域”是代码sql_sort.h中定义的宏，值为7。

6) 重复以下操作（第7步和第8步），直到留下少于“MERGEBUFF2域”标明的块数为止；“MERGEBUFF2域”是代码sql_sort.h中定义的宏，值为15。

7) 在最后一次多路归并操作中，把元组的指针（排序关键字的最后部分）写入到一个结果文件。

8) 在结果文件中，按照排列的顺序使用元组指针读取元组（为了优化这项操作，MySQL读入元组指针进入一个大的块，对块中元组指针进行排序而不是直接对数据排序，然后再用有序的元组指针获取元组到元组缓存，元组缓冲区的大小由read_rnd_buffer_size参数控制）。第8步需要从存储读入数据，引发I/O操作。

算法二：单路排序算法

除利用ORDERBY子句中包括的列对象外，还利用查询目标列中的所有列对象进行排序（适用于除BLOB、TEXT类型外的所有的其他类型的排序）

MySQL4.1之后出现的改进算法，减少一次I/O，需要增加缓冲区大小容纳更多信息。其具体实现过程如下：

1) 获取与WHERE子句匹配的元组。这一步需要从存储读入数据，引发I/O操作。

2) 对于每一个元组，记录排序键值、行的位置值、查询所需的列。这一步记录更多内容，需要更大缓存，内存存储一条元组的信息的长度比算法一的“对值”大许多，这可能引发排序速度问题（排序对象的长度变长，但是内存有限，所以就需把一次内存排序变为多次，进而影响排序的速度），为了控制这个问题，MySQL引入一个参数“max_length_for_sort_data”，如果这一步得到的元组长度大于这个值，则不使用算法二。需要MySQL的使用者特别注意的是，在排序中，如果存在“很高磁盘I/O和很低的CPU利用率”的现象，则需要考虑调整“max_length_for_sort_data”的大小以变更换排序算法。

3) 按照排序的键值，对元组（元组是第二步的结果）进行排序。

算法二直接从缓冲区中的排序的元组中获取有序的列信息等（查询的目的对象），而不是第二次访问该表读取所需的列。相比算法一减少一次I/O。

FileSort优化策略

当无法使用索引列排序时，为了提高Order By的速度，应该尝试一下优化： 
1、避免使用 “select * ” 。查询的字段越多导致元组长度总合可能

超过max_length_for_sort_data的设置，导致无法使用单路排序算法，只能用双路排序算法。 
超过sort_buffer_size的设置，超出后会创建tmp文件进行合并，导致多次IO

2、适当增大sort_buffer_size参数的设置

3、适当增大max_length_for_sort_data参数的设置

 

优化group by语句

默认情况下，MySQL对所有group by col1,col2,...的字段进行排序，若查询包括group by 但用户想避免排序结果的消耗，可以指定order by null禁止排序。

 

提高GROUP BY 语句的效率, 可以通过将不需要的记录在GROUP BY 之前过滤掉

低效:

SELECT JOB , AVG(SAL)

FROM EMP

GROUP by JOB

HAVING JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER'

高效:

SELECT JOB , AVG(SAL)

FROM EMP

WHERE JOB = ‘PRESIDENT'

OR JOB = ‘MANAGER'

GROUP by JOB

优化嵌套查询

用关联查询代替子查询。使用join优化子查询（in）

优化or条件

 

(1) where 语句里面如果带有or条件, myisam表能用到索引， innodb不行。

1)myisam表：
 CREATE TABLE IF NOT EXISTS `a` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `aNum` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

mysql> explain select * from a where id=1 or uid =2;
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
| id | select_type | table | type        | possible_keys | key         | key_len | ref  | rows | Extra                                 |
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | a     | index_merge | PRIMARY,uid   | PRIMARY,uid | 4,4     | NULL |    2 | Using union(PRIMARY,uid); Using where |
+----+-------------+-------+-------------+---------------+-------------+---------+------+------+---------------------------------------+
1 row in set (0.00 sec)
 

2)innodb表：

CREATE TABLE IF NOT EXISTS `a` (
  `id` int(1) NOT NULL AUTO_INCREMENT,
  `uid` int(11) NOT NULL,
  `aNum` char(20) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `uid` (`uid`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

mysql>  explain select * from a where id=1 or uid =2;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | a     | ALL  | PRIMARY,uid   | NULL | NULL    | NULL |    5 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
 

(2) 含有or的查询子句，如果要利用索引，则or之间的每个条件列都必须用到索引，若没有索引，则应考虑增加索引。

(3) 用union替换or：

 

   通常情况下, 用UNION替换WHERE子句中的OR将会起到较好的效果. 对索引列使用OR将造成全表扫描. 

       注意, 以上规则只针对多个索引列有效. 如果有column没有被索引, 查询效率可能会因为你没有选择OR而降低. 

       在下面的例子中, LOC_ID 和REGION上都建有索引.
       高效: 

1. select loc_id , loc_desc , region from location where loc_id = 10

2. union

3. select loc_id , loc_desc , region from location where region = "melbourne"

     低效: 

select loc_id , loc desc , region from location where loc_id = 10 or region = "melbourne"

如果你坚持要用OR, 那就需要返回记录最少的索引列写在最前面.

(4) 用in替换or：

实际执行效果还需检验：

低效: 
select…. from location where loc_id = 10 or loc_id = 20 or loc_id = 30 
高效 
select… from location where loc_in in (10,20,30);

优化分页查询

一般分页查询

一般的分页查询使用简单的 limit 子句就可以实现。limit 子句声明如下：

SELECT * FROM table LIMIT [offset,] rows | rows OFFSET offset

LIMIT 子句可以被用于指定 SELECT 语句返回的记录数。需注意以下几点：

第一个参数指定第一个返回记录行的偏移量
第二个参数指定返回记录行的最大数目

如果只给定一个参数：它表示返回最大的记录行数目
第二个参数为 -1 表示检索从某一个偏移量到记录集的结束所有的记录行
初始记录行的偏移量是 0(而不是 1)

下面是一个应用实例：

select * from orders_history where type=8 limit 1000,10;

该条语句将会从表 orders_history 中查询第1000条数据之后的10条数据，也就是第1001条到第10010条数据。

数据表中的记录默认使用主键（一般为id）排序，上面的结果相当于：

select * from orders_history where type=8 order by id limit 10000,10;

这种分页查询方式会从数据库第一条记录开始扫描，越往后，查询速度越慢，而且查询的数据越多，也会拖慢总查询速度。

(1) 使用子查询优化

这种方式先定位偏移位置的 id，然后往后查询，这种方式适用于 id 递增的情况。

1. select * from orders_history where type=8 limit 100000,1;

2.  

3. select id from orders_history where type=8 limit 100000,1;

4.  

5. select * from orders_history where type=8 and

6. id>=(select id from orders_history where type=8 limit 100000,1)

7. limit 100;

8.  

9. select * from orders_history where type=8 limit 100000,100;

?

4条语句的查询时间如下：

?

1 2 3 4

第1条语句：3674ms 第2条语句：1315ms 第3条语句：1327ms 第4条语句：3710ms

针对上面的查询需要注意：

比较第1条语句和第2条语句：使用 select id 代替 select * 速度增加了3倍
比较第2条语句和第3条语句：速度相差几十毫秒
比较第3条语句和第4条语句：得益于 select id 速度增加，第3条语句查询速度增加了3倍
这种方式相较于原始一般的查询方法，将会增快数倍。

(2) 使用 id 限定优化

这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用 id between and 来查询：

select * from orders_history where type=2 and id between 1000000 and 1000100 limit 100;

这种查询方式能够极大地优化查询速度，基本能够在几十毫秒之内完成。限制是只能使用于明确知道id的情况，不过一般建立表的时候，都会添加基本的id字段，这为分页查询带来很多便利。

 

还可以有另外一种写法：

select * from orders_history where id >= 1000001 limit 100;

当然还可以使用 in 的方式来进行查询，这种方式经常用在多表关联的时候进行查询，使用其他表查询的id集合，来进行查询：

select * from orders_history where id in (select order_id from trade_2 where goods = 'pen') limit 100;

这种 in 查询的方式要注意：某些 mysql 版本不支持在 in 子句中使用 limit。

select film_id, description from film order by title limit 50,5;

优化后：

select film_id, description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id;

 

能用UNION ALL就不要用UNION

UNION ALL不执行SELECT DISTINCT函数，这样就会减少很多不必要的资源。

在Join表的时候使用相当类型的列，并将其索引

如果应用程序有很多JOIN 查询，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL内部会启动为你优化Join的SQL语句的机制。

而且，这些被用来Join的字段，应该是相同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无法使用它们的索引。对于那些STRING类型，还需要有相同的字符集才行。（两个表的字符集有可能不一样）

能用DISTINCT的就不用GROUP BY

SELECT OrderID FROM Details WHERE UnitPrice > 10 GROUP BY OrderID

可改为：

SELECT DISTINCT OrderID FROM Details WHERE UnitPrice > 10

使用 varchar/nvarchar 代替 char/nchar

尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。