面试 MySQL

索引

1.索引是什么？有什么作用以及缺点
　　答：索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。也可以理解为索引就是一本书的目录，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚集索引那么需要的空间就会更大。
2.索引的目的是什么
　　答：为了高效的查找得到我们所需要的数据，减少分组和排序时间，提高我们的mysql的性能
3.索引对数据库系统的负面影响
　　答：虽然索引对于数据库的查询提高了效率，但一定程度上增加了空间的占用，同时写入的速度降低了不少，和原有写入数据相比较，多了一步去维护索引的操作。
4.建立索引的原则
　　答：选择唯一性索引，为经常需要查询、排序、分组和联合操作的字段建立索引，限制索引的数目，最左前缀匹配原则（非常重要的原则），尽量选择区分度高的列作为索引，字段尽力设置不为null，索引列上不计算。

基础

5.主键、外键和唯一索引的区别
　　答：定义：
　　　　　　主键：唯一标识一条记录，不能有重复的，不允许为空
　　　　　　外键：表的外键是另一表的主键, 外键可以有重复的, 可以是空值
　　　　　　索引：该字段没有重复值，但可以有空值
　　　　作用：
　　　　　　主键：用来保证数据完整性
　　　　　　外键：用来和其他表建立联系用的
　　　　　　索引：是提高查询排序的速度
　　　　个数：
　　　　　　主键：主键只能有一个
　　　　　　外键：一个表可以有多个外键
　　　　　　索引：一个表可以有多个唯一索引
6.MySQL底层实现，MySQL有什么引擎
　　答：mysql底层采用B+ tree的存储结构，也就是只有叶子节点携带真实数据，每个节点大小为16Kb，大致三层的B+tree就可以存2000W左右的数据，大大的减少了磁盘的IO。我们常见的存储引擎有InnoDB和MyISAM。

拓展：什么是B+树，这里从二叉查找树（极端成为链表）---二叉平衡树（存储数据少，树太深）--B+树（+代表了不是存2了，而是存多个）
1、是多叉而不是二叉了，使用多叉的目的是降低树的高度;
2、每个节点不再只是存储一个key了，可以存储多个key；
3、非叶子节点存储key，叶子节点存储key和数据。
4、叶子节点两两相连，为顺序查询提供了帮助
总结：  多叉平衡树？
1、B+树的非叶子节点只是存储key，占用空间非常小，因此每一层的节点能索引到的数据范围更加的广。换句话说，每次IO操作可以观看更多的数据；
2、叶子节点两两相连，符合磁盘的预读特性。如图三中存储50和55的叶子节点，它有个指针指向了60和62这个叶子节点，那么当我们从磁盘读取50和55对应的数据的时候，由于磁盘的预读特性，会顺便把60和62对应的数据读取出来。这个时候属于顺序读取，而不是磁盘寻道了，加快了速度。
3、支持范围查询，而且部分范围查询非常高效，原因是数据都是存储在叶子节点这一层，并且有指针指向其他叶子节点，这样范围查询只需要遍历叶子节点这一层，无需整棵树遍历。

7.InnoDB和MyISAM区别，InnoDB替代了MyISAM，那么MyISAM是否一无是处。
　　答：InnoDB支持事务，支持行锁，在磁盘上只存两个文件，一个是索引文件，另一个是数据文件，在B+Tree的主键索引上，叶子节点携带全部数据，MyISAM不支持事务，不支持行锁，磁盘上存了三个文件，一个是索引文件，另一个是数据文件，还有一个存放的对应关系文件，从查询的角度来说，InnoDB没有MyISAM的单条查询速度高，MyISAM采用Hash存储回行得到数据的查询过程，单MyISAM对于范围查询不是很友好。因此我们可以看出InnoDB用的更广一些，但同时MyISAM对于非范围查询的高效还是有很大用处的，而且MyISAM对于表内的总数查询，维护了单独的数据，也是很高效的。

