【网站构建】关系型数据库MySQL-技术圈

Start：关注本公众号后，可直接联系后台获取排版美化的详细文档！

Hints：本篇文章所编纂的资料均来自网络，特此感谢参与奉献的有关人员。

MySQL常用架构：主从热备

https://blog.csdn.net/weixin_37838921/article/details/104033403

https://www.cnblogs.com/honeylemon/p/10591899.html

http://www.cppcns.com/shujuku/mysql/203618.html

MySQL逻辑架构

客户端层：连接处理、授权认证、安全管理

核心服务层：查询解析、分析、优化、缓存、内置函数（时间、数学、加密等函数）、存储过程、触发器、视图

存储引擎层：数据的存储和提取

MySQL工作过程

客户端-服务端通信协议：

MySQL客户端/服务端通信协议是“半双工”的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一端要接收完整个消息才能响应它，所以我们无法也无须将一个消息切成小块独立发送，也没有办法进行流量控制。

客户端用一个单独的数据包将查询请求发送给服务器，所以当查询语句很长的时候，需要设置max_allowed_packet参数。但是需要注意的是，如果查询实在是太大，服务端会拒绝接收更多数据并抛出异常。

与之相反的是，服务器响应给用户的数据通常会很多，由多个数据包组成。但是当服务器响应客户端请求时，客户端必须完整的接收整个返回结果，而不能简单的只取前面几条结果，然后让服务器停止发送。因而在实际开发中，尽量保持查询简单且只返回必需的数据，减小通信间数据包的大小和数量是一个非常好的习惯，这也是查询中尽量避免使用SELECT *以及加上LIMIT限制的原因之一。

查询缓存：

在解析一个查询语句前，如果查询缓存是打开的，那么MySQL会检查这个查询语句是否命中查询缓存中的数据。如果当前查询恰好命中查询缓存，在检查一次用户权限后直接返回缓存中的结果。这种情况下，查询不会被解析，也不会生成执行计划，更不会执行。

MySQL将缓存存放在一个引用表（不要理解成table，可以认为是类似于HashMap的数据结构），通过一个哈希值索引，这个哈希值通过查询本身、当前要查询的数据库、客户端协议版本号等一些可能影响结果的信息计算得来。所以两个查询在任何字符上的不同（例如：空格、注释），都会导致缓存不会命中。

如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysql库中的系统表，其查询结果都不会被缓存。比如函数NOW()或者CURRENT_DATE()会因为不同的查询时间，返回不同的查询结果，再比如包含CURRENT_USER或者CONNECION_ID()的查询语句会因为不同的用户而返回不同的结果，将这样的查询结果缓存起来没有任何的意义。

MySQL的查询缓存系统会跟踪查询中涉及的每个表，如果这些表（数据或结构）发生变化，那么和这张表相关的所有缓存数据都将失效。正因为如此，在任何的写操作时，MySQL必须将对应表的所有缓存都设置为失效。如果查询缓存非常大或者碎片很多，这个操作就可能带来很大的系统消耗，甚至导致系统僵死一会儿。而且查询缓存对系统的额外消耗也不仅仅在写操作，读操作也不例外：

1任何的查询语句在开始之前都必须经过检查，即使这条SQL语句永远不会命中缓存

2如果查询结果可以被缓存，那么执行完成后，会将结果存入缓存，也会带来额外的系统消耗

基于此，我们要知道并不是什么情况下查询缓存都会提高系统性能，缓存和失效都会带来额外消耗，只有当缓存带来的资源节约大于其本身消耗的资源时，才会给系统带来性能提升。但要如何评估打开缓存是否能够带来性能提升是一件非常困难的事情，也不在本文讨论的范畴内。如果系统确实存在一些性能问题，可以尝试打开查询缓存，并在数据库设计上做一些优化，比如：

1用多个小表代替一个大表，注意不要过度设计

2批量插入代替循环单条插入

3合理控制缓存空间大小，一般来说其大小设置为几十兆比较合适

4可以通过SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

语法解析和预处理：

MySQL通过关键字将SQL语句进行解析，并生成一颗对应的解析树。这个过程解析器主要通过语法规则来验证和解析。比如SQL中是否使用了错误的关键字或者关键字的顺序是否正确等等。预处理则会根据MySQL规则进一步检查解析树是否合法。比如检查要查询的数据表和数据列是否存在等等。

查询优化：

经过语法解析和预处理后合法的语法树将由优化器转化成查询计划。多数情况下，一条查询可以有很多种执行方式，最后都返回相应的结果。优化器的作用就是找到这其中最好的执行计划。MySQL使用基于成本的优化器，它尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个。在MySQL可以通过查询当前会话的last_query_cost的值来得到其计算当前查询的成本。

