MYSQL设计规范以及索引理解和事务的原理

MYSQL表结构设计原则

• 详细的设计规范参照数据库设计规范
  #数据库三大范式

• 第一范式（1NF）
  数据库表中的所有字段值都是不可分解的原子值
  意思就是保证每个字段的数据不可再被拆分，即数据不是拼接而成的长字符串，这个规则根据实际而定
  解释：原子性 字段不可再分,否则就不是关系数据库;反向示例：
  
• 第二范式（2NF）
  首先是 1NF，另外包含两部分内容，一是表必须有一个主键；二是没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。
  有主键，非主键字段依赖主键
  解释：唯一性 一个表只说明一个事物; 即一张表只能存在一中依赖关系，不能存在多种，错误示例：
  
• 第三范式（3NF）
  首先是 2NF，另外非主键列必须直接依赖于主键，不能存在传递依赖。即不能存在：非主键列 A 依赖于非主键列 B，非主键列 B 依赖于主键的情况。
  第二范式（2NF）和第三范式（3NF）的概念很容易混淆，区分它们的关键点在于，2NF：非主键列是否完全依赖于主键，还是依赖于主键的一部分；3NF：非主键列是直接依赖于主键，还是直接依赖于非主键列。
  非主键字段不能相互依赖
  解释：每列都与主键有直接关系，不存在传递依赖。示例：
  简单来说就是建立表之间的关系

数据库命名规范

1、数据库命名规范

1. 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;

2. 命名简洁明确(长度不能超过30个字符);

3. 所有数据库对象使用小写字母；

4. 避免使用数据库的保留字 如：select、call、group；

5. 除非是备份数据库可以加0-9的自然数：user_db_20151210;

6. 数据库命名必须为项目英文名称或有意义的简写；

7. 数据库创建时必须添加默认字符集和校对规则子句。默认字符集为UTF8；

8. 命名尽量使用小写	

2、数据库表名命名规范

1. 同一个模块的表尽可能使用相同的前缀，表名称尽可能表达含义
	
2. 多个单词以下划线（_）分隔

3. 表名不能超过30个字符

4. 普通表名以t_开头，表示为table，命名规则为t_模块名（或有意义的简写）_+table_name

5. 临时表(运营、开发或数据库人员临时用作临时进行数据采集用的中间表)命名规则：加上tmp前缀和8位时间后缀（tmp_test_user_20181109）

6. 备份表（DBA备份用作保存历史数据的中间表）命名规则：加上bak前缀和8位时间后缀（bak_test_user_20181109）
		
7. 命名应使用小写

3、数据库表字段名命名规范

1. 采用26个英文字母(区分大小写)和0-9的自然数(经常不需要)加上下划线'_'组成;

2.  命名简洁明确,多个单词用下划线'_'分隔;

3. 每个表中必须有自增主键,add_time(默认系统时间)

4. 表与表之间的相关联字段名称要求尽可能的相同;

4、数据库表字段类型规范

1. 用尽量少的存储空间来存数一个字段的数据;
 	例如：能使用int就不要使用varchar、char,能用varchar(16)就不要使用varchar(256);

2. IP地址最好使用int类型;

3. 固定长度的类型最好使用char,例如：邮编;

4. 能使用tinyint就不要使用smallint,int;

5. 最好给每个字段一个默认值,最好不能为null;

5、数据库表索引规范

1. 命名简洁明确,例如：user_login表user_name字段的索引应为user_name_index唯一索引;

2. 为每个表创建一个主键索引;

3. 为每个表创建合理的索引;

4. 建立复合索引请慎重;

6、数据库中间表的命名规范

1. 建立条件：两张表是多对多的关系

2. 表字段的组成：

id	A_id	B_id
表自增id	A表的主键	B表的主键

7、数据库设计规范

数据切分、Sharding
分为垂直切分和水平切分，垂直切包括将一个表按不同字段切分成多个表、将不同表建在不同的MySQL Server中，水平切分指将同一个表的数据切分到多个表中存储

