database

数据库

MySQL的逻辑架构

第一层是服务器层，主要提供连接处理、授权认证、安全等功能
第二层实现了MySQL核心服务功能，包括查询解析、分析、优化、缓存以及日期和时间等所有内置函数，所有跨存储引擎的功能都在这一层实现，例如存储过程、触发器和视图等。
第三次是存储引擎层，存储引擎负责MySQL中数据的存储和提取。服务器通过API与存储引擎通信，这些接口屏蔽了不同存储引擎的差异，使得差异对上层查询过程透明。除了会解析外键定义的innodb外，存储引擎不会解析SQL，不同存储引擎之间也不会相互通信，只是简单响应上层服务器请求。

存储引擎 : 其实就是指定表如何存储数据，如何为存储的数据 建立索引 以及 如何更新，查询数据等技术实现的方法。因为在关系数据库中数据的存储是以表的形式存储的，所以存储引擎也可以称为表类型（即存储和操作此表的类型）

InnoDB

InnoDB是MySQL的默认事务型引擎，用来处理大量短期事务。InnoDB的性能和自动崩溃恢复特性使得它在非事务型存储需求中也很流行，除非有特别原因否则应优先考虑InnoDB。

InnoDB采用MVCC来支持高并发，并且实现了四个标准的隔离级别。其默认级别是repeatable read，并通过间隙锁策略防止幻读，间隙锁使InnoDB不仅仅锁定查询涉及的行，还会对索引中的间隙进行锁定防止幻行的插入。

InnoDB表示基于聚簇索引建立的，InnoDB的索引结构和其他存储引擎有很大不同，聚簇索引对主键查询有很高的性能，不过它的二级索引中必须包含主键列，所以如果主键很大的话其他所有索引都会很大，因此如果表上的索引较多的话主键应当尽可能小。

MyISAM

MySQL5.1及之前，MyISAM 是默认存储引擎，MyISAM 提供了大量的特性，包括全文索引、压缩、空间函数等，但不支持事务和行锁，最大的缺陷就是崩溃后无法安全恢复。对于只读的数据或者表比较小、可以忍受修复操作的情况仍然可以使用 MyISAM。

锁

innodb锁模式

InnoDB 实现了以下两种类型的行锁：但是共享锁和排他锁不是只有行锁

共享锁（S-shared）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
排他锁（X-exclusive）：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

为了支持在不同粒度上进行加锁操作(允许行锁和表锁共存，实现多粒度锁机制)，InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁：

意向共享锁（IS- intent share lock）事务想要获得一张表中某几行的共享锁

务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
意向排他锁（IX -intent exclusive lock）事务想要获得一张表中某几行的排他锁

事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁其实不会阻塞除全表扫以外的任何请求。故表级意向锁与行级锁的兼容性如下所示

如果一个事务请求的锁模式与当前的锁兼容， InnoDB 就将请求的锁授予该事务；反之，如果两者不兼容，该事务就要等待锁释放

意向锁的作用

innodb的意向锁主要用在多粒度的锁并存的情况。比如事务A要在一个表上加S锁，如果表中的一行已被事务B加了X锁，那么该锁的申请也应被阻塞。如果表中的数据很多，逐行检查锁标志的开销将很大，系统的性能将会受到影响。为了解决这个问题，可以在表级上引入新的锁类型来表示其所属行的加锁情况，这就引出了“意向锁”的概念。

举个例子，

如果表中记录1亿，事务A把其中有几条记录上了行锁了，这时事务B需要给这个表加表级锁，如果没有意向锁的话，那就要去表中查找这一亿条记录是否上锁了。
如果存在意向锁，那么假如事务Ａ在更新一条记录之前，先加意向锁，再加Ｘ锁，事务B先检查该表上是否存在意向锁，存在的意向锁是否与自己准备加的锁冲突，如果有冲突，则等待直到事务Ａ释放，而无须逐条记录去检测。事务Ｂ更新表时，其实无须知道到底哪一行被锁了，它只要知道反正有一行被锁了就行了。

innodb的意向锁主要作用是处理行锁和表锁之间的矛盾，能够显示“某个事务正在某一行上持有了锁，或者准备去持有锁”

innodb行锁实现原理

行锁是加在索引上的

innodb中的索引数据结构是B+树，数据是有序排列的，从根节点到叶子节点一层层找到对应的数据

普通索引，也叫辅助索引，叶子节点存放的是主键值
聚集索引，主键上的索引，表里的每一条记录都存放在主键的叶子节点上
当通过辅助索引select查询数据的时候，会先在辅助索引中找到对应的主键值，然后用主键值在聚集索引中查找该条记录

