SQL相关面试题

1.关系性数据库

什么是关系性数据库，可以理解为二维的数据库，标的格式类似于的Excel，有行有列，其实用的关系模型来组织数据库的模型的模型

2.SQL的执行顺序

from>join>where>group by>（聚合函数）>having  >select >order by >limit

1. from 需要确定从哪张表中找到数据
2. join 用于将两张表连接起来的，连接的类型分为：自然连接，内连接，外连接，笛卡尔连接；
3. where ：对索引添加条件
4. group by：分组语句
5. 聚合函数：count，max min avg
6. select ：插叙结果中找到的需要的数据
7. Distinct：去重
8. order by：进行排序
9. limit ：抽取结果

3.MYSQL存储引擎：

        mqsql常用的引擎的包括的innodb，myisam，memory以及merge等；

1. MYISAM：全表锁，拥有高效 的运算速度，不支持事务，不支持外键，并发能力不好的，对事务处理没有要求，以select以及insert的操作可以使用改引擎；
2. innodb：拥有的行级锁，支持事务处理的（提交，回滚），支持自动自动增长，支持外键约束，并发处理能力强。
3. Memory：全表锁，村吃在的内存中，速度块，但是会在占用和数量成正比的内存空间中，其数据库重启之后不能恢复，默认使用HasH索引，索引效率高
4. MeMory：是MeMory的组合

4.MeMory以及innodb之间的差异

对比项	innodb	MeMory
事务	支持	不支持
锁类型	行锁，表锁	表锁
缓冲	缓冲数据和索引	只缓冲索引
主键	有	可以没有
索引	B+树，主键是聚簇索引	B+树
select * from table	慢	快
hash索引	支持	不支持
外键	支持	不支持
全文索引	支持	支持
记录顺序	支持	支持

5.SQL注入的理解

        SQL将SQL代码伪装输入到参数中，传递到服务器解析并且执行的攻击手法，换而言之在对服务器发起的请求参数中的植入一些SQL代码，SERVER端在执行SQL操作时，会拼接对应的参数，同时将一些SQL注入的SQL连接起来导致一些预期之外的操作。

        SQL注入的解决：使用正则参数化过滤传入的参数，使用的绑定参数的放置参数的注入。

InnoDB的四大特性：

        1.insert/change buffer

        插入缓冲区之前的版本称之为的insert buffer 现在称之为的change buffer，主要提升的是性能，而insert buffer只对insert 命令行有效，change buffer只对insert 、update、delete以及purge有效。对于的update的操作分为两个过程：将记录标记为的已经删除，真正将记录删除

        只对于非聚集索引的插入和更新有效，对于每次的插入和更新的并不是写到索引页中的，而是判断插入的非聚集索引页是否在的缓冲池中。如果在缓冲池中则直接插入，若不在那么就直接放入插入缓冲区中的，按照一定的频率进行的合并操作的，再写回到的disk中，目的是减少的随机IO到来的性能的提升。

        其实用的条件：辅助索引，所以不是唯一的

        2.两次写

        insert buffer表示的是性能上的提升，两次写带来的是数据的性能可靠性的提升。其中两次写主要由两部分组成，一部分是doublewrite，大小为2M；另一部分的是磁盘上共享表空间中连续的128页，其大小都是尺度为2M的空间。对缓冲区数据脏页进行刷新时，并不直接写到磁盘中，首先将脏页中的数据添加到的内存中doublewrite中，之后分两次的每次1M的添加到的共享的磁盘表空间中，之后调用fsync函数同步磁盘中的数据。

        3.自适应Hash结构

        哈希算法查找的时间复杂度的为O(1)，而利用的B+数的查找次序为3-4层，故需要查询的次数为3-4次查询数据。

        InnoDB存储引擎会建立会监控的对表上各种索引的查询的，如果观察到哈希索引带来的速度的提升，则建立的哈希索引结构。自称为的自适应哈希的索引。

        4，预读处理

        预读操作是一种IO操作，用于异步将磁盘的中的数据的读取到buffer pool中，预料中的数据页会被马上读到，预读请求中的所有页会被添加到的一个的范围之内。InnoDB共有两个的预读算法。

        线性预读技术：在buffer pool中被访问到的数据的他临近的页会被的很快的访问到。能够通过的调整连续访问的页的数量来控住InnoDB中的预读操作，使用参数InnoDB_read_ahead_threshold配置，添加这个参数前，InnoDB会读取的当前区段的最后一页才开始进行预读。

        随机预读：通过buffer pool存中的预测哪些页很快被访问，而不考虑这些页的读取顺序，如果发泄的buffer pool中存在的一个区段的13个连续的页，如果的innoDB会异步发起预读的请求占据的这个页。

