面试之--mysql常见知识点

如何设计一个数据库？

1.程序实例

存储管理、缓存机制、SQL解析、日志管理、权限划分、容灾机制、索引管理、锁管理。

2.存储（文件系统）

为什么要使用索引？

避免全表扫描，通过索引提升查询大幅速度。

什么样的信息能成为索引？

索引主要有普通索引、唯一索引、主键索引、组合索引、全文索引

索引的数据结构？

生产索引，建立二叉查找树进行二分查找。

生成索引，建立B Tree结构进行查找。

生成索引，建立B+ Tree结构查找。（mysql）

生成索引，建立Hash结构进行查找。

主要有Hash&BITMAP&B+Tree

B-Tree

根节点至少包括两个孩子

树中每个节点最多含有m个孩子（m>=2） （限定深度）

除根节点和叶节点外，其他每个节点至少有ceil(m/2)个孩子，ceil取上限

K[n]为关键字，P[m]为结点指向数量，p代表的是指针，k代表的关键字

a：ki(i=1…n)为关键字，且关键字按顺序升序排序K(i-1)<Ki

b：关键字的个数n必须满足：ceil(m/2)-1<=n<=m—1

c：非叶子结点的指针:p[1],p[2],…p[M];其中p[1]指向关键字小于K[1]的子树，

P[M]指向关键字大于K[M-1]的子树

查找效率 O（logn）

B+Tree

B+树是B数的变体，其定义基本与B树相同，除了：

非叶子节点的子树指针与关键字个数相同

非叶子节点(树枝第二个节点)的子树指针P[i],指向关键字值[K[i],K[i+1])的子树

非叶子子节点仅仅用来索引，数据都保存在叶子子节点中

所有叶子节点均有一个链指针指向下一个叶子结点

B+Tree更适合用来做存储索引

B+Tree查询更稳定，稳定的O（logN)

B+Tree更有利于对数据库的扫描,有利于范围查询。

Hash索引也可以考虑

Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中

缺点：

仅仅能满足”=“，”IN”,不能使用范围查询

无法被用来避免数据的排序操作

不能利用部分索引键查询​​

不能避免表扫描

遇到大量Hash值相等的情况后性能并不一定比Btree高

密集索引和稀疏索引的区别？

密集索引文件中的每个搜索码值都对应一个索引值

密集索引查找时间短，索引存储空间大

稀疏索引文件只为索引码的某些值建立索引项

稀疏索引占用存储空间小，查找时间长

InnoDB

若一个主键被定义，该主键则作为密集索引

若没有主键被定义，则该表的一个唯一非空索引则作为密集索引

若不满足以上条件，innodb内部会生成一个隐藏主键。

非主键索引存储相关键位和其对应的主键值，包含两次查找

一个表有且仅有一个密集索引

不支持Hash数据结构

MyISAM

在MyISAM中 不管是主键索引 唯一键索引 普通索引 其索引都属于稀疏索引

不支持Hash数据结构

存储表的数据，表的结构信息，表的索引信息

如何定位并优化慢查询Sql?

• ​根据慢日志定位慢查询sql

Show variables like ‘%quer%’ 查询慢查询文件所在位置

Show status like ‘%slow_queries%’; 查询慢查询个数

将慢日志打开set global slow_query_log=on;

set global long_query_time=1;

• 使用explain等工具分析sql

在sql 语句前添加explain 显示sql语句查询类型

Explain关键字段：type extra

1.type为 index > all 需要优化

2.Extra 为Using filesort ：表示mysql会对结果使用一个外部索引排序可能在内存或磁盘上排序，mysql无法利用索引排序称为文件排序。

Using temporary：查询使用临时表，常见于order by 和group by。

• 修改sql或者尽量让sql走索引

联合索引最左匹配原则的成因

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询（>、<、between、like)就停止匹配，比如a=3 and b=4 and c>5 and d=6，如果建立（a,b,c,d) d是用不到索引的。如果建立（a,b,d,c)的索引则都可以用到。

2.= in 可以乱序，比如a=1 and b=2 and c=3 建立（a,b,c)索引可以任意顺序，mysql优化器会帮你优化成索引识别的形式。

索引是建立的越多越好吗？

数据量小的表不需要建立索引，建立会增加额外的索引开销

数据变更需要维护索引，因此更多的索引意味着更多的维护成本

更多的索引意味着更多的存储空间

MyIsam与InnoDB关于锁方面的区别是什么？

读锁也叫共享锁，写锁叫排它锁。

• MyIsam默认的是表级锁，不支持行级锁

上了共享锁之后，可以再上共享锁，不支持上排它锁。

