该文产品目录
一、SQL句子及检索的强化
SQL句子的强化
尽可能防止采用子查阅用IN来替代OR加载适度的历史记录LIMIT M,N，而千万别读累赘的历史记录明令禁止无谓的Order By次序八倍查阅能明令禁止排宠信union all防止乱数取历史记录将数次填入改成大批量Insert填入只回到必要性的列，用具体内容的表头条目替代 select * 句子界定in和exists强化Group By句子尽可能采用双蝴表头强化Join句子检索的强化/怎样防止检索失灵
二、资料库表外部结构的强化：使资料库外部结构合乎三大本体论与BCNF
三、G33的强化
四、硬体的强化
在已经开始如是说怎样强化sql前，先附有mysql外部逻辑图让我们略有介绍
（1）插座：主
（2）查阅缓存： 优先在缓存中进行查阅，如果查到了则直接回到，如果缓存中查阅不到，在去资料库中查阅。
MySQL缓存是默认关闭的，也就是说不推荐采用缓存，并且在MySQL8.0 版本已经将查阅缓存的整块功能删掉了。这主要是它的采用场景限制造成的：
先说下缓存中数据存储格式：key（sql句子）- value（数据值），所以如果SQL句子（key）只要存在一点不同之处就会直接进行资料库查阅了；由于表中的数据不是一成不变的，大多数是经常变化的，而当资料库中的数据变化了，那么相应的与此表相关的缓存数据就需要移除掉；（3）解析器/分析器：分析器的工作主要是对要执行的SQL句子进行词法解析、语法解析，最终得到抽象语法树，然后再采用预处理器对抽象语法树进行语义校验，判断抽象语法树中的表是否存在，如果存在的话，在接着判断select投影条目头是否在表中存在等。
（4）强化器： 主要将SQL经过词法解析、语法解析后得到的语法树，通过数据字典和统计信息的内容，再经过一系列运算 ，最终得出一个执行计划，包括选择采用哪个检索
在分析是否走检索查阅时，是通过进行动态数据采样统计分析出来；只要是统计分析出来的，那就可能会存在分析错误的情况，所以在SQL执行不走检索时，也要考虑到这方面的因素（5）执行器：根据一系列的执行计划去调用存储引擎提供的API接口去调用操作数据，完成SQL的执行。
一、SQL句子及检索的强化
SQL句子的强化
1. 尽可能防止采用子查阅
例：
SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name = chackca);其子查阅在Mysql5.5版本里，外部执行计划是这样：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查阅速度会十分慢。
在MariaDB10/Mysql5.6版本里，采用join关联方式对其进行了强化，这条SQL句子会自动转换为：SELECT t1.* FROM t1 JOIN t2 on t1.id = t2.id
但请注意的是：强化只针对SELECT有效，对UPDATE/DELETE子查阅无效，固生产环境应防止采用子查阅
由于MySQL的强化器对于子查阅的处理能力比较弱，所以不建议采用子查阅，能改写成Inner Join，之所以 join 连接效率更高，是因为 MySQL不需要在内存中创建临时表
2. 用IN来替代OR
低效查阅：SELECT * FROM t WHERE id = 10 OR id = 20 OR id = 30;高效查阅：SELECT * FROM t WHERE id IN (10,20,30);另外，MySQL对于IN做了相应的强化，即将IN中的常量全部存储在一个数组里面，而且这个数组是排好序的。但是如果数值较多，产生的消耗也是比较大的。再例如：select id from table_name where num in(1,2,3)对于连续的数值，能用 between 就千万别用 in 了；再或者采用连接来替代。
3. 加载适度的历史记录LIMIT M,N，而不要读累赘的历史记录
select id,name from t limit 866613, 20采用上述sql句子做分页的时候，可能有人会发现，随着表数据量的增加，直接采用limit分页查阅会越来越慢。
对于 limit m, n的分页查阅，越往后面翻页（即m越大的情况下）SQL的耗时会越来越长，对于这种应该先取出主键id，然后通过主键id跟原表进行Join关联查阅。因为MySQL 并不是跳过 offset 行，而是取offset+N 行，然后放弃前 offset 行，回到 N 行，那当 offset 特别大的时候，效率就十分的低下，要么控制回到的总页数，要么对超过特定阈值的页数进行 SQL 改写。
强化的方法如下：可以取前一页的最大行数的id（将上次遍历到的最末尾的数据ID传给资料库，然后直接定位到该ID处，再往后面遍历数据），然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。sql能采用如下的写法：
select id,name from table_name where id> 866612 limit 204. 明令禁止无谓的Order By次序
如果我们对结果没有次序的要求，就尽可能少用次序；
如果次序表头没有用到检索，也尽可能少用次序；
另外，分组统计查阅时能明令禁止其默认次序
SELECT goods_id,count(*) FROM t GROUP BY goods_id;默认情况下，Mysql会对所有的GROUP BT col1,col2…的表头进行次序，也就是说上述会对 goods_id进行次序，如果想要防止次序结果的消耗，能指定ORDER BY NULL明令禁止次序：
SELECTgoods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL5. 八倍查阅能明令禁止排宠信union all
union和union all的差异主要是前者需要将结果集合并后再进行唯一性过滤操作，这就会涉及到次序，增加大量的CPU运算，加大资源消耗及延迟。
当然，union all的前提条件是两个结果集没有重复数据。所以一般是我们明确知道不会出现重复数据的时候才建议采用 union all 提高速度。
6. 防止乱数取历史记录
SELECT * FROM t1 WHERE 1 = 1 ORDER BY RAND() LIMIT 4; SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;以上两个句子都无法用到检索
7. 将数次填入改成大批量Insert填入
INSERT INTO t(id, name) VALUES(1, aaa); INSERT INTO t(id, name) VALUES(2, bbb); INSERT INTO t(id, name) VALUES(3, ccc); —> INSERT INTO t(id, name) VALUES(1, aaa),(2, bbb),(3, ccc);8. 只回到必要性的列，用具体内容的表头条目替代 select * 句子
SELECT * 会增加很多无谓的消耗（cpu、io、内存、网络带宽）；增加了采用覆盖检索的可能性；当表外部结构发生改变时，前者也需要经常更新。所以要求直接在select后面接上表头名。
MySQL资料库是按照行的方式存储，而数据存取操作都是以一个页大小进行IO操作的，每个IO单元中存储了多行，每行都是存储了该行的所有表头。所以无论取一个表头还是多个表头，实际上资料库在表中需要访问的数据量其实是一样的。
，当存在 order by 操作的时候，select 子句中的表头多少会在很大程度上影响到我们的次序效率。
9. 界定in和exists
select * from 表A where id in (select id from 表B)上面的句子相当于：
select * from 表A where exists(select * from 表B where 表B.id=表A.id)界定in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查阅。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
另外，in查阅在某些情况下有可能会查阅回到错误的结果，因此，通常是建议在确定且有限的集合时，能采用in。如 IN （0，1，2）。
10. 强化Group By句子
如果对group by句子的结果没有次序要求，要在句子后面加 order by null（group 默认会次序）；
尽可能让group by过程用上表的检索，确认方法是explain结果里没有Using temporary 和 Using filesort；
如果group by需要统计的数据量不大，尽可能只采用内存临时表；也能通过适度调大tmp_table_size参数，来防止用到磁盘临时表；
如果数据量实在太大，采用SQL_BIG_RESULT这个提示，来告诉强化器直接使用次序算法（直接用磁盘临时表）得到group by的结果。采用where子句替代Having子句：防止采用having子句，having只会在检索出所有历史记录之后才会对结果集进行过滤，这个处理需要次序分组，如果能通过where子句提前过滤查阅的数目，就能减少这方面的开销。
低效: SELECT JOB, AVG(SAL) FROM EMP GROUP by JOB HAVING JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’高效: SELECT JOB, AVG(SAL) FROM EMP WHERE JOB = ‘PRESIDENT’ OR JOB = ‘MANAGER’ GROUP by JOB11. 尽可能采用双蝴表头
若只含数值信息的表头尽可能千万别设计为字符型，这会降低查阅和连接的性能。引擎在处理查阅和连接时会逐个比较字符串中每一个字符，而对于双蝴而言只需要比较一次就够了。
12. 强化Join句子
当我们执行两个表的Join的时候，就会有一个比较的过程，逐条比较两个表的句子是比较慢的，因此能把两个表中数据依次读进一个内存块中，在Mysql中执行：show variables like ‘join_buffer_size’，能看到join在内存中的缓存池大小，其大小将会影响join句子的性能。在执行join的时候，资料库会选择一个表把他要回到以及需要进行和其他表进行比较的数据放进join_buffer。
什么是驱动表，什么是被驱动表，这两个概念在查阅中有时容易让人搞混，有下面几种情况，我们需要介绍。
1.当连接查阅没有where条件时
left join前面的表是驱动表，后面的表是被驱动表right join 后面的表是驱动表，前面的表是被驱动表inner join / join 会自动选择表数据比较少的作为驱动表straight_join(≈join)直接选择左边的表作为驱动表（语义上与join类似，但去除了join自动选择小表作为驱动表的特性）2.当连接查阅有where条件时，带where条件的表是驱动表，否则是被驱动表
假设有表如右边：t1与t2表完全一样，a表头有检索，b无检索，t1有100条数据，t2有1000条数据
若被驱动表有检索，那么其执行算法为：Index Nested-Loop Join（NLJ），示例如下：
1.执行句子：select * from t1 straight_join t2 on (t1.a=t2.a)；由于被驱动表t2.a是有检索的，其执行逻辑如下：
从表t1中读入一行数据 R；从数据行R中，取出a表头到表t2里去查找；取出表t2中满足条件的行，跟R组成一行，作为结果集的一部分；重复执行步骤1到3，直到表t1的末尾循环结束。如果一条join句子的Extra表头什么都没写的话，就表示采用的是NLJ算法若被驱动表无索引，那么其执行算法为：Block Nested-Loop Join（BLJ）（Block 块，每次都会取一块数据到内存以减少I/O的开销），示例如下：
2.执行句子：select * from t1 straight_join t2 on (t1.a=t2.b)；由于被驱动表t2.b是没有检索的，其执行逻辑如下：
把驱动表t1的数据读入线程内存join_buffer（无序数组）中，由于我们这个句子中写的是select *，因此是把整个表t1放入了内存；顺序遍历表t2，把表t2中的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分回到。3.另外还有一种算法为Simple Nested-Loop Join（SLJ），其逻辑为：顺序取出驱动表中的每一行数据，到被驱动表去做全表扫描匹配，匹配成功则作为结果集的一部分回到。
另外，Innodb会为每个数据表分配一个存储在磁盘的 表名.ibd 文件，若关联的表过多，将会导致查阅的时候磁盘的磁头移动次数过多，从而影响性能
所以实践中，尽可能减少Join句子中的NestedLoop的循环次数：“永远用小结果集驱动大的结果集”
用小结果集驱动大结果集，将筛选结果小的表（在决定哪个表做驱动表的时候，应该是两个表按照各自的条件过滤，过滤完成之后，计算参与join的各个表头的总数据量，数据量小的那个表，就是“小表”）首先连接，再去连接结果集比较大的表，尽可能减少join句子中的Nested Loop的循环总次数优先强化Nested Loop的内层循环（也就是最外层的Join连接），因为内层循环是循环中执行次数最多的，每次循环提升很小的性能都能在整个循环中提升很大的性能；对被驱动表的join表头上建立检索；当被驱动表的join表头上无法建立检索的时候，设置足够的Join Buffer Size。尽可能用inner join(因为其会自动选择小表去驱动大表).防止 LEFT JOIN (一般我们采用Left Join的场景是大表驱动小表)和NULL，那么怎样强化Left Join呢？条件中尽可能能够过滤一些行将驱动表变得小一点，用小表去驱动大表右表的条件列一定要加上检索（主键、唯一检索、前缀检索等），最好能够使type达到range及以上（ref,eq_ref,const,system）适度地在表里面添加冗余信息来减少join的次数采用更快的固态硬盘性能强化，left join 是由左边决定的，左边一定都有，所以右边是我们的关键点，建立检索要建在右边。当然如果检索是在左边的，我们能考虑采用右连接，如下
select * from atable left join btable on atable.aid=btable.bid; — 最好在bid上建检索Tips：Join左连接在右边建立检索；组合检索则尽可能将数据量大的放在左边，在左边建立检索
检索的强化/怎样防止检索失灵
1.最佳左前缀法则
如果检索了多列，要遵守最左前缀法则，指的是查阅从检索的最左前列已经开始并且不跳过检索中的列。Mysql查阅强化器会对查阅的表头进行改进，判断查阅的表头以哪种形式组合能使查阅更快，所有比如创建的是(a,b)检索，查阅的是(b,a)，查阅强化器会修改成(a,b)后采用检索查阅。
2.不在检索列上做任何操作
1.计算：对检索进行表达式计算会导致检索失效，如where id + 1 = 10，能转改成 where id = 10 -1，这样就能走检索
2.函数：select * from t_user where length(name)=6; 此语句对表头采用到了函数，会导致检索失灵
从 MySQL 8.0 已经开始，检索特性增加了函数检索，即能针对函数计算后的值建立一个检索，也就是说该检索的值是函数计算后的值，所以就能通过扫描检索来查阅数据。
alter table t_user add key idx_name_length ((length(name)));（自动/手动）类型转换
（字符串类型必须带引号才能使检索生效）表头是varchar，用整型进行查阅时，无法走检索，如select * from user where phone = 13030303030；Mysql 在执行上述句子时，会把表头转换为数字再进行比较，所以上面那条句子就相当于：select * from user where CAST(phone AS signed int) = 13030303030; CAST 函数是作用在了 phone 表头，而 phone 表头是检索，也就是对检索采用了函数！所以检索失灵
表头是int，用string进行查阅时，mysql会自动转化，能走检索，如：select * from user where id = 1；MySQL 在遇到字符串和数字比较的时候，会自动把字符串转为数字，然后再进行比较。以上这条句子相当于：select * from user where id = CAST(“1” AS signed int)，检索表头并没有用任何函数，CAST 函数是用在了输入参数，因此是能走检索扫描的。
3.存储引擎不能采用检索中范围条件右边的列。
如这样的sql: select * from user where username=123 and age>20 and phone=1390012345,其中username, age, phone都有检索，只有username和age会生效，phone的检索没有用到。
4.尽可能使用覆盖检索（只访问检索的查阅（检索列和查阅列一致））
如select age from user，减少select *
5.mysql在采用负向查阅条件(!=、<>、not in、not exists、not like)的时候无法采用检索会导致全表扫描。
你能想象一下，对于一棵B+树，根节点是40，如果你的条件是等于20，就去左面查，你的条件等于50，就去右面查，但是你的条件是不等于66，检索应该咋办？还不是遍历一遍才知道。
6.is null, is not null也无法采用检索，在实际中尽可能千万别采用null（防止在 where 子句中对表头进行 null 值判断） 不过在mysql的高版本已经做了强化，允许采用检索
对于null的判断会导致引擎放弃采用检索而进行全表扫描。
7.like 以通配符开头(%abc..)时，mysql检索失灵会变成全表扫描的操作。
所以最好用右边like ‘abc%’。如果两边都要用，能用select username from user where username like %abc%，其中username是必须是检索列，才可让检索生效
假如index(a,b,c), where a=3 and b like ‘abc%’ and c=4，a能用，b能用，c不能用，类似于不能采用范围条件右边的列的检索
对于一棵B+树检索来讲，如果根节点是字符def，假如查阅条件的通配符在后面，例如abc%，则其知道应该搜索左子树，假如传入为efg%，则应该搜索右子树，如果通配符在前面%abc，则资料库不知道应该走哪一面，就都扫描一遍了。
8.少用or，在 WHERE 子句中，如果在 OR 前的条件列是检索列，而在 OR 后的条件列不是检索列，那么检索会失灵。
select * from t_user where id = 1 or age = 18; — id有检索，name没有，此时没法走检索因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列是检索列是没有意义的，只要有条件列不是检索列，就会进行全表扫描。
必须要or前后的表头都有检索，查阅才能采用上检索（分别采用，最后合并结果type = index_merge）
9.在组合/联合检索中，将有界定度的检索放在前面
如果没有界定度，例如用性别，相当于把整个大表分成两部分，查找数据还是需要遍历半个表才能找到，使检索失去了意义。
10.采用前缀检索
短检索不仅能提高查阅性能而且能节省磁盘空间和I/O操作，减少检索文件的维护开销，但缺点是不能用于 ORDER BY 和 GROUP BY 操作，也不能用于覆盖检索。
比如有一个varchar(255)的列，如果该列在前10个或20个字符内，能做到既使前缀检索的界定度接近全列检索，那么就千万别对整个列进行检索。为了减少key_len，能考虑创建前缀检索，即指定一个前缀长度，可以采用count(distinct leftIndex(列名, 检索长度))/count(*) 来计算前缀检索的界定度。
11.SQL 性能强化 explain 中的 type：至少要达到 range 级别，要求是 ref 级别，如果能是 consts 最好。
consts：单表中最多只有一个匹配行（主键或者唯一检索），在强化阶段即可加载到数据。ref：采用普通的检索range：对检索进行范围检索。当 type=index 时，检索物理文件全扫，速度十分慢。
二、资料库表外部结构的强化：使资料库外部结构合乎三大本体论与BCNF
https://blog.csdn.net/qq_35642036/article/details/82809974