有非常多的原因会导致MySQL选择错误的执行计划，比如统计信息不准确、不会考虑不受其控制的操作成本（用户自定义函数、存储过程）、MySQL认为的最优跟我们想的不一样（我们希望执行时间尽可能短，但MySQL值选择它认为成本小的，但成本小并不意味着执行时间短）等等。

MySQL常见的优化策略：

1重新定义表的关联顺序（多张表关联查询时，并不一定按照SQL中指定的顺序进行，但有一些技巧可以指定关联顺序）

2优化MIN()和MAX()函数（找某列的最小值，如果该列有索引，只需要查找B+Tree索引最左端，反之则可以找到最大值，具体原理见下文）

3提前终止查询（比如：使用Limit时，查找到满足数量的结果集后会立即终止查询）

4优化排序（在老版本MySQL会使用两次传输排序，即先读取行指针和需要排序的字段在内存中对其排序，然后再根据排序结果去读取数据行，而新版本采用的是单次传输排序，也就是一次读取所有的数据行，然后根据给定的列排序。对于I/O密集型应用，效率会高很多）

查询执行引擎

在完成解析和优化阶段以后，MySQL会生成对应的执行计划，查询执行引擎根据执行计划给出的指令逐步执行得出结果。整个执行过程的大部分操作均是通过调用存储引擎实现的接口来完成，这些接口被称为handler API。查询过程中的每一张表由一个handler实例表示。实际上，MySQL在查询优化阶段就为每一张表创建了一个handler实例，优化器可以根据这些实例的接口来获取表的相关信息，包括表的所有列名、索引统计信息等。存储引擎接口提供了非常丰富的功能，但其底层仅有几十个接口，这些接口像搭积木一样完成了一次查询的大部分操作。

完整工作过程：

1客户端向MySQL服务器发送一条查询请求

2服务器首先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段【是否缓存，需要提前设置】

3服务器进行SQL解析、预处理

4优化器生成对应的执行计划

5MySQL根据执行计划，调用存储引擎的API来执行查询

6将结果返回给客户端，同时缓存查询结果

MySQL关键概念

存储引擎：存储引擎就是特定的数据存储格式

innodb擅长处理并发的。因为它使用了行级锁定，只该行锁了，其它行没有锁。innodb擅长事务、数据的完整性及高并发处理，不擅长快速插入（插入前要排序，消耗时间）和检索。

MyISAM：ISAM,Indexed Sequential Access Method(索引顺序存取方法)的缩写，是一种文件系统。高速查询及插入。擅长插入和查询。

Archive：存档型，仅提供插入和查询操作。非常高效阻塞的插入和查询。

Memory：内存型，数据存储于内存中，存储引擎。缓存型存储引擎。

插件式存储引擎：用C和C++开发的存储引擎。

范式：

第一范式：具有原子性，确保每列保持原子性。

第二范式：主键列与非主键列遵循完全函数依赖关系，确保表中的每列都和主键相关。

第三范式：非主键列之间没有传递函数依赖关系索引，确保每列都和主键列直接相关,而不是间接相关。

逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能。

字段类型：

字段类型尽可能小（占用存储空间少）、尽可能定长（占用存储空间固定）、尽可能使用整数。

（1）整型

MySQL数据库支持五种整型类型，包括：TINYINT、SMALLINT、MEDIUMINT、INT和BIGINT五种。

整型类型占用空间和取值范围

类型字节最小值最大值

TINYINT 1 有符号：-128 无符号：0 有符号：127 无符号：255

SMALLINT 2有符号：-32768无符号：0有符号：32767无符号：65535

MEDIUMINT 3有符号：-8388608无符号：0有符号：8388607无符号：16777215

INT/INTEGER 4有符号：-2147483648无符号：0有符号：2147483647无符号：4294967295

BIGINT 8 有符号：-9223372036854775808无符号：0 有符号：9223372036854775807无符号：18446744073709551615

五种整型的适用场景：

TINYINT，年龄，包含在0~255之间；

SMALLINT，端口号，包含在0~65535之间；

MEDIUMINT，中小型网站注册会员，1600万够用；

INT，身份证编号，42亿可以用很久；

BIGINT，Twitter微博量，几百亿

（2）浮点型(非精确)

MySQL数据库支持两种浮点类型：FLOAT(单精度)和DOUBLE(双精度)两种

浮点型(非精确)占用空间和取值范围

类型字节范围

FLOAT 4 正数范围：1.175494351E-38~3.402823466E+38，负数范围：-3.402823466E+38~-1.175494351E-38

