MySQL优化
采用左外连接 (右外连接同理，只不过反过来，右边是驱动表，要给左边加索引)
左外连接LEFT JOIN 条件。对应右边是被驱动表，一定需要建立索引。
- join原理
子查询优化
排序优化
- 基本介绍
- FileSort算法
GROUP BY优化
分页查询优化
大表优化
使用Redis或Memcached作为缓存
explain语句
show profile语句

MySQL优化

参考博客：https://blog.csdn.net/xinzhifu1/article/details/104228470

为什么需要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
数据是存放在磁盘上的，读写速度无法和内存相比
优化的整体架构
基本上有九个：1. 字段设计 2. 表设计(3个范式) 3. 索引 4. 缓存 5. 分区 6. 集群 7. 典型SQL的优化（order by , count(*)等） 8. 慢查询日志 9. 典型服务器配置。
https://achang.blog.csdn.net/article/details/122643587

首先在S1部分，我们需要观察服务器的状态是否存在周期性的波动。如果存在周期性波动，有可能是周期性节点的原因，比如双十一、促销活动等。这样的话，我们可以通过A1这一步骤解决，也就是加缓存，或者更改缓存失效策略。
如果缓存策略没有解决，或者不是周期性波动的原因，我们就需要进一步分析延迟和卡顿的原因。接下来进入S2这一步，我们需要开启慢查询。慢查询可以帮我们定位执行慢的SQL语句。我们可以通过设置long_query.time参数定义 “慢” 的阈值。如果SQL执行时间超过了long_query_time，则会认为是慢查询。当收集上来这些慢查询之后，我们就可以通过分析工具对慢查询日志进行分析，比如针对性地用EXPLAIN查看对应SQL语句的执行计划，或者使用show profile查看SQL中每一个步骤的时间成本。这样我们就可以了解SQL查询慢是因为执行时间长，还是等待时间长。
如果是SQL等待时间长，我们进入A2步骤。在这一步骤中，我们可以调优服务器的参数，比如适当增加数据库缓冲池等。
如果是SQL执行时间长，就进入A3步骤，这一步中我们需要考虑一些问题，如索引设计、查询关联的数据表过多、数据表的字段设计问题等，然后在这些维度上进行对应的调整。
如果A2和A3都不能解决问题，我们需要考虑数据库自身的SQL查询性能是否已经达到了瓶颈，如果确认没有达到性能瓶颈，就需要重新检查，重复以上的步骤。
如果已经达到了性能瓶颈，进入A4阶段，需要考虑增加服务器，采用读写分离的架构，或者考虑对数据库进行分库分表，比如垂直分库、垂直分表和水平分表等。
选择适合的DBMS
如果对事务性处理以及安全性要求高的话，可以选择商业的数据库产品。这些数据库在事务处理和查询性能上都比较强，比如采用SQL Server、Oracle，那么单表存储上亿条数据是没有问题的。
除此以外，你也可以采用开源的MySQL进行存储，它有很多存储引擎可以选挥，如果进行事务处理的话可以选择lnnoDB，非事务处理可以选择MylSAM。
NoSQL阵营包括键值型数据库、文档型数据库、搜索引擎、列式存储和图形数据库。
DBMS的选择关系到了后面的整个设计过程，所以第一步就是要选择适合的DBMS。
字段设计

尽量使用整型来保存字符串或文本等。

尽可能选择小的数据类型和指定短的长度，使用定长的数据类型。
使用 not null 的数据类型。非null字段的处理要比null字段的处理高效些！且不需要判断是否为null。 null在MySQL中，不好处理，存储需要额外空间，运算也需要特殊的运算符。如select null = null和select null <> null（<>为不等号）有着同样的结果，只能通过is null和is not null来判断字段是否为null。

字段注释要完整，单表字段不宜过多，可以预留字段。
避免使用TEXT,BLOB数据类型。

避免使用ENUM数据类型。

使用时间戳保存时间。

用decimal替代float和double类型。

