mysql 处理字段除以1000

MySQL优化

我们为什么需要优化MySQL？

性能低，执行时间长，等待时间太长，SQL语句欠佳（连接查询），索引失效，服务器参数设置不合理（缓冲区，线程数）

整体思路

数据库最常用的优化方式有：SQL语句和索引、数据库表结构、系统配置、硬件。

优化效果：SQL语句和索引 > 数据库表结构 > 系统配置 > 硬件，但成本从低到高。

硬件优化属于运维的工作，系统配置往往是由DBA进行优化。Java工程师是数据库系统的使用者，我们需要做到的是，对数据库应用层进行优化，提升数据的处理效率，这两项也是成本更低，效果更显著的优化方式。

数据库设计规范

1）配置规范

（1）MySQL 数据库默认使用 InnoDB 存储引擎。

（2）保证字符集设置统一，MySQL 数据库相关系统、数据库、表的字符集使都用 UTF8，应用程序连接、展示等可以设置字符集的地方也都统一设置为 UTF8 字符集。

注：UTF8 格式是存储不了表情类数据，需要使用 UTF8MB4，可在 MySQL 字符集里面设置。在 8.0 中已经默认为 UTF8MB4，可以根据公司的业务情况进行统一或者定制化设置。

（3）MySQL 数据库的事务隔离级别默认为 RR（Repeatable-Read），建议初始化时统一设置为 RC（Read-Committed），对于 OLTP 业务更适合。

补充：

（4）数据库中的表要合理规划，控制单表数据量，对于 MySQL 数据库来说，建议单表记录数控制在 2000W 以内，但也资料有说数据达到500万以上性能就会降低，需要分库分表。

（5）MySQL 实例下，数据库、表数量尽可能少；数据库一般不超过 50 个，每个数据库下，数据表数量一般不超过 500 个（包括分区表）。

2）建表规范

（1）InnoDB 禁止使用外键约束，可以通过程序层面保证数据的完整性，外键与级联更新适用于单机低并发，不适合分布式、高并发集 群；级联更新是强阻塞，存在数据库更新风暴的风险；外键影响数据库的插入速度。（本条来自阿里巴巴开发手册，有争议，根据具体情况考虑使用）

（2）存储精确浮点数必须使用 DECIMAL 替代 FLOAT 和 DOUBLE。

（3）整型定义中无需定义显示宽度，比如：使用 INT，而不是 INT(4)。

（4）不建议使用 ENUM 类型，可使用 TINYINT 来代替。

（5）尽可能不使用 TEXT、BLOB 类型，如果必须使用，建议将过大字段或是不常用的描述型较大字段拆分到其他表中；另外，禁止用数据库存储图片或文件。

（6）存储年时使用 YEAR(4)，不使用 YEAR(2)，从MySQL5.6.6开始，YEAR(2)类型被自动装换为YEAR(4)，YEAR(2)类型被禁用。

• 对于YEAR（4），其显示年份的范围为1901～2155；对于YEAR（2），其显示年份的范围为1970～2070。在YEAR（2）的设置下，00～69代表2000～2069年。

（7）建议字段定义为 NOT NULL。因为NULL 其实并不是空值，而是要占用空间，所以mysql在进行比较的时候，NULL 会参与字段比较，所以对效率有一部分影响。

（8）建议 DBA 提供 SQL 审核工具，建表规范性需要通过审核工具审核。

数据库设计与数据类型优化

数据类型选择

数据库操作中最为耗时的操作就是 IO 处理，大部分数据库操作 90% 以上的时间都花在了 IO 读写上面。所以尽可能减少 IO 读写量，可以在很大程度上提高数据库操作的性能，所以我们需要在满足业务需求的前提下尽可能降低数据库每条记录的存储空间大小。

总体原则：

1、选择数据类型只要遵循小而简单的原则就好，越小的数据类型通常会更快，占用更少的磁盘、内存，处理时需要的CPU周期也更少。越简单的数据类型在计算时需要更少的CPU周期，比如，整型就比字符操作代价低，因而会使用整型来存储ip地址，使用DATETIME来存储时间，而不是使用字符串。