表中每一行的数据，是组织存放在聚集索引中的，所以叫做索引组织表。

InnoDB 行锁是通过给索引上的索引项加锁来实现的，这一点 MySQL 与 Oracle 不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB 这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
只有执行计划真正使用了索引，才能使用行锁：即便在条件中使用了索引字段，但是否使用索引来检索数据是由 MySQL 通过判断不同执行计划的代价来决定的，如果 MySQL 认为全表扫描效率更高，比如对一些很小的表，它就不会使用索引，这种情况下 InnoDB 将使用表锁，而不是行锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划（可以通过 explain 检查 SQL 的执行计划），以确认是否真正使用了索引。（更多阅读：MySQL索引总结）
由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然多个session是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的（后使用这些索引的session需要等待先使用索引的session释放锁后，才能获取锁）。应用设计的时候要注意这一点。

MVCC

MVCC只能在read committed和repeatable read两个隔离级别下工作，因为read uncommitted总是读取最新的数据行，而不是符合当前事务版本的数据行，而serializable则会对所有读取的行都加锁。

MVCC，全称Multi-Version Concurrency Control，即多版本并发控制。

MVCC在MySQL InnoDB中的实现主要是为了提高数据库并发性能，用更好的方式去处理读-写冲突，做到即使有读写冲突时，也能做到不加锁，非阻塞并发读

当前读

像select lock in share mode(共享锁), select for update ; update, insert ,delete(排他锁)这些操作都是一种当前读，为什么叫当前读？就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁
快照读
像不加锁的select操作就是快照读，即不加锁的非阻塞读；快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；之所以出现快照读的情况，是基于提高并发性能的考虑，快照读的实现是基于多版本并发控制，即MVCC,可以认为MVCC是行锁的一个变种，但它在很多情况下，避免了加锁操作，降低了开销；既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本

说白了MVCC就是为了实现读-写冲突不加锁，而这个读指的就是快照读, 而非当前读，当前读实际上是一种加锁的操作，是悲观锁的实现

当前读，快照读和MVCC的关系

准确的说，MVCC多版本并发控制指的是 “维持一个数据的多个版本，使得读写操作没有冲突” 这么一个概念。仅仅是一个理想概念
而在MySQL中，实现这么一个MVCC理想概念，我们就需要MySQL提供具体的功能去实现它，而快照读就是MySQL为我们实现MVCC理想模型的其中一个具体非阻塞读功能。而相对而言，当前读就是悲观锁的具体功能实现
要说的再细致一些，快照读本身也是一个抽象概念，再深入研究。MVCC模型在MySQL中的具体实现则是由 3个隐式字段，undo日志 ，Read View 等去完成的

数据库并发场景有三种，分别为：

读-读：不存在任何问题，也不需要并发控制
读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
写-写：有线程安全问题，可能会存在更新丢失问题，比如第一类更新丢失，第二类更新丢失
- 第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了；
- 第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失

MVCC带来的好处

多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。所以MVCC可以为数据库解决以下问题

在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能
同时还可以实现读提交和可重复度隔离级别，解决脏读，幻读？？，不可重复读等事务隔离问题，但不能解决更新丢失问题

小结一下咯
总之，MVCC就是因为大牛们，不满意只让数据库采用悲观锁这样性能不佳的形式去解决读-写冲突问题，而提出的解决方案，所以在数据库中，因为有了MVCC，所以我们可以形成两个组合：

MVCC + 悲观锁
MVCC解决读写冲突，悲观锁解决写写冲突
MVCC + 乐观锁
MVCC解决读写冲突，乐观锁解决写写冲突

这种组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题

MVCC如何在RC级解决不可重复读

MVCC是什么

MVCC 是多版本并发控制，在很多情况下避免加锁，大都实现了非阻塞的读操作，写操作也只锁定必要的行。

InnoDB 的MVCC 通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间间。不过存储的不是实际的时间值而是系统版本号，每开始一个新的事务系统版本号都会自动递增，事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。

MVCC 只能在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作，因为 READ UNCOMMITTED 总是读取最新的数据行，而不是符合当前事务版本的数据行，而 SERIALIZABLE 则会对所有读取的行都加锁。

MySQL 的读写锁

在处理并发读或写时，可以通过实现一个由两种类型组成的锁系统来解决问题。这两种类型的锁通常被称为共享锁和排它锁，也叫读锁和写锁。

读锁是共享的，相互不阻塞，多个客户在同一时刻可以同时读取同一个资源而不相互干扰。但是只能读不能修改。

写锁则是排他的，也就是说一个写锁会阻塞其他的写锁和读锁，确保在给定时间内只有一个用户能执行写入并防止其他用户读取正在写入的同一资源。