表设计

关联表的设计

一对多：使用外键。
多对多：单独新建表，把多对多改成一对多。
一对一：如商品的基本信息（item）和商品的详细信息（item_intro），通常使用相同的主键或者增加一个外键字段（item_id）
范式
在关系型数据库中，关于数据表设计的基本原则、规则就称为范式。可以理解为，一张数据表的设计结构需要满足的某种设计标准的级别。要想设计一个结构合理的关系型数据库，必须满足一定的范式。
范式设计越高阶，冗余度就越低，最高一般就遵循到BCNF，普遍也就是3NF，有时候还需要反范式化。

第一范式：字段原子性

也就是字段不可再分割，确保数据表中每个字段的值必须具有原子性。
比如某些数据库系统中需要用到“地址”这个属性，本来直接将“地址”属性设计成一个数据库表的字段就行。但是如果系统经常会访问“地址”属性中的“城市”部分，那么就要将“地址”这个属性重新拆分为省份、城市、详细地址等多个部分进行存储。这样设计才算满足了数据库的第一范式。
再举一个例子：

当然属性的原子性是主观的，如果我们就是要详细的地址，那么地址这个属性就不需要再拆分了。

第二范式：确保表中的每列都和主键相关(可以是间接相关)

第二范式需要确保数据库表中的每一列都和主键相关，而不能只与主键的某一部分相关（主要针对联合主键而言）。也就是说在一个数据库表中，一个表中只能保存一种数据，不可以把多种数据保存在同一张数据库表中。
比如：学号, 姓名, 年龄, 课程名称, 成绩, 学分。主键为学号。
学号和课程名称可以决定成绩，但是光通过学号就可以决定姓名和年龄了，因此不符合第二范式。
比如：

第三范式

确保每列都和主键列直接相关，而不是间接相关，这是第二范式的强化版。也就是说，要求数据表中的所有非主键字段不能依赖于其他非主键字段。
比如：学号, 姓名, 年龄, 所在学院, 学院联系电话，主键为单一关键字”学号”;
存在依赖传递: (学号) → (所在学院) → (学院地点, 学院电话)。也就是说，光通过主键学号是不能知道学院联系电话的，出现了间接相关。分成学生表、学院表即可。
比如：

范式的优点与缺点

