MySQL学习笔记-2

一张表最多能存多少数据？

MySQL本身并没有对单表最大记录数进行限制，这个数值取决于你的操作系统对单个文件的限制本身。阿里巴巴《Java开发手册》提出单表行数超过 500 万行或者单表容量超过 2GB，才推荐进行分库分表。

事实上，MySql数据库一张表中能存储的最大数据量和实际记录的条数无关，而与 MySQL 的配置以及机器的硬件有关。因为，MySQL 为了提高性能，会将表的索引装载到内存中。InnoDB buffer size 足够的情况下，其能完成全加载进内存，查询不会有问题。但是，当单表数据库到达某个量级的上限时，导致内存无法存储其索引，使得之后的 SQL 查询会产生磁盘 IO，从而导致性能下降。当然，这个还有具体的表结构的设计有关，最终导致的问题都是内存限制。

磁盘IO

关于IO的最小单位：

1、数据库IO的最小单位是16K(MySQL默认，oracle是8K)

为了文件读写的效率，数据库系统不直接使用512B的最小粒度，而是采用更大的页来作为文件IO的基本单位。

2、文件系统IO的最小单位是4K(也有1K的)

3、磁盘IO的最小单位是512字节（512b）

InnoDB 和 MyISAM 的区别？

• InnoDB支持事务，可以进行Commit和Rollback；

• MyISAM 只支持表级锁，而 InnoDB 还支持行级锁，提高了并发操作的性能；

• InnoDB 支持外键；

• MyISAM 崩溃后发生损坏的概率比 InnoDB 高很多，而且恢复的速度也更慢；

• MyISAM 支持压缩表和空间数据索引，InnoDB需要更多的内存和存储；

• InnoDB 支持在线热备份

• InnoDB不支持全文索引，MyISAM支持全文索引。

​

应用场景

​

• MyISAM 管理非事务表。它提供高速存储和检索（MyISAM强调的是性能，每次查询具有原子性，其执行速度比InnoDB更快），以及全文搜索能力。如果表比较小，或者是只读数据（有大量的SELECT），还是可以使用MyISAM；

• InnoDB 支持事务，并发情况下有很好的性能，基本可以替代MyISAM

事务的概念和特性？

概念：事务（Transaction）是一个操作序列，不可分割的工作单位，以BEGIN TRANSACTION开始，以ROLLBACK/COMMIT结束

特性（ACID）：

• 原子性（Atomicity）：逻辑上是不可分割的操作单元，事务的所有操作要么全部提交成功，要么全部失败回滚（用回滚日志undo log实现，反向执行日志中的操作）；

• 一致性（Consistency）：事务的执行必须使数据库保持一致性状态。在一致性状态下，所有事务对一个数据的读取结果都是相同的；数据都是正确的，不存在矛盾。

  （不满足一致性的例子：学生表中有重复的学号；转账成功了，但是付款的人余额没扣，或者收款的人余额没有增加。）

• 隔离性（Isolation）：一个事务所做的修改在最终提交以前，对其它事务是不可见的（并发执行的事务之间不能相互影响）；（通过加锁和MVCC实现）

• 持久性（Durability）：一旦事务提交成功，对数据的修改是永久性的（redo log实现）

会出现哪些并发一致性问题？

• 丢失修改：一个事务对数据进行了修改，在事务提交之前，另一个事务对同一个数据进行了修改，覆盖了之前的修改；

• 脏读（Dirty Read）：一个事务读取了被另一个事务修改、但未提交（进行了回滚）的数据，造成两个事务得到的数据不一致；

• 不可重复读（Nonrepeatable Read）：在同一个事务中，某查询操作在两个时间点读取到的同一行数据不一致。在这两个时间点之间数据已经发生了修改（针对update操作）；

• 幻读（Phantom Read）：当同一个查询操作多次执行时，由于其它事务在这个数据范围内执行了插入操作，会导致每次返回不同的结果集（返回的行数不一样，和不可重复读的区别：针对的是一个数据整体/范围；并且针对insert操作）

数据库的四种隔离级别？

• 读未提交（Read Uncommited）：在一个事务提交之前，它的执行结果对其它事务也是可见的。会导致脏读、不可重复读、幻读；

• 读已提交（Read Commited）：一个事务只能看见已经提交的事务所作的改变。可避免脏读问题；

• 可重复读（Repeatable Read）：可以确保同一个事务在多次读取同样的数据时得到相同的结果。（MySQL的默认隔离级别）。可避免不可重复读；

• 可串行化（Serializable）：强制事务串行执行，使之不可能相互冲突，从而解决幻读问题。可能导致大量的超时现象和锁竞争，实际很少使用。

读取未提交和可串行化的实现没什么好说的，一个是啥也不干，一个是直接无脑加锁避开并行化，让你啥也干不成。重头戏就是读取已提交和可重复读是如何实现的。这就是我们要说的 MVCC

为什么需要多个隔离级别？

有多个隔离级别，这样我们可以根据我们的需要选择合适的级别来完成需求，如果只有一个隔离级别例如只有串行化，它解决了所有可能会出现的问题，但是性能相比可重复读会有所降低，如果我们的业务并不需要那么高的隔离级别，那反而会得不偿失。

可重复读可以解决幻读吗？

可重复读的隔离级别没有办法彻底的解决幻读的问题，如果我们的项目中需要解决幻读的话也有两个办法：

• 使用串行化读的隔离级别

• MVCC+next-key locks：next-key locks由record locks(索引加锁) 和 gap locks(间隙锁，每次锁住的不光是需要使用的数据，还会锁住这些数据附近的数据)

实际上很多的项目中是不会使用到上面的两种方法的，串行化读的性能太差，而且其实幻读很多时候是我们完全可以接受的。

MySQL数据库中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？

1、RC 与 RR 在锁方面的区别

1)RR 支持 gap lock(next-key lock)，而RC则没有gap lock。

因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

2)RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会是否行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select … from s where 语句在s表上的锁也是不一样的

2、RC 与 RR 在复制方面的区别

RC 隔离级别不支持 statement 格式的bin log，因为该格式的复制，会导致主从数据的不一致；只能使用 mixed 或者 row 格式的bin log; 这也是为什么MySQL默认使用RR隔离级别的原因。复制时最好使用：binlog_format=row。

3、 RC 与 RR 在一致性读方面的区别

RC隔离级别时，事务中的每一条select语句会读取到他自己执行时已经提交了的记录，也就是每一条select都有自己的一致性读ReadView; 而RR隔离级别时，事务中的一致性读的ReadView是以第一条select语句的运行时，作为本事务的一致性读snapshot的建立时间点的。只能读取该时间点之前已经提交的数据。

4、RC 支持半一致性读，RR不支持

RC隔离级别下的update语句，使用的是半一致性读(semi consistent)；而RR隔离级别的update语句使用的是当前读；当前读会发生锁的阻塞。

事务日志

事务的隔离性由锁机制实现。而事务的原子性、一致性和持久性由事务的redo日志和undo日志来保证。

redo log称为重做日志，提供再写入操作，恢复提交事务修改的页操作，保证事务的持久性。把修改的操作记录下来。
    我们只是想让已经提交了的事务对数据库中数据所做的修改永久生效，即使后来系统崩溃，在重启后也能把这种修改恢复出来。所以没必要在每次提交时就把该事务在内存中修改过的全部页面刷新到磁盘，只需要把修改了哪些东西记录一下就好。
    redo日志降低了刷盘频率；redo日志占用的空间非常小
undo log称为回滚日志，回滚行记录到某个特定版本，用来保证事务的原子性、一致性。
    更新数据前先写入一个undo log,把这些如果回滚时需要用到的操作记录下来
    作用: 
        回滚数据，注意undo只是将数据库逻辑地恢复到原来的样子，所有修改被逻辑地取消，但是数据结构和页回滚后可能不同。
        MVCC，当用户读取一行记录时，若该记录已经被其他事务占用，当前事务可以通过undo读取之前地行版本信息，以此实现非锁定读取。
