标签：语句面试题数据库 SQL 查询索引 Mysql 数据

1.Mysql基础

1、from子句组装来自不同数据源的数据；
2、where子句基于指定的条件对记录行进行筛选；
3、group by子句将数据划分为多个分组；
4、使用聚集函数进行计算；
5、使用having子句筛选分组；
6、计算所有的表达式；
7、select 的字段；
8、使用order by对结果集进行排序。
SQL语言不同于其他编程语言的最明显特征是处理代码的顺序。在大多数据库语言中，代码按
编码顺序被处理。但在SQL语句中，第一个被处理的子句式 FROM，而不是第一出现的SELECT。
SQL查询处理的步骤序号：
(1) FROM <left_table>
(2) <join_type> JOIN <right_table>
(3) ON <join_condition>
(4) WHERE <where_condition>
(5) GROUP BY <group_by_list>
(6) WITH {CUBE | ROLLUP}
(7) HAVING <having_condition>
(8) SELECT
(9) DISTINCT
(9) ORDER BY <order_by_list>
(10) <TOP_specification> <select_list>
以上每个步骤都会产生一个虚拟表，该虚拟表被用作下一个步骤的输入。这些虚拟表对调用
者(客户端应用程序或者外部查询)不可用。只有最后一步生成的表才会会给调用者。如果没有在
查询中指定某一个子句，将跳过相应的步骤。
逻辑查询处理阶段简介：
1、 FROM：对FROM子句中的前两个表执行笛卡尔积(交叉联接)，生成虚拟表VT1。
2、 ON：对VT1应用ON筛选器，只有那些使为真才被插入到TV2。
3、 OUTER (JOIN):如果指定了OUTER JOIN(相对于CROSS JOIN或INNER JOIN)，保留表中未找到匹配的行将作为外部行添加到VT2，生成TV3。如果FROM子句包含两个以上的表，则对上一个联接生成的结果表和下一个表重复执行步骤1到步骤3，直到处理完所有的表位置。
4、 WHERE：对TV3应用WHERE筛选器，只有使为true的行才插入TV4。
5、 GROUP BY：按GROUP BY子句中的列列表对TV4中的行进行分组，生成TV5。
6、 CUTE|ROLLUP：把超组插入VT5，生成VT6。
7、 HAVING：对VT6应用HAVING筛选器，只有使为true的组插入到VT7。
8、 SELECT：处理SELECT列表，产生VT8。
9、 DISTINCT：将重复的行从VT8中删除，产品VT9。
10、 ORDER BY：将 VT9中的行按ORDER BY子句中的列列表顺序，生成一个游标(VC10)。
11、 TOP：从 VC10的开始处选择指定数量或比例的行，生成表TV11，并返回给调用者。 where子句中的条件书写顺序

2. 什么是事务，MySQL是如何支持事务的？

事务就是一段sql 语句的批处理，但是这个批处理是一个原子，不可分割，要么都执行，要么回滚（rollback）都不执行。
事务具体四大特性，也就是经常说的ACID ：
1.原子性（所有操作要么全部成功，要么全部失败回滚）
2.一致性（事务执行之前和执行之后都必须处于一致性状态。）
3.隔离性（数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离）
4.持久性（一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，
即使遭遇故障依然能够通过日志恢复最后一次更新在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现 BEGIN 开始一个事务 ROLLBACK 事务回滚 COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式: SET AUTOCOMMIT=0 禁止自动提交 SET AUTOCOMMIT=1 开启自动提交

3. 说一下Mysql数据库存储的原理？

储存过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
存储过程通常有以下优点：
1、存储过程能实现较快的执行速度
2、存储过程允许标准组件是编程。
3、存储过程可以用流程控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
4、存储过程可被作为一种安全机制来充分利用。
5、存储过程能够减少网络流量

4. 数据库索引种类？

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
MySQL索引的类型：

普通索引：这是最基本的索引，它没有任何限制
2.唯一索引：索引列的值必须唯一，但允许有空值，如果是组合索引，则列值的组合必须唯一
　　 3.全文索引：全文索引仅可用于 MyISAM 表，可以从CHAR、VARCHAR或TEXT列中作为CREATE TABLE语句的一部分被创建，或是随后使用ALTER TABLE 或CREATE INDEX被添加（切记对于大容量的数据表，生成全文索引是一个非常消耗时间非常消耗硬盘空间的做法）
单列索引、多列索引：多个单列索引与单个多列索引的查询效果不同，因为执行查询时，MySQL只能使用一个索引，会从多个索引中选择一个限制最为严格的索引。
5.组合索引（最左前缀）：简单的理解就是只从最左面的开始组合（实在单列索引的基础上进一步压榨索引效率的一种方式）

5. 索引在什么情况下遵循最左前缀的规则？

mysql在使用组合索引查询的时候需要遵循“最左前缀”规则

6. MySQL常见的函数？