范式的优点：数据的标准化有助于消除数据库中的数据冗余，第三范式(3NF）通常被认为在性能、扩展性和数据完整性方面达到了最好的平衡。
范式的缺点：范式的使用，可能降低查询的效率。因为范式等级越高，设计出来的数据表就越多、越精细，数据的冗余度就越低，进行数据查询的时候就可能需要关联多张表，这不但代价昂贵，也可能使一些索引策略无效。范式只是提出了设计的标准，实际上设计数据表时，未必一定要符合这些标准。开发中，我们会出现为了性能和读取效率违反范式化的原则，通过增加少量的冗余或重复的数据来提高数据库的读性能，减少关联查询，join表的次数，实现空间换取时间的目的。因此在实际的设计过程中要理论结合实际，灵活运用。
范式本身没有优劣之分，只有适用场景不同。没有完美的设计，只有合适的设计，我们在数据表的设计中，还需要根据需求将范式和反范式混合使用。

反范式化

有的数据看似冗余，其实对业务来说十分重要。如果数据库中的数据量比较大，完全按照三大范式设计数据表，读数据时会产生大量的关联查询，在一定程度上会影响数据库的读性能。此时，可以通过在数据表中增加冗余字段来提高数据库的读性能。
比如：我们有2个表，分别是商品流水表和商品信息表。商品流水表里有400万条流水记录，商品信息表里有2000条商品记录。

但是，在项目的实施过程中，对流水的查询频率很高，而且为了获取商品名称，每次都会用到与商品信息表的连接查询。
为了减少连接，我们可以直接把商品名称字段加到商品流水表里面，避免了关联查询。

反范式化的一些问题：

存储空间变大了
一个表中字段做了修改，另一个表中冗余的字段也需要做同步修改，否则数据不一致
若采用存储过程来支持数据的更新、删除等额外操作，如果更新频繁，会非常消耗系统资源
在数据量小的情况下，反范式不能体现性能的优势，可能还会让数据库的设计更加复杂

反范式化的应用场景：

这个冗余字段不需要经常进行修改。
这个冗余字段查询的时候不可或缺。
而且能大幅度提高查询效率。

BCNF(巴斯范式)

若一个关系达到了第三范式，并且它只有一个候选键，或者它的每个候选键都是单属性，则该关系自然达到BC范式。
还有第四范式以及第五范式(域键范式)，了解即可。

一个很实用的数据库设计工具：power designer。至于设计数据库的一些注意点，可以参考阿里巴巴Java开发手册。

拆分表

拆分表的目的是：让冷热数据分离。
拆分表的思路是：把1个包含很多字段的表拆分成2个或者多个相对较小的表。这样做的原因是，这些表中某些字段的操作频率很高（热数据），经常要进行查询或者更新操作，而另外一些字段的使用频率却很低（冷数据），冷热数据分离，可以减小表的宽度。如果放在一个表里面，每次查询都要读取大记录，会消耗较多的资源。

增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。
首先，分析经常联合查询表中的字段。然后，使用这些字段建立一个中间表，并将原来联合查询的表的数据插入中间表中。最后，使用中间表来进行查询。
比如：现在有一个模块需要经常查询带有学生名称（name）、学生所在班级名称（className）、学生班级班长（monitor）的学生信息。根据这种情况可以创建一个 temp_student 表。从学生表和班级表选择信息。

索引

参考MySQL深度解析里的索引。

缓存

在通过连接器后，紧接着就是查询缓存。查询缓存是key-value形式存储的，key是查询语句，value是查询结果。如果能命中，查询语句直接返回，否则会进行后面的步骤，然后将查询结果放到查询缓存中，再将查询结果返回给客户端。能够看出如果能命中的话效率还是很高的。但是不建议使用，原因如下：
MySQL8.0已经将这个模块删除掉，因为查询缓存很容易失效，如果表有任何的更新，缓存就会失效（被删除）。可以想想辛辛苦苦创建的一大堆缓存，因为一条写语句就失效了，这对大多数应用都得不偿失。
但是我们还是可以通过Redis等内存数据库，来达到缓存的目的。

分区

基本简介

一般情况下我们创建的表对应一组存储文件，使用MyISAM存储引擎时是一个.MYI 和 .MYD 文件，使用Innodb存储引擎时是一个.ibd 和 .frm（表结构）文件。
当数据量较大时（一般千万条记录级别以上），MySQL的性能就会开始下降，这时我们就需要将数据分散到多组存储文件，保证其单个文件的执行效率。
服务端的表分区对于客户端是透明的，客户端还是照常插入数据，但服务端会按照分区算法分散存储数据。

MySQL提供的分区算法

注：分区依据的字段必须是主键的一部分，分区是为了快速定位数据，因此该字段的搜索频次较高应作为强检索字段，否则依照该字段分区毫无意义。

hash(字段)

相同的输入得到相同的输出。输出的结果跟输入是否具有规律无关。参考上图。仅适用于整型字段。最常见的分区方案是按 id 分区，如下将 id 的哈希值对10取模将数据均匀分散到10个 .ibd 存储文件中：

create table article(
    id int auto_increment PRIMARY KEY,
    title varchar(64),
    content text
)PARTITION by HASH(id) PARTITIONS 10

MySQL优化 - 图22

key(字段)

和 hash(field) 的性质一样，只不过 key 是处理字符串的，比 hash() 多了一步从字符串中计算出一个整型再做取模操作。

create table article_key(
    id int auto_increment,
    title varchar(64),
    content text,
    PRIMARY KEY (id,title)    -- 要求分区依据字段必须是主键的一部分 (这是复合主键,算一个)
)PARTITION by KEY(title) PARTITIONS 10 --10表示生成10个分区,名字自动生成

range算法

是一种条件分区算法，按照数据大小范围分区（将数据使用某种条件，分散到不同的分区中）。

-- 按文章的发布时间将数据按照2018年8月、9月、10月分区存放
create table article_range(
    id int auto_increment,
    title varchar(64),
    content text,
    created_time int,    -- 发布时间到1970-1-1的毫秒数
    PRIMARY KEY (id,created_time)    -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY RANGE(created_time)(
    PARTITION p201808 VALUES less than (1535731199),    -- select UNIX_TIMESTAMP('2018-8-31 23:59:59')
    PARTITION p201809 VALUES less than (1538323199),    -- 2018-9-30 23:59:59
    PARTITION p201810 VALUES less than (1541001599)    -- 2018-10-31 23:59:59
);
insert into article_range values(null,'MySQL优化','内容示例',1535731180); --放在p201808中
flush tables;    -- 使操作立即刷新到磁盘文件

MySQL优化 - 图23 注：条件运算符只能使用less than，因为较小的范围要放在前面。

list算法

也是一种条件分区，按照列表值分区（in (值列表)）。

create table article_list(
    id int auto_increment,
    title varchar(64),
    content text,
    status TINYINT(1),    -- 文章状态：0-草稿，1-完成但未发布，2-已发布
    PRIMARY KEY (id,status)    -- 要求分区依据字段必须是主键的一部分
)charset=utf8
PARTITION BY list(status)(
    PARTITION writing values in(0,1),    -- 未发布的放在一个分区 (名字是writing)
    PARTITION published values in (2)    -- 已发布的放在一个分区 (名字是published)
);
insert into article_list values(null,'mysql优化','内容示例',0); --放在writing中
flush tables;

MySQL优化 - 图24

MySQL提供的分区管理算法

range/list

增加分区：

alter table article_range add partition( --增加一个分区
    partition p201811 values less than (1543593599)    
  -- select UNIX_TIMESTAMP('2018-11-30 23:59:59')
);

MySQL优化 - 图25
销毁分区：

alter table article_range drop PARTITION p201808;
--注：删除分区后，分区中原有的数据也会随之删除！

key/hash

新增分区：

alter table article_key add partition partitions 4; -- 4表示根据算法增加4个分区

MySQL优化 - 图26
删除分区：

alter table article_key coalesce partition 6; --销毁6个分区

key/hash 分区的管理不会删除数据，但是每一次调整（新增或销毁分区）都会将所有的数据重写分配到新的分区上。效率极低，最好在设计阶段就考虑好分区策略。

水平分割和垂直分割

水平分割：把同一张表中的数据拆分到不同的数据库中进行存储，即把一张表拆分成 n 多张小表。
垂直分割：就是根据不同的业务进行拆分的，把一张表拆分到不同的数据库，比如会员数据库、订单数据库、支付数据库、消息数据库等，垂直拆分在大型电商项目中使用比较常见。优点：拆分后业务清晰。缺点：跨数据库查询比较麻烦。

集群(库级优化)

通过主从架构优化我们的读写策略，通过对数据库进行垂直或者水平切分，突破单一数据库或数据表的访问限制，提升查询的性能。

基本介绍

MySQL集群是一个无共享的(shared-nothing)、分布式节点架构的存储方案，其目的是提供容错性和高性能。
数据更新使用读已提交隔离级别（read-committed isolation)来保证所有节点数据的一致性，使用两阶段提交机制（two-phasedcommit)保证所有节点都有相同的数据(如果任何一个写操作失败，则更新失败)
无共享的对等节点使得某台服务器上的更新操作在其他服务器上立即可见。传播更新使用一种复杂的通信机制，这一机制专用来提供跨网络的高吞吐量。
通过多个MySQL服务器分配负载，从而最大程序地达到高性能，通过在不同位置存储数据保证高可用性和冗余。