​

innodb是如何实现事务的

Innodb通过Buffer Pool缓冲池，LogBuffer日志缓冲区，Redo Log，Undo Log来实现事务，以一个update语句为例：

1.Innodb在收到一个update语句后，会先根据条件找到数据所在的页，并将该页缓存在Buffer Pool中

2.执行update语句，修改Buffer Pool中的数据，也就是内存中的数据

3.针对update语句生成一个Redo Log对象，存入LogBuffer中

4.针对update语句生成一个undo Log日志，用于事务回滚

5.如果事务提交，把Redo Log对象持久化，后续其他机制将Buffer Pool中所修改的数据页持久到磁盘中

6.如果事务回滚，利用undo Log日志进行回滚。

并发问题的解决方案

怎么解决脏读、不可重复读、幻读这些问题呢？其实有两种可选的解决方案： 方案一：读操作利用MVCC ，写操作进行加锁 。

普通的SELECT语句在读已提交和可重复读隔离级别下会使用到MVCC读取记录。

方案二：读、写操作都采用加锁的方式。

小结对比发现： 采用 MVCC 方式的话， 读-写操作彼此并不冲突，性能更高 。 采用加锁方式的话， 读-写操作彼此需要排队执行，影响性能。 一般情况下我们当然愿意采用 MVCC 来解决读-写操作并发执行的问题，但是业务在某些特殊情况下，要求必须采用加锁的方式执行。

什么是乐观锁和悲观锁？

• 悲观锁（数据库）：认为数据随时会被修改，因此每次读取数据之前都会上锁，防止其它事务读取或修改数据；应用于数据更新比较频繁的场景；

• 乐观锁：操作数据时不会上锁，但是更新时会判断在此期间有没有别的事务更新这个数据，若被更新过，则失败重试；适用于读多写少的场景。乐观锁的实现方式有：不采用数据库自身的锁机制，而是通过程序来实现。

  • 加一个版本号或者时间戳字段，每次数据更新时同时更新这个字段；

  • 先读取想要更新的字段或者所有字段，更新的时候比较一下，只有字段没有变化才进行更新

全局锁和死锁

全局锁就是对整个数据库实例加锁，让整个库处于只读状态。做全库逻辑备份

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

如何避免死锁？
1.不同业务并发访问多个表时，应约定以相同的顺序访问这些表；
合理设计索引，使业务SQL尽可能通过索引定位更少的行，减少锁竞争。
避免大事务，拆分成多个小事务，缩短锁定资源的时间。
并发比较高的系统中，不要显式加锁；
降低隔离级别；

mysql死锁怎么解决？

1、等待，直到超时（innodb_lock_wait_timeout=50s设置锁等待的时间，前提已经检测到锁的产生）。

当两个事务互相等待时，当一个等待时间超过设置的某一个阈值时，其中一个事务进行回滚，另一个等待的事务就能继续进行。 2、发起死锁检测，主动回滚一条事务，让其他事务继续执行，回滚代价最小的那一个事务。

死锁检测的原理是构建一个以事务为顶点、锁为边的有向图，判断有向图是否存在环，存在即有死锁。检测到死锁之后，选择插入、更新或者删除的行数最少的事务回滚，基于 INFORMATION_SCHEMA.INNODB_TRX 表中的 trx_weight 字段来判断。

常见的锁？

意向锁是 InnoDB 自动加的， 不需用户干预。 对于 UPDATE、 DELETE 和 INSERT 语句， InnoDB会自动给涉及数据集加排他锁（X)； 对于普通 SELECT 语句，InnoDB 不会加任何锁；

事务可以通过以下语句显式给记录集加共享锁或排他锁：
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。 其他 session 仍然可以查询记录，并也可以对该记录加 share mode 的共享锁。但是如果当前事务需要对该记录进行更新操作，则很有可能造成死锁。
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE。其他 session 但是不能对该记录加共享锁或排他锁，而是等待获得锁

• 排它锁（Exclusive Lock）/ X锁：事务对数据加上X锁时，只允许此事务读取和修改此数据，并且其它事务不能对该数据加任何锁；

• 共享锁（Shared Lock）/ S锁：加了S锁后，该事务只能对数据进行读取而不能修改，并且其它事务只能加S锁，不能加X锁

• 意向锁（Intention Locks）：允许行表锁共存，属于表锁

  • 一个事务在获得某个数据行对象的 S 锁之前，必须先获得整个表的 IS 锁或更强的锁；

  • 一个事务在获得某个数据行对象的 X 锁之前，必须先获得整个表的 IX 锁；

  • 意向锁IS/IX 锁之间都是兼容的；

  • 意向锁不会与行级的共享 / 排他锁互斥！正因为如此，意向锁并不会影响到多个事务对不同数据行加排他锁时的并发性。

  • 好处：如果一个事务想要对整个表加X锁，就需要先检测是否有其它事务对该表或者该表中的某一行加了锁，这种检测非常耗时。有了意向锁之后，只需要检测整个表是否存在IX/IS/X/S锁就行了

锁的作用：用于管理对共享资源的并发访问，保证数据库的完整性和一致性

封锁粒度的概念：

MySQL 中提供了两种封锁粒度：行级锁以及表级锁。

表锁：自增锁、元数据锁；

什么时候使用表锁

①事务需要更新大部分或者全部数据，使用行锁会让执行效率低下，并且冲突和等待增多。

②事务涉及多个表，容易引起死锁，造成事务回滚

行锁：

行锁都是加在索引上的，最终都会落在聚簇索引上。行锁实际加载索引上，所以如果一个update语句的where条件中，如果没有明确的索引时，会导致所有行均被加上行锁(所有间隙也会加上间隙锁)

创建行锁条件：

1、表中创建索引， select ... where 字段(必须是索引) 不然行锁就无效。

2、必须要有事务，这样才是行锁(排他锁)

3、在select 语句后面 加 上 FOR UPDATE；

select * from persion where name="张三" for update；
如果persion 表存在name索引，那么行锁仅仅加在了name=“张三”的这一行的上面。如果不存在name索引的话,因为MySql数据库不知道sql语句访问的是那条数据，所以会对所有数据行加锁。
 SELECT ... 语句正常情况下为快照读，不加锁；但是在 Serializable 隔离级别下为当前读，加 S 锁；

记录锁、间隙锁、临键锁

记录锁：就是仅仅把一条记录锁上，分为S型记录锁和X型记录锁； 间隙锁：Gap Locks，是Innodb在可重复读提交下为了解决幻读问题时引入的锁机制，可重复读级别下才会有间隙锁。

使用范围条件查询的时候，对于在条件范围内，但不存在的数据，叫做间隙，InnoDB也会对这个间隙进行加锁。不允许别的事务在id值为8的记录前边的间隙插入新记录；

gap锁的提出仅仅是为了防止插入幻影记录而提出的。（防止幻读）
搜索id>100的数据，但是数据只有101,102；此时会对102以后的不存在的数据进行加锁。间隙锁是为了防止幻读，如果查找id>100的记录，第一次查找之后，其中有线程进行了同范围内的插入数据，那么在此查找的时候就会引起幻读。

临键锁：既想锁住某条记录，又想阻止其他事务在该记录前边的间隙插入新纪录（记录锁+gap锁）InnoDB默认加锁方式是 next-key 锁

页级锁：

页级锁是 MySQL 中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。因此，采取了折中的页级锁，一次锁定相邻的一组记录

封锁粒度小：

• 好处：锁定的数据量越少，发生锁冲突的可能就越小，系统的并发程度就越高；

• 坏处：系统开销大（加锁、释放锁、检查锁的状态都需要消耗资源），容易出现死锁

什么是三级封锁协议？

• 一级封锁协议：事务在修改数据之前必须先对其加X锁，直到事务结束才释放。可以解决丢失修改问题（两个事务不能同时对一个数据加X锁，避免了修改被覆盖）；

