10

MySQL里建立索引应该考虑数据库引擎的类型

如果我们的数据库引擎是InnoDB,那么建立”category_id, created“复合索引是最佳答案。让我们看看InnoDB的索引结构,在InnoDB里,索引结构有一个特殊的地方:非主键索引在其BTree的叶节点上会额外保存对应主键的值,这样做一个最直接的好处就是Covering Index,不用再到数据文件里去取id的值,可以直接在索引里得到它。

如果我们的数据库引擎是MyISAM,那么建立”category_id, created”复合索引就不是最佳答案。因为MyISAM的索引结构里,非主键索引并没有额外保存对应主键的值,此时如果想利用上Covering Index,应该建立”category_id, created, id”复合索引。

继续阅读全文

08

MySQL之Prefix Index

细节决定结果!可惜人们往往无视细节的存在。很多人在给表加索引的时候,喜欢直接使用PHPMyAdmin操作,因为这样做操作简单,甚至不用记ALTER TABLE语法,用鼠标点一下就OK了,但是这样做的一个缺点是让使用者忽视了“索引长度”的存在,使用数据的完整长度去建立索引,这本身并没有什么逻辑错误,但是很多时候,我们并不需要使用数据的完整长度去建立索引,比如说有两个老王,一个是北京回龙观的老王,一个是上海徐家汇的老王。如果你按照数据的完整长度去建立索引以便定位老王的话,那么就要完整的索引北京回龙观和上海徐家汇这样的信息,但实际上在本例中回龙观,徐家汇这些地址信息是不必要的,只要提供一下城市信息是北京还是上海,就能定位要找的老王了,这也正是Prefix Index的含义所在,它的意义在于会大大缩减索引文件的大小,从而加快数据检索的速度。

继续阅读全文

十二 25

Mysql 占用CPU 100%

服务器上MYSQL进程 CPU占用快100%, 这问题有半个月了,一直都不去搞他,呵其实也不知从哪下手,直到这两天CPU直升100%不掉了,才狂晕,论坛打开个贴上分钟,论坛不稳定在我工作范围!逼上梁山,硬着头皮搞,百度一下,搜了半天听说是多半是索引没设好占用CPU 飞快

继续阅读全文

十一 18

MySQL MyIsam 存储引擎索引长度限制测试记录

MySQL MyIsam 存储引擎在创建索引的时候,索引键长度是有一个较为严格的长度限制的,所有索引键最大长度总和不能超过1000,而且不是实际数据长度的总和,而是索引键字段定义长度的总和。下面做个简单的测试,记录一下。

继续阅读全文

十一 18

Innodb 索引结构了解 – Innodb Index Structure

每个Innodb表的数据其实可以说就是以一个树型(B-Tree)结构存储的,表的数据和主键(Primary Key)共同组成了一个索引结构,也就是我们常说的Innodb的Clustered Primary Key。在这个Clustered Primary Key中,Leaf Nodes其实就是实际的表记录,我们常规理解上的索引信息全部在Branch Nodes上面。

继续阅读全文

十一 02

MySQL排序原理分析

以上两种方法,一种是老方法,另外一种是改进的方法。两种方法各有自己的优缺点。第一种方法,对sort_buffer_size的占用主要是排序的字段以及一个row pointer,这种方式对排序内存的占用较少,可以有效的减少磁盘排序,但坏处也相当的明显,要对表进行两次扫描,第二次是要根据排好序的字段的row pointer指针回表取数据,这种方式,随着排序行数的加大,对时间的消耗将会变得无法忍受(类似于oracle的nested loop)。老方法受到两个参数的影响:sort buffer size以及read_rnd_buffer_size。

如果采用改进型方法,这种新方法,只会扫描表一次,由于在一次扫描表时,取出了查询所有需要的字段,不仅包括排序字段,全都要放在排序内存(sort buffer)里,对排序内存的需求量比较大一些,如果是对少量结果集进行排序,这种新方法与老方法的性能差异并不会太明显(此前提是在并发量较低的情况下,如果这样的排序的SQL执行频率较高,那么新方法相对老方法,优势也比较明显),因为老方法在经过第一次数据扫描后,需要排序的少量的行都在内存里,第二次也只是到内存里取,这个速度相对来说,还是比较快的。如果是大量行的排序,新方法的优势便会体现出来。

对于少量行的排序,这个工作,还是交给应用层来做最合适,也多不了几行代码。

继续阅读全文