架构图

MySQL主从复制

主库与从库的关系

主库用于增删改，从库用于查找。主库修改后会引起从库的修改，但从库修改不会使主库修改。

双主双从模式：一个主机m1用于处理所有写请求，它的从机s1和另一台主机m2还有它的从机s2负责所有读请求。当m1主机宕机后，m2主机负责写请求，m1、m2互为备机。

原理

Slave会从Master读取bin log来进行数据同步。

主库会生成一个 log dump 线程，用来给从库 I/O线程传binlog，此时会在Binlog上加锁，读取完成之后，再将锁释放掉。从库生成两个线程，一个I/O线程，一个SQL线程。I/O线程去请求主库的 binlog，并将得到的binlog日志写到relay log（中继日志）文件中。SQL 线程会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，而最终数据一致。
注：每个Slave只有一个Master，每个Master可以有很多Slave。

解决主从延迟问题

进行主从同步的内容是二进制日志，它是一个文件，在进行网络传输的过程中就一定会存在主从延迟（比如 500ms），也是主从延迟问题导致了同步数据不一致的问题。
在网络正常的时候，日志从主库传给从库所需的时间是很短的。主备延迟最直接的表现是，从库消费中继日志(relay log)的速度，比主库生产binlog的速度要慢。造成原因:
1. 从库的机器性能比主库要差。 2. 从库的压力大。3. 大事务的执行。

