面试题 mysql执行_MySQL面试题

数据库建表三范式

1.确保每列保持原子性

2.确保表中的每列都和主键相关

3.确保每列都和主键列直接相关,而不是间接相关

数据库性能优化

1.查询字段要明确

2.尽量用多表查询少用子查询

3.oracle的语句解析是从右向左

4.表建立适当的索引

5.尽量避免全表扫描比如is null <>

Sql的执行顺序

from--where--group by--having--select--order by

from:需要从哪个数据表检索数据

where:过滤表中数据的条件

group by:如何将上面过滤出的数据分组

having:对上面已经分组的数据进行过滤的条件

select:查看结果集中的哪个列，或列的计算结果

order by :按照什么样的顺序来查看返回的数据

哪些情况会引起全表扫描

1.模糊查询。模糊查询的字段长度越大，模糊查询效率越低。 like ’%..%’无法使用索引。右模糊查询 ‘...%’会使用索引左模糊查询 ‘%...’无法使用索引

2.查询条件中含有is null的select语句

3.查询条件中使用!=或<>

如何创建索引

单一索引:Create Index On(Column_Name);

复合索引:

Create Index i_deptno_job on emp(deptno,job);—>在emp表的deptno、job列建立索引。

select * from emp where deptno=66 and job='sals' ->走索引。

select * from emp where deptno=66 OR job='sals' ->将进行全表扫描。不走索引

select * from emp where deptno=66 ->走索引。

select * from emp where job='sals' ->进行全表扫描、不走索引。如果在where 子句中有OR 操作符或单独引用Job 列(索引列的后面列) 则将不会走索引，将会进行全表扫描。

索引建设原则

1.索引应该经常建在Where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。

2.对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。

3.不应该在小表上建设索引。

4.更新非常频繁的字段不适合创建索引

MySQL有哪几种索引

从数据结构角度：

1、 B+树索引(O(log(n)))

2、 hash索引

3、 FULLTEXT索引

4、 R-Tree索引

从物理存储角度：

1、 聚集索引

2、 非聚集索引

从逻辑角度

1、 主键索引：主键索引是一种特殊的唯一索引，不允许有空值

2、 普通索引或者单列索引

3、 多列索引(复合索引)

4、 唯一索引或者非唯一索引 5、空间索引

聚集索引和非聚集索引的区别

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致，聚集索引表记录的排列顺序与索引的排列顺序一致，优点是查询速度快，因为一旦具有第一个索引值的纪录被找到，具有连续索引值的记录也一定物理的紧跟其后。

聚集索引的缺点是对表进行修改速度较慢，这是为了保持表中的记录的物理顺序与索引的顺序一致，而把记录插入到数据页的相应位置，必须在数据页中进行数据重排，降低了执行速度。非聚集索引指定了表中记录的逻辑顺序，但记录的物理顺序和索引的顺序不一致，聚集索引和非聚集索引都采用了B+树的结构，但非聚集索引的叶子层并不与实际的

In和exists的使用情况

当内表较小的时候，in比较快。当内表较大的时候，exists比较快。

如何查询用户信息性别用男女表示

select f.username,(CASE f.sex

WHEN '1' THEN

'男'

ELSE

'女'

END) as

性别

FROM bp_user f;

innodb和myisam的区别

主要区别：

1.事务安全(MyISAM不支持事务，INNODB支持事务)

2.外键 (MyISAM 不支持外键， INNODB支持外键)

3.锁机制(MyISAM时表锁，innodb是行锁)

4.查询和添加速度(MyISAM批量插入速度快)

5.支持全文索引(MyISAM支持全文索引，INNODB不支持全文索引 1.2.x版本开始支持)

6.MyISAM内存空间使用率比InnoDB低

7.MyIASM相对简单，效率上要优于InnoDB，小型应用可以考虑使用MyIASM

8.MyIASM表保存成文件形式，跨平台使用更加方便

应用场景：

1.MyIASM管理非事务表，提供高速存储和检索以及全文搜索能力，如果再应用中执行大量select操作，应该选择MyIASM

2.InnoDB用于事务处理，具有ACID事务支持等特性，如果在应用中执行大量insert和update操作，应该选择InnoDB

为什么要使用存储过程

①将重复性很高的一些操作，封装到一个存储过程中，简化了对这些SQL的调用

