MySQL常见面试题

1.语法

1.如何查看一个已经建好的表结构与建表参数
查看表结构 desc tablename；
查看建表语句 show create table tablename;

2.char和varchar有什么区别，varchar(255)和varchar(256)有什么区别
char(n)的字段长度是固定的
varchar(n)的字段长度是可变的
char(5) 输入:abc 存储:abc (后面补两个空格)
varchar(5) 输入:abc 存储:abc (长度自动变为3)
由于varchar长度可变，所以存取速度小于char。

对于一个8位的tinyint，无符号整数值范围0-255，当超过该范围会额外的申请一个字节，因此不建议使用varchar(256)。

3.text和blob有什么区别
text是非二进制字符串，用于存储大文本，容量较varchar大很多
blob是二进制存储格式，除了存储文本外，还可以存储图片、音频等信息

4.使用LIKE关键字’%‘和’_‘有什么作用
’%‘代表多个（包括0个）任意字符
’_‘代表1个任意的字符

5.delete、drop和truncate有什么区别与联系
drop from tablename;用于删除表
delete与truncate用于删除表中的数据（保留表结构）。
delete可以与where结合，truncate不可以。

delete涉及事务可以回滚，涉及缓存所以速度较慢，适用于数据量小，需要考虑事务提交，需要回滚的场合
当数据量较大，不需要回滚，对速度要求快、效率高可以使用truncate。

6.为什么不建议使用select * 呢
如果不查询表中所有的列，尽量避免使用 SELECT *，因为它会进行全表扫描，不能有效利用索引，增大了数据库服务器的负担，以及它与应用程序客户端之间的网络IO开销。

7.如何查询表中第100条至第200条的数据
如果有相应字段则直接where 字段 between 100 and 200
否则先用row_number() over()

8.如何查看某个字段在表中唯一值的数量
使用count(distinct field)，或者groupby后在count

9.MySQL常用内置函数

mysql字符串函数：
concat(String2 [,…]) 　　　　　　 //连接字串
length(string) 　　　　　　　　　 //string长度
ltrim(string2) 　　　　　　　　　 //去除前端空格
rtrim(string2)　　　　　　　　　 //去除后端空格
repeat(string2,count) 　　　　　　//重复count次
replace(str,search_str,replace_str) 　　//在str中用replace_str替换search_str
left(str,len) //返回字符串str的最左面len个字符。
right(str,len) //返回字符串str的最右面len个字符。
substring(str,position [,length]) 　　//position开始，取length个字符,注意：position偏移量是从1开始的
space(count) 　　　　　　　　　　//生成count个空格

mysql数学函数：
bin(decimal_number) //十进制数字转换成二进制
ceiling(number2) //向上取整
floor(number2) //向下取整
max(列名) //最大值
min(列名) //最小值
sqrt(number) //平方根
rand() //随机数(0-1)，可以进行随机排序：select * from user order by rand();

mysql日期函数：
curdate() // 返回当前日期
curtime() //返回当前时间
now() //返回当前的日期时间
unix_timestamp(date) //返回当前date的unix时间戳
from_unixtime(时间戳) //返回UNIX时间戳的日期值
week(date) //返回日期date为一年中的第几周
year(date) //返回日期date的年份
datediff(expr,expr2) //返回起始时间expr和结束时间expr2间的天数

2.多表查询

1.什么是多表查询，多表查询的过程是什么
有时一个查询结果需要从两个或两个以上表中提取字段数据，此时需要使用的就是多表关联查询。

执行顺序
from–on–join–where–group by–having-- select–distinct–order by-limit

2.什么是笛卡尔积
两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X×Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员。

3.什么是内连接、外连接、交叉连接、自身连接
内连接：也称为等值连接，返回两张表都满足条件的部分
inner join 就等于 join

外连接分为外左连接(left outer join)和外右连接(right outer join)
left outer join 与 left join 等价， 一般写成left join
左连接，取左边的表的全部，右边的表按条件，符合的显示，不符合则显示null

交叉连接：返回左表中的所有行，左表中的每一行与右表中的所有行组合。交叉联接也称作笛卡尔积。
在两个表之间使用","。

自连接：当同一字段有关系，或者有1对多，多对1的关系时可以用自连接。

4.什么是嵌套查询、集合查询
嵌套查询即采用子查询
集合操作主要包括并操作union，交操作intersect和差操作except。

3.索引

1.什么是索引、为什么要有索引
索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。
（1）快速取数据；

（2）保证数据记录的唯一性；

（3）实现表与表之间的参照完整性；

（4）在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

2.索引有哪些类型
唯一索引、非唯一索引、主键索引和聚集索引。 尽管唯一索引有助于定位信息，但为获得最佳性能结果，建议改用主键或唯一约束。
主键索引：数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键，主键中的每个值都唯一。
唯一索引是不允许其中任何两行具有相同索引值的索引。
覆盖索引：建立索引的字段正好是覆盖查询语句[select子句]与查询条件[Where子句]中所涉及的字段，也即，索引包含了查询正在查找的所有数据。