DOUBLE 8 正数范围：1.7976931348623157E-308~2.2250738585072014E+308

负数范围：-2.2250738585072014E+308~-1.7976931348623157E-308

（3）定点型(精确)

浮点型由于内部的存储方式是数值，导致它在一定程度上取得的是近似值而非精确值。如果使用定点型，那么就可以精确取得小数部分，因为它内部存储方式是字符串形式。

定点型(精确)占用空间和取值范围

类型字节范围

DECIMAL/NUMERIC M+2 M最大65位，D最大30位。

创建一个定点型格式：DECIMAL(M,D)，表示小数点D位，整数部分M位及M位内。

2.列类型之日期

MySQL数据库中有五个可用的日期时间数据类型，分别为：DATE、DATETIME、TIME、YEAR、TIMESTAMP。

日期时间类型占用空间和取值范围

类型字节最小值最大值

YEAR 1 1901 2155

TIME 3 -838:59:59838:59:59

DATE 4 1000-01-01 9999-12-31

TIMESTAMP 4 1970-01-01 00:00:00 2038-01-19 03:14:07

DATETIME 8 1000-01-01 00:00:00 9999-12-31 23:59:59

TIMESTAMP有几个特点：

a.当更新一条数据的时候，设置此类型根据当前系统更新可自动更新时间；

b.如果插入一条NULL，也会自动插入当前系统时间；

c.创建字段时，系统会自动给一个默认值；

d.会根据当前时区来存储和查询时间，存储时对当前时区进行转换，查询时再转换为当前的时区。

//查看当前时区

SHOW VARIABLES LIKE 'time_zone';

//设置为东九区，查询时间就会加1小时

SET time_zone='+9:00';

DATE占用3个字节，包含年月日，范围和DATETIME一样。DATE长度是0，无法设置。

YEAR占用1个字节，包年年份，长度默认为4位，无法设置。

TIME占用3个字节，包含时分秒，长度0到6之间，用于设置微秒。对于TIME的范围的时是-838到838的原因，是因为TIME类型不但可以保存一天的时，还可以包含时间之间的间隔。

综上考虑：使用datetime，当然也可以使用int（11）来保存时间戳。

关于INT(11)存放时间戳的优点如下：

a.INT占4个字节，DATETIME占8个字节；

b.INT存储索引的空间比DATETIME小，查询快，排序效率高；

c.在计算机时间差等范围问题，比较方便。

3.列类型之字符

字符集校对规则utf8_general_ci表示校对时不区分大小写，相对的cs表示区分大小写。还有一个bin结尾的是字节比较。而general是地区名，这里是通用，utf8表示编码。如果是gbk，可以使用gbk_chinese_ci，如果是utf8则用utf8_general。MySQL提供了多种对字符数据的存储类型，包括：CHAR、VARCHAR、VARBINARY、BLOB、TEXT、ENUM和SET等多种字符类型。

（1）CHAR是保存定长字符串，而VARCHAR则是保存变长字符串。CHAR(5)表示必须保存5个字符，而VARCHAR(5)则表示最大保存字符为5。如果是UTF8编码下，长度为5的CHAR类型，最多可以存储15字节，也就是5个汉字的内容。因为一个汉字占3个字节。

由于CHAR类型是定长，MySQL会根据定义的长度进行分配空间，在处理速度上比VARCHAR快的多，所以适合存储例如手机、身份证这种定长的字符，否则就会造成浪费。那么CHAR类型最大可以插入255个字符，最多可以存储765个字节。

（2）BINARY和VARBINARY是采用二进制存储的，没有字符集概念，意义在于防止字符集的问题导致数据丢失，存储中文会占用两个字符，会乱码，半截会问号。因为是采用二进制存储，在比较字符和排序的时候，都是二进制进行的，所以只有需要操作二进制时才需要使用。

（3）八种适合文本内容的大数据类型：TINYTEXT、TEXT、MEDIUMTEXT、LONGTEXT、TINYBLOG、BLOB、MEDIUMTEXT、LONGTEXT。

综上：短文本定长用char，变长用varchar，长文本用text

4.列类型之属性

无符号(UNSIGNED)和填充零(ZEROFILL)，还有是否为空、默认值、主键、自动编号。

严格模式

我们使用的是WAMP集成环境，默认安装的情况下，是非严格模式，用于部署阶段。而开发调试阶段，强烈建议使用严格模式，方便开发中调试将问题及时暴露出来。因为在非严格模式下将NULL插入NOTNULL等非法操作都是被运行的。设置严格模式只要打开my.ini文件，在末尾添加一句：

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

