Tuesday, June 12, 2007

If you love css..

我想把本篇作为css基础教程的序曲，从今天开始翻译和整理我从设计网页以来所学到的css基础知识。本教程会分成N个部分，单独发表，其间可能会插入几篇与教程无关的文章。没有具体的教程发表进度,推荐关注本教程的朋友订阅本站Feed。教程内容的30%-50%为Jorux原创，其余翻译部分均为意译,可能来源于多个国外站点，并在教程结束时公布参考原文地址。

曾翻译过一篇css教程:十步学会用css建站，文章声称能在十步学会建站，虽说夸张点，但确实能学到一些东西，就像现代社会流行速成一样，它只是一个css快餐，要达到自由运用css，没个百八十步恐怕有点悬。

需要什么软件学习css?

一个文本编辑器和Firefox

1.文本编辑器：这里所指的是诸如windows自带的记事本以及Editplus等改进版记事本软件。不推荐Dreamweaver, Frontpage等具有图形化可视操作，不直接编辑css源代码的软件。这类软件确实能在学习css的初期帮助你迅速达成你的愿望，但是不久你就会发现这些被Dreamweaver所自动生成的css代码冗长繁琐，css文件体积增大。一旦离开这些软件你的头脑就会像一张白纸，基本的css语法都想不起来。本人曾走过弯路，望初学css的朋友借鉴。

本人使用Editplus编写网页代码，其属于加强版的记事本，具有多步撤销，语法加亮，全部替换等功能，安装后无需设置，推荐把屏幕字体改为14号Verdana，方便阅读编写。

2.Firefox：本人绝非Firefox的Fan，它也不是盏省油的灯(占用内存多)。但作为一个免费软件，再配上适当的插件，是网页设计者的必备工具，如何使用和选择插件体现设计者专业素质的水平。推荐安装以下插件：

必装插件:

IE Tab ：调试网页在IE内核浏览器表现的优秀插件，不管你喜不喜欢，请安装！

强烈推荐安装的插件：

Web Developer：其功能之强大，以至于我还没用全它的功能，但是其直接查看网页css代码功能，页面信息的显示，以及验证css和Html的功能非常实用。

ColorZilla：方便提取网页中任何元素的颜色。

Html Validator：安装这个插件后会在浏览器右下角生成一个图标，绿色对号表示当前网页的Html通过验证，红色叉号表示Html有错误，黄色叹号表示Html存在无法通过验证的警告语句。双击图标就会高亮显示该网页存在的不能通过验证的语句数目、位置以及修改建议。作为设计者，最好养成随时观察这个图标的习惯，你就会发现网上声称能通过验证的网页，几乎都是错误或是警告。本网站除极个别网页外均通过验证。

FireBug：安装这个插件后也会在浏览器右下角生成一个图标，绿色对号表示当前网页的Javascript通过验证，并能对错误的Javascript代码debug。我们暂时并不需要这个功能。需要用到的功能是其Inspector，你需要通过定制工具栏，把Inspector的"眼镜"图标拖入工具栏。点击Inspector图标后，将鼠标移到网页任意位置，你就能在靠下的窗口看到网页Html文件的相应源代码，在网页调试时非常有用。

装这么多插件是不是有点累，我光写都有点吃不消，所以休息一下，在下一篇文章进入css教程的正文部分。

支持本教程或是有任何疑问，请留言。

感谢您对本教程的支持！

属性篇 (1)

注:由于这段时间被一些琐碎的事搅得不得安宁,直到今天才发布《If you love css…》的第一篇正文,再次向关注本站的朋友以及访客表示歉意,如在阅读过程中发现错误,请留言, Jorux会及时更正.

本教程主要介绍css的基础知识,将逐个讲解css的各个属性,过程可能比较枯燥,但会尽力多举例说明.

css语法:例:用Web Developer的css查看功能查看Jorux.com首页的css文件,可以看到以下代码:

body {
font: normal 12px/1.5 Georgia, sans-serif;
text-align:left;
background:#444 url(images/bodybg.jpg) repeat-y;
}

是不是有点复杂,现在我们没有必要细究以上代码,先简化以上代码为:

body {text-align:left;}

这便是基本css语法结构:

j's image

引用css:css文件的作用就在于控制Html文件的表现,而从Html文件中引用css文件的方法大致有三种:外联(external),嵌入(in-line)和内联(internal),这里限于篇幅以及应用频度,只介绍外联方法.

例:同样打开Jorux.com的主页,点击Firefox工具栏–>查看–>页面源代码,在<head></head>可以看到以下代码:

herf后的地址即为本网站css的地址,这里应用的地址为绝对地址,而在本地调试时一般用相对地址,将在后文说明.

建立本地调试的文件结构:如下图所示建立名为local的文件夹,以及其子文件夹style和image,分别用于存放css文件和图片文件,在local文件夹的根目录下创建Html文件index.htm,在style文件夹的根目录下创建css文件style.css:

j's image

用文本编辑器打开index.htm,写入以下代码:

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head>
<title>My first homepage</title>
<link rel="stylesheet" href="style/style.css" media="screen" /></head>
<body>
<h1>My first homepage</h1>
</body>
</html>

然后打开style.css,输入以下代码:

body {
background-color: #333;
}
h1 {
color: #F00;
background-color: #FFF;
}

用Firefox打开index.htm,如果你看到Example1的效果,那么恭喜你,一个基本的本地调试环境建立了.

下面开始逐个介绍css属性:

颜色(color):css能够控制的颜色主要包括文本颜色,边框颜色等,对于背景颜色和边框颜色会在以后说明,在这主要解释文本颜色的表现.

在如上所示style.css的选择器h1中,文本颜色的属性是用color表示的,h1的颜色的属性值为#FF0000(一个#加上十六进制值),简写为#F00. 我们甚至可以用英文单词red表示属性值: color: red; 效果是一样的. 颜色的其他属性值还有RGB值,在css中不太常用,这里就不再叙述.

例:查看Jorux.com首页的css文件,可以看到以下代码:

a {
color: #545454;
text-decoration:none;
}
a:hover {
color: #919191;
}

在选择器a中,文本颜色的属性值为#545454, 即存在超级链接的文本颜色为#545454; 而a:hover为伪类选择器(表现依赖于浏览器的状态), 它的属性值为#919191, 即鼠标在超级链接上悬停时文本的颜色. 你可以用ColorZilla验证本站首页的标题文字颜色.

文本(text):css控制的文本属性主要包括以下四个: text-indent, text-align, text-decoration, text-transform;

1. text-align:属性text-align指文本的对齐方式,其有向左,向右,居中对齐以及自动适应四种对齐方式:

text-align: left;
text-align: right;
text-align: center;
text-align: justify;

例:查看Jorux.com首页的css文件,可以看到以下代码:

body {
font: normal 12px/1.5 Georgia, sans-serif;
text-align:left;
background:#444 url(images/bodybg.jpg) repeat-y;
}

在选择器body中声明属性text-align为left,可以避免在其他需要文本左对齐的选择器中重复声明.

2. text-indent:属性text-indent指段落首行的缩进, 既然是段落的属性,一般用于选择器p(段落)中,代码如下:

p {
text-indent: 26px;
}

本站的段落缩进为0, 所以在css文件中能找到text-indent: 0;,不声明即此属性,即默认为0.

3. text-decoration:属性text-decoration为文本修饰, 其包括下划线, 上划线, 中划线和无四种修饰方式, 代码如下:

text-decoration: none;
text-decoration: underline;
text-decoration: overline;
text-decoration: line-through;

在这就不例举具体的下划线, 上划线, 中划线的例子, 相信大家很容易想像的到它的效果. 需要强调的是属性值none, 如果你查看本站css的话, 可以看到所有属性text-decoration的值均为none. 这是因为目前的浏览器对于选择器a(即超级链接), 默认text-decoration属性值为underline. 这就会使很多你不希望出现的下划线大量涌现, 而且由于无法约束下划线的粗细, 以及浏览器之间的差异, 甚至会出现各种粗细, 不同浏览器显示不同的下划线.

例:你可以看到本站文章内的超级链接的文本修饰是点划线, 这个效果不是属性text-decoration所能实现的, 其需要下边框属性的支持, 将会在边框属性时说明. 实现方法如下(请查看本站css代码):

1.在全局声明中将选择器a的text-decoration属性值设为none, 代码如下:

a {
color: #545454;
text-decoration:none;
}

2.为使文章正文部分的超级链接显示蓝色点划线的效果,代码如下(读者目前只需了解,目前暂不说明):

.post_body a{
color:#0061CA;
padding:0;
border-bottom:1px dotted #0061CA;
}

4. text-transform: 这个属性可能大家不太熟悉, 因为这个属性是只为英文服务的, 用于转换字母大小写之用. 其包括首字母大写, 大写, 小写和无变化四种属性值, 代码如下:

text-transform: capitalize;
text-transform: uppercase;
text-transform: lowercase;
text-transform: none;

例:如果你再本站留过言, 而且用的是英文名的话, 你就会发现无论你输入的姓名是有没有将首字母大写, 显示评论者姓名时首字母均被转化为大写, 查看本站css代码如下:

.comment_author {
text-transform:capitalize;
}

本篇正文完

p.s. 如果您考虑到以下四种情况, 请留言:

1. 让我知道您的想法, 有什么需要改进的, 或者是错误的, Jorux会及时修正并在下一篇文章中改进;

2. 哪怕是你想测试以下那个评论者姓名的首字母大写的效果, 请尽管将你的英文名用小写输入;

3. 本教程的更新速度应该是一到两个星期, 但文章长短不一, 如果有了您的支持, 本人认为会明显延长单篇文章的篇幅, 明显减少全教程的时间.

4. …

属性篇 (2)

上次主要说明了css对于颜色和文本属性的控制, 这次介绍css对于字体, 背景等属性的控制.

字体(Font):css控制的字体属性包括font-family, font-style, font-variant, font-weight, font-size.

1.font-family: 由字体名(family-names)和字组名(generic families)两个部分组成.首先来看个实例,例:查看Jorux.com首页的css文件,可以看到以下代码:

body {
font: normal 12px/1.5 Georgia, sans-serif;
text-align:left;
background:#444 url(images/bodybg.jpg) repeat-y;
}

蓝色加亮部分就是font的属性值, 这种写法为简写, 可将其还原为:

font-weight: normal;
font-size: 12px;
line-height: 1.5;
font-family: Georgia, sans-serif;

行高(line-height)其实并不属于font属性, 但可以在简写时归入font属性值内. 现在我们先只看最后一行font-family的部分. “Georgia“即为字体名(有simhei, arial, verdana等), “sans-serif“即为字组名(包括衬线字体(Serif), 非衬线字体(Sans-Serif) 和等宽字体(MonoSpace)三种).

由于Windows自带的汉字字体极为有限. 但一般中文Windows系统都支持宋体(默认, SimSun)和黑体(SimHei)和楷体(kaiti_gb2312). 我们来看看这几种汉字字体(要注意这几种字体名, 楷体的英文字体名为kaiti_gb2312, Firefox对汉字字体支持不良):

宋体楷体黑体默认

中国人看到西方字体名可能会比较迷惑, 但就如同学习英文时所遇到的英文名一样, 一些常用的字体名的表现和拼写必须要掌握, 对于中文学习者, 目前只推荐掌握以下几种英文字体:

Arial Verdana Georgia Courier

这几种字体的一般用法如下:

1. Arial: 是目前最为流行的正文字体, 几乎所有的web2.0站点(如Google, Flickr, Wikipedia等)都使用arial字体作为正文甚至是标题的字体. 特点是亲和力比较强, 阅读起来不易疲劳;

2. Verdana: 是1996年微软邀请顶级字体设计师花了两年时间出品的字体, 免费提供给windows用户. 和arial用法比较相似, 是目前使用最为广泛的字体, 几乎能在任何地方看到;

3. Georgia: 这个后起之秀, 由于其花哨的衬线(后说明), 被很多网页设计师所青睐, 逐渐取代了Times New Roman在serif字组中的核心地位. 本网站的标题也使用的是Georgia字体.

4. Courier: 隶属于Monospace字组, 由于其字母的宽度均一, 一般用于对单词长度控制比较严格的地方, 比如网页导航条和报纸(印刷品).

英文有那么多字体, 但都隶属于serif, sans-serif和monospace三种字组. 由于英文本身的特点, 不同于方块字(汉字), 英文字母的宽度是不相等的, 这就给出版业如报纸的排版工作增加了难度, 于是发明了等宽字体(monospace).

字组及字体关系图如下:

现在有必要解释一下serif和sans-serif的区别. “sans”来源于古法语, 意为”without”, 即”非”. 而”serif”来源于荷兰语, 译为衬线, 指字母的拐角或端点位置的修饰线, 如图所示:

所以非等宽字组又分为有衬线(serif)和无衬线(sans-serif)两组. verdana和arial均属于无衬线组.

对于字组和字体的选择, 其实是很个性化的, 读者可以根据自己的喜好, 灵活搭配, 但总的原则是不要选取一些罕见的字体, 如果访问者没有这种字体的话,就会和你的设计意图相悖.

例:如font-family的属性值如下:

font-family: verdana,arial,sans-serif;

浏览器首先在字体库中寻找verdana字体, 如果没找到则寻找arial字体, 如果还没找到就在sans-serif字组中的选择其他字体显示.

2. font-style: 包括normal, italic和oblique三个属性值. css代码如下:

font-stye:normal;
font-stye:italic;
font-stye:oblique;

表现如下:

normal italic oblique

可以看到italic和oblique都是向右倾斜的文字, 但区别在于Italic是指斜体字，而Oblique是倾斜的文字，对于没有斜体的字体应该使用Oblique属性值来实现倾斜的文字效果。主要用于一些需要区别于正文的备注说明文字.

3. font-variant: 包括normal和small-caps两种属性值. 非英文用户可能很少用到这个属性. small-caps是指小型大写字母, 是介于大写字母和小写字母之间的一种字母表现形式, css代码如下:

font-variant: normal;
font-variant: small-caps;

表现如下:

NORMAL small-caps

可以看到小型大写字母比正常的大写字母要矮一截, 需要注意的是, 要实现此效果, html中的中必须使用小写字母, 在css中用font-variant:small-caps;就能自动转化为小型大写, 否则就会显示为正常的大写字母.

4. font-weight: 包括normal和bold两种属性值. blod属性值即指粗体. css代码如下:

font-weight: normal;
font-weight: bold;

表现如下:

normal bold

5. font-size: 用于控制字体大小的属性, 属性值的单位主要有px, pt, cm, mm, %, em, 其中pt, cm, mm为字体绝对大小单位,px, %, em为相对大小单位. 这两种单位各有裨益, 绝对大小单位的优势在于, 不管用户的浏览器设置如何, 均能保持原有的设计样式, 但对于一些有阅读障碍, 习惯于大字体的用户就显得有点死板. 而相对大小单位虽然在可读性上有优势, 但网页界面容易被增大的字体所冲散.

在这里, 限于篇幅就不再解释每个单位的区别和用法. 可以参考Length units.

这里只是强调一点, 如果你的网页主要是在电脑屏幕上显示, 首推px作为你的首选单位, 因为px能够精确地表示元素在屏幕中的位置和大小. 如果你的网页主要用于打印, 则推荐使用绝对单位pt(1pt=1/72英寸)来作为首选单位, 这是因为不管屏幕的分辨率是多少, 打印出来的大小都是一样的.

Summary: 现在总结一下font的属性, 如果想让”Font Property”表现为32px, 行高为150%, 粗体, 斜体, 字体为Georgia, 字组为serif, 代码如下:

font-size: 32px;
line-height: 1.5;
font-style: italic;
font-weight: bold;
font-family: Georgia, serif;

简写为:

font: italic bold 32px/1.5 Georgia,serif;

表现如下:

Font Property

背景(background): 包括background-color, background-image, background-repeat, background-attachment, background-position五种属性.

1. background-color: 指背景颜色, 其属性值有三种形式, 十六进制, 颜色名称, rgb值. 目前最为常用的是”#+十六进制数”. 例: 本站显示代码的段落背景为#EEEEEE的灰色, 代码及示例如下:

background-color: #EEEEEE;

2. background-image: 指背景图片, 其属性值为背景图片的地址, 代码和格式如下:

background-image: url(http://jorux.com/back.png);

例: 本站首页的第一篇文章标题前有个”NEW”图标, 查看css可见如下代码:

#homeheadline a{
color:#444;
padding: 30px 68px 0 40px;
margin:0;
background:url(images/new.png) no-repeat 0 16px ;
}

3. background-repeat: 指背景图片的重复与否以及重复方式, 有no-repeat, repeat, repeat-x, repeat-y四种属性值.

no-repeat: 即无论背景图片的大小, 只显示单个背景图片, 如首页的第一篇文章标题前的”NEW”图标, 代码如上所示;

repeat: 指背景图片横向和纵向重复连续显示;

repeat-x: 指背景图片横向重复连续显示;

repeat-y 指背景图片纵向重复连续显示;例: 查看本站css文件,可以看到以下代码:

body {
font: normal 12px/1.5 Georgia, sans-serif;
text-align:left;
background:#444 url(images/bodybg.jpg) repeat-y;
}

可以看到背景图片bodybg.jpg是纵向重复显示的, 并随页面的长度的增加而增加.

4. background-attachment: 指背景图片的附着方式, 有scroll(默认属性)和fixed两种属性值. scroll指背景图片随页面的滚动而滚动, 而fixed属性值则可使背景图片固定于浏览器的某个位置, 不随页面滚动而滚动. 如下例所示, 您可以试着滚动本页面来看看两图的差别, 左边为scroll, 右边为fixed(请用IE观看以下演示):

X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X X

5.background-position: 指背景图片的位置属性. 属性值可以有百分比, 位置名称和具体数值三种形式, 如下图所示(蓝色部分表示距离左边框的数据, 黑色部分表示距离上边框的数据):

可以看到, 要使背景图片位于左上角有三种方法, 代码如下:

background-position: 0% 0%;
background-position: top left;
background-position: 0 0;

实际上, 不声明此属性, 即默认背景图片为左上角显示. 值得注意的是, 当用属性值为位置名称时, 先写距离上边框的数据(top), 后写距离左边框的数据(left). 而当用百分比或是具体数值表示时, 则反过来写.请读者仔细看看上图的蓝色和黑色部分的顺序.

不要小看这个属性, 在导航条的制作中, 这个属性将是实现一些动态变化的关键手段(将在以后的实例篇中说明).

Summary: 现在总结一下background的属性, 如果想让某背景图片表现为固定, 位于右上角, 不重复, 背景颜色为#CCC, 代码如下:

