MySQL面试系列-02

1. 表分区有什么好处？

1、存储更多数据。分区表的数据可以分布在不同的物理设备上，从而高效地利用多个硬件设备。和单个磁盘或者文件系统相比，可以存储更多数据

2、优化查询。在where语句中包含分区条件时，可以只扫描一个或多个分区表来提高查询效率；涉及sum和count语句时，也可以在多个分区上并行处理，最后汇总结果。

3、分区表更容易维护。例如：想批量删除大量数据可以清除整个分区。

4、避免某些特殊的瓶颈，例如InnoDB的单个索引的互斥访问，ext3问价你系统的inode锁竞争等。

2. 分区表都有哪些限制因素？

1、一个表最多只能有1024个分区。

2、MySQL5.1中，分区表达式必须是整数，或者返回整数的表达式。在MySQL5.5中提供了非整数表达式分区的支持。

3、如果分区字段中有主键或者唯一索引的列，那么多有主键列和唯一索引列都必须包含进来。即：分区字段要么不包含主键或者索引列，要么包含全部主键和索引列。

4、分区表中无法使用外键约束。

5、MySQL的分区适用于一个表的所有数据和索引，不能只对表数据分区而不对索引分区，也不能只对索引分区而不对表分区，也不能只对表的一部分数据分区。

3. 什么是 MVCC？有哪些优势？

MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control)

注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control

MVCC最大的优势： 读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，现阶段几乎所有的RDBMS，都支持了MVCC。

1、LBCC：Lock-Based Concurrency Control，基于锁的并发控制 2、MVCC：Multi-Version Concurrency Control

基于多版本的并发控制协议。纯粹基于锁的并发机制并发量低，MVCC是在基于锁的并发控制上的改进，主要是在读操作上提高了并发量。

4. MVCC 并发控制中读操作分成哪两类？

1、快照读 (snapshot read)：读取的是记录的可见版本 (有可能是历史版本)，不用加锁（共享读锁s锁也不加，所以不会阻塞其他事务的写）。

2、当前读 (current read)：读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

5. key 和 index 有什么区别？

key是数据库的物理结构，它包含两层意义和作用，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key、unique key、foreign key等。

index是数据库的物理结构，它只是辅助查询的，它创建时会在另外的表空间（mysql中的innodb表空间）以一个类似目录的结构存储。索引要分类的话，分为前缀索引、全文本索引等；

6. Mysql 中 MyISAM 和 InnoDB 的区别有哪些？

1、InnoDB支持事务，MyISAM不支持

对于InnoDB每一条SQL语言都默认封装成事务，自动提交，这样会影响速度，所以最好把多条SQL语言放在begin和commit之间，组成一个事务；

2、InnoDB支持外键，而MyISAM不支持。对一个包含外键的InnoDB表转为MYISAM会失败；

3、InnoDB是聚集索引，数据文件是和索引绑在一起的，必须要有主键，通过主键索引效率很高。

但是辅助索引需要两次查询，先查询到主键，然后再通过主键查询到数据。因此主键不应该过大，因为主键太大，其他索引也都会很大。而MyISAM是非聚集索引，数据文件是分离的，索引保存的是数据文件的指针。主键索引和辅助索引是独立的。

4、InnoDB不保存表的具体行数，执行select count(*) from table时需要全表扫描。而MyISAM用一个变量保存了整个表的行数，执行上述语句时只需要读出该变量即可，速度很快；

5、Innodb不支持全文索引，而MyISAM支持全文索引，查询效率上MyISAM要高。

7. Mysql 中使用 MyISAM 和 InnoDB 如何选择？

1、是否要支持事务，如果要请选择innodb，如果不需要可以考虑MyISAM；

2、如果表中绝大多数都只是读查询，可以考虑MyISAM，如果既有读写也挺频繁，请使用InnoDB

3、系统奔溃后，MyISAM恢复起来更困难，能否接受；

4、MySQL5.5版本开始Innodb已经成为Mysql的默认引擎(之前是MyISAM)，说明其优势是有目共睹的，如果你不知道用什么，那就用InnoDB，至少不会差。

8. 数据库表创建都有哪些注意事项？

1、字段名及字段配制合理性

剔除关系不密切的字段；

字段命名要有规则及相对应的含义（不要一部分英文，一部分拼音，还有类似a.b.c这样不明含义的字段）；

字段命名尽量不要使用缩写（大多数缩写都不能明确字段含义）；

字段不要大小写混用（想要具有可读性，多个英文单词可使用下划线形式连接）；

字段名不要使用保留字或者关键字；

保持字段名和类型的一致性；

慎重选择数字类型；