6.索引结构

        索引是为了帮助的MYSQL高效读取数据的数据额结构，通俗的来书的索引相当于的针对的整个数据库的目录，目的是增加数据库的数据查询速度，索引通常保存在的硬盘文件中。常见的索引结构包括的，聚集索引，覆盖索引，组合索引，前缀索引以及前缀索引。

        聚簇索引：不是一种索引类型，而是一种数据存储方式，这种存储方式是通过依靠的B+树来实现的，根据表中的主键构造一颗B+树且B+树叶子节点存放的是表的行记录的信息，称该主键索引为聚簇索引。可以理解为将数据存储和索引存放在一块，找到了索引就找到了的数据。

        优点：数据访问更快，因为聚簇索引将索引和数据的保存在一个 B+树中，举措索引对于主键的排查和范围查找速度快

        缺点：插入速度严重依赖插入顺序，按照主键的顺序插入式最快的方式，否则会出现的分裂。主键的更新代价会更高。

         非聚簇索引：非聚簇索引和索引的分开的，B+树叶子节点的存放的不是数据表的行记录，二是主键。

        缺点：二级索引的访问需要进行两次索引查找，第一次找到主键值，第二次根据的主键值找到行数据。

        索引覆盖：如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为“索引覆盖”。

7.SQL优化

1.         大表查询如何进行优化        优化表结构，SQL语句以及查询命令；添加缓冲区；主从复制，读写分离；垂直拆分，根据模块的的耦合度，将一个大的系统分为多个小的系统，也就是分布式系

•          避免的不走的索引的场景：尽量避免在字段开头模糊查询，会导致数据引擎放弃索引进行全表扫描。例如：

SELECT * FROM T WHERE USENAME LIKE `%陈%`

    优化后的结果为：

SELECT * FROM T WHERE USENAME LIKE `陈%`

•         避免使用的in和not in会导致引擎表的全表扫描

SELECT * FROM T WHERE IN (2,3)

        使用between代替

SELECT * from t between 2 and 3

如果的使用子查询，可以使用的exists代替

---不走索引
SELECT * FROM A WHERE A.ID IN (SELECT ID FROM B)
---走索引
SELECT * FROM A WHERE  EXISTS(SELECT * FROM B WHERE B.ID=A.ID)

超大分页是如何处理的

        数据库层面，这也就是我们主要几种关注的，类似于SELECT * FROM TABLE WHERE AGE>20 LIMIT 10000,10 这种查询有可以优化的余地，这句语句需要load 10000个然后取10个记录，SELECT * FROM TABLE WHERE ID IN(SELECT ID FORM TABLE WHERE AGE>20 LIMIT 10000,10)，我们可以知道如果ID是连续的则可以使用SELECT * FROM TABLE WHERE AGE>10000 LIMIT 10

慢查询日志

        通过慢查询日志

8.锁：

        锁是数据库系统区别于文件系统的一个关键特性，锁机制用于管理对共享资源的并发访问。下面我们以MySQL数据库的InnoDB引擎为例，来说明锁的一些特点。

        锁的类型的，在innodb中有两种类型的关键锁：共享锁：运行事务读取一行数据；排它锁：允许事务的删除和更新某个数据。

         如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，称这种情况为锁兼容。但若有其他的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁，这种情况称为锁不兼容。下图显示了共享锁和排他锁的兼容性，可以发现X锁与任何的锁都不兼容，而S锁仅和S锁兼容。需要特别注意的是，S和X锁都是行锁，兼容是指对同一记录（row）锁的兼容性情况。

   对读取的操作进行的进行添加S锁的记录为

SELECT ... LOCK IN SHARE MODE

   对读取的才做添加排斥X锁

SELECT ... FOR UPDATE;

        锁的粒度：Innodb存储引擎的支持多粒度的锁定，这种锁定事务在行级别上的锁和表级别上的锁同时存在。该引擎支持一种额外的锁的方式，称为意向锁。意向锁是将锁的对象划分为多种村次，意向锁锁一位置事务希望在更细粒度上进行加锁。

• 意向共享锁（IS Lock），事务想要获得一张表中某几行的共享锁。
• 意向排他锁（IX Lock），事务想要获得一张表中某几行的排他锁。

