7 MySQL

mysql

关系型数据库和非关系型数据库（NoSql(not only sql)）

关系型数据库：创建在关系模型基础上的数据库；关系模型：有明确的行和列的二维表格模型

非关系型数据库：常规情况不可以使用SQL操作，因为不是关系模型，

MySQL是什么

MySQL是一个关系型数据库管理系统（DataBase Manage System）

MySQL存储引擎

mysql常用引擎包括：innodb、myisam、memory、merge等

MYISAM：全表锁，拥有较高的执行速度，不支持事务，不支持外键，并发性能差，占用空间相对较小，对事务完整性没有要求，以select、insert为主的应用基本上可以使用这引擎，访问速度快，
Innodb:行级锁，提供了具有提交、回滚和崩溃回复能力的事务安全，支持自动增长列，支持外键约束，并发能力强，占用空间是MYISAM的2.5倍，处理效率相对会差一些
Memory:全表锁，存储在内容中，速度快，但会占用和数据量成正比的内存空间且数据在mysql重启时会丢失，默认使用HASH索引，检索效率非常高，但不适用于精确查找，主要用于那些内容变化不频繁的代码表，
MERGE：是一组MYISAM表的组合

InnoDB与MyISAM的区别

表锁是锁定整个表，行锁是锁定整个行

InnoDB和MyISAM适用场景

MyISAM适合：(1)做很多count 的计算；(2)插入不频繁，查询非常频繁；(3)没有事务。

InnoDB适合：(1)可靠性要求比较高，或者要求事务；(2)表更新和查询都相当的频繁，并且行锁定的机会比较大的情况。

SQL语句中----删除表数据drop、truncate和delete的用法]

一、SQL中的语法

1、drop table 表名称 eg: drop table dbo.Sys_Test
2、truncate table 表名称 eg: truncate table dbo.Sys_Test
3、delete from 表名称 where 列名称 = 值 eg: delete from dbo.Sys_Test where test=‘test’

一：用法和区别

drop：drop table 表名

删除内容和定义，并释放空间。执行drop语句，将使此表的结构一起删除。

truncate (清空表中的数据)：truncate table 表名

删除内容、释放空间但不删除定义(也就是保留表的数据结构)。与drop不同的是,只是清空表数据而已。

truncate不能删除行数据，虽然只删除数据，但是比delete彻底，它只删除表数据。

delete：delete from 表名 （where 列名 = 值）

与truncate类似，delete也只删除内容、释放空间但不删除定义；但是delete即可以对行数据进行删除，也可以对整表数据进行删除。

二：注意

1.delete语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存，以便进行进行回滚操作。

2.执行速度一般来说：drop>truncate>delete

3.delete语句是数据库操作语言(dml)，这个操作会放到 rollback segement 中，事务提交之后才生效；如果有相应的 trigger，执行的时候将被触发。

4.truncate、drop 是数据库定义语言(ddl)，操作立即生效，原数据不放到 rollback segment 中，不能回滚，操作不触发trigger。

5.truncate语句执行以后，id标识列还是按顺序排列，保持连续；而delete语句执行后，ID标识列不连续

SQL中’'和NULL的区别

使用NULL可以区分“没有输入数据”和“输入空数据”， 差异在于：

NULL的长度就是NULL，空字符串的长度为0
一串NULL数据比空字符串优先排序
COUNT(message)会将空字符串计数进去，但是不会将NULL数据们计入
可以使用绑定变量搜索某个空字符串，但是不可以这样搜索NULL，例如：

DML与DDL

一、DML与DDL的含义：

1、DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge

2、DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：

create、alter、drop、truncate、comment、grant、revoke