2、通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。而且B树索引时不会存储NULL值的，所以如果索引的字段可以为NULL，索引的效率会下降很多。

具体数据类型分析

1、数字类型，以下为各数字类型的取值范围和存储空间

• 对整数类型指定宽度，比如INT(11)，这对于大在多数应用程序是没有意义的，它不限制值的范围，只规定了的交互工具 (例如命令客户端) 用来显示字符的个数，对于存储计算，INT(1)和 INT(20)是一样的
• UNSIGNED（无符号）表示不允许负值，在存储空间不变的情况下，可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。
• 用 INT UNSIGNED 存储 IPV4 地址，用INET_ATON()、INET_NTOA() 进行转换，基本上没必要使用 CHAR(15) 【占16个字节】来存储，或者使用程序转换之后存入数据库，因为 IP 地址本身就是 32 大小位数字，使用 INT存储只需要4字节，节省了存储空间，同时效率也高很多，方便操作，比如判等，hash。尤其在各种防火墙规则过滤算法中有很大作用。
• 由于需要额外的空间和计算开销，只有在需要对小数进行精确的时候才使用 DECIMAL，比如保存金融数据，因为FLOAT 和 DOUBLE 类型支持使用标准的浮点运算进行近似计算。
• 一般不要使用 DOUBLE，不仅仅只是存储长度的问题，同时还会存在精确性的问题。同样，固定精度的小数，也不建议使用 DECIMAL，建议乘以固定倍数转换成BIGINT存储。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

2、字符串

• varchar：保存了可变长度的字符串，是使用得最多的字符串类型，它能比固定类型占用更少的存储空间，因为它只占用了自已需要的空间（也就是说较短的值占用的空间更小）。它使用额外的 1-2 个字节来存储值的长度。varchar 能节约空间，所以对性能有帮助。然而，由于行的长度是可变的，它们在更新的时候可能会发生变化，这会引起额外的工作。当最大长度远大于平均长度，并且很少发生更新的时候，通常适合用 varchar。这时候碎片就不会成为问题，还有你使用复杂的字符集，如 utf-8 时，它的每个字符都可能会占用不同的存储空间。varchar 存取值时候，MySQL 不会去掉字符串末尾的空格。
• char：固定长度，char 存取值时候，MySQL 会去掉末尾的空格。Char 在存储很短的字符串或长度近似相同的字符的时候很有用。例如, char 适用于存储密码的 MD5 哈希值，它的长度总是一样的。对于经常改变的值，char 也好于 varchar，因为固定长度的行不容易产生碎片，对于很短的列，char 的效率也高于 varchar。Char(1) 字符串对于单字节字符集只会占用 1 个字节，而 varchar(1) 则会占用 2 个字节，因为有一个字节用来存储其长度。
• BLOB 和 TEXT 分别用二进制和字符形式保存大量数据。BLOB 和 TEXT 唯一的区别就是 BLOB 保存的是二进制数据，没有字符集和排序规则，TEXT 保存的是字符数据，有字符集和排序规则。尽量不要使用BLOB数据类型。

3、日期和时间类型

• DATETIME：范围是：1000-01-01 00:00:00 到 9999-12-31 23:59:59，与时区无关。使用了 8 个字节存储空间，可以使用 NOW() 变量来自动插入系统的当前时间
• TIMESTAMP：保持了自 1970 年 1 月 1 日午夜（格林尼治标准时间）以来的秒数，和 UNIX 的时间戳相同。只使用了 4 个字节存储空间。可以用 UNIX_TIMESTAMP() 函数把日期转换为 UNIX 时间戳。显示的值依赖于时区，MYSQL 服务器、操作系统及客户端连接都有时区设置。因此，保存 0 值的 TIMESTAMP 实际显示的时间是美国东部的时间 1969-12-31 19:00:00，与格林尼治标准时间（GMT）相差 5 小时。最后，TIMESTAMP 默认是 NOT NULL，这也和其它的数据类型不一样

DATETIME 类型适合用来记录数据的原始的创建时间，因为无论你怎么更改记录中其他字段的值，datetime 字段的值都不会改变，除非你手动更改它

