如何更好地使用mysql索引

这篇笔记主要是记录一下mysql索引在实际应用中的一些注意点。 1.建议使用自增id作为表的主键 在之前关于innodb行格式的学习中,已经知道了数据页里面的数据是按主键递增的顺序串起来的。 如果我们建表的时候,没有使用自增列作为主键,而是使用了业务相关的列作为主键,那么在业务上会有很多插入、删除操作。如果一个数据页已经满了,此时再在中间插入一条记录的话,就需要分页。相对应的就是,如果删除了大量数据,一个页里面的数据太少的话,也会进行页的合并。页分裂和页合并都是开销很大的操作,我们需要避免。 所以,我们在实际应用中,建议使用自增id作为主键,而不是业务上的某个属性作为主键。 2.在业务中使用查询语句的时候不要select *,建议写明需要的列名 在上一篇笔记为什么innodb索引采用B+树结构里面,已经学习过索引的结构。已经知道叶子节点存放的是具体的数据,非叶子节点是索引。 那么innodb索引的结构可以简化成如下图所示: 在innodb里面,主键索引又叫聚簇索引。 我们在实际应用中,除了主键索引,还会在某个列上面建立索引,例如 CREATE TABLE `testsecondaryindex` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2`…

mysql-innodb更新varchar字段值时存储的变化

这篇笔记是innodb行格式这一篇的后续,主要是想看一下varchar类型的字段更新的时候数据页的变化。 首先还是建立一个与innodb行格式这篇笔记一样的表,只不过我改了一下表名。 CREATE TABLE `testupdate` ( `c1` int(10) unsigned NOT NULL AUTO_INCREMENT, `c2` varchar(255) DEFAULT NULL, `c3` char(11) DEFAULT NULL, `c4` varchar(255) DEFAULT NULL, PRIMARY KEY (`c1`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=ascii ROW_FORMAT=COMPACT 往里面插入了一样的数据。那么我们可以设想,将testupdate.ibd转成十六进制来查看的时候,也可以在0xC080找到第一行的内容,可以在0xC0C1找到第三行的内容,…

InnoDB的索引为什么使用B+树结构

在之前的关于innodb行格式的笔记里面,我们已经知道了,每行的头上有个next_record标志位记录的是下一个行的位置,所以行跟行之间是类似单链表的形式串起来的。 当一行记录被删除的时候,只需要改动next_record的指向,并不需要实际物理擦除,毕竟物理擦除很费时。被释放的空间,其实是被添加到了空闲空间链表中。 在innodb行格式这篇笔记以及之前的innodb逻辑存储结构这篇笔记里面了解了innodb数据页的格式,如下图所示: 从数据页的格式里面看到有个Page Directory,即页目录。在innodb逻辑存储结构这篇笔记里面提过一句,它是个稀疏目录。使用Page Directory可以用二分查找的方法快速定位到大致的位置,然后按链表查到想要的结果。 在每个数据页里面,行与行之间是单链表,同时数据页还有目录可以快速定位大致位置。而数据页和数据页之间,因为在File Header里面有FIL_PAGE_PREV和FIL_PAGE_NEXT分别指向上一个和下一个页面,所以数据页和数据页之间是双向链表。 那么我们可以大致画一下,我们要存的一行行记录在innodb里面存储是什么样子的: 大家都知道innodb的索引的数据结构是B+树。讲B+树之前,先来说一下我们最熟悉的二叉树。 我们知道二叉树这个数据结构,在完全平衡的情况下(即左右子树高度差不超过1),叫平衡二叉树,查询的时间复杂度是O(logN)。如果不够平衡,甚至退化成链表的时候,它的查询时间复杂度就变成了O(N)…

mysql InnoDB数据页行格式

之前写过两篇mysql相关的的学习笔记,分别是关于字符集和比较规则 和 InnoDB逻辑存储结构,本篇主要是记录一下InnoDB存储引擎的行格式方面的知识 行格式的几种类型: compact、redunct、compress、dynamic 在InnoDB 1.0.x版本之前,InnoDB存储引擎提供了Compact和Redunct两种格式来存放行记录数据,这两种也叫Antelope文件格式。InnoDB 1.0.x版本开始引进了Compress和Dynamic两种新的行记录格式,这两种也叫Barracuda文件格式。 使用命令show variables like "innodb_file_format";可以查看当前数据库默认使用的文件格式。 示例: mysql> show variables like "innodb_file_format"; +--------------------+----------+ | Variable_name | Value | +--------------------+----------+ | innodb_file_…

mysql InnoDB存储结构

本文主要是对InnoDB的逻辑存储结构进行学习。 mysql支持很多存储引擎,使用show engines可以查看,如下图所示 其中InnoDB是我们实际工作中最常接触到的存储引擎。 InnoDB存储引擎,数据被放在表空间(tablespace)中,表空间又分为段(segment)、区(extent)、页(page)。 默认情况下InnoDB存储引擎有一个共享表空间ibdata1,即所有的数据都放在这个表空间内。当参数innodb_file_per_table开启的时候,每个表的数据就会单独放到一个表空间内。 例如我有一个数据库mytestdb,里面有一个表user_info。那么在mysql存放数据的路径里面,就有会一个mytestdb文件夹,里面有user_info.frm和user_info.ibd两个文件。 注意:每个表的表空间里面存放的是数据、索引、插入缓冲Bitmap页,其他的数据(例如undo log、系统事务信息等)还是存放在原来的共享表空间ibdata1里面,所以即使innodb_file_per_table已开启,共享表空间ibdata1还是会变大。 表空间由各个段组成,常见的段有数据段、索引段、…