1. SQL语句如何开始执行?
MySQL分为Server和存储引擎两部分:
Server层包含连接器、存储缓存、分析器、执行器等,以及所有的内置函数(事件、日期)等等,还有视图、触发器。
存储引擎是负责数据的存储和提取,我们有InnoDB、MyISAM等等。MySQL建表默认InnoDB,但是也可以指定。
- 如何去查询?
- 连接器先建立 TCP连接,查询你的权限,此次连接,你的权限不会被别人所更改。
- 执行select语句,先查缓存,没有命中就继续。大多数情况下缓存比较麻烦,最好不要用指定缓存查询。8.0之后就没缓存功能了
- 分析器。分析器用来检查SQL语法,并语法分析。
- 优化器。MySQL内部对语句判断如何选择索引。
- 执行器。判断你有权限,继续调用InnoDB,返回结果。
2.日志系统
更新流程:
redolog,binlog
redolog WAL技术,先写日志,不往前去更改,4个盘,循环的写。
redolog是存储引擎层的日志。
binlog是server层日志,追加写。
两阶段提交:
让两份日志之间的逻辑一致。
3. 事务隔离
-
SQL标准的事务隔离级别包括:读未提交(read uncommitted)、
读提交(read committed)、可重复读(repeatable read)和串行化(serializable )。
读未提交是指,一个事务还没提交时,它做的变更就能被别的事务看到。
读提交是指,一个事务提交之后,它做的变更才会被其他事务看到。
可重复读是指,一个事务执行过程中看到的数据,总是跟这个事务在启动时看到的数据是一
致的。当然在可重复读隔离级别下,未提交变更对其他事务也是不可见的。
串行化,顾名思义是对于同一行记录,“写”会加“写锁”,“读”会加“读锁”。当出现读写锁冲突
的时候,后访问的事务必须等前一个事务执行完成,才能继续执行。 -
多版本的并发控制(MVCC),控制了回滚。
4.索引上
- 有序数组 哈希,只能用于静态存储引擎,写入了就不能去修改。
- 二叉树比较慢,所以InnoDB用B+树。
- 主键是一棵B+树,N个叉,只在叶子节点存所有数据的值,也被叫聚簇索引。
- 非主键索引的叶子是主键值,所以需要查找到主键值回表,去主键B+树去查值,如果说这个索引是个联合索引,又恰好你只需要这些值,那就不用回表,叫覆盖索引。
- 用自增主键可以防止页分裂的降低效率
- 最左前缀,
5. 索引下
- 覆盖索引:只需要查ID的值,而ID的值已经在k索引树上了,因此可以直接提供查询结果,不需要回表。
- 最左前缀:即当你创建了一个联合索引,该索引的任何最左前缀都可以用于查询。 比如当你有一个联合索引 (col1, col2, col3) ,该索引的所有前缀为 (col1) 、 (col1, col2) 、 (col1, col2, col3) ,包含这些列的所有查询都会使用该索引进行查询。
- 索引下推:组合索引满足最左匹配,但是遇到非等值判断时匹配停止。name like ‘陈%’ 不是等值匹配,所以 age = 20 这里就用不上 (name,age) 组合索引了。如果没有索引下推,组合索引只能用到 name,age 的判定就需要回表才能做了。5.6之后有了索引下推,age = 20 可以直接在组合索引里判定。
6.全局锁和表锁
MySQL M 里面的锁大致可以分成全局锁、表级锁和行锁三类
- 全局锁:做全库逻辑备份,保证备份时是唯一视图,MySQL里是mysqldump,参数single-transaction是做这个的,MVCC也能保证数据正常更新。别的引擎用FTWRL
- 表级锁:lock tables,面积太大。MySQL,引入MDL,增删改查时,加读锁,更改表结构,加写锁。读锁不互斥,写互斥,就是阻塞。
- 长事务不行,不提交占用MDL,阻塞。解决长事务,kill或者设定等待时间(指定DDL NOWIT/ WAIT N).
ALTER TABLE tbl_name NOWAIT add column …
ALTER TABLE tbl_name WAIT N add column …
7.行锁
- 两阶段锁:在InnoDB事务中,行锁是在需要的时候才加上的,但并不是不需要了就立刻释放,而是要等到事务结束Commit时才释放。这个就是两阶段锁协议。
- 更改操作顺序,共同操作的放后面,减少事务之间的等待。(影院买票,插日志,改顾客余额,更改影院余额放最后面)
- 死锁和死锁检测:死锁是并发中循环资源依赖,都在都等别人释放,导致都无限等待。A等待B放id2,B等A释放id1
- 解决死锁:
- 设置超时时间,一般不用。
- 主动死锁检测:解决热点更新的问题。1000个并发线程更改同一行,死锁检测是100万级。
- 控制并发度,更改电影院账户为10个,分流。
8. 事务如何隔离
- 视图概念:一个用查询语句定义的虚拟表,调用的时候执行查询语句生成结果。
- InnoDB实现MVCC时用到的一致性视图,consistent read view,支持RC和RR隔离的实现。
- 每一行数据有多个由InnoDB发放的Transaction id,MVCC视图可以分为已提交事务,未提交事务集合,未开始事务。分别是可见,部分可见,不可见。
- 任务视图三种情况:
- 版本未提交,不可见
- 版本已提交,视图创建后提交,不可见
- 版本已提交,视图创建前提交,可见
- 更新数据都是先读后写,这个读,只能读当前值,成为“当前读”。
- 可重复读(一致性读)核心就是唯一视图,更新数据只能用当前读,如果要更新数据被别人占用了没提交,就锁住了,称为行锁。
9. 普通索引和唯一索引
- change buffer 是InnoDB的内存操作,merge是得到新结果。
- 唯一索引要判断,需要加载入内存,已经在内存中的用内存更快。所以唯一索引不需要change buffer
- 如果插入值有冲突:
- 普通索引会将更新记录在change buffer,后面merge后会持久化到磁盘。
- 唯一索引将数据页读入内存,判断没有冲突,插入后,语句结束。(读入内存需要大量IO,change buffer减少这个操作)
- 写多读少场景,change buffer的收益最大。(账单类,日志类)。反之,是副作用。
- 尽量选择普通索引,配置change buffer比较好用
- redo log WAL,
- 写操作:如果在内存中数据直接改,没在就change buffer记录中更改,最后都写入redolog中。
- 读操作:在内存中直接返回,不在的,先读入内存,再根据buffer更改,生成正确版本返回。
- redo log节省随机写磁盘的IO消耗,change buffer节省随机读磁盘的消耗。
10. MySQL为什么有时候会选错索引?
- 删完再插入再查,时间变长,索引选择错误。是因为优化器选错索引。
- 选择索引错误,可以用analyze table t命令来重新统计索引信息。
- 矫正选择索引错误
- force index来强制选择索引,在优化器失效时,可以起到矫正作用。
- 修改SQL语句,order by b limit 1 -> order by b,a limit 1。a 1000行,也纳入排序,优化器知道a少,就索引a了
- 新建合适的索引