MySQL面试总结

1、解释一下单列索引和联合索引

单列索引是指在表的某一列上创建索引，联合索引是在多个列上联合创建索引。单列索引可以出现在where条件的任何位置，而联合索引需要按照一定的顺序来写。在多条件查询的时候，联合索引的效率更高，我们联合索引也最多创建两列。

我们创建索引的时候也得考虑到我们这张表的更新频率，如果表里索引比较多的话是比较影响更新速度的，因为创建索引的过程其实就是构建一个二叉树，而每次更新完数据都得重新计算二叉树，所以就影响更新速度。

索引并不是时时都会生效的，比如以下几种情况就能导致索引失效：

• 如果条件中有or，即使其中有条件带索引也不会使用，这也是为什么尽量少用or的原因，如果要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
• like查询是以%开头，会导致索引失效
• 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则索引失效
• 如果mysql估计使用全表扫描要比使用索引快，则不使用索引

所以呢，我们创建索引的话，也不是随便创建的，我给您说下一些常用的创建索引的原则吧

2、使用索引查询的优缺点

使用索引优点第一：可以保证数据库表中每一行的数据的唯一性，第二：可以大大加快数据的索引速度，在使用分组和排序语句

进行数据检索时，同样可以显著减少查询中分组和排序的时间；

缺点：创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

3、mysql存储引擎都有哪些，有什么区别

我了解到的数据库搜索引擎有MyISAM、InnoDB、BDB、MEMORY等，对于 MySQL 5.5 及更高版本，默认的存储引擎是 InnoDB。在 5.5 版本之前，MySQL 的默认存储引擎是 MyISAM，我主要给您介绍下这两个的区别吧

• InnoDB 存储引擎：

  • 支持自增长列（auto_increment），自增长列的值不能为空，如果在使用的时候为空的话就会从现有的最大值自动+1，如果有但是比现在的还大，则就保存这个值。

  • 支持外键（foreign key），外键所在的表称为子表而所依赖的表称为父表。

  • 支持事务，回滚以及系统崩溃的修复能力，并且支持多版本并发控制的事务安全。

  • 支持mvcc（多版本并发控制）的行级锁，就是通过多版本控制来实现的乐观锁

  • 索引使用的是B+Tree

  优缺点：InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差，占用的数据空间相对较大。

• MyISAM 存储引擎

  不支持事务、支持表级锁

  支持全文搜索

  缓冲池只缓存索引文件，

  不缓存数据文件 MyISAM 存储引擎表由 数据文件（MYD）和索引文件（ MYI）组成

我们项目中常用到的是innoDB，InnoDB存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全，但是对比Myisam的存储引擎InnoDB写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引。

4、创建索引的原则

• 经常需要搜索的列上建立索引，可以加快搜索的速度。

• 在作为主键的列上创建索引，强制该列的唯一性，并组织表中数据的排列结构。

• 在经常使用表连接的列上创建索引，这些列主要是一些外键，可以加快表连接的速 度。

• 在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，所以其指定的 范围是连续的。

• 在经常需要排序的列上创建索引，因为索引已经排序，所以查询时可以利用索引的 排序，加快排序查询。

• 在经常使用 WHERE 语句的列上创建索引，加快条件的判断速度。

5、如何查看查询语句索引是否生效

使用 explain 执行计划查看 在sql前面加入关键字explain 查询出的结果查看type类型检查是否有执行索引

举例：EXPLAIN select * from table where id=2;我们一般优化sql语句的话，type级别都要至少达到ref级别，就是每次查询必须要使用索引

• explain之后返回的列