• 二级封锁协议：在一级的基础上，事务在读取数据之前必须先加S锁，读完后释放。可以解决脏读问题（如果已经有事务在修改数据，就意味着已经加了X锁，此时想要读取数据的事务并不能加S锁，也就无法进行读取，避免了读取脏数据）；

• 三级封锁协议：在二级的基础上，事务在读取数据之前必须先加S锁，直到事务结束才能释放。可以解决不可重复读问题（避免了在事务结束前其它事务对数据加X锁进行修改，保证了事务期间数据不会被其它事务更新）

什么是两段锁协议？

事务必须严格分为两个阶段对数据进行加锁和解锁的操作，第一阶段加锁，第二阶段解锁。也就是说一个事务中一旦释放了锁，就不能再申请新锁了。

可串行化调度是指，通过并发控制，使得并发执行的事务结果与某个串行执行的事务结果相同。事务遵循两段锁协议是保证可串行化调度的充分条件。

什么是 MVCC？

多版本并发控制（Multi-Version Concurrency Control, MVCC），为了提高数据库的读写性能，让数据库读写的时候不用去加锁。MVCC主要是处理读请求的，这个读指的是快照读而不是当前读。解决读写冲突的无锁并发控制

快照读与当前读

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

select * from table ...;

当前读 读取的是数据库中最新的数据，需要对当前读取的数据进行加锁。以下第一个语句需要加 S 锁，其它都需要加 X 锁：

select * from table where ? lock in share mode;（共享锁）
select * from table where ? for update;（排他锁）
insert;（排他锁）
update;
delete;

可以实现读已提交和可重复读两种隔离级别

MVCC的实现依赖于：Undo Log、版本链，Read View。

MVCC操作流程

当查询一条记录时，系统是如何通过MVCC找到它的：
1. 首先获取事务自己的版本号，也就是事务 ID；
2. 获取 ReadView；
3、查询得到的数据，然后与readview中的事务版本号进行比较；（先查表中绿色的，判断是否可见）
4、如果不符合readview规则，就需要从undolog中获取历史快照（回滚，从版本链中判断上一个版本是否满足，不满足继续回滚）
5、最后返回符合条件的数据（最终取的数据）

历史快照保存在Undo log里。回滚指针指向上一个历史版本，构成版本链。

ReadView是事务在使用MVCC机制时进行快照读操作时产生的读视图。（跟事务是一对一的关系）。

通过ReadView来判断select时取版本链中哪一条记录。

readview中四个重要内容：
creator_trx_id：创建readview的事务id，只读时id默认为0；
trx_ids：活跃的读写事务的id列表（已启动但未提交）；
min_trx_id：活跃事务中的最小id；
max_trx_id：生成readview时系统中应该分配给下一个事务的id值；（系统最大的事务id值，已有最大的+1）
判断条件：
（1）如果被访问版本的事务id属性值trx_id与ReadView中的 creator_trx_id 值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
（2）如果被访问版本的id属性值小于活跃事务中的最小id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
（3）如果被访问版本的id属性值大于或等于ReadView中系统最大的事务id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
（4）如果被访问版本的id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下id属性值是不是在活跃事务的id列表中。
如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问。
如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问。

在READ COMMITTED 隔离级别下，一个事务在执行过程中每次执行SELECT操作时都会生成一个ReadView，ReadView的存在本身就保证了事务不可以读取到未提交的事务所做的更改，也就是避免了脏读现象； 在REPEATABLE READ隔离级别下，一个事务在执行过程中只有第一次执行SELECT操作才会生成一个ReadView，之后的SELECT操作都复用这个ReadView，这样也就避免了不可重复读的问题。

使用 READ UNCOMMITTED 隔离级别的事务，由于可以读到未提交事务修改过的记录，所以直接读取记录的最新版本就好了。
使用 SERIALIZABLE 隔离级别的事务，InnoDB规定使用加锁的方式来访问记录。
都用不到MVCC

MVCC可以在可重复读下通过乐观锁的方式解决幻读问题，可以在大多数情况下替代行级锁，降低系统的开销。（与串行化不同）

数据库的范式？

在关系型数据库中，关于数据库表设计的基本原则、规则就称为范式。

• 第一范式（1NF，Normal Form）：属性不应该是可分的。数据表中的每个字段的值为不可再次拆分的最小数据单元。举例：如果将“电话”作为一个属性（一列），是不符合1NF的，因为电话这个属性可以分解为家庭电话和移动电话...如果将“移动电话”作为一个属性，就符合1NF；

• 第二范式 2NF：首先满足第一范式，并且表中所有非主键列不存在对主键的部分依赖，都必须完全依赖主键，不能只依赖主键的一部分。第二范式要求每个表只描述一件事情。

  • B完全依赖于A，就是说A中的所有属性唯一决定B，属性少了就不能唯一决定，属性多了则有冗余（叫依赖不叫完全依赖）。

  • 要求表中的所有列，都必须依赖于主键，而不能有任何一列与主键没有关系。

• 第三范式 3NF：在 2NF 的基础上，每列和主键列唯一相关，不存在对非主键列的传递依赖。

  • 传递依赖：如果C依赖于B，B依赖于A，那么C传递依赖于A；

  • 3NF在2NF的基础上，消除了非主属性之间的依赖；比如一个表中，主属性有（学号），非主属性有（姓名，院系，院长名），可以看到院长名这个非主属性依赖于院系，传递依赖于学号。消除的办法是分解。 必须先满足第二范式（2NF），要求：表中的每一列只与主键直接相关而不是间接相关，（表中的每一列只能依赖于主键）；

不符合范式会出现哪些异常？

• 冗余数据：某些同样的数据多次出现（如学生姓名）；

• 修改异常：修改了一个记录中的信息，另一个记录中相同的信息却没有修改；

• 删除异常：删除一个信息，那么也会丢失其它信息（删除一个课程，丢失了一个学生的信息）；

• 插入异常：无法插入（插入一个还没有课程信息的学生）

范式的缺点

数据库六大范式，一级比一级要求得严格。各种范式呈递次规范，越高的范式。

可能降低查询的效率，范式即是对数据库表设计的约束，因为范式等级越高，数据库冗余越小，但是设计出来的数据表就越多，表设计就越复杂，进行查询时可能需要关联多张表。

对于我们后期对数据库表的维护以及扩展、删除、备份等种种操作带来了一定的难度。所以，在实际开发中我们只需要遵循数据库前面的三大范式即可，不需要额外延申扩展。

实际设计中可能违反范式化的原则，通过增加少量的冗余或重复的数据提高数据库的读性能，空间换时间。

在我自己的项目中

实际上没有严格满足第三范式，基本上算是结构比较简单的二范式。比如项目中的用户表，文章表，评论表这些，满足一个表只描述一个事情，非主键列对主键列，也就是对应的id是完全依赖的。但是并没有严格让非主键列不传递依赖于非主键列。

反范式化

业务优先的原则
规范化 vs 性能
1. 为满足某种商业目标 , 数据库性能比规范化数据库更重要
2. 在数据规范化的同时 , 要综合考虑数据库的性能
3. 通过在给定的表中添加额外的字段，以大量减少需要从中搜索信息所需的时间
4. 通过在给定的表中插入计算列，以方便查询
冗余字段要符合两个条件：不需要经常修改；查询的时候不可或缺
当冗余信息有价值或者能大幅度提高查询效率的时候，才会采取反范式化的操作
适用场景：历史数据，历史快照

E-R 模型

数据库设计是牵一发而动全身的，需要一些办法提前看到数据库的全貌。ER模型也叫实体关系模型，是用来描述现实生活中客观存在的事物、事物的属性，以及事物间关系的一种模型。在开发基于数据库的信息系统的设计阶段，通常使用ER模型来描述信息需求和信息特性，帮助我们理清业务逻辑，从而设计出优秀的数据库。

