所在的位置: mysql >> mysql优势 >> 说说mysql索引和事务

说说mysql索引和事务

北京湿疹最好医院 http://m.39.net/pf/a_8814564.html

mysql是市面上使用最广的免费的关系型数据库,网上关于它的文档多如牛毛,内容也是参差不齐,这里关于mysql的索引和事务总结几个容易混淆的知识点,这里主要讨论的是innodb引擎,其他存储引擎暂不做讨论。

索引

B树和B+树

众所周知,innodb的索引结构为B+树结构,那么为什么要采用B+树的结构呢,一句话总结就是索引树尽可能多的存贮节点,从而降低索引树的高度,从而降低IO次数,提高查询效率。下面看一下B树和B+树的图:

B树

B+树

由上图就可以看出,由于B树每个节点除了存储索引值以外还会存储数据,且绝大部分情况下数据占据的空间比索引大很多,单个节点的总数据量比较大;而B+树除了叶子节点外,非叶子节点只存储索引值和下一节点的地址,单个节点的总数据量很小,而计算机内存页大小固定,所以计算机在单次加载索引树的时候,B+树比B树能加载更多的节点,所以B+树比B树矮的多,计算机IO次数也就少很多(每多一层就多一次IO,索引树遍历大部分时间都花在IO上),所以采用B+树的结构能有效提高查找效率,同时叶子结点与叶子结点之间又有指针相连,让排序变得更加简单和容易。

此处附上B+树高度计算案例:

假设:

表的记录数是N。

每一个B树节点平均有B个索引KEY。

B+树索引树的高度计算公式:logNB(等价于logN/logB)。

由于索引树每个节点的大小固定,所以索引KEY越小,B值就越大,那么每个B树节点上可以保存更多的索引KEY,也就是B值越大,索引树的高度就越小,那么基于索引的查询的性能就越高。所以相同表记录数的情况下,索引KEY越小,索引树的高度就越小。

现在我们假设表W条记录(因为2^25=),如果每个节点保存64个索引KEY,那么索引的高度就是(log2^25)/log64≈25/6≈4.17。

通过上面的计算可知,要计一张表索引树的高度,只需要知道一个节点有多大,从而就能知道每个节点能存储多少个索引KEY。现代数据库经过不断的探索和优化,并结合磁盘的预读特点,每个索引节点一般都是操作系统页的整数倍,操作系统页可通过命令得到该值得大小,且一般是,即4k。而InnoDB的pageSize可以通过命令得到,默认值是16k。

以BIGINT为例,存储大小为8个字节。INT存储大小为4个字节(32位)。索引树上每个节点除了存储KEY,还需要存储指针。所以每个节点保存的KEY的数量为pagesize/(keysize+pointsize)(如果是B-TREE索引结构,则是pagesize/(keysize+datasize+pointsize))。

假设平均指针大小是4个字节,那么索引树的每个节点可以存储16k/((8+4)*8)≈。那么:一个拥有w数据,且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log≈25/7.4≈3.38。

假设平均指针大小是8个字节,那么索引树的每个节点可以存储16k/((8+8)*8)≈。那么:一个拥有w数据,且主键是BIGINT类型的表的主键索引树的高度就是(log2^25)/log≈25/7≈3.57。

由上面的计算可知:一个千万量级,且存储引擎是MyISAM或者InnoDB的表,其索引树的高度在3~5之间。

索引类型

1.聚簇索引和非聚簇索引

聚簇索引就是按照每张表的主键构造一颗B+树,同时叶子节点中存放的就是整张表的行记录数据,也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分,每张表只能拥有一个聚簇索引。

在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找。辅助索引叶子节点存储的不再是行的物理位置,而是主键值。通过辅助索引首先找到的是主键值,再通过主键值找到数据行的数据页,再通过数据页中的PageDirectory找到数据行。

2.覆盖索引

覆盖索引其实不算一种索引类型,只是从功能层面上看有“覆盖”或者说“命中”的功能,当能通过检索索引就可以读取想要的数据,那就不需要再到数据表中读取行了。如果一个索引包含了(或覆盖了)满足查询语句中字段与条件的数据就叫做覆盖索引。我们添加的索引一般都是覆盖索引(不做长度限制)。

3.联合索引

联合索引是指对表上的多个列进行索引,联合索引也是一棵B+树,不同的是联合索引的键值数量不是1,而是大于等于2。联合索引遵循最左匹配原则,所以建立联合索引时确定字段的顺序很重要,联合索引的最左前缀匹配指的是where条件一定要有联合索引的第一个字段,是否走联合索引与where条件的顺序无关,只与字段有关,groupby/orderby也遵循最左原则。

建索引注意事项

1.索引不会包含有NULL值的列。只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

2.当表的数据量很小时,可以不建索引,因为此时mysql分析器会认定走全表扫描的效率更高

3.当一个列的值区分度不好时,例如性别、状态等,在此字段上建立索引对查询提升效率不高,此时可以根据具体业务考虑建联合索引

4.使用短索引。根据前面的索引树的计算可以得知,索引越短,计算机单次IO的节点数越多,就能降低索引树的高度

5.单个表不是索引越多越好。浪费空间的同时也会影响数据的增删改的效率

使用索引的注意事项

常见的使用索引的注意事项这里就不说了,这里列举几个开发中不太容易发现的问题:

1.数据类型不对导致无法命中索引。例如:select*fromtable_xxxwhereidin(1,’2’),这种sql一般手写原生sql不会出现这种情况,但是一些弱类型语言在进行数据合并然后再进行mysql查询时可能就会发生这种情况,但是mysql5.7及以上版本优化器可以处理这种问题,但是应该主动避免。

2.In(1,2....)查询可能导致索引退化甚至无法命中索引,这个主要取决于in后面数据量的大小,当数据量很小时可以命中索引,当数据量很大时,无法命中索引,mysql会走全表扫描,所以写这类sql对业务类型的了解很重要。

3.当进行分页查询时,mysql会把offset之前的数据全部查出来然后再丢弃,所以分页页数越大,查询越慢,主要是查询了很多无用的数据,这个时候可以采用延迟关联的方法解决这个问题。例如:select*fromtable_xxxwheretimexxxxlimit,20会很慢,改成select*fromtable_xxxajoin(selectidfromtable_xxxwheretimexxxxlimit,20)bonb.id=a.id速度会提高很多。

事务

mysql常见的四种事务隔离级别,即:

1.读未提交(read-un


转载请注明:http://www.aierlanlan.com/grrz/1993.html

  • 上一篇文章:
  •   
  • 下一篇文章: 没有了