1. 表结构设计垂直切分。常见的一些场景
   a). 大字段的垂直切分。单独将 大字段建立在另外的表中，提高基础表的访问性能，原则上在性能关键的应用中应当避免数据的大字段
   b).按照使用用途垂直切分。例如企业的物料属性，即可以基本属性、销售属性、采购属性、生产制造属性、财务会计属性等用途垂直切分
   c).按照访问频率垂直切分。例如电子商务、web2.0系统中，如果用户属性设置非常多，可以将基本、使用频率的属性和不常用的属性垂直切分

2. Sharding
   Sharding指一种"shared nothing"形式的垂直切割，将切割后的部分部署在不同的服务器上。关于sharding和partition的区别可参考DBA Notes冯大辉的开源数据库 Sharding 技术 (Share Nothing)
   
   Sharding方案可以考虑：
   a). Session-based sharding，只需在创建session时确定处理节点，随后的请求都直接定向到该节点进行处理
   b). Statement-based sharding，每个语句都需要确定处理节点
   c). Transaction-based sharding，根据事务中第一条语句确定处理节点
   Sharding潜在问题：
   a). 被分割开的部分之间无法使用数据库级别的join操作（cross-shard joins，可以考虑将一些公共的、全局的表部署到每一个节点上，使用replication机制分发）
   b). 被分割开的部分之间事务处理复杂
   c). 对自增长键的管理（主要出现在混合了水平切分的Sharding情况下）

3. 水平切分
   a). 比如在线电子商务网站，订单表数据量过大，按照年度、月度水平切分
   b). Web 2.0网站注册用户、在线活跃用户过多，按照用户ID范围等方式，将相关用户以及该用户紧密关联的表做水平切分
   c). 例如论坛的置顶帖子，因为涉及到分页问题，每页都需要显示置顶贴，这种情况可以把置顶贴水平切分开来，避免取置顶帖子时从所有帖子的表中读取

   难点之一，逻辑、关联关系的复杂性阻碍水平切分。这样的场景难在如何确定切分的范围和策略，例如SAP这样的大型ERP，模块、表非常多，之间的逻辑复杂，SAP按每Client（公司、集团）将整个业务数据完全切分开，如果粒度需要再细化难度就非常大。方案一般有：a). 按主键切分；b). 维护一个主切割索引表，这种方案扩展性非常好，但是需要查找主索引表
   第二点是如何使得水平切分具备扩展性。以Web 2.0网站为例，如果按照会员ID范围进行切分，假如现在决定水平切分为5份，如果使用user_id % 5的值确定该用户属于哪个部分，这样在将来随着用户量的增长，如果以后需要再切分成为20份就会相当麻烦
   水平切分和Sharding的混合模式，理论上可以实现线性伸缩，但受限于应用程序的状况、设计以及切分、Sharding实现方案

切分和整合方案
主要2种实现思路：

1. 每个应用程序模块配置管理自己需要的一个或多个数据源，直接访问各个数据库
2. 通过中间代理层统一管理所有的数据源，后端数据库集群架构对前端应用透明

mysql索引的理解和使用

索引出现的目的

​ 一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

什么是索引？

​ 索引在MySQL中也叫做“键”或者"key"（primary key，unique key，还有一个index key），是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要，减少io次数，加速查询。（其中primary key和unique key，除了有加速查询的效果之外，还有约束的效果，primary key 不为空且唯一，unique key 唯一，而index key只有加速查询的效果，没有约束效果）
​ 索引优化应该是对查询性能优化最有效的手段了。索引能够轻易将查询性能提高好几个数量级。
​ 索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

​ 强调：一旦为表创建了索引，以后的查询最好先查索引，再根据索引定位的结果去找数据

对于添加索引时机的误解？

如果知道数据的使用，从一开始就应该在需要处添加索引。开发人员往往对数据库的使用停留在应用的层面，比如编写SQL语句、存储过程之类，他们甚至可能不知道索引的存在，或认为事后让相关DBA加上即可。DBA往往不够了解业务的数据流，而添加索引需要通过监控大量的SQL语句进而从中找到问题，这个步骤所需的时间肯定是远大于初始添加索引所需的时间，并且可能会遗漏一部分的索引。当然索引也并不是越多越好，我曾经遇到过这样一个问题：某台MySQL服务器iostat显示磁盘使用率一直处于100%，经过分析后发现是由于开发人员添加了太多的索引，在删除一些不必要的索引之后，磁盘使用率马上下降为20%。可见索引的添加也是非常有技术含量的。

索引的原理

首先申明：索引存在的目的就是提高查询效率
看书时，想看某一页通过目录就能很快查到。数据库中的索引担当的角色就是目录的作用。索引和正常数据一样都是存储在磁盘上的，他也占空间。你在没有数据的情况下先建索引或者说目录快，还是已经存在好多的数据了，然后再去建索引，哪个快，肯定是没有数据的时候快，因为如果已经有了很多数据了，你再去根据这些数据建索引，是不是要将数据全部遍历一遍，然后根据数据建立索引。你再想，索引建立好之后再添加数据快，还是没有索引的时候添加数据快，索引是用来干什么的，是用来加速查询的，那对你写入数据会有什么影响，肯定是慢一些了，因为你但凡加入一些新的数据，都需要把索引或者说书的目录重新做一个，所以索引虽然会加快查询，但是会降低写入的效率。

索引的影响

​ 1、在表中有大量数据的前提下，创建索引速度会很慢

​ 2、在索引创建完毕后，对表的查询性能会发幅度提升，但是写性能会降低

​ 本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引的数据结构

前面讲了索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，现在我们来看看索引怎么做到减少IO，加速查询的。任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生（B+树是通过二叉查找树，再由平衡二叉树，B树演化而来，等到后面讲算法的时候再将，现在这个阶段，你大概了解一下就行了，别深究~~）。

如上图，是一颗b+树，最上层是树根，中间的是树枝，最下面是叶子节点，关于b+树的定义可以参见B+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块或者叫做一个block块，这是操作系统一次IO往内存中读的内容，一个块对应四个扇区，可以看到每个磁盘块包含几个数据项（深蓝色所示，一个磁盘块里面包含多少数据，一个深蓝色的块表示一个数据，其实不是数据，后面有解释）和指针（黄色所示，看最上面一个，p1表示比上面深蓝色的那个17小的数据的位置在哪，看它指针指向的左边那个块，里面的数据都比17小，p2指向的是比17大比35小的磁盘块），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

b+树的查找过程
如图所示，如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。除了叶子节点，其他的树根啊树枝啊保存的就是数据的索引，他们是为你建立这种数据之间的关系而存在的。

b+树的性质
1.索引字段要尽量的小：
因为每个数据占磁盘空间小，每个磁盘块存储的数据量就越大，那么需要的磁盘块就越少，树的层级就越低。查询的IO次数就越少，查询的效率就越高。（简单理解）
2.索引的最左匹配特性
简单来说就是你的数据来了以后，从数据块的左边开始匹配，在匹配右边的，知道这句话就行啦~~~~，我们继续学下面的内容。当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

索引的管理

普通索引INDEX：加速查找

唯一索引：
    -主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
    -唯一索引UNIQUE:加速查找+约束（不能重复）

联合索引：
    -PRIMARY KEY(id,name):联合主键索引
    -UNIQUE(id,name):联合唯一索引
    -INDEX(id,name):联合普通索引

索引的两大数据结构hash和btree

#我们可以在创建上述索引的时候，为其指定索引类型，分两类
hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；

索引的分类和物理存储

在mysql中，索引分为两大类：聚簇索引和非聚簇索引(也叫二级索引)。
聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引不同；
聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。

1、innodb使用的是聚簇索引，将主键组织到一颗B+树中，而行数据就存储在叶子结点上。
2、若对非主键列进行条件搜索，则需要两个步骤：
第一步在辅助索引B+树中检索name，到达叶子结点获取对应的主键。
第二步使用主键在主索引B+树检索，最终到达叶子结点获得整行数据(重点是通过其他键建立辅助索引)
(个人理解除了主键索引之外的索引都是辅助索引)
3、辅助索引：与主键索引不同，主键索引叶子结点存储的数据行，而辅助索引的叶子节点中存放的是主键的键值。
一张表可以存在多个辅助索引，但是只能有一个聚簇索引，通过辅助索引来查询对应的行记录的话，需要两步，
第一步通过辅助索引查到主键，第二步通过相应的主键值在聚簇索引中查到对应的行记录，也就是两次B+树搜索；
相反通过辅助索引查询主键的话，遍历一次辅助索引就可确定主键，即所谓的索引覆盖，不用回表。