聚合函数：
　　 AVG(col)返回指定列的平均值
　　 COUNT(col)返回指定列中非NULL值的个数
　　 MIN(col)返回指定列的最小值
　　 MAX(col)返回指定列的最大值
　　 SUM(col)返回指定列的所有值之和
　　 GROUP_CONCAT(col) 返回由属于一组的列值连接组合而成的结果
数学函数：
　　 ABS(x) 返回x的绝对值
　　 BIN(x) 返回x的二进制（OCT返回八进制，HEX返回十六进制）

7. 如何开启慢日志查询？

1 执行 SHOW VARIABLES LIKE “%slow%”，获知 mysql 是否开启慢查询 slow_query_log 慢查询开启状态 OFF 未开启 ON 为开启 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）
2 修改配置文件（放在[mysqld]下），重启 long_query_time 查询超过多少秒才记录
3 测试是否成功
4 慢查询日志文件的信息格式

8. 数据库导入导出命令（结构+数据）？

1.导出整个数据库
　mysqldump -u用户名 -p密码数据库名 > 导出的文件名　　
例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec > e:\sva_rec.sql 　
2.导出一个表，包括表结构和数据
　mysqldump -u用户名 -p 密码数据库名表名> 导出的文件名　　
例如：C:\Users\jack> mysqldump -uroot -pmysql sva_rec date_rec_drv> e:\date_rec_drv.sql 　
3.导出一个数据库结构
　例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec > e:\sva_rec.sql
4.导出一个表，只有表结构
　mysqldump -u用户名 -p 密码 -d数据库名表名> 导出的文件名　　
例如：C:\Users\jack> mysqldump -uroot -pmysql -d sva_rec date_rec_drv> e:\date_rec_drv.sql 　　
5.导入数据库 　
常用source 命令　　
进入mysql数据库控制台，　　
如mysql -u root -p 　　mysql>use 数据库　　
然后使用source命令，后面参数为脚本文件(如这里用到的.sql) 　　
mysql>source d:wcnc_db.sql

9. 数据库怎么优化查询效率？

1、储存引擎选择：如果数据表需要事务处理，应该考虑使用InnoDB，因为它完全符合ACID特性。
如果不需要事务处理，使用默认存储引擎MyISAM是比较明智的
2、分表分库，主从。
3、对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引
4、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
5、应尽量避免在 where 子句中使用 != 或 <> 操作符，否则将引擎放弃使用索引而进行全表扫描
6、应尽量避免在 where 子句中使用 or 来连接条件，如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描
7、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志
8、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

10. Mysql集群的优缺点？

优点：

99.999%的高可用性
快速的自动失效切换
灵活的分布式体系结构，没有单点故障
高吞吐量和低延迟
可扩展性强，支持在线扩容
缺点：
存在很多限制，比如：不支持外键
部署、管理、配置很复杂
占用磁盘空间大、内存大
备份和恢复不方便
重启的时候，数据节点将数据load到内存需要很长的时间

11. 你用的Mysql是哪个引擎，各引擎之间有什么区别？

主要 MyISAM 与 InnoDB 两个引擎，其主要区别如下：
InnoDB 支持事务，MyISAM 不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而 MyISAM就不可以了；
MyISAM 适合查询以及插入为主的应用，InnoDB 适合频繁修改以及涉及到安全性较高的应用；
InnoDB 支持外键，MyISAM 不支持；
MyISAM 是默认引擎，InnoDB 需要指定；
InnoDB 不支持 FULLTEXT 类型的索引；
InnoDB 中不保存表的行数，如 select count() from table 时，InnoDB；需要扫描一遍整个表来计算有多少行，但是 MyISAM 只要简单的读出保存好的行数即可。注意的是，当 count()语句包含 where 条件时 MyISAM 也需要扫描整个表；
对于自增长的字段，InnoDB 中必须包含只有该字段的索引，但是在 MyISAM表中可以和其他字段一起建立联合索引；清空整个表时，InnoDB 是一行一行的删除，效率非常慢。MyISAM 则会重建表；
InnoDB 支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

12. 数据库的优化？

1.优化索引、SQL 语句、分析慢查询；
2.设计表的时候严格根据数据库的设计范式来设计数据库；
3.使用缓存，把经常访问到的数据而且不需要经常变化的数据放在缓存中，能节约磁盘IO
4.优化硬件；采用SSD，使用磁盘队列技术(RAID0,RAID1,RDID5)等
5.采用MySQL 内部自带的表分区技术，把数据分层不同的文件，能够提高磁盘的读取效率；
6.垂直分表；把一些不经常读的数据放在一张表里，节约磁盘I/O；
7.主从分离读写；采用主从复制把数据库的读操作和写入操作分离开来；
8.分库分表分机器（数据量特别大），主要的的原理就是数据路由；
9.选择合适的表引擎，参数上的优化
10.进行架构级别的缓存，静态化和分布式；
11.不采用全文索引；
12.采用更快的存储方式，例如 NoSQL存储经常访问的数据**。

13. Mysql数据库如何分区、分表？