TIMESTAMP 类型适合用来记录数据的最后修改时间，因为只要你更改了记录中其他字段的值，timestamp 字段的值都会被自动更新

数据库设计原则

范式与反范式

• 第一范式（1NF）
  即表的列的具有原子性,不可再分解，即列的信息，不能分解, 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等非原子数据项。如果实体中的某个属性有多个值时，必须拆分为不同的属性 。通俗理解即一个字段只存储一项信息。
• 第二范式（2NF）
  第二范式（2NF）是在第一范式（1NF）的基础上建立起来的，即满足第二范式（2NF）必须先满足第一范式（1NF）。第二范式（2NF）要求数据库表中的每个实例或行必须可以被惟一地区分。在满足第一范式的前提下，当存在多个主键的时候，才会发生不符合第二范式的情况。比如有两个主键，不能存在这样的属性，它只依赖于其中一个主键，这就是不符合第二范式。通俗理解是任意一个字段都只依赖表中的同一个字段。
• 第三范式（3NF）
  满足第三范式（3NF）必须先满足第二范式（2NF）。简而言之，第三范式（3NF）要求一个数据库表中不包含已在其它表中已包含的非主键字段。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放(能尽量外键join就用外键join)。很多时候，我们为了满足第三范式往往会把一张表分成多张表。

反范式，顾名思义就是与范式规定的原则背道相驰，现在常说的反范式一般是反第三范式，即允许一定数量的字段冗余，可以减少关联查询，提高查询的性能

在实际应用中，不会出现完全的范式化或完全的反范式化，时常需要 混用范式和反范式 ，使用部分范式化的schema，往往是最好的选择。

缓存表和汇总表

除了上述说到的反范式，在表中存储冗余数据，我们还可以创建一张完全独立的汇总表或缓存表，来满足检索的需要。

缓存表，指的是存储可以从schema其他表中获取数据的表，也就是逻辑上冗余的数据。而 汇总表 ，则指的是存储使用GROUP BY等语句聚合数据，计算出的不冗余的数据。

缓存表，可用于 优化搜索和检索查询语句 ，这里可以使用的技巧有对缓存表使用不同的存储引擎，例如主表使用InnoDB，而缓存表则可使用MyISAM，获得更小的索引占用空间。甚至可以将缓存表放到专门的搜索系统中，例如Lucene。

汇总表，则是为了避免实时计算统计值所带来的高昂代价 ，代价来自两方面，一是需要扫描表中的大部分数据，二是建立特定的索引，会对UPDATE操作有影响。例如，查询微信过去24小时的朋友圈数量，则可固定每1小时扫描全表，统计后写一条记录到汇总表，当查询时，只需查询汇总表上最新的24条记录，而不必每次查询时都去扫描全表进行统计。

在使用缓存表和汇总表时，必须决定是实时维护数据 还是定期重建 ，这取决于我们的需求。定期重建相比实时维护，能节省更多的资源，表的碎片更少。而在重建时，我们仍需保证数据在操作时可用，需要通过“ 影子表 ”来实现。在真实表后创建一张影子表，当填充好数据后，通过原子的重命名操作来切换影子表和原表。

加快ALTER TABLE操作的速度

当MySQL在执行ALTER TABLE操作时，往往是新建一张表，然后把数据从旧表查出并插入到新表中，再删除旧表，如果表很大，这样需要花费很长时间，且会导致MySQL的服务中断。为了避免服务中断，通常可以使用 两种技巧 ：

• 在一台不提供服务的机器上执行ALTER TABLE操作，然后再与提供服务的主库进行切换；
• “影子拷贝”，建立一张与原表无关的新表，在数据迁移完成后，通过重命名操作进行切换。

但也 不是所有的ALTER TABLE操作会引起表重建 ，例如在修改字段的默认值时，使用MODIFY COLUMN会进行表重建，而使用ALTER COLUMN则不会进行表重建，操作速度很快。这是因为ALTER COLUMN在修改默认值时，会直接修改了存在表的.frm文件（存储字段的默认值），而并未重建表。

数据库引擎选择