background-image: url(http://static.flickr.com/86/240223091_dc1dce25a5_m.jpg);
background-color: #CCC;
background-attachment: fixed;
background-position: top right;
background-repeat: no-repeat;

简写为:

background: url(http://static.flickr.com/86/240223091_dc1dce25a5_m.jpg) #CCC fixed no-repeat top right;

表现如下(请用IE观看以下演示):

本篇正文完.

原以为可以多写几个属性的, 没想到字体和背景两个属性就写了这么长, 在体力和精神上都已经到了极限, 暂写到这, 其余的还看下回更新.

属性篇 (3)

本篇主要介绍css对边框(border)的属性控制和链接(link)的伪类选择器.

边框(border): css控制的边框属性包括border-width, border-color, border-style.

Border之所以让人很困惑主要源于IE5错综复杂的BUG, 由于IE5是一个”will soon be dead“ 的浏览器, 这里只例举一个最为知名的关于border-width的BUG, 让大家更好的理解border的含义, 先看下图:

如上图所示, 对象A(白色矩形)周围有蓝色边框B, 可以看出A的实际宽度为ef, 而IE5不这么认为, 它把cd的长度定义为对象A的宽度, 这个bug在边框的宽度小时几乎察觉不到, 但在边框与对象宽度相差不大时显得尤为明显.

现在, 结合以上说明, 可以看出border是独立于对象之外, 位于magin与padding之间(后说明), 具有固定宽度, 颜色和样式的区域.

1. border-width: 其可有具体数值(如1px, 2px等)或是描述性(thin, medium, thick)的属性值. 由于浏览器及个人设置的不同导致thin, medium, thick这些属性值的表现不一, 不推荐使用. css代码如下:

border-width: 1px;
border-width: 2px;
border-width: thin;
border-width: medium;
border-width: thick;

宽度效果如下(注意不同浏览器下的区别):

1px 2px thin medium thick

2. border-style: 边框样式包括solid, dashed, dotted, double, groove, ridge, inset, outset等, 代码如下:

border-style: solid;
border-style: dashed;
border-style: dotted;
border-style: double;
border-style: groove;
border-style: ridge;
border-style: inset;
border-style: outset;

样式效果如下(你可能需要Firefox或是Opera才能看到后四种效果):

solid

dashed

dotted

double

groove

ridge

inset

outset

3. border-color: 边框颜色属性和一般颜色属性是一样的, 可以参看属性篇(1).

Summary: 以上例举的都是四边相同样式的情况, 其实可以分别设置border-top, border-right, border-bottom, border-left四边的属性, 比方说想要表现一个宽2px, 蓝色(blue), 样式为solid的上下边框, 代码如下:

border-top-width: 2px;
border-top-color: blue;
border-top-style: solid;
border-bottom-width: 2px;
border-bottom-color: blue;
border-bottom-style: solid;

简写为:

border-top: blue 2px solid;
border-bottom: blue 2px solid;

表现如下:

宽2px, 蓝色, 样式为solid的上下边框

以下内容不再属于css属性, 但暂时归入属性篇中.

链接(link): 在html里用<a></a>标明链接, 在css里用a作为链接的选择器名.

css的缺点和它的优点一样明显, 其最大的缺陷就在于网页的动态表现不足, 在这一点上和Flash差距尤为突出.

好在css还是留了一手, 那就是链接的伪类选择器, 所谓伪类就是指依赖于浏览器或是用户的状态. 对于链接来讲, 存在link, hover, visited, active四种状态, 即四种伪类选择器: a:link(存在链接, 但无鼠标动作), a:visited(被点击或访问过), a:hover(鼠标悬停于链接上时的状态), a:active(鼠标点击与释放之间的状态).

css便是以这几个伪类选择器实现了其为数不多的动态效果. 目前最为常用的就是导航条和按钮的动态显示. 以下用一个动态按钮的实例来说明这四个伪类选择器.

1. 首先准备一副图片(button.png), 如下图(160px*240px), 其由四幅160px*60px的小图自上而下排列而成.

2. 接着需要往你的本地调试文件夹(调试环境的建立方法参考属性(1)中的说明)的index.htm中写入html代码, 如下:

3. 在style.css中写入以下代码:

body {background-color: #FFF;}
#button a{
width:160px;
height:60px;
display:block;
background:url(../image/button.png) no-repeat 0 0;
}
#button a:link { background-position:0 0;}
#button a:hover { background-position:0 -60px;}
#button a:active { background-position:0 -120px;}
#button a:visited { background-position:0 -180px;}

可以看到如Example2的效果.

4. 代码解释:

在index.htm写入如上代码, 目的在于插入一个id名为”button”的盒子(div),且其内含有一个链接<a href=”#”></a>.

#button a{
width:160px;
height:60px;
display:block;
background:url(../image/button.png) no-repeat 0 0;
}

在css文件中写入以上代码, 目的在于控制盒子中链接的表现, 通过名为”#button a”的选择器来实现. 链接的宽高为160px*60px, 背景为图片button.png.

在这强调一下display:block的作用. 由于在html文件中,链接<a href=”#”> </a>中没有任何的内容(content)填充, 如果没有声明”display:block”, 那么即使声明了选择器”#button a”的宽高, 浏览器也会因为html文件中没有内容而无法显示链接. 所以”display:block”在这里的作用就在于强制浏览器显示没有内容填充的链接.

#button a:link { background-position:0 0;}

用伪类选择器a:link声明链接的背景图片在左上角显示, 即距离左边和顶边分别0, 0. 但由于已经在选择器 “#button a”中声明了图片位置, 此代码可有可无.

#button a:hover { background-position:0 -60px;}
#button a:active { background-position:0 -120px;}
#button a:visited { background-position:0 -180px;}

用伪类选择器a:hover声明鼠标悬停时, 背景图片上移60px, 而使排在第二位的绿色小图片显示出来;

用伪类选择器a:active声明在鼠标点击与释放之间的状态时, 背景图片上移120px, 而使排在第三位的红色小图片显示;

用伪类选择器a:visited声明在链接被点击或访问过时, 背景图片上移180px, 而使排在第四位的灰色小图片显示;

现在你基本了解了css动态按钮的制作过程, 但以上css代码还存在一个严重的缺陷, 相信你会很快发现问题所在——这个按钮居然是一个”一次性按钮“, 也就是说这个按钮在点击第一次后, 就一直显示那个灰色小图片, 你能想出解决方法吗? 请在留言中指出.

答案会在下一篇文章中说明! 下篇将会涉及css的核心内容——盒子模型, margin和padding属性.(正文完)

《If you love css …》已经有四篇文章发表了, 更新速度比较慢, 原因有三:
1, css教程内容本身的难度在加大, 又奢望在说明方法方式上比前人有所进步;
2, 接手一个web design的project, 到了最后调试阶段, 那位美国大哥说了声”perfect”, 就声称太忙就不再回复mail, 使我认识到网络之不可靠, 绝不只发生在别人身上. 严重打击我的网络热情;
3, 毕竟我还不是个专业网虫, 现实生活中, 还是有那么多我所关心的, 不得不干的事, 仍旧未完成.
即使每天上网时间很短, 但我依然努力挤出时间, 在某天集中精力完成某篇文章, 感谢那些订阅本网站FEED的朋友(目前每天有20个左右的订阅流量). 更要鸣谢那些在本站留言, 纠正错误, 提出建议的朋友, 如果没有你们, 可能这个教程早就 ……

属性篇 (4)

本文作为属性篇的最后一篇文章, 将讲述HTML和CSS的关键—盒子模型(Box model). 理解Box model的关键便是margin和padding属性, 而正确理解这两个属性也是学习用css布局的关键.

注: 为什么不翻译margin和padding? 原因一, 在汉语中并没有与之相对应的词语; 原因二: 即使有这样的词语, 由于在编写css代码时, 必须使用margin和padding, 如果我们总用汉语词语代替其来解释的话, 到了实际应用时容易混淆margin和padding的概念.

如果有一点Html基础的话, 就应该了解一些基本元素(Element), 如p, h1~h6, br, div, li, ul, img等. 如果将这些元素细分, 又可以分别归为顶级(top-level)元素,块级(block-level)元素和内联(inline)元素.

1. Block-level element: 指能够独立存在, 一般的块级元素之间以换行(如一个段落结束后另起一行)分隔. 常用的块级元素包括: p, h1~h6, div, ul等;

2. Inline element: 指依附其他块级元素存在, 紧接于被联元素之间显示, 而不换行. 常用的内联元素包括: img, span, li, br等;

3. Top-level element: 包括html, body, frameset, 表现如Block-level element, 属于高级块级元素.

块级元素是构成一个html的主要和关键元素, 而任意一个块级元素均可以用Box model来解释说明.

Box Model: 任意一个块级元素均由content(内容), padding, background(包括背景颜色和图片), border(边框), margin五个部分组成. 立体图如下(Fig. 1):

该立体图引自:http://www.hicksdesign.co.uk/ (Under the Creative Commons License)

平面图如下(Fig. 2):

根据以上两图, 相信大家对于Box model会有个直观的认识.

以下说明margin和padding属性:

1. Margin: 包括margin-top, margin-right, margin-bottom, margin-left, 控制块级元素之间的距离, 它们是透明不可见的, 对于Fig. 2所示的上右下左margin值均为40px, 因此代码为:

margin-top: 40px;
margin-right: 40px;
margin-bottom: 40px;
margin-left: 40px;

根据上, 右, 下, 左的顺时针规则, 简写为

margin: 40px 40px 40px 40px;

为便于记忆, 请参考下图:

当上下, 左右margin值分别一致, 可简写为:

margin: 40px 40px;

前一个40px代表上下margin值, 后一个40px代表左右margin值.

当上下左右margin值均一致, 可简写为:

margin: 40px;

2. Padding: 包括padding-top, padding-right, padding-bottom, padding-left, 控制块级元素内部, content与border之间的距离, 其代码, 简写请参考margin属性的写法.

至此, 我们已经基本了解margin和padding属性的基本用法. 但是, 在实际应用中, 却总是发生一些让你琢磨不透的事, 而它们又或多或少的与margin有关.

注: 当你想让两个元素的content在垂直方向(vertically)分隔时, 既可以选择padding-top/bottom, 也可以选择margin-top/bottom, 再此Jorux建议你尽量使用padding-top/bottom来达到你的目的, 这是因为css中存在Collapsing margins(折叠的margins)的现象.

Collapsing margins: margins折叠现象只存在于临近或有从属关系的元素, 垂直方向的margin中. 文字说明可能让人费解, 下面用一个例子说明margin-collapsing现象.

例: 在html文件的<body></body>之间写入如下代码:

<div id=”ID1″>
<h1 id=”ID2″>Margins of ID1 and ID2 collapse vertically.<br/>元素ID1与ID2的margins在垂直方向折叠.</h1>
</div>

在与其外联的css文件中写入:

* {
padding:0;
margin:0;
}
#ID1 {
background-color: #333;
color: #FFF;
margin-top: 10px;
margin-bottom: 10px;
}
#ID2 {
font: normal 14px/1.5 Verdana, sans-serif;
margin-top: 30px;
margin-bottom: 30px;
border: 1px solid #F00;
}

代码解释:

1. 在html写入的代码表示, 在html中插入id分别为ID1和ID2的两个块级元素div, h1;

2. *{padding:0; margin:0;}: 使浏览器默认的元素padding和margin值均归零;

3. #ID1{…}: 使id为ID1的元素div的背景颜色为#333, 字体颜色为#FFF, margin-top/bottom为10px;

4. #ID2{…}: 使id为ID2的元素h1的字体大小为14px, verdana字体, 行高为字体高的150%, 正常粗细. margin-top/bottom为30px, 边框为1px宽, 红色实线.

依据以上解释, 我们应该得到如下效果(Fig. 3):

即ID1的margin-top/bottom=ab=ef=10px;

ID2的margin-top/bottom=bc=de=30px;

但用浏览器打开html文件, 却得到Example4的效果, 如下图(Fig. 4):

即ab=cd=30px, ID1的margin-top/bottom=10px被折叠了, 而且ID1应有的margin黑色背景也一同被折叠消失了.

为什么会折叠: 造成以上现象的原因是, 我们在css中并没有声明id为ID1的元素div的height(高), 因此它的高便被设为auto(自动)了. 一旦其值被设为auto, 那么浏览器就会认为它的高为子元素ID2的border-top到border-bottom之间的距离, 即Fig. 4中bc的长度, 所以子元素ID2的margin-top/bottom(30px)就伸出到了父元素ID1之外, 出现了Fig. 4中ab与cd之间的空白区域. 因此父元素ID1的margin-top/bottom因子元素的”红杏出墙”而被折叠消失了.

如何解决折叠问题: 可能大家最初想到的办法就是根据折叠发生的原因—auto, 来解决问题. 但是, 在实际操作中, 某些元素如div, h1, p等, 我们是不可能预先知道它的高是多少的, 因此在css文件中是不能常规通过声明元素的高来解决折叠问题.

我们需要在css文件中加入如下代码(红色部分):

#ID1 {
background-color: #333;
color: #FFF;
margin-top: 10px;
margin-bottom: 10px;
padding-top:1px;
padding-bottom:1px;
}

或是：

#ID1 {
background-color: #333;
color: #FFF;
margin-top: 10px;
margin-bottom: 10px;
border-top:1px solid #333;
border-bottom:1px solid #333;
}

通过增加以上代码, 便可使浏览器重新计算ID1的高, 使其为子元素ID2的margin-top/bottom外缘(outer top/bottom)之间的距离, 即Fig. 3中be的距离.

看得是不是有点迷糊, 折叠问题难度比较大, 它也曾是困扰我多时的难题, 加之本人的叙述说明能力不足, 更加大了大家学习的难度, 在此Jorux深表歉意.

为检验大家学习折叠问题的效果, 给读者留下一个问题:

html文件代码为(请将css地址改为你的css文件存放地址):

<!DOCTYPE html PUBLIC “-//W3C//DTD XHTML 1.0 Transitional//EN” “http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd”>
<html>
<head>
<title>My first homepage</title>
<link rel=”stylesheet” href=”….css” type=”text/css” media=”screen” />
</head>
<body>
<h1 id=”title”>
A title</h1>
<div id=”content”>
Some text here </div>
</body>
</html>

外联css文件代码为:

*{
padding:0;
margin:0;
}
body {
font: 14px/1.5 Georgia, serif;
}
h1#title {
float: right;
width: 50%;
font: 14px/1.5 Georgia, serif;
margin-top: 0;
background-color: #F00;
color: #fff;
}
div#content {
background-color: #333;
color: #FFF;
margin-top: 30px;
border: 1px solid #333;
}

请将此代码存为css文件, 并与以上html文件关联, 得到如Example5的效果, 请分别用IE与Firefox浏览, 效果不尽相同, IE下的效果是我们想要的, 但用Firefox浏览却发现元素h1嵌入到元素div中, 你能解决这个Firefox不能正常显示的问题吗? 如果可以, 能和刚刚学过的折叠现象联系起来吗? 请在留言给出你的答案! (Jorux的解释会在留言中或是下篇文章中给出) [END]

注: 该问题有一定难度, 请读者量力而行!

至此, 《If you love css …》的属性篇全部结束, 下篇将进入《布局篇》.

本站更新速度很慢, 却起到了督促大家订阅Feed的作用, 订阅流量首次突破了40. 本人尤其不喜欢来自搜索引擎的流量, 那些visitors基本属于走马观花和掠夺类; 而特别关注来自Feed和大家收藏夹的流量, 你们才是本网站得以维继的保障和动力.

布局篇 (1)

从本篇开始讲述如何用css实现网页的布局，即如何用css控制网页内各个元素的显示位置。
如果你是一个初学者，很可能觉得做一个网页的第一步就是布局。其实不然，css网页的设计过程可以参考分为以下几步：

平面设计—>页面切割—>布局—>细节控制

1. 平面设计是一个网页的精髓所在，读者直接面对的界面，在本站以后的文章中会涉及此方面内容。也许你会认为在脑子里形成一个网站的大概印象就可以了，那么这种想法直接就给你贴上业余的标签。推荐工具：Photoshop；
2. 页面切割其实可以划分到布局里，因为你的页面切割方式直接影响了布局方式，也体现了你是属于表格布局阵营还是css布局阵营。它是布局(前)的关键步骤。将在本章重点讲述。推荐工具：Photoshop；
3. 如果把布局说简单点，就先得把你的网页简单化。简单到把你的网站分为header，content，sidebar，footer四个部分。
4. 细节控制，将header，content，sidebar，footer的表现细节化。
本章将针对页面切割讲述一些Jorux的私人观点与技术。

首先，我们要有一个被切割的对象，这里以Jorux.com的原始photoshop平面设计图为例。如下,点击看大图(Fig.01):

如果使用表格布局的话，你可能就想到在photoshop里把页面切割为无数个固定宽高的格子。但用css布局的话，你首先要明确的以下几点是：

1. 你是要横着切，还是要竖着切；
2. 第一次切割，只需要把网页中的背景图片切割出来(因为背景图片是在css里声明的)；
3. 切的的图片要尽量小，然后让css去做更多的事情；
4. 设计比较复杂的部分，可以不分割，从而减少css编码的难度；
5. 读者应该根据自己的能力，找出哪些效果css可以轻松实现，而哪些效果用图片更加简单而且size不大，仔细在3.4之间权衡利弊；

现在我们来看Fig.01, 最靠上的部分是个黑色的尺子，遮住了“Jorux记事本”，更遭的是它居然还有黑色的投影。等到要切割的时候，我才后悔当初怎么能设计得如此复杂。但没关系，一切都会好起来的。现在跟着来重温我的切割思路：
1. 整个网页背景色大家应该很清楚，就是深灰色#444，这无需图片，在css里的body选择器里声明就好；
2. 这一步是最关键的一步，请读者仔细体会。对于css布局的网页，Jorux建议你首先给你的原始平面设计来两个横刀(红色)，接着就来个竖刀(蓝色)。实现如下效果(点击看大图Fig.02)：

一定要先横再竖，即先把你的网页分为top，mid，bottom三个部分，然后再把mid分为content和sidebar两个部分。对于单栏样式，只需要两横刀，而对于三栏样式，可能就需要两横两竖刀。

这样你就得到header，content，sidebar，footer四个部分。现在来分析这四个部分的背景图片需要如何切割。
3. header部分的图片非常复杂，但要把那把尺子单独分离出来几乎是不可能的，而且是没有必要的。因为我们还需用这把尺子实现回首页的超级链接，因此它只能是在html文件中<img/>的图片，而非背景，为了减少css编码难度，我们可以把有投影的圆角也和尺子一起切下来以备用，如图(Fig.03):

那么剩下的稍淡的灰色，就是所需要header的背景，我们只需来两竖刀(宽度在4-6px左右为宜)，得到下图(Fig.04):

相信大家知道如何在css里用repeat-x来实现header的背景效果；
4. 接着就是content的背景，很容易看出就是那个带投影的白色背景。马上来上两横刀，高度同样在4-6px为宜，如下图(Fig.05)，在css里用repeat-y就能实现content背景；

5. 还好，在本设计中sidebar无背景，直接继承body的灰色背景就好；
6. footer背景比较简单，就是两个圆角而已，我为了图方便，把那个logo也切了下来，如下(Fig.06)：

这样我们就完成了第一次切割，并且把一切涉及布局的背景图片切了下来。接着就是如何用css布局了，我们的目标是实现以下效果图(Fig.07)：

那么现在就开始编写html代码。既然我们有了一清晰的布局效果图，编写html代码应该是很简单的一件事情。但是越是简单的事，人们忽略的东西就越多。以下Jorux的观点请一定仔细考量：
***对于DIV的使用，请一定从大到小，把那些能在一起的元素划分到一个DIV，然后再在此DIV中继续划分小DIV。所以为实现(Fig.07)的效果图，需要做以下几步：
1. 我们首先把header，content，sidebar，footer归为一个DIV，ID=“AllWrap”；用其实现向左浮动；
2. 接着把header作为一个DIV，ID=“Header”；
3. 把content和sidebar划分在一个DIV，ID=“MidWrap”；然后在其内继续划分为两个DIV，ID分别为Content和Sidebar；
4. 最后把footer作为一个DIV，ID=“Footer”；

最终得到Html代码为：

<!DOCTYPE HTML PUBLIC “-//W3C//DTD HTML 4.0 Transitional//EN”>
<HTML>
<HEAD>
<TITLE>My layout</TITLE>
</HEAD>
<BODY>
<div id=”AllWrap”>
<div id=”Header”>Header</div>
<div id=”MidWrap”>
<div id=”Content”>Content</div>
<div id=”Sidebar”>Sidebar</div>
</div>
<div id=”Footer”>Footer</div>
</div>
</BODY>
</HTML>

给读者留一个问题，请有能力的朋友在留言中给出实现效果图样式的css代码。有如下要求：
1. 向左浮动；
2. AllWrap宽为760px；Content宽为560px，高400px；Sidebar宽为200px，高400px；Footer高为80px，宽760px；
2. 在800×600，1024×768，或是宽屏显示器都能正常显示；
3. 在IE6/7, Opera, Firefox均能显示正常；

本篇正文完(答案将在留言中讨论，下篇将讲述如何用css实现最终布局与背景显示)

以上内容涉及某些人的商业利益，出于某些原因大家都不善于把那些能转化为利益的内容泄露出来，于是那些教程总是让人感到不得其所。
我的一个客户曾跟我说在他们的领域(PR)，大家不会免费把任何有价值的东西告诉客户，或是大众。其实在任何领域都是一样的，大部分人宁愿让他的知识在脑中腐朽，也不会透露出半点来。
我知道的东西不多，但碰巧正是我的读者所渴求的。所以，我决定把这些对于我最有价值的知识写出来，即使你们当中会有一些成为我竞争的对手。换一个角度，如果能成为合作伙伴，那是最好不过了。如果本教程没有让你的失望，还请继续支持。

致歉：要对我的读者说声抱歉，这篇教程整整晚了10天。如果一两个星期更新不了一篇post，就有放那80个Readers鸽子的感觉，用一部分乖学生的话讲就是和逃了一整天课一样，颇感沉重。推迟的一个重要原因是因为我在：

Moving to Ubuntu：
1. Ubuntu是一个让我割舍不下的免费系统，自从发现了hacked的iso免安装方法后，它的优势是如此明显：免费，无盗版，免安装，能读取/写入windows分区的文件，无需分区格式化，1分钟重装，没安驱动就识别了我的ASUS laptop上除modem以外的所有硬件。

2. 它有一个优秀的替代Editplus的编程工具–Bluefish，我管它叫胖嘴蓝鱼。编代码时有条胖鱼陪着，气氛能缓和很多。

3. 老实说Ubuntu是个严重依赖网络的系统，如果你的上网条件不佳，建议不要尝试。但一旦上了网，它就不得了了，以下这个信息就很Cool：

4. Firefox与其说是个浏览器，还不如说是个软件库，FTP上传，离线写博，Gmail硬盘，截屏，取色，Debug，网页收藏，多线程下载，Feed阅读器(Sage)，屏幕取词翻译，Gmail邮件提醒；

5. Ubuntu+Firefox+Bluefish+Mplayer+宽带=实现了像我这种不玩游戏，不聊天，以design/coding为乐趣的，无聊80后的理性与感性需要。

免费Paypal小额转账服务：

更新变慢的另一个原因就是因为要完成几个projects。还好，北美人的信誉不错，网络诈骗还没有发生在我的身上，因此收到一些paypal美金。网上订购一台Dell已经不是梦想了。
如果有想买一些线上产品，如国外空间，域名等，因为没有paypal电子现金而苦恼的朋友，而且你还信任Jorux的话，我可以免费把你的人民币转换成paypal的电子美金（单笔不超过100USD）。

布局篇 (2)

上篇讲的布局(切割)方法相信大家己经掌握，其特点是用固定宽度(fixed-width)来实现页面内各个元素的显示效果，简单实用，易于掌握。本篇将讲述其他相对高级的布局方法。

布局的分类,根据对元素宽度的定义不同，布局可分为以下几种：

1. 固定宽度(fixed-width)布局：
即上一篇所用到的布局方法，其特点是主要DIV的宽度都是用固定大小的px值定义的，其宽度不随浏览器及字体大小的影响。多以px作为宽度单位。
优点：宽度固定，最为保真地表现作者的设计意图；简单易学。
缺点：它是这几种布局方法中最为“自私”的，因为其不考虑访客的浏览设备及设置。比方说，在1024宽的浏览器表现良好的布局，到了1600以上的宽屏浏览器就显得较为局限，其可能缩于屏幕一侧或是中央。
现状：由于其简单易用，且基本满足大部分访客的需求，仍是主要的布局方法，尤其在大陆。
趋势：随着液晶屏幕的发展和普及，成本的下降，市面上宽屏显示器已经开始大量涌现，尤其在西方发达国家，1600的宽屏显示器渐成主流，所以在800或是1024下显示良好的，使用固定宽度的布局方法已经开始被边缘化。
站点举例：网易蓝色理想