分表可以通过三种方式：Mysql集群、自定义规则和merge存储引擎。
分区有四类：
RANGE 分区：基于属于一个给定连续区间的列值，把多行分配给分区。
LIST 分区：类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个
值来进行选择。
HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的
这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
KEY 分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器
提供其自身的哈希函数。必须有一列或多列包含整数值。

14. Sql注入是如何产生的，如何防止？

程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量
POST和GET提交一些sql语句正常执行。产生Sql注入。下面是防止办法：
a. 过滤掉一些常见的数据库操作关键字，或者通过系统函数来进行过滤。
b. 在PHP配置文件中将Register_globals=off;设置为关闭状态
c. SQL语句书写的时候尽量不要省略小引号(tab键上面那个)和单引号
d. 提高数据库命名技巧，对于一些重要的字段根据程序的特点命名，取不易被猜到的
e. 对于常用的方法加以封装，避免直接暴漏SQL语句
f. 开启PHP安全模式：Safe_mode=on;
g. 打开magic_quotes_gpc来防止SQL注入
h. 控制错误信息：关闭错误提示信息，将错误信息写到系统日志。
i. 使用mysqli或pdo预处理。

15. NoSQL和关系数据库的区别？

a. SQL数据存在特定结构的表中；而NoSQL则更加灵活和可扩展，存储方式可以省是JSON文档、哈希表或者其他方式。
b. 在SQL中，必须定义好表和字段结构后才能添加数据，例如定义表的主键(primary key)，索引(index),触发器(trigger),存储过程(stored procedure)等。表结构可以在被定义之后更新，但是如果有比较大的结构变更的话就会变得比较复杂。在NoSQL中，数据可以在任何时候任何地方添加，不需要先定义表。
c. SQL中如果需要增加外部关联数据的话，规范化做法是在原表中增加一个外键，关联外部数据表。而在NoSQL中除了这种规范化的外部数据表做法以外，我们还能用如下的非规范化方式把外部数据直
接放到原数据集中，以提高查询效率。缺点也比较明显，更新审核人数据的时候将会比较麻烦。
d. SQL 中可以使用JOIN表链接方式将多个关系数据表中的数据用一条简单的查询语句查询出来。NoSQL暂未提供类似JOIN的查询方式对多个数据集中的数据做查询。所以大部分NoSQL使用非规范化的数据存储方式存储数据。
e. SQL中不允许删除已经被使用的外部数据，而NoSQL中则没有这种强耦合的概念，可以随时删除任何数据。
f. SQL中如果多张表数据需要同批次被更新，即如果其中一张表更新失败的话其他表也不能更新成功。这种场景可以通过事务来控制，可以在所有命令完成后再统一提交事务。而NoSQL中没有事务这个概念，每一个数据集的操作都是原子级的。
g. 在相同水平的系统设计的前提下，因为NoSQL中省略了JOIN查询的消耗，故理论上性能上是优于SQL的。

16. 简述触发器、函数、视图、存储过程？

触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。

create trigger trigger_name
　　 after/before insert /update/delete on 表名
　　 for each row
　　 begin
　　 sql语句：（触发的语句一句或多句）
　　 end

函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）

自定义函数创建语法：
　　 创建：CREATE FUNCTION 函数名称(参数列表) 　
　　　       RETURNS 返回值类型 　函数体
 　　修改：ALTER FUNCTION 函数名称 [characteristic ...]
　　 删除：DROP FUNCTION [IF EXISTS] 函数名称
　　 调用：SELECT 函数名称(参数列表)

视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影

create view view_name as select 语句

存储过程：把一段代码封装起来，当要执行这一段代码的时候，
可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）

 create procedure 存储过程名(参数,参数,…)
　　 begin
　　 //代码
　　 end

17. 列举创建索引但是无法命中索引的8种情况。

1、如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因）
2、对于多列索引，不是使用的第一部分(第一个)，则不会使用索引
3、like查询是以%开头
4、如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引
5、如果mysql估计使用全表扫描要比使用索引快,则不使用索引
6 对小表查询
7 提示不使用索引
8 统计数据不真实
9.单独引用复合索引里非第一位置的索引列.

18. 优化数据库？提高数据库的性能

