标签：入门 索引 where MySQL 查询 面试 SQL 数据

MySQL

一、基础

1.SQL语句入门

select * from A inner join B on B.id=A.id group by B.id having B.id>10 where B.oid!=5 order by B.id desc limit 5;

如上, 瞎写了一条SQL, 不一定能跑出来, 不过写完SQL基础五十题, 这种程度的SQL题基本随便乱写, 就是这个意思.

还处于对SQL语句一知半解的时候, 没有练习之处, 也搞不明白建表语句, 数据来源, 题源的, 直接从这里开始练习, 花个两三天做完, 基本上SQL查询可以说是入门了.

SQL50题(我的库表语句及解题代码): https://www.cnblogs.com/wang-jifeng/p/16733029.html

题源: https://zhuanlan.zhihu.com/p/43289968

二、MySQL进阶知识

架构, 日志, 执行流程, 优化面试都会问, 随级别加深, 背索引和优化和一些概念就行了.

1. MySQL底层架构

1.1 逻辑架构

略

1.2 工作流程

建立连接

MySQL客户端通过协议与MySQL服务器建立连接, 发送查询语句

查询缓存

检查查询缓存中是否存在要出查询的数据, 若命中则直接返回结果, 不会再对查询进行解析, 优化, 执行, 仅将缓存中的结果返回给用户, 极大提升了性能, 未命中缓存则开始解析SQL语句

语法解析器和预处理

MySQL首先根据语法规则验证SQL语法正确性, 然后通过关键字将SQL语句进行解析, 并生成一棵对应的 解析树 , 预处理器 则根据一些MySQL规则进一步检查解析树是否正确.

生成执行计划

当解析树被确认为正确, 就会由 查询优化器 将解析树转化为执行计划(profile). 一条查询可以有很多种执行方式, 最后都返回相同的结果, 优化器的作用就是找到这其中最好的执行计划.

按计划执行并获取数据

MySQL默认使用B+Tree索引, 并且一个大致方向是: 无论怎么调整SQL, 至少在目前来说, MySQL最多只用到表中的一个索引.

1.3 日志系统(TODO)

MySQL中有7种日志文件, 分别是: 重做日志( redo log), 回滚日志( undo log), 二进制日志( bin log), 错误日志(error log), 慢查询日志(slow query log), 一般查询日志(genneral log), 中继日志(relay log).

需要注意的如下:

重做日志 - redo log

回滚日志 - undo log

二进制日志 - bin log

慢查询日志 - slow query log

中继日志 - relay log

关于MySQL日志参考: https://blog.csdn.net/pengchenxin/article/details/123627046

SQL语句执行流程(TODO)

2. 索引原理

MySQL底层的数据是以B+树的形式进行存储的, 表中展现出来的一行一行的数据只是表现形式.

首先在分析索引原理和底层存储结构之前, 需要了解索引的种类.

2.1 索引的种类

从概念的角度分为两种, 聚簇索引和非聚簇索引.

聚簇索引: 索引树的叶子上保存了数据;

非聚簇索引: 树上未保存数据, 只保存了对应的主键值, 因此需要进行一次回表才能获取到具体数据.

主键索引就是聚簇索引, 聚簇索引就是主键索引, 一个表中只能有一个主键, 也只能有一个聚簇索引

主要索引种类:

1. 普通索引: 创建时不附加任何限制条件, 用于提高查询效率, 可以创建在任何数据类型中, 其值是否唯一和非空由字段本身的约束条件决定;
2. 唯一性索引: 要求字段唯一但可以为空;
3. 主键索引: 要求字段唯一且不为空, 每张表仅能有一个主键索引;
4. 单列索引: 仅由一列作为索引;
5. 多列(联合)索引: 多列作为索引;
6. 全文索引: 关系型数据库中全文索引不是重点, 全文索引的概念更多的是在非关系型数据库中得到广泛引用, 如搜索引擎的常见技术支撑 elasticsearch , solr , 分词器等, 需要借助分词器按照一定的规则, 将数据进行分词, 然后进行索引维护;

2.2 索引原理

聚簇索引的维护

1. MySQL底层是将数据以B+树的形式存储的, 对于innoDB 来说, 数据存储的最小单位是页, 存储数据的就是数据页, 一页的默认大小是16KB, 如果每条数据占1KB, 那么每个数据页就能存储16条数据.
2. 在每个数据页内部, 从前往后依次根据主键值进行排序, 查找时根据二分法进行查找, 同时每个数据页也具有自己的编号.
3. 数据页之间形成双向链表, 从而在得到某个主键值的位置后, 能快速基于该主键值进行筛选前后的数据.
4. 在根据数据页内部的主键范围和数据页的编号, 向上一层进行汇总, 得到页的目录, 以此类推, 得到最上层的根节点, 这个形式如同redis的跳表形成方式.

