手机浏览 RSS 2.0 订阅 膘叔的简单人生 , 腾讯云RDS购买 | 超便宜的Vultr , 注册 | 登陆
浏览模式: 标准 | 列表2024年11月的文章

MySQL索引的知识点

Submitted by gouki on 2010, May 19, 1:21 PM

数据库是否建索引以及如何更好的建索引，当然是有讲究的。虽然都知道建了索引后，在按条件搜索的时候速度会快很多，但其实，如果索引建的不合理，反而是使得数据库效率降低。何时需要建索引，其实是值得深究的，创建之后，怎样判断效率，当然还得好好学习。要多做尝试才会知道。

这一篇讲的比较详细，所以就转载了，来自冰山的http://xinsync.xju.edu.cn/index.php/archives/6845。

在数据库表中，对字段建立索引可以大大提高查询速度。假如我们创建了一个 mytable表：

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL  
  4. );  

 

我们随机向里面插入了10000条记录，其中有一条：5555, admin。

在查找 username=”admin” 的记录 SELECT * FROM mytable WHERE username=’admin’; 时，如果在username上已经建立了索引，MySQL无须任何扫描，即准确可找到该记录。相反，MySQL会扫描所有记录，即要查询10000条记 录。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索包含多个列。

索引的类型包括：

（1）普通索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
●     创建索引

SQL代码
  1. CREATE INDEX indexName ON mytable(username(length));  

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。
●     修改表结构

SQL代码
  1. ALTER mytable ADD INDEX [indexName] ON (username(length))  

●     创建表的时候直接指定

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     INDEX [indexName] (username(length))  
  5. );  

 

删除索引的语法：

SQL代码
  1. DROP INDEX [indexName] ON mytable;  


（2）唯一索引

 

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
●     创建索引

SQL代码
  1. CREATE UNIQUE INDEX indexName ON mytable(username(length))  

 

●     修改表结构

SQL代码
  1. ALTER mytable ADD UNIQUE [indexName] ON (username(length))  

 

●     创建表的时候直接指定

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     UNIQUE [indexName] (username(length))  
  5. );  

 

（3）主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引：

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     PRIMARY KEY(ID)  
  5. );  

 

当然也可以用 ALTER 命令。记住：一个表只能有一个主键。

（4）组合索引

为了形象地对比单列索引和组合索引，为表添加多个字段：

SQL代码
  1. CREATE TABLE mytable(  
  2.     ID INT NOT NULL,  
  3.     username VARCHAR(16) NOT NULL,  
  4.     city VARCHAR(50) NOT NULL,  
  5.     age INT NOT NULL  
  6. );  

 

为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：

SQL代码
  1. ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);  

 

建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。

如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此 时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最有效率的单列索引。

建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：

SQL代码
  1. usernname,city,age  
  2. usernname,city  
  3. usernname  

为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都 会用到该组合索引，下面的几个SQL就会用到这个组合索引：

SQL代码
  1. SELECT * FROM mytable WHREE username="admin" AND city="乌鲁木齐"  
  2. SELECT * FROM mytable WHREE username="admin"  

而下面几个则不会用到：

SQL代码
  1. SELECT * FROM mytable WHREE age=20 AND city="乌鲁木齐"  
  2. SELECT * FROM mytable WHREE city="乌鲁木齐"  

 

（5）建立索引的时机

到这里我们已经学会了建立索引，那么我们需要在什么情况下建立索引呢？一般来说，在WHERE和JOIN中出现的列需要建立索引，但也不完全如此， 因为 MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引。例如：

SQL代码
  1. SELECT t.Name  
  2. FROM mytable t LEFT JOIN mytable m  
  3. ON t.Name=m.username WHERE m.age=20 AND m.city='乌鲁木齐'  

 

此时就需要对city和age建立索引，由于mytable表的userame也出现在了JOIN子句中，也有对它建立索引的必要。

刚才提到只有某些时候的LIKE才需建立索引。因为在以通配符%和_开头作查询时，MySQL不会使用索引。例如下句会使用索引：

SQL代码
  1. SELECT * FROM mytable WHERE username LIKE'admin%'  

而下句就不会使用：

SQL代码
  1. SELECT * FROM mytable WHEREt Name LIKE'%admin'  

 

因此，在使用LIKE时应注意以上的区别。

（6）索引的不足之处

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：

●     虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数 据，还要保存一下索引文件。