1、InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text （InnoDB 从 1.2.X 版本开始支持全文搜索的技术）等索引，不支持 Hash 索引，但是给了又有一个特殊的解释：InnoDB 存储引擎 是支持 hash 索引的，不过，我们必须启用，hash 索引的创建由 InnoDB 存储引擎引擎自动优化创建，是数据库自身创建并使用，DBA（数据库管理员）无法干预；

2、MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

3、Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；

4、MyISAM 引擎不支持外键，InnoDB 支持外键

5、MyISAM 引擎的表在大量高并发的读写下会经常出现表损坏的情况

6、对于 count() 查询来说 MyISAM 更有优势，MyISAM 直接通过计数器获取。InnoDB 需要通过扫描全部数据，虽然 InNoDB 存储引擎是支持行级别所，InNoDB 是行级别锁，是 where 对他主键是有效，非主键的都会锁全表的

7、MyISAM 引擎的表的查询、更新、插入的效率要比 InnoDB 高，如果你的数据量是百万级别的，并且没有任何的事务处理，那么用 MyISAM 是性能最好的选择。并且 MyISAM 可以节省很多内存，因为 MyISAM 索引文件是与数据文件分开放置，并且索引是有压缩，内存使用率提高不少

8、平台承载的大部分项目是读多写少的项目，MyISAM 读性能比 InnoDB 强很多

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-W2d9BPFM-1605314713305)(C:\Users\Alan Lin\Desktop\MySQL优化\引擎对比.png)]

索引优化

索引是一种用来实现 MySQL 高效获取数据的数据结构。

我们通常所说的在某个字段上建索引，意思就是让 MySQL 对该字段以索引这种数据结构来存储，然后查找的时候就有对应的查找算法。

建索引的根本目的是为了查找的优化，特别是当数据很庞大的时候，一般的查找算法有顺序查找、折半查找、快速查找等。

但是每种查找算法都只能应用于特定的数据结构之上，例如顺序查找依赖于顺序结构，折半查找通过二叉查找树或红黑树实现二分搜索。因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构。

这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

索引存储结构

Hash索引

哈希算法时间复杂度为O(1)，且不只存在于索引中，每个数据库应用中都存在该数据结构。

哈希表也为散列表，又直接寻址改进而来。在哈希的方式下，一个元素k处于h(k)中，即利用哈希函数h，根据关键字k计算出槽的位置。函数h将关键字域映射到哈希表T[0…m-1]的槽位上。InnoDB中采用除法散列函数，冲突机制采用链接法。

Hash索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以Hash索引的查询效率要远高于B-Tree索引。