在实际的数据库系统中，每时每刻都在发生锁定，当某个用户在修改某一部分数据时，MySQL 会通过锁定防止其他用户读取同一数据。写锁比读锁有更高的优先级，一个写锁请求可能会被插入到读锁队列的前面，但是读锁不能插入到写锁前面。

MySQL的锁策略

表锁是MySQL中最基本的锁策略，并且是开销最小的策略。表锁会锁定整张表，一个用户在对表进行写操作前需要先获得写锁，这会阻塞其他用户对该表的所有读、写操作。只有没有写锁时，其他读取的用户才能获取读锁，读锁之间不互相阻塞。

行锁可以最大程度地支持并发，同时也带来了最大开销。innodb和xtradb以及一些其他存储引擎实现了行锁。行锁只在存储引擎层实现，而服务器层没有实现。

死锁如何解决

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

当多个事务试图以不同顺序锁定资源时就可能会产生死锁，多个事务同时锁定同一个资源时也会产生死锁。

为了解决死锁问题，数据库系统实现了各种死锁检测和死锁超时机制。越复杂的系统，例如InnoDB 存储引擎，越能检测到死锁的循环依赖，并立即返回一个错误。这种解决方式很有效，否则死锁会导致出现非常慢的查询。还有一种解决方法，就是当查询的时间达到锁等待超时的设定后放弃锁请求，这种方式通常来说不太好。InnoDB 目前处理死锁的方法是将持有最少行级排它锁的事务进行回滚。

死锁发生之后，只有部分或者完全回滚其中一个事务，才能打破死锁。对于事务型系统这是无法避免的，所以应用程序在设计时必须考虑如何处理死锁。大多数情况下只需要重新执行因死锁回滚的事务即可。

死锁产生的四个必要条件：

1）互斥条件：指进程对所分配到的资源进行排它性使用，即在一段时间内某资源只由一个进程占用。如果此时还有其它进程请求资源，则请求者只能等待，直至占有资源的进程用毕释放。

2）请求和保持条件：指进程已经保持至少一个资源，但又提出了新的资源请求，而该资源已被其它进程占有，此时请求进程阻塞，但又对自己已获得的其它资源保持不放。

3）不剥夺条件：指进程已获得的资源，在未使用完之前，不能被剥夺，只能在使用完时由自己释放。

4）环路等待条件：指在发生死锁时，必然存在一个进程——资源的环形链，即进程集合{P0，P1，P2，···，Pn}中的P0正在等待一个P1占用的资源；P1正在等待P2占用的资源，……，Pn正在等待已被P0占用的资源。

事务

事务是一组原子性的SQL语句，或者说一个独立的工作单元。如果数据库引擎能够成功地对数据库应用该组查询的全部语句，那么就执行该组查询。如果其中有任何一条语句因为崩溃或其他原因无法执行，那么所有的语句都不会执行。也就是说事务内的语句要么全部执行成功，要么全部执行失败。

数据库事务( transaction)是访问并可能操作各种数据项的一个数据库操作序列，这些操作要么全部执行,要么全部不执行，是一个不可分割的工作单位。事务由事务开始与事务结束之间执行的全部数据库操作组成

事务的特性

原子性 atomicity

一个事务在逻辑上是必须不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说不可能只执行其中的一部分。

一致性 consistency

数据库总是从一个一致性的状态转换到另一个一致性的状态。

隔离性 isolation

针对并发事务而言，隔离性就是要隔离并发运行的多个事务之间的相互影响，一般来说一个事务所做的修改在最终提交以前，对其他事务是不可见的。

持久性 durability

一旦事务提交成功，其修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。

并发一致性问题

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

当数据库上有多个事务同时执行的时候，就可能出现一些并发一致性问题：丢失更新（Last To Modify）、脏读（Dirty Read）、不可重复读（Unrepeatable Read）、幻读（Phantom Read）

为了解决这些问题，就有了 “隔离级别” 的概念。

万事终归有利有弊，隔离级别越高，隔离得越严实，并发一致性问题就越少，那么相应的数据库付出的性能代价也就越大。所以，很多时候，我们都要在这二者之间寻找一个平衡点。

①丢失修改（Lost to modiry）

一个事务的更新操作会被另一个事务的更新操作所覆盖，从而导致数据的不一致。

从数据库层面来讲，数据库本身是可以帮助我们阻止丢失更新问题的发生？

②脏读（Dirty read）

一个事务读到了另一个事务中的“脏数据”，脏数据就是指事务未提交的数据。。

③不可重复读（Unrepeatable read）

在一个事务内多次读取同一数据集合。在这个事务还没结束时，另一个事务也访问该同一数据集合，并做了一些修改操作。因此，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的数据可能是不一样的。