对语句的优化
①用程序中，保证在实现功能的基础上，尽量减少对数据库的访问次数；
通过搜索参数，尽量减少对表的访问行数,最小化结果集，从而减轻网络负担；
②能够分开的操作尽量分开处理，提高每次的响应速度；在数据窗口使用SQL时，尽量把使用的索引放在选择的首列；算法的结构尽量简单；
③在查询时，不要过多地使用通配符如 SELECT * FROM T1 语句，要用到几列就选择几列如：
SELECT COL1,COL2 FROM T1；
④在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。
⑤不要在应用中使用数据库游标，游标是非常有用的工具，但比使用常规的、面向集的SQL语句需要更大的开销；按照特定顺序提取数据的查找。
避免使用不兼容的数据类型
例如float和int、char和varchar、binary 和varbinary是不兼容的。
数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。
例如:
SELECT name FROM employee WHERE salary ＞ 60000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为60000 是个整型数。我们应当在编程时将整型转化成为钱币型,而不要等到运行时转化。若在查询时强制转换，查询速度会明显减慢。
避免在WHERE子句中对字段进行函数或表达式操作。
若进行函数或表达式操作，将导致引擎放弃使用索引而进行全表扫描。
避免使用!=或＜＞、IS NULL或IS NOT NULL、IN ，NOT IN等这样的操作符
尽量使用数字型字段
合理使用EXISTS,NOT EXISTS子句。
尽量避免在索引过的字符数据中，使用非打头字母搜索。
分利用连接条件
消除对大型表行数据的顺序存取
避免困难的正规表达式
使用视图加速查询
能够用BETWEEN的就不要用IN
DISTINCT的就不用GROUP BY
部分利用索引
能用UNION ALL就不要用UNION
不要写一些不做任何事的查询
尽量不要用SELECT INTO语句
必要时强制查询优化器使用某个索引
虽然UPDATE、DELETE语句的写法基本固定，但是还是对UPDATE语句给点建议：
a) 尽量不要修改主键字段。
b) 当修改VARCHAR型字段时，尽量使用相同长度内容的值代替。
c) 尽量最小化对于含有UPDATE触发器的表的UPDATE操作。
d) 避免UPDATE将要复制到其他数据库的列。
e) 避免UPDATE建有很多索引的列。
f) 避免UPDATE在WHERE子句条件中的列。

19. 数据库负载均衡

负载均衡集群是由一组相互独立的计算机系统构成，通过常规网络或专用网络进行连接，由路由器衔接在一起，各节点相互协作、共同负载、均衡压力，对客户端来说，整个群集可以视为一台具有超高性能的独立服务器。
1、实现原理
实现数据库的负载均衡技术，首先要有一个可以控制连接数据库的控制端。在这里，它截断了数据库和程序的直接连接，由所有的程序来访问这个中间层，然后再由中间层来访问数据库。这样，我们就可以具体控制访问某个数据库了，然后还可以根据数据库的当前负载采取有效的均衡策略，来调整每次连接到哪个数据库。
2、实现多据库数据同步
对于负载均衡，最重要的就是所有服务器的数据都是实时同步的。这是一个集群所必需的，因为，如果数不据实时、不同步，那么用户从一台服务器读出的数据，就有别于从另一台服务器读出的数据，这是不能允许的。所以必须实现数据库的数据同步。这样，在查询的时候就可以有多个资源，实现均衡。比较常用的方法是Moebius for SQL Server集群，Moebius for SQL Server集群
采用将核心程序驻留在每个机器的数据库中的办法，这个核心程序称为Moebius for SQL Server 中间件，主要作用是监测数据库内数据的变化并将变化的数据同步到其他数据库中。数据同步完成后客户端才会得到响应，同步过程是并发完成的，所以同步到多个数据库和同步到一个数据库的时间基本相等；另外同步的过程是在事务的环境下完成的，保证了多份数据在任何时刻数据的一致性。正因为Moebius 中间件宿主在数据库中的创新，让中间件不但能知道数据的变化，而且知道引起数据变化的SQL语句，根据SQL语句的类型智能的采取不同的数据同步的策略以保证数据同步成本的最小化。
数据条数很少，数据内容也不大，则直接同步数据。数据条数很少，但是里面包含大数据类型，比如文本，二进制数据等，则先对数据进行压缩然后再同步，从而减少网络带宽的占用和传输所用的时间。数据条数很多，此时中间件会拿到造成数据变化的SQL语句，然后对SQL语句进行解析，分析其执行计划和执行成本，并选择是同步数据还是同步SQL语句到其他的数据库中。此种情况应用在对表结构进行调整或者批量更改数据的时候非常有用。
3、优缺点
优点：

扩展性强：当系统要更高数据库处理速度时，只要简单地增加数据库服务器就可以得到扩展。
可维护性：当某节点发生故障时，系统会自动检测故障并转移故障节点的应用，保证数据库的持续工作。
安全性：因为数据会同步的多台服务器上，可以实现数据集的冗余，通过多份数据来保证安全性。另外它成功地将数据库放到了内网之中，更好地保护了数据库的安全性。
易用性：对应用来说完全透明，集群暴露出来的就是一个IP
缺点：
a) 不能够按照Web服务器的处理能力分配负载。
b) 负载均衡器(控制端)故障，会导致整个数据库系统瘫痪。

20. 数据库三大范式？

什么是范式：简言之就是，数据库设计对数据的存储性能，还有开发人员对数据的操作都有莫大的关系。所以建立科学的，规范的的数据库是需要满足一些规范的来优化数据数据存储方式。在关系型数据库中这些规范就可以称为范式。
什么是三大范式：
第一范式：当关系模式R的所有属性都不能在分解为更基本的数据单位时，称R是满足第一范式的，简记为1NF。满足第一范式是关系模式规范化的最低要求，否则，将有很多基本操作在这样的关系模式中实现不了。
第二范式：如果关系模式R满足第一范式，并且R得所有非主属性都完全依赖于R的每一个候选关键属性，称R满足第二范式，简记为2NF。
第三范式：设R是一个满足第一范式条件的关系模式，X是R的任意属性集，如果X非传递依赖于R的任意一个候选关键字，称R满足第三范式，简记为3NF.
注：关系实质上是一张二维表，其中每一行是一个元组，每一列是一个属性