上了排它锁后什么锁都不允许。

• InnoDB默认的是行级锁，也支持表级锁

事务没有提交之前，上了写锁后还可以上读锁。

如果查询sql没有索引字段，则会触发表级锁，而不再使用行级锁。

MyISAM适合的场景？

频繁执行全表count语句

对数据进行增删改的频率不高，查询非常频繁

没有事务

InnoDB适合的场景？

数据增删改查相当频繁

可靠性要求比较高，要求支持事务

数据库锁的分类？

按锁的粒度划分，可分为表级锁，行级锁，页级锁

按锁级别划分，可分为共享锁，排它锁

按加锁方式划分，可分为自动锁，显式锁

按操作划分，可分为DML锁(操作数据的)，DDL锁（表结构的，如alter table）

按使用方式划分，可分为乐观锁、悲观锁

悲观锁：只有拿到锁才能访问数据，一般通过数据库的锁来实现，解决了事务的冲突，但效率低下。

乐观锁：只有在事务提交时，才会去判断是否发生了冲突。实现方式：版本号、时间戳。

数据库事务的四大特性

ACID

原子性（要么都做要么都不做）

一致性（事务执行的结果必须从一个一致性的状态到另一个一致性状态。）

隔离性（事务的执行不能被其它事务干扰）

持久性（事务一旦提交，对数据库中的数据的改变是永久的）

事务隔离级别以及各级别下的并发访问问题？

Select @ @tx_isolation 查询事务隔离级别

set session transaction isolation level read uncommitted;

Start transaction 开启事务

Commit 提交事务

Serializable(串行化)：可避免脏读，不可重复读，可避免幻读。会自动加上锁

Repeatable read（可重复读）：可避免脏读，不可重复读，mysql默认的

Read-commited（读已提交）：可避免脏读 oracle默认的

Read-uncommitted（读未提交）都不可避免

更新丢失(更新被覆盖) mysql所有事务隔离级别在数据库层面上均可避免

脏读 （读到未提交的数据） 设置为Read-commited事务隔离级别以上可避免

不可重复读（a读取数据，b更新提交，a再读不一致，两次读取的数据不一致，对同一个数据的操作）设置为Repeatable read（默认隔离级别）事务隔离级别可避免,读取事务开始时，不能再对其进行修改

幻读 （一个事务a读取后，另外一个事务插入，删除等，a再操作就会出现幻觉） SERIALIZABLE事务隔离级别可避免

级别设计的越高越好吗？

不是，越高会影响并发性能。

InnoDB可重复读隔离级别下如何避免幻读？

• 表现：快照读（非阻塞读）—伪MVCC

在Repeatable-read隔离级别下，如果现在别的事务操作数据之前，创建了快照读，不管别的事务怎么操作数据，当前事务再次使用快照读时，查到的依旧是老版本的数据。

• 内在：next-key锁（行锁+grap锁）

next-key锁:由行锁和gap锁组成，行锁就是对记录加的锁。

grap锁：1.防止同一个事务两次当前读出现幻读，2.Repeatable-read级别以上支持Gap锁。3.在rr级别对主键索引或者唯一索引会用Gap锁的情况，如果部分命中或全不命中，则会加Gap锁。全部命中，则不会加Gap锁，只会加记录锁。4.gap锁会用在非唯一索引或者不走索引的当前读中。非唯一索引gap锁:数据左右两侧，左开右闭合(6,9]。

不走索引：对整张表上锁

真正防止幻读发生是因为事务在Repeatable-read隔离级别以上（包括）对数据加了next-key锁。next-key锁由行锁和gap锁组成。

RC（读已提交）、RR（可重复读）级别下的InnoDB的非阻塞读如何实现？

RR下，事务读取数据的时机非常重要，第一次读取后数据会创建快照，以后会读取快照，事务提交前，再读取都是读取第一次读取的数据。

RC下，每一次数据读取，都会创建一个新的快照，所以RC能读取到别人提交的结果。

语法部分？

GROUP BY

HAVING 与group by搭配使用

Select student_id,avg(score) from score group by student_id having avg(score)>60

统计相关：COUNT ，SUM，MAX，MIN，AVG

索引什么时候会失效？

• 最左匹配原则，联合索引（a,b,c） 查询的时候用到b=? and c=? 就会用不到索引

• 在索引上做（计算，函数）会导致索引失效

• 在 < > between and 右侧中的字段索引会失效

• Is not null is null 会导致无法使用索引

• like查询以%开头会失效

• 字符串不加单’’引号会失效

• 条件中有or会失效，除非将or中每个列都加上索引