MySQL 面试题

MySQL中有几种锁？

1、表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最
高，并发度最低。
2、行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最
低，并发度也最高。
3、页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表
锁和行锁之间，并发度一般。

MySQL 中有哪些不同的表格？

共有 5 种类型的表格：
1、MyISAM
2、Heap
3、Merge
4、INNODB（事务安全）
5、ISAM

简述在 MySQL 数据库中 MyISAM 和 InnoDB 的区别

MyISAM：
不支持事务，但是每次查询都是原子的；
支持表级锁，即每次操作是对整个表加锁；
存储表的总行数；
一个 MYISAM 表有三个文件：索引文件、表结构文件、数据文件；
采用菲聚集索引，索引文件的数据域存储指向数据文件的指针。辅索引与主索引
基本一致，但是辅索引不用保证唯一性。

InnoDb：
支持 ACID 的事务，支持事务的四种隔离级别；
支持行级锁及外键约束：因此可以支持写并发；
不存储总行数：
一个 InnoDb 引擎存储在一个文件空间（共享表空间，表大小不受操作系统控制，一个表可能分布在多个文件里），也有可能为多个（设置为独立表空，表大小受操作系统文件大小限制，一般为 2G），受操作系统文件大小的限制；
主键索引采用聚集索引（索引的数据域存储数据文件本身），辅索引的数据域存储主键的值；因此从辅索引查找数据，需要先通过辅索引找到主键值，再访问辅索引；最好使用自增主键，防止插入数据时，为维持 B+树结构，文件的大调整。

MySQL 中 InnoDB 支持的四种事务隔离级别名称，以及逐级之间的区别？

SQL 标准定义的四个隔离级别为：
1、read uncommited ：读到未提交数据
2、read committed：脏读，不可重复读
3、repeatable read：可重读
4、serializable ：串行事物

CHAR 和 VARCHAR 的区别？

1. 长度不同
   char类型：长度是固定的
   varchar类型：varchar类型的长度是可变的
2. 效率不同
   char类型：每次修改的数据长度相同，效率更高
   varchar类型：每次修改的数据长度不同，效率更低
3. 储存不同
   char类型：储存的时候是初始预计字符串（实际字符串未达到该长度时，会填充空格，记得要trim)再加上记录字符串长度的自己二，占用空间较大
   varchar类型：存储的时候是实际字符串再加上一个记录字符串长度的字节，占用空间较小。

主键和候选键有什么区别？（超键，候选键，主键）

表格的每一行都由主键唯一标识,一个表只有一个主键。
主键也是候选键。按照惯例，候选键可以被指定为主键，并且可以用于任何外键
引用。

1. 超键（super key）：在关系中能惟一标识元素属性的集称为关系模式的超键。
2. 候选键：（Candidate Key）：不含有多余属性的超键称为候选键。也就是说在候选键中在删除属性，就不是键了。
3. 主键（Primary Key)：用户选作元组标识的候选键为主键。一般不佳说明，键就是主键。
4. 外键（Froeign Key）:如果模式R中的属性k是其他模式的主键，那么k在模式R中称为外键。

你怎么看到为表格定义的所有索引？

SHOW INDEX FROM <tablename>

LIKE声明中的%和_是什么意思？

%对应0个或者多个字符，_对应LIKE语句中的一个字符

BLOB和TEXT有什么区别

BLOB 是一个二进制对象，可以容纳可变数量的数据。TEXT 是一个不区分大小写
的 BLOB。
BLOB 和 TEXT 类型之间的唯一区别在于对 BLOB 值进行排序和比较时区分大小
写，对 TEXT 值不区分大小写。

MySQL如何优化DISTINCT？

DISTINCT 在所有列上转换为 GROUP BY，并与 ORDER BY 子句结合使用

SELECT DISTINCT t1.a FROM t1,t2 where t1.a = t2.a;

需要注意的是mysql把distinct优化为group by，它首先利用索引来分组，然后扫描索引，对需要的nick只扫描一次；
否则会对表记录进行全部扫描，这就是慢的原因

如何显示前 50 行？

SELECT * FROM <tablename> LIMIT(0,50)

MySQL 里记录货币用什么字段类型好

NUMERIC 和 DECIMAL 类型被 MySQL 实现为同样的类型，这在 SQL92 标准允
许。他们被用于保存值，该值的准确精度是极其重要的值，例如与金钱有关的数
据。当声明一个类是这些类型之一时，精度和规模的能被(并且通常是)指定。
例如：
salary DECIMAL(9,2)
在这个例子中，9(precision)代表将被用于存储值的总的小数位数，而 2(scale)代
表将被用于存储小数点后的位数。
因此，在这种情况下，能被存储在 salary 列中的值的范围是从-9999999.99 到
9999999.99。

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.