解决同步数据不一致的问题

异步复制
异步模式就是客户端提交COMMIT之后直接将结果返回给客户端，这样做处是不会影响主库写的效率，但可能会存在主库宕机，而Binlog还没有同步到从库的情况，此时的主库和从库数据不一致。这时候若在从库中选择一个作为新主服务器，那么它则可能缺少原来主服务器中已提交的事务。所以，这种复制模式下的数据一致性是最弱的。

半同步复制
MySQL5.5版本之后开始支持。原理是在客户端提交COMMIT之后不直接将结果返回给客户端，而是等待一定数量的ACK(表明至少一个从库收到了binlog的信息)，再返回给客户端。
这样做的好处就是提高了数据的一致性，当然相比于异步复制来说，增加了一个网络连接的延迟，降低了主库写的效率。
在MySQL5.7版本中还增加了一个rpl_semi_sync_master_wait_for_slave_count参数，可以对应答的从库数量进行设置，默认为1，也就是说只要有1个从库进行了响应，就可以返回给客户端。如果将这个参数调大，可以提升数据一致性的强度，但也会增加主库等待从库响应的时间。

组复制
基于Paxos协议。节点各自维护了自己的数据副本，并且在一致性协议层实现了原子消息和全局有序消息，从而保证组内数据的一致性。

用途

高可用性，当服务器发生故障或宕机时，可以切换到从服务器上，保证服务的正常运行。

正常可用时间/全年时间
比如要达到全年99.999%的时间都可用，就意味着系统在一年中的不可用时间不得超过365*24*60+(1-99.999%)=5.256分钟（含系统崩溃的时间、日常维护操作导致的停机时间等)，其他时间都需要保持可用的状态。

读写分离。面对读多写少的需求，采用读写分离的方式，可以实现更高的并发访问。同时，我们还能对从服务器进行负载均衡，让不同的读请求按照策略均匀地分发到不同的从服务器上，让读取更加顺畅。读取顺畅的另一个原因，就是减少了锁表的影响，比如我们让主库负责写，当主库出现写锁的时候，不会影响到从库进行SELECT的读取。
备份。我们通过主从复制将主库上的数据复制到了从库上，相当于是一种热备份机制，也就是在主库正常运行的情况下进行的备份，不会影响到服务。

条件

主库开启binlog日志（设置log-bin参数）。
主从server-id不同。
从库服务器能连通主库。

典型SQL的优化

