mySQL面试总结篇上（附答案）

**

1、数据库的三范式：

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就叫做范式。范式就是符合某一种设计要求的总结，要想设计一个结构合理的关系型数据库，必须满足一定的范式。
在实际开发中最常见的设计范式有三个：
（1）第一范式（确保每列保持原子性）
第一范式是最基本的范式。如果数据库表中的所有字段值都是不可分解的原子值，就说明该 数据库满足第一范式。
第一范式的合理遵循需要根据系统给的实际需求来确定。比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成为一个数据库表的字段就行，但是如果系统经常访问“地址”属性中的“城市”部分，那么一定要把“地址”这个属性重新拆分为省份、城市、详细地址等多个部分来进行存储，这样对地址中某一个部分操作的时候将非常方便，这样设计才算满足数据库的第一范式。
（2）第二范式在第一范式的基础上更进一层，第二范式包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如要设计一个订单信息表，因为订单中可能会有多种商品，所以要将订单编号和商品编号作为数据库表的联合主键。
（3）第三范式（确保每列都和主键列直接相关，而不是间接相关）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。即不能存在：非主键列A依赖于非主键列B，非主键B依赖于主键的情况。
比如在设计一个订单数据表的时候，可以将客户编号作为一个外键和订单表建立相应的关系，而不可以在订单表中添加关于客户其他信息（比如姓名、所属公司）的字段。
第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分他们的关键点在于，2NF是非主键列完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。

2、一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 mysql 数据库，又插入了一条数据，此时 id 是几？

这是需要看mysql的引擎是什么了，如果是MYISAM,则结果是8，如果是innodb的话，则结果是6；因为两种存储引擎所存储的最大ID记录的方式不同，MYISAM表将最大的id记录到数据文件里了，重启之后自增主键的最大id值不会丢失，而innodb则把最大的id值记录到内存中，重启之后最大id值将会丢失。

3、如何获取当前数据库的版本？

答：select version();

4、说一下ACID是什么？

事务：事务是访问并可能更新数据库中各种数据项的一个单独的程序执行单元（unit）。
原子性（atomicity）
表示一个事物内所有操作都是一个整体，要么全部成功，要么全部失败；
一致性（consistency）
表示事务内有一个操作失败了，所有更改过的数据都必须会滚到修改前的状态；一个事物执行之前和执行之后，数据库数据必须保持一致性状态。数据库的一致性状态由用户来负责，由并发控制机制实现。
隔离性（isoation）
事务是并发控制机制，它们交错使用时也能提供一致性。隔离让我们隐藏来自外部世界未提交的状态变化，一个失败的事务不应该破坏系统的状态。隔离是通过用悲观或乐观锁机制实现的。
事务是并发控制机制，他们交错使用时也能提供一致性。隔离让我们隐藏来自外部世界未提交的状态变化，一个失败的事务不应该破坏系统的状态。隔离是通过用悲观或乐观锁机制实现的。
事务隔离级别从低到高：
读取未提交
读取已提交
可重读
序列化
持久性（durability）
一个成功的事务将永久性的改变系统的状态，所以在它结束之前，所有导致状态的变化都记录在一个持久的事务日志中。如果我们的系统突然崩溃或者断电，那么所有未完成已提交的事务可能会重演。

5、char 和 varchar 的区别是什么？

char的字符类型是固定长度，而varchar的长度是可变的，比如：当定义一个char[6]和varchar[6],如果存进去的是‘csdn’，那么char所占的长度依然是6，剩下地跟两个空格，而varchar会马上把长度变为4，取数据时，char类型需要用trim（）去掉多余的空格，而varchar不需要。
但char的存取速度还是要比varchar快得多，因为它的长度固定，方便程序的存储和查找；但char为此付出了空间的代价，varchar则是把空间效率放在首位的。
两者的存储方式也有所不同：char对英文字符（ASCII）占用一个字节，对一个汉字占用两个字节；而varchar对每个英文字符和汉字都占用两个字节。两者的存储数据都是非Unicode的字符数据。

6、float 和 double 的区别是什么？

float数值类型用于表示单精度浮点数值，而double数值类型用于表示双精度浮点数值。
两者都是浮点型，而decimal是定点型。decimal是精确数，可以指定精度。当数据值一定要按照指定精确存储时，可以用带有小数的decimal数据类型来存储数字。
float,double容易产生误差，对精确度要求比较高时，建议使用decimal来存，decimal在mysql内存是以字符串存储的，用于定义货币要求精确度高的数据。
float占4个字节，double占8个字节，decimail(M,D)占M+2个字节。M为精度，D为标度。

7、mysql 的内连接、左连接、右连接有什么区别？