• Hash索引仅仅能满足"=",“IN"和”<=>"查询，不能使用范围查询。哈希索引只支持等值比较查询，包括＝、 IN 、<=> (注意<>和＜＝＞是不同的操作）。 也不支持任何范围查询，例如WHERE price > 100。　　
  由于Hash索引比较的是进行Hash运算之后的Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的Hash算法处理之后的Hash值的大小关系，并不能保证和Hash运算前完全一样。
• Hash索引无法被用来避免数据的排序操作。　　
  由于Hash索引中存放的是经过Hash计算之后的Hash值，而且Hash值的大小关系并不一定和Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算;
• Hash索引不能利用部分索引键查询。　　
  对于组合索引，Hash索引在计算Hash值的时候是组合索引键合并后再一起计算Hash值，而不是单独计算Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash索引也无法被利用。
• Hash索引在任何时候都不能避免表扫描。　　
  前面已经知道，Hash索引是将索引键通过Hash运算之后，将 Hash运算结果的Hash值和所对应的行指针信息存放于一个Hash表中，由于不同索引键存在相同Hash值，所以即使取满足某个Hash键值的数据的记录条数，也无法从Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。
• Hash索引遇到大量Hash值相等的情况后性能并不一定就会比BTree索引高。　
  对于选择性比较低的索引键，如果创建Hash索引，那么将会存在大量记录指针信息存于同一个Hash值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

B-/+树索引（平衡多路查找树）

文件系统及数据库系统普遍采用B-/+Tree作为索引结构：一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。这样的话，索引查找过程中就要产生磁盘I/O消耗，相对于内存存取，I/O存取的消耗要高几个数量级，所以评价一个数据结构作为索引的优劣最重要的指标就是在查找过程中磁盘I/O操作次数的渐进复杂度。换句话说，索引的结构组织要尽量减少查找过程中磁盘I/O的存取次数。

1、局部性处理与磁盘预读：

由于存储介质的特性，磁盘本身存取就比主存慢很多，再加上机械运动耗费，磁盘的存取速度往往是主存的几百分分之一，因此为了提高效率，要尽量减少磁盘I/O。为了达到这个目的，磁盘往往不是严格按需读取，而是每次都会预读，即使只需要一个字节，磁盘也会从这个位置开始，顺序向后读取一定长度的数据放入内存。

由于磁盘顺序读取的效率很高（不需要寻道时间，只需很少的旋转时间），因此对于具有局部性的程序来说，预读可以提高I/O效率。预读的长度一般为页（page）的整倍数。页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（在许多操作系统中，页得大小通常为4k），主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后异常返回，程序继续运行。

2、B-/+Tree索引的性能分析：

鉴于上文提到的磁盘IO存取的特点，数据库系统的设计者巧妙利用了磁盘预读原理，使用页作为数据读取单位，页是其磁盘管理的最小单位，默认 page 大小是 16k。系统的一个磁盘块的存储空间往往没有这么大，因此 InnoDB 每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小 16KB。每次新建节点时，也是直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了一个节点只需一次I/O。

B-树结构分析

为了描述 B-Tree，首先定义一条数据记录为一个二元组 [key, data]，key 为记录的键值，对于不同数据记录，key 是互不相同的；data 为数据记录除 key 外的数据。

那么 B-Tree 是满足下列条件的数据结构：

1. d 为大于 1 的一个正整数，称为 B-Tree 的度。
2. h 为一个正整数，称为 B-Tree 的高度。
3. 每个非叶子节点由 n-1 个 key 和 n 个指针组成，其中 d<=n<=2d。
4. 每个叶子节点最少包含一个 key 和两个指针，最多包含 2d-1 个 key 和 2d 个指针，叶节点的指针均为 null 。
5. 所有叶节点具有相同的深度，等于树高 h。
6. key 和指针互相间隔，节点两端是指针。
7. 一个节点中的 key 从左到右非递减排列。
8. 所有节点组成树结构。
9. 每个指针要么为 null，要么指向另外一个节点。
10. 如果某个指针在节点 node 最左边且不为 null，则其指向节点的所有 key 小于 v(key1)，其中 v(key1) 为 node 的第一个 key 的值。
11. 如果某个指针在节点 node 最右边且不为 null，则其指向节点的所有 key 大于 v(keym)，其中 v(keym) 为 node 的最后一个 key 的值。
12. 如果某个指针在节点 node 的左右相邻 key 分别是 keyi 和 keyi+1 且不为 null，则其指向节点的所有 key 小于 v(keyi+1) 且大于 v(keyi)。

B+树结构分析

B+Tree 是在 B-Tree 基础上的一种优化，使其更适合实现外存储索引结构，InnoDB 存储引擎就是用 B+Tree 实现其索引结构。在 B-Tree 中，每个节点中有 key，也有 data，而每一个页的存储空间是有限的，如果 data 数据较大时将会导致每个节点（即一个页）能存储的 key 的数量很小。

B+Tree 在 B-Tree 的基础上有两点变化：

1. 数据是存在叶子节点中的；
2. 数据节点之间是有指针指向的。

B+树演示：https://www.cs.usfca.edu/~galles/visualization/BPlusTree.html

基于这种结构，在 B+Tree 中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储 key 值信息，这样可以大大加大每个节点存储的 key 值数量，降低 B+Tree 的高度，在提高数据的存储容量的同时，减少了磁盘IO的次数，提升了性能。

现在我们来计算一下，一颗高度为3的B+数，可以放多少数据：

我们假设主键ID为bigint类型，长度为8字节，而指针大小在InnoDB源码中设置为6字节，这样一共14字节，我们一个页中能存放多少这样的单元，其实就代表有多少指针，即16384/14=1170。假设一行记录的数据大小为1k（实际上现在很多互联网业务数据记录大小通常就是1K左右），那么可以算出一棵高度为3的B+树，最多能存放1170×1170×16=21902400条（20G）这样的数据记录。

所以当B+树为3层时，它就能满足千万级别的数据存储，在使用主键索引的情况下，只需要1~3次的IO查询就可以查找到想要查找的数据，在实际应用中，B+树的高度最多不会超过4层。

MySQL数据库中B+树索引的具体实现

在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，这里主要讨论MyISAM和InnoDB两个存储引擎对于B+树索引实现方式。

1、MyISAM索引的实现：

（1）主键索引：

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。下图是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据记录的地址。这与MyISAM引擎的存储方式有一定关系，MyISAM引擎在存储数据时，将表数据与索引文件分开存储。所以MyISAM的索引方式也叫做非聚簇（也称非聚集）的，之所以这么称呼是为了与InnoDB的聚簇索引（聚集索引）区分。

（2）辅助索引：

在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。

2、InnoDB索引的实现：

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却不相同。

（1）主键索引：

与MyISAM第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

（2）辅助索引：

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录，这种检索方式也被称为回表。

InnoDB 表是基于聚簇索引建立的。因此InnoDB 的索引能提供一种非常快速的主键查找性能。不过，它的辅助索引也会包含主键列，所以，如果主键使用过长的字段，将会导致其他辅助索变得更大。如果想在表上定义 、很多索引，则争取尽量把主键定义得小一些。InnoDB 不会压缩索引。

主键设计的原则：

1. 一定要显式定义主键
2. 采用与业务无关的单独列
3. 采用自增列
4. 数据类型采用int，并尽可能小，能用tinyint就不用int，能用int就不用bigint

索引类型

Mysql常见索引有：主键索引、唯一索引、普通索引、全文索引、组合索引。

• 普通索引(INDEX)：最基本的索引，没有任何限制
• 唯一索引(UNIQUE)：与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
• 主键索引(PRIMARY)：它 是一种特殊的唯一索引，不允许有空值。
• 全文索引(FULLTEXT )：仅可用于 MyISAM 表， 用于在一篇文章中，检索文本信息的, 针对较大的数据，生成全文索引很耗时好空间。
• 联合索引：为了更多的提高mysql效率可建立联合索引，遵循”最左前缀“原则，对于经常联合使用的字段，可以建立联合索引，效率会更高。

索引设计原则

建议建立索引的场景

（1）对于经常查询的字段，建议创建索引。

（2）索引不是越多越好，一个表如果有大量索引，不仅占用磁盘空间，而且会影响INSERT，DELETE，UPDATE等语句的性能。

（3）避免对经常更新的字段建立索引，因为当表中数据更改的同时，索引也会进行调整和更新，十分消耗系统资源。

（4）数据量小的表建议不要创建索引，数据量小时索引不仅起不到明显的优化效果，对于索引结构的维护反而消耗系统资源。

（5）不要在区分度低的字段建立索引。比如性别字段，只有 “男” 和 “女” ，建索引完全起不到优化效果。

（6）当唯一性是某字段本身的特征时，指定唯一索引能提高查询速度。

（7）在频繁进行跑排列分组（即进行 group by 或 order by操作）的列上建立索引，如果待排序有多个，可以在这些列上建立组合索引，区分度较高的字段排在前面

不建议建立索引的场景

• 表记录太少，记录较少的情况下，使用索引与否对于查询性能的影响几乎没有
• 经常增删改的表或者字段，不建议建立太多索引
• Where 条件里用不到的字段不创建索引
• 过滤性不好的不适合建索引，即重复值较多的字段，没有必要建立索引，比如性别，完全不需要建立索引

避免过度索引

1、因为索引列实际上也是一张表，记录了主键与索引字段，并指向了实体表的记录，所以索引列也是需要占用空间的。

2、索引虽然大大提升了查询速度，但是会降低更新表的速度，在进行insert、update、delete的操作时，需要根据数据变化更新索引列信息。

MySQL查询语句优化

MySQL查询语句的执行顺序

查询优化分析（explain）

使用EXPLAIN 关键字可以模拟优化器执行SQL 查询语句，从而知道MySQL 是如何处理你的SQL 语句的。分析你的查询语句或是表结构的性能瓶颈。

[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-bj8a2bDC-1605314713317)(C:\Users\Alan Lin\Desktop\MySQL优化\explain结果字段.png)]