给文本字段留足余量。

2、系统特殊字段处理及建成后建议

添加删除标记（例如操作人、删除时间）；

建立版本机制。

3、表结构合理性配置

多型字段的处理，就是表中是否存在字段能够分解成更小独立的几部分（例如：人可以分为男人和女人）；

多值字段的处理，可以将表分为三张表，这样使得检索和排序更加有调理，且保证数据的完整性！

4、其它建议

对于大数据字段，独立表进行存储，以便影响性能（例如：简介字段）；

使用varchar类型代替char，因为varchar会动态分配长度，char指定长度是固定的；

给表创建主键，对于没有主键的表，在查询和索引定义上有一定的影响；

避免表字段运行为null，建议设置默认值（例如：int类型设置默认值为0）在索引查询上，效率立显；

建立索引，最好建立在唯一和非空的字段上，建立太多的索引对后期插入、更新都存在一定的影响（考虑实际情况来创建）。

9. MySQL 中都有哪些锁？

一、按照对数据操作的锁粒度来分：行级锁、表级锁、页级锁、间隙锁

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

二、按照锁的共享策略来分：共享锁、排他锁、意向共享锁、意向排他锁

三、从加锁策略上分：乐观锁和悲观锁

四、其他：自增锁

自增锁（AUTO-INC锁）、外键检测的加锁策略

10. MySQL 中如何找出最后一次插入时分配的自动增量？

LAST_INSERT_ID将返回由Auto_increment分配的最后一个值，并且不需要指定表名称。

11. LIKE 声明中 ％ 和 _ 是什么含义？

“％”表示0个或更多字符。

“_”表示LIKE语句中的一个字符。

12. 时间戳如何在 Unix 和 MySQL 之间进行转换？

UNIX_TIMESTAMP是从Mysql时间戳转换为Unix时间戳的命令。

FROM_UNIXTIME是从Unix时间戳转换为Mysql时间戳的命令。

13. MySQL 中 BLOB 和 TEXT 有什么区别？

BLOB是一个二进制对象，可以容纳可变数量的数据。TEXT是一个不区分大小写的BLOB。

BLOB和TEXT类型之间的唯一区别在于对BLOB值进行排序和比较时区分大小写，对TEXT值不区分大小写。

14. mysql_fetch_array 和 mysql_fetch_object有什么区别？

mysql_fetch_array()–将结果行作为关联数组或来自数据库的常规数组返回。

mysql_fetch_object()–从数据库返回结果行作为对象。

15. MySQL 中有哪些非标准字符串类型？

TINYTEXT

TEXT

MEDIUMTEXT

LONGTEXT

16. MySQL 中数据一日十万增量，预计三年运维如何优化？

1、设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。

2、选择合适的表字段数据类型和存储引擎，适当的添加索引。

3、mysql库主从读写分离。

4、找规律分表，减少单表中的数据量提高查询速度。

5、添加缓存机制，比如memcached，apc等。

6、不经常改动的页面，生成静态页面。

7、书写高效率的SQL。比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE。

17. MySQL 中什么情况下设索引失效？

1、如果条件中有or，即使其中有条件带索引也不会使用，如果使用or想让索引生效，只能将or条件中的每个列都加上索引。

2、对于多列索引，不是使用的第一部分，则不会使用索引。

3、以“%”开头的LIKE语句，模糊匹配。

4、数据类型出现隐式转化，如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引。（如varchar不加单引号的话可能会自动转换为int型）

5、如果mysql估计使用全表扫描要比使用索引快，则不使用索引。

18. 为什么索引列不能存 Null 值？

将索引列值进行建树，其中必然涉及到诸多的比较操作。Null值的特殊性就在于参与的运算大多取值为null。

索引列存储Null值，null值实际上是不能参与进建索引的过程。也就是说，null值不会像其他取值一样出现在索引树的叶子节点上。

19. SQL 注入漏洞是什么原因造成的？如何防止？

造成SQL注入的原因是程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。

防止SQL注入的方式：

1、开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置。

2、执行sql语句时使用addslashes进行sql语句转换。

3、Sql语句书写尽量不要省略双引号和单引号。

4、过滤掉sql语句中的一些关键词：update、insert、delete、select、*。

5、提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。

20. MySQL 中外连接、内连接与自连接有什么区别？

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。

内连接是指只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。

外连接其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。

左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL来填充。

右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL目前还不支持全外连接，可以使用union all进行替换。

自连接查询是指当前表与自身的连接查询，关键点在于虚拟化出一张表给一个别名。例如：查询员工以及他的领导的名称，由于上司也是员工，所以需要虚拟化出一张领导表。