• 数据库要分表，可以1个月数据存储一张表**（数据进行及时备份）**
• 读写分离：写入用一台服务器，然后同步到另外的服务器进行读出。
• 如果有图片，建议有专门图片服务器(对象关系存储服务），使用独立域名访问

锁的优化策略

Innodb行锁优化建议

• 尽可能让所有的数据检索都通过索引来完成，从而避免Innodb因为无法通过索引键加锁而升级为表级锁定；
• 合理设计索引，让Innodb在索引键上面加锁尽可能准确，尽可能的缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行；
• 尽可能减少基于范围的数据检索过滤条件，避免间隙锁带来的负面影响而锁定了不该锁定的记录；
• 尽量控制事务的大小，减少锁定的资源量和锁定时间长度；
• 在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本；

索引的底层实现原理和优化

B+树，经过优化的 B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针，因此 InnoDB 建
议为大部分表使用默认自增的主键作为主索引。

优化数据库的方法

最好是按照以下顺序优化：

1. SQL 语句及索引的优化

2. 数据库表结构的优化

3. 系统配置的优化

4. 硬件的优化

5. 选取最适用的字段属性，尽可能减少定义字段宽度，尽量把字段设置 NOTNULL，
   例如’省份’、’性别’最好适用 ENUM

6. 使用连接(JOIN)来代替子查询

7. 适用联合(UNION)来代替手动创建的临时表

8. 事务处理

9. 锁定表、优化事务处理

10. 适用外键，优化锁定表

11. 建立索引

12. 优化查询语句

简单描述MySQL中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速
度。
普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值，在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说，唯一索引可以保证数据记录的唯一性。
主键，是一种特殊的唯一索引，在一张表中只能定义一个主键索引，主键用于唯一标识一条记录，使用关键字 PRIMARY KEY 来创建。
索引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引。
索引可以极大的提高数据的查询速度，但是会降低插入、删除、更新表的速度，因为在执行这些写操作时，还要操作索引文件。

什么是事务

事务（transaction）是作为一个单元的一组有序的数据库操作。如果组中的所有操作都成功，则认为事务成功，即使只有一个操作失败，事务也不成功。如果所有操作完成，事务则提交，其修改将作用于所有其他数据库进程。如果一个操作失败，则事务将回滚，该事务所有操作的影响都将取消。
事务特性：

1. 原子性：即不可分割性，事务要么全部被执行，要么就全部不被执行。
2. 一致性。事务的执行使得数据库从一种正确状态转换成另一种正确状
   态
3. 隔离性。在事务正确提交之前，不允许把该事务对数据的任何改变提供给任何
   其他事务，
4. 持久性。事务正确提交后，其结果将永久保存在数据库中，即使在事务提交后
   有了其他故障，事务的处理结果也会得到保存。
   或者这样理解：
   事务就是被绑定在一起作为一个逻辑工作单元的 SQL 语句分组，如果任何一个语句操作失败那么整个操作就被失败，以后操作就会回滚到操作前状态，或者是上有个节点。为了确保要么执行，要么不执行，就可以使用事务。要将有组语句作为事务考虑，就需要通过 ACID 测试，即原子性，一致性，隔离性和持久性。

为表中的字段选择合适的数据类型

字段类型优先级: 整形>date,time>enum,char>varchar>blob,text
优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型

对于关系型数据库而言，索引是相当重要的概念，请回答有关索引的几个问题：

1. 索引的目的是什么？
   快速访问数据表中的特定信息，提高检索速度
   创建唯一性索引，保证数据库表中每一行数据的唯一性。
   加速表和表之间的连接
   使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间
2. 索引对数据库系统的负面影响是什么？
   负面影响：
   创建索引和维护索引需要耗费时间，这个时间随着数据量的增加而增加；索引需
   要占用物理空间，不光是表需要占用数据空间，每个索引也需要占用物理空间；
   当对表进行增、删、改、的时候索引也要动态维护，这样就降低了数据的维护速
   度。
3. 为数据表建立索引的原则有哪些？
   在最频繁使用的、用以缩小查询范围的字段上建立索引。
   在频繁使用的、需要排序的字段上建立索引
4. 什么情况下不宜建立索引？
   对于查询中很少涉及的列或者重复值比较多的列，不宜建立索引。
   对于一些特殊的数据类型，不宜建立索引，比如文本字段（text）等

解释 MySQL 外连接、内连接与自连接的区别

先说什么是交叉连接: 交叉连接又叫笛卡尔积，它是指不使用任何条件，直接将一个表的所有记录和另一个表中的所有记录一一匹配。
内连接 则是只有条件的交叉连接，根据某个条件筛选出符合条件的记录，不符合条件的记录不会出现在结果集中，即内连接只连接匹配的行。
外连接 其结果集中不仅包含符合连接条件的行，而且还会包括左表、右表或两个表中的所有数据行，这三种情况依次称之为左外连接，右外连接，和全外连接。
左外连接，也称左连接，左表为主表，左表中的所有记录都会出现在结果集中，对于那些在右表中并没有匹配的记录，仍然要显示，右边对应的那些字段值以NULL 来填充。右外连接，也称右连接，右表为主表，右表中的所有记录都会出现在结果集中。左连接和右连接可以互换，MySQL 目前还不支持全外连接。

说说对 SQL 语句优化有哪些方法？（选择几条）

1. Where 子句中：where 表之间的连接必须写在其他 Where 条件之前，那些可
   以过滤掉最大数量记录的条件必须写在 Where 子句的末尾.HAVING 最后。
2. 用 EXISTS 替代 IN、用 NOT EXISTS 替代 NOT IN。(in会导致全表扫描，NOT IN子句将执行一个内部的排序和合并)
   3、 避免在索引列上使用计算
   4、避免在索引列上使用 IS NULL 和IS NOT NULL (没有效果，空值不存在在索引列中会导致索引失效)
   5、对查询进行优化，应尽量避免全表扫描，首先应考虑在where 及order by 涉
   及的列上建立索引。
   6、应尽量避免在where子句中对字段进行 null 值判断，否则将导致引擎放弃
   使用索引而进行全表扫描
   7、应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用
   索引而进行全表扫描