②批量处理：SQL+循环，减少流量，也就是“跑批”

③统一接口，确保数据的安全

如何创建存储过程

DELIMITER //

CREATE PROCEDURE pro1(IN n1 INT,IN n2 INT,OUT result INT) BEGIN SET result=n1+n2; END //

DELIMITER ;

SET @n1=10,@n2=28; CALL pro1(@n1,@n2,@result); SELECT @result;

DELIMITER // 定义结束符号开始定义了以//作为结束，后面再次使用恢复为默认结束符分号，为了防止存储过程体中的分号对其造成影响，因为系统会默认以分号作为结束。

Mysql主从复制

主从复制，是用来建立一个和主数据库完全一样的数据库环境，称为从数据库；主数据库一般是准实时的业务数据库。

作用：

1、做数据的热备，作为后备数据库，主数据库服务器故障后，可切换到从数据库继续工作，避免数据丢失。

2、架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。

3、读写分离，使数据库能支撑更大的并发。在报表中尤其重要。由于部分报表sql语句非常的慢，导致锁表，影响前台服务。如果前台使用master，报表使用slave，那么报表sql将不会造成前台锁，保证了前台速度。

原理：

1.数据库有个bin-log二进制文件，记录了所有sql语句。

2.我们的目标就是把主数据库的bin-log文件的sql语句复制过来。

3.让其在从数据的relay-log重做日志文件中再执行一次这些sql语句即可。

4.下面的主从配置就是围绕这个原理配置

5.具体需要三个线程来操作：

1).binlog输出线程:每当有从库连接到主库的时候，主库都会创建一个线程然后发送binlog内容到从库。在从库里，当复制开始的时候，从库就会创建两个线程进行处理：

2).从库I/O线程:当START SLAVE语句在从库开始执行之后，从库创建一个I/O线程，该线程连接到主库并请求主库发送binlog里面的更新记录到从库上。从库I/O线程读取主库的binlog输出线程发送的更新并拷贝这些更新到本地文件，其中包括relay log文件。

3).从库的SQL线程:从库创建一个SQL线程，这个线程读取从库I/O线程写到relay log的更新事件并执行。

MySQL主从复制面试之作用和原理

数据库分库分表

分库还是分表，都有两种切分方式：水平切分和垂直切分。

分表：

1.垂直分表

表中的字段较多，一般将不常用的、 数据较大、长度较长的拆分到“扩展表“。一般情况加表的字段可能有几百列，此时是按照字段进行数竖直切。注意垂直分是列多的情况。

2.水平分表

单表的数据量太大。按照某种规则(RANGE,HASH取模等)，切分到多张表里面去。 但是这些表还是在同一个库中，所以库级别的数据库操作还是有IO瓶颈。这种情况是不建议使用的，因为数据量是逐渐增加的，当数据量增加到一定的程度还需要再进行切分。比较麻烦。

分库：

1.垂直分库

一个数据库的表太多。此时就会按照一定业务逻辑进行垂直切，比如用户相关的表放在一个数据库里，订单相关的表放在一个数据库里。注意此时不同的数据库应该存放在不同的服务器上，此时磁盘空间、内存、TPS等等都会得到解决。

2.水平分库

水平分库理论上切分起来是比较麻烦的，它是指将单张表的数据切分到多个服务器上去，每个服务器具有相应的库与表，只是表中数据集合不同。 水平分库分表能够有效的缓解单机和单库的性能瓶颈和压力，突破IO、连接数、硬件资源等的瓶颈。

分库分表之后的问题:

1、联合查询困难

联合查询不仅困难，而且可以说是不可能，因为两个相关联的表可能会分布在不同的数据库，不同的服务器中。

2、需要支持事务

分库分表后，就需要支持分布式事务了。数据库本身为我们提供了事务管理功能，但是分库分表之后就不适用了。如果我们自己编程协调事务，代码方面就又开始了麻烦。

3、跨库join困难

分库分表后表之间的关联操作将受到限制，我们无法join位于不同分库的表，也无法join分表粒度不同的表， 结果原本一次查询能够完成的业务，可能需要多次查询才能完成。 我们可以使用全局表，所有库都拷贝一份。

4、结果合并麻烦

分库分表之后,id 主键如何处理？

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。 生成全局 id 有下面这几种方式：

UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。

数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。

利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。

Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。

美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html。