1、id

select 查询的序列号,包含一组数字，表示查询中执行select 子句或操作表的顺序。

• id 相同，执行顺序由上至下

• id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行

• id有相同也有不同

id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

关注点：id 号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。

2、select_type

select_type 代表查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

select_type 属性	含义
SIMPLE	简单的select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
DERIVED	在FROM字句中包含的子查询被标记为DERIVED(衍生)，MySQL 会递归执行这些子查询, 把结果放在临时表里。
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DEPEDENT SUBQUERY	在SELECT或WHERE列表中包含了子查询，认为子查询基于外层
UNCACHEABLE SUBQUERY	无法使用缓存的子查询
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

3、type

type 是查询的访问类型。是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL ，一般来说，得保证查询至少达到range 级别，最好能达到ref。

3.1 system

表只有一行记录（等于系统表），这是const 类型的特列，平时不会出现，这个也可以忽略不计

3.2 const

表示通过索引一次就找到了,const 用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，如果将主键置于where 列表中，MySQL 就能将该查询转换为一个常量。

3.3 eq_ref

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描，用于联表查询的情况，按联表的主键或唯一键联合查询。

3.4 ref

非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。与eq_ref的区别就在于ref中的索引不是唯一值。

3.5 range

只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where 语句中出现了between、<、>、in 等的查询这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。

