文章目录
- 1.ACID靠什么保证的
- 2.Explain语句结果中各个字段分表表示
- 3.MySQL的索引结构是什么样的
- 4.MySQL集群如何搭建,读写分离是怎么做的?(vip)
- 5.mysql聚簇和非聚簇索引的区别(vip)
- 6.Mysql慢查询该如何优化?
- 7.mysql索引结构,各自的优劣(vip)
- 8.MySQL有哪几种数据存储引擎(VIP)
- 9.mysql执行计划怎么看
- 10.mysql主从同步原理
- 11.阿里二面:Innodb是如何实现事务的
- 12.阿里二面:Mysql数据库中,什么情况下设置了索引但无法使用
- 13.存储拆分后如何解决唯一主键
- 14.海量数据下,如何快速查找一条记录
- 15.简述Myisam和Innodb的区别
- 16.简述mysql中索引类型及对数据库的性能的影响
- 17.蚂蚁二面:Mysql的锁你了解哪些(VIP)
- 18.蚂蚁一面:B树和B+树的区别,为什么Mysql使用B+树
- 19.面试-事务的基本特性和隔离级别(VIP)
- 20.什么是MVCC
- 21.什么是脏读、幻读、不可重复读(VIP)
- 22.索引的基本原理
- 23.索引的设计原则
- 24.索引覆盖是什么
- 25.谈谈如何对MySQL进行分库分表(vip)
- 26.怎么处理慢查询
- 27.最左前缀原则是什么
1.ACID靠什么保证的
A原子性由undo log日志保证,它记录了需要回滚的日志信息,事务回滚时撤销已经执行成功的sql
C一致性由其他三大特性保证、程序代码要保证业务上的一致性
I隔离性由MVCC来保证
D持久性由内存+redo log来保证,mysql修改数据同时在内存和redo log记录这次操作,宕机的时候可 以从redo log恢复
InnoDB redo log 写盘,InnoDB 事务进入 prepare 状态。
如果前面 prepare 成功,binlog 写盘,再继续将事务日志持久化到 binlog,如果持久化成功,那么 InnoDB 事务则进入 commit 状态(在 redo log 里面写一个 commit 记录)
redolog的刷盘会在系统空闲时进行
2.Explain语句结果中各个字段分表表示
3.MySQL的索引结构是什么样的
二叉树 -》 AVL树 -》 红黑树 -》 B-树 -》 B+树
二叉树: 每个节点最多只有两个子节点, 左边的子节点都比当前节点小,右边的子节点都比当前节点大。
AVL树: 树中任意节点的两个子树的高度差最大为1
红黑树:1、每个节点都是红色或者黑色。2 根节点是黑色。3 每个叶子节点都是黑色的空节点。4 红色节点的父子节点都必须是褐色。5 从任一节点到其每个叶子节点的所有路径都包含相同的黑色节点。
B-树: 1、B-树的每个非叶子节点的子节点个数都不会超过D(这个D就是B-树的阶)2、所有的叶子节点都在同一层。3.所有节点关键字都是按照递增顺序排列。
B+树: 1、非叶子节点不存储数据,只进行数据索引。2、所有数据都存储在叶子节点当中。3、每个叶子节点都存有相邻叶子节点的指针。4、叶子节点按照本身关键字从小到大排序。
4.MySQL集群如何搭建,读写分离是怎么做的?(vip)
MySQL主从集群的搭建原理:
MySQL通过将主节点的Binlog同步给从节点完成主从之间的数据同步。
MySQL的主从集群只会将binlog从主节点同步到从节点,而不会反过来同步。由此也就引申出了读写分离的问题。
因为要保证主从之间的数据一致,写数据的操作只能在主节点完成, 而读数据的操作,可以在主节点或者从节点上完成。
5.mysql聚簇和非聚簇索引的区别(vip)
一、
都是B+树的数据结构
聚簇索引:将数据存储与索引放到了一块、并且是按照一定的顺序组织的,找到索引也就找到了数 据,数据的物理存放顺序与索引顺序是一致的,即:只要索引是相邻的,那么对应的数据一定也是 相邻地存放在磁盘上的
非聚簇索引:叶子节点不存储数据、存储的是数据行地址,也就是说根据索引查找到数据行的位置 再取磁盘查找数据,这个就有点类似一本树的目录,比如我们要找第三章第一节,那我们先在这个 目录里面找,找到对应的页码后再去对应的页码看文章。
优势:
- 查询通过聚簇索引可以直接获取数据,相比非聚簇索引需要第二次查询(非覆盖索引的情况下)效率 要高
- 聚簇索引对于范围查询的效率很高,因为其数据是按照大小排列的
- 聚簇索引适合用在排序的场合,非聚簇索引不适合
劣势:
- 维护索引很昂贵,特别是插入新行或者主键被更新导至要分页(page split)的时候。建议在大量插 入新行后,选在负载较低的时间段,通过OPTIMIZE TABLE优化表,因为必须被移动的行数据可能造成 碎片。使用独享表空间可以弱化碎片
- 表因为使用UUId(随机ID)作为主键,使数据存储稀疏,这就会出现聚簇索引有可能有比全表扫面 更慢,所以建议使用int的auto_increment作为主键
- 如果主键比较大的话,那辅助索引将会变的更大,因为辅助索引的叶子存储的是主键值;过长的主键 值,会导致非叶子节点占用占用更多的物理空间
InnoDB中一定有主键,主键一定是聚簇索引,不手动设置、则会使用unique索引,没有unique索引, 则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引, 辅助索引访问数据总是需要二次查找,非聚簇索引都是辅助索引,像复合索引、前缀索引、唯一索引, 辅助索引叶子节点存储的不再是行的物理位置,而是主键值
MyISM使用的是非聚簇索引,没有聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构 完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助 键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。
如果涉及到大数据量的排序、全表扫描、count之类的操作的话,还是MyISAM占优势些,因为索引所 占空间小,这些操作是需要在内存中完成的。
二、
聚簇索引就是数据和索引是在一起的。
MyISAM使用的是非聚簇索引,树的子节点上的data不是数据本身,而是数据存放的地址。InnoDB采用的是聚簇索引,树的叶子节点上的data就是数据本身。
聚簇索引的数据物理存放顺序和索引顺序是一致的,所以一个表当中只能有一个聚簇索引,而非聚簇索引可以有多个。
InnoDB中,如果表定义了PK,那PK就是聚簇索引。 如果没有PK,就会找第一个非空的unique列作为聚簇索引。否则,InnoDB会创建一个隐藏的row-id作为聚簇索引。
6.Mysql慢查询该如何优化?
- 检查是否⾛了索引,如果没有则优化SQL利⽤索引
- 检查所利⽤的索引,是否是最优索引
- 检查所查字段是否都是必须的,是否查询了过多字段,查出了多余数据
- 检查表中数据是否过多,是否应该进⾏分库分表了
- 检查数据库实例所在机器的性能配置,是否太低,是否可以适当增加资源
7.mysql索引结构,各自的优劣(vip)
一、
索引的数据结构和具体存储引擎的实现有关,在MySQL中使用较多的索引有Hash索引,B+树索引等, InnoDB存储引擎的默认索引实现为:B+树索引。对于哈希索引来说,底层的数据结构就是哈希表,因 此在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快;其余大部分场景,建议选择BTree索引。
B+树:
B+树是一个平衡的多叉树,从根节点到每个叶子节点的高度差值不超过1,而且同层级的节点间有指针 相互链接。在B+树上的常规检索,从根节点到叶子节点的搜索效率基本相当,不会出现大幅波动,而且基于索引的顺序扫描时,也可以利用双向指针快速左右移动,效率非常高。因此,B+树索引被广泛应用于数据库、文件系统等场景。
哈希索引:
哈希索引就是采用一定的哈希算法,把键值换算成新的哈希值,检索时不需要类似B+树那样从根节点到 叶子节点逐级查找,只需一次哈希算法即可立刻定位到相应的位置,速度非常快
如果是等值查询,那么哈希索引明显有绝对优势,因为只需要经过一次算法即可找到相应的键值;前提 是键值都是唯一的。如果键值不是唯一的,就需要先找到该键所在位置,然后再根据链表往后扫描,直 到找到相应的数据;
如果是范围查询检索,这时候哈希索引就毫无用武之地了,因为原先是有序的键值,经过哈希算法后, 有可能变成不连续的了,就没办法再利用索引完成范围查询检索;
哈希索引也没办法利用索引完成排序,以及like ‘xxx%’ 这样的部分模糊查询(这种部分模糊查询,其实 本质上也是范围查询);
哈希索引也不支持多列联合索引的最左匹配规则;
B+树索引的关键字检索效率比较平均,不像B树那样波动幅度大,在有大量重复键值情况下,哈希索引 的效率也是极低的,因为存在哈希碰撞问题。
8.MySQL有哪几种数据存储引擎(VIP)
MySQL中通过show ENGINES指令可以看到所有支持的数据库存储引擎。 最为常用的就是MyISAM 和InnoDB 两种。
MyISAM和InnDB的区别:
1、存储文件。 MyISAM每个表有两个文件。 MYD和MYISAM文件。 MYD是数据文件。 MYI是索引文件。 而InnDB每个表只有一个文件,idb。
2、InnoDB支持事务,支持行级锁,支持外键。
3、InnoDB支持XA事务
4、InnoDB支持savePoints
9.mysql执行计划怎么看
执行计划就是sql的执行查询的顺序,以及如何使用索引查询,返回的结果集的行数
EXPLAIN SELECT * from A where X=? and Y=?
-
id :是一个有顺序的编号,是查询的顺序号,有几个 select 就显示几行。id的顺序是按 select 出现 的顺序增长的。id列的值越大执行优先级越高越先执行,id列的值相同则从上往下执行,id列的值为 NULL最后执行。
-
selectType 表示查询中每个select子句的类型
- SIMPLE: 表示此查询不包含 UNION 查询或子查询
- PRIMARY: 表示此查询是最外层的查询(包含子查询)
- SUBQUERY: 子查询中的第一个 SELECT
- UNION: 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION: UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT, UNION 的结果
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查
- 询的结果.
- DERIVED:衍生,表示导出表的SELECT(FROM子句的子查询)
-
table:表示该语句查询的表
-
type:优化sql的重要字段,也是我们判断sql性能和优化程度重要指标。他的取值类型范围:
- const:通过索引一次命中,匹配一行数据
- system: 表中只有一行记录,相当于系统表;
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配
- ref: 非唯一性索引扫描,返回匹配某个值的所有
- range: 只检索给定范围的行,使用一个索引来选择行,一般用于between、<、>;
- index: 只遍历索引树;
- ALL: 表示全表扫描,这个类型的查询是性能最差的查询之一。 那么基本就是随着表的数量增多,
- 执行效率越慢。
执行效率:
ALL < index < range< ref < eq_ref < const < system。最好是避免ALL和index
-
possible_keys:它表示Mysql在执行该sql语句的时候,可能用到的索引信息,仅仅是可能,实际不一 定会用到。
-
key:此字段是 mysql 在当前查询时所真正使用到的索引。 他是possible_keys的子集
-
key_len:表示查询优化器使用了索引的字节数,这个字段可以评估组合索引是否完全被使用,这也是我们优化sql时,评估索引的重要指标
-
rows:mysql 查询优化器根据统计信息,估算该sql返回结果集需要扫描读取的行数,这个值相关重 要,索引优化之后,扫描读取的行数越多,说明索引设置不对,或者字段传入的类型之类的问题,说明 要优化空间越大
-
filtered:返回结果的行占需要读到的行(rows列的值)的百分比,就是百分比越高,说明需要查询到 数据越准确, 百分比越小,说明查询到的数据量大,而结果集很少
-
extra
- using filesort :表示 mysql 对结果集进行外部排序,不能通过索引顺序达到排序效果。一般有
- using filesort都建议优化去掉,因为这样的查询 cpu 资源消耗大,延时大。
- using index:覆盖索引扫描,表示查询在索引树中就可查找所需数据,不用扫描表数据文件,往 往说明性能不错。
- using temporary:查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不 高,建议优化。
- using where :sql使用了where过滤,效率较高。
10.mysql主从同步原理
mysql主从同步的过程:
Mysql的主从复制中主要有三个线程: master(binlog dump thread)、slave(I/O thread 、SQL
thread) ,Master一条线程和Slave中的两条线程。
- 主节点 binlog,主从复制的基础是主库记录数据库的所有变更记录到 binlog。binlog 是数据库服 务器启动的那一刻起,保存所有修改数据库结构或内容的一个文件。
- 主节点 log dump 线程,当 binlog 有变动时,log dump 线程读取其内容并发送给从节点。
- 从节点 I/O线程接收 binlog 内容,并将其写入到 relay log 文件中。
- 从节点的SQL 线程读取 relay log 文件内容对数据更新进行重放,最终保证主从数据库的一致性。
注:主从节点使用 binglog 文件 + position 偏移量来定位主从同步的位置,从节点会保存其已接收到的 偏移量,如果从节点发生宕机重启,则会自动从 position 的位置发起同步。 由于mysql默认的复制方式是异步的,主库把日志发送给从库后不关心从库是否已经处理,这样会产生 一个问题就是假设主库挂了,从库处理失败了,这时候从库升为主库后,日志就丢失了。由此产生两个 概念。
全同步复制
主库写入binlog后强制同步日志到从库,所有的从库都执行完成后才返回给客户端,但是很显然这个方式的话性能会受到严重影响。
半同步复制
和全同步不同的是,半同步复制的逻辑是这样,从库写入日志成功后返回ACK确认给主库,主库收到至少一个从库的确认就认为写操作完成。
11.阿里二面:Innodb是如何实现事务的
Innodb通过Buffer Pool,LogBuffer,Redo Log,Undo Log来实现事务,以⼀个update语句为例:
- Innodb在收到⼀个update语句后,会先根据条件找到数据所在的⻚,并将该⻚缓存在Buffer Pool 中
- 执⾏update语句,修改Buffer Pool中的数据,也就是内存中的数据
- 针对update语句⽣成⼀个RedoLog对象,并存⼊LogBuffer中
- 针对update语句⽣成undolog⽇志,⽤于事务回滚
- 如果事务提交,那么则把RedoLog对象进⾏持久化,后续还有其他机制将Buffer Pool中所修改的 数据⻚持久化到磁盘中
- 如果事务回滚,则利⽤undolog⽇志进⾏回滚
12.阿里二面:Mysql数据库中,什么情况下设置了索引但无法使用
- 没有符合最左前缀原则
- 字段进⾏了隐式数据类型转化
- ⾛索引没有全表扫描效率⾼
13.存储拆分后如何解决唯一主键
UUID:简单、性能好,没有顺序,没有业务含义,存在泄漏mac地址的风险
数据库主键:实现简单,单调递增,具有一定的业务可读性,强依赖db、存在性能瓶颈,存在暴露业务信息的风险
redis,mongodb,zk等中间件:增加了系统的复杂度和稳定性
雪花算法
14.海量数据下,如何快速查找一条记录
-
使用布降过滤器,快速过滤不存在的记录,
使用Redis的bitmap结构来实现布隆过滤器。
-
在Redis中建立数据缓存。-将我们对Redis使用场景的理解尽量表达出来。
以普通字符串的形式来存储,(userld ->userjson)。 以一个hash来存储一条记录 (userld key->username field->userAge->)。 以一个整的hash来存储所有的数据,Userinfo-> field就用userld,value就用user.json。一个hash最多能支持2^32-1(40多个亿)个键值对
缓存击穿:对不存在的数据也建立key。这些key都是经过布隆过滤器过滤的,所以一般不会太多。
缓存过期:将热点数据设置成永不过期,定期重建缓存。使用分布式锁重建缓存。
-
查询优化。
按槽位分配数据
自己实现槽位计算,找到记录应该分配在哪台机器上,然后直接去目标机器上找。
15.简述Myisam和Innodb的区别
MyISAM:
不支持事务,但是每次查询都是原子的;
支持表级锁,即每次操作是对整个表加锁;
存储表的总行数;
一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;
采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引 不用保证唯一性。
InnoDb:
支持ACID的事务,支持事务的四种隔离级别;
支持行级锁及外键约束:因此可以支持写并发;
不存储总行数;
一个InnoDb引擎存储在一个文件空间(共享表空间,表大小不受操作系统控制,一个表可能分布在多 个文件里),也有可能为多个(设置为独立表空,表大小受操作系统文件大小限制,一般为2G),受操 作系统文件大小的限制;
主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅 索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时, 为维持B+树结构,文件的大调整。
16.简述mysql中索引类型及对数据库的性能的影响
普通索引:允许被索引的数据列包含重复的值。
唯一索引:可以保证数据记录的唯一性。
主键:是一种特殊的唯一索引,在一张表中只能定义一个主键索引,主键用于唯一标识一条记录,使用关键字 PRIMARY KEY 来创建。
联合索引:索引可以覆盖多个数据列,如像INDEX(columnA, columnB)索引。
全文索引:通过建立 倒排索引 ,可以极大的提升检索效率,解决判断字段是否包含的问题,是目前搜索引 擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
索引可以极大的提高数据的查询速度。
通过使用索引,可以在查询的过程中,使用优化隐藏器,提高系统的性能。
但是会降低插入、删除、更新表的速度,因为在执行这些写操作时,还要操作索引文件
索引需要占物理空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果要建立聚 簇索引,那么需要的空间就会更大,如果非聚集索引很多,一旦聚集索引改变,那么所有非聚集索引都会跟着变。
17.蚂蚁二面:Mysql的锁你了解哪些(VIP)
从锁的粒度来区分
-
行锁:加锁粒度小,但是加锁资源开销比较大。 InnDB支持。
共享锁: 读锁。多个事务可以对同一个数据共享同一把锁。持有锁的事务都可以访问数据,但是只能读不能修改。select xxx LOCK IN SHARE MODE。 排他锁: 写锁。只有一个事务能够获得排他锁,其他事务都不能获取该行的锁。InnoDB会对update\delete\insert语句自动添加排他锁。SELECT xxx FOR UPDATE。 自增锁: 通常是针对MySQL当中的自增字段。如果有事务回滚这种情况,数据会回滚,但是自增序列不会回滚。
-
表锁:加锁粒度大,加锁资源开销比较小。MyISAM和InnoDB都支持。
表共享读锁 表排他写锁 意向锁:是InnoDB自动添加的一种锁,不需要用户干预。
-
全局锁: Flush tables with read lock 。 加锁之后整个数据库实例都处于只读状态。所有的数据变更操作都会被挂起。一般用于全库备份的时候。
常见的锁算法: user: userid ( 1,4,9) update user set xxx where userid=5; REPEATABLE READ 间隙锁锁住(5,9)
- 记录锁:锁一条具体的数据。
- 间隙锁:RR隔离级别下,会加间隙锁。锁一定的范围,而不锁具体的记录。是为了防止产生幻读。(-xx,1)(1,4)(4,9)(9,xxx)
- Next-key : 间隙锁+右记录锁。(-xx,1](1,4](4,9](9,xxx)
18.蚂蚁一面:B树和B+树的区别,为什么Mysql使用B+树
-
B树的特点:
- 节点排序
- ⼀个节点了可以存多个元素,多个元素也排序了
B+树的特点:
- 拥有B树的特点
- 叶⼦节点之间有指针
- ⾮叶⼦节点上的元素在叶⼦节点上都冗余了,也就是叶⼦节点中存储了所有的元素,并且排好顺序
Mysql索引使⽤的是B+树,因为索引是⽤来加快查询的,⽽B+树通过对数据进⾏排序所以是可以提⾼查 询速度的,然后通过⼀个节点中可以存储多个元素,从⽽可以使得B+树的⾼度不会太⾼,在Mysql中⼀ 个Innodb⻚就是⼀个B+树节点,⼀个Innodb⻚默认16kb,所以⼀般情况下⼀颗两层的B+树可以存2000 万⾏左右的数据,然后通过利⽤B+树叶⼦节点存储了所有数据并且进⾏了排序,并且叶⼦节点之间有指 针,可以很好的⽀持全表扫描,范围查找等SQL语句。
19.面试-事务的基本特性和隔离级别(VIP)
事务: 表示多个数据操作组成一个完整的事务单元,这个事务内的所有数据操作要么同时成功,要么同时失败。
事务的特性:ACID
1、原子性:事务是不可分割的,要么完全成功,要么完全失败。
2、一致性:事务无论是完成还是失败,都必须保持事务内操作的一致性。当失败时,都要对前面的操作进行回滚,不管中途是否成功。
3、隔离性:当多个事务操作一个数据的时候,为防止数据损坏,需要将每个事务进行隔离,互相不干扰。
4、持久性: 事务开始就不会终止。他的结果不受其他外在因素的影响。即一旦事务提交,所做的修改就会永久保存到数据库中。
事务的隔离级别:SHOW VARIABLES like ‘transaction%’
设置隔离级别: set transaction level xxx 设置下次事务的隔离级别。
set session transaction level xxx 设置当前会话的事务隔离级别
set global transaction level xxx 设置全局事务隔离级别
MySQL当中有五种隔离级别
NONE : 不使用事务。
READ UNCOMMITED: 允许脏读
READ COMMITED: 防止脏读,最常用的隔离级别
REPEATABLE READ:防止脏读和不可重复读。MYSQL默认
SERIALIZABLE: 事务串行,可以防止脏读、幻读,不可重复度。
五种隔离级别,级别越高,事务的安全性是更高的,但是,事务的并性能也就会越低。
20.什么是MVCC
多版本并发控制:读取数据时通过一种类似快照的方式将数据保存下来,这样读锁就和写锁不冲突了, 不同的事务session会看到自己特定版本的数据,版本链
MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作。其他两个隔离级别够和 MVCC不兼容, 因为 READ UNCOMMITTED 总是读取最新的数据行, 而不是符合当前事务版本的数据 行。而 SERIALIZABLE 则会对所有读取的行都加锁。
聚簇索引记录中有两个必要的隐藏列:
trx_id:用来存储每次对某条聚簇索引记录进行修改的时候的事务id。
roll_pointer:每次对哪条聚簇索引记录有修改的时候,都会把老版本写入undo日志中。这个 roll_pointer就是存了一个指针,它指向这条聚簇索引记录的上一个版本的位置,通过它来获得上一个 版本的记录信息。(注意插入操作的undo日志没有这个属性,因为它没有老版本)
已提交读和可重复读的区别就在于它们生成ReadView的策略不同。
开始事务时创建readview,readView维护当前活动的事务id,即未提交的事务id,排序生成一个数组
访问数据,获取数据中的事务id(获取的是事务id最大的记录),对比readview:
InnoDB redo log 写盘,InnoDB 事务进入 prepare 状态。
如果在readview的左边(比readview都小),可以访问(在左边意味着该事务已经提交)
如果在readview的右边(比readview都大)或者就在readview中,不可以访问,获取roll_pointer,取 上一版本重新对比(在右边意味着,该事务在readview生成之后出现,在readview中意味着该事务还 未提交)
已提交读隔离级别下的事务在每次查询的开始都会生成一个独立的ReadView,而可重复读隔离级别则在第一次读的时候生成一个ReadView,之后的读都复用之前的ReadView。
这就是Mysql的MVCC,通过版本链,实现多版本,可并发读-写,写-读。通过ReadView生成策略的不同实现不同的隔离级别
21.什么是脏读、幻读、不可重复读(VIP)
这些问题都是MySQL进行事务并发控制时经常遇到的问题。
脏读: 在事务进行过程中,读到了其他事务未提交的数据。
不可重复读: 在一个事务过程中,多次查询的结果不一致。
幻读: 在一个事务过程中,用同样的操作查询数据,得到的记录数不相同。
处理的方式有很多种:加锁、事务隔离、MVCC
加锁:
- 脏读:在修改时加排他锁,直到事务提交才释放。读取时加共享锁,读完释放锁。
- 不可重复读: 读数据时加共享锁,写数据时加排他锁。
- 幻读: 加范围锁。
22.索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引,一般来说执行查询时遍历整张表。
索引的原理:就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
- 在查询的时候,先拿到倒排表内容,再取出数据地址链,从而拿到具体数据
23.索引的设计原则
查询更快、占用空间更小
- 适合索引的列是出现在where子句中的列,或者连接子句中指定的列
- 基数较小的表,索引效果较差,没有必要在此列建立索引
- 使用短索引,如果对长字符串列进行索引,应该指定一个前缀长度,这样能够节省大量索引空间,如果搜索词超过索引前缀长度,则使用索引排除不匹配的行,然后检查其余行是否可能匹配。
- 不要过度索引。索引需要额外的磁盘空间,并降低写操作的性能。在修改表内容的时候,索引会进行更新甚至重构,索引列越多,这个时间就会越长。所以只保持需要的索引有利于查询即可。
- 定义有外键的数据列一定要建立索引。
- 更新频繁字段不适合创建索引
- 若是不能有效区分数据的列不适合做索引列(如性别,男女未知,最多也就三种,区分度实在太低)
- 尽量的扩展索引,不要新建索引。比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修 改原来的索引即可。
- 对于那些查询中很少涉及的列,重复值比较多的列不要建立索引。
- 对于定义为text、image和bit的数据类型的列不要建立索引。
24.索引覆盖是什么
索引覆盖就是⼀个SQL在执⾏时,可以利⽤索引来快速查找,并且此SQL所要查询的字段在当前索引对 应的字段中都包含了,那么就表示此SQL⾛完索引后不⽤回表了,所需要的字段都在当前索引的叶⼦节点上存在,可以直接作为结果返回了
MySQL的覆盖索引和回表
如果只需要在一颗索引树上就可以获取SQL所需要的所有列,就不需要再回表查询,这样查询速度就可以更快。
实现索引覆盖最简单的方式就是将要查询的字段,全部建立到联合索引当中。
user(PK id , name ,sex)
select count(name) from user ; -> 在name 字段上建立一个索引。
select id , name ,sex from user; -> 将name上的索引升级成为(name,sex)的联合索引。
25.谈谈如何对MySQL进行分库分表(vip)
什么是分库分表? 就是当表中的数据量过大时,整个查询效率就会降低得非常明显。这时为了提升查询效率,就要将一个表中的数据分散到多个数据库的多个表当中。
分库分表最常用的组件: Mycat\ ShardingSphere
数据分片的方式有垂直分片和水平分片。垂直分片就是从业务角度将不同的表拆分到不同的库中,能够解决数据库数据文件过大的问题,但是不能从根本上解决查询问题。水平分片就是从数据角度将一个表中的数据拆分到不同的库或表中,这样可以从根本上解决数据量过大造成的查询效率低的问题。
有非常多的分片策略,比如 取模、按时间、按枚举值。。。。
阿里提供的开发手册当中,建议:一个表的数据量超过500W或者数据文件超过2G,就要考虑分库分表了。
分库分表后的执行流程:
一个user表,按照userid进行了分片,然后我需要按照sex字段去查,这要怎么查?强制指定只查一个数据库,要怎么做?查询结果按照userid来排序,要怎么排?
分库分表的问题: 垮库查询、跨库排序、分布式事务、公共表、主键重复。。。。。
26.怎么处理慢查询
在业务系统中,除了使用主键进行的查询,其他的都会在测试库上测试其耗时,慢查询的统计主要由运 维在做,会定期将业务中的慢查询反馈给我们。
慢查询的优化首先要搞明白慢的原因是什么?是查询条件没有命中索引?是load了不需要的数据列?还 是数据量太大?
所以优化也是针对这三个方向来的,
- 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载 了许多结果中并不需要的列,对语句进行分析以及重写。
- 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽 可能的命中索引。
- 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者 纵向的分表。
27.最左前缀原则是什么
当⼀个SQL想要利⽤索引是,就⼀定要提供该索引所对应的字段中最左边的字段,也就是排在最前⾯的字段,⽐如针对a,b,c三个字段建⽴了⼀个联合索引,那么在写⼀个sql时就⼀定要提供a字段的条件,这样才能⽤到联合索引,这是由于在建⽴a,b,c三个字段的联合索引时,底层的B+树是按照a,b,c三个字段从左往右去⽐较⼤⼩进⾏排序的,所以如果想要利⽤B+树进⾏快速查找也得符合这个规则