④幻读（Phantom read）

幻读本质上是属于不可重复读的一种情况。区别在于，不可重复读主要是针对数据的更新（即事务的两次读取结果值不一样），而幻读主要是针对数据的增加或减少（即事务的两次读取结果返回的数量不一样）。

不可重复读和幻读的区别

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

MySQL的隔离级别

未提交读 READ UNCOMMITTED

在该级别事务中的修改即使没有被提交，对其他事务也是可见的。事务可以读取其他事务修改完但未提交的数据，这种问题称为脏读。这个级别还会导致不可重复读和幻读，性能没有比其他级别好很多，很少使用。

提交读 READ COMMITTED

多数数据库系统默认的隔离级别。提交读满足了隔离性的简单定义：一个事务开始时只能"看见"已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前的任何修改对其他事务都是不可见的。也叫不可重复读，因为两次执行同样的查询可能会得到不同结果。

可重复读 REPEATABLE READ（MySQL默认的隔离级别）

可重复读解决了不可重复读的问题，保证了在同一个事务中多次读取同样的记录结果一致。但还是无法解决幻读，所谓幻读指的是当某个事务在读取某个范围内的记录时，会产生幻行。InnoDB 存储引擎通过多版本并发控制MVCC 解决幻读的问题。

可串行化 SERIALIZABLE

最高的隔离级别，通过强制事务串行执行，避免幻读。可串行化会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题。实际应用中很少用到这个隔离级别，只有非常需要确保数据一致性且可以接受没有并发的情况下才考虑该级别。

隔离级别和并发一致性

对于两个并发执行的事务，如果涉及到操作同一条记录的时候，可能会发生问题。因为并发操作会带来数据的不一致性，包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让我们有针对性地选择事务的隔离级别，避免数据不一致的问题。

SQL标准定义了4种隔离级别，分别对应可能出现的数据不一致情况：

Isolation Level	脏读（Dirty Read）	不可重复读（Non Repeatable Read）	幻读（Phantom Read）
Read Uncommitted	Yes	Yes	Yes
Read Committed	-	Yes	Yes
Repeatable Read	-	-	Yes
Serializable	-	-	-

查询执行流程

简单来说分为五步：

① 客户端发送一条查询给服务器。

② 服务器先检查查询缓存，如果命中了缓存则立刻返回存储在缓存中的结果，否则进入下一阶段。

③ 服务器端进行 SQL 解析、预处理，再由优化器生成对应的执行计划。

④ MySQL 根据优化器生成的执行计划，调用存储引擎的 API 来执行查询。

⑤ 将结果返回给客户端。

数据类型

varchar和char

VARCHAR 用于存储可变字符串，是最常见的字符串数据类型。它比 CHAR 更节省空间，因为它仅使用必要的空间。VARCHAR 需要 1 或 2 个额外字节记录字符串长度，如果列的最大长度不大于 255 字节则只需要 1 字节。VARCHAR 不会删除末尾空格。

VARCHAR 适用场景：字符串列的最大长度比平均长度大很多、列的更新很少、使用了 UTF8 这种复杂字符集，每个字符都使用不同的字节数存储。

CHAR 是定长的，根据定义的字符串长度分配足够的空间。CHAR 会删除末尾空格。

CHAR 适合存储很短的字符串，或所有值都接近同一个长度，例如存储密码的 MD5 值。对于经常变更的数据，CHAR 也比 VARCHAR更好，因为定长的 CHAR 不容易产生碎片。对于非常短的列，CHAR 在存储空间上也更有效率，例如用 CHAR 来存储只有 Y 和 N 的值只需要一个字节，但是 VARCHAR 需要两个字节，因为还有一个记录长度的额外字节。

DATETIME 和 TIMESTAMP

DATETIME 能保存大范围的值，从 1001~9999 年，精度为秒。把日期和时间封装到了一个整数中，与时区无关，使用 8 字节存储空间。

TIMESTAMP 和 UNIX 时间戳相同，只使用 4 字节的存储空间，范围比 DATETIME 小得多，只能表示 1970 ~2038 年，并且依赖于时区。

数据类型优化策略

更小的通常更好

一般情况下尽量使用可以正确存储数据的最小数据类型，更小的数据类型通常也更快，因为他们占用更小的磁盘、内存和CPU缓存
尽可能简单

简单数据类型的操作通常需要更少的的CPU周期，例如整数比字符操作代价更低，因为字符集和校对规则使字符相比整形更复杂。应该使用MySQL内建类型date、time和datetime而不是字符串来存储日期和时间
尽量避免null

