Mysql面试题
本文主要以面经中的面试题为主，整建面试题库
1.varchar 和char的区别
- CHAR的长度是不可变的，而VARCHAR的长度是可变的，也就是说，定义一个CHAR[10]和VARCHAR[10],如果存进去的是‘ABCD’, 那么CHAR所占的长度依然为10，除了字符‘ABCD’外，后面跟六个空格，而VARCHAR的长度变为4了，取数据的时候，CHAR类型的要用trim()去掉多余的空格，而VARCHAR类型是不需要的。
- CHAR的存取速度要比VARCHAR快得多，因为其长度固定，方便程序的存储与查找；但是CHAR为此付出的是空间的代价，因为其长度固定，所以难免会有多余的空格占位符占据空间，可以说是以空间换取时间效率，而VARCHAR则是以空间效率为首位的。
- CHAR的存储方式是，一个英文字符（ASCII）占用1个字节，一个汉字占用两个字节；而VARCHAR的存储方式是，一个英文字符占用2个字节，一个汉字也占用2个字节。
- 两者的存储数据都是非unicode的字符数据。
2.float 和 double 的区别是什么？
-
float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值，float和double都是浮点型，而decimal是定点型；
-
MySQL 浮点型和定点型可以用类型名称后加（M，D）来表示，M表示该值的总共长度，D表示小数点后面的长度，M和D又称为精度和标度，如float(7,4)的 可显示为-999.9999，MySQL保存值时进行四舍五入，如果插入999.00009，则结果为999.0001。
-
FLOAT和DOUBLE在不指 定精度时，默认会按照实际的精度来显示，而DECIMAL在不指定精度时，默认整数为10，小数为0
-
FLOAT只保证6位有效数字的准确性，所以FLOAT(M,D)中，M<=6时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。
-
D取值范围为0~30，同时必须<=M。double只保证16位有效数字的准确性，所以DOUBLE(M,D)中，M<=16时，数字通常是准确的。如果M和D都有明确定义，其超出范围后的处理同decimal。
3.事务包括四大特性：ACID
- 原子性（Atomicity）
一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被恢复（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。事务是最小的工作单元，不可再分。
- 一致性（Consistency）
事务开始前和结束后，数据库的完整性没有被破坏 。比如A向B转账，不可能A扣了钱，B却没收到。比如A向B转账，不可能A扣了钱，B却没收到。
- 隔离性（Isolation）
同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如A正在从一张银行卡中取钱，在A取钱的过程中，B不能向这张卡转账。
- 持久性（Durability）
事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。
4.数据库的三范式是什么？
- 第一范式：任何一张表都应该有主键，并且每一个字段原子性不可再分。（原子性）
- 第二范式：建立在第一范式的基础之上，主键必须要有唯一性，所有非主键字段完全依赖主键，不能产生部分依赖。
多对多？三张表，关系表两个外键。（完全） - 第三范式：建立在第二范式的基础之上，所有非主键字段直接依赖主键，不能产生传递依赖。第三范式是确保每列都和主键列直接相关，而不是间接相关，即限制列的冗余性。如果一个关系满足第二范式，并且除了主键以外的其他列都依赖于主键列，列和列之间不存在相互依赖关系，则满足第三范式。（直接）一对多？两张表，多的表加外键。比如员工表和部门表就得两张表，外键依赖.
5.事务隔离性存在隔离级别，理论上隔离级别包括4个
MySQL默认的隔离级别是可重复读（repeatable-read），MySQL事务隔离级别有以下四种：
| 事务隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| 读未提交（read-uncommitted） | 是 | 是 | 是 |
| 读已提交（read-committed） | 否 | 是 | 是 |
| 可重复读（repeatable-read） | 否 | 否 | 是 |
| 串行化（serializable） | 否 | 否 | 否 |
- 读未提交（read-uncommitted）：对方事务还没有提交，我们当前事务可以读取到对方未提交的数据。
读未提交存在脏读（Dirty Read）现象：表示读到了脏的数据。 - 读已提交（read-committed）：对方事务提交之后的数据我方可以读取到。
种隔离级别解决了: 脏读现象没有了。
读已提交存在的问题是：不可重复读。 - 可重复读（repeatable-read）：这种隔离级别解决了：不可重复读问题。
这种隔离级别存在的问题是：读取到的数据是幻象。 - 串行化（serializable）：解决了所有问题。效率低。需要事务排队。
- 脏读 ：表示读取到了还未提交的数据
- 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的
- 幻读 ：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。
- 幻读和不可重复读的区别：都是读取的结果不一样了。不可重复读的重点是修改，幻读的重点在于新增或者删除
6.Mysql索引的原理？B+树？
数据的存储方式主要的有两种，数组和链表，（树其实就可以看做链表的高阶形式）
- 数组天生就有索引查找快，增删慢
- 而链表反之，就没有索引所有查找慢，但是存储的方式的原因增删十分方便
前两种主要代表就是list集合的几个实现，树的出现就是数组的短板无法改进，树采用链的形式，但每个节点中的存储的节点有两个所以形成了树。就像人的左右手一样。典型的就是TreeMap底层为红黑树，因为map以键值对存储数据。键是唯一的，所以可以用键来充当索引，就形成了树，普通的树，相继有了平衡二叉树，但是这种导致树很大很深，所以采用二三树，就是，每个节点两个值，就会有三条链，也就是红黑树的原型，红黑树就是一个二三树，不过用普通树进行表示，红的为节点内的链条，黑的为节点之间的链条。因为二三树是在插入的时候向上生长，所以十分复杂，红黑树算是取比较好地方进行优化，将普通的二叉树和二三树进行了良好的结合。B树是每个节点上的值允许两个以上都是b树。都是为了减少树的深度。磁盘的存储数据就是使用的就是B树。
重点来了，B+树的非叶子节点不存储数据，只存储索引，所有的叶子节点，组成了一个有序的链表。
一般B+树的高度为3-4层 ，因为3层就可以存放2千万的数据了，每个节点16k，在计算机中磁盘存储数据最小单元是扇区，一个扇区的大小是512字节，而文件系统（例如XFS/EXT4）他的最小单元是块，一个块的大小是4k，而对于我们的InnoDB存储引擎也有自己的最小储存单元——页（Page），一个页的大小是16K。
7.Mysql索引的定义，和分类
索引(Index)是帮助MySQL高效获取数据的一种数据结构，而且是排好序的数据结构，索引存储在磁盘文件里。
- MySQL索引主要有两种结构： B+Tree索引和Hash索引
我们平常所说的索引，如果没有特别说明，一般都是指B+Tree结构的索引。索引能极大的减少存储引擎需要扫描的数据量，从而提高数据的检索速度。
- 主键索引，也叫集聚索引，也叫聚簇索引（针对innodb而言）。辅助索引，也叫二级索引，也叫非集聚索引，也叫非聚簇索引
- 每个存储引擎为InnoDB的表都有一个特殊的索引，叫聚集索引。聚集索引并不是一种单独的索引类型，而是一种数据存储方式。当表有聚集索引的时候，它的数据行实际上存放在叶子页中。一个表不可能有两个地方存放数据，所以一个表只能有一个聚集索引。因为是存储引擎负责实现索引，因此不是所有的存储引擎都支持聚集索引。InnoDB表中聚集索引的索引列就是主键，所以聚集索引也叫主键索引。
- 对于InnoDB表，在非主键列的其他列上建的索引就是二级索引（因为聚集索引只有一个）。二级索引可以有0个，1个或者多个。二级索引和聚集索引的区别是什么呢？二级索引的节点页和聚集索引一样，只存被索引列的值，而二级索引的叶子页除了索引列值，还存这一列对应的主键值。
- 主键索引: 数据列不允许重复，不允许为NULL，一个表只能有一个主键。
- 唯一索引: 数据列不允许重复，允许为NULL值，一个表允许多个列创建唯一索引。
可以通过 ALTER TABLE table_name ADD UNIQUE (column); 创建唯一索引
可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2); 创建唯一组合索引
- 普通索引: 基本的索引类型，没有唯一性的限制，允许为NULL值。
可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引
可以通过ALTER TABLE table_name ADD INDEX index_name(column1, column2, column3);创建组合索引
- 全文索引： 是目前搜索引擎使用的一种关键技术。
可以通过ALTER TABLE table_name ADD FULLTEXT (column);创建全文索引
8.常见的搜索引擎
-
MyISAM
- 优点：可被压缩，节省存储空间。并且可以转换为只读表，提高检索效率。只支持表级锁
- 缺点：不支持事务。
-
InnoDB
- 优点：支持事务、行级锁、外键等。这种存储引擎数据的安全得到保障。
-
MEMORY
- 缺点：不支持事务。数据容易丢失。因为所有数据和索引都是存储在内存当中的。
- 优点：查询速度最快。
9.Mysql的锁
数据库管理系统（DBMS）中的并发控制的任务是确保在多个事务同时存取数据库中同一数据时不破坏事务的隔离性和统一性以及数据库的统一性。乐观并发控制（乐观锁）和悲观并发控制（悲观锁）是并发控制主要采用的技术手段。
- 乐观锁
每次取数据的时候都认为他人不会对其修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号机制和CAS算法实现。 - 悲观锁
悲观锁也如同它的名字一样，总是假设比较坏的情况，每次取数据的时候都认为他人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给一个线程使用，其它线程阻塞，用完后再把资源转让给其它线程）。
从锁的类别上来讲，有共享锁和排他锁。
- 共享锁,也叫读锁。当用户要进行数据的读取时，对数据加上共享锁。共享锁可以同时加上多个。
- 排他锁: 又叫做写锁。 当用户要进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，他和其他的排他锁，共享锁都相斥。
在关系型数据库中，可以按照锁的粒度把数据库锁分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)和页级锁(BDB引擎 )。
-
行级锁 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。行级锁分为共享锁 和 排他锁。
特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。 -
表级锁 表级锁是MySQL中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MYISAM与INNODB都支持表级锁定。表级锁定分为表共享读锁（共享锁）与表独占写锁（排他锁）。
特点：开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。 -
页级锁 页级锁是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组记录。
10.主从复制
MySQL 主从复制是基于主服务器在二进制日志跟踪所有对数据库的更改。因此，要进行复制，必须在主服务器上启用二进制日志。
每个从服务器从主服务器接收已经记录到日志的数据。当一个从服务器连接到主服务器时，它通知主服务器从服务器日志中读取最后一个更新成功的位置。
从服务器接收从那时发生起的任何更新，并在主机上执行相同的更新。然后封锁等待主服务器通知的更新。
从服务器执行备份不会干扰主服务器，在备份过程中主服务器可以继续处理更新。
MySQL 的主从复制工作过程大致如下：
-
从库生成两个线程，一个 I/O 线程，一个 SQL 线程；
-
I/O 线程去请求主库的 binlog，并将得到的 binlog 日志写到 relay log(中继日志) 文件中；
-
主库会生成一个 log dump 线程，用来给从库 I/O 线程传 binlog；
-
SQL 线程会读取 relay log 文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致；
11.MVCC
MVCC 是通过数据行的多个版本管理来实现数据库的并发控制，简单来说它的思想就是保存数据的历史版本。
我们可以通过比较版本号决定数据是否显示出来（具体的规则后面会介绍到），读取数据的时候不需要加锁也可以保证事务的隔离效果。
通过 MVCC 我们可以解决以下几个问题：
（1）读写之间阻塞的问题，通过 MVCC 可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提升事务并发处理能力。
（2）降低了死锁的概率。这是因为 MVCC 采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行。
（3）解决一致性读的问题。一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到这个时间点之后事务提交的更新结果。
12.索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理很简单，就是把无序的数据变成有序的查询
-
把创建了索引的列的内容进行排序
-
对排序结果生成倒排表
-
在倒排表内容上拼上数据地址链
-
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
13.联合索引是什么？为什么需要注意联合索引中的顺序？
MySQL可以使用多个字段同时建立一个索引，叫做联合索引。在联合索引中，如果想要命中索引，需要按照建立索引时的字段顺序挨个使用，否则无法命中索引。
具体原因为:
MySQL使用索引时需要索引有序，假设现在建立了"name，age，school"的联合索引，那么索引的排序为: 先按照name排序，如果name相同，则按照age排序，如果age的值也相等，则按照school进行排序。
当进行查询时，此时索引仅仅按照name严格有序，因此必须首先使用name字段进行等值查询，之后对于匹配到的列而言，其按照age字段严格有序，此时可以使用age字段用做索引查找，以此类推。因此在建立联合索引的时候应该注意索引列的顺序，一般情况下，将查询需求频繁或者字段选择性高的列放在前面。此外可以根据特例的查询或者表结构进行单独的调整。
14.sql优化
大表数据查询，怎么优化
- 优化shema、sql语句+索引；
- 第二加缓存，memcached, redis；
- 主从复制，读写分离；
- 垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；
- 水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；
超大分页怎么处理？
MySQL并不是跳过offset行，而是取offset+N行，然后返回放弃前offset行，返回N行，那当offset特别大的时候，效率就非常的低下，要么控制返回的总页数，要么对超过特定阈值的页数进行SQL改写。
正例：先快速定位需要获取的id段，然后再关联：
SELECT a.* FROM 表1 a, (select id from 表1 where 条件 LIMIT 100000,20 ) b where a.id=b.id
15.创建索引的原则
1） 最左前缀匹配原则，组合索引非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2）较频繁作为查询条件的字段才去创建索引
3）更新频繁字段不适合创建索引
4）若是不能有效区分数据的列不适合做索引列(如性别，男女未知，最多也就三种，区分度实在太低)
5）尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。
6）定义有外键的数据列一定要建立索引。
7）对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。
8）对于定义为text、image和bit的数据类型的列不要建立索引。
16.创建索引的三种方式，删除索引
第一种方式：在执行CREATE TABLE时创建索引
CREATE TABLE user_index2 (
id INT auto_increment PRIMARY KEY,
first_name VARCHAR (16),
last_name VARCHAR (16),
id_card VARCHAR (18),
information text,
KEY name (first_name, last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
第二种方式：使用ALTER TABLE命令去增加索引
ALTER TABLE table_name ADD INDEX index_name (column_list);
第三种方式：使用CREATE INDEX命令创建
CREATE INDEX index_name ON table_name (column_list);
删除索引
alter table user_index drop KEY name;
alter table user_index drop KEY id_card;
alter table user_index drop KEY information;
28万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
