Submitted by gouki on 2008, August 12, 1:28 PM
问题描述：
我们要访问的表是一个非常大的表，四千万条记录，id是主键，program_id上建了索引。
执行一条SQL:
select * from program_access_log where program_id between 1 and 4000
这条SQL非常慢。
我们原以为处理记录太多的原因，所以加了id限制，一次只读五十万条记录
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 4000
但是这条SQL仍然很慢，速度比上面一条几乎没有提升。
Mysql处理50万条记录的表，条件字段还建了索引，这条语句应该是瞬间完成的。
问题分析：
这张表大约容量30G，数据库服务器内存16G，无法一次载入。就是这个造成了问题。
这条SQL有两个条件，ID一到五十万和Program_id一到四千，因为program_id范围小得多，mysql选择它做为主要索引。
先通过索引文件找出了所有program_id在1到4000范围里所有的id，这个过程非常快。
接下来要通过这些id找出表里的记录，由于这些id是离散的，所以mysql对这个表的访问不是顺序读取。
而这个表又非常大，无法一次装入内存，所以每访问一条记录mysql都要重新在磁盘上定位并把附近的记录都载入内存，大量的IO操作导致了速度的下降。
问题解决方案：
1. 以program_id为条件对表进行分区
2. 分表处理，每张表的大小不超过内存的大小
然而，服务器用的是mysql5.0，不支持分区，而且这个表是公共表，无法在不影响其它项目的条件下修改表的结构。
所以我们采取了第三种办法：
select * from program_access_log where id between 1 and 500000 and program_id between 1 and 15000000
现在program_id的范围远大于id的范围，id被当做主要索引进行查找，由于id是主键，所以查找的是连续50万条记录，速度和访问一个50万条记录的表基本一样
总结：
这是一个在千万笔记录表中由于使用了索引导致了数据查找变慢的问题，有一定的典型性和大家交流下！
————END————
索引这个东西很害人的。好的时候很好。。。用的不好。那真的是害己害人啊。
本来来自：PHP5研究室，责编：抽烟的蚊子，网址：http://www.phpv.net/html/1624.html
Tags: mysql, database, 索引, 优化, 技巧
DataBase | 评论:0
| 阅读:24177
Submitted by gouki on 2008, August 11, 2:35 PM
Sun日前已经确认最新版的 MySQL 5.1的所有重大漏洞已经被修复,一切都准备就绪,翘首以盼的开源用户可以期待它能够在未来的几周之内公开发布.Sun公司的数据库产品部门副主席 Zack Urlocker表示,Sun的开发人员已经在很多方面对MySQL 5.1进行了反复检查和测试,确保公开发行的正式版毫无问题.
Sun的官员早在四月份的MySQL大会上就曾经放出消息说,这个新版的MySQL数据库会在会议结束的几周之内与大家见面.不过,为了找出 MySQL 5.1的重大漏洞并将其修复所花费的时间远远超出了Sun的预算,所以MySQL 5.1正式版的公开发布时间不得不一拖再拖.
市场调查机构451集团的分析师Matt Aslett表示,数周前MySQL的创始人Monty Widenius邀请MySQL的开发人员帮助公司决定是否该公开发布MySQL 5.1.很显然,该公司对之前5.0的公开发布以及5.1候选版发布时因为不够审慎所犯下的错误仍然心有余悸,以至于现在很担心会不会第三次犯错. MySQL认为有必要公开咨询发布事宜的事实表明,公司对于漏洞报告过程信心不足,不然就是过于谨慎了.
Urlocker称,Sun承诺,在给该产品贴上公开发行的标签并正式推荐客户将其用在生产环境前,必须确保产品是真的没有问题可以投放市场才行.这也是 Sun为什么在产品发布候选版(RC)阶段找人来帮忙的原因.同时,Urlocker也表示,在开发人员的努力下,目前已经把客户曾经上报没有修复的优先 级为一和二的已知漏洞都修复了.
Forrester市场调查机构的分析师Noel Yuhanna则认为,如果MySQL要扩展其现有功能,以便为高性能的企业级环境提供支持,那么在这条提升的道路上必定会遇到困难,而Sun的延迟发布 就是最有力的证明.MySQL 5.1的研发大概耗时了三年时间,Sun对MySQL的项目一直都雄心勃勃,为此增加了大量企业级功能,以改善性能,例如基于行的复制等特性.这个功能可 以在主服务器和从服务器之间复制数据的变化,而不是实际的SQL语句.为了提高灵活性,MySQL团队还添加了混合复制功能,可以根据单个的SQL操作来 选择使用语句复制或行复制.其他的增强功能还包括支持五种不同的数据分区形式——hash分区、list分区、key分区、range分区和sub- partitioning分区,据称这项功能可以帮助客户处理超大型的数据集.考虑到这些功能都非常复杂,确实需要在研发和测试过程中投入相当大的力度, 特别是如果想让这款软件能够支持大型复杂的生产环境的话.
News from cnbeta.com
Tags: database, mysql, release
DataBase | 评论:0
| 阅读:19334
Submitted by gouki on 2008, August 6, 11:08 PM
经常在使用MYSQL，那么总是有一些小技巧的，我在这里稍稍介绍一点，这也是日常工作中常用到的。
1、尽量在SQL中对字段使用函数（条件部分）
在WHERE条件中，对字段使用函数的时候，往往就自动放弃了该字段的索引值了。举例：
SQL代码
- SELECT * FROM order WHERE YEAR(OrderDate)<2001;
-
- SELECT * FROM order WHERE OrderDate<"2001-01-01";
第二句会比第一句快很多，数据少可能看不出，数据量超过50W以后将非常明显。
2、尽量在SQL的条件中先取值再比较
SQL代码
- SELECT * FROM inventory WHERE Amount/7<24;
-
- SELECT * FROM inventory WHERE Amount<24*7;
虽然取回来的值都一样，但是。。。第一句是拿字段中的值先进行计算，再比较大小，而第二句却仅仅比较大小，谁快谁慢，一眼便知。
3、在搜索字符型字段时，我们有时会使用 LIKE 关键字和通配符，这种做法虽然简单，但却也是以牺牲系统性能为代价的。（这个我自己没有测过，不能乱讲）。。。
例如下面的查询将会比较表中的每一条记录。
SQL代码
- SELECT * FROM books WHERE name like "MySQL%"
但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
SQL代码
- SELECT * FROM books WHERE name>="MySQL"and name<"MySQM"
4、应该注意避免在查询中让MySQL进行自动类型转换，因为转换过程也会使索引变得不起作用。
5、为字段选用最恰当的属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的 查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。例如，在定义邮政编码这个字段时，如果将其设置为 CHAR(255),显然给数据库增加了不必要的空间，甚至使用VARCHAR这种类型也是多余的，因为CHAR(6)就可以很好的完成任务了。同样的， 如果可以的话，我们应该使用MEDIUMINT而不是BIGIN来定义整型字段。
另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。
对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。这样，我们又可以提高数据库的性能。
6、使用命令复制表时，请先禁止源表中的索引，因为在COPY的过程中，MYSQL会同样复制索引，而不是复制数据后重建索引，因此建议COPY前先禁用，然后COPY完后，为目标表添加相应的索引。
Tags: mysql, 技巧
DataBase | 评论:0
| 阅读:20119
Submitted by gouki on 2008, August 2, 8:07 PM
单位用的数据库一直是MYSQL4，这有历史原因，这里就不讨论了。虽然一直想升为MYSQL5，但谁也不能预期升上来之后会有什么问题。也就导致一直不敢升级，但随着数据库的压力越来越大，以及新版本的功能越来越有意义的时候，升级这种事情就被提上了日程，正好看到这篇文章，估计以后可能会遇到同样的问题，因此先记下来，同时也可以给其他朋友一个解决方法。
原文作者：andot，网址：http://www.coolcode.cn/show-155-1.html
» 阅读全文
Tags: mysql, database, blob, insert
DataBase | 评论:0
| 阅读:33278
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 | 评论:3
| 阅读:33381