非聚簇索引的维护

非聚簇索引的B+树中目录页保存的是 索引值 和 数据页编号 , 而数据页中保存的是 索引值 和 主键值 .

当通过非聚簇索引查询到对应数据的主键值后, 需要进行回表才能获取到完整的数据记录.

如果select需要返回的数据在非聚簇索引中存在, 那么就不需要回表了, 如查询 索引值 和 主键值 ;

--需要回表
select emp_id,emp_name,emp_age,emp_subject from t_emp where emp_age=30;

--不需要回表
create index idx_emp_name on t_emp(emp_name);
select emp_id,emp_name from t_emp where emp_name like 'tom%';

估算保存数据量

如果每条数据占1KB, 目录页中每条数据页信息占16B(主键8B,页码8B),那么每个目录页中能保存1024个数据页.
因此当B+树的高度如下,能保存的数据分别是:
2层:1024*16
3层:1024*1024*16
4层:1024*1024*1024*16

B树和B+树的概念及异同(详细见算法与数据结构篇)

B树，概括来说是一个节点可以拥有多于2个子节点的二叉查找树

B+树叶子节点保存数据, 并且叶子节点形成双向链表, 可以方便的遍历

B和B+树的区别在于，B+树的非叶子结点只包含导航信息，不包含实际的值，所有的叶子结点和相连的节点使用链表相连，便于区间查找和遍历。
--------------------------------------------------------
资料来源: https://blog.csdn.net/ChaoticNg/article/details/114588507
还有文件预热的概念,挺有意思的.

资料来源: https://www.wolai.com/dyKEuzD3ofbMbg1fqtd1n1

3. 优化(SQL优化和存储结构优化)

3.1索引失效

所有具有过滤功能的子句都会将相关字段去和索引尝试匹配, 如: on, where, group by, having, limit子句.

最左原则(重要)

关于最左原则, 主要是针对联合索引来说的, 在开发过程中, 对于多个可能会出现在子句过滤条件中的列, 通常建议建立联合索引, 而不是分别建立单列索引, 因为每一个索引的建立, 底层都需要单独维护一棵索引树, 在对数据每一次进行更改操作时, 每颗索引树的维护都消耗了系统资源, 过多的建立索引, 会增大系统开销.

现有联合索引形如(a, b, c)
可能存在的查询条件如下
1. abc, acb 生效
2. bac, bca 生效
3. cba, cab 生效
当顺序与联合索引顺序不一致时, 可以生效, 但这是由于SQL优化器调整了SQL语句查询条件的顺序, 这也会产生系统开销
4. ab, ba 生效 理由同上
5. ac, ca 部分生效
这种情况下, 只利用到了a字段的索引, 之后的c字段没有利用到索引,
6. bc, cb 失效

总结来说一句话, 最左必须有, 中间不能断 , 只有这样才能完全利用到整个联合索引.

分析: 对于联合索引 (a, b, c) 来说, 相当于在索引树中按照这个优先级来对每条数据进行排序, 依照顺序才能在这个索引树中进行查找, 提高效率, 违反最左原则就不能去索引树中进行查找, 因为对于过滤条件来说, 这棵树不满足条件.

范围查询

范围查询会使其后的索引失效, 因此应尽量将范围查询放在过滤条件的最后.

SELECT SQL_NO_CACHE * FROM A WHERE A.age=30 AND A.deptId>20 AND A.name='abc';

SELECT SQL_NO_CACHE * FROM A WHERE A.age=30 AND A.name='abc' AND A.deptId>20;

在where子句条件中使用函数

使用函数会导致索引失效

select SQL_NO_CACHE * from emp where left(name, 3)="abc";

不等于

所有不等于操作都会导致索引失效 != , <> , not .

关于exit和in走不走索引

like

1. 条件没有%: 走索引;
2. %放前面: 不走索引;
3. %放中间: 走索引;
4. %放后面: 走索引;

总结: %在最左的情况, 索引失效.

涉及类型转换

传入数据类型和字段类型不一致, 可能会导致索引失效.

--失效情况
select sql_no_cache * from emp where name=123;
--索引仍然有效的情况
select sql_no_cache * from emp where age = '123';

总结: 加上引号, 能避免索引失效.

3.2 SQL优化流程

（SQL优化的流程-怎么考虑SQL优化）？

1. 首先，使用慢查询日志等功能进行低效率SQL的检索和过滤，筛选出需要优化的SQL；
2. 其次，考虑SQL语句本身的层级结构，避免堆叠复杂的语句结构，避免使用函数和复杂运算，查询条件不要使用复杂的条件运算等，如果可以放在Java程序中进行运算的逻辑，尽量放在程序中，而不是放在SQl语句里去进行拼接；
3. 然后是避免索引失效以及建立高效索引；