1、内连接：
也被称为自然连接，只有两个表相匹配的行才能在结果集中出现。返回的结果集选取了两个表中所有相匹配的数据，舍弃了不匹配的数据。内连接容易造成信息的丢失。
关键字：INNER JOIN …ON
语句：select * from table1 inner join table2 on table1.column = table2.column
2、左连接（左外链接）：
是外连接的一种，左连接显示左表所有数据，右表没有对应的数据用NULL补齐，多了的数据删除。
关键字：LEFT OUTER JOIN …ON
语句：select * from table1 left join table2 on table1.column = table2.column
3、右链接
右连接显示右表所有数据，左表没有对应的数据用NULL对齐，多了的数据删除
关键字： RIGHT OUTER JOIN …ON
语句：select * from table1 right join table2 on table1.column = table2.column

8、mysql 索引是怎么实现的？

mysql的索引分为单列索引(主键索引,唯索引,普通索引)和组合索引.

单列索引:一个索引只包含一个列,一个表可以有多个单列索引.
（1）普通索引：最基本的索引。
sql语句：CREATE INDEX indexname ON tablename(‘字段名’(length))
或者 ALTER TABLE tablename ADD INDEX indexname(‘字段名’(length))
如果是char，varchar类型，则length可以小于字段的实际长度，如果是blob和text类型就必须指定长度。
（2）唯一索引：与普通索引类似，但是唯一索引要求所有的类的值是唯一的，这一点和主键索引一样，但主键索引不允许有空值，而唯一索引允许。
与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值。
sql语句：CERATE UNIQUE INDEX indexname ON tablename(‘字段名’(length))
或者 ALTER TABLE tablename ADD UNIQUE(column_list).
（3）主键索引，不允许有空值，在B+TREE中的innodb引擎中，主键索引起到了至关重要的地位。
主键索引建立的规则是int优于把人插入，一般在建表的时候创建，最好是与标的字段不相关的列或者业务不相关的列，一般会设为int而且是AUTO_INCREMENT自增类型的
组合索引:一个组合索引包含两个或两个以上的列.
一个表中包含多个单列索引不代表是组合索引，组合索引是包含多个字段但是只有索引名称。
sql语句：CREATE INDEX indexname ON tablename (‘字段名’(length),‘字段名’(length),…);
如果你建立了一个组合索引，这个组合索引包含1,2,3列，那么实际上它包含三个索引(1),(1,2)(1,2,3);
在使用组合索引查询的时候要遵循mysql组合索引的“最左前缀”，以及索引where时的条件要按照建立缩印的时候字段的排序方式。
1、索引必须按你组合索引设置的最左列开始查询，如果不按索引最左列开始查询，则不能使用索引。
2、查询中的某个列有范围查询（like），则其右边的所有列都无法使用组合索引查询。
3、不能跳过某个字段来进行查询，这样利用不到索引
全文索引
文本字段上（text）如果建立的是普通索引，那么只有对文本的字段前面的字符进行索引，其字符大小根据索引建立索引时申明的大小来规定。如果文本中出现多个一样的字符，而且需要查找的话，那么其条件只能是where column lick‘%xxxx%’，模糊查询，会使索引失效。这时全文索引就可以显示出作用了。

sql语句：ALTER TABLE tablename ADD FULLTEXT(column1,column2);

有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)
这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。
索引的删除：
sql语句：DROP INDEX indexname ON ‘tablename’;
使用索引的优点
1、通过建立唯一索引或主键索引，保证数据库表中每一行数据的唯一性。
2、建立索引可以大大提高检索的数据，以及减少表的检索行数。
3、作为表的连接条件，可以加快表与表直接的相连。
4、在分组和排序字句中进行数据检索，可以减少查询时间中分组和排序时所消耗的时间（数据库的记录会重新排序）
5、建立索引，在查询中使用索引，可以提高性能。
使用索引的缺点
1、在创建索引和维护索引，会耗费时间，并且会随着数据量的增加而增加
2、索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间。
3、当对表的数据进行INSERT,UPDATE,DELETE 的时候，索引也要动态的维护，这样就会降低数据的维护速度，（建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快）。
使用索引需要注意的地方
在建立索引的时候应该考虑索引应该建立在数据库表中的某些列上面 哪一些索引需要建立,哪一些所以是多余的.
一般来说,
1.在经常需要搜索的列上,可以加快索引的速度
2.主键列上可以确保列的唯一性
3.在表与表的而连接条件上加上索引,可以加快连接查询的速度
4.在经常需要排序(order by),分组(group by)和的distinct 列上加索引 可以加快排序查询的时间, (单独order by 用不了索引，索引考虑加where 或加limit)
5.在一些where 之后的 < <= > >= BETWEEN IN 以及某个情况下的like 建立字段的索引(B-TREE)
6.like语句的 如果你对nickname字段建立了一个索引.当查询的时候的语句是 nickname lick ‘%ABC%’ 那么这个索引讲不会起到作用.而nickname lick ‘ABC%’ 那么将可以用到索引
7.索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串
8.使用短索引,如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
9.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描
10.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

什么情况下不创建索引
1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.定义为text和image和bit数据类型的列不应该增加索引,
4.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系