Java面试题总结10之MySQL索引和锁
索引的基本原理
把无需的数据变成有序的查询
1，把创建了索引的列的内容进行排序
2，对排序结果生成倒排表
3，到倒排表内容上拼上数据地址链
4，在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
mysql聚簇和非聚簇索引的区别
都是B+树的数据结构
聚簇索引：将数据存储与索引放到了一块，并且时按照一定的顺序组织的，找到索引就是找到数
据，数据的物理存放顺序与索引顺序时一致的，即：索引是相邻的，那么对应的数据一定的也是相
邻地放在磁盘上的
非聚簇索引：叶子节点不存储数据，存储的是数据行地址，也就是说根据索引查找到数据行的位置
再取磁盘查找数据，类似一本书的目录，比如要查找第一章第一节，那么我们先在这个目录里面
找，找到对于的页码再去对应的页码查看文章
InnoDB中一定有主键，主键一定是聚簇索引，不手动设置则会使用unique索引，没有unique索
引，则会使用数据库内部的一个行的隐藏id来当作主键索引，在聚簇索引之上创建的索引称之为辅
助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引，前缀索
引，唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值
MyISAM使用的是非聚簇索引，没有聚簇索引，非聚簇索引的两颗B+树看上去没什么不同，节点的
结构完全一致，只是存储的内容不同，主键索引B+树的节点存储了主键，辅助索引B+树存储了辅
助建，表数据存储在独立的地方，这两颗B+树的叶子节点都是用一个地址只想真正的表数据，对
于表数据来说，这两个键没有任何区别，由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
但是涉及到大数据量的排序，全表扫描，count之类的操作，还是MyISAM占优，因为索引所占空
间小，因为这些操作是需要在内存中完成的。
MySQL索引的数据结构，各自的优劣
简介：
索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引为Hash索引，B+树索引等，InnoDB存储引擎的默认实现为B+树索引，对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大部分需求为单挑记录查询的时候，可以选择哈希索引，查询性能最快，其余大部分场景，建议选择BTree索引
B+树：
B+树是一个平衡的多叉树，从根节点到每个叶子节点的高度差值不超过一，而且同层级的节点间有指针相互链接，在B+ 树上的常规检索，从根节点到叶子节点的搜索效率基本相当，不会出现大幅波动，而且基于索引的顺序扫描时，也可以利用双向指针快速左右移动了，效率非常高，B+树索引也被广泛应用于数据库，文件系统
哈希索引：
采取一定的哈希算法，把键值换算成新的哈希值，检索式不需要类似B+树从根节点到叶子节点
逐级查找，只需要一次哈希算法就可立刻定位到相应的位置，速度很快，在等值查询上，哈希索引
有绝对优势，经过一次算法就能找到对应的键值，前提键值唯一，不唯一就得查找键所在地址，然后根据链表往后扫描，直到找到对应的数据，
不支持范围查询搜索，因为有序的键值经过哈希之后，就会变得不连续，不能再利用索引进行范围查询检索，
也没办法利用索引完成排序，以及部分模糊查询（like 'xxx%'）
不支持多列联合索引的最左匹配机制
大量重复键值情况下，哈希效率极低，会存在哈希碰撞
索引设计的原则
查询更快，占用空间更小，等主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立索引。
1、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
2、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如
一张表总共100万行记录，其中有个性别字段，
其值不是男就是女，那么该字段的基数就是2。
3、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tiny int之类的。
4、where与order by冲突时优先where
5、基于慢sql查询做优化
6、更新频繁的不适合做索引
7、定义有外键的数据列一定建立索引
MySQL锁的分类
基于锁的属性分类：共享锁，排他锁
基于锁的粒度分类：行级锁，表级锁，页级锁，记录锁，间隙锁，临键锁
基于锁的状态分类：意向共享锁，意向排它锁
共享锁(Share Lock)
又称读锁，S锁，一个事务为数据加上读锁之后，其他事务之对该数据加读锁，不能加写锁直到所
有读锁释放才能进行加持写锁，它的特性就是为了支持并发的读取数据，读取数据的时候不支持修改，避免重复读
排他锁(ExClusive Lock)
又称写锁，X锁，一个事务为数据加上写锁时，其他请求就不能为数据加任何锁，直到该锁释放之
后，其他事务才能对数据加锁，它的目的就是在数据修改时其他人不能同时修改，也不能读取，避免脏数据和脏读的问题
表锁(Table Lock)
指上锁的时候锁住的是整个表，当下一个事务访问该表的时候，必须等前一个事务释放了锁才能进行对表访问，特点：力度大，加锁简单，容易冲突
行锁()
指上锁的时候锁住的是表的某一行数据或多行记录，其他事务访问同一张表的时，只有被锁住的记录不能访问，其他记录可正常访问。
特点：粒度小，加锁比表锁麻烦，不容易冲突，相比表锁支持的并发要高、
记录锁(Record Lock)
属于行锁的一种，记录锁的范围只是表的某一条记录，事务在枷锁后所住的只是表的某一条记录
避免数据在被查询时被修改的重复读问题和在修改的事务未提交钱被其他事务读取的脏读问题
页锁
MySQL中锁定颗粒介于行级锁和表级锁中间的锁，取了折中的页级，一次锁定相邻的一组记录
特点，开锁和加锁时间介于表锁和行锁之间，会出现死锁，并发度一般
间隙锁(Gap Lock)
行锁的一种，在事务枷锁后锁住的时表记录的某一个区间，当表的相邻id之间出现空隙则会形成一
个区间，遵循左开右闭原则，只会出现在REPEATABLE READ(重复读)的事务级别中，没有间隙锁则会出现同一事务，两次查询结构不同
临建锁(Next-Key Locks)
锁住某条记录 ，又想阻止其他事务在该记录前边的间隙插入新记录 ，Next-Key Locks是在存储引
擎innodb 、事务级别在可重复读的情况下使用的数据库锁,避免在范围查询时出现脏读，重复读，
幻读问题，加锁后，范围区间内数据不允许被修改和插入
意向共享锁
事务试图对整个表进行加共享锁之前，首先要获得这个表的意向共享锁
意向排他锁
事务试图对整个表进行加排他锁之前，首先要获得这个表的意向排他锁
原文地址：https://blog.csdn.net/weixin_51627264/article/details/136546009
免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！