3.索引如何加速查询速度
索引的目的：提高查询效率
原理：通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据结构：B+树

4.一个表中的索引是不是越多越好
1 首先数据量小的表不需要建立索引,因为小的表即使建立索引也不会有大的用处,还会增加额外的索引开销
2 不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义
3 经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率
4 索引并不是一劳永逸的,用的时间长了需要进行整理或者重建

5.联合索引的最左匹配原则
联合索引是对多个字段同时建立的索引。
以联合索引(a,b,c)为例
建立这样的索引相当于建立了索引a、ab、abc三个索引。

6.回表查询是如何产生的
如果 select 所需获得列中有非索引列或者一次索引无法获得记录的列（辅助索引），索引就需要到表中找到相应的列的信息，这就叫回表。

InnoDB聚集索引的叶子节点存储行记录，因此， InnoDB必须要有，且只有一个聚集索引：
（1）如果表定义了PK，则PK就是聚集索引；
（2）如果表没有定义PK，则第一个not NULL unique列是聚集索引；
（3）否则，InnoDB会创建一个隐藏的row-id作为聚集索引；

InnoDB普通索引的叶子节点存储主键值。MyISAM的索引叶子节点存储记录指针。

普通索引的叶子节点存储的是主键值。

4.存储引擎

1.什么是存储引擎
存储引擎是如何存储数据、如何为存储的数据建立索引和如何更新、查询数据等技术的实现方法。在关系型数据库中数据是以表的形式存储的，所以存储引擎也可以称为表类型。

2.MySQL的主流存储引擎，主要应用场景是什么，默认是什么存储引擎
InnoDB（MySQL 5.1更高版本默认引擎）支持事物
使用场景：
1.支持事务；
2.行级锁定对高并发有很好的适应能力
3.数据更新较为频繁的场景
4.数据一致性要求较高
5.硬件设备内存较大，可以利用InnoDB较好的缓存能力提高内存利用。

MyISAM（MySQL 5.1及以前默认引擎）
使用场景：
1.不需要支持事务（不支持事务）
2.并发相对较低（表级别锁定）
3.数据修改相对较少（阻塞问题）
4.数据一致性要求不是非常高
NDBCluster

3.MyISAM与InnoDB的区别，数据分别是如何存储的
区别上题可以看出

MySQL存储采用B+树，所有叶子节点包含全部的关键字，叶子节点本身根据关键字自小而大顺序连接（即每个叶子节点指向下一个叶子节点）。
这也是主键设为自增ID的优势，插入新纪录时可以顺序添加到节点后续位置。

InnoDB的数据存储在主键B+树的叶子节点上。
辅助索引的叶子节点存储主键值。

MyISAM的B+树的叶子节点存储数据的地址
辅助索引的叶子节点也存储数据的地址。

5.性能

1.如何查询SQL语句的执行性能
在sql语句前面加上 explain。

2.导致慢查询的主要原因
没有使用索引
使用了like "%a"等（会导致全文索引）
查询了不需要的列
union all好于union
没有必要不适用distinct
not exists 好于 not in
死锁、IO吞吐小

3.什么是读写分离，什么场景需要使用读写分离
读写分离其实就是将数据库分为了主从库，一个主库用于写数据（增删改），多个从库完成读数据的操作，主从库之间通过某种机制进行数据的同步，是一种常见的数据库架构。
在数据库读多写少时使用读写分离。
分表、分库和读写分离可以有效地减小单台数据库的压力。

4.什么是分库、分表、分区，应用场景分别是什么，如何分库、分表。
分库即单台DB的存储空间不够，需要对数据库分库
分库有垂直拆分（按业务划分）、水平拆分（按规则划分）、读写分离

分表将一张表按一定规则分解成N个具有独立存储空间的实体表。
当一张表的查询速度已经慢到影响使用时；
数据量太大、当频繁插入或者联合查询时速度变慢需要分表。
分表策略：Range（范围）、Hash（哈希）、按时间等。

分区把一张表的数据分成N个区块，逻辑上是一张表，底层有N个物理区组成。
当一张表的查询速度已经慢到影响使用时；
数据量太大；表中的数据是分段的；
对数据操作往往只涉及一部分数据，而不是所有的数据时可以分区。

分区与分表都可以提升增删改查的效率
分区只是将一张表中的数据的存储位置发生该表，分表是将一张表分成多张表。
当访问量大，且表中数据比较大时，分区和分表可以配合使用。
当访问量不大，但表中数据比较多时，可以只进行分区。

5.MySQL主从实例如何实现数据同步
???

6.数据库表设计

1.设计表时的注意事项
1.字段非NULL（可以减少存储开销、避免排序不准的问题）
2.设置默认值
3.写注释

2.数据库的三范式
第一范式（1NF）：强调的是列的原子性，即列不能够再分成其他几列

第二范式（2NF）：首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

第三范式（3NF）：首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。