三个要素：
实体：可以看作数据对象，独立存在；用矩形表示；分为强实体和弱实体，强实体不依赖于其他实体，弱实体依赖于另一个实体
属性：实体的特性，属性不可再分；椭圆形（实体是表，属性是表中字段）
关系：实体之间的关系；菱形

ER模型图转换成数据表（把抽象出来的数据模型，落实到具体的数据库设计中）
转换的原则：
（1）一个实体通常转换成一个数据表；
（2）一个多对多的关系，也转换成一个表；
（3）一个1对1，或者1对多的关系，往往通过表的外键来表达；
（4）属性转换成表的字段

列举几种表连接方式？

• 内连接（Inner Join）：仅将两个表中满足连接条件的行组合起来作为结果集

  • 自然连接：只考虑属性相同的元组对；

  • 等值连接：给定条件进行查询

• 外连接（Outer Join）

  • 左连接：左边表的所有数据都有显示出来，右边的表数据只显示共同有的那部分，没有对应的部分补NULL；

  • 右连接：和左连接相反；

  • 全外连接（Full Outer Join）：查询出左表和右表所有数据，但是去除两表的重复数据

• 交叉连接（Cross Join）：返回两表的笛卡尔积（对于所含数据分别为m、n的表，返回m*n的结果）

用inner join关键字 和 用Where 里写等号

select * from tb1 INNER JOIN tb2 ON tb1.id = tb2.id

select * from tb1,tb2 where tb1.id = tb2.id

没有本质区别，结果也一样。

inner join连接是sql99的语法和sql92语法中的等值连接效果是一样的，都是查询多表交集。 好处：语句上，连接条件和筛选条件实现了分离，简洁明了！

一个是显式连接，一个是隐式连接，隐性连接随着数据库语言的规范和发展，已经逐渐被淘汰，比较新的数据库语言基本上已经抛弃了隐性连接，全部采用显性连接了。

join是基于hashtable连接比较，而=直接就是取笛卡尔集再过滤，所以后者效率低，是O(N^2)，前者是O(LogN)

什么是存储过程？有哪些优缺点？

存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合。想要实现相应的功能时，只需要调用这个存储过程就行了（类似于函数，输入具有输出参数）。

优点：

• 预先编译，而不需要每次运行时编译，提高了数据库执行效率；

• 封装了一系列操作，对于一些数据交互比较多的操作，相比于单独执行SQL语句，可以减少网络通信量；

• 具有可复用性，减少了数据库开发的工作量；

• 安全性高，可以让没有权限的用户通过存储过程间接操作数据库；

• 更易于维护

缺点：

• 可移植性差，存储过程将应用程序绑定到了数据库上；

• 开发调试复杂：没有好的IDE；

• 修改复杂，需要重新编译，有时还需要更新程序中的代码以更新调用

Drop/Delete/Truncate的区别？

• Delete用来删除表的全部或者部分数据，执行delete之后，用户需要提交之后才会执行，会触发表上的DELETE触发器（包含一个OLD的虚拟表，可以只读访问被删除的数据），DELETE之后表结构还在，删除很慢，一行一行地删，因为会记录日志，可以利用日志还原数据；

• Truncate删除表中的所有数据，这个操作不能回滚，也不会触发这个表上的触发器。操作比DELETE快很多（直接把表drop掉，再创建一个新表，删除的数据不能找回）。如果表中有自增（AUTO_INCREMENT）列，则重置为1；

• Drop命令从数据库中删除表，所有的数据行，索引和约束都会被删除；不能回滚，不会触发触发器；

​

什么是触发器？

​

触发器（TRIGGER）是由事件（比如INSERT/UPDATE/DELETE）来触发运行的操作（不能被直接调用，不能接收参数）。在数据库里以独立的对象存储，用于保证数据完整性（比如可以检验或转换数据）。

​

有哪些约束类型？

​

约束（Constraint）类型：主键（Primary Key）约束，唯一约束（Unique），检查约束，非空约束，外键（Foreign Key）约束。

什么是视图？什么是游标？

• 视图：从数据库的基本表中通过查询选取出来的数据组成的虚拟表（数据库中存放视图的定义）。可以对其进行增/删/改/查等操作。视图是对若干张基本表的引用，一张虚表，查询语句执行的结果，不存储具体的数据（基本表数据发生了改变，视图也会跟着改变）；可以跟基本表一样，进行增删改查操作(ps:增删改操作有条件限制)；如连表查询产生的视图无法进行，对视图的增删改会影响原表的数据。好处：

  • 通过只给用户访问视图的权限，保证数据的安全性；

  • 简化复杂的SQL操作，隐藏数据的复杂性（比如复杂的连接）；

• 游标（Cursor）：用于定位在查询返回的结果集的特定行，以对特定行进行操作。使用游标可以方便地对结果集进行移动遍历，根据需要滚动或对浏览/修改任意行中的数据。主要用于交互式应用。

---

主键和外键

主键要求非空且唯一，一个表只能有一个，但可以多个字段一起作为一张表的主键——联合主键。

主键（primary key）和候选键（candidate key）有什么区别？

候选键可以是任何列或可以作为数据库中唯一键的列组合。一张表中可以有多个候选键。每个候选键都可以作为主键。

主键是唯一标识记录的列或列组合。只有一个候选键可以是主键。

自动增长型字段

很多数据库设计者喜欢使用自动增长型字段，因为它使用简单。自动增长型字段允许我们在向数据库添加数据时，不考虑主键的取值，记录插入后，数据库系统会自动为其分配一个值，确保绝对不会出现重复。

外键： 如果一个实体的某个字段指向另一个实体的主键，就称为外键。被指向的实体，称之为主实体（主表），也叫父实体（父表）。负责指向的实体，称之为从实体（从表），也叫子实体（子表）

外键的作用： ①为了一张表记录的数据不要太过冗余。

②保持数据的一致性、完整性。

数据表无主键会怎样（索引）

如果定义了主键，InnoDB会使用主键作为聚簇索引。

如果没有定义主键，那么会使用第一个非空且唯一的列作为聚簇索引。

如果没有符合条件的列，InnoDB会自动生成会自动创建一个隐藏的row-id作为聚集索引；

这个在既没有主键，也没有一个非空唯一键的InnoDB表中自动添加的被称为ROW_ID的列，既不能被任何查询访问，也不能被内部（例如基于行的复制）使用。所有用ROW_ID列的表，共享同一个被保存在数据字典中的全局序列数。

缺少主键或者非空索引存在问题

• 使用不了主键索引，查询会进行全表扫描

• 影响数据插入性能，插入数据需要生成ROW_ID，而生成的ROW_ID是全局共享的，并发会导致锁竞争，影响性能

索引的实现原理（B+树）

索引是存储引擎用于快速找到数据记录的一种结构。

建索引是为了减少磁盘I/O的次数，加快查询效率

索引是帮助MySQL高效获取数据的数据结构

• 缺点：
创建和维护索引要耗费时间；
索引需要占磁盘空间
虽然大大提高了查询速度，但降低了更新表的速度。当对表中数据增删改时，索引也要动态维护。
• 综合考虑
如果需要频繁插入数据
先删除表中的索引，然后插入数据，插入完成后再创建索引。

B树

多路平衡M叉树，考虑到数据量N大，树的分叉M大的时候，M叉树的高度远小于二叉树的高度；

B树在插入和删除节点的时候如果导致树不平衡，会自动调整；
叶子节点和非叶子节点都存放数据，搜索可能在非叶子节点结束
搜索性能相当于在关键字全集内做一次二分查找

B+树

• 聚簇索引

• 所有的用户记录都存储在叶子节点，索引即数据，数据即索引
  
  “聚簇”表示数据行和相邻的键值聚簇的存储在一起
  
  页内的记录是按照主键的大小顺序排成一个单向链表。各个存放用户记录的页也是根据页中用户记录的主键大小顺序排成一个双向链表 。存放目录项记录的页分为不同的层次，在同一层次中的页也是根据页中目录项记录的主键大小顺序排成一个双向链表。
  
  限制：只有InnoDB支持聚簇索引，每个表只有一个，一般就是该表的主键。如果没有定义主键，InnoDB会选择非空的唯一索引代替，如果没有这样的索引，会隐式的定义一个主键来作为聚簇索引。
  
  不是人为创建的，Innodb默认就有