3.6 index

出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是仅仅利用索引进行了排序分组。即虽然使用了索引，但并没有起到过滤数据的作用。

3.7 all

这个没什么好说的，就是全表扫描了

4、possible_keys

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一
定被查询实际使用。

5、key

实际使用的索引。如果为NULL，则没有使用索引。

6、key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。key_len 字段能够帮你检查是否充分的
利用上了索引。ken_len 越长，说明索引使用的越充分。

7、ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。

8、rows

rows 列显示MySQL 认为它执行查询时必须检查的行数。越少越好！

9、Extra

其他的额外重要的信息

9.1 Using filesort

说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。

出现filesort 的情况：

filesort 是一种非常差的情况，尽可能避免这种情况出现，查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。优化后：

9.2 Using temporary

使用了临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序order by 和分组查询groupby。

优化后：

9.3 Using index

Using index 代表表示相应的select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找;如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

利用索引进行了排序或分组。

9.4 impossible where

where 子句的值总是false，不能用来获取任何元组。

索引的失效和使用优化

1、全值匹配我最爱

使用查询的字段按照顺序在索引中都可以匹配到，按照索引的个数和顺序依次作为条件进行查询（=查询），这时最理想的状态。

2、最左匹配原则

使用联合索引，需要遵循最佳左前缀法则，即如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。

• 对于联合索引，MySQL 会一直向右匹配直到遇到范围查询（> ， < ，between，like）就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6，如果建立的是（a,b,c,d）这种顺序的索引，那么 d 是用不到索引的，但是如果建立的是 （a,b,d,c）这种顺序的索引的话，那么就没问题，而且 a，b，d 的顺序可以随意调换。
• = 和 in 可以乱序，比如 a = 3 and b = 4 and c = 5 建立 （a，b，c）索引可以任意顺序。
• 如果建立的索引顺序是 （a，b）那么直接采用 where b = 5 这种查询条件是无法利用到索引的，这一条最能体现最左匹配的特性。
• order by的子句后面的顺序必须按照索引列的顺序给出

3、范围查询不当导致索引失效

条件中出现这些符号或关键字：>、>=、<、<=、!= 、between…and…、like等，如果查询范围不明确，达不到优化效果。

1）大于号、小于号范围过大

2）使用!=和<>导致索引失效

3）between…and…

like

如果以 % 开头，不会用到索引