2. 自适应(liquid)布局：
即根据用户浏览器的宽度，自动调整宽度显示的布局方法。以%作为宽度单位。
优点：自动适应各种屏幕宽度，轻松实现满屏显示。
缺点：如果屏幕过宽，而内容有不多的情况下，文字可能拉伸得很长，影响美观。而当屏幕过窄时，内容又会被压缩得很窄，可读性下降。
现状：目前很多的web2.0站点使用%来实现布局，即自适应访客的屏幕宽度。
趋势：就目前的情况看来，没有哪一个屏幕分辨率能够统一整个市场，所以800，1028，1280，1600等各种宽度的浏览器会各据一方，并且只会越来越宽，liquid布局以其良好的自适应能力将成为主要的布局方法。
站点举例：Google Cssliquid

3. 弹性（elastic）布局：
所谓弹性，是针对字体大小而言的，当用户调整字体大小时，宽度会随字体大小的变化而变化。以字体高em作为宽度单位。
优点：满足了视力下降人士的需求，不管用户如何调整字体大小，页面布局都不会被打乱；
缺点：当用户不调整字体大小时，其效果和固定宽度布局的效果其实是一样的，即不会随屏幕的大小的变化而自适应；对于初学者不易掌握。
现状：弹性布局方法是目前较为少用的一种。
趋势：由于其能自动适应字体大小，且在用户不调整字体大小时，有固定的宽度，所以它将是固定宽度布局的不错的替代方法。
站点举例：BlueAnvil Designbit(可能需要代理浏览)

4. 混合布局：
即在一个页面内用两种以上的布局方法。比方说对于内容DIV使用%，而在边框DIV则使用em作为宽度单位。

如何选择适合你的布局方法：
在选择布局方法时，你需要考虑以下两个方面：
1.你的原始设计：
当你的原始设计的宽度固定，或是宽度过大会影响美观的话，Jorux建议你使用弹性布局。
2.用户群的分布：
如果你的用户群数量巨大，也就是说使用各种屏幕分辨率的用户都会达到一个可观的数量，那么你在布局时就应该考虑使用自适应的方法布局。并且在设计时考虑到这点，设计出在各种宽度下拉伸都基本保持美观的作品。

你可以看到，固定宽度的布局方法已经不是Jorux的推荐方法。这也是Jorux写这篇文章的原因，大家应该秉持以用户为中心的设计理念，更好地适应各种客户端及其设置。本站也会很快改写CSS。

怎样使用弹性布局的方法实现布局篇(1)中的效果：
1. 在body中设置font-size:62.5%，使得1em=10px；
2. 避免在任何DIV中声明字体大小，这是为了避免em的值因继承父级元素的字体高而不断变化，而在DIV的子元素中声明字体大小。如在p,h1等选择器中声明。
3. 用em作为宽度和高度单位，例如你想设置DIV宽度为760px，那么你需要将其设置为76em。
那么css代码可以为(仅供参考)：

* {
margin: 0;
padding: 0;
}
body{
font-size:62.5%;
background-color:#444;
}
#AllWrap {
float:left;
width: 76em;
background-color: white;
}
#Header {
height: 8em;
background-color: red;
}
#MidWrap {
}
#Content {
float:left;
width:56em;
height:40em;
background-color: white;
}
#Sidebar {
float:left;
width:20em;
height:40em;
background-color: green;
}
#Footer {
clear:both;
width:76em;
height: 8em;
background-color: blue;
}

你可以尝试调整浏览器的字体大小，可以看到各个DIV也随之变化。

如何用%实现自适应的布局方法：
满足以下条件：
1.满屏显示，及Header，(Content+Sidebar),Footer的宽度均自动适应屏幕宽度，并占满整个屏幕宽度；
2.Content占70%屏幕宽，Sidebar占30%，高度均为400px；
3.Header和Footer的高度均为为80px；
4. 实现如下效果效果(点击看大图)：

读者能在留言中给出你的答案吗？
精彩在留言，请读者踊跃解答，参与讨论关于布局的任何问题，Jorux将在留言中响应大家的提问或是解答。

Important reference:
1. Going elastic with ems, layout techniques
2. Fixed vs Liquid vs Elastic
3. Create a simple liquid layout
4. Elastic Design

Friday, May 25, 2007

MySQL查询优化技术系列讲座 [整理]

MySQL查询优化技术系列讲座之使用索引

2005-09-09 09:09 作者：陶刚编译出处：天极网　　　　

　　索引是提高查询速度的最重要的工具。当然还有其它的一些技术可供使用，但是一般来说引起最大性能差异的都是索引的正确使用。在MySQL邮件列表中，人们经常询问那些让查询运行得更快的方法。在大多数情况下，我们应该怀疑数据表上有没有索引，并且通常在添加索引之后立即解决了问题。当然，并不总是这样简单就可以解决问题的，因为优化技术本来就并非总是简单的。然而，如果没有使用索引，在很多情况下，你试图使用其它的方法来提高性能都是在浪费时间。首先使用索引来获取最大的性能提高，接着再看其它的技术是否有用。
　　这一部分讲述了索引是什么以及索引是怎么样提高查询性能的。它还讨论了在某些环境中索引可能降低性能，并为你明智地选择数据表的索引提供了一些指导方针。在下一部分中我们将讨论MySQL查询优化器，它试图找到执行查询的效率最高的方法。了解一些优化器的知识，作为对如何建立索引的补充，对我们是有好处的，因为这样你才能更好地利用自己所建立的索引。某些编写查询的方法实际上让索引不起作用，在一般情况下你应该避免这种情形的发生。

　　索引的优点

　　让我们开始了解索引是如何工作的，首先有一个不带索引的数据表。不带索引的表仅仅是一个无序的数据行集合。例如，图1显示的ad表就是不带索引的表，因此如果需要查找某个特定的公司，就必须检查表中的每个数据行看它是否与目标值相匹配。这会导致一次完全的数据表扫描，这个过程会很慢，如果这个表很大，但是只包含少量的符合条件的记录，那么效率会非常低。

图1：无索引的ad表

　　图2是同样的一张数据表，但是增加了对ad表的company_num数据列的索引。这个索引包含了ad表中的每个数据行的条目，但是索引的条目是按照company_num值排序的。现在，我们不是逐行查看以搜寻匹配的数据项，而是使用索引。假设我们查找公司13的所有数据行。我们开始扫描索引并找到了该公司的三个值。接着我们碰到了公司14的索引值，它比我们正在搜寻的值大。索引值是排过序的，因此当我们读取了包含14的索引记录的时候，我们就知道再也不会有更多的匹配记录，可以结束查询操作了。因此使用索引获得的功效是：我们找到了匹配的数据行在哪儿终止，并能够忽略其它的数据行。另一个功效来自使用定位算法查找第一条匹配的条目，而不需要从索引头开始执行线性扫描（例如，二分搜索就比线性扫描要快一些）。通过使用这种方法，我们可以快速地定位第一个匹配的值，节省了大量的搜索时间。数据库使用了多种技术来快速地定位索引值，但是在本文中我们不关心这些技术。重点是它们能够实现，并且索引是个好东西。

图2：索引后的ad表

　　你可能要问，我们为什么不对数据行进行排序从而省掉索引？这样不是也能实现同样的搜索速度的改善吗？是的，如果表只有一个索引，这样做也可能达到相同的效果。但是你可能添加第二个索引，那么就无法一次使用两种不同方法对数据行进行排序了（例如，你可能希望在顾客名称上建立一个索引，在顾客ID号或电话号码上建立另外一个索引）。把与数据行相分离的条目作为索引解决了这个问题，允许我们创建多个索引。此外，索引中的行一般也比数据行短一些。当你插入或删除新的值的时候，移动较短的索引值比移动较长数据行的排序次序更加容易。
　　不同的MySQL存储引擎的索引实现的具体细节信息是不同的。例如，对于MyISAM数据表，该表的数据行保存在一个数据文件中，索引值保存在索引文件中。一个数据表上可能有多个索引，但是它们都被存储在同一个索引文件中。索引文件中的每个索引都包含一个排序的键记录（它用于快速地访问数据文件）数组。
　　与此形成对照的是，BDB和InnoDB存储引擎没有使用这种方法来分离数据行和索引值，尽管它们也把索引作为排序后的值集合进行操作。在默认情况下，BDB引擎使用单个文件存储数据和索引值。InnoDB使用单个数据表空间（tablespace），在表空间中管理所有InnoDB表的数据和索引存储。我们可以把InnoDB配置为每个表都在自己的表空间中创建，但是即使是这样，数据表的数据和索引也存储在同一个表空间文件中。
前面的讨论描述了单个表查询环境下的索引的优点，在这种情况下，通过减少对整个表的扫描，使用索引明显地提高了搜索的速度。当你运行涉及多表联结（jion）查询的时候，索引的价值就更高了。在单表查询中，你需要在每个数据列上检查的值的数量是表中数据行的数量。在多表查询中，这个数量可能大幅度上升，因为这个数量是这些表中数据行的数量所产生的。
　　假设你拥有三个未索引的表t1、t2和t3，每个表都分别包含数据列i1、i2和i3，并且每个表都包含了1000条数据行，其序号从1到1000。查找某些值匹配的数据行组合的查询可能如下所示：

　　　　SELECT t1.i1, t2.i2, t3.i3

　　　　FROM t1, t2, t3

　　　　WHERE t1.i1 = t2.i2 AND t2.i1 = t3.i3;

　　这个查询的结果应该是1000行，每个数据行包含三个相等的值。如果在没有索引的情况下处理这个查询，那么如果我们不对这些表进行全部地扫描，我们是没有办法知道哪些数据行含有哪些值的。因此你必须尝试所有的组合来查找符合WHERE条件的记录。可能的组合的数量是1000 x 1000 x 1000（10亿！），它是匹配记录的数量的一百万倍。这就浪费了大量的工作。这个例子显示，如果没有使用索引，随着表的记录不断增长，处理这些表的联结所花费的时间增长得更快，导致性能很差。我们可以通过索引这些数据表来显著地提高速度，因为索引让查询采用如下所示的方式来处理：
　　1．选择表t1中的第一行并查看该数据行的值。
　　2．使用表t2上的索引，直接定位到与t1的值匹配的数据行。类似地，使用表t3上的索引，直接定位到与表t2的值匹配的数据行。
　　3．处理表t1的下一行并重复前面的过程。执行这样的操作直到t1中的所有数据行都被检查过。
　　在这种情况下，我们仍然对表t1执行了完整的扫描，但是我们可以在t2和t3上执行索引查找，从这些表中直接地获取数据行。理论上采用这种方式运行上面的查询会快一百万倍。当然这个例子是为了得出结论来人为建立的。然而，它解决的问题却是现实的，给没有索引的表添加索引通常会获得惊人的性能提高。
　　MySQL有几种使用索引的方式：
　　· 如上所述，索引被用于提高WHERE条件的数据行匹配或者执行联结操作时匹配其它表的数据行的搜索速度。
　　· 对于使用了MIN()或MAX()函数的查询，索引数据列中最小或最大值可以很快地找到，不用检查每个数据行。
　　· MySQL利用索引来快速地执行ORDER BY和GROUP BY语句的排序和分组操作。
　　· 有时候MySQL会利用索引来读取查询得到的所有信息。假设你选择了MyISAM表中的被索引的数值列，那么就不需要从该数据表中选择其它的数据列。在这种情况下，MySQL从索引文件中读取索引值，它所得到的值与读取数据文件得到的值是相同的。没有必要两次读取相同的值，因此没有必要考虑数据文件。

　　索引的代价

　　一般来说，如果MySQL能够找到方法，利用索引来更快地处理查询，它就会这样做。这意味着，对于大多数情况，如果你没有对表进行索引，就会使性能受到损害。这就是我所描绘的索引优点的美景。但是它有缺点吗？有的，它在时间和空间上都有开销。在实践中，索引的优点的价值一般会超过这些缺点，但是你也应该知道到底有一些什么缺点。
　　首先，索引加快了检索的速度，但是减慢了插入和删除的速度，同时还减慢了更新被索引的数据列中的值的速度。也就是说，索引减慢了大多数涉及写操作的速度。发生这种现象的原因在于写入一条记录的时候不但需要写入数据行，还需要改变所有的索引。数据表带有的索引越多，需要做出的修改就越多，平均性能的降低程度也就越大。在本文的"高效率载入数据"部分中，我们将更细致地了解这些现象并找出处理方法。
　　其次，索引会花费磁盘空间，多个索引相应地花费更多的磁盘空间。这可能导致更快地到达数据表的大小限制：
　　· 对于MyISAM表，频繁地索引可能引起索引文件比数据文件更快地达到最大限制。
　　· 对于BDB表，它把数据和索引值一起存储在同一个文件中，添加索引引起这种表更快地达到最大文件限制。
　　· 在InnoDB的共享表空间中分配的所有表都竞争使用相同的公共空间池，因此添加索引会更快地耗尽表空间中的存储。但是，与MyISAM和BDB表使用的文件不同，InnoDB共享表空间并不受操作系统的文件大小限制，因为我们可以把它配置成使用多个文件。只要有额外的磁盘空间，你就可以通过添加新组件来扩展表空间。
　　使用单独表空间的InnoDB表与BDB表受到的约束是一样的，因为它的数据和索引值都存储在单个文件中。
　　这些要素的实际含义是：如果你不需要使用特殊的索引帮助查询执行得更快，就不要建立索引。

　　选择索引

　　假设你已经知道了建立索引的语法，但是语法不会告诉你数据表应该如何索引。这要求我们考虑数据表的使用方式。这一部分指导你如何识别出用于索引的备选数据列，以及如何最好地建立索引：
　　用于搜索、排序和分组的索引数据列并不仅仅是用于输出显示的。换句话说，用于索引的最好的备选数据列是那些出现在WHERE子句、join子句、ORDER BY或GROUP BY子句中的列。仅仅出现在SELECT关键字后面的输出数据列列表中的数据列不是很好的备选列：

　　　　SELECT
　　　　col_a <- 不是备选列
　　　　FROM
　　　　tbl1 LEFT JOIN tbl2
　　　　ON tbl1.col_b = tbl2.col_c <- 备选列
　　　　WHERE
　　　　col_d = expr; <- 备选列

　　当然，显示的数据列与WHERE子句中使用的数据列也可能相同。我们的观点是输出列表中的数据列本质上不是用于索引的很好的备选列。
　　Join子句或WHERE子句中类似col1 = col2形式的表达式中的数据列都是特别好的索引备选列。前面显示的查询中的col_b和col_c就是这样的例子。如果MySQL能够利用联结列来优化查询，它一定会通过减少整表扫描来大幅度减少潜在的表－行组合。
　　考虑数据列的基数（cardinality）。基数是数据列所包含的不同值的数量。例如，某个数据列包含值1、3、7、4、7、3，那么它的基数就是4。索引的基数相对于数据表行数较高（也就是说，列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。如果某数据列含有很多不同的年龄，索引会很快地分辨数据行。如果某个数据列用于记录性别（只有"M"和"F"两种值），那么索引的用处就不大。如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是"30%"。现在查询优化器更加复杂，把其它一些因素也考虑进去了，因此这个百分比并不是MySQL决定选择使用扫描还是索引的唯一因素。
　　索引较短的值。尽可能地使用较小的数据类型。例如，如果MEDIUMINT足够保存你需要存储的值，就不要使用BIGINT数据列。如果你的值不会长于25个字符，就不要使用CHAR(100)。较小的值通过几个方面改善了索引的处理速度：
　　· 较短的值可以更快地进行比较，因此索引的查找速度更快了。
　　· 较小的值导致较小的索引，需要更少的磁盘I/O。
　　· 使用较短的键值的时候，键缓存中的索引块（block）可以保存更多的键值。MySQL可以在内存中一次保持更多的键，在不需要从磁盘读取额外的索引块的情况下，提高键值定位的可能性。
　　对于InnoDB和BDB等使用聚簇索引（clustered index）的存储引擎来说，保持主键（primary key）短小的优势更突出。聚簇索引中数据行和主键值存储在一起（聚簇在一起）。其它的索引都是次级索引；它们存储主键值和次级索引值。次级索引屈从主键值，它们被用于定位数据行。这暗示主键值都被复制到每个次级索引中，因此如果主键值很长，每个次级索引就需要更多的额外空间。
　　索引字符串值的前缀（prefixe）。如果你需要索引一个字符串数据列，那么最好在任何适当的情况下都应该指定前缀长度。例如，如果有CHAR(200)数据列，如果前面10个或20个字符都不同，就不要索引整个数据列。索引前面10个或20个字符会节省大量的空间，并且可能使你的查询速度更快。通过索引较短的值，你可以获得那些与比较速度和磁盘I/O节省相关的好处。当然你也需要利用常识。仅仅索引某个数据列的第一个字符串可能用处不大，因为如果这样操作，那么在索引中不会有太多的唯一值。
　　你可以索引CHAR、VARCHAR、BINARY、VARBINARY、BLOB和TEXT数据列的前缀。
　　使用最左（leftmost）前缀。建立多列复合索引的时候，你实际上建立了MySQL可以使用的多个索引。复合索引可以作为多个索引使用，因为索引中最左边的列集合都可以用于匹配数据行。这种列集合被称为"最左前缀"（它与索引某个列的前缀不同，那种索引把某个列的前面几个字符作为索引值）。
　　假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列，因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值，或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：

　　　　state, city, zip
　　　　state, city
　　　　state

　　MySQL不能利用这个索引来搜索没有包含在最左前缀的内容。例如，如果你按照city或zip来搜索，就不会使用到这个索引。如果你搜索给定的state和具体的ZIP代码（索引的1和3列），该索引也是不能用于这种组合值的，尽管MySQL可以利用索引来查找匹配的state从而缩小搜索的范围。
　　不要过多地索引。不要认为"索引越多，性能越高"，不要对每个数据列都进行索引。我们在前面提到过，每个额外的索引都会花费更多的磁盘空间，并降低写操作的性能。当你修改表的内容的时候，索引就必须被更新，甚至可能重新整理。如果你的索引很少使用或永不使用，你就没有必要减小表的修改操作的速度。此外，为检索操作生成执行计划的时候，MySQL会考虑索引。建立额外的索引会给查询优化器增加更多的工作量。如果索引太多，有可能（未必）出现MySQL选择最优索引失败的情况。维护自己必须的索引可以帮助查询优化器来避免这类错误。
　　如果你考虑给已经索引过的表添加索引，那么就要考虑你将增加的索引是否是已有的多列索引的最左前缀。如果是这样的，不用增加索引，因为已经有了（例如，如果你在state、city和zip上建立了索引，那么没有必要再增加state的索引）。
　　让索引类型与你所执行的比较的类型相匹配。在你建立索引的时候，大多数存储引擎会选择它们将使用的索引实现。例如，InnoDB通常使用B树索引。MySQL也使用B树索引，它只在三维数据类型上使用R树索引。但是，MEMORY存储引擎支持散列索引和B树索引，并允许你选择使用哪种索引。为了选择索引类型，需要考虑在索引数据列上将执行的比较操作类型：
　　· 对于散列（hash）索引，会在每个数据列值上应用散列函数。生成的结果散列值存储在索引中，并用于执行查询。散列函数实现的算法类似于为不同的输入值生成不同的散列值。使用散列值的好处是散列值比原始值的比较效率更高。散列索引用于执行=或<=>操作等精确匹配的时候速度非常快。但是对于查询一个值的范围效果就非常差了：

　　　　id < 30
　　　　weight BETWEEN 100 AND 150

　　· B树索引可以用于高效率地执行精确的或者基于范围（使用操作<、<=、=、>=、>、<>、!=和BETWEEN）的比较。B树索引也可以用于LIKE模式匹配，前提是该模式以文字串而不是通配符开头。
　　如果你使用的MEMORY数据表只进行精确值查询，散列索引是很好的选择。这是MEMORY表使用的默认的索引类型，因此你不需要特意指定。如果你希望在MEMORY表上执行基于范围的比较，应该使用B树索引。为了指定这种索引类型，需要给索引定义添加USING BTREE。例如：

　　　　CREATE TABLE lookup
　　　　(
　　　　id INT NOT NULL,
　　　　name CHAR(20),
　　　　PRIMARY KEY USING BTREE (id)
　　　　) ENGINE = MEMORY;

　　如果你希望执行的语句的类型允许，单个MEMORY表可以同时拥有散列索引和B树索引，即使在同一个数据列上。
　　有些类型的比较不能使用索引。如果你只是通过把值传递到函数（例如STRCMP()）中来执行比较操作，那么对它进行索引就没有价值。服务器必须计算出每个数据行的函数值，它会排除数据列上索引的使用。
　　使用慢查询（slow-query）日志来识别执行情况较差的查询。这个日志可以帮助你找出从索引中受益的查询。你可以直接查看日志（它是文本文件），或者使用mysqldumpslow工具来统计它的内容。如果某个给定的查询多次出现在"慢查询"日志中，这就是一个线索，某个查询可能没有优化编写。你可以重新编写它，使它运行得更快。你要记住，在评估"慢查询"日志的时候，"慢"是根据实际时间测定的，在负载较大的服务器上"慢查询"日志中出现的查询会多一些。

MySQL查询优化系列讲座之查询优化器

2005-09-13 08:23 作者：陶刚编译出处：天极网

　　当你提交一个查询的时候，MySQL会分析它，看是否可以做一些优化使处理该查询的速度更快。这一部分将介绍查询优化器是如何工作的。如果你想知道MySQL采用的优化手段，可以查看MySQL参考手册。

