膘叔　　簡單人生简单人生|PHP开发|架构研究

浏览模式: 标准 | 列表分类:DataBase

MYSQL索引之小小分析

Submitted by gouki on 2008, July 31, 9:23 PM

MYSQL中，如果表是MYISAM，除了主键索引外，基本上常用的就是默认的索引，而unique index应该是比较少用到的。外键索引在MYSQL4.0以下直接忽略。

查了一下资料，好象默认索引应该是称之为B*Tree索引

B*Tree索引

B*Tree索引是最常见的索引结构，默认建立的索引就是这种类型的索引。B*Tree索引在检索高基数数据列（高基数数据列是指该列有很多不同的值）时提供了最好的性能。当取出的行数占总行数比例较小时B-Tree索引比全表检索提供了更有效的方法。但当检查的范围超过表的10%时就不能提高取回数据的性能。B-Tree索引是基于二叉树的，由分支块（branch block）和叶块（leaf block）组成。在树结构中，位于最底层底块被称为叶块，包含每个被索引列的值和行所对应的rowid。在叶节点的上面是分支块，用来导航结构，包含了索引列（关键字）范围和另一索引块的地址，如图26-1所示。
假设我们要找索引中值为80的行，从索引树的最上层入口开始，定位到大于等于50，然后往左找，找到第2个分支块，定位为75－100，最后再定位到叶块上，找到80所对应的rowid，然后根据rowid去读取数据块获取数据。如果查询条件是范围选择的，比如where column >20 and column <80，那么会先定位到第一个包含20的叶块，然后横向查找其他的叶块，直到找到包含80的块为止，不用每次都从入口进去再重新定位。

平时在MYSQL的查询中，我们也是建议，筛选条件高的、返回结果集比较整齐的，尽量放在条件的第一个，依稀记得WHERE条件中，如果是AND那么是从前往后比较，如果是OR条件，那么是从后往前比较。（记不清了。。。）

刚才又看到文章，说在使用B*Tree索引的时候千万要注意：Btree索引不存储NULL值，而在order by desc中，NULL值总是最大的，sql语句通过索引无法判断表是否存在NULL，执行计划还是走全表扫描[详见：http://rdc.taobao.com/blog/dba/html/67_oracle_fenye.html]（当然，这也只是一个参考）

如果是确实这样，那么如果该字段可能会需要用到排序的时候，请尽量不要使用default NULL，而且在处理where的时候，NULL是不会被显示的。除非是条件是is NULL。

刚才测试了一下，设定了某字段允许NULL，然后插入数据，并为该字段做了索引，结果在where fields < 100 的时候，使用了该索引（当然NULL值是不显示的），但我取where fields > 0的时候，扫描方式立刻变为了全表，而且没有使用fields索引（同样不显示含 NULL值的行），where fields > 1 的时候，同样扫描全表。直到我设为fields > 2的时候，又开始使用了索引，看来，MYSQL对于索引字段做查询条件的时候还是需要斟酌的，条件不能随便乱加。

其实在程序中，如果使用INT字段（数值型字段），请尽量不要使用NULL，否则确实有可能会出现值查不到的情况。

然而，在我现在的项目中却有这种情况，要求我们把该字段设为NULL，明天我要再检查一下代码，以防止有漏掉的数据。

不看不知道一看吓一跳呀

Tags: mysql, 索引, index

DataBase | 评论:2 | 阅读:2751

惊心动魄的SQL BUG

Submitted by gouki on 2008, July 30, 8:42 PM

刚才打开google reader的时候，突然看到一篇文章，让俺大吃一惊呀。详细内容如下：

原文网址：http://www.cnblogs.com/xinerzhui/archive/2008/07/30/1256648.html

请谨慎注意这一微软SQLBug
刚来博客园，有什么不对，需要改进的地方，欢迎各位同道指出来，谢谢。
今天在使用数据库时出现意外操作,将一张表的数据删除了。仔细查看SQL语句，发现问题。将问题类推到Northwind中。我们使用两个表,Employees和Products
SQL语句如下:
select * from Products where CategoryID in (select CategoryID from Employees)
不看表结构是看不出什么问题的，不清楚的看一下表结构。执行上面的SQL语句,你将能看到所有的产品记录。
当你执行上面语句的"select CategoryID from Employees",将会提示一个错误提示:
消息 207，级别 16，状态 3，第 1 行
列名 'CategoryID' 无效。
表Employees中根本就没有CategoryID列,而微软忽略了括号中的错误,执行前面的语句。如果是应用在删除，更新，那后果将是不可现象的。我曾为此付出了代价。
这个应该就是微软SQL的BUG吧,我们需要特别注意。
在SQL2000企业管理器,SQL2000的查询分析器,SQL2005的Management Studio Express中均有此Bug。