Month: December 2014

看下下面这条语句的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可以接受，同时长度仍然适于直接索引。

文本字段越长，在这个字段上建索引的代价就越高。 为了解决这个问题，可以为这个字段配一个hash字段，然后在这个hash字段上建索引。 如， select * from log where url_hash= CRC32(‘http://bing.com?query=xxx&tp=xxx’ ) crc32()返回的值一般比较短，比较合适； 如果用md5或sha1做hash, 返回的值也很长，那就失去意义了。 不过，像上面这样写可能存在hash collision问题，所以应该加个原字段上的搜索条件，确保搜出来的是自己想要的： select * from log where url_hash= CRC32(‘http://bing.com?query=xxx&tp=xxx’ ) and url = ”http://bing.com?query=xxx&tp=xxx’ 另一种类似的方案是： 不用hash值，而用前缀。 这里有提到。

select … order by a a上有索引时执行上面的语句，比a上没有索引时要快。因为B-树索引中各结点已经排好了序，数据库只需要沿着树中的叶子结点链路朝左或朝右走即可。