详细过程:

开启慢查询日志, 然后筛选效率过低的SQL语句,

使用Explain关键字对SQL语句进行分析, 可以模拟优化器执行SQL查询语句, 从而知道MySQL是如何处理该SQL的, 分析查询语句或是表结构的性能瓶颈.

对于SQL语句的基本优化规则如下:

关联查询优化

1.小表驱动大表: 因为在执行的时候是先读取驱动表的所有数据, 再根据连接条件筛选被驱动表中的数据;

2.保证被驱动表的join字段被索引, join字段就是作为连接条件的字段, inner join时, MySQL会自动将小表作为驱动表;

3.子查询尽量不要放在被驱动表, 子查询会生成衍生表, 衍生表建不了索引;

4.能用关联查询就不用子查询;

5.关联查询涉及到的表不要超过3个;

子查询优化

能不用子查询尽量不用子查询

排序分组优化

1. order by子句需要配合limit子句才能让索引生效;
2. 如果涉及多个字段的排序, 那么这些字段排序的方向要一致(要么都升序, 要么都降序), 否则索引失效

--索引失效
SELECT SQL_NO_CACHE * FROM EMP ORDER BY AGE;
--索引生效
SELECT SQL_NO_CACHE * FROM EMP ORDER BY AGE LIMIT 10;


--生效
SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId desc limit 10;
--失效
SELECT SQL_NO_CACHE * FROM emp ORDER BY age desc,deptId asc limit 10;

3.3 存储结构的优化(分库, 分表, 结合冷热分离, 查询分离等思想)

拆分方式	特点	场景
水平分库	库结构及功能一致, 以hash散列或其它规则的方式将表拆分到不同的库中
垂直分库	库结构及功能不一致, 根据业务需求或其它规则将表划分到不同的库中	冷热分离, 查询分离
水平分表	表结构一致, 以hash散列或其它骨子额将表数据拆分到其它表中
垂直分表	表结构不一致, 将一张表的列进行拆分, 根据业务需要, 如访问量查询量等需求, 划分到不同的表	冷热分离

冷热分离: 基于访问频率对数据进行区分, 热数据使用缓存, 冷数据持久化, 诸如此类.

查询分离: 基于读写场景需求不同, 分为读库和写库, 数据一式两份, 由写库同步到读库, 读请求在读库, 写请求在写库, 大大提高系统并发性能, 例如使用MySQL作为写库, 使用ElasticSearch作为读库, 将MySQL的数据同步到ElasticSearch.

主要参考:

https://www.wolai.com/4UcNv48AKShsviyt45cQCB

日志系统: https://blog.csdn.net/pengchenxin/article/details/123627046

4.集群和复制架构

待补充

三、面试常问

1、概念

1、ACID

（事务必须具有ACID，ACID是什么）

（Atomicity）原子性：事务中的每个操作，要么一起成功，要么一起失败回滚；

（Consistency）一致性：事务执行前后，数据库完整性没有被破坏（数据库结构发生改变），写入数据符合预设规则，资料精确度、串联性、以及后续数据库自发性完成预设动作；

（Isolation）隔离性：多个事务并发执行时，由于交叉执行导致数据的不一致，此处就涉及到4种隔离级别；

（Durability）持久性：事务结束后，数据的修改是永久的，即使系统故障也不会丢失；

2、隔离级别

（数据库事务的隔离级别）

3种问题：

1. 脏读：事务A修改了数据，还未提交；此时事务B读取到了修改后的数据；事务A又进行了回滚；
2. 不可重复读：事务A进行了修改；事务B读取到了修改；事务C又进行了修改；事务B又读取到了修改；即在一次事务中，某个事务对同一条数据进行重复读取出现数据不一致；
3. 幻读：两个事务同时执行的时候, 第一个事务对表中的数据进行修改, 同时这种修改涉及到表中的全部行, 第二个事务向表中插入一条数据, 第一个事务进行检查的时候发现没有被修改的行, 如同幻觉一样, 即幻读.

4种隔离级别：

读未提交：能读到另一事务未提交的修改；

读已提交（解决脏读）：能读到另一事务已提交的修改；

可重复读（解决不可重复读）：A事务进行修改，B事务进行读取；交叉操作的情况下，只有B事务和A事务都结束了，B事务才能读取到A进行修改后的数据；

串行化（解决幻读）：串行执行事务

3、悲观锁和乐观锁(在MySQL中的实现方式)

乐观锁和悲观锁是锁的概念, 并不仅存在MySQL中.