然后，重启服务器即可。检查SQL_MODE状态

SELECT @@global.sql_mode;

锁：

锁的概念：当客户端操作表（记录）时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理。

操作方面：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）

写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读，写操作。

锁定粒度（范围）：

行级：提升并发性，锁本身开销大

表级：不利于并发性，锁本身开销小。

索引

MySQL索引相关的数据结构和算法：

MySQL索引采用的数据结构是B+Tree, 而B+Tree就是一种多路搜索树。

MySQL将每个节点的大小设置为一个页的整数倍. 也就是在节点空间大小一定的情况下，每个节点可以存储更多的内结点，这样每个结点能索引的范围更大更精确。所有的叶子节点使用指针链接的好处是可以进行区间访问.

页是计算机管理存储器的逻辑块，硬件及OS往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页（许多OS中，页的大小通常为4K）。主存和磁盘以页为单位交换数据。当程序要读取的数据不在主存中时，会触发一个缺页异常，此时系统会向磁盘发出读盘信号，磁盘会找到数据的起始位置并向后连续读取一页或几页载入内存中，然后一起返回，程序继续运行。

MySQL巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次I/O就可以完全载入。为了达到这个目的，每次新建节点时，直接申请一个页的空间，这样就保证一个节点物理上也存储在一个页里，加之计算机存储分配都是按页对齐的，就实现了读取一个节点只需一次I/O。假设B+Tree的高度为h，一次检索最多需要h-1次I/O（根节点常驻内存），复杂度O(h) = O(logmN)。实际应用场景中，M通常较大，常常超过100，因此树的高度一般都比较小，通常不超过3。

索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有了索引的“最左原则”。

索引设计：

从索引的定义方式和用途中来看：主键索引，唯一索引，普通索引，全文索引。

无论任何类型，都是通过建立关键字与位置的对应关系来实现的。索引是通过关键字找对应的记录的地址。

以上类型的差异：对索引关键字的要求不同。

关键字：记录的部分数据（某个字段，某些字段，某个字段的一部分）。

普通索引,index：对关键字没有要求。

唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。

主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。

全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

关键字含义：可以是某个字段，也可以是某些字段。如果一个索引通过在多个字段上提取的关键字，称之为复合索引。命令:alter table exp add index (field1, field2);

PS：这里主键索引和唯一索引的区别在于：主键索引不能为空值，唯一索引允许空值；主键索引在一张表内只能创建一个，唯一索引可以创建多个。主键索引肯定是唯一索引，但唯一索引不一定是主键索引。

索引原则：如果索引不遵循使用原则，则可能导致索引无效。

（1）列独立

如果需要某个字段上使用索引，则需要在字段参与的表达中，保证字段独立在一侧。

（2）左原则

Like：匹配模式必须要左边确定不能以通配符开头。

（3）OR的使用

必须要保证 OR 两端的条件都存在可以用的索引，该查询才可以使用索引。

（4）MySQL智能选择

即使满足了上面说原则，MySQL也能弃用索引：查询即使使用索引，会导致出现大量的随机IO，相对于从数据记录的第一条遍历到最后一条的顺序IO开销，还要大。

综上归纳：

a、不要过度索引。索引越多，占用空间越大，反而性能变慢；

b.只对WHERE子句中频繁使用的建立索引；

c.尽可能使用唯一索引，重复值越少，索引效果越强；

d.使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；

e.充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边。

f.索引存在，如果没有满足使用原则，也会导致索引无效：

5.索引的使用场景

（1）索引检索：检索数据时使用索引。

（2）索引排序

如果order by 排序需要的字段上存在索引，则可能使用到索引。

（3）索引覆盖

索引拥有的关键字内容，覆盖了查询所需要的全部数据，此时，就不需要在数据区获取数据，仅仅在索引区即可。覆盖就是直接在索引区获取内容，而不需要在数据区获取。

6.前缀索引

前缀索引是建立索引关键字一种方案。通常会使用字段的整体作为索引关键字。有时，即使使用字段前部分数据，也可以去识别某些记录。就比如一个班级里，我要找王xx，假如姓王的只有1个人，那么就可以建一个前缀索引，就是王。

7.全文索引

该类型的索引特殊在：关键字的创建上。是为了解决 like‘%keyword%’这类查询的匹配问题。（mysql的全文索引几乎不用，因为它不支持中文，我们应该使用sphinx全文索引）

MySQL性能优化

整体优化方向：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

具体优化建议：

1不要轻易打开查询缓存，特别是写密集型应用。如果需要，可以将query_cache_type设置为DEMAND，这时只有加入SQL_CACHE的查询才会走缓存，其他查询则不会，这样可以非常自由地控制哪些查询需要被缓存。

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