　　当然，MySQL查询优化器也利用了索引，但是它也使用了其它一些信息。例如，如果你提交如下所示的查询，那么无论数据表有多大，MySQL执行它的速度都会非常快：

　　　　SELECT * FROM tbl_name WHERE 0;

　　在这个例子中，MySQL查看WHERE子句，认识到没有符合查询条件的数据行，因此根本就不考虑搜索数据表。你可以通过提供一个EXPLAIN语句看到这种情况，这个语句让MySQL显示自己执行的但实际上没有真正地执行的SELECT查询的一些信息。如果要使用EXPLAIN，只需要在EXPLAIN单词放在SELECT语句的前面：

　　　　mysql> EXPLAIN SELECT * FROM tbl_name WHERE 0G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: NULL
　　　　type: NULL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: NULL
　　　　Extra: Impossible WHERE

　　通常情况下，EXPLAIN返回的信息比上面的信息要多一些，还包括用于扫描数据表的索引、使用的联结类型、每张数据表中估计需要检查的数据行数量等非空（NULL）信息。

　　优化器是如何工作的

　　MySQL查询优化器有几个目标，但是其中最主要的目标是尽可能地使用索引，并且使用最严格的索引来消除尽可能多的数据行。你的最终目标是提交SELECT语句查找数据行，而不是排除数据行。优化器试图排除数据行的原因在于它排除数据行的速度越快，那么找到与条件匹配的数据行也就越快。如果能够首先进行最严格的测试，查询就可以执行地更快。假设你的查询检验了两个数据列，每个列上都有索引：

　　　　SELECT col3 FROM mytable
　　　　WHERE col1 = ’some value’ AND col2 = ’some other value’;

　　假设col1上的测试匹配了900个数据行，col2上的测试匹配了300个数据行，而同时进行的测试只得到了30个数据行。先测试Col1会有900个数据行，需要检查它们找到其中的30个与col2中的值匹配记录，其中就有870次是失败了。先测试col2会有300个数据行，需要检查它们找到其中的30个与col1中的值匹配的记录，只有270次是失败的，因此需要的计算和磁盘I/O更少。其结果是，优化器会先测试col2，因为这样做开销更小。

　　你可以通过下面一个指导帮助优化器更好地利用索引：

　　尽量比较数据类型相同的数据列。当你在比较操作中使用索引数据列的时候，请使用数据类型相同的列。相同的数据类型比不同类型的性能要高一些。例如，INT与BIGINT是不同的。CHAR(10)被认为是CHAR(10)或VARCHAR(10)，但是与CHAR(12)或VARCHAR(12)不同。如果你所比较的数据列的类型不同，那么可以使用ALTER TABLE来修改其中一个，使它们的类型相匹配。

　　尽可能地让索引列在比较表达式中独立。如果你在函数调用或者更复杂的算术表达式条件中使用了某个数据列，MySQL就不会使用索引，因为它必须计算出每个数据行的表达式值。有时候这种情况无法避免，但是很多情况下你可以重新编写一个查询让索引列独立地出现。

　　下面的WHERE子句显示了这种情况。它们的功能相同，但是对于优化目标来说就有很大差异了：

　　　　WHERE mycol < 4 / 2
　　　　WHERE mycol * 2 < 4

　　对于第一行，优化器把表达式4/2简化为2，接着使用mycol上的索引来快速地查找小于2的值。对于第二个表达式，MySQL必须检索出每个数据行的mycol值，乘以2，接着把结果与4进行比较。在这种情况下，不会使用索引。数据列中的每个值都必须被检索到，这样才能计算出比较表达式左边的值。

　　我们看另外一个例子。假设你对date_col列进行了索引。如果你提交一条如下所示的查询，就不会使用这个索引：

　　　　SELECT * FROM mytbl WHERE YEAR(date_col) < 1990;

　　这个表达式不会把1990与索引列进行比较；它会把1990与该数据列计算出来的值比较，而每个数据行都必须计算出这个值。其结果是，没有使用date_col上的索引，因为执行这样的查询需要全表扫描。怎么解决这个问题呢？只需要使用文本日期，接着就可以使用date_col上的索引来查找列中匹配的值了：

　　　　WHERE date_col < ’1990-01-01’

　　但是，假设你没有特定的日期。你可能希望找到一些与今天相隔固定的几天的日期的记录。表达这种类型的比较有很多种方法--它们的效率并不同。下面就有三种：

　　　　WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
　　　　WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
　　　　WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　对于第一行，不会用到索引，因为每个数据行都必须检索以计算出TO_DAYS(date_col)的值。第二行要好一些。Cutoff和TO_DAYS(CURDATE())都是常量，因此在处理查询之前，比较表达式的右边可以被优化器一次性计算出来，而不需要每个数据行都计算一次。但是date_col列仍然出现在函数调用中，它阻止了索引的使用。第三行是这几个中最好的。同样，在执行查询之前，比较表达式的右边可以作为常量一次性计算出来，但是现在它的值是一个日期。这个值可以直接与date_col值进行比较，再也不需要转换成天数了。在这种情况下，会使用索引。

　　在LIKE模式的开头不要使用通配符。有些字符串搜索使用如下所示的WHERE子句：

　　　　WHERE col_name LIKE ’%string%’

　　如果你希望找到那些出现在数据列的任何位置的字符串，这个语句就是对的。但是不要因为习惯而简单地把"%"放在字符串的两边。如果你在查找出现在数据列开头的字符串，就删掉前面的"%"。假设你要查找那些类似MacGregor或MacDougall等以"Mac"开头的名字。在这种情况下，WHERE子句如下所示：

　　　　WHERE last_name LIKE ’Mac%’

　　优化器查看该模式中词首的文本，并使用索引找到那些与下面的表达式匹配的数据行。下面的表达式是使用last_name索引的另一种形式：

　　　　WHERE last_name >= ’Mac’ AND last_name < ’Mad’

　　这种优化不能应用于使用了REGEXP操作符的模式匹配。REGEXP表达式永远不会被优化。

　　帮助优化器更好的判断索引的效率。在默认情况下，当你把索引列的值与常量进行比较的时候，优化器会假设键值在索引内部是均匀分布的。在决定进行常量比较是否使用索引的时候，优化器会快速地检查索引，估计出会用到多少个实体（entry）。对应MyISAM、InnoDB和BDB数据表来说，你可以使用ANALYZE TABLE让服务器执行对键值的分析。它会为优化器提供更好的信息。

　　使用EXPLAIN验证优化器的操作。EXPLAIN语句可以告诉你是否使用了索引。当你试图用另外的方式编写语句或检查添加索引是否会提高查询执行效率的时候，这些信息对你是有帮助的。

　　在必要的时候给优化器一些提示。正常情况下，MySQL优化器自由地决定扫描数据表的次序来最快地检索数据行。在有些场合中优化器没有作出最佳选择。如果你察觉这种现象发生了，就可以使用STRAIGHT_JOIN关键字来重载优化器的选择。带有STRAIGHT_JOIN的联结类似于交叉联结，但是强迫数据表按照FROM子句中指定的次序来联结。

　　在SELECT语句中有两个地方可以指定STRAIGHT_JOIN。你可以在SELECT关键字和选择列表之间的位置指定，这样会对语句中所有的交叉联结产生影响；你也可以在FROM子句中指定。下面的两个语句功能相同：

　　　　SELECT STRAIGHT_JOIN ... FROM t1, t2, t3 ... ;
　　　　SELECT ... FROM t1 STRAIGHT_JOIN t2 STRAIGHT_JOIN t3 ... ;

　　分别在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的情况下运行这个查询；MySQL可能因为什么原因没有按照你认为最好的次序使用索引（你可以使用EXPLAIN来检查MySQL处理每个语句的执行计划）。

　　你还可以使用FORCE INDEX、USE INDEX或IGNORE INDEX来指导服务器如何使用索引。

　　利用优化器更加完善的区域。MySQL可以执行联结和子查询，但是子查询是最近才支持的，是在MySQL 4.1中添加的。因而在很多情况下，优化器对联结操作的调整比对子查询的调整要好一些。当你的子查询执行地很慢的时候，这就是一条实际的提示。有一些子查询可以使用逻辑上相等的联结来重新表达。在可行的情况下，你可以把子查询重新改写为联结，看是否执行地快一些。

　　测试查询的备用形式，多次运行。当你测试查询的备用形式的时候（例如，子查询与等同的联结操作对比），每种方式都应该多次运行。如果两种形式都只运行了一次，那么你通常会发现第二个查询比第一个快，这是因为第一个查询得到的信息仍然保留在缓存中，以至于第二个查询没有真正地从磁盘上读取数据。你还应该在系统负载相对平稳的时候运行查询，以避免系统中其它的事务影响结果。

　　避免过度地使用MySQL自动类型转换。MySQL会执行自动的类型转换，但是如果你能够避免这种转换操作，你得到的性能就更好了。例如，如果num_col是整型数据列，那么下面这些查询将返回相同的结果：

　　　　SELECT * FROM mytbl WHERE num_col = 4;
　　　　SELECT * FROM mytbl WHERE num_col = ’4’;

　　但是第二个查询涉及到了类型转换。转换操作本身为了把整型和字符串型转换为双精度型进行比较，使性能恶化了。更严重的情况是，如果num_col是索引的，那么涉及到类型转换的比较操作不会使用索引。

　　相反类型的比较操作（把字符串列与数值比较）也会阻止索引的使用。假设你编写了如下所示的查询：

　　　　SELECT * FROM mytbl WHERE str_col = 4;

　　在这个例子中，不会使用str_col上的索引，因为在把str_col中的字符串值转换成数值的时候，可能有很多值等于4（例如’4’、’4.0’和’4th’）。分辨哪些值符合要求的唯一办法是读取每个数据行并执行比较操作。

　　使用EXPLAIN来检查优化器的操作

　　EXPLAIN对于了解优化器生成的、用于处理语句的执行计划的内部信息是很有帮助的。在这一部分中，我们将解释EXPLAIN的两种用途：

　　· 查看采用不同的方式编写的查询是否影响了索引的使用。

　　· 查看向数据表添加索引对优化器生成高效率执行计划的能力的影响。

　　这一部分只讨论与示例相关的EXPLAIN输入字段。

　　前面，在"优化器是如何工作的"部分中我们得出的观点是，你编写表达式的方式将决定优化器是否能使用可用的索引。特别是上面的讨论使用了下面三个逻辑相等的WHERE子句的例子，只有第三个允许使用索引：

　　　　WHERE TO_DAYS(date_col) - TO_DAYS(CURDATE()) < cutoff
　　　　WHERE TO_DAYS(date_col) < cutoff + TO_DAYS(CURDATE())
　　　　WHERE date_col < DATE_ADD(CURDATE(), INTERVAL cutoff DAY)

　　EXPLAIN允许你查看编写表达式的某种方式是否比另外的方式好一些。为了看到结果，让我们分别用这三个WHERE子句搜索成员表中过期的数据列值，把cutoff值设为30天。为了看到索引的使用和表达式编写方式之间的关系，我们首先对expiration列进行索引：

　　　　mysql> ALTER TABLE member ADD INDEX (expiration);

　　接着在每个表达式形式上使用EXPLAIN，看优化器生成了什么样的执行计划：

　　　　mysql> EXPLAIN SELECT * FROM MEMBER
　　　　-> WHERE TO_DAYS(expiration) - TO_DAYS(CURDATE()) < 30G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: MEMBER
　　　　type: ALL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: 102
　　　　Extra: Using where
　　　　mysql> EXPLAIN SELECT * FROM MEMBER
　　　　-> WHERE TO_DAYS(expiration) < 30 + TO_DAYS(CURDATE())G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: MEMBER
　　　　type: ALL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: 102
　　　　Extra: Using where
　　　　mysql> EXPLAIN SELECT * FROM MEMBER
　　　　-> WHERE expiration < DATE_ADD(CURDATE(), INTERVAL 30 DAY)G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: MEMBER
　　　　type: range
　　　　possible_keys: expiration
　　　　key: expiration
　　　　key_len: 4
　　　　ref: NULL
　　　　rows: 6
　　　　Extra: Using where

　　上面的结果显示，前面两个语句没有使用索引。类型（type）值表明了将如何从数据表中读取信息。ALL意味着"将检查所有的记录"。也就是说，它会执行全表扫描，没有利用索引。每个与键相关的列都是NULL也表明没有使用索引。

　　与此形成对比的是，第三个语句的结果显示，采用这种方式编写的WHERE子句，优化器可以使用expiration列上的索引：

　　· 类型（type）值表明它可以使用索引来搜索特定范围的值（小于右边表达式给定的值）。

　　· 可能键（possible_keys）和键（key）值显示expiration上的索引已经被考虑作为备选索引，并且它也是真正使用的索引。

　　· 行数（rows）值显示优化器估计自己需要检查6个数据行来处理该查询。这比前面两个执行计划的102小很多。

　　EXPLAIN的第二种用途是查看添加索引是否能帮助优化器更高效率地执行语句。我将使用两个未被索引的数据表。它足够显示建立索引的效率。相同的规则可以应用于涉及多表的更加复杂的联结操作。

　　假设我们有两个数据表t1和t2，每个有1000行，包含的值从1到1000。下面的查询查找出两个表中值相同的数据行：

　　　　mysql> SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2;
　　　　+------+------+
　　　　| i1 | i2 |
　　　　+------+------+
　　　　| 1 | 1 |
　　　　| 2 | 2 |
　　　　| 3 | 3 |
　　　　| 4 | 4 |
　　　　| 5 | 5 |
　　　　...

　　两个表都没有索引的时候，EXPLAIN产生下面的结果：

　　　　mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t1
　　　　type: ALL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: 1000
　　　　Extra:
　　　　*************************** 2. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t2
　　　　type: ALL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: 1000
　　　　Extra: Using where

　　类型列中的ALL表明要进行检查所有数据行的全表扫描。可能键列中的NULL表明没有找到用于提高查询速度的备选索引（键、键长度和参考列都是NULL也是因为缺少合适的索引）。Using where表明使用WHERE子句中的信息来识别合格的数据行。

　　这段信息告诉我们，优化器没有为提高执行查询的效率找到任何有用的信息：

　　· 它将对t1表进行全表扫描。

　　· 对于t1中的每一行，它将执行t2的全表扫描，使用WHERE子句中的信息识别出合格的行。

　　行数值显示了优化器估计的每个阶段查询需要检查的行数。T1的估计值是1000，因为1000可以完成全表扫描。相似地，t2的估计值也是1000，但是这个值是对于t1的每一行的。换句话说，优化器所估计的处理该查询所需要检查的数据行组合的数量是1000×1000，也就是一百万。这会造成很大的浪费，因为实际上只有1000个组合符合WHERE子句的条件。

　　为了使这个查询的效率更高，给其中一个联结列添加索引并重新执行EXPLAIN语句：

　　　　mysql> ALTER TABLE t2 ADD INDEX (i2);
　　　　mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t1
　　　　type: ALL
　　　　possible_keys: NULL
　　　　key: NULL
　　　　key_len: NULL
　　　　ref: NULL
　　　　rows: 1000
　　　　Extra:
　　　　*************************** 2. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t2
　　　　type: ref
　　　　possible_keys: i2
　　　　key: i2
　　　　key_len: 5
　　　　ref: sampdb.t1.i1
　　　　rows: 10
　　　　Extra: Using where; Using index

　　我们可以看到性能提高了。T1的输出没有改变（表明还是需要进行全表扫描），但是优化器处理t2的方式就有所不同了：

　　· 类型从ALL改变为ref，意味着可以使用参考值（来自t1的值）来执行索引查找，定位t2中合格的数据行。

　　· 参考值在参考（ref）字段中给出了：sampdb.t1.i1。

　　· 行数值从1000降低到了10，显示出优化器相信对于t1中的每一行，它只需要检查t2中的10行（这是一个悲观的估计值。实际上，在t2中只有一行与t1中数据行匹配。我们在后面会看到如何帮助优化器改善这个估计值）。数据行组合的全部估计值使1000×10＝10000。它比前面的没有索引的时候估计出来的一百万好多了。

　　对t1进行索引有价值吗？实际上，对于这个特定的联结操作，扫描一张表是必要的，因此没有必要对t1建立索引。如果你想看到效果，可以索引t1.i1并再次运行EXPLAIN：

　　　　mysql> ALTER TABLE t1 ADD INDEX (i1);
　　　　mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t1
　　　　type: index
　　　　possible_keys: i1
　　　　key: i1
　　　　key_len: 5
　　　　ref: NULL
　　　　rows: 1000
　　　　Extra: Using index
　　　　*************************** 2. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t2
　　　　type: ref
　　　　possible_keys: i2
　　　　key: i2
　　　　key_len: 5
　　　　ref: sampdb.t1.i1
　　　　rows: 10
　　　　Extra: Using where; Using index

　　上面的输出与前面的EXPLAIN的输出相似，但是添加索引对t1的输出有一些改变。类型从NULL改成了index，附加（Extra）从空的改成了Using index。这些改变表明，尽管对索引的值仍然需要执行全表扫描，但是优化器还是可以直接从索引文件中读取值，根据不需要使用数据文件。你可以从MyISAM表中看到这类结果，在这种情况下，优化器知道自己只询问索引文件就能够得到所有需要的信息。对于InnoDB 和BDB表也有这样的结果，在这种情况下优化器可以单独使用索引中的信息而不用搜索数据行。

　　我们可以运行ANALYZE TABLE使优化器进一步优化估计值。这会引起服务器生成键值的静态分布。分析上面的表并再次运行EXPLAIN得到了更好的估计值：

　　　　mysql> ANALYZE TABLE t1, t2;
　　　　mysql> EXPLAIN SELECT t1.i1, t2.i2 FROM t1, t2 WHERE t1.i1 = t2.i2G
　　　　*************************** 1. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t1
　　　　type: index
　　　　possible_keys: i1
　　　　key: i1
　　　　key_len: 5
　　　　ref: NULL
　　　　rows: 1000
　　　　Extra: Using index
　　　　*************************** 2. row ***************************
　　　　id: 1
　　　　select_type: SIMPLE
　　　　table: t2
　　　　type: ref
　　　　possible_keys: i2
　　　　key: i2
　　　　key_len: 5
　　　　ref: sampdb.t1.i1
　　　　rows: 1
　　　　Extra: Using where; Using index

　　在这种情况下，优化器估计在t2中与t1的每个值匹配的数据行只有一个。

　　重载优化过程

　　这个过程听起来多余，但是有时候你还是希望去掉某些MySQL优化行为的：

　　重载优化器的表联结次序。使用STRAIGHT_JOIN强迫优化器按照特定的次序使用数据表。在这样操作的时候，你必须对数据表进行排序，这样才能保证第一张表是被选择的行数最少的表。如果你不能确定被选择行数最少的是哪一张表，那么就把行数最多的放到第一的位置。换句话说，试着对表进行排序，使最有约束力的选择出现在最前面。你对可能的备选数据行缩小地越早，执行查询的性能就越好。请确保在带有STRAIGHT_JOIN和不带STRAIGHT_JOIN的时候分别执行该查询。有时候由于某些原因的存在，优化器没有按照你认定的方式联结数据表，STRAIGHT_JOIN也可能没有实际的帮助作用。

　　另一个可能性是在联结的数据表列表中的某个表的后面使用FORCE INDEX、USE INDEX和IGNORE INDEX调节符来告诉MySQL如何使用索引。这在优化器没有做出正确选择的时候是有用处的。

　　以最小的代价清空一张表。当需要完全地清空一张MyISAM数据表的时候，最快的方法是删除它并利用它的.frm文件中存储的脚本来重新建立它。使用TRUNCATE TABLE语句实现：

　　　　TRUNCATE TABLE tbl_name;

　　通过重新建立MyISAM数据表来清空它的这种服务器优化措施使该操作非常快，因为不需要单独地逐行删除。

　　但是TRUNCATE TABLE也带来了一些副作用，在某些环境中是不符合要求的：

　　· TRUNCATE TABLE不一定能够计算出被删除的数据列的精确数量。如果你需要这个数值，请使用不带WHERE子句的DELETE语句：

　　　　DELETE FROM tbl_name;

　　· 但是，通过重新建立来清空数据表，它可能会把序号的起始值设置为1。为了避免这种情况，请使用"不优化的"全表DELETE语句，它带有一个恒为真的WHERE子句：

　　　　DELETE FROM tbl_name WHERE 1;

　　添加WHERE子句会强迫MySQL进行逐行删除，因为它必须计算出每一行的值来判断是否能够删除它。这个语句执行的速度很慢，但是它却保留了当前的AUTO_INCREMENT序号。

MySQL查询优化系列讲座之数据类型与效率

2005-09-14 09:20 作者： yoyo编译出处：天极网

　　这一部分提供了如何选择数据类型来帮助提高查询运行速度的一些指导：

　　在可以使用短数据列的时候就不要用长的。如果你有一个固定长度的CHAR数据列，那么就不要让它的长度超出实际需要。如果你在数据列中存储的最长的值有40个字符，就不要定义成CHAR(255)，而应该定义成CHAR(40)。如果你能够用MEDIUMINT代替BIGINT，那么你的数据表就小一些（磁盘I/O少一些），在计算过程中，值的处理速度也快一些。如果数据列被索引了，那么使用较短的值带来的性能提高更加显著。不仅索引可以提高查询速度，而且短的索引值也比长的索引值处理起来要快一些。

　　如果你可以选择数据行的存储格式，那么应该使用最适合存储引擎的那种。对于MyISAM数据表，最好使用固定长度的数据列代替可变长度的数据列。例如，让所有的字符列用CHAR类型代替VARCHAR类型。权衡得失，我们会发现数据表使用了更多的磁盘空间，但是如果你能够提供额外的空间，那么固定长度的数据行被处理的速度比可变长度的数据行要快一些。对于那些被频繁修改的表来说，这一点尤其突出，因为在那些情况下，性能更容易受到磁盘碎片的影响。