myism使用的是非聚簇索引。即myism的索引存储是索引和数据是分开存储的

InnoDB索引和文件存储在一起：.idb

MyIsm索引和文件存储分开存储：索引存储在.myi文件，数据存储在.myd文件

聚簇索引

特点

1、索引必须为唯一索引
2、叶子结点存储的是整行数据（这也是为何用主键(聚簇索引)进行查询时，查询速度比较快的原因(减少回表查询)）

新建数据表时，推荐建立自增id作为主键，同时id自增确保业务层面的无意义。确保主键ID业务上无意义很重要。因为他确保你再生成记录的主键ID，是不会被update的。如果是随机主键或者频繁更新主键的话，就会存在数据页频繁断裂，B+数不饱和的情况（原因是聚簇索引是按顺序进行排序的）。而如果设置主键是自增，那么每一次都是在聚簇索引的最后增加，当写满一页，就会自动开辟一个新页，不会有聚簇索引树分裂这一步，效率会比随机主键高很多。这也是很多简表规范要求主键自增的原因。

非聚簇索引

1、索引值必须为可不唯一
2、叶子结点处存储的索引行和主键

辅助索引的叶子结点，存储主键值，而不是整行数据，这样的好处：
1、减少存储数据，降低了辅助索引的树所占用的存储空间
2、聚簇索引列不变的情况下，非聚簇索引列改变不影响辅助索引的原本结构

正确命中索引

1 范围问题，或者说条件不明确，条件中出现这些符号或关键字：****>、>=、<、<=、!= 、between…and…、like

如果你写where id >1 and id <1000000;你会发现，随着你范围的增大，速度会越来越慢，会成倍的体现出来。


使用like的时候，通配符写在最前面，也是需要全匹配一遍，然后在比较字符串的第二个字符，最左匹配的规则，还记得吗。

2、尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少

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

4、索引列不能参与计算，保持列“干净”
5、and/or

#1、and与or的逻辑
    条件1 and 条件2:所有条件都成立才算成立，但凡要有一个条件不成立则最终结果不成立
    条件1 or 条件2:只要有一个条件成立则最终结果就成立

#2、and的工作原理
    条件：
        a = 10 and b = 'xxx' and c > 3 and d =4
    索引：
        制作联合索引(d,a,b,c)
    工作原理:  #如果是你找的话，你会怎么找，是不是从左到右一个一个的比较啊，首先你不能
    确定a这个字段是不是有索引，即便是有索引，也不一定能确保命中索引了（所谓命中索引，
    就是应用上了索引），mysql不会这么笨的，看下面mysql是怎么找的：
      索引的本质原理就是先不断的把查找范围缩小下来，然后再进行处理，对于连续多个and：mysql会按照
      联合索引，从左到右的顺序找一个区分度高的索引字段(这样便可以快速锁定很小的范围)，加速查询，
      即按照d—>a->b->c的顺序

#3、or的工作原理
    条件：
        a = 10 or b = 'xxx' or c > 3 or d =4
    索引：
        制作联合索引(d,a,b,c)
        
    工作原理:
        只要一个匹配成功就行，所以对于连续多个or：mysql会按照条件的顺序，从左到右依次判断，
        即a->b->c->d

​ 索引要加在数据区分度高的字段上

​ 经过分析，在条件为name=‘egon’ and gender=‘male’ and id>333 and email='xxx’的情况下，我们完全没必要为前三个条件的字段加索引，因为只能用上email字段的索引，前三个字段的索引反而会降低我们的查询效率
​ 6 最左前缀匹配原则（详见第八小节），非常重要的原则，对于组合索引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的顺序可以任意调整。

​ 7 其他情况

在这里插入代码片- 使用函数
    select * from tb1 where reverse(email) = 'egon';
            
- 类型不一致
    如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select * from tb1 where email = 999;
    
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
- order by
    select name from s1 order by email desc;
    当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
    select email from s1 order by email desc;
    特别的：如果对主键排序，则还是速度很快：
        select * from tb1 order by nid desc;
 
- 组合索引最左前缀
    如果组合索引为：(name,email)
    name and email       -- 命中索引
    name                 -- 命中索引
    email                -- 未命中索引 


- count(1)或count(列)代替count(*)在mysql中没有差别了