• 非聚簇索引（二级索引）

  想以别的列作为搜索条件，比如以C2列

• 使用记录C2列的大小进行记录和页的排列
  
  叶子节点存储的不是完整的用户信息，只是C2列+主键这两个列的值
  
  目录项不再是主键+页号，变成了C2列+页号的搭配
  
  一张表可以有多个非聚簇索引
  
  聚簇索引查询效率高，增删改非聚簇索引高

• 回表：根据以C2列大小排序的B+树只能确定要查找记录的主键值，如果想得到完整记录，需要到聚簇索引中再查一遍，这个过程叫回表。（根据C2列的值查询一条完整的用户记录需要使用2棵B+树）

• 为什么不把完整的用户记录放到二级索引的叶子节点？ 太占空间，每建一棵B+树都需要把所有的用户记录拷贝一遍。

B+树的节点（数据页）

InnoDB 的数据是按「数据页」为单位来读写的，也就是说，当需要读一条记录的时候，并不是将这个记录本身从磁盘读出来，而是以页为单位，将其整体读入内存。一次最少从磁盘中读取 16K 的内容到内存中。

采用链表的结构让数据页之间不需要是物理上的连续，而是逻辑上的连续；

一个结点（数据页）的大小应该为页（操作系统）的整数倍

操作上系统是按页4kb为单位存取的，因此为了避免数据的浪费，一个结点的大小应该为页的整数倍。MySQL数据库的一页大小为16KB，因此为一个结点应为4页。

行溢出

innodb的页块大小默认为16kb，表中数据是存放在B-tree node的页块中，但如果表中一行的数据长度超过了16k，这时候就会出现行溢出，溢出的行是存放在另外的地方，存放该溢出数据的页叫uncompresse blob page。

innodb采用聚簇索引的方式把数据存放起来，即B+树结构，因此每个页块中至少有两行数据，否则就失去了B+树的意义（每一个页中只有一条数据，整个树成为了一条双向链表），这样就得出了一行数据的最大长度就限制为了8k。

当插入的一行数据不能在一个数据页块中存放时，为了保证该页至少能存放两行数据，innodb将会自动部分数据溢出到另外页中，一部分数据将存放在数据页块中，其大小为该列的前768字节，同时接着还有偏移指向溢出页。

开发中B+树通常不会超过四层

B+树有几层，查找时就进行了几次IO，层数越低越好；

假设存放用户记录的叶子节点代表的数据页可以存放100条数据，所有存放目录项记录的节点可以存放1000条目录数据；（目录数据占的小）
如果只有1层，最多能存放100条记录
2层，存放1000*100=10，0000条记录
3层，存放1000*1000*100= 1亿条记录
4层， 1000亿条记录 
实际表中不会有这么多数据，所以一般情况下不会超过四层。考虑到InnoDB存储引擎设计时是将根节点常驻内存的，所以查找某一键值的行记录时最多只需1-3次磁盘I/O操作

使用B树和B+树的比较

InnoDB的索引使用的是B+树实现，B+树对比B树的好处：

B 树对于特定记录的查询，其时间复杂度更低。而 B+ 树对于范围查询则更加方便，另外 B+ 树相对于 B 树来说更加扁平。

• IO次数少：B+树的中间结点只存放索引，数据都存在叶结点中，因此中间结点可以存更多的数据，让索引树更加矮胖；

• 范围查询效率更高：B树需要中序遍历整个树，只B+树需要遍历叶结点中的链表；

• 查询效率更加稳定：每次查询都需要从根结点到叶结点，路径长度相同，所以每次查询的效率都差不多

B树索引和哈希索引的比较

哈希索引能以 O(1) 时间进行查找，但是只支持精确查找，无法用于部分查找和范围查找，无法用于排序与分组；B树索引支持大于小于等于查找，范围查找。哈希索引遇到大量哈希值相等的情况后查找效率会降低。哈希索引不支持数据的排序。

Redis中用了Hash索引

MyISAM索引的原理（也是B+树）

• • 文件和数据文件是分离的,索引文件仅保存数据记录的地址。

  • MyISAM将索引和数据分开存储，叶子节点存放的是主键值（某一列）+数据记录的地址

  • 都是非聚簇索引

  • 可以没有主键；回表操作十分快速，直接拿地址偏移量到文件中取数据

使用索引的优缺点

• 大大加快了数据的检索速度，降低数据库的IO成本；

• 可以显著减少查询中分组和排序的时间；

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性；

• 将随机 I/O 变为顺序 I/O（B+Tree 索引是有序的，会将相邻的数据都存储在一起）

缺点： 
创建和维护索引要耗费时间；
索引需要占磁盘空间；
虽然大大提高了查询速度，但降低了更新表的速度。当对表中数据增删改时，索引也要动态维护。
所以建议单张表索引数量不超过6个（创建索引多时，会给优化器造成负担，增加优化器生成执行计划时间，降低查询效率）

创建索引的三种方式

创建表的定义语句create table中指定索引列；
	隐式的方式：在声明有主键约束、唯一性约束、外键约束的字段上，会自动的添加相关的索引
	显式的创建：CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name [length]) [ASC | DESC]
    UNIQUE、FULLTEXT和SPATIAL为可选参数，分别表示唯一索引、全文索引和空间索引；
    INDEX与KEY为同义词，两者的作用相同，用来指定创建索引；
    index_name 指定索引的名称，可选，如果不指定，那么MySQL默认col_name为索引名；
    col_name 为需要创建索引的字段列，该列必须从数据表中定义的多个列中选择；
    length 为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度；
    ASC或DESC指定升序或者降序的索引值存储。
    MySQL8.0支持降序索引
修改表的时候：alter table语句；
alter table 字段 add index [index_name] (col_name)
或者使用create index语句在已存在的表上添加索引
create index index_name on table_name (col_name)

哪些情况下索引会失效？

用不用索引，是优化器说了算，基于cost开销，怎么开销少就怎么来；另外跟数据库版本、数据量、数据选择度都有关系。（不一定严格按照规则，一切都基于优化器对成本的考虑）

• 以“%(表示任意0个或多个字符)”开头的LIKE语句；（模糊查询，开头不确定，无法从B+树中扫描）

• OR语句前后没有同时使用索引；

• 范围条件右边的索引失效（创建联合索引中，把范围涉及到的字段写在最后）

• 计算、函数、类型转换导致索引失效（会先把数一个一个从B+树取出来，做运算再找）

• !=，<>索引失效，is null 可以， is not null 失效（设计表的时候就设置字段为not null约束）；not like 失效

• 数据类型出现隐式转化（如varchar不加单引号的话可能会自动转换为int型）；

• 数据库和表的字符集要统一使用utf8（不同字符集比较前进行转换会导致索引失效）

• 对于联合索引，必须满足 最左匹配原则/最左前缀原则 (最左优先，eg：多列索引col1、col2和col3，则索引生效的情形包括 col1或 col1，col2或 col1，col2，col3)；

• 如果MySQL估计全表扫描比索引快，则不使用索引（比如非常小的表）

联合索引

• • 属于非聚簇索引

  • 可以同时以多个列的大小作为排序规则，也就是同时为多个列建立索引，比方说我们想让B+树按

  • 照 c2和c3列 的大小进行排序，这个包含两层含义：

先把各个记录和页按照c2列进行排序。

在记录的c2列相同的情况下，采用c3列进行排序

注意一点，以c2和c3列的大小为排序规则建立的B+树称为联合索引 ，本质上也是一个二级索引。