innodb支持事务，myisam不支持
innodb支持行级锁，myisam支持表级锁
innodb支持外键，myisam不支持
innodb支持MVCC，myisam不支持
innodb不支持全文索引，myisam支持
应用场景：
MyISAM：做很多count计算、插入不频繁、查询非常频繁、没有事务、查询快
InnoDB:对事务要求比较高、可靠性要求高、表更新相当频繁、并发写入高
DELETE操作：
 MyISAM:先drop表，然后重建表
 InnoDB:一行一行删除
查询表的行数不同：
 MyISAM:只是简单的读出保存好的行数
 InnoDB:不保存具体行数，执行count(*)时要扫描一整个表来计算有多少行

8.什么是事务，事务特性
　　答：事务是指作为单个逻辑工作单元执行的一系列操作，要么完全地执行，要么完全地不执行。在我们的mysql里也是如此，也就是我们的ACID原则。

A原子性，把一系列的动作视为一个最小的操作（原子操作） 　　
C一致性，从一个状态到另一个状态是一致的，
I隔离性：事务与事务之间是不可见相互隔离的， 　　
D持久性：一旦事务提交，则所做修改就会被永久保存到数据库中。

优化

9.如何设计一个高并发的系统（对于mysql来讲）
　　答：这个后面会结合别的技术来说，只讲mysql不太好说。大致就是我们首先应该考虑到的是读写分离操作（过几天博客里会详细说这个），再就是我们常见的分库分表操作，水平切分垂直切分。还可以加入缓存redis操作。合理使用索引，explain进行sql优化。
10.锁的优化策略
　　答：优化，也就是最小力度的锁我们的数据，也就是行锁，InnoDB的行锁其实是加在索引字段的，避免行锁的升级为表锁，再就是我们尽量避免间隙锁，尽量避免我们的范围修改，如果真的必须范围修改，那么应该尽可能的缩小到最小的范围。

拓展： 　　
表锁 特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
我们在编辑表，或者执行修改表的事情了语句的时候，一般都会给表加上表锁，可以避免一些不同步的事情出现，表锁分为两种，一种是读锁，一种是写锁。
 
行锁 特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。且行锁支持事务。
行为：
1、当我们对一行进行更新但是不提交的时候，其他进程也对该行进行更新则需要进行等待，这就是行锁。
2、如果我们对一行进行更新，其他进程更新别的行是不会受影响的。
行锁升级为表锁：
当我们的行锁涉及到索引失效的时候，会触发表锁的行为。

11.优化SQL的方法
　　答：设置一个主键索引，需主要主键索引一般没有真正业务含义，使用int类型自动增长的，而且不能为null，非主键索引字段优先考虑区分度高的业务情况和最左前缀原则，设置为null。如果真的数据量不大，不建议加索引，反而会影响效率的。选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置NOTNULL，使用连接(JOIN)来代替子查询，适用联合(UNION)来代替手动创建的临时表。学会使用explain进行SQL分析，实在不行可以打开trace进行分析SQL情况，用完记得关闭。
12.谈谈三大范式，什么时候使用反范式设计
　　答：第一范式（1NF）：确保每列保持原子性即列不可分
　　　　第二范式（2NF）：属性完全依赖于主键，也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
　　　　第三范式（3NF）：属性和主键不能间接相关（减少数据冗余，这样就可以通过主外键进行表之间连接）
　　比如我们表比较多，需要关联时，但我们的A表只需要关联B表的一个字段，而且每次都需要关联查询，这时我们可以采用A表放置一个冗余字段来存B表的那个字段。这个操作其实就是一个反范式的。