通常情况下最好指定列为not null，除非需要存储null值。因为如果查询中包含可为null的列对MySQL来说更难优化，可以为null的列使索引、索引统计和值比较都更为复杂，并且使用更多的存储空间

索引

索引的作用

通过创建唯一性索引，可以保证数据库表中的每一行数据的唯一性
可以大大加快数据的检索速度（大大减少检索的数据量）
帮助服务器避免排序和临时表
将随机IO变为顺序IO
加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义

索引的分类

主键索引--主键字段
唯一索引--唯一字段
普通索引--非主键非唯一
全文索引--全文检索--很少用--一般用Lucene-->solr->es
组合索引--表中的多个字段值可以共同构成索引

索引并不是越多越好，一张表建议不要超过5-6个索引

索引的维护会非常麻烦
占用的存储空间变大，会导致io增多

索引常见问题

一个表中只能有一个索引吗？

可以按照需求创建多个索引，但是不要超过5/6个
每一个索引是一棵B+树，还是所有的索引共用一棵B+树？

一个索引一棵B+树
如果有多棵B+树的话，那么数据存储几份？

一份
那么其他索引的叶子节点放什么数据？

主键（不太精确），放跟数据绑定存储的索引列的值

在innodb存储引擎中，数据在进行插入的时候需要跟某一个索引列绑定在一起，这个索引列如果有主键，那么使用主键，如果没有主键，那么使用唯一键，如果没有唯一键，那么使用6字节的rowid

非/聚簇索引

聚簇索引：数据跟索引绑定存储在一起

非聚簇索引：数据跟索引分开存储

存储引擎：不同数据文件在磁盘的不同组织形式

innodb：既有聚簇索引（主键索引）也有非聚簇索引（其他索引）
myisam：非聚簇索引

回表

索引覆盖

最左匹配

索引使用原则

建立索引

对于查询频次较高且数据量比较大的表建立索引。

索引字段的选择，最佳候选列应当从where子句的条件中提取，如果where子句中的组合比较多，应当挑选最常用、过滤效果最好的列的组合。

业务上具有唯一特性的字段，即使是多个字段的组合，也必须建成唯一索引。
使用前缀索引

索引列开始的部分字符，索引创建后也是使用硬盘来存储的，因此短索引可以提升索引访问的 IO 效率。对于 BLOB、TEXT 或很长的 VARCHAR 列必须使用前缀索引，MySQL 不允许索引这些列的完整长度。前缀索引是一种能使索引更小更快的有效方法，但缺点是 MySQL 无法使用前缀索引做 ORDER BY 和 GROUP BY，也无法使用前缀索引做覆盖扫描。
选择合适的索引顺序

当不需要考虑排序和分组时，将选择性最高的列放在前面。索引的选择性是指不重复的索引值和数据表的记录总数之比，索引的选择性越高则查询效率越高，唯一索引的选择性是1，因此也可以使用唯一索引提升查询效率。
删除无用索引

MySQL允许在相同的列上创建多个索引，重复的索引需要单独维护，并且优化器在优化查询时也需要逐个考虑，影响性能。

重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。比如创建了(A,B)再创建(A)就是冗余索引。

索引失效情况

索引列出现隐式类型转换，常见如SQL的where条件中字段类型为字符串，值为数值，没加引号那么MySQL不会使用索引
where条件里含有or，除非or之前使用了索引列而or之后是非索引列，索引会失效
以通配符开头的like查询，存储引擎无法做比较，MySQL只能提取数据行而不是索引值做比较。最左匹配的like比较会被转换为简单的比较操作
多个范围条件查询，MySQL无法使用第一个范围列后边的其他索引列，对于多个等值查询则没有这种限制
如果MySQL判断全表扫描比使用索引查询更快，则不会使用索引，比如小表
索引文件具有B+树的最左匹配特性，左边的未确定，无法使用索引

如何定位低效的SQL

通过慢查询日志定位哪些已经执行完毕的SQL语句
通过show processlist命令查看当前MySQL正在进行的线程，包括线程的状态、是否锁表等，实时查看SQL执行情况

找到执行效率低的SQL语句后，就可以通过explain或trace、show profile等来继续优化语句

show profile的作用

通过show profile可以分析SQL语句性能消耗，例如查询SQL会执行多长时间，并显示CPU、内存使用量，执行过程中系统锁及表锁的花费时间等信息。

例如 SHOW PROFILE CPU/MEMORY/BLOCK IO FOR QUERY Query_ID 分别查询 id 为 Query_ID（show profiles列表中的Query_ID）的 SQL 语句的 CPU、内存以及 IO 的消耗情况。

trace的作用

从 MySQL5.6 开始，可以通过 trace 文件进一步获取优化器是是如何选择执行计划的，在使用时需要先打开设置，然后执行一次 SQL，最后查看 information_schema.optimizer_trace 表而都内容，该表为联合i表，只能在当前会话进行查询，每次查询后返回的都是最近一次执行的 SQL 语句。

EXPLAIN 的字段

执行计划是 SQL 调优的一个重要依据，可以通过 EXPLAIN 命令查看 SQL 语句的执行计划，如果作用在表上，那么该命令相当于 DESC。EXPLAIN 的指标及含义如下：

指标名	含义
id	表示 SELECT 子句或操作表的顺序，执行顺序从大到小执行，当 id 一样时，执行顺序从上往下。
select_type	表示查询中每个 SELECT 子句的类型，例如 SIMPLE 表示不包含子查询、表连接或其他复杂语法的简单查询，PRIMARY 表示复杂查询的最外层查询，SUBQUERY 表示在 SELECT 或 WHERE 列表中包含了子查询。
type	表示访问类型，性能由差到好为：`ALL` 全表扫描、`index` 索引全扫描、`range` 索引范围扫描、`ref` 返回匹配某个单独值得所有行，常见于使用非唯一索引或唯一索引的非唯一前缀进行的查找，也经常出现在 join 操作中、`eq_ref` 唯一性索引扫描，对于每个索引键只有一条记录与之匹配、`const` 当 MySQL 对查询某部分进行优化，并转为一个常量时，使用这些访问类型，例如将主键或唯一索引置于 WHERE 列表就能将该查询转为一个 const、`system` 表中只有一行数据或空表，只能用于 MyISAM 和 Memory 表、`NULL` 执行时不用访问表或索引就能得到结果。SQL 性能优化的目标：至少要达到 range 级别，要求是 ref 级别，如果可以是consts 最好。
possible_keys	表示查询时可能用到的索引，但不一定使用。列出大量可能索引时意味着备选索引数量太多了。
key	显示 MySQL 在查询时实际使用的索引，如果没有使用则显示为 NULL。
key_len	表示使用到索引字段的长度，可通过该列计算查询中使用的索引的长度，对于确认索引有效性以及多列索引中用到的列数目很重要。
ref	表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows	表示 MySQL 根据表统计信息及索引选用情况，估算找到所需记录所需要读取的行数。
Extra	表示额外信息，例如 `Using temporary` 表示需要使用临时表存储结果集，常见于排序和分组查询。`Using filesort` 表示无法利用索引完成的文件排序，这是 ORDER BY 的结果，可以通过合适的索引改进性能。`Using index` 表示只需要使用索引就可以满足查询表得要求，说明表正在使用覆盖索引。

SQL优化策略

优化count查询

（1）count(*)---包括所有列，返回表中的记录数，相当于统计表的行数，在统计结果的时候，不会忽略列值为NULL的记录。

（2）count(1)---忽略所有列，1表示一个固定值，也可以用count(2)、count(3)代替，在统计结果的时候，不会忽略列值为NULL的记录。

（3）count(列名)---只包括列名指定列，返回指定列的记录数，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

（4）count(distinct 列名)---只包括列名指定列，返回指定列的不同值的记录数，在统计结果的时候，在统计结果的时候，会忽略列值为NULL的记录（不包括空字符串和0），即列值为NULL的记录不统计在内。

count(字段)<count(主键id)<count(1)≈count(\*)
优化关联查询

确保on或using子句中的列上有索引，在创建索引时就要考虑关联的顺序

确保任何group by 和order by的表达式只涉及一个表中的列，这样才有可能使用索引来优化这个过程
优化group by

如果没有通过order by子句显示指定要排序的列，当使用group by时，结果会自动按照分组的字段进行排序，如果不关心结果集的顺序，可以使用order by null禁止排序
优化limit分页

在偏移量非常大的时候，需要查询很多条数据再舍弃，代价非常高。limit offset,n语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。

要优化这种查询，要么在页面中限制分页的数量。要么优化大偏移量的性能。最简单的办法就是尽可能地使用覆盖索引扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列。

或者关联到一个冗余表，冗余表只包含主键列和需要做排序的数据列。
优化union查询

MySQL 通过创建并填充临时表的方式来执行 UNION 查询，除非确实需要服务器消除重复的行，否则一定要使用 UNION ALL，如果没有 ALL 关键字，MySQL 会给临时表加上 DISTINCT 选项，这会导致对整个临时表的数据做唯一性检查，这样做的代价非常高。
优化insert

需要对一张表插入很多行数据时，应尽量使用一次插入多个值的insert语句，这种方式将缩减客户端与数据库之间的连接、关闭等消耗，效率比多条插入单个值的insert语句高。也可以关闭事务的自动提交，在插入完数据后提交。当插入的数据是按主键的顺序插入时，效率更高。

