主键策略
mysql底层数据结构是 B+ 树,索引对应一棵 B+ 树,mysql 中的数据都是按顺序保存在 B+ 树上的(所以说索引本身是有序的)。
然后 mysql 在底层又是以数据页为单位来存储数据的,一个数据页大小默认为 16k,当然你也可以自定义大小,也就是说如果一个数据页存满了,mysql 就会去申请一个新的数据页来存储数据。
如果主键为自增 id 的话,mysql 在写满一个数据页的时候,直接申请另一个新的数据页接着写就可以了,无需任何的数据迁移。
但是,如果使用非自增 id,为了确保索引有序,mysql 就需要将每次插入的数据都插入到某个现有数据页的位置上。
当往一个快满或已满的数据页中插入数据时,新插入的数据会将数据页写满。因此mysql 就需要申请新的数据页,并且把上个数据页中的部分数据挪到新的数据页上,造成数据迁移。
而这就是页分裂,这个大量移动数据的过程是会严重影响插入效率的。
非自增主键,基本都是随机的,因此效率存储效率会低的多。
但是为什么我们通常使用自定义主键,譬如雪花算法呢。原因
一:多线程竞争
二:多库多表时自增主键会非常痛苦.分表分库时,必须使用不同起始步长或其他策略来保证主键在业务系统的唯一性。比如说将一种数据分为两张表放置,这两张表的共用一个逻辑主键。也就是,比如两张表都是放学生数据的,考虑到集群,需要分为两张表存放。其中一张表的主键为1、3、5····,另一张表为2、4、6···。这样子做,插入性能高,同时保证了主键的唯一性。问题就是,迁移数据库数据时会非常麻烦,因为主键是关系当前数据库的。
为什么不使用UID,而是使用雪花算法
UID长而且无序,雪花算法稍微比较短,并且有序。但是使用雪花算法,必须保证各个节点的时间戳是相同的。
Drop truncate Delete *
Drop database 删除库
Drop table 删除表,包括表结构,索引,触发器等等
truncate 删除表记录,保留表结构
truncate table命令将快速删除数据表中的所有记录,但保留数据表结构。这种快速删除与delete from 数据表的删除全部数据表记录不一样,delete命令删除的数据将存储在系统回滚段中,需要的时候,数据可以回滚恢复,而truncate命令删除的数据是不可以恢复的
1.TRUNCATE TABLE是非常快的
2.TRUNCATE之后的自增字段从头开始计数了,而DELETE的仍保留原来的最大数值
Mysql锁
Mysql自己实现了悲观锁,而乐观锁是由我们编写程序来CAS实现的。
MVCC
MVCC为每个数据行都保存了一个Undo_log,Undo_log保存的是数据行快照的链表,每个节点都持有一个TRX_ID
,是根据事务开始时系统的SYS_ID
当前数据库维护了一个ReadView
,包含是还未完成的事务。每个事务开始时,都是记录此时的TRX_ID_MIN
与TRX_ID_MAX
,后续的判断都会依据这个记录的值。
对于快照读,都是读取的快照,均不需要加锁。
TRX_ID_MIN <= TRX_ID <= TRX_ID_MAX,需要根据隔离级别再进行判断:
- 提交读:如果 TRX_ID 在 TRX_IDs 列表中,表示该数据行快照对应的事务还未提交,则该快照不可使用。否则表示已经提交,可以使用。
- 可重复读:都不可以使用。因为只有TRX_ID<TRX_ID_MIN,才能说明这个快照是在此事务开始前完成的,对于此事务开始时,其他还没完成的事务的提交结果自然是不给被此事务读到。
MVCC 其它会对数据库进行修改的操作(INSERT、UPDATE、DELETE)需要进行加锁操作,从而读取最新的数据。
在进行 SELECT 操作时,可以强制指定进行加锁操作进行当前读。以下第一个语句需要加 S 锁,第二个需要加 X 锁。
SELECT * FROM table WHERE ? lock in share mode;
SELECT * FROM table WHERE ? for update;
next-key lock
for update 仅适用于InnoDB,并且必须开启事务。
select * from gap_table where num<6 for update
如果提交读使用for update
。那么就没有gap锁,只有行锁,变为可重复读,会出现幻读。也就是对select的出来的行都加上行锁。
可重复读,不使用for update
的情况下,永远读取的都是TRX_ID
小于TRX_ID_MIN
的快照,绝对不会出现幻读。
但是可重复使用了for update
的情况下,会读取最新提交的事务快照。那就有问题出现,这样子不是会出现不可重复读吗?因此for upadte
需要加锁。同时加上行锁和gap锁,结合起来就是next-key lock
,它保证在可重复读使用for update
的情况下,不会出现幻读的情况。
可重复读for update,看看几条总结的何时加锁的规则。
唯一索引
1、精确等值检索,Next-Key Locks就退化为行锁,不会加gap锁
2、范围检索,会锁住where条件中相应的范围,范围中的记录以及间隙,换言之就是加上行锁和gap 锁。
非唯一索引
1、精确等值检索,Next-Key Locks会对相同值的节点间隙加gap锁,以及对应检索到的行加行锁。
2、范围检索,会锁住where条件中相应的范围,范围中的行以及间隙,换言之就是加上行锁和gap 锁。
非索引检索,全表间隙gap lock,全表记录record lock
索引
全文索引
MyISAM 存储引擎支持全文索引,用于查找文本中的关键词,而不是直接比较是否相等。查找条件使用 MATCH AGAINST,而不是普通的 WHERE。InnoDB 存储引擎在 MySQL 5.6.4 版本中也开始支持全文索引。
全文索引使用倒排索引实现,它记录着关键词到其所在文档的映射。
英文原名Inverted index,大概因为 Invert 有颠倒的意思,就被翻译成了倒排。
但是倒排这个名称很容易让人理解为从A-Z颠倒成Z-A。个人认为翻译成转置索引可能比较合适。
一个未经处理的数据库中,一般是以文档ID作为索引,以文档内容作为记录。
而Inverted index 指的是将单词或记录作为索引,将文档ID作为记录,这样便可以方便地通过单词或记录查找到其所在的文档。
聚合索引
最左匹配原则
表结构,有三个字段,分别是id,name,cid
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`cid` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `name_cid_INX` (`name`,`cid`),
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8‘’
索引方面:id
是主键,(name,cid)
是一个多列索引。
两个查询: