CHEN Jian's Java Blog

有的数据库支持游标。你输入一个select * from t, 拿到一个ResultSet对象，要求取10行，数据库就临时给你找出10行；再取下一个10行，数据库再给你查询出10行。 MySQL不支持这种机制。你输入select * from t, MySQL就会把所有数据都查出来再给你。在全部查完之前，根本停不下来。这就是 "limit"关键字非常重要的原因。

1.查询出了过多数据。 比如只需查某列数据，应用却要求每行都返回所有字段。 2.数据库扫描了过多数据，虽然不一定全部返回。 如：只需查一行数据，由于没有索引导致全表扫描。

一个红人发了一个帖子，每秒对这个帖子的“赞”数可能会很高。如果这些赞请求都落到数据库中的同一条记录上，会导致对同一条记录过高的临界访问（因为有锁），导致排队。 解决办法是把这1条记录拆成100条记录，更新赞数时可能会更新到其中任意一条记录上。这样并发访问的竞争就会松很多。当需要显示总赞数时，临时把100条记录的总赞数累加起来即可。 对于NoSQL数据库和关系数据库，实现这种分槽(slot)机制都不难。对于MySQL数据库，最简单的做法是： insert into post_like_counter(post_id, slot, like_count) values (100, rand() * 100, 1) on duplicate key update like_count = like_count + 1 其中slot是槽号(1-100).  <post_id, slot>组成一个唯一键。

check table a123 检查索引状态是否正常，没有重复唯一键之类的 repair table a123 修复索引。InnoDB不支持这个命令，可以通过执行一次alter table engine达到同样的目的： alter table a123 engine = INNODB analyze table a123 为优化器提供本表索引的统计数据 optimize table a123 把分散存储（fragmented）的数据和索引重新挪到一起(defragmentation)，对I/O速度有好处。 InnoDB的索引无法使用这个命令来deframent, 你也可以通过执行一次alter table engine达到同样的目的： alter table a123 engine = INNODB

对于联合索引，要注意索引字段在order by中出现的方式，否则可能会导致索引失效。下面举例说明。 下面这张表有个联合索引: <a1,a2,a3>，并且表里有100万行数据 create table a123( id bigint unsigned auto_increment not null, a1 varchar(50) not null, a2 varchar(50) not null, a3 varchar(50) not null, b1 varchar(50) not null, b2 varchar(50) not null, b3 varchar(50) not null, primary key(id), index idx_a_123(a1, a2, a3) ) explain select * from a123 order by a1 asc limit 100; …

MySQL: 联合索引在order by中的使用 Read More »

看下下面这条语句的explain plan  （user_name已被索引) select user_name from user where user_name = ‘chenjianjx’ 你会发现 Extra这一列中写的是：Using Index 它的意思执行查询时用到了Covering Index.  Covering Index的意思是指索引中直接包含了一个查询所需返回的数据。按user_name只查user_name, 所有数据都可以从user_name的索引中给出。 很明显，这对性能有帮助，因为你不需要再费几次I/O去取数据； 同时由于索引本身排好了序，体积小更容易直接存储在内存中，所以只查索引，性能上的好处是很大的。 如果查整行， select * from user where user_name = ‘chenjianjx’ Extra中就不再有"Using Index"了，因为你的索引中本身没有存储整行。 如果你的存储引擎是Innodb, 这样查呢？ select id, user_name from user where user_name = ‘chenjianjx’ — id是主键 Extra中又会有"Using Index", 这是因为在innodb中，所有secondary index中的结点中都包含了相应数据对应的主键的值。 以上结论的作用是：不要总是傻傻地返回整行( select *) . 如果你直需要索引中的数据，或者索引中的数据加上主键(只对innodb有效)，那就让你的sql只返回索引字段吧(以及主键）。

Clustered Index: 把索引和数据存储在一起。说白了，就是B-树的叶子结点上直接挂了数据。 它对性能的好处是： 1. 从存储的角度来看，索引即表。索引找到了，数据也就找到了，少做一次i/o 2. 如果索引值相邻，记录也相邻；获取一堆索引值相邻的数据时，由于存储地点相邻没有分散，I/O性能会比较高。比如说，获取 id between (3,10)的数据，可能只需要读取一个page. Innodb中总是有一个Clustered Index; 如果你定义了primary key, 那么primary key就是Clustered Index; 如果没定义，那innodb也会跟你生成一个隐式的primary key, 并使它作为Clustered Index. 在Innodb中, secondary index没有指针直接指向数据，它只会记录primary index的值。 1. 坏处：基于secondary index的查找实际上是两次查找：先按secondary index值找到primary index的值；再按primary index的值找到记录。 2. 好处：由于secondary index没有指针直接指向数据，但数据的存储位置变化时(值不变)，secondary index不需要更新。 Innodb使用Primary Index作为Clustered Index隐含了一个结论：应该先插入主键值小的记录，再插入主键值大的记录。否则，会导致插入过程中的page split和最终存储时的data fragmentation，影响插入性能和查询性能。 例如，假设1条记录占半个page; 把id=1的记录插入到page1的上半部分后，再插入id=3的记录，很自然地，系统会把它安排在page1的下半部分，以保证相邻数据放在一起；最后要插入id=2的记录时，你不能把它安排在page2,因为id=2和id=1的记录必须相邻，不能让id=3挡在中间，这时系统就会把page1拆成两个page, 以把id=2插入到id=1和id=3之间; 这三条记录也可能各占一个page, 并且有些空白的page可能间隔其中（取决于磁盘上数据移动的具体策略）。 如果严格按照id=1,id=2,id=3的顺序来，就不会有这些问题，因为数据总是插在当前最后一个存储位置的后面，不需要拆散谁。 所以说， 主键最好是auto incremented并且不可修改的，这样就可以确保数据移动不会发生；如果做不到这一点，也应该时不时地运行一下optimize table，使得已经有所分散的数据能够重新紧凑地聚合在一起。 至于具体的性能区别，High Performance MySQL做了一下benchmark，1个用自增主键，另1个用UUID，结论是插入性能相差四倍。当然，主键数据类型及长度差异也是性能差别的原因之一，但主要因素还是非顺序插入导致的数据移动。

一个索引的Selectivity是： select count(distinct 姓)/count(*) from … 即 索引字段上所有值的相异程度。相异程度越高(中国人每人1个姓)，意味着一个普通lookup查询返回的值越少(姓陈的只有1个), 性能越好； 相异程度越低（中国人只允许100个姓），则相反（姓陈记录数=有13亿/100 =130万) ，性能不会好。 知道这个又有什么用呢？ 举例来说，一个文本字段越长，Selectivity一般就越高，但同时在它上面建索引的代价也越高。 避免给长文本建索引的方法之一是给它的前缀（或其他截取子串的方式）建立索引。 问题是，前缀的长度定多少合适？ 这个就取决于前缀的Selectivity了。很短的前缀，比如只取第1个字母，那建索引肯定很快，但Selectivty也会很低，查询性能会很弱。 前缀长度要做到：Selectivity可以接受，同时长度仍然适于直接索引。