MySQL架构

主从复制

作用

复制解决的基本问题是让一台服务器的数据与其他服务器保持同步，一台主库的数据可以同步到多台备库上，备库本身也可以被配置成另外一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

MySQL支持两种复制方式：

基于语句的复制。也称为逻辑复制，从MySQL3.23版本已存在
基于行的复制。MySQL5.1版本才被加进来

这两种方式都是通过在主库上记录二进制日志、在备库重放日志的方式来实现异步的数据复制。因此同一时刻备库的数据可能与主库存在不一致，并且无法保证主备之间的延迟。

MySQL复制大部分是向后兼容的，新版本的服务器可以作为老版本服务器的备库，但是老版本不能作为新版本服务器的备库，因为它可能无法解析新版本所用的新特性或语法，另外所使用的二进制文件格式也可能不同。

复制解决的问题：

数据分布、负载均衡、备份、高可用和故障切换、MySQL升级测试

主从复制步骤

在主库上把数据更改记录到二进制日志中
备库将主库的日志复制到自己的中继日志中
备库读取中继日志中的事件，将其重放到备库数据之上

第一步是在主库上记录二进制日志，每次准备提交事务完成数据更新前，主库将数据更新的事件记录到二进制日志中。MySQL会按事务提交的顺序而非每条语句的执行顺序来记录二进制日志，在记录二进制日志后，主库会告诉存储引擎可以提交事务了。

下一步，备库将主库的二进制日志复制到其本地的中继日志中。备库首先会启动一个工作的IO线程，IO线程跟主库建立一个普通的客户端连接，然后在主库上启动一个特殊的二进制转储线程，这个线程会读取主库上的二进制日志中的事件。它不会对事件进行轮询，如果该线程追赶上主库将进入睡眠状态，直到主库发送信号量通知其有新的事件产生时才会被唤醒，备库IO线程会将接收到的事件记录到中继日志中。

备库的SQL线程执行最后一步，该线程从中继日志中读取事件并在备库执行，从而实现备库数据的更新。当SQL线程追赶上IO线程时，中继日志通常已经在系统缓存中，所以中继日志的开销很低。SQL线程执行的时间也可以通过配置选项来决定是否写入其自己的二进制日志中。

读写分离

分库分表

mybatis

mybatis优缺点

优点：

相比JDBC减少了大量代码量，减少冗余代码
使用灵活，SQL语句写在XML里，从程序代码中彻底分离，降低了耦合度，便于管理
提供XML标签，支持编写动态SQL语句
提供映射标签，支持对象与数据库的ORM字段映射关系

缺点：

SQL语句编写工作量较大，尤其是字段和关联表多时

SQL语句依赖于数据库，导致数据库移植性差，不能随意更换数据库

mybatis的xml文件标签属性

select、insert、update、delete 标签分别对应查询、添加、更新、删除操作。

其中<sql>为 sql 片段标签，通过<include>标签引入 sql 片段，<selectKey>为不支持自增的主键生成策略标签。

parameterType 属性表示参数的数据类型，包括基本数据类型和对应的包装类型、String 和 Java Bean 类型，当有多个参数时可以使用 #{argn} 的形式表示第 n 个参数。除了基本数据类型都要以全限定类名的形式指定参数类型。

resultType 表示返回的结果类型，包括基本数据类型和对应的包装类型、String 和 Java Bean 类型。还可以使用把返回结果封装为复杂类型的 resultMap 。

resultType和resultMap

MyBatis中在查询进行select映射的时候，返回类型可以用resultType，也可以用resultMap，resultType是直接表示返回类型的，而resultMap则是对外部ResultMap的引用，但是resultType跟resultMap不能同时存在。

在MyBatis进行查询映射时，其实查询出来的每一个属性都是放在一个对应的Map里面的，其中键是属性名，值则是其对应的值。

①当提供的返回类型属性是resultType时，MyBatis会将Map里面的键值对取出赋给resultType所指定的对象对应的属性。所以其实MyBatis的每一个查询映射的返回类型都是ResultMap，只是当提供的返回类型属性是resultType的时候，MyBatis对自动的给把对应的值赋给resultType所指定对象的属性。

②当提供的返回类型是resultMap时，因为Map不能很好表示领域模型，就需要自己再进一步的把它转化为对应的对象，这常常在复杂查询中很有作用。

异同

相同点：resultType和resultMap都是映射结果集到Javabean用的

不同点：