线上DDL
DDL(Database Definition Language)是指数据库表结构的定义（create table）和维护（alter table）的语言。在线上执行DDL，在低于MySQL5.6版本时会导致全表被独占锁定，此时表处于维护、不可操作状态，这会导致该期间对该表的所有访问无法响应。但是在MySQL5.6之后，支持Online DDL，大大缩短了锁定时间。
随着MySQL的升级，这个问题几乎淡化了。

数据库导入语句
导入时先禁用索引和约束，等数据导入完成时，再开启索引和约束。 ```sql alter table (table-name) disable keys

alter table (table-name) enable keys


2. **数据库如果使用的引擎是Innodb，那么它默认会给每条写指令加上事务（这也会消耗一定的时间），因此建议先手动开启事务，再执行一定量的批量导入，最后手动提交事务。**
2. **如果批量导入的SQL指令格式相同只是数据不同，那么你应该先 prepare 预编译一下，这样也能节省很多重复编译的时间。**
<a name="oXntQ"></a>
### limit offset,rows 语句
**尽量保证不要出现大的offset，比如 limit 10000,10，也就是从10000行开始取，取十行。这相当于对已查询出来的行数弃掉前10000行后再取10行，完全可以加一些条件过滤一下（完成筛选），而不应该使用limit跳过已查询到的数据。这是一个offset做无用功的问题。**
<a name="Cvtf5"></a>
### select * 要少用
**即尽量选择自己需要的字段select，但这个影响不是很大，因为网络传输多了几十上百字节也没多少延时，并且现在流行的ORM框架都是用的select *，只是我们在设计表的时候注意将大数据量的字段分离，比如商品详情可以单独抽离出一张商品详情表，这样在查看商品简略页面时的加载速度就不会有影响了。**<br />![image.png](https://cdn.nlark.com/yuque/0/2022/png/23175776/1647521954153-e739218c-b4eb-4b39-b4ea-543c83b5381f.png#clientId=ua9780505-197c-4&crop=0&crop=0&crop=1&crop=1&from=paste&height=120&id=Qy832&margin=%5Bobject%20Object%5D&name=image.png&originHeight=239&originWidth=1689&originalType=binary&ratio=1&rotation=0&showTitle=false&size=192940&status=done&style=none&taskId=u768396d1-75d1-4c67-b48c-276a931a449&title=&width=844.5)
<a name="kMAB2"></a>
### order by rand()不要用
**它的逻辑就是随机排序（为每条数据生成一个随机数，然后根据随机数大小进行排序），在应用程序中，我们可以随机生成主键，去数据库中利用主键检索。**
```sql
select * from student order by rand() limit 5 --效率很低

count(*)

在 MyISAM 存储引擎中，会自动记录表的行数，因此使用 count(*) 能够快速返回。而 Innodb 内部没有这样一个计数器，需要我们手动统计记录数量，解决思路就是单独使用一张表

limit 1

如果可以确定仅仅检索一条，建议加上limit 1，其实ORM框架帮我们做到了这一点（查询单条的操作都会自动加上limit 1）。

EXISTS 和 IN的区分

SELECT * FROM A WHERE cc IN(SELECT cc FROM B)
SELECT * FROM A WHERE EXISTS (SELECT cc FROM B WHERE B.cc = A.cc)

哪个表小就用哪个表来驱动，A表小就用EXISTS，B表小就用IN。

多使用commit

慢查询日志

基本介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL。long_query_time的默认值为10，意思是运行 10s 以上的语句。默认情况下，Mysql数据库并不启动慢查询日志，需要我们手动来设置这个参数，当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件，也支持将日志记录写入数据库表。

各种操作

开启慢查询日志

配置项：slow_query_log

show variables like 'slov_query_log'  --查看慢查询日志是否开启
set GLOBAL slow_query_log = 'ON' --开启慢查询日志

如果状态值为OFF，可以使用 set GLOBAL slow_query_log = on 来开启，它会在datadir下产生一个xxx-slow.log的文件。

设置临界时间

配置项：long_query_time

show VARIABLES like 'long_query_time' --查看临界时间，单位为秒
set long_query_time=0.5 --设置临界时间(超过多少秒算慢查询)