　　· 在使用可变长度的数据行的时候，由于记录长度不同，在多次执行删除和更新操作之后，数据表的碎片要多一些。你必须使用OPTIMIZE TABLE来定期维护其性能。固定长度的数据行没有这个问题。

　　· 如果出现数据表崩溃的情况，那么数据行长度固定的表更容易重新构造。使用固定长度数据行的时候，每个记录的开始位置都可以被检测到，因为这些位置都是固定记录长度的倍数，但是使用可变长度数据行的时候就不一定了。这不是与查询处理的性能相关的问题，但是它一定能够加快数据表的修复速度。

　　尽管把MyISAM数据表转换成使用固定长度的数据列可以提高性能，但是你首先需要考虑下面一些问题：

　　· 固定长度的数据列速度较快，但是占用的空间也较大。CHAR(n)列的每个值（即使是空值）通常占n个字符，这是因为把它存储到数据表中的时候，会在值的后面添加空格。VARCHAR(n)列占有的空间较小，因为只需要分配必要的字符个数用于存储值，加上一两个字节来存储值的长度。因此，在CHAR和VARCHAR列之间进行选择的时候，实际上是时间与空间的对比。如果速度是主要的考虑因素，那么就使用CHAR数据列获取固定长度列的性能优势。如果空间很重要，那么就使用VARCHAR数据列。总而言之，你可以认为固定长度的数据行可以提高性能，虽然它占用了更大的空间。但是对于某些特殊的应用程序，你可能希望使用两种方式来实现某个数据表，然后运行测试来决定哪种情况符合应用程序的需求。

　　· 即使愿意使用固定长度类型，有时候你也没有办法使用。例如，长于255个字符的字符串就无法使用固定长度类型。

　　MEMORY数据表目前都使用固定长度的数据行存储，因此无论使用CHAR或VARCHAR列都没有关系。两者都是作为CHAR类型处理的。

　　对于InnoDB数据表，内部的行存储格式没有区分固定长度和可变长度列（所有数据行都使用指向数据列值的头指针），因此在本质上，使用固定长度的CHAR列不一定比使用可变长度VARCHAR列简单。因而，主要的性能因素是数据行使用的存储总量。由于CHAR平均占用的空间多于VARCHAR，因此使用VARCHAR来最小化需要处理的数据行的存储总量和磁盘I/O是比较好的。

　　对于BDB数据表，无论使用固定长度或可变长度的数据列，差别都不大。两种方法你都可用试一下，运行一些实验测试来检测是否存在明显的差别。

　　把数据列定义成不能为空（NOT NULL）。这会使处理速度更快，需要的存储更少。它有时候还简化了查询，因为在某些情况下你不需要检查值的NULL属性。

　　考虑使用ENUM数据列。如果你拥有的某个数据列的基数很低（包含的不同的值数量有限），那么可以考虑把它转换为ENUM列。ENUM值可以被更快地处理，因为它们在内部表现为数值。

　　使用PROCEDURE ANALYSE()。运行PROCEDURE ANALYSE()可以看到数据表中列的情况：

　　　　SELECT * FROM tbl_name PROCEDURE ANALYSE();
　　　　SELECT * FROM tbl_name PROCEDURE ANALYSE(16,256);

　　输出的每一列信息都会对数据表中的列的数据类型提出优化建议。第二个例子告诉PROCEDURE ANALYSE()不要为那些包含的值多于16个或者256字节的ENUM类型提出建议。如果没有这样的限制，输出信息可能很长；ENUM定义通常很难阅读。
　　根据的PROCEDURE ANALYSE()输出信息，你可能发现，可以修改自己的数据表来利用那些效率更高的数据类型。如果你决定改变某个数据列的类型，需要使用ALTER TABLE语句。

　　使用OPTIMIZE TABLE来优化那些受到碎片影响的数据表。被大量修改的数据表，特别是那些包含可变长度数据列的表，容易遭受碎片的影响。碎片很糟糕，因为它会导致用于存储数据表的磁盘块形成无用空间（空洞）。随着时间的推移，为了得到有效的数据行，你必须读取更多的块，性能就会降低。这会出现在任何可变长度的数据行上，但是对于BLOB或TEXT数据列尤其突出，因为它们的长度差异太大了。在正常情况下使用OPTIMIZE TABLE会防止数据表的性能降低。OPTIMIZE TABLE可以用于MyISAM和BDB数据表，但是defragments只能用于MyISAM数据表。任何存储引擎中的碎片整理方法都是用mysqldump来转储（dump）数据表，接着使用转储的文件删除并重新建立那些数据表：

　　　　% mysqldump --opt db_name tbl_name > dump.sql
　　　　% mysql db_name < dump.sql

　　把数据打包放入BLOB或TEXT数据列。使用BLOB或TEXT数据列存储打包（pack）的数据，并在应用程序中进行解包（unpack），使你能够在一次检索操作中得到需要的任何信息，而不需要进行多次检索。它对那些很难用标准的数据表结构表现的数据值和频繁变化的数据值也是有帮助的。

　　解决这个问题的另一种方法是让那些处理Web窗体的应用程序把数据打包成某种数据结构，然后把它插入到单个BLOB或TEXT数据列中。例如，你可以使用XML表示调查表回复，把那些XML字符串存储在TEXT数据列中。由于要对数据进行编码（从数据表中检索数据的时候还需要解码），它会增加客户端的开销，但是可以简化数据结构，而且它还消除了那些因为改变了调查表的内容而必须改变数据表结构的需求。

　　另一方面，BLOB和TEXT值也会引起自己的一些问题，特别是执行了大量的删除或更新操作的时候。删除这种值会在数据表中留下很大的"空洞"，以后填入这些"空洞"的记录可能长度不同（前面讨论的OPTIMIZE TABLE提出解决这个问题的一些建议）。

　　使用合成的（synthetic）索引。合成的索引列在某些时候是有用的。一种办法是根据其它的列的内容建立一个散列值，并把这个值存储在单独的数据列中。接下来你就可以通过检索散列值找到数据行了。但是，我们要注意这种技术只能用于精确匹配的查询（散列值对于类似<或>=等范围搜索操作符是没有用处的）。我们可以使用MD5()函数生成散列值，也可以使用SHA1()或CRC32()，或者使用自己的应用程序逻辑来计算散列值。请记住数值型散列值可以很高效率地存储。同样，如果散列算法生成的字符串带有尾部空格，就不要把它们存储在CHAR或VARCHAR列中，它们会受到尾部空格去除的影响。

　　合成的散列索引对于那些BLOB或TEXT数据列特别有用。用散列标识符值查找的速度比搜索BLOB列本身的速度快很多。

　　在不必要的时候避免检索大型的BLOB或TEXT值。例如，SELECT *查询就不是很好的想法，除非你能够确定作为约束条件的WHERE子句只会找到所需要的数据行。否则，你可能毫无目的地在网络上传输大量的值。这也是BLOB或TEXT标识符信息存储在合成的索引列中对我们有所帮助的例子。你可以搜索索引列，决定那些需要的数据行，然后从合格的数据行中检索BLOB或TEXT值。

　　把BLOB或TEXT列分离到单独的表中。在某些环境中，如果把这些数据列移动到第二张数据表中，可以让你把原数据表中的数据列转换为固定长度的数据行格式，那么它就是有意义的。这会减少主表中的碎片，使你得到固定长度数据行的性能优势。它还使你在主数据表上运行SELECT *查询的时候不会通过网络传输大量的BLOB或TEXT值。

　　高效率地载入数据

　　在大多数情况下，你所关注的是SELECT查询的优化，因为SELECT查询是最常见的查询类型，而且如何优化它们又不是太简单。与此形成对比，把数据载入数据库的操作就相对直接了。然而，你仍然可以利用某些策略来改善数据载入操作的效率。基本的原理如下所示：

　　· 批量载入比单行载入的效率高，因为在每条记录被载入后，键缓存（key cache）不用刷新（flush）；可以在这批记录的末尾刷新键缓存。键缓存刷新的频率减少得越多，数据载入的速度就越快。

　　· 没有索引的数据表的载入速度比有索引的要快一些。如果存在索引，不但要把记录添加到数据文件中，还必须修改索引来反映新增的记录。

　　· 较短的SQL语句比较长的SQL语句快，因为它们所涉及到服务器端分析过程较少，同时通过网络把它们从客户端发送到服务器上的速度也更快。

　　其中有些因素看起来是次要的（尤其是最后一个），但是如果你载入的数据很多，那么即使很小的效率差异也会导致一定的性能差别。我们可以从前面的一般原理得出几条如何快速载入数据的实践结论：

　　· LOAD DATA（所有形式的）比INSERT效率高，因为它是批量载入数据行的。服务器只需要分析和解释一条语句，而不是多条语句。同样，索引只需要在所有的数据行被处理过之后才刷新，而不是每行刷新一次。

　　· 不带LOCAL的LOAD DATA比带有LOCAL的LOAD DATA的速度要快。不带LOCAL的时候，文件必须位于服务器上，而且你必须拥有FILE权限，但是服务器却可以直接从磁盘上读取文件。使用LOAD DATA LOCAL的时候，客户端读取文件并通过网络把它发送给服务器，速度慢一些。

　　· 如果你必须使用INSERT，那么试着使用在一个语句中指定多个数据行的形式：

　　　　INSERT INTO tbl_name VALUES(...),(...),... ;

　　在这个语句中指定的数据行越多，效果就越好。这会减少必要的语句数量，并最小化索引刷新的次数。这一条结论看起来与前面所讨论的"语句越短，执行速度越快"相矛盾，但是实际上并不矛盾。这儿所讨论的是同时插入多个数据行的一个INSERT语句所花费的开销比功能相同的多个单行INSERT语句的花费的开销要小一些，并且多行语句消耗的索引刷新开销也少一些。

　　如果你使用mysqldump生成数据库备份文件，那么MySQL 4.1会默认地生成多行INSERT语句：它会激活--opt (优化)选项，而这个选项会激活--extended-insert选项，该选项生成多行INSERT语句，还存在其它一些选项也可以使数据被载入的时候，转储文件被处理的效率更高。对于MySQL 4.1以前的版本，你可以明确地指定--opt或--extended-insert选项。

　　使用mysqldump的时候要避免使用--complete-insert选项；它生成的INSERT语句是每个数据行一条语句的，语句总共会很长，比多行语句需要的分析操作更多。

　　· 如果你必须使用INSERT语句，那么在可能的情况下，对它们进行分组以减少索引的刷新。对于事务性的存储引擎，在单个事务中提交，而不是在自动提交（autocommit）模式下提交INSERT语句可以实现这样的功能：

　　　　START TRANSACTION;
　　　　INSERT INTO tbl_name ... ;
　　　　INSERT INTO tbl_name ... ;
　　　　INSERT INTO tbl_name ... ;
　　　　COMMIT;

　　对于非事务性的存储引擎，获取数据表上的写入锁，它被锁定的时候提交INSERT语句：

　　　　LOCK TABLES tbl_name WRITE;
　　　　INSERT INTO tbl_name ... ;
　　　　INSERT INTO tbl_name ... ;
　　　　INSERT INTO tbl_name ... ;
　　　　UNLOCK TABLES;
　　无论采用哪种方法，你得到的好处都是相同的：索引在所有的语句都被执行之后才刷新一次，而不是每个INSERT语句刷新一次索引。后面介绍了在自动提交模式下或数据表没有被锁定的时候发生的情况。

　　· 对于MyISAM数据表，减少索引刷新的另外一个策略是使用DELAYED_KEY_WRITE表选项。使用这个选项的时候，数据行会像平常一样立即写入数据文件中，但是键缓存只是偶尔刷新一次，而不是在每次插入操作之后都需要刷新。如果要在服务器上全面地使用延迟索引刷新，那么就需要使用--delay-key-write选项来启动mysqld。在这种情况下，每个数据表的索引块写入操作都会被延迟，直到这些数据块必须为其它的索引值提供空间、或者执行了FLUSH TABLES命令、或者数据表被关闭的时候才执行操作。

　　如果你选择了对MyISAM数据表使用延迟键写入，那么不正常的服务器关闭可能会引起索引值的丢失。这不是致命的问题，因为MyISAM索引可以依据数据行来进行修复，但是如果想让修复过程出现，你就必须使用--myisam-recover=FORCE选项来启动服务器。这个选项会使服务器在打开MyISAM数据表的时候检查它们，如果有必要就自动地修复它们。

　　对于复制（replication）从属服务器，你可能希望使用--delay-key-write=ALL来延迟所有的MyISAM数据表索引的刷新，不管在主服务器上最初是如何建立它们的。

　　· 使用压缩的客户端/服务器协议来减少网络上数据传输的数量。对于大多数MySQL客户端来说，我们都可以使用--compress命令行选项来指定它。通常，这个选项只是在较慢的网络上使用，这是因为压缩操作会花费大量的处理器时间。

　　· 让MySQL替你插入默认值。也就是说，无论如何都不要给INSERT语句中那些可以赋予默认值的列指定值。平均起来，你的语句更短，减少了通过网络发送到服务器的字符数量。此外，由于语句包含的值较少，服务器执行的分析和值转换操作也较少。

　　· 对于MyISAM数据表，如果你必须把大量的数据载入一个新表，最好建立不带索引的表，载入数据，然后建立索引，这样的工作次序的速度要快一些。一次性地建立索引比每行都更新索引的速度要快一些。对于已经带有索引的表，如果预先删除或禁止索引，后来再重新建立或者激活索引，那么数据载入的速度也要快一些。这些策略不能应用于InnoDB或BDB表，它们没有对分离的索引建立过程进行优化。

　　如果你考虑使用删除或禁止索引的策略，把数据载入MyISAM数据表，那么在评估获得的优势的时候，就需要考虑整个环境。如果你把少量的数据载入大型的数据表中，那么在没有任何特殊准备工作的情况下，重新建立索引花费的时间可能比载入数据的时间还要长。

　　要删除并且重新建立索引，需要使用DROP INDEX和CREATE INDEX，或者使用与索引相关的ALTER TABLE。禁止和激活索引有两种办法：

　　· 你可用使用ALTER TABLE的DISABLE KEYS和ENABLE KEYS形式：

　　　　ALTER TABLE tbl_name DISABLE KEYS;
　　　　ALTER TABLE tbl_name ENABLE KEYS;

　　这些语句关闭或打开表中非唯一（non-unique）索引的更新过程。

　　ALTER TABLE的DISABLE KEYS和ENABLE KEYS子句是索引禁止和激活操作的推荐方法，因为服务器也是这样操作的（如果你使用LOAD DATA语句把数据载入空的MyISAM表中，服务器会自动地执行这样的优化操作）。

　　· Myisamchk工具可以执行索引维护。它直接在数据表文件上进行操作，因此使用它的时候，你必须拥有数据表文件的写入权限。
使用myisamchk禁止MyISAM表的索引的方法是，首先你要确保已经告诉了服务器让该数据表独立出来，接着把它移动到适当的数据库目录中，并运行下面的命令：

　　　　% myisamchk --keys-used=0 tbl_name

　　载入数据之后，重新激活索引：

　　　　% myisamchk --recover --quick --keys-used=n tbl_name

　　其中的n是位掩码（bitmask），它指明了要激活的索引。Bit 0（第一个位）与索引1对应。例如，如果某张表拥有三个索引，那么n的值应该是7（二进制的111）。你也可以使用--description选项来检测索引的数量：

　　　　% myisamchk --description tbl_name

　　前面的数据载入原则也可以应用于混合查询环境（客户端执行多种不同的操作）。例如，你应该避免在那些频繁被修改（写入）的数据表上运行长时间的SELECT查询。这会引发大量的争用（contention），导致写入操作的性能较差。一个可能的解决办法是，如果你的写入操作主要是INSERT操作，那么把新记录添加到辅助表中，接着周期性地把这些记录添加到主表中。如果你必须立即访问这些新记录，那么这个策略是不行的，但是如果你能够承担得起短期内不访问这些数据的代价，那么使用辅助表可以在两个方面带来好处。首先，它减少了主表上的SELECT查询争用的问题，因此它们执行得更快。其次，把辅助表中的批量数据载入主表中所花费的时间总和也比单独载入记录花费的时间总和要小一些；键缓存只需要在每次批量载入结束后刷新一次，而不用每个数据行载入后都刷新一次。

　　使用这种策略的一个应用是把Web服务器的Web页面访问日志载入MySQL数据库的时候。在这种情况下，保证实体立即进入主表的优先级并不高（没有这个必要性）。

　　如果你在MyISAM表上使用了混合的INSERT和SELECT语句，你就可以利用并发性插入操作的优点了。这个特性允许插入和检索操作同时进行，而不需要使用辅助表。你可以查看"使用并发性插入操作"部分。

MySQL查询优化系列讲座之调度和锁定

2005-09-19 16:49 作者：陶刚编译出处：天极网

　　前面的部分主要是聚焦于如何让单独的查询执行的速度更快。MySQL还允许你改变语句调度的优先级，它可以使来自多个客户端的查询更好地协作，这样单个客户端就不会由于锁定而等待很长时间。改变优先级还可以确保特定类型的查询被处理得更快。这一部分讲解MySQL的默认的调度策略和可以用来影响这些策略的选项。它还谈到了并发性插入操作的使用和存储引擎锁定层次对客户端的并发性的影响。为了讨论的方便，我们把执行检索（SELECT）的客户端称为"读取者"，把执行修改操作（DELETE、INSERT、REPLACE或UPDATE）的客户端称为"写入者"。

　　MySQL的默认的调度策略可用总结如下：

　　· 写入操作优先于读取操作。

　　· 对某张数据表的写入操作某一时刻只能发生一次，写入请求按照它们到达的次序来处理。

　　· 对某张数据表的多个读取操作可以同时地进行。

　　MyISAM和MEMORY存储引擎借助于数据表锁来实现这样的调度策略。当客户端访问某张表的时候，首先必须获取它的锁。当客户端完成对表的操作的时候，锁就会被解除。通过LOCK TABLES和UNLOCK TABLES语句来显式地获取或释放锁是可行的，但是在通常情况下，服务器的锁管理器会自动地在需要的时候获取锁，在不再需要的时候释放锁。获取的锁的类型依赖于客户端是写入还是读取操作。

　　对某张表进行写入操作的客户端必须拥有独占的（排他的）访问权的锁。操作在进行的过程中，该数据表处于不一致的（inconsistent）状态，因为数据记录在删除、添加或修改的时候，数据表上的索引也可能需要更新以相互匹配。这个数据表在变化的过程中，如果允许其它的客户端访问，会出现问题。非常明显，允许两个客户端同时写入一张数据表是不利的，因为这样的操作会很快使数据表中的信息成为一堆无用的垃圾。但是允许客户端读取变化之中的数据表也不好，因为正在读取的位置中的数据可能正在变化（修改），读取的结果可能不是真实的。

　　对某张表执行读取操作的客户端必须获取一个锁，防止在读取的过程中，其它的客户端写入或改变表。但是这个锁不需要独占的访问权。读取操作不会改变数据，因此没有理由让某个读取者阻止其它的读取者访问这张表。因此读取锁允许其它的客户端在同一时刻读取这张表。

　　MySQL提供了几个语句调节符，允许你修改它的调度策略：

　　· LOW_PRIORITY关键字应用于DELETE、INSERT、LOAD DATA、REPLACE和UPDATE。

　　· HIGH_PRIORITY关键字应用于SELECT和INSERT语句。

　　· DELAYED关键字应用于INSERT和REPLACE语句。

　　LOW_PRIORITY和HIGH_PRIORITY调节符影响那些使用数据表锁的存储引擎（例如MyISAM和MEMORY）。DELAYED调节符作用于MyISAM和MEMORY数据表。

　　改变语句调度的优先级

　　LOW_PRIORITY关键字影响DELETE、INSERT、LOAD DATA、REPLACE和UPDATE语句的执行调度。通常情况下，某张数据表正在被读取的时候，如果有写入操作到达，那么写入者一直等待读取者完成操作（查询开始之后就不能中断，因此允许读取者完成操作）。如果写入者正在等待的时候，另一个读取操作到达了，该读取操作也会被阻塞（block），因为默认的调度策略是写入者优先于读取者。当第一个读取者完成操作的时候，写入者开始操作，并且直到该写入者完成操作，第二个读取者才开始操作。

　　如果写入操作是一个LOW_PRIORITY（低优先级）请求，那么系统就不会认为它的优先级高于读取操作。在这种情况下，如果写入者在等待的时候，第二个读取者到达了，那么就允许第二个读取者插到写入者之前。只有在没有其它的读取者的时候，才允许写入者开始操作。理论上，这种调度修改暗示着，可能存在LOW_PRIORITY写入操作永远被阻塞的情况。如果前面的读取操作在进行的过程中一直有其它的读取操作到达，那么新的请求都会插入到LOW_PRIORITY写入操作之前。

　　SELECT查询的HIGH_PRIORITY（高优先级）关键字也类似。它允许SELECT插入正在等待的写入操作之前，即使在正常情况下写入操作的优先级更高。另外一种影响是，高优先级的SELECT在正常的SELECT语句之前执行，因为这些语句会被写入操作阻塞。

　　如果你希望所有支持LOW_PRIORITY选项的语句都默认地按照低优先级来处理，那么请使用--low-priority-updates选项来启动服务器。通过使用INSERT HIGH_PRIORITY来把INSERT语句提高到正常的写入优先级，可以消除该选项对单个INSERT语句的影响。

　　使用延迟插入操作