二、DML与DDL的区别：`

1.DML操作是可以手动控制事务的开启、提交和回滚的。

2.DDL操作是隐性提交的，不能rollback！

事务相关

事务：是 一组SQL语句要么执行都成功，要么执行都失败。

事务的特性

• 原子性(Atomicity)：一个事务不可分割。要么都执行，要么完全不起作用。 通过undo log实现
• 一致性(最重要, consistency)：一个事务提交前和提交后的数据必须保持一致。
• 隔离性(isolation)：多个事务之间是相互隔离的，相互独立的，事务A不能干扰事务B。MVCC和加锁
• 持久性(durability)：事务提交后，数据会持久化存储在数据库中。redo log

事务并发带来的问题

在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。

• 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
• 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
• 不可重复读（Unrepeatable read）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
• 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。

不可重复读和幻读区别：

不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。

数据库三个日志 binlog, redo log, undo log

binlog：

用于记录数据库执行的写入性操作(不包括查询)信息，以二进制的形式保存在磁盘中。是mysql的逻辑日志，在server层。通过追加的方式进行写入的。 主要是用作主从复制和数据恢复

redolog：为了实现持久性，只要事务提交成功，那么对数据库做的修改就会被永久保存下来。redo log存在的意义主要就是降低对数据页刷盘的要求

每次事务提交的时候，将该事务涉及修改的数据页全部刷新到磁盘中。但是这么做会有严重的性能问题，主要体现在两个方面：1、因为Innodb是以页为单位进行磁盘交互的，而一个事务很可能只修改一个数据页里面的几个字节，这个时候将完整的数据页刷到磁盘的话，太浪费资源了！
2、 一个事务可能涉及修改多个数据页，并且这些数据页在物理上并不连续，使用随机IO写入性能太差！

此mysql设计了redo log，具体来说就是只记录事务对数据页做了哪些修改

redo log包括两部分：一个是内存中的日志缓冲(redo log buffer)，另一个是磁盘上的日志文件(redo log file)。mysql每执行一条DML语句，先将记录写入redo log buffer，后续某个时间点再一次性将多个操作记录写到redo log file。这种先写日志，再写磁盘的技术就是MySQL里经常说到的WAL(Write-Ahead Logging) 技术。

mysql支持三种将redo log buffer写入redo log file的时机

undo log 为了实现原子性

undo log主要记录了数据的逻辑变化，比如一条INSERT语句，对应一条DELETE的undo log，对于每个UPDATE语句，对应一条相反的UPDATE的undo log，这样在发生错误时，就能回滚到事务之前的数据状态。

数据库的隔离级别

未提交读（Read Uncommitted）：允许读取尚未提交的数据，可能会导致脏读、不可重复读、幻读。

已提交读（Read Commmitted）：只能读取到已提交的数据。可能会导致不可重复读和幻读。Oracle数据库默认隔离级别。

可重复读（Repeated Read）：对同一字段的多次读取结果都是一致的，除非数据是被本身事务所修改。该隔离级别还存在幻读。InnoDB默认级别。

串行化（Serializable）：每次读都需要获得表级共享锁，读写相互都会阻塞，事务不能进行并行操作。

从上到下，隔离级别越来越高，越来越安全，效率越来越低。

如下表所示：

都不会导致丢失修改：

隔离级别/问题	脏读	不可重复读	幻读
read uncommitted	√	√	√
read committed	×	√	√
repeatable read	×	×	√
serializable	×	×	×

MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。

mysql锁

共享锁（S，Shared）：

一个事务对数据加共享锁，也可以允许其他事务对此交集数据加此锁。但阻止其他事务对此交集数据加排他锁。

加共享锁语句：

SELECT * FROM table_name WHERE ...
LOCK IN SHARE MODE

互斥锁（X, Exclusive）：

一个事务对数据加排他锁，会阻止其他事务对此交集数据加任何锁。

表级锁：

每个事务操作会锁住整张表，粒子度最大，简单粗暴。优点是加锁和释放锁次数会大大减少。缺点是锁冲突的概率会大大增加，高并发情况下不可取。

资源开销介于行级锁和表级锁，会出现死锁。

行级锁：

每个事务仅会锁住被影响的行，也就是说，涉及到哪些行记录，哪些行才会被锁住，会出现死锁。优点是锁冲突概率小，并发度高。缺点是由于锁离子度小，加锁和释放锁的次数会大大增加，资源开销大。

切记： MySQL的行级锁通过索引上的索引项来实现的，InnoDB这种行锁实现特点意味者：只有通过索引条件检索数据，InnoDB才会使用行级锁，否则，InnoDB将使用表锁。

InnoDB避免死锁：

• 为了在单个InnoDB表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个记录（行）使用SELECT … FOR UPDATE语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。
• 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁
• 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。 在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会
• 通过SELECT … LOCK IN SHARE MODE获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。
• 改变事务隔离级别

mysql实现乐观锁

1.使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。
一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。
当提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，就进行更新操作，否则认为是过期数据。

2.第二种实现方式和第一种差不多，同样是在需要乐观锁控制的table中增加一个字段，名称无所谓，字段类型使用时间戳（timestamp）, 和上面的version类似，也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行对比，如果一致就更新，否则就是版本冲突。

public class Goods implements Serializable {  
    private static final long serialVersionUID = 5503791908148880587L;  
    private int id;
    private int status;
    private String name;  
    private int version;   // version: 商品的版本号

}

读写锁：

读锁可以在没有写锁的时候被多个线程同时持有，写锁是独占的。
读写锁比互斥锁允许对于共享数据更大程度的并发。每次只能有一个写线程，但是同时可以有多个线程并发地读数据。

多版本并发控制（Multi-Version Concurrency Control, MVCC）主要是解决读已提交和可重复读

MVCC比单纯的加锁效率更高效。

可以认为MVCC是行级锁的一个变种, 但是它在很多情况下避免了加锁操作, 因此开销更低。虽然实现机制有所不同, 但大都实现了非阻塞的读操作，写操作也只锁定必要的行。

是 MySQL 的 InnoDB 存储引擎实现隔离级别的一种具体方式，用于实现提交读和可重复读这两种隔离级别。而未提交读隔离级别总是读取最新的数据行，要求很低，无需使用 MVCC。可串行化隔离级别需要对所有读取的行都加锁，单纯使用 MVCC 无法实现。

MVCC把SQL分为两类：一种是快照读，就是普通的select操作，读的就是历史版本的数据。另一种是当前读，比如select … for update，insert，update，delete 读的都是最新的数据，不可重复读就是利用快照保存数据，然后就解决啦！

快照读：使用 MVCC 读取的是快照中的数据，这样可以减少加锁所带来的开销：

当前读：当前读读取的是最新的数据，需要加锁。

锁的兼容性

InnoDB的锁 （行锁的三种算法）

记录锁 Record Lock: 单个行记录上的锁
间隙锁 Gap Locks ：锁定一个范围，但不包含记录本身
Next-key Locks = Record Lock + Gap Lock 锁定一个范围，并且锁定记录本身。 ：主要是为了解决幻读问题。

Record Lock 锁定行

Record Lock是对索引记录的锁定。记录锁有两种模式，S模式和X模式。

例如

SELECT id FROM test WHERE id = 10 FOR UPDATE;

表示防止任何其他事务插入、更新或者删除id =10的行。

记录锁始终只锁定索引。即使表没有建立索引，InnoDB也会创建一个隐藏的聚簇索引(隐藏的递增主键索引)，并使用此索引进行记录锁定。

间隙锁 Gap Locks

间隙锁作用在索引记录之间的间隔，又或者作用在第一个索引之前，最后一个索引之后的间隙。不包括索引本身。
例如，

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

锁定范围：(10, 20)
这条语句阻止其他事务插入10和20之间的数字，无论这个数字是否存在。
间隙可以跨越0个，单个或多个索引值。

唯一索引可以锁定一行，所以不需要间隙锁锁定。
如果列没有索引或者具有非唯一索引，该语句会锁定当前索引前的间隙。

InnoDB中的间隙锁的唯一目的是防止其他事务插入间隙。

间隙锁是可以共存的，一个事务占用的间隙锁不会阻止另一个事务获取同一个间隙上的间隙锁。

如果事务隔离级别改为RC，则间隙锁会被禁用。

间隙锁有一个致命的弱点，就是当锁定一个范围键值以后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。某些场景下可能会造成很大的危害。

Next-key Locks

Next-key锁
Next-key锁实际上是Record锁和gap锁的组合。Next-key锁是在下一个索引记录本身和索引之前的gap加上S锁或是X锁(如果是读就加上S锁，如果是写就加X锁)。
默认情况下，InnoDB的事务隔离级别为RR，系统参数innodb_locks_unsafe_for_binlog的值为false。InnoDB使用next-key锁对索引进行扫描和搜索，这样就读取不到幻象行，避免了幻读的发生。

幻读是指在同一事务下，连续执行两次同样的SQL语句，第二次的SQL语句可能会返回之前不存在的行。

当查询的索引是唯一索引时，Next-key lock会进行优化，降级为Record Lock，此时Next-key lock仅仅作用在索引本身，而不会作用于gap和下一个索引上。

作用范围：

由于id是主键索引，会做优化，所以用code举例说明

select * from test where code=5 for update;

此时锁定范围是 （1，5） + 5 + （5， 10] = （1, 10 ]

where和having的区别

having与where的区别:
having是在分组后对数据进行过滤
where是在分组前对数据进行过滤
having后面可以使用聚合函数
where后面不可以使用聚合

在查询过程中执行顺序：from>where>group（含聚合）> having > order > select。

聚合函数：

例如SUM, COUNT, MAX, AVG等。这些函数和其它函数的根本区别就是它们一般作用在多条记录上。

HAVING子句可以让我们直接筛选成组后的各组数据，也可以在聚合后对组记录进行筛选，而WHERE子句在聚合前先筛选记录，也就是说作用在GROUP BY 子句和HAVING子句前。

索引是什么：

索引是一种帮助mysql提高查询效率的数据结构。

优点：

• 可以大大加快数据的检索速度；
• 可以在查询过程中使用优化隐藏器，提高系统的性能；

缺点：

• 时间方面：创建索引和维护索引要耗费时间，具体的，当对表中的数据进行增、删、改操作时，索引也要动态的维护，会降低增、删、改的执行效率；
• 空间方面：索引需要占用物理空间；

索引的使用场景

1、表的主键、外键必须有索引；
2、数据量超过300的表应该有索引；
3、经常与其他表进行连接的表，在连接字段上应该建立索引；
4、经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5、索引应该建在选择性高的字段上；
6、索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7、复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8、频繁进行数据操作的表，不要建立太多的索引；
9、删除无用的索引，避免对执行计划造成负面影响；

MySql在建立索引优化时需要注意的问题

1，创建索引
但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引
比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;

如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算
select * from users where YEAR(add date)

8，不使用NOT IN和操作
NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

https://blog.csdn.net/qq_33934427/article/details/105073406

索引覆盖

1. 联合索引是由非聚簇索引（辅助索引、二级索引）组成的，非聚簇索引包含索引列+主键，主键用于回表查询。
2. 覆盖索引：如果一个非聚簇索引覆盖所有需要查询的字段的值，并不需要根据主键回查聚簇索引表，就称为覆盖索引。（此时联合索引的最左前缀匹配原则不成立）

最左前缀原则

在创建复合索引时，要根据业务需求，where子句中使用最频繁的列放在最左边。

最左前缀匹配原则（创建在前面的才可以 ）： 在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

最左前缀原则：MySQL会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。比如a=1 and b=2 and c>3 and d=4，若建立(a,b,c,d)顺序的索引，d是用不到索引的。若建立(a,b,d,c)顺序的索引，则可以使用到，a,b,d的顺序根据业务需要可以任意调整。

=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

经典题目：name（在前）, age, bir复合索引，查询字段 name, age, bir，下面哪些可以利用索引：

name bir age 会调整成 name, age,bir	可以
name age bir	可以
age bir	不可以
bir age name 会调整成 name, age,bir	可以
age bir	不可以

索引的类型

INNODB

主键索引：一个表只能有一个主键。列不允许重复，不允许为NULL；设定为主键以后，数据库会自动创建索引，而且innodb中创建的为聚簇索引

单列索引（单值索引， 普通索引）：即一个索引只包含单个列，一个表可以有多个单列索引；允许为空，允许多个null值；（比如，name, age有索引，where name 或 where age 都可以用上索引，但是 where name and age这就无法使用索引，所以复合索引才有存在的意义）。

普通索引也是非聚簇索引。

唯一索引： 和主键索引的区别在于，索引列的值必须唯一，但允许有空值。 唯一索引的列值可以存在null，但是只能存在一个null。

复合索引：一个索引包括多个列。在查询的时候。

题目： 见上面

1.最左前缀原则2.mysql引擎在查询时为了更好的利用索引，在查询过程中会动态调整字段顺序以便利用索引 专用于组合搜索，

MYISAM
全文索引：类型为FULLTEXT， 在定义索引列上支持值的全文查找，允许在这些索引列中插入重复值和空值，可以在CHAR, VRACHAR, TEXT类型上创建。（InnoDB中不支持使用全文索引，mysql5.7以前，只能用于MYISAM引擎）

索引优化 怎么避免回表查询，建立联合索引

https://blog.csdn.net/shenjian58/article/details/97456354

索引数据结构

索引的数据结构和具体存储引擎的实现有关，在MySQL中使用较多的索引有Hash索引，B+树索引等，而我们经常使用的InnoDB存储引擎的默认索引实现为：B+树索引。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；
InnoDB和MyISAM这两种存储引擎的索引用的数据结构都是B+树。
MEMORY存储引擎用的是hash索引。

B树和B+树的区别⭐

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。

从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页，不是磁盘存储结构那个页） 能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。

B+Tree相对于B-Tree有几点不同：

1. 非叶子节点只存储键值信息。
2. 所有叶子节点之间都有一个链指针。
3. 数据记录都存放在叶子节点中。

• InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为1000。也就是说一个深度为3的B+Tree索引可以维护10^3 10^3 10^3 = 10亿 条记录。

• 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1~3次磁盘I/O操作。

顶层页常驻内存，所以少一次I/O操作。

• 在B树中，可以将键和值存放在内部节点和叶子节点；在B+树中，内部节点都是键，没有值，叶子节点同时存放了键和值；
• B树的叶子节点相互独立；B+树的叶子节点是相互连接的，形似一个链表；

为什么B+树更适合应用于数据库索引⭐

• B+树更适应磁盘特性，相比B树减少了I/O读写的次数。B+树的非叶子结点只存key不存数据，因此单个页可以存储更多key，一次性读入内存需要查找的key也就更多，磁盘的I/O读取次数就相对较少。

• B+树的查询效率比B树更稳定，因为数据只存在叶子结点上，所以查找效率为O(logN)；

• B树非叶子结点存了数据，所有只能通过中序遍历按序遍历。B+树叶子结点间用链表链表，所以遍历所有数据只需遍历一遍叶子结点，相对于B树效率更高；

如果用的B树，可能需要五层，但是B+只需要四层目录，树的四层遍历跟五层遍历又不一样。B+可以存储更多的数据，

hash索引

哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希索引将哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针。索引自身只需存储对应的哈希值，所以索引结构十分紧凑，这让哈希索引的速度非常快。限制：

• 数据不是按照索引值顺序存储的，无法排序；
• 不支持部分索引匹配查找，因为哈希索引是使用索引列的全部内容来计算哈希值的；
• 只支持等值比较查询，不支持范围查询；

聚簇索引、非聚簇索引区别简洁版

MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。

InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。

聚簇索引和非聚簇索引

https://www.cnblogs.com/GarrettWale/p/14465970.html
聚簇索引在表中指的就是主键索引，

聚簇索引：将数据存储和索引放到一块，索引结构的叶子节点保存了整行数据（所以聚簇索引不一定是主键索引，但是主键索引一定是聚簇索引），下面的图中叶子节点 数据存储和索引在一起，就是聚簇索引。

非聚簇索引： 数据与索引分开存储，索引结构的叶子节点指向了数据对应位置 （一个表里面只有一个聚簇索引，剩下创建的什么name, age什么复合都是非聚簇索引）

这种就是聚簇索引：

下面是非聚簇索引

注意：为什么记录的不是数据地址，地址可以直接返回？

因为在增删改查的时候会导致树上地址的变化

注意:在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引也被称为辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

1. InnoDB中

• InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。

• 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

• 聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会 隐式定义一个主键（类似oracle中的RowId） 来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

2. MYISAM

• MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
• 这种引擎下就不在需要二次查找，这种一定快。偏向于查询
  

使用聚簇索引的优势 每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

• 1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

• 2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。（因为存地址和存主键他们所占用的空间大小不同）

聚簇索引需要注意什么?

• 当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位置，导致索引树调整复杂度变大，消耗更多的时间和资源。
• 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id

• 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

什么情况下无法利用索引呢?

• 查询语句中使用LIKE关键字
  在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。

• 查询语句中使用多列索引
  多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。 也就是最左原则

• 查询语句中使用OR关键字
  查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

补充 MySQL索引失效的底层原理 （对上面的一个补充）

之前说过比如name, age, bir复合索引，name是第一个建立的，左前原则的前指的就是name，就是因为建立索引的时候，他是按照name的顺序排列，age，bir是无序的。

这个SQL语句会失效，原因是：a = 1时，值时有顺序，但是a > 1时就无法保证b有序。

1%（这个是前缀）是1开头的数据，此时会用到索引，%1或者%1%用不到索引。

走不走索引-索引失效情况

1、使用!= 或者 <> 导致索引失效（诸葛老师的课程里面，>也有可能会使用索引，简单来说就是如果全盘扫描效率更搞，就不会再走索引）

2、类型不一致导致的索引失效

字段varchar，条件为int，会导致失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_phone = 13756494953

字段int，条件字符型，不会导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = '101'

3、函数导致的索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE DATE(receive_date) = '2020-02-17 14:08:31'

4、运算符导致的索引失效

对列进行了（+，-，*，/，!）, 那么都将不会走索引

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id -1 = 100

5、OR引起的索引失效

or连接的字段，都建立了索引，不会失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = 100 or customer_name = '李四'

or连接的字段，如果其中一个没建立索引，则会是整个索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = 101 or coupon_id = 2

6、模糊搜索导致索引失效(并不是所有的模糊搜索都会导致索引失效)

前缀不会失效，如果%放在前缀，可能失效，如果是后缀，不会失效。

7、NOT IN、NOT EXISTS导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id NOT IN (100,101)

8、IS NOT NULL会导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name IS NOT NULL

百万级别或以上的数据如何删除

由于索引需要额外的维护成本，因为索引文件是单独存在的文件,所以当我们对数据的增加,修改,删除,都会产生额外的对索引文件的操作,这些操作需要消耗额外的IO,会降低增/改/删的执行效率。所以，在我们删除数据库百万级别数据的时候，查询MySQL官方手册得知删除数据的速度和创建的索引数量是成正比的。

先删除索引
再删除一些无用数据

DDL和DML

一、DML与DDL的含义：

1、DML（Data Manipulation Language）数据操作语言-数据库的基本操作，SQL中处理数据等操作统称为数据操纵语言,简而言之就是实现了基本的“增删改查”操作。包括的关键字有：select、update、delete、insert、merge

2、DDL（Data Definition Language）数据定义语言-用于定义和管理 SQL 数据库中的所有对象的语言，对数据库中的某些对象(例如，database,table)进行管理。包括的关键字有：

create、alter、drop、truncate、comment、grant、revoke

二、DML与DDL的区别：

1.DML操作是可以手动控制事务的开启、提交和回滚的。

2.DDL操作是隐性提交的，不能rollback！

数据库拆分

水平切分 Sharding

它是将同一个表中的记录拆分到多个结构相同的表中，可以起到缩表的作用。并且这些表分布在不同的数据库，保存在多个MYSQL节点上

水平切分的另一个缺点就是扩容比较麻烦，日积月累，分片迟早有不够用的时候。这时候不是首先选择增加新的集群分片。因为一个MySQL分片，需要4~8个MySQL节点（最小规模），增加一个分片的投入成本是很高的。

垂直切分

垂直切分是将一张表按列切分成多个表，将按数据库中表的密集程度部署到不同的库中。达到的结果是将原来一个数据库系统的压力按照业务均摊到各个拆分后的数据库中。垂直拆分也是比较推荐的一直拆分方式。

结合着微服务体系，一般会进行垂直拆分。当微服务中的数据库出现压力时，然后进行水平拆分。

主从复制和读写分离

主从复制（Replication）是指数据可以从一个MySQL数据库主服务器复制到一个或多个从服务器，从服务器可以复制主服务器中的所有数据库或者特定的数据库，或者特定的表。默认采用异步模式。

实现原理：

• 主服务器 binary log dump 线程：将主服务器中的数据更改（增删改）日志写入 Binary log 中；
• 从服务器 I/O 线程：负责从主服务器读取binary log，并写入本地的 Relay log；
• 从服务器 SQL 线程：负责读取 Relay log，解析出主服务器已经执行的数据更改，并在从服务器中重新执行（Replay），保证主从数据的一致性

原因：

主从复制：1、当主数据库出现问题时，可以当从数据库代替主数据库，可以避免数据的丢失。

2、可以进行读写分离。

读写分离；1、避免从数据库进行写操作而导致的主从数据库数据不一致的情况，因为当主从数据库数据不一致时，那么从数据库最主要的备份任务就没有意义了。

2、减轻主数据库的压力。因为进行写操作更耗时，所以如果不进行读写分离的话，写操作将会影响到读操作的效率

Mysql哪些字段适合建立索引

https://blog.csdn.net/qq_33934427/article/details/105073406

数据库优化

SQL语句优化

• 避免在where字句中使用!=、<、>操作符或对字段进行null值判断，否则将引擎放弃使用索引而进行全表扫描；

• 只返回必要的列：最好不要使用 SELECT * 语句；

• 只返回必要的行：使用 LIMIT 语句来限制返回的数据；

• 将一个大连接查询分解成对每一个表进行一次单表查询，然后在应用程序中进行关联，这样做的好处有：

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用；
  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询；
  • 减少锁竞争

索引优化

注意索引失效的情况，在合适的地方建立索引

数据库表结构优化

• 遵循三大范式
• 选择合适的数据类型，避免存储NULL的字段
• 水平切分，将数据分布到集群的不同节点上，从而环节单个数据库的压力
• 垂直切分，业务更加清晰，系统之间整合容易，数据维护简单

系统配置优化

配置文件优化：缓存池大小和个数设置

硬件优化