mysql随笔
15.22 InnoDB Limits
This section describes limits for InnoDB tables, indexes, tablespaces, and other aspects of the InnoDB storage engine.
A table can contain a maximum of 1017 columns. Virtual generated columns are included in this limit.
A table can contain a maximum of 64 secondary indexes
1.一张表最多可以有多少个字段?
InnoDB引擎最多有1017列。
2.InnoDB一张表最多有多少个索引?
官方文档给的最多有64个二级索引,再加1个主键索引 ,所以一张表最多有65个索引。
找了一些网上的资料,有很多说16个。
我亲自验证了一下,数据库版本Mysql 5.6.44,建了17个索引没有报错,所以16个是错误答案。
但是单个索引最多只能索引16列,超过这个值报错Too many key parts specified; max 16 parts allowed
3.聚簇索引和非聚簇索引
InnoDB使用的聚簇索引,索引和数据存在一起。主键索引的叶子节点存储数据,辅助索引的叶子节点指向主键。
若使用"where id = 1"这样的条件查找主键,则按照B+树的检索算法即可查找到对应的叶节点,之后获得行数据。
若对Name列进行条件搜索,则需要两个步骤:第一步在辅助索引B+树中检索Name,到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作,最终到达叶子节点即可获取整行数据
MyISAM使用非聚簇索引,索引文件和数据文件分开存储。
4.InnoDB索引类型?
Hash索引与B+树索引,B+树索引为默认索引。
Hash索引使用散列算法计算索引列的hashcode,然后在该位置存储指向数据行的地址,优点是访问速度非常快,但因为是散列分布的,所以hash索引不支持范围查找和排序功能。
5.为什么InnoDB索引使用B+树,而不是B树或者红黑树?
InnoDB是聚集索引,索引是和数据一起存在磁盘的,为了更快速的响应查询,IO次数要尽可能少。InnoDB读取磁盘的单位是Page,常见的的Page有好几种,而数据Page就是树的节点,一次查询树的层数越高,IO次数就越多,所以这颗树需要层数少,矮胖的结构,这里就排除了红黑树等层数多的树形结构。
单个page的大小是16KB,因为B+树非叶子节点只存索引不存数据(这是B+树和B树的不同点),所以一个节点能存更多的索引,同样的树形结构下B+树能比B树索引更多的内容,这是其一。B+树所有叶子节点按顺序用指针连在了一起,对于范围查询,B+树只需要索引到第一个值,剩下的顺序遍历就行了,而B树需要中序遍历挨个查找,效率更低,这是其二。
6.InnoDB与MyISAM的区别
(1)InnoDB支持事务,MyISAM不支持
(2)MyISAM锁粒度是表级,InnoDB支持行级锁
(3)MyISAM支持全文索引,InnoDB不支持
7.Mysql悲观锁
指对数据被外界修改持保守态度,因此,在整个数据处理过程中,将数据处于锁定状态。
主要使用场景有对状态的先查询后修改操作。
例如,id为1的商品goods,状态status 1是存在,2是已下单。在商品被下单前需要存在。
(1)普通逻辑是
select status from goods where id =1
update user set status =2 where id = 1
这种情况线性访问不会有问题,但是在分布式系统并行时会出现一个商品被多次下单的情况。
(2)悲观锁的处理(事务设置改为手动提交)
begin;
select status from goods where id =1 for update;
update user set status =2 where id = 1;
commit;
普通的select不会阻塞,但是select ... for update会相互阻塞。
(3)行锁还是表锁
mysql InnoDB默认使用行锁,条件是需要明确指定主键。不明确指定主键时会使用表锁,锁住整张表严重影响性能。
当明确指定的主键不存在时不会加锁。
8.Mysql乐观锁
相对悲观锁而言,乐观锁假设认为数据一般情况下不会造成冲突,所以在数据进行提交更新的时候,才会正式对数据的冲突与否进行检测,如果发现冲突了,则让返回用户错误的信息,让用户决定如何去做。
常见的实现方式是在数据表中加一个版本或者时间戳字段,更新前先读,更新时与之前的版本号比较,若没变就更新,否则就不更新。
(1)普通逻辑是
select status from goods where id =1
update user set status =2 where id = 1
(2)乐观锁的处理
select version from goods where id =1;
update user set status =2,version=version+1, where id = 1 and version=#{version};
这样当其他应用实例将状态变更了,本次更新就不会生效。
9.乐观锁和悲观锁的应用场景
乐观锁不依赖数据库锁机制,不会有死锁的现象,如果业务需要快速响应,冲突少,重试代价小可以使用乐观锁
悲观锁依赖数据库锁机制,冲突多,重试代价大的业务使用悲观锁。