数据库三范式 数据库事务的特性 事务的隔离级别 索引的概念以及优缺点 索引的分类 索引的底层实现原理 B树和B+树的区别 如何避免索引失效 数据库锁有哪些 连接查询聚合函数sql关键字 MyISAM存

数据库三范式

1范式、原子性,字段不可再分

2范式、非主键必须完全依赖于主键,减少行内数据冗余

3范式、非主键不能依赖于其它非主键,主要解决列的冗余

数据库事务的特性

原子性:不可分割,事务要么全部执行,要么不执行

一致性:事务执行前后,数据库的状态保持一致

隔离性:一个事务不受另一个事务的影响,互不干扰

持久性:事务一旦结束,数据就会持久到数据库。(redo log)

事务的隔离级别

1、读未提交 :读到其它事务还没提交的数据,会出现脏读的情况

2、读已提交( update 或 delete):读到其它事务已经提交的数据,获得的结果不一致,出现不可重复读

3、可重复读(insert):数据库默认的隔离级别,再次读取的时候,可能会出现幻读现象。InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决了该问题。mysql会在内部自动为我们的数据字段添加一个版本控制(解决不可重复读问题)

4、串行化：事务的最高级别,强制事务进行排序,可以有效解决问题。它是在每个读的数据行上加上共享锁。在这个级别，可能导致大量的超时现象和锁竞争，一般为了提升程序的吞吐量不会采用这个.

索引的概念以及优缺点

概念:大部分常用的索引是存储在内存中的,索引主要是为了能够快速的找到想要查询的数据,提高数据库的性能，减少数据库的回表查询。(用空间换时间)

索引的缺点:占用内存空间,会影响对数据增删改操作的效率。索引的维护成本也较高。

索引的优点:加快对数据的查询效率;创建唯一索引,保证数据的完整性与唯一性;

索引的分类

普通索引:基本的索引,没有限制,(允许索引列的值可重复)

唯一索引:保证索引列的值是唯一的,可以加快对表的查找和排序效率

主键索引:是一种特殊的唯一索引，用于唯一标识数据表中的某一条记录，不允许有空值，一般用 primary key 来约束。

联合索引:多个字段创建的索引,可以加速符合查询的条件(手机号和密码)(订单头中的订单状态和订单id )

全文索引:新版本 MySQL 5.6 的 InnoDB 1支持全文索引,但是并没有es完善。所以在项目中我们采用 ElasticSearch做搜索引擎库

索引的底层实现原理

1. B树和B+树索引
2. 哈希索引
3. 全文索引
4. 空间索引

索引是在 Mysql 的存储引擎(InnoDB,MyISAM)层中实现的, 主要通过b树索引和b+树索引来实现的

B+Tree 的叶子节点保存所有的 key 信息, 依 key 大小顺序排列

B+Tree 叶子节点元素维护了一个单项链表

B树和B+树的区别

1. 数据存储方式：B树中每个节点既存储数据又存储索引，而B+树只存储索引，数据只存在叶子节点中。

2. 叶子节点的指针：B树中，每个节点的指针指向下一层节点，而B+树中，每个叶子节点的指针指向下一个叶子节点，形成一个链表。

3. 节点的大小：B树中，每个节点的大小与磁盘块的大小相同，而B+树中，每个节点的大小通常比磁盘块小，可以存储更多的节点。

4. 查询效率：B+树的查询效率比B树更高，因为B+树只需要遍历叶子节点即可找到数据，而B树可能需要遍历多个节点才能找到数据。

5. 范围查询：B+树比B树更适合范围查询，因为B+树的叶子节点形成了一个链表，可以很方便地遍历整个范围内的数据。而B树需要遍历多个节点才能找到全部数据。

如何避免索引失效

1、避免在索引列上进行函数操作

2、避免使用or操作符

3、避免对索隐列进行范围查询

4、字符串不加引号,也会造成索引失效

5、避免使用select * 尽量使用索引覆盖,提高查询效率

6、使用对于索引列避免!=

7、避免使用 like '%X'

数据库锁有哪些

1、行级锁:开销大，加锁慢，会出现死锁；锁定粒度小，发生锁冲突的概率低，并发度高

2、页级锁:锁定数据库表的一页数据，只有持有锁的事务可以对该页数据进行读取或修改。页级锁可以是共享锁或独占锁。

3、表级锁:开销小，加锁快，不会出现死锁；锁定力度大，发生锁冲突概率高，并发度最低

4、库级锁:锁定整个数据库，只有持有锁的事务可以对该数据库进行读取或修改。数据库级锁可以是共享锁或独占锁。

悲观锁

每次拿数据的时候,认为别人会对改数据进行修改,所以每次拿数据的时候就会对数据进行加锁,这样别人就不可以访问了,当自己操作完数据的时候,释放锁,别人才能拿到

乐观锁

认为别人不会修改数据,所以当获取数据的时候不会对数据进行加锁,只有当要提交数据的时候,可以使用版本号机制,判断一下当前数据的版本与获取数据前所拿到的版本是否一致,如果一致就可以提交,不一致就再次尝试。这样可以提高吞量

mysql基本必会点

1、连接查询

内连接:只显示左右边数据字段一致的数据

左连接:以左边表为基础进行查询,左边的字段与右边字段不一致时,右边会显示为null

右连接以右边表为基础进行查询,右边的字段与左边字段不一致时,右边会显示为null

2、聚合函数

count:统计行数