21. 简述数据库设计中一对多和多对多的应用场景？

一对多：学生与班级---一个学生只能属于一个班级，一个班级可以有多个学生
多对多：学生与课程---一个学生可以选择多个课程，一个课程也可以被多个学生选择

22. 如何基于数据库实现商城商品计数器？

创建一个商城表---包含（id，商品名，每一个商品对应数量）

create table product
　　(id primary key auto_increment,
　　　 pname varchar(64),
　　　　 pcount int);

23. char和varchar的区别？

char：定长，char的存取数度相对快
varchar：不定长，存取速度相对慢
到底如何取舍可以根据一下几个方面考虑：
1、对于MyISAM表，尽量使用Char，对于那些经常需要修改而容易形成碎片的myisam和isam数据表就更是如此，它的缺点就是占用磁盘空间；
2、对于InnoDB表，因为它的数据行内部存储格式对固定长度的数据行和可变长度的数据行不加区分（所有数据行共用一个表头部分，这个标头部分存放着指向各有关数据列的指针），所以使用char类型不见得会比使用varchar类型好。事实上，因为char类型通常要比varchar类型占用更多的空间，所以从减少空间占用量和减少磁盘i/o的角度，使用varchar类型反而更有利；
3、存储很短的信息，比如门牌号码101，201……这样很短的信息应该用char，因为varchar还要占个byte用于存储信息长度，本来打算节约存储的现在得不偿失。
4、固定长度的。比如使用uuid作为主键，那用char应该更合适。因为他固定长度，varchar动态根据长度的特性就消失了，而且还要占个长度信息。
5、十分频繁改变的column。因为varchar每次存储都要有额外的计算，得到长度等工作，如果一个非常频繁改变的，那就要有很多的精力用于计算，而这些对于char来说是不需要的。

24. 在对name做了唯一索引前提下，简述以下区别：

select * from tb where name = ‘Oldboy’ -------------查找到tb表中所有name = ‘Oldboy’的数据
 
select * from tb where name = ‘Oldboy’ limit 1------查找到tb表中所有name = ‘Oldboy’的数据只取其中的第一条

25.MySQL 常见数据库引擎及区别?

'''
MySQL数据库引擎取决于MySQL在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。如果技术高超，还可以使用MySQL+API自己做一个引擎。下面介绍几种数据库引擎：
 
    ISAM：ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到 数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不 支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实 时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。
    
    MyISAM：MyISAM是MySQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MyISAM还使用一种表格锁定的机制，来优化多个并发的读写操作，其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MyISAM还有一些有用的扩展，例如用来修复数据库文件的MyISAMCHK工具和用来恢复浪费空间的 MyISAMPACK工具。MYISAM强调了快速读取操作，这可能就是为什么MySQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。MyISAM格式的一个重要缺陷就是不能在表损坏后恢复数据。
    
    HEAP：HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。
    
    InnoDB：InnoDB数据库引擎都是造就MySQL灵活性的技术的直接产品，这项技术就是MYSQL+API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MyISAM数据库引擎不支持事务处理（transaction process）也不支持外来键。尽管要比ISAM和 MyISAM引擎慢很多，但是InnoDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者 或者两者，那你就要被迫使用后两个引擎中的一个了。
    如果感觉自己的确技术高超，你还能够使用MySQL+API来创建自己的数据库引擎。这个API为你提供了操作字段、记录、表格、数据库、连接、安全帐号的功能，以及建立诸如MySQL这样DBMS所需要的所有其他无数功能。深入讲解API已经超出了本文的范围，但是你需要了解MySQL+API的存在及其可交换引擎背后的技术，这一点是很重要的。估计这个插件式数据库引擎的模型甚至能够被用来为MySQL创建本地的XML提供器（XML provider）。（任何读到本文的MySQL+API开发人员可以把这一点当作是个要求。）
MyISAM与InnoDB的区别
 
　　InnoDB和MyISAM是许多人在使用MySQL时最常用的两个表类型，这两个表类型各有优劣，视具体应用而定。基本的差别为：MyISAM类型不支持事务处理等高级处理，而InnoDB类型支持。MyISAM类型的表强调的是性能，其执行数度比InnoDB类型更快，但是不提供事务支持，而InnoDB提供事务支持已经外部键等高级数据库功能。
'''

26.简述事务及其特性?

事务具有四个特性，即 ACID 特性：
（ 1 ）原子性：事务中包括的所有操作要么都做，要么都不做。
（ 2 ）一致性：事务必须使数据库从一个一致性状态变到另一个一致性状态。
（ 3 ）隔离性：一个事务内部的操作及使用的数据对并发的其他事务是隔离的。
（ 4 ）持续性：事务一旦提交，对数据库的改变是永久的。

27.简述触发器、函数、视图、存储过程?