- create index xxxx  on tb(title(19)) #text类型，必须制定长度

8、其他注意事项

- 避免使用select *
- count(1)或count(列) 代替 count(*)
- 创建表时尽量时 char 代替 varchar
- 表的字段顺序固定长度的字段优先
- 组合索引代替多个单列索引（经常使用多个条件查询时）
- 尽量使用短索引
- 使用连接（JOIN）来代替子查询(Sub-Queries)
- 连表时注意条件类型需一致
- 索引散列值（重复少）不适合建索引，例：性别不适合

联合索引与覆盖索引

联合索引

1、注意建立联合索引的一个原则：索引是有个最左匹配的原则的，所以建联合索引的时候，将区分度高的放在最左边，依次排下来，范围查询的条件尽可能的往后边放。
2、联合索引的第二个好处是在第一个键相同的情况下，已经对第二个键进行了排序处理

#对于联合索引（a,b）,下述语句可以直接使用该索引，无需二次排序
select ... from table where a=xxx order by b;

#然后对于联合索引(a,b,c)来首，下列语句同样可以直接通过索引得到结果
select ... from table where a=xxx order by b;
select ... from table where a=xxx and b=xxx order by c;

#但是对于联合索引(a,b,c)，下列语句不能通过索引直接得到结果，还需要自己执行一次
#filesort操作，因为索引（a，c)并未排序
select ... from table where a=xxx order by c; 

查询优化器explain

关于explain命令相信大家并不陌生，具体用法和字段含义可以参考官网explain-output，这里需要强调rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

type
执行计划：让mysql预估执行操作(一般正确)
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
    id,email
    
由左至右，由最差到最好
a.ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
b.index：Full Index Scan，index与ALL区别为index类型只遍历索引树
c.range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
d.ref：非唯一性索引扫描，返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
e.eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
f.const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
g.NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引

    慢：
        select * from userinfo3 where name='alex'
        
        explain select * from userinfo3 where name='alex'
        type: ALL(全表扫描)
            select * from userinfo3 limit 1;
    快：
        select * from userinfo3 where email='alex'
        type: const(走索引)

联合索引的命中规则

创建林和索引
alter table E add index E(a, b, c, d)

触发联合索引是有条件的：
1、使用联合索引的全部索引键，可触发索引的使用。
例如：SELECT E.* FROM E WHERE E.a=1 AND E.b=2
	  SELECT E.* FROM E WHERE E.b=1 AND E.a=2

2、使用联合索引的前缀部分索引键，如“key_part_1 <op>常量”，可触发索引的使用。
例如：SELECT E.* FROM E WHERE E.a=1

3、使用部分索引键，但不是联合索引的前缀部分，如“key_part_2 <op>常量”，不可触发索引的使用。
例如：SELECT E.* FROM E WHERE E.b=1
	SELECT E.* FROM E WHERE E.c=1
	SELECT E.* FROM E WHERE E.d=1

4、使用联合索引的全部索引键，但索引键不是AND操作，不可触发索引的使用。
例如：SELECT E.* FROM E WHERE E.a=2 OR E.b=1

索引的命中规则：
create index sindex on test(name, sex, salary) 

1、
explain select * from test where name='张三丰' and sex=1 and salary=2 \G; 命中
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: sindex
          key: sindex
      key_len: 88
          ref: const,const,const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)
2、
explain select name,sex,salary from test where name='张三丰'  and salary=2 \G;命中
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: sindex
          key: sindex
      key_len: 78
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
3、
mysql> explain select name,sex,salary from test where name='张三丰' or salary=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: sindex
          key: sindex
      key_len: 88
          ref: NULL
         rows: 2
     filtered: 75.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
4、
mysql> explain select name,sex,salary from test where  salary=2 and name='张三丰' \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: ref
possible_keys: sindex
          key: sindex
      key_len: 78
          ref: const
         rows: 1
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
5、
mysql> explain select name,sex,salary from test where  salary=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: sindex
      key_len: 88
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
6、
mysql> explain select name,sex,salary from test where  sex=1 and salary=2 \G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: test
   partitions: NULL
         type: index
possible_keys: NULL
          key: sindex
      key_len: 88
          ref: NULL
         rows: 2
     filtered: 50.00
        Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)

