如何更好地使用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找到第三行的内容,…

mysql事务的四种隔离级别

这篇笔记主要对mysql事务进行一些学习。 业务里一般当进行转账等操作的时候,会用到mysql事务。 mysql事务有ACID特性: Atomicity,原子性。即事务里的操作要么都成功,要么都失败 Consistency,一致性。数据库总是从一个一致性的状态转换到另外一个一致性的状态。 Isolation,隔离性。一个事务所做的修改在最终提交前,对其他事务是不可见的。 Durability,持久性。一旦事务提交,则其所做的修改就会永久保存到数据库中。 mysql事务有四种隔离级别: serializable 可串行化。强制让事务串行执行,serializable会在读取的每一行数据上都加上锁,所以可能导致大量的超时和锁竞争的问题。 当出现读写锁冲突的时候,后访问的事务必须等前一个事务执行完成,才能继续访问。 这个隔离级别隔离度最高,但是性能最低。实际应用中很少使用到这个隔离级别,只有在非常需要确保数据一致性而且可以接受没有大并发的情况下,才考虑采用该级别。 repeatable read 可重复读。一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一致的。 这个隔离级别有幻读问题。幻读是指,当某个事务读取某范围的记录时,另一个事务又在该范围插入了新的记录,当前事务再次读取该范围的记录时,会产生幻行。 read committed 读已提交。一个事务提交之后,它做的变更才会被其他事务看到。 这个隔离级别多了个不可重复读问题。…

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还是会变大。 表空间由各个段组成,常见的段有数据段、索引段、…

mysql字符集

这边笔记主要记录一下对mysql字符集相关知识的学习 字符集 一般我们使用如下语句创建一张表,创建表的时候指定了charset为utf8编码。 CREATE TABLE `test` ( `c1` int UNSIGNED NOT NULL AUTO_INCREMENT, `c2` varchar(255), `c3` char(11), `c4` varchar(255), PRIMARY KEY (`c1`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; 但其实mysql也支持除了utf8之外的其他字符集,可以使用命令show charset查看当前mysql支持的所有字符集。 例如下图就是我的mysql支持的所有字符集。 可以从上图中看到,第四列表示该字符集最大长度。我们建表时常用的utf8在mysql里面实际最大长度是3个字符。一般认知中utf8是用1~4个字符来表示具体的内容的,但mysql里面的utf8实际是utf8mb3的别名,正宗的的最大用4个字符表示的在mysql中是utf8mb4。 注意: 如果要存emoji,那么应该要用utf8mb4字符集 mysql8默认使用的字符集是utf8mb4 比较规则 在上面那张图中,第三列Default collation就是这些字符集默认的比较规则。…

mysql语句插入含单引号或者反斜杠的值

比如说有个表,它的结构是这个样子的 CREATE TABLE `activity` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', `title` varchar(255) NOT NULL COMMENT '活动标题', PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='活动表'; 比如说往里面插入记录,示例代码如下: $servername = "xxxxservername"; $port = 3306; $username = "xxxusername"; $password = "xxxpwd"; $dbname = "xxxxxxdb&…