-
内连接、左外连接、右外连接、全连接
-
MySQL 七大约束
- not null 不允许为空
- default 默认值
- comment 列描述
- zerofill 在数据前面自动填充0
- primary key 主键不能为空，不能重复，一张表有且只能有一个主键，可以是复合主键
- auto_increment 自增长
- unique 唯一键允许为空，但是不能重复
-
MyISAM和InnoDB区别
MyISAM是MySQL的默认数据库引擎（5.5版之前）。虽然性能极佳，而且提供了大量的特性，包括全文索引、压缩、空间函数等，但MyISAM不支持事务和行级锁，而且最大的缺陷就是崩溃后无法安全恢复。不过，5.5版本之后，MySQL引入了InnoDB（事务性数据库引擎），MySQL 5.5版本后默认的存储引擎为InnoDB。
大多数时候我们使用的都是 InnoDB 存储引擎，但是在某些情况下使用 MyISAM 也是合适的比如读密集的情况下。（如果你不介意 MyISAM 崩溃恢复问题的话）。
两者的对比：
- 是否支持行级锁 : MyISAM 只有表级锁(table-level locking)，而InnoDB 支持行级锁(row-level locking)和表级锁,默认为行级锁。
- 是否支持事务和崩溃后的安全恢复： MyISAM 强调的是性能，每次查询具有原子性,其执行速度比InnoDB类型更快，但是不提供事务支持。但是InnoDB 提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。
- 是否支持外键： MyISAM不支持，而InnoDB支持。
- 是否支持MVCC ：仅 InnoDB 支持。应对高并发事务, MVCC比单纯的加锁更高效;MVCC只在 READ COMMITTED 和 REPEATABLE READ 两个隔离级别下工作;MVCC可以使用 乐观(optimistic)锁 和 悲观(pessimistic)锁来实现;各数据库中MVCC实现并不统一。推荐阅读：MySQL-InnoDB-MVCC多版本并发控制
- ……
《MySQL高性能》上面有一句话这样写到:
不要轻易相信“MyISAM比InnoDB快”之类的经验之谈，这个结论往往不是绝对的。在很多我们已知场景中，InnoDB的速度都可以让MyISAM望尘莫及，尤其是用到了聚簇索引，或者需要访问的数据都可以放入内存的应用。
一般情况下我们选择 InnoDB 都是没有问题的，但是某些情况下你并不在乎可扩展能力和并发能力，也不需要事务支持，也不在乎崩溃后的安全恢复问题的话，选择MyISAM也是一个不错的选择。但是一般情况下，我们都是需要考虑到这些问题的。
-
字符集及校对规则
字符集指的是一种从二进制编码到某类字符符号的映射。校对规则则是指某种字符集下的排序规则。MySQL中每一种字符集都会对应一系列的校对规则。
MySQL采用的是类似继承的方式指定字符集的默认值，每个数据库以及每张数据表都有自己的默认值，他们逐层继承。比如：某个库中所有表的默认字符集将是该数据库所指定的字符集（这些表在没有指定字符集的情况下，才会采用默认字符集）
-
索引
MySQL索引使用的数据结构主要有BTree索引 和 哈希索引 。对于哈希索引来说，底层的数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，可以选择哈希索引，查询性能最快；其余大部分场景，建议选择BTree索引。
MySQL的BTree索引使用的是B树中的B+Tree，但对于主要的两种存储引擎的实现方式是不同的。
-
MyISAM: B+Tree叶节点的data域存放的是数据记录的地址。在索引检索的时候，首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其 data 域的值，然后以 data 域的值为地址读取相应的数据记录。这被称为“非聚簇索引”。
-
InnoDB: 其数据文件本身就是索引文件。相比MyISAM，索引文件和数据文件是分离的，其表数据文件本身就是按B+Tree组织的一个索引结构，树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引（或聚集索引）”。而其余的索引都作为辅助索引，辅助索引的data域存储相应记录主键的值而不是地址，这也是和MyISAM不同的地方。在根据主索引搜索时，直接找到key所在的节点即可取出数据；在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。 因此，在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
https://www.kancloud.cn/kancloud/theory-of-mysql-index/41852
-
-
查询缓存的使用
执行查询语句的时候，会先查询缓存。不过，MySQL 8.0 版本后移除，因为这个功能不太实用。
my.cnf加入以下配置，重启MySQL开启查询缓存
1 2
query_cache_type=1 query_cache_size=600000
MySQL执行以下命令也可以开启查询缓存
1 2
set global query_cache_type=1; set global query_cache_size=600000;
如上，开启查询缓存后在同样的查询条件以及数据情况下，会直接在缓存中返回结果。 这里的查询条件包括查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息。因此任何两个查询在任何字符上的不同都会导致缓存不命中。此外，如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、MySQL库中的系统表，其查询结果也不会被缓存。
缓存建立之后，MySQL的查询缓存系统会跟踪查询中涉及的每张表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。
缓存虽然能够提升数据库的查询性能，但是缓存同时也带来了额外的开销，每次查询后都要做一次缓存操作，失效后还要销毁。 因此，开启缓存查询要谨慎，尤其对于写密集的应用来说更是如此。如果开启，要注意合理控制缓存空间大小，一般来说其大小设置为几十MB比较合适。此外，还可以通过sql_cache和sql_no_cache来控制某个查询语句是否需要缓存：
1
select sql_no_cache count(*) from usr;
-
什么是事务?
事务是逻辑上的一组操作，要么都执行，要么都不执行。
事务最经典也经常被拿出来说例子就是转账了。假如小明要给小红转账1000元，这个转账会涉及到两个关键操作就是：将小明的余额减少1000元，将小红的余额增加1000元。万一在这两个操作之间突然出现错误比如银行系统崩溃，导致小明余额减少而小红的余额没有增加，这样就不对了。事务就是保证这两个关键操作要么都成功，要么都要失败。
事务的四大特性(ACID)
- 原子性（Atomicity）： 事务是最小的执行单位，不允许分割。事务的原子性确保动作要么全部完成，要么完全不起作用；
- 一致性（Consistency）： 执行事务前后，数据保持一致，多个事务对同一个数据读取的结果是相同的；
- 隔离性（Isolation）： 并发访问数据库时，一个用户的事务不被其他事务所干扰，各并发事务之间数据库是独立的；
- 持久性（Durability）： 一个事务被提交之后。它对数据库中数据的改变是持久的，即使数据库发生故障也不应该对其有任何影响。
一致性（Consistency）
一致性是指事务使得系统从一个一致的状态转换到另一个一致状态。事务的一致性决定了一个系统设计和实现的复杂度。事务可以不同程度的一致性：
-
强一致性：读操作可以立即读到提交的更新操作。
-
弱一致性：提交的更新操作，不一定立即会被读操作读到，此种情况会存在一个不一致窗口，指的是读操作可以读到最新值的一段时间。
-
最终一致性：是弱一致性的特例。事务更新一份数据，最终一致性保证在没有其他事务更新同样的值的话，最终所有的事务都会读到之前事务更新的最新值。如果没有错误发生，不一致窗口的大小依赖于：通信延迟，系统负载等。
数据库一致性
ACID里的AID都是数据库的特征,也就是依赖数据库的具体实现.而唯独这个C,实际上它依赖于应用层,也就是依赖于开发者.这里的一致性是指系统从一个正确的状态,迁移到另一个正确的状态.什么叫正确的状态呢?就是当前的状态满足预定的约束就叫做正确的状态.而事务具备ACID里C的特性是说通过事务的AID来保证我们的一致性.
例子：A要向B支付100元,而A的账户中只有90元,并且我们给定账户余额这一列的约束是,不能小于0.那么很明显这条事务执行会失败,因为90-100=-10,小于我们给定的约束了.
-
并发事务带来哪些问题?
在典型的应用程序中，多个事务并发运行，经常会操作相同的数据来完成各自的任务（多个用户对同一数据进行操作）。并发虽然是必须的，但可能会导致以下的问题。
- 脏读（Dirty read）: 当一个事务正在访问数据并且对数据进行了修改，而这种修改还没有提交到数据库中，这时另外一个事务也访问了这个数据，然后使用了这个数据。因为这个数据是还没有提交的数据，那么另外一个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
- 丢失修改（Lost to modify）: 指在一个事务读取一个数据时，另外一个事务也访问了该数据，那么在第一个事务中修改了这个数据后，第二个事务也修改了这个数据。这样第一个事务内的修改结果就被丢失，因此称为丢失修改。 例如：事务1读取某表中的数据A=20，事务2也读取A=20，事务1修改A=A-1，事务2也修改A=A-1，最终结果A=19，事务1的修改被丢失。
- 不可重复读（Unrepeatableread）: 指在一个事务内多次读同一数据。在这个事务还没有结束时，另一个事务也访问该数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改导致第一个事务两次读取的数据可能不太一样。这就发生了在一个事务内两次读到的数据是不一样的情况，因此称为不可重复读。
- 幻读（Phantom read）: 幻读与不可重复读类似。它发生在一个事务（T1）读取了几行数据，接着另一个并发事务（T2）插入了一些数据时。在随后的查询中，第一个事务（T1）就会发现多了一些原本不存在的记录，就好像发生了幻觉一样，所以称为幻读。
不可重复读和幻读区别：
不可重复读的重点是修改比如多次读取一条记录发现其中某些列的值被修改，幻读的重点在于新增或者删除比如多次读取一条记录发现记录增多或减少了。
-
事务隔离级别有哪些?MySQL的默认隔离级别是?
SQL 标准定义了四个隔离级别：
- READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
- READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻止脏读，但是幻读或不可重复读仍有可能发生。
- REPEATABLE-READ(可重复读)： 对同一字段的多次读取结果都是一致的，除非数据是被本身事务自己所修改，可以阻止脏读和不可重复读，但幻读仍有可能发生。
- SERIALIZABLE(可串行化)： 最高的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执行，这样事务之间就完全不可能产生干扰，也就是说，该级别可以防止脏读、不可重复读以及幻读。
隔离级别 脏读 不可重复读 幻影读 读取未提交(READ-UNCOMMITTED) √ √ √ 读取已提交(READ-COMMITTED) × √ √ 可重复读(REPEATABLE-READ) × × √ 可串行化(SERIALIZABLE) × × × MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。
这里需要注意的是：与 SQL 标准不同的地方在于 InnoDB 存储引擎在 REPEATABLE-READ（可重读） 事务隔离级别下使用的是Next-Key Lock 锁算法，因此可以避免幻读的产生，这与其他数据库系统(如 SQL Server) 是不同的。所以说InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读） 已经可以完全保证事务的隔离性要求，即达到了 SQL标准的 SERIALIZABLE(可串行化) 隔离级别。因为隔离级别越低，事务请求的锁越少，所以大部分数据库系统的隔离级别都是 READ-COMMITTED(读取提交内容) ，但是你要知道的是InnoDB 存储引擎默认使用 REPEATABLE-READ（可重读） 并不会有任何性能损失。
InnoDB 存储引擎在 分布式事务 的情况下一般会用到 SERIALIZABLE(可串行化) 隔离级别。
-
锁机制与InnoDB锁算法
MyISAM和InnoDB存储引擎使用的锁：
- MyISAM采用表级锁(table-level locking)。
- InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁
表级锁和行级锁对比：
- 表级锁： MySQL中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单，资源消耗也比较少，加锁快，不会出现死锁。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。
- 行级锁： MySQL中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。
InnoDB存储引擎的锁的算法有三种：
- Record lock：单个行记录上的锁
- Gap lock：间隙锁，锁定一个范围，不包括记录本身
- Next-key lock：record+gap 锁定一个范围，包含记录本身
相关知识点：
- innodb对于行的查询使用next-key lock
- Next-locking keying为了解决Phantom Problem幻读问题
- 当查询的索引含有唯一属性时，将next-key lock降级为record key
- Gap锁设计的目的是为了阻止多个事务将记录插入到同一范围内，而这会导致幻读问题的产生
- 有两种方式显式关闭gap锁：（除了外键约束和唯一性检查外，其余情况仅使用record lock） A. 将事务隔离级别设置为RC B. 将参数innodb_locks_unsafe_for_binlog设置为1
-
大表优化
当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：
-
限定数据的范围
务必禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；
-
读/写分离
经典的数据库拆分方案，主库负责写，从库负责读；
-
垂直分区
根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。
简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。 如下图所示，这样来说大家应该就更容易理解了。
-
垂直拆分的优点： 可以使得列数据变小，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结构，易于维护。
-
垂直拆分的缺点： 主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应用层进行Join来解决。此外，垂直分区会让事务变得更加复杂；
-
-
水平分区
保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。 水平拆分可以支撑非常大的数据量。
水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。举个例子：我们可以将用户信息表拆分成多个用户信息表，这样就可以避免单一表数据量过大对性能造成影响。
水平拆分可以支持非常大的数据量。需要注意的一点是：分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库 。
水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java工程师修炼之道》的作者推荐 尽量不要对数据进行分片，因为拆分会带来逻辑、部署、运维的各种复杂度 ，一般的数据表在优化得当的情况下支撑千万以下的数据量是没有太大问题的。如果实在要分片，尽量选择客户端分片架构，这样可以减少一次和中间件的网络I/O。
下面补充一下数据库分片的两种常见方案：
- 客户端代理： 分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。 当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。
- 中间件代理： 在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。 我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。
-
-
解释一下什么是池化设计思想。什么是数据库连接池?为什么需要数据库连接池?
池化设计应该不是一个新名词。我们常见的如java线程池、jdbc连接池、redis连接池等就是这类设计的代表实现。这种设计会初始预设资源，解决的问题就是抵消每次获取资源的消耗，如创建线程的开销，获取远程连接的开销等。就好比你去食堂打饭，打饭的大妈会先把饭盛好几份放那里，你来了就直接拿着饭盒加菜即可，不用再临时又盛饭又打菜，效率就高了。除了初始化资源，池化设计还包括如下这些特征：池子的初始值、池子的活跃值、池子的最大值等，这些特征可以直接映射到java线程池和数据库连接池的成员属性中。
数据库连接本质就是一个 socket 的连接。数据库服务端还要维护一些缓存和用户权限信息之类的 所以占用了一些内存。我们可以把数据库连接池是看做是维护的数据库连接的缓存，以便将来需要对数据库的请求时可以重用这些连接。为每个用户打开和维护数据库连接，尤其是对动态数据库驱动的网站应用程序的请求，既昂贵又浪费资源。在连接池中，创建连接后，将其放置在池中，并再次使用它，因此不必建立新的连接。如果使用了所有连接，则会建立一个新连接并将其添加到池中。 连接池还减少了用户必须等待建立与数据库的连接的时间。
-
分库分表之后,id 主键如何处理？
因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。
生成全局 id 有下面这几种方式：
- UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
- 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
- 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
- Twitter的snowflake算法 ：Github 地址：https://github.com/twitter-archive/snowflake。
- 美团的Leaf分布式ID生成系统 ：Leaf 是美团开源的分布式ID生成器，能保证全局唯一性、趋势递增、单调递增、信息安全，里面也提到了几种分布式方案的对比，但也需要依赖关系数据库、Zookeeper等中间件。感觉还不错。美团技术团队的一篇文章：https://tech.meituan.com/2017/04/21/mt-leaf.html 。
- ……
- 数据库自增
- 数据库多主模式
- 号端模式
- 雪花模式
- Redis
-
高性能优化规范建议
索引设计规范
-
限制每张表上的索引数量,建议单张表索引不超过 5 个
索引并不是越多越好！索引可以提高效率同样可以降低效率。
索引可以增加查询效率，但同样也会降低插入和更新的效率，甚至有些情况下会降低查询效率。
因为 MySQL 优化器在选择如何优化查询时，会根据统一信息，对每一个可以用到的索引来进行评估，以生成出一个最好的执行计划，如果同时有很多个索引都可以用于查询，就会增加 MySQL 优化器生成执行计划的时间，同样会降低查询性能。
-
禁止给表中的每一列都建立单独的索引
5.6 版本之前，一个 sql 只能使用到一个表中的一个索引，5.6 以后，虽然有了合并索引的优化方式，但是还是远远没有使用一个联合索引的查询方式好。
-
每个 Innodb 表必须有个主键
Innodb 是一种索引组织表：数据的存储的逻辑顺序和索引的顺序是相同的。每个表都可以有多个索引，但是表的存储顺序只能有一种。
Innodb 是按照主键索引的顺序来组织表的
不要使用更新频繁的列作为主键，不适用多列主键（相当于联合索引）
不要使用 UUID,MD5,HASH,字符串列作为主键（无法保证数据的顺序增长）
主键建议使用自增 ID 值
-
常见索引列建议
出现在 SELECT、UPDATE、DELETE 语句的 WHERE 从句中的列
包含在 ORDER BY、GROUP BY、DISTINCT 中的字段
并不要将符合 1 和 2 中的字段的列都建立一个索引， 通常将 1、2 中的字段建立联合索引效果更好
多表 join 的关联列
-
如何选择索引列的顺序
建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。
区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）
-
避免建立冗余索引和重复索引（增加了查询优化器生成执行计划的时间）
重复索引示例：primary key(id)、index(id)、unique index(id)
冗余索引示例：index(a,b,c)、index(a,b)、index(a)
-
对于频繁的查询优先考虑使用覆盖索引
覆盖索引：就是包含了所有查询字段 (where,select,ordery by,group by 包含的字段) 的索引
覆盖索引的好处：
避免 Innodb 表进行索引的二次查询: Innodb 是以聚集索引的顺序来存储的，对于 Innodb 来说，二级索引在叶子节点中所保存的是行的主键信息，如果是用二级索引查询数据的话，在查找到相应的键值后，还要通过主键进行二次查询才能获取我们真实所需要的数据。而在覆盖索引中，二级索引的键值中可以获取所有的数据，避免了对主键的二次查询 ，减少了 IO 操作，提升了查询效率。
可以把随机 IO 变成顺序 IO 加快查询效率: 由于覆盖索引是按键值的顺序存储的，对于 IO 密集型的范围查找来说，对比随机从磁盘读取每一行的数据 IO 要少的多，因此利用覆盖索引在访问时也可以把磁盘的随机读取的 IO 转变成索引查找的顺序 IO。
-
索引 SET 规范
尽量避免使用外键约束
不建议使用外键约束（foreign key），但一定要在表与表之间的关联键上建立索引
外键可用于保证数据的参照完整性，但建议在业务端实现
外键会影响父表和子表的写操作从而降低性能
-
-
数据库索引总结
为什么要使用索引
- 通过创建唯一性索引，可以保证数据库每一行数据的唯一性。
- 可以大大加快数据的检索速度（大大减少的检索的数据量），这也是创建索引的最主要的原因。
索引有这么多优点，为什么不对表中的每一个列创建一个索引呢
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还有占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
使用索引的注意事项
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
- 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序时间。
- 对于中到大型表索引都是非常有效的，但是特大型表的话维护开销会很大，不适合建索引。
- 在经常用在连接的列上，这些列主要是一些外键，可以加快连接速度。
- 避免where子句中对字段施加函数，这会造成无法命中索引。
- 将打算加索引的列设置为NOT NULL，否则将导致引擎放弃使用索引而进行全表扫描。
- 在使用limit offset查询缓慢时，可以借助索引来提高性能。
覆盖索引介绍
-
什么是覆盖索引
如果一个索引包含（或者说覆盖）所有需要查询的字段的值，我们就称之为“覆盖索引”。我们知道在InnoDB存储引擎中，如果不是主键索引，叶子结点存储的是主键+列值。最终还是要“回表”，也就是要通过主键再查找一次，这样就会比较慢。覆盖索引就是要查询出的列和索引是对应的，不做回表操作。
-
覆盖索引使用实例
现在我创建了索引(username, age)，在查询数据的时候：
select username, age from user where username = 'Java' and age = 22，要查询出的列在叶子结点都存在，所以就不用回表。
选择索引和编写利用这些索引的查询的3个原则
-
单个访问时很慢的。如果服务器从存储中读取一个数据块知识为了获取其中一行，那么就浪费了很多工作。最好读取的块中能包含尽可能多所需要的行。使用索引可以创建位置引，用以提高效率。
-
按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要多次磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需要顺序读取数据，那么就不再需要额外的排序操作，并且GROUPBY查询也无需再做排序和将行按组进行聚合计算了。
-
索引覆盖查询是很快的。如果一个索引包含了查询需要的所有列，那么查询引擎就不需要再回表查找行。这避免了大量的单行访问，而上面的第1点已经写明单行访问时很慢的。
-
Mysql(innondb 下同) 有哪几种事务隔离级别？
读取未提交、读取已提交、可重复读、可串行化
-
不同事务隔离级别分别会加哪些锁？
读取已提交级别：读取不加锁，写入、修改、删除加行锁。 读（快照读）存在不可重复读的问题，解决方法是读取数据后，将这些数据加行锁。写（当前读）存在幻读的问题，解决方法是使用next-key锁。
可重复读级别：next-key锁=gap锁+行锁。 行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。
可串行化：读加共享锁，写加排他锁，读写互斥。并发能力非常差。
-
读取已提交 Read Committed
在RC级别中，数据的读取都是不加锁的，但是数据的写入、修改和删除是需要加锁的。
事务A 事务B begin; begin; update class_teacher set class_name=‘初三二班’ where teacher_id=1; update class_teacher set class_name=‘初三三班’ where teacher_id=1; commit; 为了防止并发过程中的修改冲突，事务A中MySQL给teacher_id=1的数据行加锁，并一直不commit（释放锁），那么事务B也就一直拿不到该行锁，wait直到超时。
这时我们要注意到，teacher_id是有索引的，如果是没有索引的class_name呢？update class_teacher set teacher_id=3 where class_name = ‘初三一班’; 那么MySQL会给整张表的所有数据行的加行锁。这里听起来有点不可思议，但是当sql运行的过程中，MySQL并不知道哪些数据行是 class_name = ‘初三一班’的（没有索引嘛），如果一个条件无法通过索引快速过滤，存储引擎层面就会将所有记录加锁后返回，再由MySQL Server层进行过滤。
但在实际使用过程当中，MySQL做了一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁 (违背了二段锁协议的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。可见即使是MySQL，为了效率也是会违反规范的。（参见《高性能MySQL》中文第三版p181）
这种情况同样适用于MySQL的默认隔离级别RR。所以对一个数据量很大的表做批量修改的时候，如果无法使用相应的索引，MySQL Server过滤数据的的时候特别慢，就会出现虽然没有修改某些行的数据，但是它们还是被锁住了的现象。
-
可重复读 Repeatable Read
这是MySQL中InnoDB默认的隔离级别。我们姑且分“读”和“写”两个模块来讲解。
读
RC（不可重读）模式下的展现
事务A 事务B begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=1; ① update class_teacher set class_name=’初三三班’ where id=1; commit; select id,class_name,teacher_id from class_teacher where teacher_id=1; ② commit; ① | id | class_name | teacher_id | | – | – | – | | 1 | 初三二班 | 1 | | 2 | 初三一班 | 1 |
② | id | class_name | teacher_id | | – | – | – | | 1 | 初三三班 | 1 | | 2 | 初三一班 | 1 |
读到了事务B修改的数据，和第一次查询的结果不一样，是不可重读的。
我们注意到，当teacher_id=1时，事务A先做了一次读取，事务B中间修改了id=1的数据，并commit之后，事务A第二次读到的数据和第一次完全相同。所以说它是可重读的。那么MySQL是怎么做到的呢？
不可重复读和幻读的区别
很多人容易搞混不可重复读和幻读，确实这两者有些相似。但不可重复读重点在于update和delete，而幻读的重点在于insert。
如果使用锁机制来实现这两种隔离级别，在可重复读中，该sql第一次读取到数据后，就将这些数据加锁，其它事务无法修改这些数据，就可以实现可重复读了。但这种方法却无法锁住insert的数据，所以当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，不能通过行锁来避免。需要Serializable隔离级别 ，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。
所以说不可重复读和幻读最大的区别，就在于如何通过锁机制来解决他们产生的问题。
写
对于读取历史数据的方式，我们叫它快照读 (snapshot read)，而读取数据库当前版本数据的方式，叫当前读 (current read)。
- 快照读：就是select
- select * from table ….;
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，处理的都是当前的数据，需要加锁。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert;
- update;
- delete;
事务的隔离级别中虽然只定义了读数据的要求，实际上这也可以说是写数据的要求。上文的“读”，实际是讲的快照读；而这里说的“写”就是当前读了。
为了解决当前读中的幻读问题，MySQL事务使用了Next-Key锁。
Next-Key锁是行锁和GAP（间隙锁）的合并，行锁上文已经介绍了，接下来说下GAP间隙锁。
行锁可以防止不同事务版本的数据修改提交时造成数据冲突的情况。但如何避免别的事务插入数据就成了问题。我们可以看看RR级别和RC级别的对比
RC级别：
事务A 事务B begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; ① update class_teacher set class_name=’初三四班’ where teacher_id=30; insert into class_teacher values (null,’初三二班’,30);commit; select id,class_name,teacher_id from class_teacher where teacher_id=30; ② ①
id class_name teacher_id 2 初三二班 30 ②
id class_name teacher_id 2 初三二班 30 10 初三二班 30 RR级别：
事务A 事务B begin; begin; select id,class_name,teacher_id from class_teacher where teacher_id=30; ① update class_teacher set class_name=’初三四班’ where teacher_id=30; insert into class_teacher values (null,’初三二班’,30);waiting…. select id,class_name,teacher_id from class_teacher where teacher_id=30; ② commit; 事务Acommit后，事务B的insert执行。 ①
id class_name teacher_id 2 初三二班 30 ②
id class_name teacher_id 2 初三二班 30 通过对比我们可以发现，在RC级别中，事务A修改了所有teacher_id=30的数据，但是当事务Binsert进新数据后，事务A发现莫名其妙多了一行teacher_id=30的数据，而且没有被之前的update语句所修改，这就是“当前读”的幻读。
RR级别中，事务A在update后加锁，事务B无法插入新数据，这样事务A在update前后读的数据保持一致，避免了幻读。这个锁，就是Gap锁。
MySQL是这么实现的：
在class_teacher这张表中，teacher_id是个索引，那么它就会维护一套B+树的数据关系，为了简化，我们用链表结构来表达（实际上是个树形结构，但原理相同）
如图所示，InnoDB使用的是聚集索引，teacher_id身为二级索引，就要维护一个索引字段和主键id的树状结构（这里用链表形式表现），并保持顺序排列。
Innodb将这段数据分成几个个区间
- (negative infinity, 5],
- (5,30],
- (30,positive infinity)；
update class_teacher set class_name=‘初三四班’ where teacher_id=30;不仅用行锁，锁住了相应的数据行；同时也在两边的区间，（5,30]和（30，positive infinity），都加入了gap锁。这样事务B就无法在这个两个区间insert进新数据。
受限于这种实现方式，Innodb很多时候会锁住不需要锁的区间。如下所示：
事务A 事务B 事务C begin; begin; begin; select id,class_name,teacher_id from class_teacher; ① update class_teacher set class_name=’初一一班’ where teacher_id=20; insert into class_teacher values (null,’初三五班’,10);waiting ….. insert into class_teacher values (null,’初三五班’,40); commit; 事务A commit之后，这条语句才插入成功 commit; commit; ①
id class_name teacher_id 1 初三一班 5 2 初三二班 30 update的teacher_id=20是在(5，30]区间，即使没有修改任何数据，Innodb也会在这个区间加gap锁，而其它区间不会影响，事务C正常插入。
如果使用的是没有索引的字段，比如update class_teacher set teacher_id=7 where class_name=‘初三八班（即使没有匹配到任何数据）’,那么会给全表加入gap锁。同时，它不能像上文中行锁一样经过MySQL Server过滤自动解除不满足条件的锁，因为没有索引，则这些字段也就没有排序，也就没有区间。除非该事务提交，否则其它事务无法插入任何数据。
行锁防止别的事务修改或删除，GAP锁防止别的事务新增，行锁和GAP锁结合形成的的Next-Key锁共同解决了RR级别在写数据时的幻读问题。
- 快照读：就是select
-
可串行化 Serializable
这个级别很简单，读加共享锁，写加排他锁，读写互斥。使用的悲观锁的理论，实现简单，数据更加安全，但是并发能力非常差。如果你的业务并发的特别少或者没有并发，同时又要求数据及时可靠的话，可以使用这种模式。
-
-
MVCC
数据库并发场景有三种，分别为：
- 读-读：不存在任何问题，也不需要并发控制
- 读-写：有线程安全问题，可能会造成事务隔离性问题，可能遇到脏读，幻读，不可重复读
- 写-写：有线程安全问题，可能会存在更新丢失问题
多版本并发控制（MVCC）是一种用来解决读-写冲突的无锁并发控制，也就是为事务分配单向增长的时间戳，为每个修改保存一个版本，版本与事务时间戳关联，读操作只读该事务开始前的数据库的快照。
- 在并发读写数据库时，可以做到在读操作时不用阻塞写操作，写操作也不用阻塞读操作，提高了数据库并发读写的性能。
- 同时还可以解决脏读，幻读，不可重复读等事务隔离问题，但不能解决更新丢失问题。
有了MVCC，这两个组合的方式就可以最大程度的提高数据库并发性能，并解决读写冲突，和写写冲突导致的问题
- MVCC + 悲观锁：MVCC解决读写冲突，悲观锁解决写写冲突
- MVCC + 乐观锁：MVCC解决读写冲突，乐观锁解决写写冲突
版本链
对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列：
- trx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。
- roll_pointer：每次对某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息。
ReadView
对于使用READ UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版本就好了，对于使用SERIALIZABLE隔离级别的事务来说，使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别的事务来说，就需要用到我们上边所说的版本链了，核心问题就是：需要判断一下版本链中的哪个版本是当前事务可见的。
ReadView中主要包含4个比较重要的内容：
- m_ids：表示在生成ReadView时当前系统中活跃的读写事务的事务id列表。
- min_trx_id：表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值。
- max_trx_id：表示生成ReadView时系统中应该分配给下一个事务的id值。
- creator_trx_id：表示生成该ReadView的事务的事务id。
有了这个ReadView，这样在访问某条记录时，只需要按照下边的步骤判断记录的某个版本是否可见：
- 如果被访问版本的trx_id属性值与ReadView中的creator_trx_id值相同，意味着当前事务在访问它自己修改过的记录，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值小于ReadView中的min_trx_id值，表明生成该版本的事务在当前事务生成ReadView前已经提交，所以该版本可以被当前事务访问。
- 如果被访问版本的trx_id属性值大于ReadView中的max_trx_id值，表明生成该版本的事务在当前事务生成ReadView后才开启，所以该版本不可以被当前事务访问。
- 如果被访问版本的trx_id属性值在ReadView的min_trx_id和max_trx_id之间，那就需要判断一下trx_id属性值是不是在m_ids列表中，如果在，说明创建ReadView时生成该版本的事务还是活跃的，该版本不可以被访问；如果不在，说明创建ReadView时生成该版本的事务已经被提交，该版本可以被访问
-
undolog, redolog, binlog
redo log 是重做日志，提供 前滚 操作；undo log 是回退日志，提供 回滚 操作。
只用 undo log 实现原子性和持久性的缺陷：
- 事务提交前需要将 Undo Log 写磁盘（提供可回滚功能，保证原子性），这会造成多次磁盘 IO（不考虑各种优化例如 SQL 解析优化等），这些 IO 算是顺序 IO；
- 事务提交后需要将数据立即更新到数据库中，这又会造成至少一次磁盘 IO，这是一次随机 IO。
如何优化？事务提交后如果能够将数据缓存一段时间，而不是立即更新到数据库，就能将一次次的随机 IO 打包变成一次 IO，可以提高性能。但是这样就会丧失事务的持久性。因此引入了另外一种机制来实现持久化，即 redo log。redo 解决的问题之一就是事务执行过程中的强制刷脏。
在事务提交前，只要将 Redo Log 持久化即可，不需要将数据持久化。当系统崩溃时，虽然数据没有持久化，但是 Redo Log 已经持久化。系统可以根据 Redo Log 的内容，将所有数据恢复到最新的状态。
redo log 通常是 物理 日志，记录的是 数据页 的物理修改，而不是某一行或某几行修改成怎样怎样，它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。undo log 用来回滚行记录到某个版本。undo log 一般是逻辑日志，根据每行记录进行记录。
Undo 记录某 数据 被修改 前 的值，可以用来在事务失败时进行 rollback；Redo 记录某 数据块 被修改 后 的值，可以用来恢复未写入 data file 的已成功事务更新的数据。
Redo Log 保证事务的持久性。Undo Log 保证事务的原子性（在 InnoDB 引擎中，还用 Undo Log 来实现 MVCC）。
redo/undo log 和 binlog
- 层次不同。redo/undo 是 innodb 引擎层维护的，而 binlog 是 mysql server 层维护的，跟采用何种引擎没有关系，记录的是所有引擎的更新操作的日志记录。
- 记录内容不同。redo/undo 记录的是 每个页/每个数据 的修改情况，属于物理日志+逻辑日志结合的方式（redo log 是物理日志，undo log 是逻辑日志）。binlog 记录的都是事务操作内容，binlog 有三种模式：Statement（基于 SQL 语句的复制）、Row（基于行的复制） 以及 Mixed（混合模式）。不管采用的是什么模式，当然格式是二进制的。
- 记录时机不同。redo/undo 在 事务执行过程中 会不断的写入，而 binlog 是在 事务最终提交前 写入的。binlog 什么时候刷新到磁盘跟参数 sync_binlog 相关。
-
快照读、当前读
快照读：select …
在rr级别下，mvcc完全解决了重复读，但并不能真正的完全避免幻读，只是在部分场景下利用历史数据规避了幻读
当前读：select … lock in share mode (共享锁), select … for update (排他锁), update, insert, delete
要完全避免，需要手动加锁将快照读调整使用next-key完全避免了幻读
-
mysql的行锁、表锁、间隙锁、意向锁分别是做什么的？
-
共享锁Shared Locks（S锁）
1、兼容性：加了S锁的记录，允许其他事务再加S锁，不允许其他事务再加X锁
2、加锁方式：select…lock in share mode
-
排他锁Exclusive Locks（X锁）
1、兼容性：加了X锁的记录，不允许其他事务再加S锁或者X锁
2、加锁方式：select…for update
-
表锁：意向锁 Intention Locks，意向锁相互兼容
1、表明“某个事务正在某些行持有了锁、或该事务准备去持有锁”
2、意向锁的存在是为了协调行锁和表锁的关系，支持多粒度（表锁与行锁）的锁并存。
3、例子：事务A修改user表的记录r，会给记录r上一把行级的排他锁（X），同时会给user表上一把意向排他锁（IX），这时事务B要给user表上一个表级的排他锁就会被阻塞。意向锁通过这种方式实现了行锁和表锁共存且满足事务隔离性的要求。
4、
1）意向共享锁（IS锁）：事务在请求S锁前，要先获得IS锁
2）意向排他锁（IX锁）：事务在请求X锁前，要先获得IX锁
q1：为什么意向锁是表级锁呢？
当我们需要加一个排他锁时，需要根据意向锁去判断表中有没有数据行被锁定（行锁）：
（1）如果意向锁是行锁，则需要遍历每一行数据去确认；
（2）如果意向锁是表锁，则只需要判断一次即可知道有没数据行被锁定，提升性能。
q2：意向锁怎么支持表锁和行锁并存？
（1）首先明确并存的概念是指数据库同时支持表、行锁，而不是任何情况都支持一个表中同时有一个事务A持有行锁、又有一个事务B持有表锁，因为表一旦被上了一个表级的写锁，肯定不能再上一个行级的锁。
（2）如果事务A对某一行上锁，其他事务就不可能修改这一行。这与“事务B锁住整个表就能修改表中的任意一行”形成了冲突。所以，没有意向锁的时候，让行锁与表锁共存，就会带来很多问题。于是有了意向锁的出现，如q1的答案中，数据库不需要在检查每一行数据是否有锁，而是直接判断一次意向锁是否存在即可，能提升很多性能。
-
行锁：记录锁(Record Locks)
（1）记录锁, 仅仅锁住索引记录的一行，在单条索引记录上加锁。
（2）record lock锁住的永远是索引，而非记录本身，即使该表上没有任何索引，那么innodb会在后台创建一个隐藏的聚集主键索引，那么锁住的就是这个隐藏的聚集主键索引。
所以说当一条sql没有走任何索引时，那么将会在每一条聚合索引后面加X锁，这个类似于表锁，但原理上和表锁应该是完全不同的。
-
行锁：间隙锁(Gap Locks)
（1）区间锁, 仅仅锁住一个索引区间（开区间，不包括双端端点）。
（2）在索引记录之间的间隙中加锁，或者是在某一条索引记录之前或者之后加锁，并不包括该索引记录本身。比如在 1、2、3中，间隙锁的可能值有 (∞, 1)，(1, 2)，(2, ∞)。
（3）间隙锁可用于防止幻读，保证索引间的不会被插入数据
-
行锁：临键锁(Next-Key Locks)
（1）record lock + gap lock, 左开右闭区间。
（2）默认情况下，innodb使用next-key locks来锁定记录。select … for update
（3）但当查询的索引含有唯一属性的时候，Next-Key Lock 会进行优化，将其降级为Record Lock，即仅锁住索引本身，不是范围。
（4）Next-Key Lock在不同的场景中会退化:
-
-
说说什么是最左匹配？
最左前缀匹配原则
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，示例： 对列col1、列col2和列col3建一个联合索引
1
KEY test_col1_col2_col3 on test(col1,col2,col3);
联合索引 test_col1_col2_col3 实际建立了(col1)、(col1,col2)、(col,col2,col3)三个索引。
1
SELECT * FROM test WHERE col1=“1” AND clo2=“2” AND clo4=“4”
上面这个查询语句执行时会依照最左前缀匹配原则，检索时会使用索引(col1,col2)进行数据匹配。
注意
索引的字段可以是任意顺序的，如：
1 2
SELECT * FROM test WHERE col1=“1” AND clo2=“2” SELECT * FROM test WHERE col2=“2” AND clo1=“1”
这两个查询语句都会用到索引(col1,col2)，mysql创建联合索引的规则是首先会对联合合索引的最左边的，也就是第一个字段col1的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个字段col2进行排序。其实就相当于实现了类似 order by col1 col2这样一种排序规则。
有人会疑惑第二个查询语句不符合最左前缀匹配：首先可以肯定是两个查询语句都包含索引(col1,col2)中的col1、col2两个字段，只是顺序不一样，查询条件一样，最后所查询的结果肯定是一样的。既然结果是一样的，到底以何种顺序的查询方式最好呢？此时我们可以借助mysql查询优化器explain，explain会纠正sql语句该以什么样的顺序执行效率最高，最后才生成真正的执行计划。
为什么要使用联合索引
- 减少开销。建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！
- 覆盖索引。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
- 效率高。索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！
-
如何定位慢SQL
-
索引失效
-
什么时候没用
1.有or必全有索引; 2.复合索引未用左列字段; 3.like以%开头; 4.需要类型转换; 5.where中索引列有运算; 6.where中索引列使用了函数; 7.如果mysql觉得全表扫描更快时（数据少）;
-
什么时候没必要用
1.唯一性差; 2.频繁更新的字段不用（更新索引消耗）; 3.where中不用的字段; 4.索引使用<>时，效果一般;
-
-
explain
- id
- select_type: simple, [primary, subquery], [primary, derived (在from子句里)], [primary, union, union result]
- type: eq_ref, ref, range, index, all
- extra: using index, using where, using temporary
高性能MySQL P200
一般MysQL能够使用如下三种方式应用WHERE条件，从好到坏依次为：
- 在索引中使用WHERE条件来过滤不匹配的记录。
- 使用索引覆盖扫描（在Extra列中出现了Using index）来返回记录，直接从索引中过滤不需要的记录并返回命重的结果。
- 从数据表中返回数据，然后过滤不满足条件的记录（在Extra列中出现Using Where）。
-
MySQL 执行流程
https://www.cnblogs.com/liyasong/p/mysql_zhixingguocheng.html
mysql得到sql语句后，大概流程如下：
1.sql的解析器：负责解析和转发sql
2.预处理器：对解析后的sql树进行验证
3.查询优化器：得到一个执行计划
4.查询执行引擎：得到数据结果集
5.将数据放回给调用端。
-
如何优化慢查询？
-
索引优化
索引类型
- 普通索引：是最基本的索引，它没有任何限制。
- 唯一索引：与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。
- 组合索引：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的***个字段，索引才会被使用。
- 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。一般是在建表的时候同时创建主键索引
- 全文索引：主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用，而不是一般的where语句加like。它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
优化原则
- 只要列中含有NULL值，就不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
- 尽量使用短索引，如果可以，应该制定一个前缀长度
- 对于经常在where子句使用的列设置索引，这样会加快查找速度
- 对于有多个列where或者order by子句的，应该建立复合索引
- 对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引
- 尽量不要在列上进行运算（函数操作和表达式操作）
- 尽量不要使用not in和<>操作
-
SQL语句优化
优化原则
- 查询时，能不要*就不用*，尽量写全字段名
- 大部分情况连接效率远大于子查询
- 多使用explain和profile分析查询语句
- 查看慢查询日志，找出执行时间长的sql语句优化
- 多表连接时，尽量小表驱动大表，即小表 join 大表
- 在分页时使用limit
- 对于经常使用的查询，可以开启缓存
-
大表优化
数据表拆分：主要就是垂直拆分和水平拆分。
- 水平切分:将记录散列到不同的表中，各表的结构完全相同，每次从分表中查询, 提高效率。
- 垂直切分:将表中大字段单独拆分到另外一张表, 形成一对一的关系。
-
-
mysql索引为什么用的是b+ tree而不是b tree、红黑树
B-树、B+树、红黑树，都是平衡查找树，那么查询效率上讲，平均都是O(logn)。使用什么哪种数据结构，肯定是出于提高数据库的查询效率的考虑。
一、B+树做索引而不用B-树
那么Mysql如何衡量查询效率呢？– 磁盘IO次数。
一般来说索引非常大，尤其是关系性数据库这种数据量大的索引能达到亿级别，所以为了减少内存的占用，索引也会被存储在磁盘上。B-树/B+树 的特点就是每层节点数目非常多，层数很少，目的就是为了减少磁盘IO次数，但是B-树的每个节点都有data域（指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少。
-
优点一： B+树只有叶节点存放数据，其余节点用来索引，而B-树是每个索引节点都会有Data域。
-
优点二： B+树所有的Data域在叶子节点，并且所有叶子节点之间都有一个链指针。 这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。
二、B+树做索引而不用红黑树
AVL 树（平衡二叉树）和红黑树（二叉查找树）基本都是存储在内存中才会使用的数据结构。在大规模数据存储的时候，红黑树往往出现由于树的深度过大而造成磁盘IO读写过于频繁，进而导致效率低下的情况。为什么会出现这样的情况，我们知道要获取磁盘上数据，必须先通过磁盘移动臂移动到数据所在的柱面，然后找到指定盘面，接着旋转盘面找到数据所在的磁道，最后对数据进行读写。磁盘IO代价主要花费在查找所需的柱面上，树的深度过大会造成磁盘IO频繁读写。根据磁盘查找存取的次数往往由树的高度所决定，所以，只要我们通过某种较好的树结构减少树的结构尽量减少树的高度，B树可以有多个子女，从几十到上千，可以降低树的高度。
数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，在实际实现B-Tree还需要使用如下技巧：每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个node只需一次I/O。
-
-
分库分表如何选择分表键
分表键即分库/分表字段，是在水平拆分过程中用于生成拆分规则的数据表字段。
数据表拆分的首要原则，就是要尽可能找到数据表中的数据在业务逻辑上的主体，并确定大部分（或核心的）数据库操作都是围绕这个主体的数据进行，然后可使用该主体对应的字段作为分表键，进行分库分表。
业务逻辑上的主体，通常与业务的应用场景相关，下面的一些典型应用场景都有明确的业务逻辑主体，可用于分表键：
- 面向用户的互联网应用，都是围绕用户维度来做各种操作，那么业务逻辑主体就是用户，可使用用户对应的字段作为分表键；
- 侧重于卖家的电商应用，都是围绕卖家维度来进行各种操作，那么业务逻辑主体就是卖家，可使用卖家对应的字段作为分表键；
以此类推，其它类型的应用场景，大多也能找到合适的业务逻辑主体作为分表键的选择。
如果确实找不到合适的业务逻辑主体作为分表键，那么可以考虑下面的方法来选择分表键：
- 根据数据分布和访问的均衡度来考虑分表键，尽量将数据表中的数据相对均匀地分布在不同的物理分库/分表中，适用于大量分析型查询的应用场景（查询并发度大部分能维持为1）；
- 按照数字（字符串）类型与时间类型字段相结合作为分表键，进行分库和分表，适用于日志检索类的应用场景。
注意：无论选择什么拆分键，采用何种拆分策略，都要注意拆分值是否存在热点的问题，尽量规避热点数据来选择拆分键。
注意：不一定需要拿数据库主键当做分表键，也可以拿其他业务值当分表键。拿主键当分表键的好处是可以散列均衡，减少热点问题。
-
分库分表的情况下，查询时一般是如何做排序的？
Mysql分库分表方案
-
为什么要分表
当一张表的数据达到几千万时，你查询一次所花的时间会变多，如果有联合查询的话，我想有可能会死在那儿了。分表的目的就在于此，减小数据库的负担，缩短查询时间。
-
大数据量并且访问频繁的表，将其分为若干个表
比如对于某网站平台的数据库表-公司表，数据量很大，这种能预估出来的大数据量表，我们就事先分出个N个表，这个N是多少，根据实际情况而定。
某网站现在的数据量至多是5000万条，可以设计每张表容纳的数据量是500万条，也就是拆分成10张表。
那么如何判断某张表的数据是否容量已满呢?可以在程序段对于要新增数据的表，在插入前先做统计表记录数量的操作，当<500万条数据，就直接插入，当已经到达阀值，可以在程序段新创建数据库表(或者已经事先创建好)，再执行插入操作。
-
利用merge存储引擎来实现分表
如果要把已有的大数据量表分开比较痛苦，最痛苦的事就是改代码，因为程序里面的sql语句已经写好了。用merge存储引擎来实现分表, 这种方法比较适合。
数据库架构
-
简单的MySQL主从复制
MySQL的主从复制解决了数据库的读写分离，并很好的提升了读的性能，其图如下：
其主从复制的过程如下图所示：
但是，主从复制也带来其他一系列性能瓶颈问题：
- 写入无法扩展
- 写入无法缓存
- 复制延时
- 锁表率上升
- 表变大，缓存率下降
-
MySQL垂直分区
如果把业务切割得足够独立，那把不同业务的数据放到不同的数据库服务器将是一个不错的方案，而且万一其中一个业务崩溃了也不会影响其他业务的正常进行，并且也起到了负载分流的作用，大大提升了数据库的吞吐能力。经过垂直分区后的数据库架构图如下：
然而，尽管业务之间已经足够独立了，但是有些业务之间或多或少总会有点联系，如用户，基本上都会和每个业务相关联，况且这种分区方式，也不能解决单张表数据量暴涨的问题，因此为何不试试水平分割呢?
-
MySQL水平分片(Sharding)
这是一个非常好的思路，将用户按一定规则（按id哈希）分组，并把该组用户的数据存储到一个数据库分片中，即一个sharding，这样随着用户数量的增加，只要简单地配置一台服务器即可，原理图如下：
如何来确定某个用户所在的shard呢，可以建一张用户和shard对应的数据表，每次请求先从这张表找用户的shard id，再从对应shard中查询相关数据，如下图所示：
单库单表
单库单表是最常见的数据库设计，例如，有一张用户(user)表放在数据库db中，所有的用户都可以在db库中的user表中查到。
单库多表
随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。
可以通过某种方式将user进行水平的切分，产生两个表结构完全一样的user_0000,user_0001等表，user_0000 + user_0001 + …的数据刚好是一份完整的数据。
多库多表
随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。
分库分表规则
设计表的时候需要确定此表按照什么样的规则进行分库分表。例如，当有新用户时，程序得确定将此用户信息添加到哪个表中；同理，当登录的时候我们得通过用户的账号找到数据库中对应的记录，所有的这些都需要按照某一规则进行。
路由
通过分库分表规则查找到对应的表和库的过程。如分库分表的规则是user_id mod 4的方式，当用户新注册了一个账号，账号id的123,我们可以通过id mod 4的方式确定此账号应该保存到User_0003表中。当用户123登录的时候，我们通过123 mod 4后确定记录在User_0003中。
分库分表产生的问题，及注意事项
-
分库分表维度的问题
假如用户购买了商品,需要将交易记录保存取来，如果按照用户的维度分表，则每个用户的交易记录都保存在同一表中，所以很快很方便的查找到某用户的 购买情况，但是某商品被购买的情况则很有可能分布在多张表中，查找起来比较麻烦。反之，按照商品维度分表，可以很方便的查找到此商品的购买情况，但要查找 到买人的交易记录比较麻烦。
所以常见的解决方式有：
- 通过扫表的方式解决，此方法基本不可能，效率太低了。
- 记录两份数据，一份按照用户纬度分表，一份按照商品维度分表。
通过搜索引擎解决，但如果实时性要求很高，又得关系到实时搜索。
-
联合查询的问题
联合查询基本不可能，因为关联的表有可能不在同一数据库中。
-
避免跨库事务
避免在一个事务中修改db0中的表的时候同时修改db1中的表，一个是操作起来更复杂，效率也会有一定影响。
-
尽量把同一组数据放到同一DB服务器上
例如将卖家a的商品和交易信息都放到db0中，当db1挂了的时候，卖家a相关的东西可以正常使用。也就是说避免数据库中的数据依赖另一数据库中的数据。
-
-
数据库主从一致性
半同步复制
（1）系统先对DB-master进行了一个写操作，写主库
（2）等主从同步完成，写主库的请求才返回
（3）读从库，读到最新的数据（如果读请求先完成，写请求后完成，读取到的是“当时”最新的数据）
数据库中间件
（1）所有的读写都走数据库中间件，通常情况下，写请求路由到主库，读请求路由到从库
（2）记录所有路由到写库的key，在经验主从同步时间窗口内（假设是500ms），如果有读请求访问中间件，此时有可能从库还是旧数据，就把这个key上的读请求路由到主库
（3）经验主从同步时间过完后，对应key的读请求继续路由到从库
缓存记录写key法
当写请求发生的时候：
（1）将某个库上的某个key要发生写操作，记录在cache里，并设置“经验主从同步时间”的cache超时时间，例如500ms
（2）修改数据库
而读请求发生的时候：
（1）先到cache里查看，对应库的对应key有没有相关数据
（2）如果cache hit，有相关数据，说明这个key上刚发生过写操作，此时需要将请求路由到主库读最新的数据
（3）如果cache miss，说明这个key上近期没有发生过写操作，此时将请求路由到从库，继续读写分离
-
mysql 索引 大于等于 走不走索引 最左前缀
https://blog.csdn.net/weixin_36429334/article/details/53945022
-
如何设计数据库表
https://blog.csdn.net/jiyiqinlovexx/article/details/44544325
E-R图：实体、关系
一对一（外键）、一对多（多的一方加外键）、多对多（中间表，两列作为联合主键）
三大范式
-
第一范式（1NF）：要求数据库表的每一列都是不可分割的原子数据项。
-
第二范式（2NF）：在1NF的基础上，非码属性必须完全依赖于候选码（在1NF基础上消除非主属性对主码的部分函数依赖）
第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。
-
第三范式（3NF）：在2NF基础上，任何非主属性不依赖于其它非主属性（在2NF基础上消除传递依赖）
第三范式需要确保数据表中的每一列数据都和主键直接相关，而不能间接相关。
-
-
MySQL死锁
-
MySQL如何保证ACID
- 原子性：undo log
- 持久性：redo log
- 隔离性：利用锁和MVCC机制
-
MySQL三层体系结构
- 应用
- 连接层：通信协议处理理、线程处理理、账号认证、安全检查等
- SQL层：权限判断、查询解析、优化器&缓存、查询执⾏&返回
- 存储层：存储引擎、错误日志、二进制⽇日志、中继日志
-
MySQL数据库常⽤用操作
- DDL：数据定义语⾔言DDL（Data Definition Language）Create、 Alter
- 创建数据库
CREATE DATABASE 数据库名; - 删除数据库
DROP DATABASE 数据库名; - 创建表
CREATE TABLE table_name (column_name column_type); - 新增列
ALTER TABLE testalter_tbl ADD i INT; - 新增索引
ALTER TABLE tableName ADD INDEX indexName(columnName)
- 创建数据库
- DML：数据操纵语⾔言DML（Data Manipulation Language）Insert、 Update、 Delete
- 插入数据
INSERT INTO table_name ( field1, field2,...fieldN ) VALUES ( value1, value2,...valueN ); - 更新数据
UPDATE table_name SET field1=new-value1, field2=new-value2 [WHERE Clause] - 删除数据
DELETE FROM table_name [WHERE Clause]
- 插入数据
- DQL：数据查询语⾔言DQL（Data Query Language）Select
- DCL：数据控制语⾔言DCL（Data Control Language）Grant、 Commit、 Rollback
- DDL：数据定义语⾔言DDL（Data Definition Language）Create、 Alter
-
MySQL重要的数据类型
- 数值类型
- INT类型
- TINYINT 1 byte
- SMALLINT 2 byte
- MEDIUMINT 3 byte
- INT 4 byte
- BIGINT 8 byte
- 精度类型
- FLOAT 4 byte
- DOUBLE 8 byte
- DECIMAL 变⻓
- INT类型
- 字符串类型
- CHAR(N) 定长字符
- VARCHAR(N) 变长字符
- TEXT 大对象
- 字符集选择： UTF8-MB4
- 字符排序规则：
- utf8_general_ci 不区分大小写，这个你在注册用户名和邮箱的时候就要使用
- utf8_general_cs 区分⼤大⼩小写，如果用户名和邮箱用这个就会照成不良后果,A和a不一样
- 时间类型
- 推荐使用： DATETIME
- DATE：日期
- TIMESTAMP：1970-2038年
-
JSON类型
- int(11)
对于Integer类型括号中的数字称为字段的显示宽度。显示宽度并不影响可以存储在该列中的最大值。
当列设置为UNSIGNED ZEROFILL时，INT(11)才有意义，其表示的意思为如果要存储的数字少于11个字符，则这些数字将在左侧补零。
- 数值类型