判断索引命中哪一列

• 先解释explain字段

id: 每个执行的计划都有一个id，如果是一个联合查询，还会有多个id
select_type: 表示select查询类型，这条sql是simple(普通查询，既没有联合查询、子查询)类型。其他还有PRIMARY(主键查询)、UNION(UNION中后面的查询)、SUBQUERY(子查询)等。
table: 当前执行计划查询的表，如果给表起了别名，则显示别名信息。
patitions: 访问分区表消息
type: 执行计划：让mysql预估执行操作(一般正确)（从最差到最好）
    all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
possible_keys: 可能使用到的索引。可能是两个以上
key: 实际用到的索引。最终判断使用的索引
key_len: 当前使用索引的长度。通过key_len可以判断索引的命中情况
ref: 关联id等信息
rows: 查找记录所扫描的行数
filtered: 查找所需记录占总扫描记录数的比例。
extra: 额外信息

• 索引长度公式

1、所有的索引字段，如果没有设置not null，则需要加一个字节
2、定长字段，int占四个字节、date占三个字节、char(n)占n个字符
3、对于变成字段varchar(n)，则有n个字符+两个字节
4、不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占一个字节；gbk编码的，
一个字符占两个字节，utf-8编码，一个字符站三个字节。
5、索引长度char()、varchar()索引长度计算公式：
character set:(utf8mb4=4,utf8=3,gbk=2,latin1=1)*列长度+1(允许null)+2(变长列)

• 分析命中了那些列

联合索引：
create index name_age_brithday on users (name, age, pass_word);
示例一：
explain select * from users where name='ad' and pass_word='sda' and age = 10;
可以看到key_len=236.
在看联合索引长度计算
		name       		age			pass_word
		50*3+2+1		4+1			25*3+2+1 
三个索引长度相加=236.说明完全命中了索引
示例二：
explain select * from users where name='ad' and age > 10 and pass_word='sda';
key_len = 158 = name列长度+age列长度，说明命中了 name和age索引，而pass_word由于age出现
范围符号>,造成索引失效。

示例三：
explain select * from users where name='ad' and pass_word='sda' and age > 10;
结果和示例二一样。说明where子句最有位置是按照联合索引顺序调整的


那么我们在来看将age和pass_word换位置创建联合索引
示例四：
执行和示例二一样的sql发现：
key_len=236 完全命中了索引

总结：where子句的索引顺序会根据索引顺序调整，如果非最后顺序的索引列使用了> < between会使后面的
索引列索引失效

示例一：

示例二：
示例三：

示例四：

字段允许为空的索引命中

null和空值的区别

null占空间
空不占空间
mysql>  select length(NULL), length(''), length('1');
+--------------+------------+-------------+
| length(NULL) | length('') | length('1') |
+--------------+------------+-------------+
| NULL         |          0 |           1 |
+--------------+------------+-------------+
1 row in set

1、当某一列有为null值的数据时，该列的索引是否还能生效
官方文档
You can add an index on a column that can have NULL values if you are using the MyISAM, InnoDB, or MEMORY storage engine. Otherwise, you must declare an indexed column NOT NULL, and you cannot insert NULL into the column.

大意是：如果你选择了MyISAM, InnoDB, or MEMORY 这几个引擎，那么你可以为允许null值的字段加上索引，如果不是这几个引擎，那么只能为那些not null的字段加上索引，这些字段也无法保存null值。

MySQL can perform the same optimization on col_name IS NULL that it can use for col_name = constant_value. For example, MySQL can use indexes and ranges to search for NULL with IS NULL.

大意是：mysql可以使用索引来查询NULL字段。

当某一列有为null值的数据时，该列的索引依然生效

2、is null或者is not null是否能使用到索引
在有NULL值得字段上使用常用的索引，如普通索引、复合索引、全文索引等不会使索引失效。在官网查看在空间索引的情况下，说明了 索引列必须为NOT NULL。

事务的特性及其原理

事物的四个特性ACID

1、原子性（Atomicity，或称不可分割性）

定义：语句要么全部执行，要么全部不执行。也就是说如果事务中有语句执行出错，那么整个事物的已经执行的操作全部回滚到之前的状态。

实现原理：undolog