resultType属于自动映射到javabean，而resultMap是手动映射到Javabean的，其中简单的映射关系可以使用resultType，复杂映射关系的推荐使用resultMap。
使用resultMap需要先在mapper.xml中定义resultMap。而resultType则无需定义。

resultMap是Mybatis最强大的元素，它可以将查询到的复杂数据（比如查询到几个表中数据）映射到一个结果集当中。

resultMap包含的元素：

<!--column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
<resultMap id="唯一的标识" type="映射的pojo对象">
  <id column="表的主键字段，或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
  <result column="表的一个字段（可以为任意表的一个字段）" jdbcType="字段类型" property="映射到pojo对象的一个属性（须为type定义的pojo对象中的一个属性）"/>
  <!-- 级联查询，一对一关系 -->
  <association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
    <id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的主席属性"/>
    <result  column="任意表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
  </association>
  <!-- 级联查询，一对多关系 -->
  <!-- 集合中的property须为oftype定义的pojo对象的属性-->
  <collection property="pojo的集合属性" ofType="集合中的pojo对象">
    <id column="集合中pojo对象对应的表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
    <result column="可以为任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />  
  </collection>
</resultMap>

mybatis缓存

一级缓存

一级缓存是SqlSession级别，默认开启且不能关闭。

操作数据库时需要创建 SqlSession 对象，对象中有一个 HashMap 存储缓存数据，不同 SqlSession 之间缓存数据区域互不影响。

一级缓存的作用域是 SqlSession 范围的，在同一个 SqlSession 中执行两次相同的 SQL 语句时，第一次执行完毕会将结果保存在缓存中，第二次查询直接从缓存中获取。

如果 SqlSession 执行了 DML 操作（insert、update、delete），Mybatis 必须将缓存清空保证数据有效性。

二级缓存

二级缓存是Mapper级别，默认关闭。

使用二级缓存时多个 SqlSession 使用同一个 Mapper 的 SQL 语句操作数据库，得到的数据会存在二级缓存区，同样使用 HashMap 进行数据存储，相比于一级缓存，二级缓存范围更大，多个 SqlSession 可以共用二级缓存，作用域是 Mapper 的同一个 namespace，不同 SqlSession 两次执行相同的 namespace 下的 SQL 语句，参数也相等，则第一次执行成功后会将数据保存在二级缓存中，第二次可直接从二级缓存中取出数据。

要使用二级缓存，需要在全局配置文件中配置 <setting name="cacheEnabled" value="true"/> ，再在对应的映射文件中配置一个 <cache/> 标签。

Mybatis `#{}` 和 `${}` 的区别？

${} 相当于使用字符串拼接，存在 SQL 注入的风险。

#{} 相当于使用占位符，可以防止 SQL 注入，不支持使用占位符的地方就只能使用 ${} ，典型情况就是动态参数。

${}是 Properties 文件中的变量占位符，它可以用于标签属性值和 sql 内部，属于静态文本替换，比如${driver}会被静态替换为com.mysql.jdbc.Driver。
#{}是 sql 的参数占位符，MyBatis 会将 sql 中的#{}替换为?号，在 sql 执行前会使用 PreparedStatement 的参数设置方法，按序给 sql 的?号占位符设置参数值，比如 ps.setInt(0, parameterValue)，#{item.name} 的取值方式为使用反射从参数对象中获取 item 对象的 name 属性值，相当于 param.getItem().getName()。

ORM是什么

ORM即Object-Relational Mapping，表示对象关系映射，映射的不只是对象的值还有对象之间的关系，通过ORM就可以把对象映射到关系型数据库中。操作实体类就相当于操作数据库表，可以不再关注SQL语句。

JPA是什么

JPA顾名思义就是Java Persistence API的意思，是JDK 5.0注解或XML描述对象－关系表的映射关系，并将运行期的实体对象持久化到数据库中。

Spring Data JPA是在实现了JPA规范的基础上封装的一套JPA应用框架，虽然ORM框架都实现了JPA规范，但是在不同的ORM框架之间切换仍然需要编写不同的代码，而使用Spring Data JPA能够方便大家在不同的ORM框架之间进行切换而不需要更改代码。Spring Data JPA旨在统一ORM框架的访问持久层的操作，来提高开发人的效率。

Hibernate其实是JPA的一种实现，而Spring Data JPA是一个JPA数据访问抽象。也就是说Spring Data JPA不是一个实现或JPA提供的程序，它只是一个抽象层，主要用于减少为各种持久层存储实现数据访问层所需的样板代码量。但是它还是需要JPA提供实现程序，其实Spring Data JPA底层就是使用的 Hibernate实现。

标签：事务,database,存储,查询,索引,MySQL,数据
From： https://www.cnblogs.com/qzkuan/p/16872502.html

数据库