4、不要在索引列上做任何计算（包括类型转换）

不在索引列上做任何操作（计算、函数、(自动or 手动)类型转换），会导致索引失效而转向全表扫描。

所以在使用字符串时，一定要养成习惯，不能依赖于字符串与数值的自动转换，必须加上单引号

5、尽量使用覆盖索引

除了减少表结构修改给业务带来的影响，避免使用select*也可以充分利用覆盖索引，当查询的字段与索引字段一致时，可以避免回表，进一步提高索引的性能。

6、字段的is not null 和is null

当索引字段允许为null时，使用is null作为条件的话索引生效，使用is not null作为条件会导致索引失效

7、减少使用or

可以使用union all 或者union 来替代：

8、关联查询优化（join）

MySQL 表关联的算法是 Nest Loop Join，是通过驱动表的结果集作为循环基础数据，然后一条一条地通过该结果集中的数据作为过滤条件到下一个表中查询数据，然后合并结果。left join中左边的是驱动表，右边的是被驱动表，right join则相反；inner join 时，mysql 会自己帮你把小结果集的表选为驱动表。

根据上述特点，可以得到以下优化建议：

1）在被驱动表（副表）建立索引才有效

这里用以下伪代码，描述关联查询的原理：

//在Book表中建立索引
foreach (rowA in class)//驱动表
{
    foreach (rowB in book)//被驱动表
    {	//将被驱动表B中符合可关联的数据合并至驱动表A中，因为B中字段的添加了索引，匹配到之后可以退出内层循环
        if (existsInBRowWithID(rowA.id)
 		{
        addToResult(rowA.text, getRowInBWithID(rowA.id).text);
       	break；
    	}
    }
}

//在class表中建立索引没有用，因为驱动表必须要全表扫描
foreach (rowA in class)//驱动表
{
    foreach (rowB in book)//被驱动表
    {	//将被驱动表B中符合可关联的数据合并至驱动表A中，B中的字段没有添加索引，需要全表扫描，匹配到之后可以退出内层循环
        if (existsInBRowWithID(rowA.id)
 		{
        addToResult(rowA.text, getRowInBWithID(rowA.id).text);
    	}
    }
}

2）以小结果集驱动大结果集

由于驱动表中必须全表扫描，所以我们优化的目标是尽可能减少JOIN查询中驱动表的循环次数，所以我们必须要用小结果集驱动大结果集！

left join中左边的是驱动表，右边的是被驱动表，right join则相反；inner join 时，mysql 会自己帮你把小结果集的表选为驱动表，在编写SQL语句时要注意将较小的结果集或行数较小的表放在驱动表的位置，如果不确定驱动表的位置，请使用inner join。

3）尽量不要根据非驱动表的字段排序

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序，会出现using temporary的情况，性能会下降。

4）关联查询与子查询

能用关联查询时不使用子查询，能用where关联时尽量不要使用join关联查询

9、exists和in

在范围判断时，用exists和not exists代替in和not in，尽量使用join关联查询代替exists和in

总结：

总结下来就是下面这个口诀：

全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写*；
不等空值还有OR，索引影响要注意；
VAR 引号不可丢，SQL 优化有诀窍。

集驱动大结果集

由于驱动表中必须全表扫描，所以我们优化的目标是尽可能减少JOIN查询中驱动表的循环次数，所以我们必须要用小结果集驱动大结果集！

left join中左边的是驱动表，右边的是被驱动表，right join则相反；inner join 时，mysql 会自己帮你把小结果集的表选为驱动表，在编写SQL语句时要注意将较小的结果集或行数较小的表放在驱动表的位置，如果不确定驱动表的位置，请使用inner join。

3）尽量不要根据非驱动表的字段排序

对驱动表可以直接排序，对非驱动表（的字段排序）需要对循环查询的合并结果（临时表）进行排序，会出现using temporary的情况，性能会下降。

4）关联查询与子查询

能用关联查询时不使用子查询，能用where关联时尽量不要使用join关联查询

9、exists和in

在范围判断时，用exists和not exists代替in和not in，尽量使用join关联查询代替exists和in