• 在说明原子性原理之前，首先介绍一下MySQL的事务日志。MySQL的日志有很多种，如二进制日志、错误日志、查询日志、慢查询日志等，此外InnoDB存储引擎还提供了两种事务日志：redo log(重做日志)和undo log(回滚日志)。其中redo log用于保证事务持久性；undo log则是事务原子性和隔离性实现的基础。
• 下面说回undo log。实现原子性的关键，是当事务回滚时能够撤销所有已经成功执行的sql语句。InnoDB实现回滚，靠的是undo log：当事务对数据库进行修改时，InnoDB会生成对应的undo log；如果事务执行失败或调用了rollback，导致事务需要回滚，便可以利用undo log中的信息将数据回滚到修改之前的样子。
• undo log属于逻辑日志，它记录的是sql执行相关的信息。当发生回滚时，InnoDB会根据undo log的内容做与之前相反的工作：对于每个insert，回滚时会执行delete；对于每个delete，回滚时会执行insert；对于每个update，回滚时会执行一个相反的update，把数据改回去。
• 以update操作为例：当事务执行update时，其生成的undo log中会包含被修改行的主键(以便知道修改了哪些行)、修改了哪些列、这些列在修改前后的值等信息，回滚时便可以使用这些信息将数据还原到update之前的状态。

2、一致性（Consistency）

定义：事务执行结束后，数据库的完整性约束没有被破坏，事务执行的前后都是合法的数据状态。
实现：可以说一致是事务追求的最终目标：原子性、持久性、隔离性都是为了保证数据库状态的一致性。除了数据库层面的保障，一致性的实现也需要应用层面进行保障。
实现一致性的措施包括：

• 保证原子性、持久性和隔离性。如果这些特性无法保证，食物一致性也无法保障
• 数据库本身提供保障，例如不允许向整形列插入字符串、字符串长度不能超过列的限制
• 应用层面进行保障，例如如果转账操作只扣除转账着的余额，而没有增加接收者的余额，无论数据库实现的多完美，也无法保证状态一致。

3、隔离性（Isolation）

定义：与原子性、持久性侧重于研究事物本省不同，隔离性是研究事务之间的相互影响。隔离性是指事物内部的操作与其他事务是隔离的，并发执行的事务之间不能相互影响。严格的隔离性对应了隔离级别中的serializable(可串行化)，但实际出于性能考虑很少使用串行化。
隔离性追求的是并发事务之间的互不干扰。只考虑简答你的读写操作分为两个方面：

• (一个事务)写操作(对另一个事务)写操作的影响：锁机制保证隔离性
• (一个事务)写操作(对另一个事务)读操作的影响：MVCC保证隔离性

锁机制
隔离性要求同一时刻只能有一个事务对数据进行写操作。锁机制原理：事务的执行需要先获得锁，期间，其他事务需要修改数据，需要等待当前事务提交或者回滚释放锁。

• 行锁与表锁
  按照粒度可划分为表锁、行锁及二者之间的锁。表锁操作数据时锁定整张表，并发性能差；行锁只锁定操作的数据，并发性好。由于加锁需要消耗资源(获得锁、检查锁、释放锁)，所以锁定数据较多情况下可节省大量资源。myisam只支持表锁，innodb支持表锁和行锁，处于性能考虑，多数情况使用行锁。

并发情况下写操作对读操作的影响的三类问题

• 脏读：当前事务中可以读到其他事务未提交的数据(在那个数据)，这种现象属于脏读。
• 不可重复读：在事务A中先后读取一个数据两次，两次读取的结果不一样，属于不可重复读。脏读和不可重复读的区别在于：前者读到的事其他事务未提交的数据，而后者读到的是已提交的数据。
• 幻读：事务A中按照某个条件先后两次查询数据库，两次查询结果的条数不同，称为幻读。与不可重复读的区别在于：幻读是数据行数变了，不可重复读是数据变了。

这里存在一个MVCC(Multiversion Concurrency Control)概念，意思是多版本并发控制