InnoDB中的中行的数据结构：

• Record Lock：单个行记录上的锁。
• Gap Lock：间隙锁，锁定一个范围，但不包含记录本身。
• Next-Key Lock∶Gap Lock+Record Lock，锁定一个范围，并且锁定记录本身

9.数据库的乐观锁和悲观锁

        数据库管理系统中的并发控制的是保存多个事务同时的存取同一个数据时不破坏事务的隔离性和统一性。

        悲观锁：假定会产生冲突，屏蔽 一切可以可能违法数据完整性的操作。在查询数据的时候将 数据锁起来，直到提交事务提交，实现：使用数据数据库中的锁机制。常用在修改操作

        乐观锁：假设是不会发生数据并发冲突，只在提交操作时检查是否违法的数据完成整性。在修改数据的时候把事务的锁起来的，通过的version实现，乐观锁使用的版本号机制实现。常用语读操作。

10.如何解决死锁

        死锁是指多个的事务在同一资源商相互占用，互相请求对方的资源，从而导致恶性循环。

        常用来的解决死锁的方法：如果多个程序会并发的存取多个表，尽量约定以相同的顺序访问的标，可以大大的降低死锁的机会；在同一事务中，尽可能的做到一次锁定所需的所有资源，减少死锁产生的概率。

        MYSQL中，INNODB引擎使用了一种引擎机制，当数据库的江策到死锁发生时，会选择事务较小的一个事务进行回滚操作。