触发器：触发器是一个特殊的存储过程，它是MySQL在insert、update、delete的时候自动执行的代码块。
 
函数：MySQL中提供了许多内置函数，还可以自定义函数（实现程序员需要sql逻辑处理）
　　 自定义函数创建语法：
　　　　   创建：CREATE FUNCTION 函数名称(参数列表) 　
　　　       RETURNS 返回值类型 　函数体
 　　修改： ALTER FUNCTION 函数名称 [characteristic ...]
　　 删除：DROP FUNCTION [IF EXISTS] 函数名称
　　 调用：SELECT 函数名称(参数列表)
  
视图：视图是由查询结果形成的一张虚拟表，是表通过某种运算得到的一个投影
　　 　　 create view view_name as select 语句
  
存储过程：把一段代码封装起来，当要执行这一段代码的时候，可以通过调用该存储过程来实现（经过第一次编译后再次调用不需要再次编译，比一个个执行sql语句效率高）
　　 create procedure 存储过程名(参数,参数,…)
　　 begin
　　 //代码
　　 end

28.简述MySQL的执行计划的作用及使用方法?

作为程序员，难免要和数据库打交道，一般情况下，我们不是DBA ，但是又要写很多SQL，因此SQL效率就成了很大的问题。关于SQL效率优化，除了要掌握一定优化技巧外， 还得有很多经验的积累，但是这里我们可以通过执行计划对SQL进行分析，能快速找到优化的地方，这是一种很不错的方式
 
1.查看mysql执行计划
explain  select ......
2.执行计划包含的信息
Column Meaning
id	The SELECT identifier
select_type	The SELECT type
table	The table for the output row
partitions	The matching partitions
type	The join type
possible_keys	The possible indexes to choose
key	The index actually chosen
key_len	The length of the chosen key
ref	The columns compared to the index
rows	Estimate of rows to be examined
filtered	Percentage of rows filtered by table condition
Extra	Additional information
 
(1).id
含义，指示select字句或操作表的顺序。
(2).select_type
含义：select语句的类型
(3).type
含义：获取记录行采用的方式，亦即mysql的访问方式。
(4).possible_keys
含义：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用
(5).key
含义：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL
(6)key_len
含义：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度
(7)ref
含义：用于连接查询，表示具体某个表的某列被引用
(8)rows
含义：MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数,这个值是不准确的，只有参考意义。
 (9)Extra
含义：显示一些辅助的额外信息

29.1000w 条数据，使用 limit offset 分页时，为什么越往后翻越慢?如何解决?

当一个数据库表过于庞大，LIMIT offset, length中的offset值过大，则SQL查询语句会非常缓慢，你需增加order by，并且order by字段需要建立索引。
 
如果使用子查询去优化LIMIT的话，则子查询必须是连续的，某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性。
 
如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询。

30.什么是索引合并?

The Index Merge method is used to retrieve rows with several range scans and to merge their results into one. The merge can produce unions, intersections, or unions-of-intersections of its underlying scans. This access method merges index scans from a single table; it does not merge scans across multiple tables.
 
根据官方文档中的说明，我们可以了解到：
1、索引合并是把几个索引的范围扫描合并成一个索引。
2、索引合并的时候，会对索引进行并集，交集或者先交集再并集操作，以便合并成一个索引。
3、这些需要合并的索引只能是一个表的。不能对多表进行索引合并。

31.什么是覆盖索引?

如果一个索引包含(或覆盖)所有需要查询的字段的值，称为‘覆盖索引’。即只需扫描索引而无须回表。
只扫描索引而无需回表的优点：
    1.索引条目通常远小于数据行大小，只需要读取索引，则mysql会极大地减少数据访问量。
    2.因为索引是按照列值顺序存储的，所以对于IO密集的范围查找会比随机从磁盘读取每一行数据的IO少很多。
    3.一些存储引擎如myisam在内存中只缓存索引，数据则依赖于操作系统来缓存，因此要访问数据需要一次系统调用
    4.innodb的聚簇索引，覆盖索引对innodb表特别有用。(innodb的二级索引在叶子节点中保存了行的主键值，所以如果二级主键能够覆盖查询，则可以避免对主键索引的二次查询)
 
覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引不存储索引列的值，所以mysql只能用B-tree索引做覆盖索引。
 
当发起一个索引覆盖查询时，在explain的extra列可以看到using index的信息

32.简述数据库读写分离?

MySQL Proxy最强大的一项功能是实现“读写分离(Read/Write Splitting)”。基本的原理是让主数据库处理事务性查询，而从数据库处理SELECT查询。数据库复制被用来把事务性查询导致的变更同步到集群中的从数据库。 当然，主服务器也可以提供查询服务。使用读写分离最大的作用无非是环境服务器压力。
 
读写分离的好处
1、增加冗余
2、增加了机器的处理能力
3、对于读操作为主的应用，使用读写分离是最好的场景，因为可以确保写的服务器压力更小，而读又可以接受点时间上的延迟。
 