（谈一谈悲观锁和乐观锁）

参考: https://blog.csdn.net/weixin_45433031/article/details/120838045

悲观锁是在进行操作时, 默认会有其它线程对对象进行更改;

MySQL中使用悲观锁: 必须关闭MySQL数据库的自动提交属性 set autocommit = 0 , 因为MySQL默认该模式, 当执行一个更新操作后, MySQL会立刻将结果进行提交

//开始事务
begin;
//1.查询出商品库存信息
select quantity from items where id=1 for update;
//2.修改商品库存为2
update items set quantity=2 where id = 1;
//3.提交事务

在对id=1的记录修改前, 先通过for update的方式进行加锁, 然后再进行修改, 这就是比较典型的悲观锁策略, 如果发生并发, 同一时间只有一个线程可以开启事务并获得id=1的锁, 其它的事务必须等本次提交之后才能执行, 这样可以保证当前的数据不会被其它事务修改.

使用 select ... for update 锁数据, 需要注意锁的级别, MySQL InnoDB默认行级锁, 行级锁都是基于索引的, 如果一条SQL语句用不到索引时不会使用行级锁的, 会使用表级锁把整张表锁住, 需注意.

乐观锁是假设数据一般情况下不会造成冲突, 所以在数据进行提交更新的时候, 才会对数据的冲突已否进行检测, 如果冲突, 则返回给用户异常信息, 让用户决定如何去做. 乐观锁适合读多写少的场景, 这样可以提高程序的吞吐量, 了光速哦不会刻意使用数据库本身的锁机制, 而是依据数据本身来保证数据的正确性, 乐观锁的实现主要依靠CAS机制和版本号进行控制.

实现乐观锁的SQL语句范例

//修改商品库存
update item set quantity=quantity-1 where id=1 and quantity - 1 >0

update item set stock=stock-count where id=1 and stock-count>0;

in会走索引吗，exist和in的区别

基础知识：
ALL 全表扫描，对整个表进行扫描，效率最差；
Index 索引扫描，是对整个索引的扫描，如果查询的选择结果中没有包含在索引中时，那跟全表扫描的效果时一样的；
Range 有范围的索引扫描；
Ref 查询条件的列中使用了索引，但是索引不是唯一的，所以需要继续在该范围内查询，但是因为索引时有序的，所以只是在小范围内的查询；
Const 常量查询，直接通过主键值等值查询；

这几个判断究竟走不走索引呢？
目前大部分说的是in 在某种情况下会出现索引失效；not in <>都不会走索引；其实也对也不对；
在mysql中如果脱离的搜索引擎的版本去看问题就不好判断了。

In 在5.7以前，如果是小范围的查询，还是走索引的，type属于range，在随着数据量的增大时会自动进行全表的扫描（并且与要查询的结果是否包含在索引树中决定走index还是all）；not in则不走索引；
目前在8.0以后验证，发现无论是in not 或者<>,都会走索引；
-------------------------------------------
来源: https://www.cnblogs.com/ykwang/p/14781104.html

4、SQL优化

（SQL优化的流程-怎么考虑SQL优化）？

1. 首先，使用慢查询日志等功能进行低效率SQL的检索和过滤，筛选出需要优化的SQL；
2. 其次，考虑SQL语句本身的层级结构，避免堆叠复杂的语句结构，避免使用函数和复杂运算，查询条件不要使用复杂的条件运算等，如果可以放在Java程序中进行运算的逻辑，尽量放在程序中，而不是放在SQl语句里去进行拼接；
3. 然后是避免索引失效以及建立高效索引；

5、having和where的区别

where是一个约束声明，使用where来约束来自数据库的数据，where是在结果返回之前起作用的，且where中不能使用聚合函数；

having是一个过滤生命，是在查询返回结果集以后对查询结果进行的过滤操作，在having中可以使用聚合函数，在说区别之前，得先介绍group by这个子句，而在说group by之前，又得说聚合函数……

SQL语句中的函数，如SUM，COUNT，MAX等，这些函数和其它函数的根本区别就是他们一般作用在多条记录上，结果值返回一个聚合结果；

而通过使用group by子句，可以让SUM和COUNT这些函数对属于一组的数据起作用，当你指定group by region时，只有属于同一个region的一组数据才将返回一行值，也就是说，表中所有除region外的字段，只能通过SUM，COUNT等聚合函数运算后返回一个值；

再说说having和where：

having可以让筛选成组后的各组数据，where子句在聚合前先筛选记录，作用在group by 子句和having前；而having在聚合后进行筛选。

标签：入门,索引,where,MySQL,查询,面试,SQL,数据
From： https://www.cnblogs.com/wang-jifeng/p/16774314.html