MVCC是通过保存数据在某个时间点的快照来实现并发控制的。也就是说，不管事务执行多长时间，事务内部看到的数据是不受其他事务影响的，根据事务开始的时间不同，每个事务对同一张表，同一时刻看到的数据可能是不一样的。简单来说，就是保存数据的历史版本，通过对数据行的多个版本管理来实现数据库的并发控制。这样我们就可以通过比较版本号决定数据是否显示出来，读取数据的时候不需要加锁也可以保证事务的隔离效果。可以认为MVCC是行级锁的一个变种，但是他很多情况下避免了加锁操作，因此开销更低

MVCC没有一个统一的实现标准，典型的有乐观并发控制和悲观并发控制

• 解决哪些问题

1、读写之间的阻塞的问题
 通过MVCC可以让读写互相不阻塞，即读不阻塞写，写不阻塞读，这样就可以提高事务的并发处理能力。
 提高并发的演进思路：
 	普通锁，只能串行执行
 	读写锁，可以实现读读并发
 	数据多版本并发控制，可以实现读写并发
2、降低了死锁的概率
 因为InnoDB的MVCC采用了乐观锁的方式，读取数据时并不需要加锁，对于写操作，也只锁定必要的行
3、解决一致性读的问题
一致性读也被称为快照读，当我们查询数据库在某个时间点的快照时，只能看到这个时间点之前事务提交更新的结果，而不能看到时间点之后事务提交的更新结果。

快照读和当前读
快照读是一种一致性不加锁的读，是InnoDB并发如此之高的核心原因之一。这里的
一致性是指事务读取到的数据，要么是事务开始前就存在的数据，要么是事务本身
插入或者修改的数据

• 总结

MVCC在一定程度上实现了并发读写，他只在可重复读（REPEATABLE READ）和提交读（READ COMMITTED）两个隔离级别下工作。其他两个隔离级别和MVCC不兼容。因为未提交读(READ UNCOMMITTED)总是读取最新的数据行，而不是符合当前事物版本的数据行，而可串行化(SERIALIZBLE)则所有读取的行都加锁。

4、持久性（Durability）

定义：事务一旦提交，那么就是永久的修改，别的操作和故障不会再对其有影响

实现原理：mysql数据是存储在磁盘的，但是每次读取数据从磁盘读取，效率低下。innnodb引入了缓存(Buffer pool)，它包含了部分数据页的映射，作为访问缓冲，读取数据时会先从缓存中读，如果没有则从磁盘读取到缓存；写数据时，先写到缓存，然后缓存中的修改的数据定期刷新到磁盘（即刷脏）
但是此过程回引入新的问题，如果缓存还没刷到数据库，mysql宕机了，那么就意味着这部分的数据会丢失。于是redo log被引入结局此问题。
当数据修改时，除了修改缓存中的数据，还会在redo log记录这次操作；事务提交时，会顶用fsync接口对redo log刷盘。重启数据库时会读取redo log对数据进行恢复。redo log采用的是预写式日志WAL(write-ahead logging)，所有修改先写入日志，在更新到缓存，保证怎不回丢失数据。
既然redo log也是写入磁盘，那么为什么会比从缓存写入数据库更快的原因是：
1、刷脏是随机IO，每次修改数据位置随机，而redo log是追加操作，属于顺序IO
2、刷脏是以数据页为单位的，mysql默认每夜16kb，每个小的改动都要整页写入；而redo log只包含真正修改的地方，减少了无效IO

redo log和binlog的区别
二进制日志也可以记录写操作并用于数据的恢复，但是两个日志有着根本的不同
1、作用不同：redo log用户crash recovery的，保证mysql宕机也不会影响持久性；binlog是用于point-in-time recovery的，保证服务器可以基于时间点恢复数据，此外binlog还用于主从复制
2、层次不同：redo log是innodb存储引擎实现的，而binlog是mysql服务器层实现的，同时支持innodb和其他存储引擎
3、内容不同：redo log是物理日志，基于磁盘；binlog事儿支持的，根据binlog_format参数不同，可能基于sql语句、数据本省或二者的混合
4、写入时机不同：binlog在事务提交时写入；redo log的写入世纪相对多元

• 注意：事务提交会嗲用fsync接口对redo log进行刷盘；这位默认策略，修改innodb_flush_log_at_trx_commit参数可以改变该策略，但事务的持久性无法保障
• 除了事务提交时，还有其他刷盘时机：如master thread没秒刷盘一次redo log等，这样的好处是不一定等到commit时刷盘，commit速度大大加快。