读写分离提高性能之原因
1、物理服务器增加，负荷增加
2、主从只负责各自的写和读，极大程度的缓解X锁和S锁争用
3、从库可配置myisam引擎，提升查询性能以及节约系统开销
4、从库同步主库的数据和主库直接写还是有区别的，通过主库发送来的binlog恢复数据，但是，最重要区别在于主库向从库发送binlog是异步的，从库恢复数据也是异步的
5、读写分离适用与读远大于写的场景，如果只有一台服务器，当select很多时，update和delete会被这些select访问中的数据堵塞，等待select结束，并发性能不高。 对于写和读比例相近的应用，应该部署双主相互复制
6、可以在从库启动是增加一些参数来提高其读的性能，例如--skip-innodb、--skip-bdb、--low-priority-updates以及--delay-key-write=ALL。当然这些设置也是需要根据具体业务需求来定得，不一定能用上
7、分摊读取。假如我们有1主3从，不考虑上述1中提到的从库单方面设置，假设现在1分钟内有10条写入，150条读取。那么，1主3从相当于共计40条写入，而读取总数没变，因此平均下来每台服务器承担了10条写入和50条读取（主库不承担读取操作）。因此，虽然写入没变，但是读取大大分摊了，提高了系统性能。另外，当读取被分摊后，又间接提高了写入的性能。所以，总体性能提高了，说白了就是拿机器和带宽换性能。MySQL官方文档中有相关演算公式：官方文档 见6.9FAQ之“MySQL复制能够何时和多大程度提高系统性能”
8、MySQL复制另外一大功能是增加冗余，提高可用性，当一台数据库服务器宕机后能通过调整另外一台从库来以最快的速度恢复服务，因此不能光看性能，也就是说1主1从也是可以的。

33.简述数据库分库分表?(水平、垂直)

# 水平分库
1、概念：以字段为依据，按照一定策略（hash、range等），将一个库中的数据拆分到多个库中。
2、结果：
  每个库的结构都一样；
  每个库的数据都不一样，没有交集；
  所有库的并集是全量数据；
3、场景：系统绝对并发量上来了，分表难以根本上解决问题，并且还没有明显的业务归属来垂直分库。
4、分析：库多了，io和cpu的压力自然可以成倍缓解。
 
# 水平分表
1、概念：以字段为依据，按照一定策略（hash、range等），将一个表中的数据拆分到多个表中。
2、结果：
  每个表的结构都一样；
  每个表的数据都不一样，没有交集；
  所有表的并集是全量数据；
3、场景：系统绝对并发量并没有上来，只是单表的数据量太多，影响了SQL效率，加重了CPU负担，以至于成为瓶颈。
4、分析：表的数据量少了，单次SQL执行效率高，自然减轻了CPU的负担。
 
# 垂直分库
1、概念：以表为依据，按照业务归属不同，将不同的表拆分到不同的库中。
2、结果：
  每个库的结构都不一样；
  每个库的数据也不一样，没有交集；
  所有库的并集是全量数据；
3、场景：系统绝对并发量上来了，并且可以抽象出单独的业务模块。
4、分析：到这一步，基本上就可以服务化了。例如，随着业务的发展一些公用的配置表、字典表等越来越多，这时可以将这些表拆到单独的库中，甚至可以服务化。再有，随着业务的发展孵化出了一套业务模式，这时可以将相关的表拆到单独的库中，甚至可以服务化。
 
# 垂直分表
1、概念：以字段为依据，按照字段的活跃性，将表中字段拆到不同的表（主表和扩展表）中。
2、结果：
  2.1、每个表的结构都不一样；
  2.2、每个表的数据也不一样，一般来说，每个表的字段至少有一列交集，一般是主键，用于关联数据；
  2.3、所有表的并集是全量数据；
3、场景：系统绝对并发量并没有上来，表的记录并不多，但是字段多，并且热点数据和非热点数据在一起，单行数据所需的存储空间较大。以至于数据库缓存的数据行减少，查询时会去读磁盘数据产生大量的随机读IO，产生IO瓶颈。
4、分析：可以用列表页和详情页来帮助理解。垂直分表的拆分原则是将热点数据（可能会冗余经常一起查询的数据）放在一起作为主表，非热点数据放在一起作为扩展表。这样更多的热点数据就能被缓存下来，进而减少了随机读IO。拆了之后，要想获得全部数据就需要关联两个表来取数据。
但记住，千万别用join，因为join不仅会增加CPU负担并且会讲两个表耦合在一起（必须在一个数据库实例上）。关联数据，应该在业务Service层做文章，分别获取主表和扩展表数据然后用关联字段关联得到全部数据。

34.数据库锁的作用?

根据不同的锁的作用域我们可以把数据库的锁分为三种，分别为：
  行锁：对表中的某一行进行加锁。
  页锁：对表中一组连续的行进行加锁。
  表锁：对整张表进行加锁
不同的作用域对并发性能是有很大影响的，比如说如果数据库的插入都是使用表锁，那在大量用户对某张表进行插入读取操作的话，同时只能有一个用户可以访问该表，那并发量肯定就是惨不忍睹了。
 