●     建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

（7）使用索引的注意事项

使用索引时，有以下一些技巧和注意事项：

●     索引不会包含有NULL值的列

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

●     使用短索引

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

●     索引列排序

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

●     like语句操作

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

●     不要在列上进行运算

SQL代码
  1. SELECT * FROM users WHERE YEAR(adddate)<2007;  

 

将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成

SQL代码
  1. SELECT * FROM users WHERE adddate< '2007-01-01';  

 

●     不使用NOT IN和<>操作

NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以使用NOT EXISTS代替，id<>3则可以使用id>3 or id<3来代替。

--EOF--

本博客内部关于MYSQL索引的相关文章有：

MYSQL索引之小小分析

 

 

 

 

 

 

 

 

 

 

 

 

 

 

［分享］mysql数据库索引查询优化的分享

联合索引的经典例子

当然，对于索引，肯定要看MYSQL中EXPLAIN的说明

Tags: mysql, index, 索引, 知识点, explain

DataBase | 评论:1 | 阅读:20870

PHPChina活动涉及PPT在线观看

Submitted by gouki on 2010, May 19, 11:39 AM

上周六的phpchina搞的活动可以通过echo谈xhprof,cache and long-polling查看，其中echo的ppt可以在线观看了，当然你也可以下载到本地。

 

Php perf
View more presentations from ericjonah.

 

不得不说的是，ppt上可以展现的内容确实有点少，如果身在上海，没有去听echo讲课，确实有点遗憾。

PPT下载在这里：web_perf.pdf

Tags: phpchina, pea, xhprof, cache, long-polling

PHP | 评论:0 | 阅读:17073

最精确硬盘分区的算法

Submitted by gouki on 2010, May 18, 10:01 AM

想知道磁盘空间有多大，其实还是有算法的，下面的就是从网络搜索而来，当然我是从http://xinsync.xju.edu.cn/index.php/archives/5137COPY过来的。仅仅作为一个资料查看一下

硬盘一般有255磁头，63扇区，故每柱面大小为：512byte x 255 x 63＝8225280bytes ＝7.84423828125 MB

如果要分40GB,那么要40×1024MB=40960MB
需要柱面数为40960÷7.84423828125=5221.66
取整数既为5222个柱面，应分M数为5222×7.84423828125=40962.6123046875MB

不管小数点后面几位都进1，也就是40963MB，windows就认为是40GB了。
这个方法NTFS和FAT32通用。

下面附10GB到200GB整10GB递增的精确计算结果：
10GB ＝ 10245MB
20GB ＝ 20482MB
30GB ＝ 30726MB
40GB ＝ 40963MB
50GB ＝ 51208MB
60GB ＝ 61444MB
70GB ＝ 71681MB
80GB ＝ 81926MB
90GB ＝ 92162MB
100GB ＝ 102407MB
110GB ＝ 112644MB
120GB ＝ 122888MB
130GB ＝ 133125MB
140GB ＝ 143362MB
150GB ＝ 153606MB
160GB ＝ 163843MB
170GB ＝ 174088MB
180GB ＝ 184324MB
190GB ＝ 194561MB

此精确分区结果，在管理工具-磁盘管理界面，和Windows资源管理器里面显示的是整数，10G就是10.00GB，20G就是 20.00GB，40G就是40.00GB。

Linux | 评论:0 | 阅读:15808

PHP 技巧：file_get_contents的超时处理

Submitted by gouki on 2010, May 17, 3:34 PM

话说，从PHP5开始，file_get_content已经支持context了（手册上写着：5.0.0 Added the context support. ），也就是说，从5.0开始，file_get_contents其实也可以POST数据，关于这个，我在通过file_get_contents来 Post数据的实例也有所介绍。

今天说的这篇是讲超时的，确实在跨服务器提交的时候，不可避免的会遇到超时的情况，这个时候怎么办？set_time_limit是没有用的，只有用context中的timeout时间来控制。相反，我们不是要抑止，而是要管理。比如在超时返回错误后，进行一次尝试，就象js中的settimeout那样，对函数重新处理。错误超过3次或者5次后，我们就确实的认为无法连接服务器而彻底放弃。这，是一个好办法，应该值得推荐使用。其实。不全是file_get_contents，只要支持context的都应该加上，避免超时浪费时间。这样可以被支持的函数大致有：fsocketopen（该函数的最后一个参数。好象比较推荐在读stream的时候，使用stream_time_out函数进行控制）,fopen（也是从PHP5开始加入context支持）,file(PHP5加入支持),curl（curl有自已的变量CURLOPT_TIMEOUT）等 。

