mysql笔记Word文件下载.docx
《mysql笔记Word文件下载.docx》由会员分享，可在线阅读，更多相关《mysql笔记Word文件下载.docx（24页珍藏版）》请在冰点文库上搜索。
指定查询数据的表
•where子句：
查询数据的过滤条件
•groupby子句：
对匹配where子句的查询结果进行分组
•having子句：
对分组后的结果进行条件限制
•orderby子句：
对查询结果结果进行排序，后面跟desc降序或asc升序（默认）。
•limit子句：
对查询的显示结果限制数目
•procedure子句：
查询存储过程返回的结果集数据
如何优化Mysql千万级快速分页,limit优化快速分页,MySQL处理千万级数据查询的优化方案！
2010-11-1813:
48
MySQL数据库优化处理实现千万级快速分页分析，来看下吧。
数据表collect（id,title,info,vtype）就这4个字段，其中title用定长，info用text,id是逐渐，vtype是tinyint，vtype是索引。
这是一个基本的新闻系统的简单模型。
现在往里面填充数据，填充10万篇新闻。
最后collect为10万条记录，数据库表占用硬盘1.6G。
OK,看下面这条sql语句：
selectid,titlefromcollectlimit1000,10;
很快；
基本上0.01秒就OK，再看下面的
selectid,titlefromcollectlimit90000,10;
从9万条开始分页，结果？
8-9秒完成，mygod哪出问题了？
？
其实要优化这条数据，网上找得到答案。
看下面一条语句:
selectidfromcollectorderbyidlimit90000,10;
很快，0.04秒就OK。
为什么？
因为用了id主键做索引当然快。
网上的改法是：
selectid,titlefromcollectwhereid>
=（selectidfromcollectorderbyidlimit90000,1）limit10;
这就是用了id做索引的结果。
可是问题复杂那么一点点，就完了。
看下面的语句
selectidfromcollectwherevtype=1orderbyidlimit90000,10;
很慢，用了8-9秒！
到了这里我相信很多人会和我一样，有崩溃感觉！
vtype做了索引了啊？
怎么会慢呢？
vtype做了索引是不错，你直接selectidfromcollectwherevtype=1limit1000,10;
是很快的，基本上0.05秒，可是提高90倍，从9万开始，那就是0.05*90=4.5秒的速度了。
和测试结果8-9秒到了一个数量级。
从这里开始有人提出了分表的思路，这个和discuz论坛是一样的思路。
思路如下：
建一个索引表：
t（id,title,vtype）并设置成定长，然后做分页，分页出结果再到collect里面去找info。
是否可行呢？
实验下就知道了。
10万条记录到t（id,title,vtype）里，数据表大小20M左右。
用
selectidfromtwherevtype=1orderbyidlimit90000,10;
很快了。
基本上0.1-0.2秒可以跑完。
为什么会这样呢？
我猜想是因为collect数据太多，所以分页要跑很长的路。
limit完全和数据表的大小有关的。
其实这样做还是全表扫描，只是因为数据量小，只有10万才快。
OK，来个疯狂的实验，加到100万条，测试性能。
加了10倍的数据，马上t表就到了200多M，而且是定长。
还是刚才的查询语句，时间是0.1-0.2秒完成！
分表性能没问题？
错！
因为我们的limit还是9万，所以快。
给个大的，90万开始
selectidfromtwherevtype=1orderbyidlimit900000,10;
看看结果，时间是1-2秒！
why?
?
分表了时间还是这么长，非常之郁闷！
有人说定长会提高limit的性能，开始我也以为，因为一条记录的长度是固定的，mysql应该可以算出90万的位置才对啊？
可是我们高估了mysql的智能，他不是商务数据库，事实证明定长和非定长对limit影响不大？
怪不得有人说discuz到了100万条记录就会很慢，我相信这是真的，这个和数据库设计有关！
难道MySQL无法突破100万的限制吗？
到了100万的分页就真的到了极限？
答案是：
NO!
!
为什么突破不了100万是因为不会设计mysql造成的。
下面介绍非分表法，来个疯狂的测试！
一张表搞定100万记录，并且10G数据库，如何快速分页！
好了，我们的测试又回到collect表，开始测试结论是：
30万数据，用分表法可行，超过30万他的速度会慢道你无法忍受！
当然如果用分表+我这种方法，那是绝对完美的。
但是用了我这种方法后，不用分表也可以完美解决！
答案就是：
复合索引！
有一次设计mysql索引的时候，无意中发现索引名字可以任取，可以选择几个字段进来，这有什么用呢？
开始的selectidfromcollectorderbyidlimit90000,10;
这么快就是因为走了索引，可是如果加了where就不走索引了。
抱着试试看的想法加了search（vtype,id）这样的索引。
然后测试
selectidfromcollectwherevtype=1limit90000,10;
非常快！
0.04秒完成！
再测试:
selectid,titlefromcollectwherevtype=1limit90000,10;
非常遗憾，8-9秒，没走search索引！
再测试：
search（id,vtype），还是selectid这个语句，也非常遗憾，0.5秒。
综上：
如果对于有where条件，又想走索引用limit的，必须设计一个索引，将where放第一位，limit用到的主键放第2位，而且只能select主键！
完美解决了分页问题了。
可以快速返回id就有希望优化limit，按这样的逻辑，百万级的limit应该在0.0x秒就可以分完。
看来mysql语句的优化和索引时非常重要的！
好了，回到原题，如何将上面的研究成功快速应用于开发呢？
如果用复合查询，我的轻量级框架就没的用了。
分页字符串还得自己写，那多麻烦？
这里再看一个例子，思路就出来了：
select*fromcollectwhereidin（9000,12,50,7000）;
竟然0秒就可以查完！
mygod，mysql的索引竟然对于in语句同样有效！
看来网上说in无法用索引是错误的！
-
有了这个结论，就可以很简单的应用于轻量级框架了：
代码如下：
$db=dblink（）;
$db->
pagesize=20;
$sql="
selectidfromcollectwherevtype=$vtype"
;
execute（$sql）;
$strpage=$db->
strpage（）;
//将分页字符串保存在临时变量，方便输出
while（$rs=$db->
fetch_array（））{
$strid.=$rs['
id'
].'
'
}
$strid=substr（$strid,0,strlen（$strid）-1）;
//构造出id字符串
pagesize=0;
//很关键，在不注销类的情况下，将分页清空，这样只需要用一次数据库连接，不需要再开；
execute（"
selectid,title,url,sTime,gTime,vtype,tagfromcollectwhereidin（$strid）"
）;
<
phpwhile（$rs=$db->
fetch_array（））:
?
>
tr>
td>
&
nbsp;
phpecho$rs['
];
/td>
url'
sTime'
gTime'
vtype'
ahref="
act=show&
id=<
"
target="
_blank"
title'
/a>
tag'
/tr>
phpendwhile;
/table>
php
echo$strpage;
通过简单的变换，其实思路很简单：
1）通过优化索引，找出id，并拼成"
123,90000,12000"
这样的字符串。
2）第2次查询找出结果。
小小的索引+一点点的改动就使mysql可以支持百万甚至千万级的高效分页！
通过这里的例子，我反思了一点：
对于大型系统，PHP千万不能用框架，尤其是那种连sql语句都看不到的框架！
因为开始对于我的轻量级框架都差点崩溃！
只适合小型应用的快速开发，对于ERP,OA，大型网站，数据层包括逻辑层的东西都不能用框架。
如果程序员失去了对sql语句的把控，那项目的风险将会成几何级数增加！
尤其是用mysql的时候，mysql一定需要专业的dba才可以发挥他的最佳性能。
一个索引所造成的性能差别可能是上千倍！
PS:
经过实际测试，到了100万的数据，160万数据，15G表，190M索引，就算走索引，limit都得0.49秒。
所以分页最好别让别人看到10万条以后的数据，要不然会很慢！
就算用索引。
经过这样的优化，mysql到了百万级分页是个极限！
但有这样的成绩已经很不错，如果你是用sqlserver肯定卡死！
而160万的数据用idin（str）很快，基本还是0秒。
如果这样，千万级的数据，mysql应该也很容易应付。
[摘]千万级数据库查询+分页优化
1:
数据分页代码（千万级）摘自
思路:
方法一:
selecttop5*
fromdbo.CompanyNews
wherePKId>
（selectmax（PKId）fromCompanyNewsWHEREpkidin
（selecttop5PKIdfromdbo.CompanyNewsorderbyPKId）
）
orderbypkid
上面等价于==
方法二（建议采用该方法,原因:
top加快查询速度,而in减慢数度,）
（selectmax（PKId）from
（selecttop5PKIdfromdbo.CompanyNewsorderbyPKId）asT
示例代码如下:
-------------------------------------------------------------------
--功能:
获取指定页的分页数据
--作者:
SQ
--修改时间:
2005-08-24
CREATEPROCEDUREp_GetPageRecord
@tblNamevarchar（255）,--表名
@strGetFieldsvarchar（1000）='
*'
--需要返回的列
@fldNamevarchar（255）='
'
--排序的字段名
@PageSizeint=10,--页尺寸
@PageIndexint=1,--页码
@doCountbit=0,--返回记录总数,非0值则返回
@OrderTypebit=0,--设置排序类型,非0值则降序
@strWherevarchar（1500）='
--查询条件（注意:
不要加where）
AS
declare@strSQLvarchar（5000）--主语句
declare@strTmpvarchar（110）--临时变量
declare@strOrdervarchar（400）--排序类型
if@doCount!
=0
begin
if@strWhere!
='
set@strSQL='
selectcount（*）asTotalfrom['
+@tblName+'
]where'
+@strWhere
else
]'
end
--以上代码的意思是如果@doCount传递过来的不是0，就执行总数统计。
以下的所有代码都是@doCount为0的情况
if@OrderType!
set@strTmp='
（selectmin'
set@strOrder='
orderby['
+@fldName+'
]desc'
--如果@OrderType不是0，就执行降序，这句很重要！
end
（selectmax'
]asc'
if@PageIndex=1
='
selecttop'
+str（@PageSize）+'
'
+@strGetFields+'
from['
+@strWhere+'
+@strOrder
+@tblName+'
]'
+@strOrder
--如果是第一页就执行以上代码，这样会加快执行速度
--以下代码赋予了@strSQL以真正执行的SQL代码
]where['
+@fldName+'
+@strTmp+'
（['
+@fldName+'
]）from（selecttop'
+str（（@PageIndex-1）*@PageSize）+'
['
]from['
+@strOrder+'
）astblTmp）'
+@strOrder+'
）astblTmp）and'
exec（@strSQL）
--print@strSQL
GO
在大数据量的情况下，特别是在查询最后几页的时候，查询时间一般不会超过9秒；
而用其他存储过程，在实践中就会导致超时，所以这个存储过程非常适用于大容量数据库的查询。
笔者希望能够通过对以上存储过程的解析，能给大家带来一定的启示，并给工作带来一定的效率提升，同时希望同行提出更优秀的实时数据分页算法。
四、聚集索引的重要性和如何选择聚集索引
在上一节的标题中，笔者写的是：
实现小数据量和海量数据的通用分页显示存储过程。
这是因为在将本存储过程应用于“办公自动化”系统的实践中时，笔者发现这第三种存储过程在小数据量的情况下，有如下现象：
1、分页速度一般维持在1秒和3秒之间。
2、在查询最后一页时，速度一般为5秒至8秒，哪怕分页总数只有3页或30万页。
虽然在超大容量情况下，这个分页的实现过程是很快的，但在分前几页时，这个1－3秒的速度比起第一种甚至没有经过优化的分页方法速度还要慢，借用户的话说就是“还没有ACCESS数据库速度快”，这个认识足以导致用户放弃使用您开发的系统。
笔者就此分析了一下，原来产生这种现象的症结是如此的简单，但又如此的重要：
排序的字段不是聚集索引！
本篇文章的题目是：
“查询优化及分页算法方案”。
笔者只所以把“查询优化”和“分页算法”这两个联系不是很大的论题放在一起，就是因为二者都需要一个非常重要的东西――聚集索引。
在前面的讨论中我们已经提到了，聚集索引有两个最大的优势：
1、以最快的速度缩小查询范围。
2、以最快的速度进行字段排序。
第1条多用在查询优化时，而第2条多用在进行分页时的数据排序。
而聚集索引在每个表内又只能建立一个，这使得聚集索引显得更加的重要。
聚集索引的挑选可以说是实现“查询优化”和“高效分页”的最关键因素。
但要既使聚集索引列既符合查询列的需要，又符合排序列的需要，这通常是一个矛盾。
笔者前面“索引”的讨论中，将fariqi，即用户发文日期作为了聚集索引的起始列，日期的精确度为“日”。
这种作法的优点，前面已经提到了，在进行划时间段的快速查询中，比用ID主键列有很大的优势。
但在分页时，由于这个聚集索引列存在着重复记录，所以无法使用max或min来最为分页的参照物，进而无法实现更为高效的排序。
而如果将ID主键列作为聚集索引，那么聚集索引除了用以排序之外，没有任何用处，实际上是浪费了聚集索引这个宝贵的资源。
为解决这个矛盾，笔者后来又添加了一个日期列，其默认值为getdate（）。
用户在写入记录时，这个列自动写入当时的时间，时间精确到毫秒。
即使这样，为了避免可能性很小的重合，还要在此列上创建UNIQUE约束。
将此日期列作为聚集索引列。
有了这个时间型聚集索引列之后，用户就既可以用这个列查找用户在插入数据时的某个时间段的查询，又可以作为唯一列来实现max或min，成为分页算法的参照物。
经过这样的优化，笔者发现，无论是大数据量的情况下还是小数据量的情况下，分页速度一般都是几十毫秒，甚至0毫秒。
而用日期段缩小范围的查询速度比原来也没有任何迟钝。
聚集索引是如此的重要和珍贵，所以笔者总结了一下，一定要将聚集索引建立在：
1、您最频繁使用的、用以缩小查询范围的字段上；
2、您最频繁使用的、需要排序的字段上。
SQnote·
www.SQ
关注-0
粉丝-1
关注博主
0
（请您对文章做出评价）
如何才能在查询大数据量数据的时候提高效率，节省时间呢？
1：
索引，我们最先想到的就是创建索引，创建索引可以成倍的提升查询的效率，节省时间。
但是如果数据量太过于巨大的时候，这个时候单纯的创建索引是无济于事的，我们知道假如特别是在大数据量中统计查询，就拿1000W数据来说吧，如果使用count函数的话，最少要50-100秒以上，当然如果你的服务器配置够高，处理够快，或许会少很多但是一样会超过10秒。
单纯的建立索引是无济于事的。
我们可以在创建索引的时候给索引加个属性，compress，这个属性可以将所创建的索引进行一个良好的归类，这样的话，查询速度会提升5-10倍，或者更高。
但是唯一的缺点是，压缩索引只能手动创建，对于那些KEY是无法进行压缩的，因为KEY（主键）是自动创建的索引，compress必选的属性，一般默认是不创建。
所以在创建压缩索引的时候，可以找其他的关键字段进行压缩，比如工单表里面的流水号
2：
尽量少的使用那些函数，比如ISNUll;
ISNOTNULL,IN;
NOTIN等这样的匹配函数，可以使用符号程序进行操作
3：
尽量少使用子查询，如果你写个类，里面模仿子查询的效果，你就会发现，简直在要命，我们可以使用联合查询，或者是外连接查询，这样速度会比子查询快很多。
4：
在使用索引的时候，注意如下：
Where子句中有“！
=”将不使用索引
升级会员 