Java八股文-MySQL

mysql事务特性

原子性：一个事务内的操作统一成功或失败

一致性：事务前后的数据总量不变

隔离性：事务与事务之间相互不影响

持久性：事务一旦提交发生的改变不可逆

事务的隔离级别

读未提交： 只允许事务读取另一个事务没有提交的数据，可能出现不可重复读，幻读。

读已提交： 允许一个事务读取另一个事务已提交的数据，可能出现不可重复读，幻读。

可重复读： 确保同一字段多次读取结果一致，可能出现欢幻读。

可串行化： 所有事务逐次执行，没有并发问题

Inno DB 默认隔离级别为可重复读级别，分为快照读和当前读，并且通过间隙锁解决了幻读问题。

MySQL有哪些索引

主键索引：一张表只能有一个主键索引，主键索引列不能有空值和重复值

唯一索引：唯一索引不能有相同值，但允许为空

普通索引：允许出现重复值

组合索引：对多个字段建立一个联合索引，减少索引开销，遵循最左匹配原则

全文索引：myisam引擎支持，通过建立倒排索引提升检索效率，广泛用于搜索引擎

聚簇索引和非聚簇索引的区别

聚簇索引：聚簇索引的叶子节点存放的是主键值和数据行；辅助索引（在聚簇索引上创建的其它索引）的叶子节点存放的是主键值或指向数据行的指针。

优点：根据索引可以直接获取值，所以他获取数据更快；对于主键的排序查找和范围查找效率更高；

缺点：如果主键值很大的话，辅助索引也会变得很大；如果用uuid作为主键，数据存储会很稀疏；更新主键也会导致被更新的行移动；所以一般都定义主键为自增并且不可更新。

非聚簇索引（辅助索引）：叶子节点存放的是数据行地址，先根据索引找到数据地址，再根据地址去找数据

他们都是b+树结构

B和B+树的区别，为什么使用B+树

二叉树：索引字段有序，极端情况会变成链表形式

AVL数：树的高度不可控

B数：控制了树的高度，但是索引值和data都分布在每个具体的节点当中，若要进行范围查询，要进行多次回溯，IO开销大

B+树：非叶子节点只存储索引值，叶子节点再存储索引+具体数据，从小到大用链表连接在一起，范围查询可直接遍历不需要回溯

MySQL有哪些锁

基于粒度：

  *表级锁：对整张表加锁，粒度大并发小

  *行级锁：对行加锁，粒度小并发大

  *间隙锁：间隙锁，锁住表的一个区间，间隙锁之间不会冲突只在可重复读下才生效，解决了幻读

基于属性：

    *共享锁：又称读锁，一个事务为表加了读锁，其它事务只能加读锁，不能加写锁

    *排他锁：又称写锁，一个事务加写锁之后，其他事务不能再加任何锁，避免脏读问题

为什么要用内连接而不用外连接？

用外连接的话连接顺序是固定死的，比如left join，他必须先对左表进行全表扫描，然后一条条到右表去匹配；而内连接的话mysql会自己根据查询优化器去判断用哪个表做驱动。

子查询的话同样也会对驱动表进行全表扫描，所以尽量用小表做驱动表。

MySQL如何做慢SQL优化

可以查看执行计划分析数据的扫描类型、索引是否生效，常见的慢查询优化有：

（1）尽量减少select的数据列，尽量使用覆盖索引

（2）orderby查找时使用索引进行排序，否则的话需要进行回表

（3）groupby查询时，同样要用索引，避免使用到临时表

（4）分页查询时，如果limit 后面的数字太大，可以使用子查询查出主键，再limit主键后n条数据就能走覆盖索引

（5） 使用复杂查询时，使用关联查询来代替子查询，并且最好使用内连接

（6）使用count函数时直接使用count的话count(*)的效率最高

count(*)或count(唯一索引)或count(数字):表中总记录数，count(字段)不会统计null

 (7) 在写update语句时，where条件要使用索引，否则会锁会从行锁升级为表锁

（8）表中数据是否太大，是不是要分库分表

哪些情况索引会失效

（1）where条件中有or，除非所有查询条件都有索引，否则失效

（2）like查询用%开头，索引失效

（3）索引列参与计算，索引失效

（4）违背最左匹配原则，索引失效

（5）索引字段发生类型转换，索引失效

（6）mysql觉得全表扫描更快时（数据少），索引失效

where和having的区别？

where是约束声明，having是过滤声明，where早于having执行，并且where不可以使用聚合函数，having可以