最近据传因中美关系紧张，公司买不到 oracle 的服务器。又因为新项目是基于 sef2.0 微服务开发的，租用阿里云的服务器，阿里云的服务器是 mysql 为主。学习一下 mysql 的相关知识。
最近据传因中美关系紧张，公司买不到 oracle 的服务器。又因为新项目是基于 sef2.0 微服务开发的，租用阿里云的服务器，阿里云的服务器是 mysql 为主。学习一下 mysql 的相关知识。
MySQL 是目前绝大多数互联网公司所使用的的关系型数据库。对于我们公司的社保医保业务，查询是至关重要的。查询的场景往往比更新或者写入多得多。所以如何优化查询是非常重要的。
下面看一条常见的查询语句：
SELECT COUNT(*)
FROM SI3C.PER_REG a
WHERE a.ryid=’33580202’
AND a.zcsj BETWEEN ‘2018-01-01’ and ‘2019-01-01’
一般优化的方法就是在查询的 where 的字段上添加索引（或者是主键，但是如何添加索引，索引的顺序怎么弄？一般人并不太了解（包括我的领导
MySQL 索引原理
下面我们想象一个场景，当我们想查询一个单词（LOVE）的中文意思的时候是如何操作的？（当然是在实体的英文词典里，APP 上是如何做到的也是个有趣的问题。如果从头开始查询，a,abandon,… 只有到 LOVE 的时候才能查到，那将耗费大量的时间，所有就有了目录，通过先查询 L 再查询 O 再查 V 再查 E 后定位到 LOVE，通过这样的方式来锁定这个数据。数据库也是一样的，其中的 =，>,< BETWEEN,IN,LIKE,AND,OR and so on, 都是用来模拟现实生活中的场景的。那么怎么模拟就成为了决定性的问题。我们还是用查词典来举例， 有 100 个单词，love 在第 66 个，那就可以将数据分成段 1-10,11-20・・・・那就可以直接找第六段就可以了，省去了百分之 90 的 invalid data。100m 的数据怎么办呢？数据结构的二叉树中有一个 BST（Binary Search Tree）（PS：很像 BTS 有木有！）有很快的查询性能，但是我们计算一种数据结构的复杂的时是基于每次相同的操作成本的。真实的计算机情况是，数据保存在磁盘上，每次操作是将磁盘上的部分数据放到内存中处理计算，磁盘的访问成本大概是内存访问成本的 100 thousand times，所以简单的 BST 是不行的
磁盘 I/O 和预读
磁盘读取数据，考的是机械运动，每次读取数据花费的时间可以分成：寻道时间、旋转延迟、传输时间三个部分。寻道时间指的是磁臂移动到指定磁盘所需要的时间，主流的磁盘一般在 5ms 以下；旋转延迟指的是我们经常说的磁盘转速，比如一个磁盘 7200 转，表示的就是每分钟磁盘能转 7200 次，转换成秒也就是 120 次每秒，旋转延迟就是 1/120/2=4.17ms；传输时间指的是从磁盘读取出数据或将数据写入磁盘的时间，一般都在零点几毫秒，相对于前两个，可以忽略不计。那么访问一次磁盘的时间，即一次磁盘 I/O 的时间约等于 5+4.17=9.17ms，9ms 左右，听起来还是不错的哈，但要知道一台 500-MIPS 的机器每秒可以执行 5 亿条指令，因为指令依靠的是电的性质，换句话说，执行一次 I/O 的时间可以执行 40 万条指令，数据库动辄百万级甚至千万级的数据，每次 9ms 的时间，显然是一个灾难。
考虑到磁盘 I/O 是非常高昂代价的操作，计算机系统做了一些优化，当一次 I/O 时，不光会把当前磁盘地址的数据读取到内存中，而且会把相邻的数据也读取到内存缓冲区中，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快访问到。每一次 I/O 读取的数据我们称之为一页（Page）。具体一页的数据有多大，这个跟操作系统有关，一般为 4K 或 8K，也就是我们读取一页数据的时候，实际上才发生了一次 I/O，这个理论对于索引的数据结构设计很有帮助。
索引的数据结构
上面讲了索引的基本原理，数据库的复杂性，以及操作系统的一些内容，目的就是让大家了解到，任何一种数据结构都不是凭空产生的，一定有它的背景和使用场景。那么，我们需要这些数据结构能够做什么呢？其实很简单，就是：每次查找数据的时候，把磁盘 I/O 次数限制在一个很小的数量级，最好是一个常量数量级。那么我们就想到，如果一个高度可控的多路搜索树，是否能够满足需求呢？在这样的背景下，B + 树应运而生。
详解 B + 树（其实这是个 B 树，上面的 17，35，8，12 等非叶子节点仅仅是索引）
如上图，是一棵 B + 树。B + 树的定义，童鞋可以自行百度，我们只说一些重点。图中浅蓝色的块，我们称之为一个磁盘，可以看到，每个磁盘块包含几个数据项（深蓝色）和指针（黄色）。如：磁盘块 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 + 树的查找过程
还是使用上面的 B + 树。假设，我们要查找数据项 29，那么我们首先会把磁盘块 1 由磁盘加载到内存中，此时进行了一次 I/O，在内存中用二分查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，内存计算时间由于非常短（对比于 I/O）可以忽略不计，通过磁盘块 1 的 P2 指针的磁盘地址指向磁盘块 3，由磁盘加载到内存，此时进行了第二次 I/O，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，此时进行了第三次 I/O，同时内存中计算二分查找找到 29，查询结束。这一过程，一共进行了 3 次 I/O。在真实使用场景中，三层的 B + 树可以表示上百万的数据，如果上百万的数据查询只需要三次 I/O，性能提高将会是巨大的。B + 树就是一种索引数据结构，如果没有这样的索引，每个数据项发生一次 I/O，那么成本将会大大提升。
B + 树的性质
在上面的查找例子中，我们可以分析出一些 B + 树的性质：
1，I/O 的次数取决于 B + 树的高度 H，假设当前数据表的数据为 N，每个磁盘块的数据项的数量是 M，则有：H=log (M+1) N，当数据量 N 一定的情况下，M 越大，H 越小；而 M = 磁盘块大小 / 数据项大小，磁盘块大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度也就越低。这也就是为什么每个数据项，即索引字段要尽量的小，比如 int 占 4 个字节，要比 bigint 的 8 个字节小一半。这也是为什么 B + 树要求把真实数据放在叶子节点内而不是内层节点内，一旦放到内层节点内，磁盘块的数据项会大幅度的下降，导致树层级的增高。当数据项为 1 时，B + 树会退化成线性表。
2，B + 树的数据项是复合性数据结构，比如（name，age，gender）的时候，B + 树是按照从左到右的顺序来建立搜索树的，比如当（小张，22，女）这样的数据来检索的时候，B + 树会优先比较 name 来确定下一步的搜索方向，如果 name 相同再依次比较 age 和 gender，最后得到检索的数据。但是，当（22，女）这样没有 name 的数据来的时候，B + 树就不知道下一步该查哪个节点，因为建立搜索树的时候，name 就是第一个比较因子，必须根据 name 来搜索才知道下一步去哪里查询。比如，当（小张，男）这样的数据来检索时，B + 树就可以根据 name 来指定搜索方向，但下一字段 age 缺失，所以只能把名字是 “小张” 的所有数据都找到，然后再匹配性别是 “男” 的数据了。这个是非常重要的一条性质，即索引的最左匹配特性。
索引的类型
在 MySQL 中，索引分为两大类：聚簇索引和非聚簇索引。聚簇索引是按照数据存放的物理位置为顺序的，而非聚簇索引则不同；聚簇索引能够提高多行检索的速度，而非聚簇索引则对单行的检索速度很快。
在这两大类的索引类型下，还可以将索引分成四个小类：
1，普通索引：最基本的索引，没有任何限制，是我们大多数情况下使用到的索引。
2，唯一索引：与普通索引类型，不同的是唯一索引的列值必须唯一，但允许为空值。
3，全文索引：全文索引（FULLTEXT）仅可以适用于 MyISAM 引擎的数据表；作用于 CHAR、VARCHAR、TEXT 数据类型的列。
4，组合索引：将几个列作为一条索引进行检索，使用最左匹配原则。
建立索引的原则
我们回头来看一开始提到的慢查询，当我们了解完索引原理之后，对慢查询的优化应该有一些想法，这里我们先总结一下建立索引的一些原则：
1，最左前缀匹配原则。这是非常重要、非常重要、非常重要（重要的事情说三遍）的原则，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 的顺序可以任意调整。
2，等于（=）和 in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL 的查询优化器会帮你优化成索引可以识别的模式。
3，尽量选择区分度高的列作为索引，区分度的公式是 COUNT (DISTINCT col) / COUNT (*)。表示字段不重复的比率，比率越大我们扫描的记录数就越少，唯一键的区分度是 1，而一些状态、性别字段可能在大数据面前区分度是 0。可能有人会问，这个比率有什么经验么？使用场景不同，这个值也很难确定，一般需要 JOIN 的字段我们要求在 0.1 以上，即平均 1 条扫描 10 条记录。
4，索引列不能参与计算，尽量保持列 “干净”。比如，FROM_UNIXTIME (create_time) = ‘2016-06-06’ 就不能使用索引，原因很简单，B + 树中存储的都是数据表中的字段值，但是进行检索时，需要把所有元素都应用函数才能比较，显然这样的代价太大。所以语句要写成 ： create_time = UNIX_TIMESTAMP (‘2016-06-06’)。
5，尽可能的扩展索引，不要新建立索引。比如表中已经有了 a 的索引，现在要加（a,b）的索引，那么只需要修改原来的索引即可。
6，单个多列组合索引和多个单列索引的检索查询效果不同，因为在执行 SQL 时，MySQL 只能使用一个索引，会从多个单列索引中选择一个限制最为严格的索引。
本文源自于:
https://blog.csdn.net/mysteryhaohao/article/details/51719871