　　DELAYED调节符应用于INSERT和REPLACE语句。当DELAYED插入操作到达的时候，服务器把数据行放入一个队列中，并立即给客户端返回一个状态信息，这样客户端就可以在数据表被真正地插入记录之前继续进行操作了。如果读取者从该数据表中读取数据，队列中的数据就会被保持着，直到没有读取者为止。接着服务器开始插入延迟数据行（delayed-row）队列中的数据行。在插入操作的同时，服务器还要检查是否有新的读取请求到达和等待。如果校映偈菪卸恿芯捅还移穑市矶寥≌呒绦僮鳌５泵挥卸寥≌叩氖焙颍衿髟俅慰疾迦胙映俚氖菪小Ｕ飧龉桃恢苯校钡蕉恿锌樟宋埂?BR>
　　感觉上LOW_PRIORITY和DELAYED是相似的，两者都允许数据行插入操作被延迟，但是它们对客户端操作的影响却有很大的差异。LOW_ PRIORITY强迫客户端等待，直到那些数据行可以被插入数据表。DELAYED允许客户端继续操作，服务器在内存中缓冲那些数据行，直到自己有时间处理它们。

　　如果其它的客户端可能运行很长的SELECT语句并且你不希望阻塞，等待插入操作完成的时候，INSERT DELAYED就非常有用处了。客户端提交INSERT DELAYED的时候可能处理得很快，因为服务器只是简单地把要插入的数据行排队。

　　但是，你也必须知道正常的INSERT与INSERT DELAYED行为之间的一些其它的差异。如果INSERT DELAYED语句包含语法错误，客户端会得到一个错误，但是却无法得到其它一些在正常情况下可以使用的信息。例如，当语句返回的时候，你无法依赖（得到）AUTO_INCREMENT（自动增长）值。同样，你无法得到唯一索引的副本数量。发生这种情况的原因在于插入操作在真正地被执行之前已经返回了状态信息。另一种可能出现的情况是，由于INSERT DELAYED语句的数据行都在内存中排队，当服务器崩溃或者使用kill -9退出的时候，数据行可能丢失（正常情况下，kill -TERM终止命令不会导致这种情况，因为服务器在退出之前会把数据行插入表中）。

　　使用并发的插入操作

　　MyISAM存储引擎有一条例外的规则，它允许读取者阻塞写入者。这种现象发生在MyISAM数据表中间没有"空洞"（可能是删除或更新数据行的结果）的情况下。当数据表没有"空洞"的时候，任何INSERT语句必然在末尾而不是中部添加数据行。在这种情况下，MySQL允许其它客户端在读取数据的同时向数据表添加数据行。这就是"并发性插入操作"，因为它们同时发生，检索并没有被阻塞。

　　如果你希望使用并发性插入操作，请注意下面一些事项：

　　· 在INSERT语句中不要使用LOW_PRIORITY调节符。它会引起INSERT经常被读取者阻塞，因此阻碍了并发性插入操作的执行。

　　· 如果读取者需要显式地锁定数据表以执行并发性插入操作，就应该使用LOCK TABLES ... READ LOCAL，而不是LOCK TABLES ... READ。LOCAL关键字会获取一个锁，允许并发性操作继续进行，因为它只能应用于数据表中已有的数据行，不会阻塞那些添加到末尾的新数据行。

　　· LOAD DATA操作应该使用CONCURRENT调节符，允许该数据表上的SELECT语句同时执行。

　　· 中间包含了"空洞"的MyISAM数据表不能使用并发性插入操作。但是，你可以使用OPTIMIZE TABLE语句来整理该数据表的碎片。

　　锁的层次和并发性

　　前面讨论的调度调节符允许你改变默认的调度策略。其中的大部分内容都是介绍使用这些调节符来解决数据表层次（table-level）的锁引起的问题，这都是MyISAM和MEMORY存储引擎用来管理数据表争用的问题的。

　　BDB和InnoDB存储引擎实现了不同层次的锁，所以其性能特征和对争用的管理是不同的。BDB引擎使用页面层次（page-level）的锁。InnoDB引擎使用数据行层次（row-level）的锁，但是只在必要的时候使用（在很多情况下，例如当读取操作都完成的时候，InnoDB可能根本就不使用锁）。

　　存储引擎使用的锁的层次对客户端的并发操作有很大的影响。假设两个客户端都希望更新某个数据表中的一行。由于要执行更新，每个客户端都需要一个写入锁。对于MyISAM数据表，引擎会为第一个客户端分配一个锁，这会引起第二个客户端阻塞，直到第一个客户端完成操作。对于BDB数据表，它可以实现更大的并发性：两个更新操作会同步进行，除非两个数据行都位于同一个页面中。在InnoDB数据表中，并发性更高；只要两个客户端没有更新同一行，两个更新操作就能同时发生。

　　一般的规则是，锁的层次越细微，并发性越好，因为只要客户端使用数据表的部分不同，那么使用表的客户端就可以更多。它实际暗示着不同的存储引擎适合于不同的语句混合（mixes）：

　　· MyISAM检索的速度非常快。但是使用表层次的锁可能成为混合的检索和更新环境中的问题，特别是检索倾向于长时间运行的时候。在这些条件下，更新可能需要等待很久才能进行。

　　· 当更新操作很多的时候，BDB和InnoDB数据表可以提供更好的性能。由于锁在页面或数据行层次进行，表被锁定的范围较小。这会减少锁的争用，提高并发性。

　　在防止死锁（deadlock）方面，表层次的锁比细微层次的锁更有优势。使用表层次的锁的时候，死锁不会发生。服务器可以通过查看语句来检测需要的数据表，并提前锁定它们。而InnoDB和BDB数据表会发生死锁，因为这些存储引擎没有在事务开始的时候分配所有必要的锁。作为代替，在事务处理的过程中，当检测到需要锁的时候才分配。这就可能出现两个语句获取了锁，接着试图进一步获取锁（需要多个锁），但是这些锁却被对方保持着，等待对方释放。其结果是每个客户端都拥有一个锁，同时还需要利用其它的客户端拥有的锁才能继续执行。这会导致死锁，服务器必须终止其中一个事务。

MySQL查询优化讲座之管理员的优化措施

2005-09-20 15:26 作者：陶刚编译出处：天极网

　　前面的部分中讲解的优化措施都是没有特权的MySQL用户能够执行的。可以控制MySQL服务器或计算机的系统管理员能够执行额外的优化措施。例如，有些服务器参数附属于查询处理过程，并且是可以调整的，而且某些硬件配置因素对查询处理速度有直接的影响。在很多情况下，这些优化措施提高了整个服务器的性能，因此可以让所有的MySQL用户都受益。

　　一般来说，当你执行管理员优化的时候，应该紧记以下规则：

　　· 访问内存中的数据快于访问磁盘上的数据。

　　· 尽量把数据保存在内存中可以减少磁盘操作。

　　· 保留索引中的信息比保留数据记录的内容更重要。

　　我们在后面将讨论如何应用这些规则。

　　增加服务器缓存的大小。服务器拥有很多参数（系统变量），你可以改变这些参数来影响服务器的操作。其中的几个参数直接地影响查询处理的速度。你可以改变的最重要的参数是数据表缓存的大小和存储引擎用于缓冲索引操作信息的缓存大小。如果你拥有可用的内存，就把它分配给服务器的缓存，以允许信息存储在内存中并减少磁盘操作。这会有很好的效果，因为访问内存中的信息比从磁盘读取信息的速度快得多。

　　· 当服务器打开表文件的时候，它试图保持这些文件的打开状态，以减少打开文件操作的数量。为了实现这样的功能，它在表缓存中维护打开文件的信息。table_cache系统变量控制着这个缓存的大小。如果服务器访问了大量的表，表缓存就会被填满，并且服务器会关闭那些有一段时间没有使用的表，为打开新表留出空间。你可以通过检查Opened_tables状态指示器来访问表缓存的效果：

　　　　SHOW STATUS LIKE ’Opened_tables’;

　　Opened_tables显示了某个数据表必须打开的次数（因为它还没有打开）。这个值也显示为mysqladmin状态命令的输出信息中的Opens值。如果这个数字是稳定的或缓慢增长，那么它的设置可能是正确的。如果这个数字增长得很快，就意味着这个缓存太小了，必须经常关闭数据表来为打开其它的数据表留出空间。如果你拥有文件描述信息，增加表缓存大小将减少数据表打开操作的数量。

　　· MyISAM存储引擎使用键缓冲来保持与索引相关的操作的索引信息块。它的大小是由key_buffer_size系统变量控制的。这个值越大，MySQL就一次性在内存中保持更多的索引信息块，可以增加在内存中（而不用从磁盘上读取新的信息块）找到键值的可能性。键缓存的默认大小是8MB。如果你拥有很多的内存，这是一个很保守的值，你可以直接增加它的大小，并且会看到基于索引的检索、索引的建立和修改操作的性能有很大改善。

　　在MySQL 4.1以上版本中，你可以为MyISAM数据表建立附加的键缓存，并指定某些表使用它们。这样可以帮助提高这些数据表上的查询处理速度。

　　· InnoDB和BDB引擎拥有自己的用于缓冲数据和索引值的缓存。它们的大小是由innodb_buffer_pool_size和bdb_cache_size变量控制的。InnoDB引擎还维护了一个日志缓冲。innodb_log_buffer_size变量可以控制它的大小。

　　· 另一个专用的缓存是查询缓存，我们在"使用查询缓存"部分中解释。

　　当你改变这些参数值的时候，应该遵循下面一些原则：

　　· 每次只改变一个参数。如果你一次改变多个相互独立的变量，那么就很难评估每种改变的效果了。

　　· 逐渐地增加系统变量值。根据理论，数量越多，性能越好，但是如果你使某个变量变得太大了，有可能造成系统资源匮乏，导致逆向效果，降低速度。

　　· 不要在运行业务MySQL数据库的服务器上做调整参数的实验，最好建立一个独立的测试服务器。

　　· 为了大致了解哪种参数变量可能适合自己的系统，你可以查看MySQL发布文档中包含的my-small.cnf、my-medium.cnf、my-large.cnf和my-huge.cnf选项文件（在Unix系统上，你可以在源发布文件的支持文件目录和二进制发布文件的共享目录总找到这些文件。在Windows上，它们位于基本的安装目录中，其扩展名可能是.ini）。这些文件可能让你知道最好改变服务器上的那些参数以适应不同的使用层次，并且为这些参数提供了一些典型值。

　　用于提高服务器的操作性能的其它一些策略还包括：

　　禁止不需要的存储引擎。服务器不会为禁止的引擎分配任何内存，因此我们可以利用这一点。如果从源文件建立MySQL，那么在配置的时候，大多数存储引擎就可以被排除在服务器之外。对于那些包含在服务器中的引擎来说，使用适当的启动选项可以在运行时禁止其中的大多数。
保持授权表许可的简单性。尽管服务器在内存中缓存了授权表内容，但是如果你在tables_priv或columns_priv表中有一些数据行的话，服务器就必须为每个查询语句检查表层次和列层次的权限。如果这些表是空的，那么服务器就能优化自己的权限检查过程，略过这些层次。

　　如果你从源文件建立MySQL，那么就把它配置为使用静态类库，而不要使用共享类库。使用共享类库的动态二进制文件节约磁盘空间，然而静态二进制文件速度更快。但是，如果你使用了用户自定义函数（UDF）机制，那么有些系统要求使用动态链接。在这类系统上，静态二进制文件不能工作。

　　使用MyISAM键缓存

　　当MySQL执行某个利用了MyISAM数据表索引的语句的时候，它会使用键缓存来保持索引值。这种缓存减少了磁盘I/O：如果在缓存中找到了某个数据表需要的键值，就不需要再次从磁盘中读取。不幸的是，这种键缓存是有限的，并且在默认情况下，它是所有的MyISAM数据表共享使用的。如果在键缓存中没有找到键值并且键缓存是满的，争用将会导致：必须丢弃缓存中的某些值，为新值留出空间。如果下次需要那些已经被丢弃的值，就必须再次从磁盘上读取。

　　如果你很倚重MyISAM数据表，那么把它的键保存在内存中效果会很好，但是缓存中的争用却会导致相反的效果。从同一张表或不同的表读取数据都可能引起争用。你可以通过把键缓存设置成足以保存某个特定数据表的全部索引，从而避免同一张数据表的争用，但是其它数据表的键仍然需要争用缓存空间。

　　MySQL 4.1以上版本为这个问题提供了一种解决方案：它支持我们建立多个键缓存，并允许我们把某张数据表的索引指定并且预先装入某个缓存。如果你的数据表使用得很频繁，并且你有足够的内存，能够把它的索引载入缓存中，那么这种操作就是有用的。这种能力允许你同时避免同一张表和不同的表的争用：建立一个足够大的缓存，让它保存数据表的全部索引，并且指定该缓存专门用于那张数据表。在键被载入缓存之后，不在需要磁盘I/O操作。同时，键值永远不会被丢弃，对数据表的键的查看操作可以在内存中完成。

　　下面的例子显示了如何为sampdb数据库的member数据表建立一个键缓存，该缓存的名称是member_cache，大小为1MB。执行这些指令的时候，你必须有超级（SUPER）权限。

　　1.建立一个足够容纳数据表索引的独立的缓存：

　　　　mysql> SET GLOBAL member_cache.key_buffer_size = 1024*1024;

　　2.给数据表指定键缓存：

　　3.把数据表索引预先读入它的键缓存中：

　　如果你希望把其它的数据表载入同一个缓存中，或者为其它的数据表建立键缓存，上面的操作就足够了。

　　使用查询缓存

　　MySQL服务器可以使用查询缓存来提高那些重复执行的SELECT语句的处理速度。它对性能的提高通常都是惊人的。查询缓存的工作方式如下所示：

　　· 第一次执行某条SELECT语句的时候，服务器记住该查询的文本内容和它返回的结果。

　　· 服务器下一次碰到这个语句的时候，它不会再次执行该语句。作为代替，它直接从查询缓存中的得到结果并把结果返回给客户端。

　　· 查询缓存是基于服务器所接收到的查询字符串的文本内容的。如果某些查询的文本完全相同，那些它就认为这些查询是相同的。如果某些查询的字符不同，或者来自那些使用了不同的字符集或通讯协议的客户端，那么它会认为这些查询是不同的。同样，如果某些查询采用其它的功能相当、但是实际上没有指向相同的数据表（例如引用了不同的数据库中的同名数据表），那么它们也是不同的。

　　· 当数据表被更新了之后，涉及到该数据表的任何缓存查询都变成无效的，并且会被丢弃。这可以防止服务器返回过期的结果。

　　在默认情况下，MySQL对查询缓存的支持是内建的。如果你不希望使用这种缓存，并且想避免它所导致的性能开销，可以使用--without-query-cache选项来运行配置脚本建立服务器。

　　如果需要检测某个服务器是否支持查询缓存，可以检查它的have_query_cache系统变量：

　　　　mysql> SHOW VARIABLES LIKE ’have_query_cache’;
　　　　+------------------+-------+
　　　　| Variable_name | Value |
　　　　+------------------+-------+
　　　　| have_query_cache | YES |
　　　　+------------------+-------+

　　对于那些支持查询缓存的服务器来说，缓存的操作是基于三个系统变量值的：

　　· query_cache_type决定查询缓存的操作模式。下表显示了可以使用的模式值：

　　　　模式含义
　　　　0 不要缓存查询结果或检索缓存的结果。
　　　　1 缓存查询，除非它们以SELECT SQL_NO_CACHE开头。
　　　　2 根据需要只缓存那些以SELECT SQL_CACHE开头的查询。

　　· query_cache_size决定分配给缓存的内存数量，单位是字节。

　　· query_cache_limit设置被缓存的最大结果集大小；比这个值大的查询结果不会被缓存。

　　例如，为了激活查询缓存并为它分配16MB内存，在配置文件中使用下面的设置：

　　　　[mysqld]
　　　　query_cache_type=1
　　　　query_cache_size=16M

　　即使query_cache_type的值设置为零，query_cache_size指定内存数量也会被分配。为了避免浪费内存，只有在希望激活缓存的时候才把大小设置成大于零。同时，即使query_cache_type不为零，查询缓存的大小设置为零也会禁用缓存。

　　使用了查询缓存的独立客户端会在服务器的默认缓存模式状态下操作。客户端可以使用下面的语句改变自己的查询的默认缓存模式：

　　　　SET query_cache_type = val;

　　其中的val可以是0、1或2，它的意义与设置服务器启动时的query_cache_type变量的意义是相同的。在SET语句中，OFF、ON和DEMAND这些符号值与0、1和2对应。

　　客户端还可以通过在SELECT关键字后天添加调节符来控制个别查询的缓存操作。如果缓存模式是ON或DEMAND，那么SELECT SQL_CACHE语句会让查询结果被缓存。SELECT SQL_NO_CACHE语句会使查询结果不被缓存。

　　如果某些查询从经常改变的数据表中检索信息，那么抑止对这些查询的缓存操作是有用的。在这种情况下，缓存未必有多大用处。假设你把Web服务器请求的日志存储在MySQL数据表中，同时周期性地运行该数据表上的一组统计查询。对于很繁忙的Web服务器来说，会频繁地出现新行插入该数据表的操作，因此该数据表的任何缓存了的查询结果很快就变成无效的了。其含义是，尽管你周期性地提交统计查询，但是查询缓存可能对这些查询没有什么价值。在这种情况下，最好使用SQL_NO_CACHE调节符告诉服务器不要缓存这些查询的结果。

　　硬件问题

　　本文前面的部分中讨论的帮助你提高服务器性能的技术是没有考虑硬件配置的。你当然可以通过使用更好的硬件来让服务器运行地更快。但是并非所有的与硬件相关的改变都有相同的价值。当我们评估哪些硬件提高了性能的时候，最重要的原则与调整服务器参数的原则是相同的：尽可能地把最多的信息放在最快的存储中，并让这些信息尽可能地保持在该存储中。

　　你可以改变几种硬件配置来提升服务器的性能：

　　在计算机上安装更多的内存。这可以让你把服务器的缓存和缓冲区大小值配置成更大的，从而使数据保存在内存中的时间更长，从磁盘上读取信息的需要更少。

　　重新配置系统，如果你拥有足够的内存，能够在内存文件系统中执行全部的交换操作，那么就删除所有的磁盘交换设备。否则，即使你拥有足够的用于交换操作的RAM，某些系统仍然会跟磁盘进行交换操作。

　　增加更快的磁盘以改善I/O等待时间。在这种情况下，寻道时间是有代表性的主要的性能决定因素。横向移动磁头的速度比较慢，在磁头定位以后，从磁道上读取信息块的速度相对较快。但是，如果需要选择是添加更多的内存还是更快的磁盘，那么最好选择添加更多的内存。内存总是比磁盘快，而且添加内存可以让你使用更大的缓存，从而减少磁盘活动。

　　通过在物理设备上划分磁盘活动来获取并行操作的优势。如果你可以在多个物理设备上划分读操作和写操作，那么其速度就会比从同一个设备读写要快一些。例如，如果你把数据库存储在一个设备上，把日志存储在另一个设备上，那么同时向两个设备写入信息的速度就比数据库和日志共享同一个设备的速度要快。请注意，使用同一个物理设备上的不同分区不算是并行操作。这是没有好处的，因为它们仍然需要争用相同的物理资源（磁头）。

　　在把数据重新部署到另外一个设备之前，你要确保自己知道系统的负载状况。如果在某个特定的物理设备上正在运行一些重要的业务，那么把数据库放在该设备上有可能使性能更差。例如，如果你正在处理大量的Web业务，同时把数据库移动到Web服务器文档目录所在的设备上，就可能感觉不到任何优势。

　　使用RAID设备可以让你获取并行操作的优势。

　　使用多处理器硬件。对于类似MySQL服务器的多线程应用程序来说，多处理器硬件可以同时执行多个线程。

Thursday, May 24, 2007

图像数据库 [整理]

1. 用ORACLE数据库存储海量图像数据

==============================
2004-06-29 10:32:01
郑浩张蔚

在利用ORACLE数据库平台进行数据库系统的开发过程中，对于海量图像数据的管理，目前，大都采用表+实体的方法，即图像数据以文件形式存放于指定的计算机目录下，在数据库表中只反映图像数据文件的存储路径。这种管理模式，给数据的维护增加了难度，同时，也给数据的安全带来一定的隐患。因此，要真正做到各类数据在数据库中安全管理，研究和探索直接将海量图像数据存储在数据库关系表中的方法是非常必要的。
笔者在Visual Basic 6.0开发环境中，采用客户机／服务器的工作方式，针对ORACLE数据库关系表中存储大量图像数据的问题和存储海量图像数据的策略与方法进行了初步探讨，提出了一套基本解决方案，供读者参考。

一、大对象数据类型介绍

在关系型数据库中，大数据量图像数据等大型对象是由lob型字段来进行存取的。在Oracle8i中，正式引入了此标准，以适应多媒体大对象

处理的需求。Oracle数据库中，lob型数据有以下几种：

Lob类型说明
Clob：和Oracle7的long型相似，clob可以存储单字节型数据
Nclob： Nclob存储定宽的多字节国家字符集数据
Blob：和Oracle7中的long raw类型相似。可以存储无结构的二进制数据。Oracle8没有对这种数据进行解释，只是按照原来的形式存储和检索它。
Bfile： Bfile允许对Oracle数据库以外存储的大型二进制文件进行只读形式的访问。和其它三种lob类型数据不同的是，bfile类型数据存储在一个单独的文件中，该文件不由Oracle来维护。

特点：
1.在Oracle7中，相应的long或long raw字段有2g的限制，而lob的限制是4g 。
2.lob可以使用调用接口OCI或者由pl/sql利用dbms_lob包进行操纵。
3.lob不象long型那样每个表中最多只有一个字段的限制，其可以有多个，而又可以利用触发器的特性。
4.lob数据处理可以获得与其它数据同样的事物特性。
5.lob的存储比较特殊，它并不是跟其他数据存储在同一个数据库表中，而是可以单独存放于不同的表空间中，由一个定位符指向实际的lob数据。

