目录
10、为什么建议innodb表需要建立主键,并且推荐使用整型的自增主键(不推荐使用uuid)?
1、myisam和innodb的区别
myisam:
1、myisam不支持事务,但每一次操作都是原子性的
2、支持表级别锁
3、非聚集索引:索引和数据存在两张不同的表,索引和主键的数据域存储指向数据文件的指针
4、不支持外键
innobd:
1、支持事务
2、支持行级别锁
3、聚集索引:索引的数据域存储主键,主键的数据域存储数据
4、支持外键
2、innodb事务
事务是数据库作为一个单元的一组数据操作。在事务执行中,所有数据操作都成功事务才会成功;如果其中一个数据操作失败,事务将会失败进行回滚,并且这个事务的所有操作都会被取消。
事务的特性:
1、原子性:事务中的操作要么一起成功,要么一起失败
2、隔离性:一个事务不会对另外一个事务造成影响
3、一致性:事务的执行使得数据库从一个正确的状态变为另外一个正确的状态
4、持久性:事务被执行后,其结果将会永久保持在数据库中
3、简述脏读、幻读、不可重复读
脏读:事务A可以读取事务B未提交的数据,当事务B回滚时,事务A读取的数据有误,不符合一致性原则
不可重复读:事务A读取两次数据,在这期间事务B对数据进行修改或删除,导致事务A两次读取的数据不一致。不符合隔离性原则
幻读:事务A第一次读取数据后,事务B新增满足条件的数据,导致事务A第二次检索同样条件的数据时,读取到了事务A新增的数据,两次读取的数据不一致。不符合隔离性原则
4、事务的隔离级别
读未提交:在这一隔离级别下,事务可以读取另外一个事务未提交的数据;会有脏读、不可重复读、幻读问题
读已提交:大多数数据库默认的级别,这一级别下事务只能读另外事务已提交的数据;解决了脏读的问题,但是仍有不可重复读、幻读问题
可重复读:MySQL默认的隔离级别,确保了多个事务在并发情况下读取数据,会看到同样的数据;解决了脏读、不可重复读问题,有幻读问题
可串行化:最高级别,强制了事务排序,解决了脏读、不可重复读、幻读。但是会造成大量的锁竞争,性能最低
5、什么是索引?
索引是一种数据文件(innodb索引是数据表空间的一个组成部分),索引包含了数据记录的引用指针。
索引是一种数据结构,通过索引可以快速查询表数据。
索引包括主键索引、唯一索引和普通索引
索引的优点:可以快速查询到数据
缺点:1、插入和更新数据都需要维护索引,需要消耗一定的时间。
2、索引本身也是一种数据结构,需要占用一定的空间存储
mysql索引常用的数据结构有:哈希索引、b+树索引
6、主键、唯一、普通索引的区别
1、主键索引:每张表只能有一个主键索引,主键索引不能为null,不能重复,主键索引的叶子存储数据记录
2、唯一索引:可以为null,索引列不能重复,叶子存储主键id
3、普通索引:可以为null,索引列可以重复,叶子存储主键id
7、什么情况下建立索引又无法使用索引?
1、使用”like %“这样的索引字段查询时,因为索引遵循最左匹配
2、or语句前后没有使用索引
3、数据类型出现隐式转化(如varchar不加单引号的话可能会自动转换为int型),注意:varchar类型转成int类型,都是直接转成0
4、where条件的索引字段进行了表达式操作,也不能走索引(如 where a+1=2)
5、where条件使用了!=或者not in
5、使用联合索引,未使用第一个索引
8、B树和B+树的区别,以及hash索引的区别
B+树是升级的B树,叶子节点是链性结构,非叶子结点不存储data,只存储冗余索引
hash会把列的做一次hash计算得到一个值,再进行存储,所以hash索引不适合范围查询。hash会有冲突问题
9、聚集索引和非聚集索引区别
1、非聚集索引:索引和主键的叶子节点都存储数据的内存地址,索引文件和数据文件是分离的
2、聚集索引:主键的叶子结点存储完整的数据记录,非主键索引的叶子结点存储主键;索引文件和数据文件是一起的
10、为什么建议innodb表需要建立主键,并且推荐使用整型的自增主键(不推荐使用uuid)?
1、mysql为了维护索引文件,如果不指定主键的话,mysql会从选取某一列符合唯一索引作为主键,当找不到某一列作为主键时,mysql会创建一列隐形列作为主键,增加了mysql的维护开销,所以建议指定主键
2、使用整型的原因是因为整型的查找开销小。
3、使用自增的原因是因为新增数据时,不需要通过分裂节点来维护索引文件
4、使用自增的方式插入还有一个好处就是插入时可以提高效率,只需要插入到链表最后;如果使用uuid,那么插入的话可能需要在链表的中间去插入数据,效率变低;
11、简述联合索引的最左前缀原则
按着联合字段从左到右依次递增排序
12、innodb每一次从磁盘中拿取多大的数据到内存中?
一页16kb
13、什么是覆盖索引?
查询的字段正好是索引字段,不需要通过主键回表
14、order by索引字段什么时候索引失效?
当回表次数太多的时候会索引失效
15、什么是索引条件下推?
联合索引,符合最左前缀原则查询,并且后面的字段使用到索引,就是索引条件下推
16、什么是全局锁、排它锁、共享锁?
全局锁:全局锁就是对整个数据库实例加锁。
共享锁:读锁,也就是在查询时加锁。当加上共享锁时,其它事务可以并发读取数据(也就是其它事务可以加共享锁,不能加排它锁),但是不能对数据进行修改,直到已释放所有的共享锁。如果事务对共享锁进行修改,有可能会造成死锁。共享锁的出现是为了并发读数据
排它锁:写锁,只有这个事务可以对数据进行读写,其它事务不能对数据进行任何操作。在排它锁期间,其它事务不能对该数据加共享锁和排它锁,除非释放锁。排它锁是悲观锁的一种实现。
17、解决死锁的策略
1、设置超时时间
2、主动回滚其中的某一个事务释放锁
18、mysql优化
1、读写分离
2、分库分表
- 水平拆分:
一、根据u_id进行hash取模,尽可能打乱,然后进行分表插入
二、按照时间来进行分表
3、加索引,并且使用最优索引
4、加缓存
5、尽量不要使用select *
6、不要使用is null查询,有可能会全表扫描
7、in 和 not in 也要慎用,否则会导致全表扫描
8、or进行连接也要注意
19、索引的基本原理
1、把创建了索引的列的内容进行排序
2、对排序结果生成倒序表
3、在倒序表的内容拼接上数据的地址指针
4、在查询时,先拿到倒序表的内容,再根据内容拿到地址指针,进而通过指针拿到查询的内容
20、聚簇索引和非聚簇索引的区别
1、聚簇索引:数据和索引是放在同一个文件中,并且按照一定的排序进行存放,找到索引就找到数据,数据的物理存储顺序和索引的存储顺序是一致的。
2、非聚簇索引:数据和索引存放在两个不同个文件中,非聚簇索引的叶子结点存放数据行的内存地址
3、聚簇索引的优势和劣势:
- 优势:
1)聚簇索引如果是查询覆盖索引的情况下速度要更快
2)聚簇索引适合范围查询,因为是有序的
2)聚簇索引适合排序查询
- 劣势:
1)维护索引的开销很大,特别是当插入新行或者主键被更新导致要分页时。
2)当使用uuid作为主键时,会出现聚簇索引比全表扫描更慢
3)当主键很大时,由于辅助索引的叶子结点存储主键,会导致辅助索引占用空间很大
21、B+树索引和哈希索引的区别
1、B+树是有序的,且叶子节点之前是双向的,所以B+树特别适合范围查找
2、哈希索引适合等值查询,只需要将索引做一次哈希计算即可找到数据地址,遍历度为1
3、哈希索引不适合做范围查询或模糊查询
4、在有大量重复键的情况下,哈希索引可能会有大量的哈希冲突,效率极低
22、索引设计的原则
查询更快、占用空间越小
1、适合索引的列是出现再where字句中的列,或者连接子句中指定的列
2、基数较小的表,索引效果较差,没必要建立索引
3、使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间,如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配
4、不要过度索引,创建索引需要维护索引文件,过多的索引会占用很多空间,并且会降低写的性能
5、定义有外键的数据列一定要建立索引
6、更新频繁的字段不适合创建索引
7、不能有效区分数据的列不适合做索引(如性别)
8、尽量的拓展索引,不要新建索引。比如表中已经有a字段索引,要创建联合索引(ab),可以直接在原有索引基础上修改
9、对于查询中很少涉及的列,重复值比较多的列不要建立索引
10、定义为text、image和bit数据类型的列不要建立索引
23、锁的类型有哪些?
1、基于锁的属性分类:共享锁、排它锁
2、基于锁的粒度分类:行级锁(innodb)、表级锁(innodb、myisam)、页级锁、记录锁、间隙锁、临键锁
- 行级锁:某一行或者某些行,力度小,加锁开销大
- 表级锁:对整个表加锁,力度大、加锁难度小
- 页级锁:基于行级锁和表级锁之间,容易造成死锁
- 记录锁:某一些行的记录进行加锁
- 间隙锁:左开右闭的记录加锁
- 临键锁:左闭右闭的记录加锁
3、基于锁的状态分类:意向共享锁、意向排它锁
意向锁解释:当对某一行加锁时,需要先获取意向锁,这样可以告诉后面的人我已经对这一行数据加了锁了。
24、sql慢查询优化
1、看是否有命中索引,或者有没有使用最优索引,这个可以从执行计划看出来
2、看有没有某些不必要的字段返回
3、看查询出来的数据量是不是过大了,如果过大的话就要考虑分表分库了
25、ACID是靠什么保证的?
1、原子性:有undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql
2、一致性(目的):靠其它三大特性保证、程序代码要保证业务上的一致性
3、隔离性:有MVCC保证
4、持久性:由内存+redo log保证,mysql修改数据同时在内存和redo log记录这次操作,宕机时可以从redo log恢复
补充:innodb redo log写盘,innodb事务进入prepare状态。如果prepare成功,binlog写盘,再继续将事务日志持久到binlog,如果持久化成功,那么事务则进入commit状态(在redo log里面写入一个commit记录),所以判断一个事务是否执行成功可以从redo log中是否有commit记录判断
26、说明一下MVCC
MVCC只在读已提交隔离级别和可重复度隔离级别下有效。MVCC保证了事务的隔离级别
开始事务时创建readview,readview维护当前活动的事务id,即未提交的事务id,排序生成一个数组访问数据,获取数据中的事务id(获取的是事务id最大的记录,即roll_pointer,上个版本的事务id),对比readview:
- 如果在左边,可以访问
- 如果在右边(比readview都大),不可以访问;获取roll_pointer,取上一版本重新对比(在右边意味着,改事务在readview生成之后出现,在readview中则意味着该事务还未提交)
已提交隔离级别下的事务每次查询的开始都会生成一个独立的readview,而可重复读则在第一次读的时候生成readview,之后的读都复用之前的readview
这就是mysql的MVCC,通过版本链,实现多版本,课并发读-写,写-读。通过readview生成策略的不同实现不同的隔离级别。
27、如何处理脏读、不可重复读、幻读
加锁、隔离级别、MVCC
1、加锁:
- 脏读:修改数据时加排它锁,读数据时加共享锁
- 不可重复读:跟脏读一样
- 幻读:加范围锁。
2、隔离级别:
- 脏读:读已提交、可重复度、串行解决了
- 不可重复读:可重复读、串行
- 幻读:串行