一旦SQL超过了我们设置的临界时间就会被记录到xxx-slow.log中。

其他操作

SHOW GLOBAL STATUS LIKE '%Slow_queries%'; #查看系统中有多少条慢查询记录
#关闭慢查询日志
#配置文件
[mysqld]
slow_query_log=OFF  #持续
SET GLOBAL slow_query_log=off;  #临时，需要重启一下

慢查询分析工具：mysqldumpslow

https://zhuanlan.zhihu.com/p/106405711

优化MySQL服务器

优化服务器硬件

优化MySQL参数

参考博客：https://www.cnblogs.com/angryprogrammer/p/6667741.html
https://achang.blog.csdn.net/article/details/122706347

max_connections，最大客户端连接数

table_open_cache，表文件句柄缓存（表数据是存储在磁盘上的，缓存磁盘文件的句柄方便打开文件读取数据）

key_buffer_size，索引缓存大小（将从磁盘上读取的索引缓存到内存，可以设置大一些，有利于快速检索）

innodb_buffer_pool_size，Innodb存储引擎缓存池大小（对于Innodb来说最重要的一个配置，如果所有的表用的都是Innodb，那么甚至建议将该值设置到物理内存的80%，Innodb的很多性能提升如索引都是依靠这个）

innodb_file_per_table（innodb中，表数据存放在 .ibd 文件中，如果将该配置项设置为ON，那么一个表对应一个ibd文件，否则所有innodb共享表空间）
关联查询优化
基本优化
左外连接添加索引的情况：左边是驱动表，右边是被驱动表（右外连接正好反过来）。被驱动表加索引可以避免全表扫描，而驱动表加则不能。 ``sql SELECT * FROMtype` LEFT JOIN book ON type.card = book.card;

采用左外连接 (右外连接同理，只不过反过来，右边是驱动表，要给左边加索引)

ALTER TABLE book ADD INDEX Y(card); #【card是被驱动表】，可以避免全表扫描 SELECT FROM type LEFT JOIN book ON type.card = book.card;

左外连接LEFT JOIN 条件。对应右边是被驱动表，一定需要建立索引。

ALTER TABLE type ADD INDEX X (card); #【type是驱动表】，无法避免全表扫描 EXPLAIN SELECT SQL_NO_CACHE * FROM type LEFT JOIN book ON type.card = book.card;

**内连接添加索引的情况：优化器选择被驱动表和驱动表。只有一个索引的情况下，有索引的是被驱动表。两个都有的情况下，表小的为驱动表。(当然还是要根据实际情况的，也不是这么绝对)**
```sql
#采用内连接
#对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表
CREATE INDEX X ON `type`(card);
SELECT * FROM `type` INNER JOIN book ON type.card = book.card;
#只有一个字段有索引的话，谁有索引就会被确定为被驱动表
#当两个字段都存在索引的话，会选择小表作为驱动表，"小表驱动大表"

join原理

Simple Nested-Loop Join

这种效率非常低。

Index Nested-Loop Join（索引嵌套循环连接）

注：索引是非驱动表的索引，每次获取到驱动表A的元素后，都要通过索引回表查到非驱动表B的元素。

SELECT * FROM `type` LEFT JOIN book ON type.card = book.card;
#对应索引就是 book表的 card，为一个二级索引
#每次获取一个驱动表A的元素，就根据card回表到对应非驱动表B的元素

Block Nested-Loop Join

Hash Join

子查询优化

MySQL从4.1版本开始支持子查询，使用子查询可以进行SELECT语句的嵌套查询，即一个SELECT查询的结果作为另一个SELECT语句的条件。子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高。
① 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
② 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。
③ 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。
因此，在MySQL中，可以使用连接（JOIN）查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。

#查询班长的信息 班长有对应的索引
SELECT * FROM student stu1 
  WHERE stu1.`stuno` IN(
      SELECT monitor FROM class c
      WHERE  monitor IS NOT NULL;
  ); #子查询