1.SELECT * FROM table WHERE a = 1 and b = 2 and c = 3; 如何建立索引?
此题正确答法是:(a,b,c)或者(c,b,a)或者(b,a,c)都可以，重点要的是将区分度高的字段放在前面，区分度低的字段放后面。像性别、状态这种字段区分度就很低，我们一般放后面。
例如假设区分度由大到小为b,a,c。那么我们就对(b,a,c)建立索引。在执行sql的时候，优化器会帮我们调整where后a,b,c的顺序，让我们用上索引。

2.SELECT * FROM table WHERE a > 1 and b = 2; 
此题正确答法是，对(b,a)建立索引。
如果你建立的是(a,b)索引，那么只有a字段能用得上索引，毕竟最左匹配原则遇到范围查询就停止匹配。如果对(b,a)建立索引那么两个字段都能用上，优化器会帮我们调整where后a,b的顺序，让我们用上索引。

3.SELECT * FROM `table` WHERE a > 1 and b = 2 and c > 3; 如何建立索引?
此题回答也是不一定，(b,a)或者(b,c)都可以，要结合具体情况具体分析。

4.SELECT * FROM `table` WHERE a = 1 ORDER BY b;
如何建立索引？这还需要想？一看就是对(a,b)建索引，当a = 1的时候，b相对有序，可以避免再次排序！那么

SELECT * FROM `table` WHERE a > 1 ORDER BY b; 
如何建立索引？只对(a)建立索引，因为a的值是一个范围，这个范围内b值是无序的，没有必要对(a,b)建立索引。

SELECT * FROM `table` WHERE a = 1 AND b = 2 AND c > 3 ORDER BY c;
(b,a,c)或者(a,b,c)都可以

5.SELECT * FROM `table` WHERE a IN (1,2,3) and b > 1; 
如何建立索引？还是对(a,b)建立索引，因为IN在这里可以视为等值引用，不会中止索引匹配，所以还是(a,b)!

SELECT * FROM `table` WHERE a = 1 AND b IN (1,2,3) AND c > 3 ORDER BY c;
如何建立索引？此时c排序是用不到索引的。对(a,b,c)建立索引

6.select where a=1 order by b asc, cdesc 
asc和desc混用，8.0之前不走索引，只走ab；
8.0引入了 Descending Index jiang'xu的特性，允许利用索引直接 asc 和 desc 混合排序。

最左匹配原则

最左前缀原则就是只要查询的是联合索引的最左 N 个字段，就可以利用该联合索引来加速查询。 不按照最左匹配来为什么失效，其原因就在于联合索引的 B+ 树中的键值是排好序的。不过，这里指的排好序，其实是相对的，举个例子，有 (a, b, c) 联合索引，a 首先是排序好的，而 b 列是在 a 列排序的基础上做的排序，同样的 c 是在 a,b 有序的基础上做的排序。所以说，如果有 where a = xxx order by b = xxx 这种请求的话，是可以直接在这颗联合索引树上查出来的，不用对 b 列进行额外的排序；而如果是 where a = xxx order by c = xxx 这种请求的话，还需要额外对 c 列进行一次排序才行。 另外，如果有对 a,b,c 的联合条件查询的话，并且 a 是模糊匹配或者说是范围查询的话，其实并不能完全踩中联合索引（a,b,c），a 列右边的所有列都无法使用索引进行快速定位了。所以这个时候就需要进行回表判断。也就是说数据库会首先根据索引来查找记录，然后再根据 where 条件来过滤记录。 不过在 MySQL 5.6 中支持了索引下推 ICP，数据库在取出索引的同时，会根据 where 条件（中的联合索引列）直接过滤掉不满足条件的记录，减少回表次数

回表

MySQL 要先查询到主键索引,然后再用主键索引定位到数据。

根据以C2列大小排序的B+树只能确定要查找记录的主键值，如果想得到完整记录，需要到聚簇索引中再查一遍，这个过程叫回表。（根据C2列的值查询一条完整的用户记录需要使用2棵B+树）

索引下推

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。

在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。 举例

select * from tuser where name like '张%' and age=10;

根据最左前缀匹配原则，这个语句在搜索索引树的时候，只能用 张。在MySQL 5.6之前，存储引擎根据通过联合索引找到name like '张%' 的主键id（1、4），逐一进行回表扫描，去聚簇索引找到完整的行记录，server层再对数据根据age=10进行筛选。需要回表两次，把我们联合索引的另一个字段age浪费了。

而MySQL 5.6 以后， 存储引擎根据（name，age）联合索引，找到name like '张%'，由于联合索引中包含age列，所以存储引擎直接在联合索引里按照age=10过滤。按照过滤后的数据再一一进行回表扫描。只回表了一次。

• 索引下推（ICP）

• • 是MySQL 5.6中新特性，是一种在存储引擎层使用索引过滤数据的优化方式。

  • 在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。

在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。

• 索引下推是先判断过滤后再回表，不加是先回表再判断

• 优点：是对MySQL使用索引从表中检索行的情况的优化。索引下推在非主键索引上的优化，可以有效减少回表的次数，大大提升了查询的效率。

• 使用条件：

① 只能用于二级索引(secondary index)

②explain显示的执行计划中type值（join 类型）为 range 、 ref 、 eq_ref 或者 ref_or_null 。

③ 并非全部where条件都可以用ICP筛选，如果where条件的字段不在索引列中，还是要读取整表的记录到server端做where过滤。

④ ICP可以用于MyISAM和InnnoDB存储引擎

⑤ MySQL 5.6版本的不支持分区表的ICP功能，5.7版本的开始支持。

⑥ 当SQL使用覆盖索引时，不支持ICP优化方法。（不会回表肯定就不支持ICP）

覆盖索引

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。

• 是非聚簇复合索引的一种形式，建索引的字段正好是覆盖查询条件中所涉及的字段，不用再回表操作（不用再去聚簇索引中找）

• 简单说就是， 索引列+主键 包含 SELECT 到 FROM之间查询的列 。

好处：避免回表；把随机I/O变成了顺序I/O;

弊端：索引字段的维护是有代价的，因此在建立冗余索引来支持覆盖索引需要权衡考虑。

那些地方不适合创建索引

where中使用不到的字段；数据量小的表；大量重复数据的列（10%）；经常更新的表和字段；不要使用无序的值作为索引；像性别这样的字段；需要经常增删改的

在哪些地方适合创建索引？

• 某列经常作为最大最小值；

• 字段的数值有唯一性的限制；

• 经常被查询的字段；where后的

• update、delete的where条件列（如果更新的是非索引字段，提升的效果更明显；因为非索引字段不需要对索引进行维护）

• 经常用作表连接的字段；（该字段在多张表中的类型必须一致，避免索引失效）

• 经常出现在ORDER BY/GROUP BY/DISTINCT后面的字段

  (索引本来就会按顺序把组一样的放一块，去重也会快；在select查询时，先group by 再order by，所以按照（分组后字段， 顺序后字段）的联合索引效率最高)

创建索引时需要注意？

• 只应建立在小字段上，而不要对大文本或图片建立索引（一页存储的数据越多一次IO操作获取的数据越大效率越高）；

• 建立索引的字段应该非空，在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。应该用0、一个特殊的值或者一个空串代替NULL；

• 选择数据密度大（唯一值占总数的百分比很大）的字段作索引

• 对于字符长的使用字符串前缀创建索引

• 使用最频繁的列放到联合索引的左侧

索引的分类？

• 普通索引

• 唯一索引 UNIQUE：索引列的值必须唯一，但允许有空值；

  普通索引和唯一索引查询效率基本没有差别
  根据页结构的原理，读取一条记录时，不是单独将这条记录从磁盘中读出去，而是将这个记录所在的页加载到内存中的读取。普通索引的查找就是在内存中多几次判断的操作；对于CPU来说，这些操作消耗的时间可以忽略不计。