avg:获取某个列的平均数

sum:获取某个列的和

max:获取某个列的最大值

min:获取某个列的最小值

3、sql关键字

1、分页

mysql关键字 limit 限制

排序

倒序 desc by 列名 desc 升序 asc by 列名 asc

2、分组

mysql关键字分组 group by 如果还有条件 后面加 having

3、去重

去重关键字 distinct

4、sql select 语句完整执行顺序

select...from... left join... on... where ... group by ... having...order by...asc/desc ..limit...

MyISAM存储引擎与InnoDB引擎的区别

1、事务支持:InnoDB支持事务,MyISAM不支持事务

2、锁定机制:InnoDB支持行级锁,锁的力度比MyISAM粒度小

3、外键支持:InnoDB支持外键约束,MyISAM不支持外键约束

4、并发性能:InnoDB 要优于 MyISAM。由于 InnoDB 支持行级锁定和事务处理，因此在高并发情况下，InnoDB 的并发性能更高。

注意:

如果只是进行简单的读写操作，并且需要更快的查询速度，则可以选择 MyISAM。

Mysql如何进行优化

1、查看数据库是以查询为主还是更新为主,如果是查询,重点优化查询,如果是增删改较多.重点优化写入操作。

show status like 'Com_select' 查看查询次数

show status like 'Com_update' 查看更改次数

2、通过explain +sql语句查询语句获取sql的执行计划

主要参数:

1.id ：每个查询步骤的唯一标识符。

2. select_type ：查询的类型，如 SIMPLE 、 PRIMARY 、 SUBQUERY 等。

SIMPLE: 简单查询

PRIMARY:表示复杂查询中的最外层的select查询语句。

SUBQUERY:表是子查询语句

derived: 派生查询，跟在一个select查询语句的from关键字后面的select查询语句

3. table ：查询涉及的表。

4. partitions ：查询涉及的分区。

5. type ：访问表的方式，如 ALL 、 index 、 range 等。

6. possible_keys ：可能用到的索引。

7. key ：实际使用的索引。

8. key_len ：使用的索引长度。

9. ref ：列与索引的比较。

10. rows ：扫描的行数。

11. filtered ：查询结果的过滤比例。

12. Extra ：额外的信息，如 Using where 、 Using index 等。

type:这个字段决定mysql如何进行查找表中的数据

这个字段的所有值表示的从最优到最差依次为：

system > const > eq_ref > ref > range > index > all;

system/const:用户主键索引或者唯一索引查询时，只能匹配1条数据 一般可以对sql查询语句优化成一个常量，那么type一般就是system或者const，system是const的一个特例。

eq_ref:在进行连接查询时，例如left join 时，如果是使用主键索引或者唯一索引连接查询 ，结果返回一条数据，则type的值为一般为eq_ref。

ref:使用的是普通索,索引与一个值进行比较后可能获取到多个符合条件的行，不在是唯一的行了。

range:通常使用范围查找，例如between，in，<,>,>=等使用索引进行范围检索。

index:一般直接扫描二级索引的叶子节点，速度比较慢

ALL:这是一种效率最低的type，需要扫描主键索引树的叶子节点，获取数据是表中其他列的数据，即全表扫描。

什么是垂直拆分,什么是水平拆分

1、垂直拆分:当对于表中经常使用到的字段,我们可以把它拆到另外一个表中

2、水平拆分:一个表中的数据存储过多时,我们可以将表按照时间顺序进行拆分,将时间久的拆分出来

什么是索引覆盖什么是回表 什么是索引下推 什么是最左匹配原则

1、索引覆盖: 当我们查询的字段在索引中能找到时,我们就可以直接将数据返回

2、索引回表: 当我们查询的字段并没有在索引中或不全在索引中时,我们需要进行对表查询

3、索引下推:对于一些简单的计算,索引可以帮助我们进行计算 无需进行回表,减少回表的操作

4、最左匹配原则:当我们查询联合索引中的某个字段时,如果条件是联合索引的最左边那个,则符合最左匹配原则,将获得的结果直接返回,如果条件是联合索引右边那个,则不满足最左匹配原则,我们需要进行回表查询

索引的设计原则

1、对于查询频次较高的,数据量较大的 建立索引

2、使用唯一索引, 区分度越高, 使用索引的效率越高，能建唯一索引就建唯一索引，或者普通索引

3、索引并非越多越好, 如果该表赠,删,改操作较多, 慎重选择建立索引, 过多索引会降低表维护效率. 不是越多越好(避免过多的索引)

4、如果 where 后有多个条件经常被用到, 建议建立复合索引, 复合索引需要遵循最左前缀法则,

sql语句调优

1、使用合适的索引,避免使用全表扫描

2、避免使用子查询,尽量使用连接查询；

3、避免使用select *,只查询需要的字段；

4、避免使用OR，使用IN代替；

5、避免使用函数，尽量使用简单的运算符

mysql慢日志

查看慢日志是否开启 show variables like '%query%' slow_query_log为off则为关闭

开启慢日志 set global slow_query_log='ON;

慢日志有两种输出形式:table和file

慢日志以表格的形式存储在MySQL数据库中的`slow_log`表中。

慢日志以文本文件的形式存储在磁盘上。每条慢日志记录都以一行文本的形式存储，记录中包含了查询的详细信息，如执行时间、查询语句等

设置慢日志输出形式:set global log_output='FILE,TABLE';

慢日志主要用于:性能调优 查询调优 监控分析等问题