数据库相关&部分性能优化

一、分页

Mysql的limit用法

limit 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数,第一个参数指定第一个返回记录行的偏移量,第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1):

①检索前100条记录:0-100
mysql> SELECT * FROM table LIMIT 5;   //检索前 5 个记录行  
等价于
mysql> SELECT * FROM table LIMIT 0,5;

②检索中间记录:101-150
mysql> SELECT * FROM table LIMIT 100,50; // 检索记录行 101-150  

③检索剩余记录:151到结束的所有
mysql> SELECT * FROM table LIMIT 150,-1; // 检索记录行 151-last.  

分页方式

SELECT * FROM tableName WHERE category_id = 123 ORDER BY id LIMIT 50, 10

附:为了提高搜索效率,最好在category_id, id两列上建立复合索引。(此部分为数据库性能优化方面的知识)

扩展:索引

SQL索引在数据库优化中占有一个非常大的比例, 一个好的索引的设计,可以让你的效率提高几十甚至几百倍,在这里将带你一步步揭开他的神秘面纱。
索引主要目的是提高了SQL Server系统的性能,加快数据的查询速度与减少系统的响应时间

  • 什么是索引?
      SQL索引有两种,①聚集索引和②非聚集索引。

下面举两个简单的例子:
举例一(图书馆的例子):一个图书馆那么多书,怎么管理呢?建立一个字母开头的目录,例如:a开头的书,在第一排,b开头的在第二排,这样在找什么书就好说了,这个就是一个聚集索引,可是很多人借书找某某作者的,不知道书名怎么办?图书管理员在写一个目录,某某作者的书分别在第几排,第几排,这就是一个非聚集索引。

举例二(字典的例子):字典前面的目录,可以按照拼音和部首去查询,我们想查询一个字,只需要根据拼音或者部首去查询,就可以快速的定位到这个汉字了,这个就是索引的好处,拼音查询法就是聚集索引,部首查询就是一个非聚集索引.

看了上面的例子,下面的一句话大家就很容易理解了:聚集索引存储记录是物理上连续存在,而非聚集索引是逻辑上的连续,物理存储并不连续。就像字段,聚集索引是连续的,a后面肯定是b,非聚集索引就不连续了,就像图书馆的某个作者的书,有可能在第1个货架上和第10个货架上。

还有一个小知识点就是:聚集索引一个表只能有一个,而非聚集索引一个表可以存在多个。

建立索引的原则如下:
1) 定义主键的数据列一定要建立索引。
2) 定义有外键的数据列一定要建立索引。
3) 对于经常查询的数据列最好建立索引。
4) 对于需要在指定范围内的快速或频繁查询的数据列;
5) 经常用在WHERE子句中的数据列。
6) 经常出现在关键字order by、group by、distinct后面的字段,建立索引。如果建立的是复合索引,索引的字段顺序要和这些关键字后面的字段顺序一致,否则索引不会被使用。
7)其他。。。。

二、子查询

但随着数据量的增加,页数会越来越多,查看后几页的SQL就可能类似:

SELECT * FROM articles WHERE category_id = 123 ORDER BY id LIMIT 10000, 10  

一言以蔽之,就是越往后分页,LIMIT语句的偏移量就会越大,速度也会明显变慢。
此时,我们可以通过子查询的方式来提高分页效率,大致如下:

SELECT * FROM articles WHERE  id >=  
 (SELECT id FROM articles  WHERE category_id = 123 ORDER BY id LIMIT 10000, 1) LIMIT 10  


SELECT 语句中的子查询
通常情况下子查询都与 SELECT 语句一起使用,其基本语法如下所示:
SELECT column_name [, column_name ]
FROM   table1 [, table2 ]
WHERE  column_name OPERATOR
      (SELECT column_name [, column_name ]
      FROM table1 [, table2 ]
      [WHERE])

子查询(Sub Query)或者说内查询(Inner Query),也可以称作嵌套查询(Nested Query),是一种嵌套在其他 SQL 查询的 WHERE 子句中的查询。
子查询用于为主查询返回其所需数据,或者对检索数据进行进一步的限制。

SQL子查询的一些例子

Join相关

Join相关知识查看w3school

其他文章
[MySQL的ORDERBY...LIMIT优化思路 ]
(http://blog.163.com/li_hx/blog/static/18399141320143351538647/)

mysql中使用order by 和limit查询变慢解决办法

100亿数据1万属性数据架构设计

推荐阅读更多精彩内容