13.说几个mysql中你常用的函数答：
　　答：sum、count 、avg、min、max
14.varchar(100)和varchar(200)的区别
　　答：占用内存空间大小肯定是不一致的，但是占用我们磁盘的大小是一致的，我们存储字符串"abc"，完全是一样的磁盘空间，但是对于varchar(100)来说，接收到的字符串长度太长了就会报错的。后面的数字代表可存储的字节数。
15.varchar(20)和int(20)中的20含义一样吗
　　答：显然不一致，int(M) M表示的不是数据的最大长度，只是数据宽度，并不影响存储多少位长度的数据；varchar(M) M表示的是varchar类型数据在数据库中存储的最大长度，超过则不存；
16.如何开启慢日志查询？
　　答：有2种方式，一是修改mysql的配置文件，二是通过set global语句来实现。slow_query_log = ON，打开日志，long_query_time = 2，设置时间，2秒就算是慢查询，然后重启mysql服务即可，进入mysql控制台，输入SET GLOBAL slow_query_log = ‘ON’;SET GLOBAL long_query_time = X;不需要重启服务就可以得到慢查询日志。

sql语句

3.2 drop、delete、truncate的区别
drop直接删掉表、truncate删除表中的数据，再插入数据自增长id又从1开始,delete删除表中的数据，可以加where语句

删除数据库表 drop table 数据库名.表名 或者 drop table 表名
DELETE FROM 表名称 WHERE 列名称 = 值
也可以删除所有行 DELETE * FROM table_name 注意表的结构是完整的

 delete语句删除表中的某一行。并同时将改行的删除操作作为事务记录在日志中，以便后续进行回滚；truncate则是一次性删除表中所有的数据并不会单独把操作记录日志保存，是不能恢复的
 表和索引所占的空间：truncate之后空间会恢复初始大小，delete操作不会减少表索引所占的空间，drop将释放所有的占用空间 一般而言
drop>truncate>delete truncate和delete只删除数据，drop直接删除整个表
 在没有备份的情况下，慎用drop和truncate truncate速度快、效率高

3.4 内连接、、左连接（左外）、右连接（右外）、全连接（全外）
内连接：

说明：组合两个表中的记录，返回关联字段的记录，返回两个表交集部分

关键字：innner join on
语句：select * from a_table a inner join b_ table b on a.id = b.id
左连接：

说明：left join 是left outer join的简写，左外连接是外连接的一种，左外连接：左表的数据全显示，右表显示符合搜索条件的记录，右表记录不足的地方均为null

关键字：left join on / left outer join on
语句:select * from a_table a left join b_table b on a.id = b.id
右连接：

说明：right join 是right outer join的简写，右外连接是外连接的一种，右外连接：右表的数据全显示，左表显示符合搜索条件的记录，左表记录不足的地方均为null

关键字：right join on / right outer join on
语句:select * from a_table a right join b_table b on a.id = b.id
全连接： 目前mysql不支持

3.10 varchar和char的区别以及varchar(50) 50的含义
区别：char是一种固定长度的类型，varchar则是一种可变长度的类型
含义：最多存放50个字符，varchar(50)和varchar(200)存储hello所占空间一样，但后者在排序时会消耗更多的内存，因为order by col采用fixed_length计算col长度
int(20)20的含义：是指显示字符的长度，最大为255，仍占4字节存储，存储范围不变

3.5 行级锁、表级锁、乐观锁、悲观锁
加锁原因：放置更新丢失，并不能单靠数据库事务控制器来解决，需要应用程序对更新的数据加必要的锁来解决

表级锁：每次操作都锁定在整张表，开销少，加锁块，不会出现死锁，锁定粒度大，发生锁冲突的概率最高，并发度最低
行级锁:每次操作锁定一行数据，开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突的概率最低，并发度最高
页面锁：开销和加锁时间介于两者之间，会出现死锁，并发度一般
悲观锁：假定会发生并发冲突，屏蔽一切可能违反数据完整性的操作
乐观锁：假设不会发生并发冲突，只在提交操作时检查是否违反数据完整性

参考文章：
https://www.cnblogs.com/cxiaocai/p/11634257.html
https://www.cnblogs.com/JetpropelledSnake/p/9397033.html
https://blog.csdn.net/u013090299/article/details/80438704