11.隔离级别和锁之间的关系

        READ-unconmmitted(读取未提交）,最低的隔离级别，允许读取未提交的数据表更，可能会导致脏读，不可重复读以及幻读产生。读取操作不需要添加共享锁

        READ-conmmitted(读取未提交）：允许读取并发事务已经提交的数据，可以防止脏读，但是无法避免的幻读和不可重复读。   读操作的需要的加共享锁

        REPEATABLE-READ(可重复读)：对同一字段多次读取的结果是一致的。除非数据本身自己修改，可以防止脏读和幻读的产生。读操作需要进行枷锁

        SERIALIZABLE（可串行化）：最高的隔离级别，完全的服从ACID的隔离界别，事务的依次逐个进行，这样事务的之间不可能的产生干扰。

12.视图

        视图是虚拟的表，与包含数据的表是不一样的，视图只包含使用时动态检索数据的查询。

        视图的特点：1，视图的列可以由来自不同的表，是表抽象和逻辑意义上建立的关系；2，视图是由基表产生的表；3，视图的建立和删除不影响基本表；4，对视图内容的更新影响虚表；5：当视图的来自多个基本表，不允许添加和删除数据。

        视图表的使用：视图表的基本用于以下的几个场景中：1，重用的SQL语句；2，简化复杂的SQL操作；3，使用表的组成部分而不是整个表；4，保护数据。5：更改数据格式和表示。

        视图的优点：查询简单化；数据安全；逻辑数据独立性

        视图的缺点：性能的降低；修改限定的权限

13.游标

        游标是系统为用户开设的数据缓冲区，存放SQL语句的执行结果，每个游标区都有一个 名字，用户可以通过的游标的逐一回去记录并赋值给主表量

14.事务

        事务就是由单独单元的一个或多个sql语句组成，在这个单元中，每个sql语句都是相互依赖的。而整个单独单元是作为一个不可分割的整体存在，类似于物理当中的原子（一种不可分割的最小单位）。

        事务的四大特性

        原子性（Atomicity）：指事务是一个不可分割的最小工作单位，事务中的操作只有都发生和都不发生两种情况
        一致性（Consistency）：事务必须使数据库从一个一致状态变换到另外一个一致状态，举一个栗子，李二给王五转账50元，其事务就是让李二账户上减去50元，王五账户上加上50元；一致性是指其他事务看到的情况是要么李二还没有给王五转账的状态，要么王五已经成功接收到李二的50元转账。而对于李二少了50元，王五还没加上50元这个中间状态是不可见的。
        隔离性（Isolation）：一个事务的执行不能被其他事务干扰，即一个事务内部的操作及使用的数据对并发的其他事务是隔离的，并发执行的各个事务之间不能互相干扰。
        持久性（Durability）：一个事务一旦提交成功，它对数据库中数据的改变将是永久性的，接下来的其他操作或故障不应对其有任何影响

        事务可以划分显示事务和隐式的事务：

        隐式事务：该事务没有明显的开启和结束标记，它们都具有自动提交事务的功能；不妨思考一下，update语句修改数据时，是不是对表中数据进行改变了，它的本质其实就相当于一个事务

        显示事务：该事务具有明显的开启和结束标记；也是本文重点要讲的东西。使用显式事务的前提是你得先把自动提交事务的功能给禁用。禁用自动提交功能就是设置autocommit变量值为0（0:禁用 1:开启）

       事务的类别包括：扁平事务（Flat Transactions）；带有保存点的扁平事务（Flat Transactions With Savepoints）；链事务（Chained Transactions）；嵌套事务（Nested Transactions）；分布式事务（Distributed Transactions）

15，explain 优化查询语句
       

         我们会有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，些时我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节。慢查询记录，数据库记录查询过程中执行超过某一个时间点的查询命令。

        通过explain进行优化查询能够获得以下的查询记录

                表的读取顺序
                数据读取操作的操作类型
                哪些索引可以使用
                哪些索引被实际使用
                表之间的引用
                每张表有多少行被优化器查询

        通过执行以下的查询命令可以得到命令查询结果

explain select  * from table

包含以下10行的内容

id	在每个大的查询语句中，每个SELECT关键字对应一个位移的id
select type	SELECT 关键字对应的查询类型
table	对应的表名
partition	匹配的分区信息
type	针对单表的访问方法
possible_keys	可能用到的索引
key	实际用到的索引
ken_len	实际用到的索引的长度
ref	当时用索引列时等值查询时，与索引列进行等值查询时匹配的对象信息
rows	预计读取的记录条数
fitered	正对的预估的需要读取

select type:查询类型，主要用于鱼粉普通查询，联合查询，子查询的查询

•  simple：查询语句中不包括UNION或者子查询的查询可以称之为SIMPLE类型
•  PRIMARY ：对于包含的UNION   UNION ALL 或者子查询的大查询来说，他由几个小的查询组成
• UNION ：对于包括UNION  UNION ALL的大查询来说，它由几个小的查询组成；除了最左端的那个查询外，其他的查询方式为UNION

table：表示的进行explain执行的过程中访问的表的名字

partition：当前查询使用那个分片键，分片的类型range list hash等信息，但是现在的mysql并不会的使用分区。

type：连接类型，有如下几种取值

      创建如下所示的数据表：

CREATE TABLE sing_table(
    id int not null auto_increment,
    key1 varchar(100),
    key2 int,
    key3 varchar(100),
    key_part1 varchar(100),
    key_part2 varchar(100),
    key_part3 varchar(100),
    common_field vaarchar(100).
    primary key(id),
    key key1(key1),
    unique key ukey2(key2),
    key idx_key3(key3),
    key idx_key_part(key_part1,key_part2,key_part3)

)Engine =InnoDB,CHARSET=UTF8

•  const

   通过主键值或者的唯一二级主键索引值和某个的常值进行比较

SELECT * from sing_table WHERE id = 128

SELECT * from sing_table WHERE key2 = 128

• ref    有时需要将二级索引的列值的名与常数进行比较时

SELECT * from sing_table WHERE key1="abc"

• ref_or_null

        有时我们不仅仅想找到某个的二级索引的列的值等于某个常数的记录，而且还想把列中的值为NULL的记录中的值找出来的。

SELECT  * FROM sing_table WHERE key1="abc" or key1 is null

• range

在对索引的某个列与某个常数额比较时，才会使用的到前文介绍的几种方法，进行范围中的数据。

SELECT * FROM sing_table WHEERE KEY2  IN (1838,6652) OR (key2 >=38 AND key2 <=79)

• index

SELECT key_part1,key_part2,key_part3 FROM  sing_table where key_part2 = "abc"

• all

select * from table

possible_keys：在进行explain进行查询时的，可能用到的哪些索引

key：实际用到的索引类型

key_len：表示索引的中使用的字节数，可以通过该列值计算查询中使用的索引的长度，改长度为最大可能的长度，并非时间使用的长度，根据表定义可以得到

ref：type类型为const ref等类型时对应的等值匹配的值是啥

rows：估算找到所需要的记录所需要读取的行数

filtered：该列表示按表条件过滤的表行的估计百分比。最大值为100，这表示未过滤行。值从100减小表示过滤量增加。

Extra：该字段包含有关MySQL如何解析查询的其他信息