机能优化建议

看了这么多，你可能会等待给出一些优化手段，是的，下面会大年夜 3 个不合方面给出一些优化建议。但请等等，还有一句忠言要先送给你：不要听信你看到的关于优化的 “绝对真谛”，包含本文所评论辩论的内容，而应当是在实际的营业场景下经由过程测试来验证你关于履行筹划以及响应时光的假设。

1. Scheme 设计与数据类型优化

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

这里总结几个可能轻易懂得缺点的技能：

• 平日来说把可为 NULL 的列改为 NOT NULL 不会对机能晋升有若干赞助，只是如不雅筹划在列上创建索引，就应当将该列设置为 NOT NULL。
• 半数数类型指定宽度，比如 INT(11)，没有任何卵用。INT 应用 32 位（4 个字节）存储空间，那么它的表示范围已经肯定，所以 INT(1) 和 INT(20) 对于存储和计算是雷同的。
• UNSIGNED 表示不许可负值，大年夜致可以使正数的上限进步一倍。比如 TINYINT 存储范围是 - 128 ~ 127，而 UNSIGNED TINYINT 存储典范围倒是 0 – 255。
• 平日来讲，没有太大年夜的须要应用 DECIMAL 数据类型。即使是在须要存储财务数据时，仍然可以应用 BIGINT。比如须要精确到万分之一，那么可以将数据乘以一百万然后应用 BIGINT 存储。如许可以避免浮点数计算不精确和 DECIMAL 精确计算价值高的问题。
• TIMESTAMP 应用 4 个字节存储空间，DATETIME 应用 8 个字节存储空间。因而，TIMESTAMP 只能表示 1970 – 2038 年，比 DATETIME 表示典范围小得多，并且 TIMESTAMP 的值因时区不合而不合。
• 大年夜多半情况下没有应用列举类型的须要，个一一个缺点是列举的字符串列表是固定的，添加和删除字符串（列举选项）必须应用 ALTER TABLE（如不雅只是在列表末尾追加元素，不须要重建表）。
• schema 的列不要太多。原因是存储引擎的 API 工作时须要在办事器层和存储引擎层之间经由过程行缓冲格局拷贝数据，然后在办事器层精华冲内容解码成各个列，这个转换过程的价值是异常高的。如不雅列太多而实际应用的列又很少的话，有可能会导致 CPU 占用过高。
• 大年夜表 ALTER TABLE 异常耗时，MySQL 履行大年夜部分修改表结不雅操作的办法是用新的构造创建一个张空表，大年夜旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大年夜，并且还有很大年夜索引的情况下，耗时更久。当然有一些事业淫巧可以解决这个问题，有兴趣可自行查阅。

2. 创建高机能索引

索引是进步 MySQL 萌芽机能的一个重要门路，但过多的索引可能会导致过高的磁盘应用率以及过高的内存占用，大年夜而影响应用法度榜样的┞符体机能。应当尽量避免过后才想起添加索引，因为过后可能须要监控大年夜量的 SQL 才能定位到问题地点，并且添加索引的时光肯定是弘远年夜于初始添加索引所须要的时光，可见索引的添加也是异常有技巧含量的。

接下来将向你展示一系列创建高机能索引的策略，以及每条策略其背后的工作道理。但在此之前，先懂得与索引相干的一些算法和数据构造，将有助于更好的懂得后文的内容。

3. 索引相干的数据构造和算法

平日我们所说的索引是指 B-Tree 索引，它是今朝关系型数据库中查找数据最为常用和有效的索引，大年夜多半存储引擎都支撑这种索引。应用 B-Tree 这个术语，是因为 MySQL 在 CREATE TABLE 或其它语句中应用了这个关键字，但实际上不合的存储引擎可能应用不合的数据构造，比如 InnoDB 就是应用的 B+Tree。

B+Tree 中的 B 是指 balance，意为均衡。须要留意的是，B + 树索引并不克不及找到一个给定键值的具体行，它找到的只是被查找数据行地点的页，接着数据库会把页读入到内存，再在内存中进行查找，最后获得要查找的数据。

在介绍 B+Tree 前，先懂得一下二叉查找树，它是一种经典的数据构造，其左子树的值老是小于根的值，右子树的值老是大年夜于根的值，如下图①。如不雅要在这课树中查找值为 5 的记录，其大年夜致流程：先找到根，其值为 6，大年夜于 5，所以查找左子树，找到 3，而 5 大年夜于 3，接着找 3 的右子树，总共找了 3 次。同样的办法，如不雅查找值为 8 的记录，也须要查找 3 次。所以二叉查找树的平均查找次数为 (3 + 3 + 3 + 2 + 2 + 1) / 6 = 2.3 次，而次序查找的话，查找值为 2 的记录，仅须要 1 次，但查找值为 8 的记录则须要 6 次，所以次序查找的平均查找次数为：(1 + 2 + 3 + 4 + 5 + 6) / 6 = 3.3 次，是以大年夜多半情况下二叉查找树的平均查找速度比次序查找要快。

二叉查找树和均衡二叉树

因为二叉查找树可以随便率性构造，同样的值，可以构造出如图②的二叉查找树，显然这棵二叉树的萌芽效力温柔序查找差不多。若想二叉查找数的萌芽机能最高，须要这棵二叉查找树是均衡的，也即均衡二叉树（AVL 树）。

均衡二叉树起首须要相符二叉查找树的定义，其次必须知足任何节点的两个子树的高度差不克不及大年夜于 1。显然图②不知足均衡二叉树的定义，而图①是一课均衡二叉树。均衡二叉树的查找机能是比较高的（机能最好的是最优二叉树），萌芽机能越好，保护的成本就越大年夜。比如图①的均衡二叉树，当用户须冲要入一个新的值 9 的节点时，就须要做出如下更改。

　　推荐阅读

　　教你玩转Hadoop分布式集群搭建，进击大数据

开辟者大年夜赛路演 | 12月16日，技巧立异，北京不见不散 Hadoop的搭建有三种方法，单机版合适开辟调试;伪分布式版，合适模仿集群进修;完全分布式，临盆应用的模式。这篇文件介绍若何搭建完>>>详细阅读

本文标题：万字干货总结：MySQL优化原理学习，这一篇就够了！

地址：http://www.17bianji.com/lsqh/39574.html

 1/2    1