• 主键索引 PRIMARY KEY：必须唯一，不允许空值（是一种特殊的唯一索引；MySQL创建主键时默认为聚集索引，但主键也可以是非聚集索引）；

  一张表最多只有一个主键索引，因为数据存储在文件中只能按照一种顺序进行存储。

• 单列索引和多列索引/联合索引：索引的列数；

• 覆盖（Covering）索引：索引包含了所有满足查询所需要的数据，查询的时候只需要读取索引而不需要回表读取数据；

• 聚集（Clustered）索引/非聚集索引：对磁盘上存放数据的物理地址重新组织以使这些数据按照指定规则排序的一种索引（数据的物理排列顺序和索引排列顺序一致）。因此每张表只能创建一个聚集索引（因为要改变物理存储顺序）。优点是查询速度快，因为可以直接按照顺序得到需要数据的物理地址。缺点是进行修改的速度较慢。对于需要经常搜索范围的值很有效。非聚集索引只记录逻辑顺序，并不改变物理顺序；

• 分区索引（？）

• 虚拟索引（Virtual）：模拟索引的存在而不用真正创建一个索引，用于快速测试创建索引对执行计划的影响。没有相关的索引段，不增加存储空间的使用

热备份和冷备份

• 热备份：在数据库运行的情况下备份的方法。优点：可按表或用户备份，备份时数据库仍可使用，可恢复至任一时间点。但是不能出错

• 冷备份：数据库正常关闭后，将关键性文件复制到另一位置的备份方式。优点：操作简单快速，恢复简单

更详细的可以参考：MySQL 数据库的存储引擎与适用场景 - Images

如何优化数据库？

SQL 语句的优化——索引——数据库表结构——系统配置——硬件

SQL 语句的优化

分析慢查询日志（调优的时候才开启）：记录了在MySQL中响应时间超过阀值long_query_time的SQL语句，通过日志去找出IO大的SQL以及发现未命中索引的SQL（通过慢查询日志分析工具mysqldumpslow）show profile命令可以查看执行成本

使用 Explain工具进行分析，查看执行计划：通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及被扫描的行数等问题；（explain + sql语句）

explain语句输出的各个列的作用

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

• 外连接与内连接的查询优化

• • 对于左外连接，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点,一定需要建立索引

  • 对于内连接，查询优化器可以决定谁作为驱动表，谁作为被驱动表

  • 内连接，如果表的连接条件只能有一个字段有索引，则有索引的字段会被作为被驱动表；如果都存在索引，选择小表（数据量小的那个表）作为驱动表

• 子查询优化

  子查询可以一次性完成很多逻辑上需要多个步骤才能完成的SQL操作

  子查询是MySQL的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。

  原因：

  ① 执行子查询时，MySQL需要为内层查询语句的查询结果 建立一个临时表 ，然后外层查询语句从临时表

  中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。

  ② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引 ，所以查询性能会

  受到一定的影响。

  ③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

  尽量不要使用NOT IN 或者 NOT EXISTS，用LEFT JOIN xxx ON xx WHERE xx IS NULL替代

在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表 ，其速度比子查询

要快 ，如果查询中使用索引的话，性能就会更好。

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

  • 让缓存更高效。对于连接查询，如果其中一个表发生变化，那么整个查询缓存就无法使用。而分解后的多个查询，即使其中一个表发生变化，对其它表的查询缓存依然可以使用；

  • 分解成多个单表查询，这些单表查询的缓存结果更可能被其它查询使用到，从而减少冗余的查询；

  • 减少锁竞争

• count(*)、count(1)与count(具体字段)

count(1)和count(*)都会对全表进行扫描，统计所有记录的条数，包括那些为null的记录，因此，它们的效率可以说是相差无几。如果是MyISAM是O(1)的复杂度（每张表都存储了一个row_count值记录行数），InnoDB是O(n)的复杂度，全表扫描；

而count(字段)则与前两者不同，它会一行行统计该字段不为null的记录条数。多了一个步骤就是判断所查询的字段是否为NULL，所以他的性能要比COUNT(*)慢.

如果采用count(具体字段)，尽量采用二级索引（因为聚簇索引包含的信息多）；count(*)和count(1)会自动采用占用空间更小的二级索引来统计(Mysql做了优化)

• select(*)

• • 不建议使用*作为查询列表,推荐select 字段列表查询

  • 原因：MySQL解析的过程，会通过查询数据字典将*按序转换成需要的列名，会耗费资源和时间

  • • 无法使用覆盖索引

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

• limit1对优化的影响

针对的是会扫描全表的SQL语句，如果确定结果集只有一条，加上limit 1的时候，当找到一条结果后就不会继续扫描了，会加快查询速度。

如果数据库已经对字段建立了唯一索引，可以通过索引进行查询，不会全表扫描的话，就不需要加limit1.

• 排序优化

• • 在where条件字段上加索引，为什么在order by字段上还要加索引？

MySQL支持两种索引，分别是FileSort和Index排序。

index排序中，索引可以保证数据的有序性，不需要再排序，效率更高；

FileSort一般在内存中进行排序，占用CPU较多。如果待拍结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。

所以要使用索引，避免在order by子句中使用FileSort排序。无法使用index时，对filesort方式进行调优

对filesort方式进行调优的方法：

1. 尝试提高 sort_buffer_size

2. 尝试提高 max_length_for_sort_data

3. Order by 时select * 是一个大忌。最好只Query需要的字段。

order by规则不一致，索引失效（顺序错，不索引；方向反，不索引；都反（都desc）可以索引）

• 分组查询优化

• • group by 使用索引的原则几乎跟order by一致

  • group by 先排序再分组，遵照索引建的最佳左前缀法则

  • where效率高于having，能写在where限定的条件就不要写在having中了

减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group

by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。

分页查询优化

在数据量比较大时，如果进行limit分页查询，在查询时，越往后，分页查询效率越低。

一般分页查询时，一个问题是limit 200 0000，10，此时需要MySQL排序前两百万零十条数据，但是查的是0-2000010记录，仅仅返回200 0000 - 200 0010的记录，其他记录丢弃，查询代价很大。

先用主键关联，然后返回结果集。

通过覆盖索引加子查询形式进行优化：

在索引上完成排序分页操作，先查出在（200 0000，10）的主键id，根据主键关联回原表查询所需的其他列内容。

• 多使用commit

• • 在程序中尽量多使用commit，会释放资源，可以提高程序的性能

  • commit所释放的资源：回滚段上用于恢复数据的信息；被程序语句获得的锁；redo/undo log buffer中的空间；管理上述三种资源的内部花费

大字段查询

大字段慢的原因

其实主要是因为一个页中存放的行数变少了，mysql在操作数据的时候，以page为单位，不管是更新，插入，删除一行数据，都需要将那行数据所在的page读到内存中，然后在进行操作，这样就存在一个命中率的问题，如果一个page中能够相对的存放足够多的行，那么命中率就会相对高一些，性能就会有提升。

优化方法：

主要的应对策略：

1、拆表，可以考虑将大字段专门放在另外一张表中，只有在需要时再关联查询，增加 InnoDB 的当前表缓存命中率。

2、索引，从访问密度较小的数据页改为访问密度很大的索引页，随机io转换为顺序io，同时内存命中率大大提升；

总结：核心思想是让单个page能够存放足够多的行，不断的提示内存的命中率

mysql text字段比较特殊，text用来存储较多字符的字符串，因为text一般比较大，普通索引效率很低， 不可以直接在text字段上创建普通索引。

在text字段上可以创建2种索引：**全文索引**（fulltext index）和**前缀索引**（column prefix index）