二、存储海量图像数据的策略

图像数据库技术一直致力于解决海量数字图像的有效存储和管理问题。它是数据库技术的继承和发展，一方面，图像数据和文本数据存在

着本质的区别，在文本数据领域得以成功应用的传统数据库技术，如果一成不变的照搬到图像数据库领域，结果往往是低效，甚至无效；另一方面，传统数据库的许多成果，如SQL语言、索引技术等都值得图像数据库借鉴。上述两个方面的结合成为目前图像数据库技术发展的主流。
BLOB大对象数据是数据量很大的数据类型，它会占用大量的硬盘空间、内存和网络资源，因此合理地设计包含有BLOB大对象数据类型的属性表，对提高存储效率、查询速度有很大的影响。一般BLOB大对象的设计原则如下：

（1）尽量不使用BLOB大对象
二进制大对象并不一定要存储为text、ntext或者image数据类型，它们也可以作为varchar或者varbinary数据类型存储在表格中。数据类型的选择要根据将要存储的BLOB的实际大小。如果数据不会超过8K，那么就使用Varchar或者varbinary数据类型。如果这些大对象的尺寸超过8K，那么就使用text、ntext或者image数据类型。

（2）何时使用BLOB数据类型

在下列情况下，我们可能要使用到BLOB数据类型
·您要将OLE对象（如图形、声音等）存入您的数据库中；
·您要将大型的二进制对象存入您的数据库中；
·您所要操纵的文本对象过大，以致于一般的字符串函数无法对其操作；
·您所使用数据库的数据类型oracle不能支持，所以您只能使用blob函数对其进行操纵。

（3）最好将BLOB存储在数据库中

常见的设计问题是将图片存在数据库中还是存在文件系统中。在大多数情况下，最好把图片文件与其它数据一起存在数据库中。因为将影

像数据文件存储在数据库中有许多优点：
⑴易于管理。当BLOB与其他数据一起存储在数据库中时，BLOB和表格是数据一起备份和恢复。这样就降低了表格数据与BLOB数据不同步的机会，而且降低了其他用户无意中删除了文件系统中BLOB数据位置的路径和风险。另外，将数据存储在数据库中BLOB和其他数据的插入、更新和删除都在同一个事务中实现。这样就确保了数据的一致性和文件与数据库之间的一致性。还有一点好处是不需要为文件系统中的文件单独设置安全性。
⑵可伸缩性。尽管文件系统被设计为能够处理大量不同大小的对象，但是文件系统不能对大量小文件进行优化。在这种情况下，数据库系统可以进行优化。
⑶可用性。数据库具有比文件系统更多的可用性。数据库复制允许在分布式环境中复制、分配和潜在的修改数据。在主系统失效的情况下，日志转移提供了保留数据库备用副本的方法。

当然，在某些情况下，将图片存储在文件系统中将是更好的选择：
（1）使用图片的应用程序需要数据流性能，例如实时的视频重现。
（2）象Microsoft PhotoDraw或者Adobe Photoshop这样的应用程序经常访问BLOB，这些应用程序只知道怎样访问文件。
（3）需要使用一些NTFS文件系统中的特殊功能，例如远程存储。

三、海量数据存储、备份及分发

（1）存储
目前对数据的存储可分为两大类型，一种是传统的以主机为中心的存储方式，另一种是基于网络的以网络为中心的存储方式。与传统的存储方式相比，网络存储具有更大的灵活性，可以实现对所有数据的共享，可以较好地保持数据的一致性、完整性和安全性。存储方式有许多，最常用的是磁带库和磁盘阵列。但是，磁盘阵列价格昂贵，磁带库又有很多缺点。最近，市面上出现了一种叫光盘库的东西。DVD光盘库，特别是DVD-RAM光盘库适用于所有大容量资料数据的存储场合，适合存储一些资料性的不经常更改的数据，比如：医院的医疗影像资料、银行等金融机构的重要票据影像资料、图书馆的书库、电视台的音像资料库等等。
DVD光盘库不仅支持传统的文件，而且可以支持各种类型的数据库，例如Oracle、Informix、SQL等，用户可以直接把数据库的表空间建立在光盘库中的DVD-RAM光盘上，可以支持数据查询和数据插入、修改、删除等操作，对于用户完全透明，感觉就如同使用硬盘一样方便。但是，目前DVD光盘库的技术似乎不太成熟，还不能作为安全性较高的数据库的存储设备。

（2）备份
光盘作为一种近十年才兴起的存储介质，同传统的磁带、软磁盘相比具有不可同日而语的优点。CD/DVD光盘由于容量大，目前单盘9.4GB，日后容量将迅速突破20GB、易保存，保存期长达30年以上，可靠性高，即使表面磨损，也可用几百元一台的修复机迅速修复，携带方便，数据交换方便，每台PC目前大部分都安装了光盘驱动器。特别DVD-RAM作为一种世界潮流的新存储介质将拥有无可限量的前途。既可以用作普通的备份介质又可用作实时的存储载体。
磁带特别是数据流磁带的单盘容量确实较大，可达到500GB，但这样一盘磁带的价格将近1000美元，同时磁带如保管不善易发霉、易磨损，接近磁体时易数据丢失，而且不同格式的磁带驱动器也不相兼容，造成了介质数据交换不便。光盘与硬盘相比也有许多无可比拟的优点，硬盘本身比较脆弱，遇到大的震动、冲击，容易损坏，光盘就可很好的解决这些问题。所以，大型图像数据库系统所有数据的备份能通过光盘完成是最好的选择。

（3）分发

目前，DVD驱动器已经成为IT行业的一种标准输入输出设备，单张DVD光盘片容量大，如DVD-ROM（4.7GB）、DVD-RAM（9.4GB），非常适合

作为分发存储介质。

四、存储图像数据的方法举例

4.1 建立具有BLOB字段的ORACLE数据库

按照如下步骤来完成各个操作：

（1）创建表空间：
CREATE TABALEESPACE VIDO_STORE
DATAFILE'C:DATABASEtest.dbf' SIZE 200M

（2）创建表：
create table part(
part_id NUMBER, 主建
part_name VARCHAR2(20),
part_image BLOB,
part_desc CLOB
part_colla BFILE
);
这个数据库第一列存储一个码，第二列存储名称，另外三列存储lob型数据。

（3）创建新的用户：如user1/pass1，赋予connect,resource权限。

（4）创建逻辑目录：
bfile类型有着特殊性，跟clob，blob不同。实际的数据文件存储在操作系统的外面：所以有两个特点：1.没有事务性控制 2.bfile是只读的，不能用dbms_lob或oracl8 oci进行修改。为了访问外部文件，服务器需要知道文件在操作系统中的位置。下面我们建立一个目录：
create DIRECTORY utils AS '/home/utils';
utils表示目录逻辑名，'/home/utils'是实际目录。

总结：

表空间 VIDO_STORE
Oracle service_names： oradb
Oracle用户名： user1
户名密码： pass1
测试表名： test
tnsnames： oradb
逻辑目录： utils

4.2利用Visual Basic 6.0来处理大对象

在vb中处理大对象，一般可以用OO4O(oracle objects for ole)来处理。这里介绍一种不用0040处理大对象blob的方法。
下面这段程序可以将一个图像数据保存到数据库中，并可以将其从数据库读出。
程序中需要两个commandbutton
cmd1 名称 cmdsave caption 保存
cmd2 名称 cmdread caption 读取

向数据库中写入数据：
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim PartDesc As OraClob
Dim buffer As String
Dim chunksize As Long
Dim amount_written As Long

'建立OraSession对象.
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'打开数据库连接建立OraDatabase 对象.
Set OraDatabase = OraSession.OpenDatabase("ExampleDb", "scott/tiger", 0&)

'建立 OraDynaset 对象
Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&)
Set PartDesc = OraDynaset.Fields("part_desc").Value
chunksize = 32000

重新调整buffer大小

buffer = String$(chunksize, 32)
FNum = FreeFile

'打开文件
Open "partdesc.dat" For Binary As #FNum

'设置offset和PollingAmount属性

'写入操作
PartDesc.offset = 1
PartDesc.PollingAmount = LOF(FNum)
remainder = LOF(FNum)

'锁定写入行
OraDynaset.Edit
Get #FNum, , buffer

'第一次写入操作
amount_written = PartDesc.Write(buffer, chunksize, ORALOB_FIRST_PIECE)
While PartDesc.Status = ORALOB_NEED_DATA
remainder = remainder - chunksize
If remainder < chunksize Then
piecetype = ORALOB_LAST_PIECE
chunksize = remainder
Else
piecetype = ORALOB_NEXT_PIECE
End If
Get #FNum, , buffer
amount_written = PartDesc.Write(buffer, chunksize, piecetype)
Wend
Close FNum

'更新提交
OraDynaset.Update

从数据库中读取数据：
Dim OraSession As OraSession
Dim OraDatabase As OraDatabase
Dim OraDynaset As OraDynaset
Dim PartImage As OraBlob
Dim chunksize As Long
Dim AmountRead As Long
Dim buffer As Variant
Dim buf() As Byte

'建立 OraSession对象
Set OraSession = CreateObject("OracleInProcServer.XOraSession")

'建立OraDatabase对象
Set OraDatabase = OraSession.OpenDatabase("ExampleDb","scott/tiger", 0&)

'建立OraDynaset对象
Set OraDynaset = OraDatabase.CreateDynaset("select * from part", 0&)

'从动态集中获得OraBlob
Set PartImage = OraDynaset.Fields("part_image").Value

'设置Offset和PollingAmount属性
PartImage.offset = 1
PartImage.PollingAmount = PartImage.Size
chunksize = 50000

'获得自由文件号
FNum = FreeFile

'打开文件
Open "image.dat" For Binary As #FNum

'第一次读
AmountRead = PartImage.Read(buffer, chunksize)
buf = buffer
Put #FNum, , buf

' 检查属性
While PartImage.Status = ORALOB_NEED_DATA
AmountRead = PartImage.Read(buffer, chunksize)
buf = buffer
Put #FNum, , buf
Wend
Close FNum

五、分析与展望

随着数据库管理系统功能的不断增强、性能的不断完善，将各类数据完全由数据库管理系统统一存储和管理，已成为技术发展的趋势。只

有这样，数据库管理系统的强大功能才能得到充分发挥，数据的安全性才能得到充分的保障，使得诸如数据库复制、数据的转移等许多工作，变得非常简单容易。
但是，也应当清醒的认识到对于大数据量的图像数据的存储，我们还有许多问题要进行研究。一个问题就是大对象数据的特殊操作实现，因为lob型数据是二进制的大对象，他不能简单的按照一般数据的操作符来进行计算。比如，要查出一个数据表中含有大对象的图像，如果图像很大，我们又要进行浏览，那怎么办呢？如果直接进行读取那速度是难以忍受的。只有采取分块或添加索引影像的方法，由此会带来许多需要研究的问题。
另外一个重要的问题就是对海量图像数据库的性能优化，原先的优化方法如索引优化等仍然适用，但是现在遇到了新的问题：海量图像数据是庞大的，那么对海量图像数据的操作（尤其是检索）开销巨大，那么如何降低这种开销，缩短操作时间，又是一个重要课题。

(来源：CCW)

2. 存储50万张图片的数据库

==============================
bfile类型，存储的就是文件的路径，
例子：
grant create any directory to scott;
grant create any library to scott;
create or replace directory utllobdir as 'G:oracle';
create table bfile_tab (bfile_column BFILE);
create table utl_lob_test (blob_column BLOB);

set serveroutput on

然后执行下面语句就将G:oracle目录下的Azul.jpg存入到utl_lob_test表中的blob_column字段中了。
declare
a_blob BLOB;
a_bfile BFILE := BFILENAME('UTLLOBDIR','Azul.jpg');
begin
insert into bfile_tab values (a_bfile)
returning bfile_column into a_bfile;
insert into utl_lob_test values (empty_blob())
returning blob_column into a_blob;
dbms_lob.fileopen(a_bfile);
dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
dbms_lob.fileclose(a_bfile);
commit;
end;
/
select dbms_lob.getlength(blob_column) from UTL_LOB_TEST;

本人正在做一个存储50万张图片的数据库，希望大家讨论一下，包括字段格式，存储方式，相关代码等给一个最优解。
50W张图片，如果存到数据库，那就很大了。用sql server2000存进去也可以。如果保密性要求不高的话，最好将图片存到一个目录下，数据库就存路径名就好了。
1.方案选择
如果你的数据库经常转移地方，则把图片存储在数据库中，这样方便。
如果你着重于效率，则将图片文件直接放在文件服务器中，数据库中只保存检索必须的信息，例如：文件名，目录，简介，检索关键字。
2.字段类型
图片放在数据库中的话，sql数据库字段类型用image，ACCESS用ole，其他数据库各自有各自的对应字段类型。
3.图片放在数据库中的存取

'VB/VBA中实现数据库中的文件存取

'示例数据库为ACCESS数据库,用SQL数据库的话,只需要改连接字符串
'
'*************************************************************************
'**
'** 使用 ADODB.Stream 保存/读取文件到数据库
'** 引用 Microsoft ActiveX Data Objects 2.5 Library 及以上版本
'**
'** ----- 数据库连接字符串模板 ---------------------------------------
'** ACCESS数据库
'** iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
'** ";Data Source=数据库名"
'**
'** SQL数据库
'** iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
'** "User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
'**
'*************************************************************************
'
'保存文件到数据库中
Sub s_SaveFile()
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConcStr As String

'ACCESS数据库的连接字符串
iConcStr = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=F:My Documents客户资料1.mdb"

'SQL数据库的连接字符串
iConcStr = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=用户名;Password=密码;Initial Catalog=数据库名;Data Source=SQL服务器名"
'读取文件到内容
Set iStm = New ADODB.Stream
With iStm
.Type = adTypeBinary '二进制模式
.Open
.LoadFromFile "c:test.doc"
End With

'打开保存文件的表
Set iRe = New ADODB.Recordset
With iRe
.Open "表", iConc, adOpenKeyset, adLockOptimistic
.AddNew '新增一条记录
.Fields("保存文件内容的字段") = iStm.Read
.Update
End With

'完成后关闭对象
iRe.Close
iStm.Close
End Sub

'从数据库中读取数据,保存成文件
Sub s_ReadFile()
Dim iStm As ADODB.Stream
Dim iRe As ADODB.Recordset
Dim iConc As String

'数据库连接字符串
iConc = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False" & _
";Data Source=\xzc$Inetpubzjzjzj.mdb"

'打开表
Set iRe = New ADODB.Recordset
iRe.Open "tb_img", iConc, adOpenKeyset, adLockReadOnly
iRe.Filter = "id=64"

if iRe("img").ActualSize>0 Then

'保存到文件
Set iStm = New ADODB.Stream
With iStm
.Mode = adModeReadWrite
.Type = adTypeBinary
.Open
.Write iRe("img")
.SaveToFile "c:test.doc"
End With

'关闭对象
iStm.Close
End If

iRe.Close
End Sub

新建一个表 img表列 id int , tp image
'是一个类savepicture
Dim Save_Rs As New ADODB.Recordset
Dim FileLength As Long '定义文件长度
Dim NumBlocks As Integer '定义块数量
Dim LeftOver As Integer '定义剩余数量
Dim byteData() As Byte '定义二进制数据块
Const BlockSize = 1000 '定义快大小
Dim MyFile As New FileSystemObject
Dim MyFolder As Folder
Dim i As Integer

Public Function Save(Ado As ADODB.Connection, DiskFile As String, Table As String, Col As String, id As String, Optional

IdValue As String) As Boolean
Dim strSQL As String
'没有文件路径
If Len(DiskFile) = 0 Then
Save = False
Exit Function
End If
'没有表名
If Len(Table) = 0 Then
Save = False
Exit Function
End If
'没有字段名称
If Len(Col) = 0 Then
Save = False
Exit Function
End If
'没有条件列名
If Len(id) = 0 Then
Save = False
Exit Function
End If
'没有条件列名的值
If Len(IdValue) = 0 Then
Save = False
Exit Function
End If

On Error GoTo err1
If MyFile.FolderExists("c:MyTemp") = True Then
MyFile.DeleteFolder "c:MyTemp", True
End If
MyFile.CreateFolder "c:MyTemp"

If Save_Rs.State = 1 Then Save_Rs.Close

strSQL = "select " & id & ", " & Col & " from " & Table & " where " & id & " ='" & IdValue & "'"

Save_Rs.Open strSQL, Ado, adOpenKeyset, adLockOptimistic

Open DiskFile For Binary Access Read As #1
FileLength = LOF(1)
NumBlocks = FileLength BlockSize
LeftOver = FileLength Mod BlockSize

ReDim byteData(BlockSize)
For i = 1 To NumBlocks
Get #1, , byteData()
Save_Rs.Fields(Col).AppendChunk byteData()
Next
ReDim byteData(LeftOver)
Get #1, , byteData()
Save_Rs.Fields(Col).AppendChunk byteData()
Close #1
Save_Rs.Update

If Save_Rs.State = 1 Then Save_Rs.Close

Save = True
Exit Function

err1:
Save = False
If Save_Rs.State = 1 Then Save_Rs.Close
MsgBox "保存没有成功" & Err.Description, vbQuestion

End Function

Public Function Show(Ado As ADODB.Connection, Table As String, Col As String, id As String, Optional IdValue As String)

As String
Dim strSQL As String
Dim TempF As String

'没有表名
If Len(Table) = 0 Then
Show = ""
Exit Function
End If
'没有字段名称
If Len(Col) = 0 Then
Show = ""
Exit Function
End If
'没有条件列名
If Len(id) = 0 Then
Show = ""
Exit Function
End If
'没有条件列名的值
If Len(IdValue) = 0 Then
Show = ""
Exit Function
End If

On Error GoTo err1

If MyFile.FolderExists("c:MyTemp") = True Then
MyFile.DeleteFolder "c:MyTemp", True
End If
MyFile.CreateFolder "c:MyTemp"

If Save_Rs.State = 1 Then Save_Rs.Close
strSQL = "select " & id & "," & Col & " from " & Table & " where " & id & " ='" & IdValue & "'"

Save_Rs.Open strSQL, Ado, adOpenKeyset, adLockOptimistic

FileLength = Save_Rs.Fields(1).ActualSize
TempF = "c:mytempasd.txt"

Open TempF For Binary Access Write As #2
NumBlocks = FileLength BlockSize
LeftOver = FileLength Mod BlockSize

ReDim byteData(LeftOver)
byteData() = Save_Rs.Fields(1).GetChunk(LeftOver)
Put #2, , byteData()

For i = 1 To NumBlocks
ReDim byteData(BlockSize)
byteData() = Save_Rs.Fields(1).GetChunk(BlockSize)
Put #2, , byteData()
Next
Close #2
Show = TempF
If Save_Rs.State = 1 Then Save_Rs.Close
Exit Function
err1:
Show = ""
If Save_Rs.State = 1 Then Save_Rs.Close
End Function

vb中
Dim adocon As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim DiskFile As String
Dim FileLength As Long
Dim NumBlocks As Integer
Dim LeftOver As Integer
Dim byteData() As Byte
Const BlockSize = 1000
Dim MyFile As New FileSystemObject
Dim MyFolder As Folder
Dim i As Integer
Dim TP As New SaveBin

'保存
Private Sub Command1_Click()
dim diskfile as string
CD.ShowOpen
DiskFile = CD.FileName
if len(trim(diskfiel))>0 then
TP.Save adocon, DiskFile, "img", "img", "nno", "1"
endif
End Sub

'显示
Private Sub Command2_Click()
Dim TempFile As String
TempFile = TP.Show(adocon, "image11", "img", "nno", "1")
Image1.Picture = LoadPicture(TempFile)
End Sub

3. 数据库中图片存储及读取

==============================
源作者：波波

开发环境：Window 2000、SQLServer2000、.Net Framework SDK正式版
开发语言：C#、ASP.Net
简介：数据库中图片存储及读取

说明：在ASP中，我们用Request.TotalBytes、Request.BinaryRead()来上传图片，这个可恶的BinaryRead()方法非常笨，单个文件上传倒没什么大事，单如果多个图片上专可就花大气力了…！而现在ASP.Net中将会把解决以前ASP中文件上传的种种问题，使你在ASP.Net中轻轻松松开发出功能强大的上传程序，下面大家看看例子啦。

首先在SQL Server中建立一个图片存储的数库表，SqlScript如下:

if exists (select * from dbo.sysobjects where id = object_id(N"[dbo].[image]") and OBJECTPROPERTY(id, N"IsUserTable") = 1)
drop table [dbo].[image]
GO