乐观锁
在乐观锁中，我们有3种常用的做法来实现：
 
第一种就是在数据取得的时候把整个数据都copy到应用中，在进行提交的时候比对当前数据库中的数据和开始的时候更新前取得的数据。当发现两个数据一模一样以后，就表示没有冲突可以提交，否则则是并发冲突，需要去用业务逻辑进行解决。
 
第二种乐观锁的做法就是采用版本戳，这个在Hibernate中得到了使用。采用版本戳的话，首先需要在你有乐观锁的数据库table上建立一个新的column，比如为number型，当你数据每更新一次的时候，版本数就会往上增加1。比如同样有2个session同样对某条数据进行操作。两者都取到当前的数据的版本号为1，当第一个session进行数据更新后，在提交的时候查看到当前数据的版本还为1，和自己一开始取到的版本相同。就正式提交，然后把版本号增加1，这个时候当前数据的版本为2。当第二个session也更新了数据提交的时候，发现数据库中版本为2，和一开始这个session取到的版本号不一致，就知道别人更新过此条数据，这个时候再进行业务处理，比如整个Transaction都Rollback等等操作。在用版本戳的时候，可以在应用程序侧使用版本戳的验证，也可以在数据库侧采用Trigger(触发器)来进行验证。不过数据库的Trigger的性能开销还是比较的大，所以能在应用侧进行验证的话还是推荐不用Trigger。
 
第三种做法和第二种做法有点类似，就是也新增一个Table的Column，不过这次这个column是采用timestamp型，存储数据最后更新的时间。在Oracle9i以后可以采用新的数据类型，也就是timestamp with time zone类型来做时间戳。这种Timestamp的数据精度在Oracle的时间类型中是最高的，精确到微秒(还没与到纳秒的级别)，一般来说，加上数据库处理时间和人的思考动作时间，微秒级别是非常非常够了，其实只要精确到毫秒甚至秒都应该没有什么问题。和刚才的版本戳类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致则OK，否则就是版本冲突。如果不想把代码写在程序中或者由于别的原因无法把代码写在现有的程序中，也可以把这个时间戳乐观锁逻辑写在Trigger或者存储过程中
 
悲观锁（排他锁）
悲观锁也称之为互斥锁，可以写为X锁，指的是同时只能有一个事务可以对某个资源进行访问操作。如果有两个事务同时要操作某张表，我们称之为事务A和事务B，如果事务A获得了这张表的表锁，那事务B只能等待事务A释放了这个锁之后才能对该表进行操作。
 
数据库的insert，update操作默认是采用互斥锁进行加锁，读取select则不是，如果要对select操作使用互斥锁，可以这样写
 
select * from table where id = 1 for update
共享锁
共享锁是一种乐观锁，可以写为S锁，在数据库中共享锁的作用主要是针对读取操作的。如果读取操作使用X锁的话，并发量会非常低，所以数据库提供了共享锁S锁，提高读取操作的并发性能，多个事务可以同时持有一个资源的S锁，不像X锁，同时只能有一个事务持有。
 
举个例子：
 
事务A和事务B对表TABLE进行访问，事务A想查看id = 1的行信息
 
select * from TABLE where id = 1 lock in share mode
如果当前id = 1的行对应的X锁没有被其他事务获取，那事务A就顺利的获得了该行的S锁。
 
现在事务B也想查看id = 1 的行信息，会怎么样？
 
select * from TABLE where id = 1 lock in share mode
现在持有该行锁的只有事务A，持有的是S锁，所以事务B也可以获取该行的S锁，两个事务可以并发的读取id = 1的行。
 
这个和之前所说的乐观锁实现是有区别的，最大的不同就是读取的时候共享锁是要真的去持有锁，但是乐观锁只是实现了一种CAS模式，但是并读取的时候没有真的持有锁。

35.where 子句中有 a,b,c 三个查询条件, 创建一个组合索引 abc(a,b,c)，以下哪种会命中索引

(a)   *****
(b)
(c)
(a,b)    *****
(b,c)
(a,c)
(a,b,c)   *****

36.mysql 下面那些查询不会使用索引

between, like "c%" , not in, not exists, !=, <, <=, =, >, >=,in

37.mysql 中 varchar 与 char 的区别以及 varchar(50)中的 50 代表的含义

varchar与char的区别char是一种固定长度的类型，varchar则是一种可变长度的类型 
尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间，	
其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
 
varchar(50)代表的含义：
varchar(50)中50的涵义最多存放50个字符，varchar(50)和(200)存储hello所占空间一样，但后者在排序时会消耗更多内存，
因为order by col采用fixed_length计算col长度(memory引擎也一样)

38.从delete语句中省略where子句,将产生什么后果?

A. delete语句将失败因为没有记录可删除 
B. delete语句将从表中删除所有的记录    *****
C. delete语句将
标签：语句,面试题,数据库,SQL,查询,索引,Mysql,数据	

From： https://www.cnblogs.com/ycmyay/p/17382841.html

Mysql面试题