(实际上全文索引可以在char，varchar，text上创建）

全文索引将字段中的词汇进行索引；前缀索引会截取前N给字符并创建B+树索引。

3、如果只需要获取大字段的部分数据，可以使用 SUBSTRING( ) 函数，这样可以避免使用磁盘临时表。 4、必要时可以考虑对大字段进行压缩后再存储到表中。

5.页面静态化技术

加速用户访问，先展示主体部分，也就是文章的详情，其他数据异步加载

网页能够增量更新呈现在页面上，而不需要刷新整个页面。

6.浏览器存储介质：网站缓存，更快加载，减轻服务器压力

1.cookie，保存在浏览器，不设置过期时间保存到内存；设置过期时间保存到硬盘。4k

2.webStorage，对cookies的一种改良。

sessionStorage：数据保存到session对象，浏览器关了数据就丢失。

localStorage：数据保存到客户端本地磁盘，数据持久化。

索引的优化

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

数据库表结构的优化

• 设计表时遵循三大范式；

• 选择合适的数据类型：尽可能不要存储NULL字段；使用简单的数据类型（int, varchar/ text）；

• 表的水平切分（Sharding）：将同一个表中的记录拆分到多个结构相同的表中（策略：哈希取模；根据ID范围来分）。当一个表的数据不断增多时，Sharding 是必然的选择，它可以将数据分布到集群的不同节点上，从而缓解单个数据库的压力；

• 表的垂直切分：将一张表按列切分成多个表。可以将不常用的字段单独放在同一个表中；把大字段独立放入一个表中；或者把经常使用的字段（关系密切的）放在一张表中（冷热数据分离）。垂直切分之后业务更加清晰，系统之间整合或扩展容易，数据维护简单

系统配置的优化

• 操作系统：增加TCP支持的队列数；

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

硬件的优化

• 磁盘性能：固态硬盘；

• CPU：多核且高频；

• 内存：增大内存

什么是主从复制？实现原理是什么？

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

实现原理：

将主服务器中的数据更改（增删改）日志写入 Binary log 中；

• 主服务器 binary log dump 线程：是一个主库线程。当从库线程连接的时候， 主库可以将二进制日志发送给从库，当主库读取事件（Event）的时候，会在 Binlog 上 加锁 ，读取完成之后，再将锁释放掉。

• 从服务器 I/O 线程：负责从主服务器读取binary log，并写入本地的 Relay log；

• 从服务器 SQL 线程：负责读取 Relay log，解析出主服务器已经执行的数据更改，并在从服务器中重新执行（Replay），保证主从数据的一致性

为什么要主从复制？

• 读写分离：主服务器负责写，从服务器负责读

  • 缓解了锁的争用，即使主服务器中加了锁，依然可以进行读操作；

  • 从服务器可以使用 MyISAM，提升查询性能以及节约系统开销；

  • 增加冗余，提高可用性

• 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换

• 降低单个服务器磁盘I/O访问的频率，提高单个机器的I/O性能

主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会 存在主从延迟 （比如 500ms），这样就可能造成用户在从库上读取的数据不是最新的数据，也就是主从同步中的数据不一致性问题。

在网络正常的时候，日志从主库传给从库所需的时间是很短的，即T2-T1的值是非常小的。即，网络正常 情况下，主备延迟的主要来源是备库接收完binlog和执行完这个事务之间的时间差。

主备延迟最直接的表现是，从库消费中继日志（relay log）的速度，比主库生产binlog的速度要慢。造成原因：

1、从库的机器性能比主库要差

2、从库的压力大

3、大事务的执行

如何减少主从延迟

1. 降低多线程大事务并发的概率，优化业务逻辑 
2. 优化SQL，避免慢SQL， 减少批量操作 ，建议写脚本以update-sleep这样的形式完成。 3. 提高从库机器的配置 ，减少主库写binlog和从库读binlog的效率差。 
4. 尽量采用 短的链路 ，也就是主库和从库服务器的距离尽量要短，提升端口带宽，减少binlog传输 的网络延时。 
5. 实时性要求的业务读强制走主库，从库只做灾备，备份。

关系型数据库和非关系型数据库的区别？

关系型和非关系型数据库的主要差异是数据存储的方式。关系型数据天然就是表格式的，因此存储在数据表的行和列中。数据表可以彼此关联协作存储，也很容易提取数据。

非关系型数据不适合存储在数据表的行和列中，而是大块组合在一起。非关系型数据通常存储在数据集中，就像文档、键值对或者图结构。你的数据及其特性是选择数据存储和提取方式的首要影响因素。

关系型数据库最典型的数据结构是表，由二维表及其之间的联系所组成的一个数据组织 优点： 1、易于维护：都是使用表结构，格式一致； 2、使用方便：SQL语言通用，可用于复杂查询； 3、复杂操作：支持SQL，可用于一个表以及多个表之间非常复杂的查询。 缺点： 1、读写性能比较差，尤其是海量数据的高效率读写； 2、固定的表结构，灵活度稍欠； 3、高并发读写需求，传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。

非关系型数据库严格上不是一种数据库，应该是一种数据结构化存储方法的集合，可以是文档或者键值对等。

优点：

1、格式灵活：存储数据的格式可以是key,value形式、文档形式、图片形式等等，文档形式、图片形式等等，使用灵活，应用场景广泛，而关系型数据库则只支持基础类型。 2、速度快：nosql可以使用硬盘或者随机存储器作为载体，而关系型数据库只能使用硬盘； 3、高扩展性； 4、成本低：nosql数据库部署简单，基本都是开源软件。

缺点：

1、不提供sql支持，学习和使用成本较高； 2、无事务处理； 3、数据结构相对复杂，复杂查询方面稍欠。

大数据量、高并发数据库的高性能、高可用性解决方案

1.拆表：大表拆小表（垂直拆，水平拆；分表，分区partition，分片sharding），可以在应用层实现，也可以在数据库层面实现一部分；提高系统性能。

垂直分库，分表是指按照业务将表进行分类，分布到不同的数据库上面不同表拆到不同数据库中。按照业务将表进行分类。
		比如把原有的卖家表，分为了商品库和店铺库
水平分库就是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。
	   将店铺ID为单数的和店铺ID为偶数的商品信息分布存在两个表中。

分库分表采取的是分而治之的策略，分库目的是减轻单台MySQL实例存储压力及可扩展性，而分表是解决单张表数据过大以后查询的瓶颈问题。

2.分库：把表放到不同的数据库，这也是分布式数据库的基础；提高系统性能。

3.分布式：不同的数据库放到不同的服务器；提高系统性能。

4.集群：使用数据库复制等技术组建集群，实现读写分离、备份等；提高系统性能、可用性。

5.缓存：对常用的数据进行缓存。提高系统性能。

6.备份：主从库，快照，热备，异地备份等；提高系统可用性。

1000w条数据插入数据库?中间遇到问题怎么办?

【Mysql】大批量(百万级)数据插入数据库应该怎么做(提高效率)？_大量数据导入数据库_小树ぅ的博客-CSDN博客

1.合并sql语句(一个sql插入单条数据转为一个sql插入多条数据)

原因：合并后减少了日志生成量

2.在事务中进行插入操作

*原因：单条sql执行时mysql内部会自动创建事务进行提交，所以通过自主创建事务，减少mysql执行sql时创建事务的消耗。*

3.有序插入

无序插入会增大维护索引的成本

1.Mybatis 轻量级框架插入（无事务）

处理大批量的数据插入操作时，需要过程中实现一个ORM的转换，很慢。

2.采用JDBC批处理（开启事务、无事务）

每次插入10万条数据就提交一次事务，如果是一条一条插的话估计要好几个小时，网上有人试过时间浪费在数据库的连接上了。

1000万条数据288秒完成

出问题：可以看日志，回滚一次事务操作。

Java代码向数据库中插入数据 整体策略：通过多线程分批次对大量数据进行插入操作实现高效插入。

1.比如数据量为10w，将10w条数据分为10个1w条，然后通过创建多（10）个线程，对这10个1w数据进行插入操作。

2.通过遍历数据去进行插入操作实际上就是一条一条的插入，每执行一次insert就要向数据库传一条sql，数据库编译sql，然后执行，这里用的是Statement。这样做插入效率明显很低，所以我们需要采用批量插入大方式去进行，这里需要用到PreparedStatement。