3 把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。

4对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

5UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

5没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

6 TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

7大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

8尽量避免使用SELECT *，schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

9尽量加上LIMIT限制查询返回数量

10索引是提高MySQL查询性能的一个重要途径，但过多的索引可能会导致过高的磁盘使用率以及过高的内存占用，从而影响应用程序的整体性能。

11 MySQL不会使用索引的情况：“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数。比如：select * from where id + 1 = 5

12使用前缀索引，如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率。

13 多列索引和索引顺序问题：在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如MySQL5.0之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略。

select film_id,actor_id from film_actor where actor_id = 1 or film_id = 1

老版本的MySQL会随机选择一个索引，但新版本做如下的优化：

select film_id,actor_id from film_actor where actor_id = 1

union all

select film_id,actor_id from film_actor where film_id = 1 and actor_id <> 1

-出现多个索引做相交操作时（多个AND条件），通常来说一个包含所有相关列的索引要优于多个独立索引。

-出现多个索引做联合操作时（多个OR条件），对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高。所以这种情况下还不如走全表扫描

索引的顺序对于查询是至关重要的，很明显应该把选择性更高的字段放到索引的前面，这样通过第一个字段就可以过滤掉大多数不符合条件的数据。

14避免多个范围条件

select user.* from user where login_time > '2017-04-01' and age between 18 and 30;

这个查询有一个问题：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们。

覆盖索引：

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

-条目远小于数据行大小，如果只读取索引，极大减少数据访问量

-引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

16 使用索引扫描来排序：扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。

在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。

只有当索引的列顺序和ORDER BY子句的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有ORDER BY子句引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求（有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例），其他情况下都需要执行排序操作，而无法利用索引排序。

17冗余和重复索引：冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。

18 删除长期未使用的索引：索引并不总是最好的工具，只有当索引帮助提高查询速度带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，简单的全表扫描更高效。对于中到大型的表，索引就非常有效。对于超大型的表，建立和维护索引的代价随之增长，这时候其他技术也许更有效，比如分区表。

19如果要统计行数，直接使用COUNT(*)，意义清晰，且性能更好。

20 优化关联查询：

- 确保ON和USING字句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列c关联的时候，如果优化器关联的顺序是A、B，那么就不需要在A表的对应列上创建索引。没有用到的索引会带来额外的负担，一般来说，除非有其他理由，只需要在关联顺序中的第二张表的相应列上创建索引（具体原因下文分析）。

- 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列，这样MySQL才有可能使用索引来优化。

当前MySQL关联执行的策略非常简单，它对任何的关联都执行嵌套循环关联操作，即先在一个表中循环取出单条数据，然后在嵌套循环到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为为止。

21优化LIMIT分页

SELECT film_id,description FROM film ORDER BY title LIMIT 50,5;

如果这张表非常大，那么这个查询最好改成下面的样子：

SELECT film.film_id,film.description

FROM film INNER JOIN (

SELECT film_id FROM film ORDER BY title LIMIT 50,5

) AS tmp USING(film_id);

这里的延迟关联将大大提升查询效率，让MySQL扫描尽可能少的页面，获取需要访问的记录后在根据关联列回原表查询所需要的列。

22优化UNION

MySQL处理UNION的策略是先创建临时表，然后再把各个查询结果插入到临时表中，最后再来做查询。因此很多优化策略在UNION查询中都没有办法很好的时候。经常需要手动将WHERE、LIMIT、ORDER BY等字句“下推”到各个子查询中，以便优化器可以充分利用这些条件先优化。

除非确实需要服务器去重，否则就一定要使用UNION ALL，如果没有ALL关键字，MySQL会给临时表加上DISTINCT选项，这会导致整个临时表的数据做唯一性检查，这样做的代价非常高。当然即使使用ALL关键字，MySQL总是将结果放入临时表，然后再读出，再返回给客户端。虽然很多时候没有这个必要，比如有时候可以直接把每个子查询的结果返回给客户端。

参考链接

https://blog.csdn.net/weixin_37838921/article/details/104033403

https://www.cnblogs.com/honeylemon/p/10591899.html

http://www.cppcns.com/shujuku/mysql/203618.html

https://www.cnblogs.com/sharpest/p/10390035.html

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

https://blog.csdn.net/qq_35642036/article/details/82820129

https://www.cnblogs.com/eric-fang/p/9285093.html

公众号二维码

End：如果有兴趣了解金融量化交易和其他数据分析的实用技术，欢迎关注本公众号