SELECT stu1.* FROM student stu1 JOIN class c ON stu1.`stuno` = c.`monitor`
  WHERE c.`monitor` IS NOT NULL; #用Join代替

排序优化

基本介绍

CREATE INDEX idx_age_stuno_name ON student (age,stuno,NAME);
SELECT * FROM student WHERE age = 30 AND stuno <101000 ORDER BY NAME; #使用了索引
#注意这里跟索引失效不太一样，因为一个全部都是跟WHERE有关，这个是WHERE和ORDER BY
#有时候数据量大就不会使用索引，如果使用limit限制就会使用了
SELECT * FROM student ORDER BY age,stuno; #未使用
SELECT * FROM student ORDER BY age,stuno LIMIT 10; #使用了

FileSort算法

GROUP BY优化

分页查询优化

在索引上完成排序分页操作(只搜索主键)，最后根据主键关联回原表，查询所需要的其他列内容。
```
SELECT * FROM student,(SELECT id FROM student ORDER BY id LIMIT 200000,10) temp
WHERE t.id = a.id
```
该方案适用于主键自增的表，可以把limit查询转换成某个位置的查询。
```
SELECT * FROM student WHERE id > 2000000 LIMIT 10;
```
大表优化
限定查询的范围

禁止不带任何限制数据范围条件的查询语句。比如：我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内；

读/写分离

经典的数据库拆分方案，主库负责写，从库负责读。

垂直拆分

当数据量级达到千万级以上时，有时候我们需要把一个数据库切成多份，放到不同的数据库服务器上，减少对单一数据库服务器的访问压力。

水平拆分

使用Redis或Memcached作为缓存

explain语句

我们可以通过 explain语句来分析SQL语句执行前的执行计划（当执行SQL语句时，首先会分析、优化，形成执行计划）
MySQL优化 - 图72
由上图可看出此SQL语句是按照主键索引来检索的。
详细介绍： https://www.cnblogs.com/tufujie/p/9413852.html
https://www.jianshu.com/p/da16879611ea

show profile语句

Show Profile是MysQL提供的可以用来分析当前会话中SQL都做了什么、执行的资源消耗情况的工具，可用于sql调优的测量。默认情况下处于关闭状态，并保存最近15次的运行结果。
https://www.cnblogs.com/developer_chan/p/9231761.html

MySQL优化

MySQL优化

为什么需要优化

优化的整体架构

选择适合的DBMS

字段设计

表设计

关联表的设计

范式

第一范式：字段原子性

第二范式：确保表中的每列都和主键相关(可以是间接相关)

第三范式

范式的优点与缺点

反范式化

BCNF(巴斯范式)

拆分表

增加中间表

索引

缓存

分区

基本简介

MySQL提供的分区算法

hash(字段)

key(字段)

range算法

list算法

MySQL提供的分区管理算法

range/list

key/hash

水平分割和垂直分割

集群(库级优化)

基本介绍

架构图

MySQL主从复制

主库与从库的关系

原理

解决主从延迟问题

解决同步数据不一致的问题

用途

条件

典型SQL的优化

线上DDL

数据库导入语句

count(*)

limit 1

EXISTS 和 IN的区分

多使用commit

慢查询日志

基本介绍

各种操作

开启慢查询日志

设置临界时间

其他操作

慢查询分析工具：mysqldumpslow

优化MySQL服务器

优化服务器硬件

优化MySQL参数

关联查询优化

基本优化

采用左外连接 (右外连接同理，只不过反过来，右边是驱动表，要给左边加索引)

左外连接LEFT JOIN 条件。对应右边是被驱动表，一定需要建立索引。

join原理

Simple Nested-Loop Join

Index Nested-Loop Join（索引嵌套循环连接）

Block Nested-Loop Join

Hash Join

子查询优化

排序优化

基本介绍

FileSort算法

GROUP BY优化

分页查询优化

大表优化

使用Redis或Memcached作为缓存

explain语句

show profile语句