列	说明
id	在一个大的查询语句中每个SELECT关键字都对应一个唯一的id
select_type	SIMPLE（simple）：简单SELECT(不使用UNION或子查询)。 PRIMARY（primary）：子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY。 UNION（union）：UNION中的第二个或后面的SELECT语句。 DEPENDENT UNION（dependent union）：UNION中的第二个或后面的SELECT语句,取决于外面的查询。 UNION RESULT（union result）：UNION的结果，union语句中第二个select开始后面所有select。 SUBQUERY（subquery）：子查询中的第一个SELECT，结果不依赖于外部查询。 DEPENDENT SUBQUERY（dependent subquery）：子查询中的第一个SELECT，依赖于外部查询。 DERIVED（derived）：派生表的SELECT (FROM子句的子查询)。 UNCACHEABLE SUBQUERY（uncacheable subquery）：(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
table	表名
partitions	匹配的分区信息
type	针对单表的访问方法，参考下面的说明
possible_keys	可能用到的索引
key	实际上使用的索引
key_len	实际使用到的索引长度
ref	当使用索引列等值查询时，与索引列进行等值匹配的对象信息
rows	预估的需要读取的记录条数
filtered	某个表经过搜索条件过滤后剩余记录条数的百分比
Extra	一些额外的信息

• type说明

类型	说明
All	最坏的情况,全表扫描
index	和全表扫描一样。只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。如在Extra列看到Using index，说明正在使用覆盖索引，只扫描索引的数据，它比按索引次序全表扫描的开销要小很多
range	范围扫描，一个有限制的索引扫描。key 列显示使用了哪个索引。当使用=、 <>、>、>=、<、<=、IS NULL、<=>、BETWEEN 或者 IN 操作符,用常量比较关键字列时,可以使用 range
ref	一种索引访问，它返回所有匹配某个单个值的行。此类索引访问只有当使用非唯一性索引或唯一性索引非唯一性前缀时才会发生。这个类型跟eq_ref不同的是，它用在关联操作只使用了索引的最左前缀，或者索引不是UNIQUE和PRIMARY KEY。ref可以用于使用=或<=>操作符的带索引的列。
eq_ref	最多只返回一条符合条件的记录。使用唯一性索引或主键查找时会发生 （高效）
const	当确定最多只会有一行匹配的时候，MySQL优化器会在查询前读取它而且只读取一次，因此非常快。当主键放入where子句时，mysql把这个查询转为一个常量（高效）
system	这是const连接类型的一种特例，表仅有一行满足条件。
Null	意味说mysql能在优化阶段分解查询语句，在执行阶段甚至用不到访问表或索引（高效）

6、有没有做过数据库建模，自己是设计表和模块

数据库建模就是使用PowerDesigner工具，先分析项目需求，前端先出相应的原型，根据原型，我开始做相应的表，设计初期的时候表会有些小浮动修改等，再根据需求设计详细字段。如果后期客户需求改变时，表结构后期跟着调整，就是这样使用工具不断完善过程就是建模，不过一些小的项目的话，简单的通过Navicat里的模型工具就可以实现了

7、左连接、右连接、内连接的区别

内连接的话，就是两表关联的数据才能查出来，关联不到的就查询不到。左连接就是以左表为主，左表数据全查，右表数据没有就显示null，右连接相反

我给您举个例子吧，比如员工和部门表，如果要查询出每个员工的信息以及他的部门信息，那么这个时候用内连接最合适。如果要查询出每个部门下对应的员工信息，那么就需要以部门表为左表，进行左连接查询。这样的话，没有员工的部门也可以被查询出来。

8、 count(1)和count(*) 有什么区别

从执行结果来看count(*)和count(1)没有区别，因为他们都不过滤空值

从执行效率来看MySQL会对count（*）做优化

（1）如果列为主键，count(列名)效率优于count(1)

（2）如果列不为主键，count(1)效率优于count(列名)

（3）如果表中存在主键，count(主键列名)效率最优

（4）如果表中只有一列，则count(*)效率最优

9、mysql查询语句的优化？

• 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及 的列上建立索引。

• 应尽量避免在 where 子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行 全表扫描。

• 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用 索引而进行全表扫描，如： select id from t where num is null 可以在 num 上设置默认值 0，确保表中 num 列没有 null 值，然后这样查询:select id from t where num=0

• 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引 而进行全表扫描，如： select id from t where num=10 or num=20 ，可以使用可以这样查询： select id from t where num=10 union all select id from t where num=20

• 以%开头的模糊查询也会导致全表扫描： select id from t where name like '%abc%'，如果要提高效率的话，可以考虑全文检索来解决。

• in 和 not in 也要慎用，否则会导致全表扫描，如： select id from t where num in(1,2,3) 对于连续的数值，能用 between 就不要用 in 了： select id from t where num between 1 and 3

• 应尽量避免在 where 子句中对字段进行表达式操作，这将导致放弃使用索引 而进行全表扫描。如：select id from t where num/2=100应改为: select id from t where num=100*2

• 应尽量避免在 where 子句中对字段进行函数操作，这将导致引擎放弃使用索引而 进行全表扫描。

  比如说查询name以abc开头的数据： select id from t where substring(name,1,3)='abc' ，可以改为select id from t where name like 'abc%'

• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系 统将可能无法正确使用索引。

• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中 的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可 能的让字段顺序与索引顺序相一致。

• 很多时候用 exists 代替 in 是一个好的选择： select num from a where num in(select num from b)用下面的语句替换： select num from a where exists(select 1 from b where num=a.num)

• 并不是所有索引对查询都有效，SQL 是根据表中数据来进行查询优化的，当索引 列有大量数据重复时，SQL 查询可能不会去利用索引，如一表中有字段 sex，男、女的值 几乎各一半，那么即使在 sex 上建了索引也对查询效率起不了作用。

• 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低 了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索 引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过 6 个

10、mysql批量插入5000条数据如何优化？

• 第一种方法：

合并sql插入语句，合并后日志量减少，降低日志刷盘的数据量和频率，从而提高效率，通过合并SQL语句，

同时也能减少SQL语句解析的次数，减少网络传输的IO

比如：INSERT INTO table(uid,content, type) VALUES ('userid_0', 'content_0', 0);

改为：

INSERT INTO table (uid,content, type) VALUES ('userid_0', 'content_0', 0), ('userid_1','content_1', 1);

• 第二种方法：

在同一个事务中进行插入处理

这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用同一个事务可以减少创建事务所消耗的时间，所有插入都在执行后才统一进行提交操作。

11、mysql查询重复数据？

比如A表有字段id，pid，sname，

查询重复数据：select * from A

where pid in (select pid from A group by pid having count(pid) > 1);

12、了解过MySQL存储过程和视图吗，介绍一下

• 存储过程

存储程序是被存储在服务器中的组合SQL语句，经过创建编译并保存在数据库中，用户可通过存储过程的名字调用执行。存储过程核心思想就是数据库SQL语言层面的封装与复用。使用存储过程可以较少应对系统的业务复杂性，但是会增加数据库服务器系统的负荷，所以在使用的时候需要综合业务考虑。

对应存储过程的名字使用call调用 ，把对应的参数传递进去，输出参数使用@声明

基本语法，了解熟悉一下

-- 创建存储过程

DROP PROCEDURE IF EXISTS p01_discount;  //如果存在先删掉再创建 

CREATE PROCEDURE p01_discount(IN consume NUMERIC(5,2),OUT payfee NUMERIC(5,2)) //声明存储过程，in输入参数  out输出参数

BEGIN

​       --判断收费方式

​       IF(consume>100.00AND consume<=300.00) THEN

​              SET payfee=consume*0.8;

​       ELSEIF(consume>300.00) THEN 

​              SET payfee=consume*0.6;

​       ELSE

​              SET payfee = consume;

​       END IF;

​       SELECT payfee AS result;

END ;

-- 调用存储过程

CALL p01_discount(100.0,@discount); 

• 视图

视图本身是一张虚拟表，不存放任何数据。在使用SQL语句访问视图的时候，获取的数据是MySQL从其它表中生成的，视图和表在同一个命名空间（因为表和视图共享数据库中相同的名称空间，因此，数据库不能包含具有相同名称的表和视图）。视图查询数据相对安全，视图可以隐藏一些数据和结构，只让用户看见权限内的数据，使复杂的查询易于理解和使用。

原来我们公司做过一个项目的时候，用的是5张表的联查，然后用sql语句来写的话，比较慢，比较麻烦，然后我们把这5张表的联查创建了一个视图，然后就直接查找的是视图，查询速度快，这个视图就是只能做查询，而不能做增删改操作

基本语法，了解熟悉一下

-- 创建视图

CREATE OR REPLACE VIEW user_order_view AS 

SELECT

​       t1.id,t1.user_name,t2.order_no,t2.good_id,

​       t2.good_name,t2.num,t2.total_price

FROM v01_user t1

LEFT JOIN v02_order t2 ON t2.user_id =t1.id;

-- 视图调用

SELECT * FROM user_order_view WHERE user_name='Cicada';

13、where和having的区别

这两个都是添加查询条件用的。where的话就是拼接普通字段的查询条件，having后边跟上聚合之后数据的查询条件。

比如计算平均薪资在10k以上的部门信息，这会儿的话就要用select xx from table group by deptId having avg(salary)>10000

常用的聚合函数有：count、sum、avg、min、max

14、数据库三范式介绍一下

第一范式，原子性，列或者字段不能再分

第二范式的话要满足第一范式，并且不可以把多种数据保存在同一张表中，即一张表只能保存一类数据，否则可能导致插入数据异常。

第三范式，直接性，不存在传递依赖，他要在满足第二范式的条件上，在每一列都和主键直接相关，而不能间接相关。

15、select语句的执行顺序

from—>where—>group by—>having—>计算所有的表达式—>order by-- ->select 输出

大致上是这么个顺序，如果sql里有子查询的话，也会按照这个方式来执行的

16、mysql分库分表介绍下

分库分表的话，是解决MySQL数据量多了之后，单表单库存储量多了之后查询效率低下等问题的，主要分为两种方式，一个是水平拆分，另一个是垂直拆分

垂直拆分的话就是单个表中比如30个字段，拆分为两个表，一个表20个字段，一个表10个字段这样，或者按照其他方式拆分成3个表，这样的拆分原则呢就是将大字段或者不经常修改的或者经常查询的字段拆分出来，作为单独的表存储，然后跟主表一对一的关系存储，这样的话水平扩展了表，并且对功能也做了分离，高并发场景下，垂直拆分一定程度的提升IO性能，不过依然存在单表数据量过大的问题

水平拆分的话就是按照数据量来拆分，比如我们的表里，每个表最多存储200W条数据，然后每个表命名方式为user_0001、user_0002的方式，在查询的时候，用逻辑代码来控制数据查询。这样的话不存在单表单库数据量过大、高并发的性能瓶颈，提升系统稳定性和负载能力。不过水平拆分会导致跨分片的事务一致性难以保证，跨库的join关联查询性能较差，要根据具体的业务来判断具体适用那种分表方式

我们都是结合读写分离和mysql集群做的，读写分离以及集群的话，读写分离话保证了数据的安全性，集群的话其实就相当于水平拆分。这个我们项目中使用MyCat来做的,在mycat里配置好主库和从库,做增删改的时候是对主库进行操作,查询的时候是对从库进行操作,其实mysql本身从5.6以后的版本就带主从复制的功能了,他们是利用mysql里的log文件进行的数据同步