CREATE TABLE [dbo].[image] (
[img_pk] [int] IDENTITY (1, 1) NOT NULL ,
[img_name] [varchar] (50) NULL ,
[img_data] [image] NULL ,
[img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
[img_pk]
) ON [PRIMARY]
GO
------------------------------------------------------------
一、上传图片:
imgupload.aspx文件:
<%@ Page language="c#" Codebehind="imgupload.aspx.cs" AutoEventWireup="false" Inherits="study.uploadimage.imgupload" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>imgupload</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body>
<form enctype="multipart/form-data" runat="server" id="form1" name="form1">
文件名 <input type="text" id="imgName" runat="server" NAME="imgName">
<br>
选择文件 <input id="UploadFile" type="file" runat="server" NAME="UploadFile">
<br>
<asp:button Text="上传" runat="server" ID="Button1" />
</form>
<a href="imgview.aspx?id=1" target="_blank">看图</a>
</body>
</HTML>

codebehind文件:
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.IO;
using System.Data.SqlClient;

namespace study.uploadimage
{
/// <summary>
/// imgupload 的摘要说明。
/// </summary>
public class imgupload : System.Web.UI.Page
{
protected System.Web.UI.WebControls.Button Button1;
protected System.Web.UI.HtmlControls.HtmlInputText imgName;
protected System.Web.UI.HtmlControls.HtmlInputFile UploadFile;

private void Page_Load(object sender, System.EventArgs e)
{
// 在此处放置用户代码以初始化页面
}

private void Button1_Click(object sender, System.EventArgs e)
{
Stream imgStream;
int imgLen;
string imgName_value;
string imgContentType;
string imgUploadedName;

imgStream = UploadFile.PostedFile.InputStream;
imgLen = UploadFile.PostedFile.ContentLength;
imgUploadedName = UploadFile.PostedFile.FileName;
byte[] imgBinaryData=new byte[imgLen];
imgContentType = UploadFile.PostedFile.ContentType;
imgName_value = imgName.Value;

try
{
if(imgName_value.Length < 1)
{
imgName_value = GetLastRightOf("\",imgUploadedName );
}
}
catch(Exception myEx)
{
Response.Write(myEx.Message);
}

int n = imgStream.Read(imgBinaryData, 0, imgLen);
int NumRowsAffected = MyDatabaseMethod(imgName_value, imgBinaryData, imgContentType);
if(NumRowsAffected > 0)
Response.Write( "<BR> uploaded image " );
else
Response.Write ( "<BR> an error occurred uploading the image.d " );
}
public string GetLastRightOf(string LookFor,string myString)
{
int StrPos;
StrPos = myString.LastIndexOf(LookFor);
return myString.Substring(StrPos + 1);
}
public int MyDatabaseMethod(string imgName,byte[] imgbin,string imgcontenttype)
{
SqlConnection connection = new SqlConnection(Application["Test_Conn"].ToString());
string SQL="INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )";
SqlCommand command=new SqlCommand ( SQL,connection );

SqlParameter param0=new SqlParameter ( "@img_name", SqlDbType.VarChar,50 );
param0.Value = imgName;
command.Parameters.Add( param0 );

SqlParameter param1=new SqlParameter ( "@img_data", SqlDbType.Image );
param1.Value = imgbin;
command.Parameters.Add( param1 );

SqlParameter param2 =new SqlParameter ( "@img_contenttype", SqlDbType.VarChar,50 );
param2.Value = imgcontenttype;
command.Parameters.Add( param2 );

connection.Open();
int numRowsAffected = command.ExecuteNonQuery();
connection.Close();
return numRowsAffected;
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Button1.Click += new System.EventHandler(this.Button1_Click);
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}

------------------------------------------------------------
二、浏览图片：
imgvies.aspx文件：
<%@ Page language="c#" Codebehind="imgview.aspx.cs" AutoEventWireup="false" Inherits="study.uploadimage.imgview" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
<title>imgview</title>
<meta name="GENERATOR" Content="Microsoft Visual Studio 7.0">
<meta name="CODE_LANGUAGE" Content="C#">
<meta name="vs_defaultClientScript" content="JavaScript">
<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
</HEAD>
<body MS_POSITIONING="GridLayout">
</body>
</HTML>

codebehind文件：
using System;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Web;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;

namespace study.uploadimage
{
/// <summary>
/// imgview 的摘要说明。
/// </summary>
public class imgview : System.Web.UI.Page
{

private void Page_Load(object sender, System.EventArgs e)
{
SqlConnection myDSN = new SqlConnection(Application["Test_Conn"].ToString());
myDSN.Open();

int imgid = int.Parse(Request.QueryString["id"]);
string sqlText = "SELECT img_name, img_data, img_contenttype FROM image where img_pk=" + imgid;
Trace.Write(sqlText);
SqlCommand MyCommand = new SqlCommand (sqlText, myDSN);
SqlDataReader dr =MyCommand.ExecuteReader();
if(dr.Read())
{
Response.ContentType = (dr["img_contenttype"].ToString());
Response.BinaryWrite((byte[])dr["img_data"]);
}
myDSN.Close();
}

#region Web Form Designer generated code
override protected void OnInit(EventArgs e)
{
//
// CODEGEN：该调用是 ASP.NET Web 窗体设计器所必需的。
//
InitializeComponent();
base.OnInit(e);
}

/// <summary>
/// 设计器支持所需的方法 - 不要使用代码编辑器修改
/// 此方法的内容。
/// </summary>
private void InitializeComponent()
{
this.Load += new System.EventHandler(this.Page_Load);
}
#endregion
}
}

这样这个程序就完成了，简单吧。当然还很多改进之处，希望大家多想想多编编一定可以写出更多的图象上传程序。

4. 系统中图片存储的解决方案

==============================
【标　题】：系统中图片存储的解决方案——将图片放在服务器上，将其路径存入数据库
【关键字】：
【来　源】：http://blog.csdn.net/icefireleaf/archive/2006/12/20/1450962.aspx

系统中图片存储的解决方案——将图片放在服务器上，将其路径存入数据库

主题　　：系统中图片存储的解决方案——将图片放在服务器上，将其路径存入数据库
方案明细：1、用户选择的图片由系统将其拷贝至服务器"\servermcphoto"中，每个文件夹限制存放图片1000张
　 2、用户选择图片后，系统自动检查当前使用文件夹内图片数量是否超出限制，若否，则继续使用，若是，则新建一文件夹
　 3、保存图片时，系统自动检查该物品图片是否是新增图片，若是，则将当前使用文件夹中文件数加1
　 4、对于以前在数据库储存的图片均已转换为图片文件放在"\servermcphotoold"中，若对其更换图片，则将新图片放入当前使用文件夹，同时将原图片文件删除
整理人：icefireleaf
整理时间：2006-12-20
备注：该方案是在公司改将图片存入数据库为将图片路径存入数据库之后编写，因此该方案增加了对更换从数据库中转换出来的图片时的处理，old文件夹内存放的是以前在数据库中存放，现转换出来的图片，更改存储图片方案后用户选择的图片均放在其它文件夹（即以‘n’开头的文件夹）
//----------------------------------------------------------------//
string ls_picture_path,ls_old_picturepath
string ls_docname,ls_named
int li_value
int li_filenumber,li_return
string ls_filepath,ls_filename,ls_newfile
string ls_oldfile,ls_oldfilename
int li_id
//
if dw_edit_sub.getrow()<1 then
return
end if
//
li_value=GetFileOpenName("请选择物品图片",ls_docname,ls_named,".jpg","jpg files(*.jpg),*.jpg")
if li_value=1 then
//表ormaster_file记录存放图片文件的文件夹的名称、路径、当前存放图片数量
select id,file_path,file_name,file_number
into :li_id,:ls_filepath,:ls_filename,:li_filenumber
from ormaster_file
order by id desc using sqlca;
//
if li_filenumber<=1000 then
//文件夹内图片数量未超出限制，继续使用该文件夹
ls_picture_path=ls_filepath + "" + ls_filename + "" + is_stk_no + ".jpg"
else
//文件夹内图片数量超出限制，新建一文件夹供保存图片使用，文件夹以该文件夹存满时系统已存放图片数的理论值命名
li_id=li_id + 1
ls_filename="n" + string(li_id*1000)
ls_newfile=ls_filepath + "" + ls_filename
//create new file
if not DirectoryExists(ls_newfile) then
if CreateDirectory(ls_newfile) <> 1 then
MessageBox(gs_sysmess,'创建图片保存目录'+ls_newfile+'失败﹗')
//gs_sysmess为定义的messagebox对话框的标题
return
end if
end if
//insert the record that create new file
insert into ormaster_file (file_path,file_name,file_number,creator)
values(:ls_filepath,:ls_filename,0,:gsuserid)
using sqlca;
if sqlca.sqlcode=0 then
commit;
else
rollback;
end if
//
ls_picture_path=ls_filepath + "" + ls_filename + "" + is_stk_no + ".jpg"
end if
ls_old_picturepath=dw_edit_sub.object.picture_path[1]
ls_oldfile=left(ls_old_picturepath,24)
//删除原有图片
if ls_oldfile="\servermcphotoold" then
ls_oldfilename=ls_oldfile + is_stk_no + ".jpg"
filedelete(ls_oldfilename)
end if
//copy picture to server
li_return=FileCopy(ls_docname,ls_picture_path,true)
//
if li_return=1 then
dw_edit_sub.object.picture_path[1]=ls_picture_path
p_1.picturename=ls_picture_path
//若是新加图片或更换从数据库中转换出来的图片，则当前存放图片文件夹内图片数量加1
if ls_old_picturepath='' or isnull(ls_old_picturepath) or ls_oldfile="\servermcphotoold" then
update ormaster_file set file_number=file_number + 1
where id=:li_id using sqlca;
if sqlca.sqlcode=0 then
commit;
else
rollback;
end if
end if
messagebox(gs_sysmess,"保存物品图片成功")
else
messagebox(gs_sysmess,"保存物品图片失败")
return
end if
else
messagebox(gs_sysmess,"选择物品图片失败")
return
end if

5. 基于JSP实现图片的数据库存储与显示

==============================
日期：2006-3-17 13:28:36
摘要：本文介绍了利用JSP的编程模式如何实现图片的数据库存储和显示。
关键词：JSP、动态存取、SQL、输入输出流
收集整理：晨风教程网（http://www.Net118.com）

1、引言
数据库应用程序，特别是基于WEB的数据库应用程序，常会涉及到图片信息的存储和显示。通常我们使用的方法是将所要显示的图片存在特定的目录下，在数据库中保存相应的图片的名称，在JSP中建立相应的数据源，利用数据库访问技术处理图片信息。但是，如果我们想动态的显示图片，上述方法就不能满足需要了。我们必须把图片存入数据库，然后通过编程动态地显示我们需要的图片。实际操作中，可以利用JSP的编程模式来实现图片的数据库存储和显示。

2、建立后台数据库
假定处理的是图片新闻，那么我们可以建立相应的数据库及数据表对象。我们要存取的数据表结构的SQL脚本如下所示：
if exists (select * from dbo.sysobjects where id =
object_id(N'[dbo].[picturenews]') andOBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[picturenews]
GO
CREATE TABLE [dbo].[picturenews] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[image] [image] NULL ,
[content] [varchar] (500) COLLATE Chinese_PRC_CI_AS NULL ,
[detail] [varchar] (5000) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
表picturenews中，字段id作为标识，每存储一行数据，自动增加1。字段image用于存储图片信息，其数据类型为“image”。

3、向数据库存储二进制图片
启动Dreamweaver MX后，新建一个JSP文件。其代码如下所示。
<%@ page contentType="text/html;charset=gb2312"%>
<HTML>
<HEAD>
<TITLE>存储图片</TITLE>
</HEAD>
<body>

<FORM METHOD=POST ACTION="testimage.jsp">
新闻标题：<INPUT TYPE="text" NAME="content"><BR>
新闻图片：<INPUT TYPE="file" NAME="image"><BR>
新闻内容：<TEXTAREA name="txtmail" rows="15" cols="90" style="BORDER-BOTTOM: #000000 1px solid; BORDER-LEFT: #000000 1px

solid; BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; FONT-SIZE: 9pt; HEIGHT: 200px; WIDTH: 100%"

wrap="physical" ></TEXTAREA><br>
<INPUT TYPE="submit"></form>
</body>
</HTML>
将此文件保存为InputImage.jsp文件，其中testimage.jsp文件是用来将图片数据存入数据库的，具体代码如下所示：
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.io.*"%>
<html>
<body>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//加载驱动程序类
Connection con=DriverManager.getConnection("jdbc:odbc:denglu","sa","sa");
//建立数据库联机，其中denglu为数据库名，sa为连接数据库的帐号及密码。
Statement stmt=con.createStatement();
//建立Statement对象
String content=request.getParameter("content");
content=new String(content.getBytes("8859_1"),"gb2312");
String filename=request.getParameter("image");
filename=new String(filename.getBytes("8859_1"),"gb2312");
String detail=request.getParameter("txtmail");
detail=new String(detail.getBytes("8859_1"),"gb2312");

//获得所要显示图片的标题、存储路径、内容，并进行中文编码
FileInputStream str=new FileInputStream(filename);
String sql="insert into picturenews(content,image,detail) values(?,?,?)";
PreparedStatement pstmt=con.prepareStatement(sql);
pstmt.setString(1,content);
pstmt.setBinaryStream(2,str,str.available());
pstmt.setString(3,detail);
pstmt.execute();
//将数据存入数据库
out.println("Success,You Have Insert an Image Successfully");
%>

4、网页中动态显示图片

接下来我们要编程从数据库中取出图片，其代码如下所示。
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.util.*"%>
<%@ page import="java.text.*"%>
<%@ page import="java.io.*"%>
<html>
<body>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//加载驱动程序类
Connection con=DriverManager.getConnection("jdbc:odbc:denglu","sa","sa");
Statement stmt=con.createStatement();
ResultSet rs=null;
//建立ResultSet（结果集）对象
int id= Integer.parseInt(request.getParameter("id"));
//获得所要显示图片的编号id，并转换为整型
String sql = "select image from picturenews WHERE id="+id+"";
//要执行查询的SQL语句
rs=stmt.executeQuery(sql);
while(rs.next()) {
ServletOutputStream sout = response.getOutputStream();
//图片输出的输出流
InputStream in = rs.getBinaryStream(1);
byte b[] = new byte[0x7a120];
for(int i = in.read(b); i != -1;)
{
sout.write(b);
//将缓冲区的输入输出到页面
in.read(b);
}
sout.flush();
//输入完毕，清除缓冲
sout.close();
}
%>
</body>
</html>
将此文件保存为testimageout.jsp文件。下一步要做的工作就是使用HTML标记：
<IMG src="testimageout.jsp?id=<%=rs.getInt("id")%>" width=100 height=100>取出所要显示的图片，其中id是所要取出图片的编号。本例中我们输出了第一个和最后一个图片信息，详细的程序代码如下所示。
<%@ page contentType="text/html;charset=gb2312"%>
<%@ page import="java.sql.*" %>
<html>
<head>
<title>动态显示数据库图片</title>
</head>
<body>
<%
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnection("jdbc:odbc:denglu","sa","sa");
Statement stmt=con.createStatement();
String sql=new String();
sql= "select * from picturenews";
ResultSet rs=stmt.executeQuery(sql);
rs.last();
//将指针移至最后一条记录
%>
<table>
<tr><td><IMG height=99 src="testimageout.jsp?id=1" width=136></td>
//取出第一个图片
<td><IMG height=99 src="testimageout.jsp?id=<%=rs.getInt("id")%>" width=136></td>
//取出最后一个图片
</tr></table>
</body>
</html>
以上WEB应用程序在Windows 2000 Professional/SQL Server 2000/ Apache Tomcat 4.0/JDK 1.4 JAVA环境下调试通过。

6. Image Storage -- Better to store them on the Filesystem or in the MySQL DB

==============================
Posted by: Marco Schierhorn (IP Logged)
Date: November 24, 2005 03:23AM

we´ve large amount of Images ( nearly 2 GB ). And they will increase, cause or Editors will submit several articles a day where they´re allowed to upload some new pics.
So, whats the better solution ? To store them in the filesystem ( with only the path in the db ) or to store them as BLOB´s in the DB ( MySQL ). When we decide to store them in the DB, we´ve thought about splitting larger Images ( > 64 K ) into several pieces into the db. So, we´ve a simulated "streaming". I´ve seen it at [php.dreamwerx.net].
So, any recommendation is very welcome.I would really appreciate any of them.

Marco

--------------
Posted by: Jay Pipes (IP Logged)
Date: November 24, 2005 11:29AM

If you aren't running a web server farm (where images are stored on multiple web servers), let the operating system store the image files, as the OS is better suited for such things. Not that it can't be done in MySQL. It certainly can, but it's more complicated and less efficient than if you can store things in the filesystem. The only times I bother with storing image type files in the SQL server is if there are certain security restrictions, there is a distributed web server farm that needs a single source of image files, or if the client absolutely insists on it... :)

Jay Pipes
Community Relations Manager, North America, MySQL Inc.

--------------
Posted by: James Day (IP Logged)
Date: November 24, 2005 08:31PM

Opinions vary, with good reasons for the different views. Jay gave one common view. There are many merits to that approach. One drawback of using file system storage is the comparative difficulty of replicating it to a server farm to spread the load. This doesn't matter initially but as load grows and you possibly start to have a need for more than one server, possibly at several different sites, the benefits of the replication built into the database start to become more interesting.

In the database server version, you might use a different database server, possibly on the same physical machine initially. The different database makes it easy to have different backup schedules and easy to split later. As load rises you could switch to a different machine or to a set of them. For example, you might consider sets of three plain web servers running Apache as their main job and give them a small MySQL instance just to serve some images and make use of their disk drives for something. Three because that makes recovery from failure easy - if one dies, another can keep serving while you use the third as the source for a copy to a replacement machine. If you use InnoDB you don't need to be

greatly concerned about the chunk size - I know of a case of storage of 64MB video chunks for broadcast distribution. I do recommend not storing them with the metadata or other row data, because that substantially increases the time for typical operations, which are unlikely to need the image itself.

Alternatively, investigate the very interesting MogileFS ( [www.danga.com] ) from the LiveJournal people. It's a very scalable system using MySQL to record where distributed, redundant copies of images are stored. It seems to be a very capable system, with nice features like automatic load balancing recently added.

Better still, build an object-based location specifier which lets you change between any of these methods as circumstances change or you introduce new storage options.

At two gigabytes, my guess is that either MogileFS or the database will be the option which makes your life easiest, through increased performance and availability.

There's no one, perfect, right answer which covers all cases. It's always a matter of considering the situation and the relative merits of each approach in the circumstances involved.

James Day
Support engineer, MySQL AB

--------------
Posted by: Richard Hillström (IP Logged)
Date: May 05, 2006 05:36AM

I run a mysql database to store and search among my personal pictures and other stuff i want to have online. One table is used for all the metadata and one table is used to hold just the files as large blobs and also a third table for picture thumbnails as normal blobs.

I currently have 10GB data, (7 000 files, mostly pictures) in the database, website is running on Apache/PHP/Ubuntu. And all on a laptop, the fastest I could find, but still a laptop! The trick is to use the metadata table as much as possible, and just use the pointer in that table to get the data from the blob tables. And ofcourse indexes, without them you're lost. Insert and selects are as fast as the harddisk can handle. When searching for files i get the thumbnail pictures from the database as previews.

Searching and displaying 3000 thumbnails (which are approx 2-3k in size), takes below 10 seconds. I did a test trying to crash the database by inserting all my pictures but in multiple copies, ended up with over 30GB blob table and approx 20 000 rows. Still no problem at all, selects and getting the data are just as fast as before.

So for me storing in the database works great.

BR
Richard

7. store image files in MySQL or flat file

==============================
http://techrepublic.com.com/

I want to compare 2 image storage systems, one in MySQL and one in a flat file format:-

1) performance
As I read some articles / forums that someone says if the image stored in MySQL to be retreive more faster than stored in a flat file and someone have different ideas.

2) backup
Someone said it is easiler to backup the image files stored in a flat file than stored in MySQL database.

3) security
I would like to hear more about the 3 points that I pointed out since I will install the shopping cart in my website. Once I select the image storage method, then I cannot return it back.

Pleaes give advise.

Andy Ng
Posted: 04/18/2005 @ 01:03
Job Role: Networking / LAN Administration
Location: Hong Kong, HK
Member since: 02/27/2002

------------
1) the db performance takes a hit when you store the images in it. they increase th size for no gain. just store the path to the images.

2) to backup the db, you need to be able to pull a copy of the sql file or else, dump contents to a file with full inserts. if you do not have that access then you can't backup anything in the db. ( unlikley, hosting companies do give that access )

3) images will be copied of people want them. there is no 100% effective means to stop it.
there is no risk to site security by having images in the site space instead of the db, the space should not be world writable anyways.

Posted: 04/21/2005 @ 03:49
Jaqui 124
Job Role: Software / Applications Development
Location: Vancouver, BC
Member since: 08/25/1999

------------
there are a few methods to interfere with downloading images from sites.

javascript based methods are useless, all that needs to be done is turn javascript off and security broken.

best method:
slice image and set as background in table.
in forground, place a transparent gif.
when people try to save, they get the gif.
if they get the backgrounds, then they have to re-assemble the image correctly to be able to use it.

Posted: 06/04/2005 @ 19:03
Jaqui 124
Job Role: Software / Applications Development
Location: Vancouver, BC
Member since: 08/25/1999

Redhobor's Blog

Tuesday, June 12, 2007

If you love css..

需要什么软件学习css?

属性篇 (1)

属性篇 (2)

属性篇 (3)

属性篇 (4)

布局篇 (1)

布局篇 (2)

Friday, May 25, 2007

MySQL查询优化技术系列讲座 [整理]

MySQL查询优化技术系列讲座 [整理]

MySQL查询优化技术系列讲座之使用索引

MySQL查询优化系列讲座之查询优化器

MySQL查询优化系列讲座之数据类型与效率

MySQL查询优化系列讲座之调度和锁定

MySQL查询优化讲座之管理员的优化措施

Thursday, May 24, 2007

图像数据库 [整理]

图像数据库 [整理]

1. 用ORACLE数据库存储海量图像数据

2. 存储50万张图片的数据库

3. 数据库中图片存储及读取

4. 系统中图片存储的解决方案

5. 基于JSP实现图片的数据库存储与显示

6. Image Storage -- Better to store them on the Filesystem or in the MySQL DB

7. store image files in MySQL or flat file

Google Reader List

Blog Archive

Links

About Me