下面开始看原文吧：http://xinsync.xju.edu.cn/index.php/archives/6840。

因为要用php去向我的虚拟主机管理系统发送开通空间等的请求，需要Post传值，由于开通空间过程很慢，同时需要延时处理。以下找到了一下 file_get_contents的超时处理，网上有人用2个方法解决：

在使用file_get_contents函数的时候，经常会出现超时的情况，在这里要通过查看一下错误提示，看看是哪种错误，比较常见的是读取超 时，这种情况大家可以通过一些方法来尽量的避免或者解决。这里就简单介绍两种：

一、增加超时的时间限制

这里需要注意：set_time_limit只是设置你的PHP程序的超时时间，而不是file_get_contents函数读取URL的超时时 间。
我一开始以为set_time_limit也能影响到file_get_contents，后来经测试，是无效的。真正的修改 file_get_contents延时可以用resource $context的timeout参数：

PHP代码
  1. $opts = array(  
  2.     'http'=>array(  
  3.         'method'=>"GET",  
  4.         'timeout'=>60,  
  5.     )  
  6. );  
  7.    
  8. $context = stream_context_create($opts);  
  9.    
  10. $html =file_get_contents('http://www.example.com', false, $context);  
  11. fpassthru($fp);  
二、一次有延时的话那就多试几次

有时候失败是因为网络等因素造成，没有解决办法，但是可以修改程序，失败时重试几次，仍然失败就放弃，因为file_get_contents()如果失 败将返回 FALSE，所以可以下面这样编写代码：
PHP代码
  1. $cnt=0;  
  2. while($cnt < 3 && ($str=@file_get_contents('http...'))===FALSE) $cnt++;  
以上方法对付超时已经OK了。那么Post呢？细心点有人发现了’method’=>”GET”, 对！是不是能设置成post呢？百度找了下相关资料，还真可以！而且有人写出了山寨版的post传值函数，如下：
PHP代码
  1. function Post($url, $post = null)  
  2. {  
  3.     $context = array();  
  4.    
  5.     if (is_array($post)) {  
  6.         ksort($post);  
  7.    
  8.         $context['http'] = array (  
  9.             'timeout'=>60,  
  10.             'method' => 'POST',  
  11.             'content' => http_build_query($post, '', '&'),  
  12.          );  
  13.     }  
  14.    
  15.     return file_get_contents($url, false, stream_context_create($context));  
  16. }  
  17.    
  18. $data = array (  
  19.     'name' => 'test',  
  20.     'email' => 'test@gmail.com',  
  21.     'submit' => 'submit',  
  22.  );  
  23.    
  24.  echo Post('http://www.example.com', $data);  

OK , 上面函数完美了，既解决了超时控制又解决了Post传值。再配合康盛的改良版RC4加密解密算法，做一个安全性很高的webservice就简单多了。

通过以上函数的组合，终于实现了PHP和我的ASP版虚拟主机管理软件的通信。.net相信也很容易了，因为已经有了.net版本的 authcode类。未来再做一个java版的就天下大同了。

--EOF--

我只能说这样的处理好是好，只是还得注意文件头的Set_time_out，否则整个文件都得超时了。呵呵

 

 

Tags: settimelimit, timeout, filegetcontents, context

PHP | 评论:0 | 阅读:30751

杯具：终于知道自己为什么会胖了

Submitted by gouki on 2010, May 17, 8:51 AM

杯具啊，终于知道自己为什么会这么胖了，以前都不是特别了解，一直以为自己是缺乏锻炼啥的，但现在我才发现，原来还不止这一个原因，还有一个重要的原因就是。。。

春节的时候，家家户户都贴点门神，我们家也不例外的买了点贴一下，开始没注意，前几天的时候突然发现，原来这就是让我胖的原因，LOOK

大小: 314.27 K 尺寸: 500 x 332 浏览: 1412 次 点击打开新窗口浏览全图

一个福，一个发，多好的词啊，可是，反过来就成了“发福”。一下子傻掉了。

Tags: 发福, 门神

Misc | 评论:2 | 阅读:18501

Records:3094«‹358359360361362363364365366367›»