【SQL】【MySql】SQL性能优化

索引，索引！！！为经常查询的字段建索引！！
但也不能过多地建索引。insert和delete等改变表记录的操作会导致索引重排，增加数据库负担。

优化目标

1. 减少 IO 次数
   IO永远是数据库最容易瓶颈的地方，这是由数据库的职责所决定的，大部分数据库操作中超过90%的时间都是 IO 操作所占用的，减少 IO 次数是 SQL 优化中需要第一优先考虑，当然，也是收效最明显的优化手段。
2. 降低 CPU 计算
   除了 IO 瓶颈之外，SQL优化中需要考虑的就是 CPU 运算量的优化了。order by, group by,distinct … 都是消耗 CPU 的大户(这些操作基本上都是 CPU 处理内存中的数据比较运算)。当我们的 IO 优化做到一定阶段之后，降低 CPU 计算也就成为了我们 SQL 优化的重要目标。

优化方法

1. 改变 SQL 执行计划
   明确了优化目标之后，我们需要确定达到我们目标的方法。对于 SQL 语句来说，达到上述2个目标的方法其实只有一个，那就是改变 SQL 的执行计划，让他尽量“少走弯路”，尽量通过各种“捷径”来找到我们需要的数据，以达到 “减少 IO 次数” 和 “降低 CPU 计算” 的目标。
2. 分析复杂的SQL语句

使用 explain

mysql> explain select * from (select * from ( select * from t3 where id=3952602) a) b;
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
| id | select_type | table      | type   | possible_keys     | key     | key_len | ref  | rows | Extra |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+
|  1 | PRIMARY     | <derived2> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  2 | DERIVED     | <derived3> | system | NULL              | NULL    | NULL    | NULL |    1 |       |
|  3 | DERIVED     | t3         | const  | PRIMARY,idx_t3_id | PRIMARY | 4       |      |    1 |       |
+----+-------------+------------+--------+-------------------+---------+---------+------+------+-------+

很显然这条SQL是从里向外的执行,就是从id=3 向上执行。

show

show tables或show tables from database_name; // 显示当前数据库中所有表的名称

show databases; // 显示mysql中所有数据库的名称

show columns from table_name from database_name; 或MySQL show columns from database_name.table_name; // 显示表中列名称

show grants for user_name@localhost; // 显示一个用户的权限，显示结果类似于grant 命令

show index from table_name; // 显示表的索引

show status; // 显示一些系统特定资源的信息，例如，正在运行的线程数量

show variables; // 显示系统变量的名称和值
show processlist; // 显示系统中正在运行的所有进程，也就是当前正在执行的查询。

show table status; // 显示当前使用或者指定的database中的每个表的信息。信息包括表类型和表的最新更新时间

show privileges; // 显示服务器所支持的不同权限

show create database database_name; // 显示create database 语句是否能够创建指定的数据库

show create table table_name; // 显示create database 语句是否能够创建指定的数据库

show engies; // 显示安装以后可用的存储引擎和默认引擎。

show innodb status; // 显示innoDB存储引擎的状态

show logs; // 显示BDB存储引擎的日志

show warnings; // 显示最后一个执行的语句所产生的错误、警告和通知

show errors; // 只显示最后一个执行语句所产生的错误

开启缓存查询

对于完全相同的sql，使用已经存在的执行计划，从而跳过解析和生成执行计划的过程。

应用场景：有一个不经常变更的表，且服务器收到该表的大量相同查询。对于频繁更新的表，查询缓存是不适合的

Mysql 判断是否命中缓存的办法很简单，首先会将要缓存的结果放在引用表中，然后使用查询语句，数据库名称，客户端协议的版本等因素算出一个hash值，这个hash值与引用表中的结果相关联。如果在执行查询时，根据一些相关的条件算出的hash值能与引用表中的数据相关联，则表示查询命中

查询必须是完全相同的(逐字节相同)才能够被认为是相同的。另外，同样的查询字符串由于其它原因可能认为是不同的。使用不同的数据库、不同的协议版本或者不同 默认字符集的查询被认为是不同的查询并且分别进行缓存。

下面sql查询缓存认为是不同的：

    1. SELECT * FROM tbl_name  
    2. Select * from tbl_name  

缓存机制失效的场景

如果查询语句中包含一些不确定因素时（例如包含 函数Current()），该查询不会被缓存,不确定因素主要包含以下情况。

• 引用了一些返回值不确定的函数
• 引用自定义函数(UDFs)。
• 引用自定义变量。
• 引用mysql系统数据库中的表。
• 下面方式中的任何一种：

SELECT ...IN SHARE MODE
SELECT ...FOR UPDATE
SELECT ...INTO OUTFILE ...
SELECT ...INTO DUMPFILE ...
SELECT * FROM ...WHERE autoincrement_col IS NULL

• 使用TEMPORARY表。
• 不使用任何表。
• 用户有某个表的列级别权限。

额外的消耗

如果使用查询缓存，在进行读写操作时会带来额外的资源消耗，消耗主要体现在以下几个方面

• 查询的时候会检查是否命中缓存，这个消耗相对较小.
• 如果没有命中查询缓存，MYSQL会判断该查询是否可以被缓存，而且系统中还没有对应的缓存，则会将其结果写入查询缓存。
• 如果一个表被更改了，那么使用那个表的所有缓冲查询将不再有效，并且从缓冲区中移出。这包括那些映射到改变了的表的使用MERGE表的查询。一个表可以被许多类型的语句更改，例如INSERT、UPDATE、DELETE、TRUNCATE、ALTER TABLE、DROP TABLE或DROP DATABASE。

对于InnoDB而言，事物的一些特性还会限制查询缓存的使用。当在事物A中修改了B表时，因为在事物提交之前，对B表的修改对其他的事物而言是不可见的。为了保证缓存结果的正确性，InnoDB采取的措施让所有涉及到该B表的查询在事物A提交之前是不可缓存的。如果A事物长时间运行，会严重影响查询缓存的命中率

查询缓存的空间不要设置的太大。

因为查询缓存是靠一个全局锁操作保护的，如果查询缓存配置的内存比较大且里面存放了大量的查询结果，当查询缓存失效的时候，会长时间的持有这个全局锁。因为查询缓存的命中检测操作以及缓存失效检测也都依赖这个全局锁，所以可能会导致系统僵死的情况。

静态表速度更快

定长类型和变长类型

CHAR(M)定义的列的长度为固定的，M取值可以为0～255之间，当保存CHAR值时，在它们的右边填充空格以达到指定的长度。当检索到CHAR值时，尾部的空格被删除掉。在存储或检索过程中不进行大小写转换。CHAR存储定长数据很方便，CHAR字段上的索引效率级高，比如定义char(10)，那么不论你存储的数据是否达到了10个字节，都要占去10个字节的空间,不足的自动用空格填充。

VARCHAR(M)定义的列的长度为可变长字符串，M取值可以为0~65535之间，(VARCHAR的最大有效长度由最大行大小和使用的字符集确定。整体最大长度是65,532字节）。VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)。VARCHAR值保存时不进行填充。当值保存和检索时尾部的空格仍保留，符合标准SQL。varchar存储变长数据，但存储效率没有CHAR高。

如果一个字段可能的值是不固定长度的，我们只知道它不可能超过10个字符，把它定义为 VARCHAR(10)是最合算的。VARCHAR类型的实际长度是它的值的实际长度+1。空间上考虑，用varchar合适；从效率上考虑，用char合适，关键是根据实际情况找到权衡点。

VARCHAR和TEXT、BlOB类型

VARCHAR，BLOB和TEXT类型是变长类型，对于其存储需求取决于列值的实际长度(在前面的表格中用L表示)，而不是取决于类型的最大可能尺寸。

BLOB和TEXT类型需要1，2，3或4个字节来记录列值的长度，这取决于类型的最大可能长度。VARCHAR需要定义大小，有65535字节的最大限制；TEXT则不需要。如果你把一个超过列类型最大长度的值赋给一个BLOB或TEXT列，值被截断以适合它。

一个BLOB是一个能保存可变数量的数据的二进制的大对象。4个BLOB类型TINYBLOB、BLOB、MEDIUMBLOB和LONGBLOB仅仅在他们能保存值的最大长度方面有所不同。

BLOB 可以储存图片,TEXT不行，TEXT只能储存纯文本文件。

在BLOB和TEXT类型之间的唯一差别是对BLOB值的排序和比较以大小写敏感方式执行，而对TEXT值是大小写不敏感的。换句话说，一个TEXT是一个大小写不敏感的BLOB。

效率来说基本是char>varchar>text，但是如果使用的是Innodb引擎的话，推荐使用varchar代替char

char和varchar可以有默认值，text不能指定默认值

静态表和动态表

静态表字段长度固定，自动填充，读写速度很快，便于缓存和修复，但比较占硬盘，动态表是字段长度不固定，节省硬盘，但更复杂，容易产生碎片，速度慢，出问题后不容易重建。

基本原则

1. in和exist
   select * from 表A where id in (select id from 表B)
   这句相当于
   select * from 表A where exists(select * from 表B where 表B.id=表A.id)
   对于表A的每一条数据,都执行select * from 表B where 表B.id=表A.id的存在性判断,如果表B中存在表A当前行相同的id,则exists为真,该行显示,否则不显示
   区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。
   所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况

2. 尽量少 join
   MySQL 的优势在于简单，但这在某些方面其实也是其劣势。MySQL 优化器效率高，但是由于其统计信息的量有限，优化器工作过程出现偏差的可能性也就更多。对于复杂的多表 Join，一方面由于其优化器受限，再者在 Join 这方面所下的功夫还不够，所以性能表现离 Oracle 等关系型数据库前辈还是有一定距离。但如果是简单的单表查询，这一差距就会极小甚至在有些场景下要优于这些数据库前辈。

3. 尽量少排序
   排序操作会消耗较多的 CPU 资源，所以减少排序可以在缓存命中率高等 IO 能力足够的场景下会较大影响 SQL 的响应时间。
   对于MySQL来说，减少排序有多种办法，比如：
   上面误区中提到的通过利用索引来排序的方式进行优化
   减少参与排序的记录条数
   非必要不对数据进行排序

4. 尽量避免 select *
   大多数时候是不会影响到 IO 量，当我们的查询结果仅仅只需要在索引中就能找到的时候，还是会极大减少 IO 量的。

5. 尽量用 join 代替子查询
   虽然 Join 性能并不佳，但是和 MySQL 的子查询比起来还是有非常大的性能优势。MySQL 的子查询执行计划一直存在较大的问题，虽然这个问题已经存在多年，但是到目前已经发布的所有稳定版本中都普遍存在，一直没有太大改善。虽然官方也在很早就承认这一问题，并且承诺尽快解决，但是至少到目前为止我们还没有看到哪一个版本较好的解决了这一问题。
   MySQL需要为内层查询语句的查询结果建立一个临时表。然后外层查询语句在临时表中查询记录。查询完毕后，MySQL需要插销这些临时表。所以在MySQL中可以使用连接查询来代替子查询。连接查询不需要建立临时表，其速度比子查询要快。

6. 尽量少 or
   当 where 子句中存在多个条件以“或”并存的时候，MySQL 的优化器并没有很好的解决其执行计划优化问题，再加上 MySQL 特有的 SQL 与 Storage 分层架构方式，造成了其性能比较低下，很多时候使用 union all 或者是union(必要的时候)的方式来代替“or”会得到更好的效果。

7. 尽量用 union all 代替 union
   union 和 union all 的差异主要是前者需要将两个(或者多个)结果集合并后再进行唯一性过滤操作，这就会涉及到排序，增加大量的 CPU 运算，加大资源消耗及延迟。所以当我们可以确认不可能出现重复结果集或者不在乎重复结果集的时候，尽量使用 union all 而不是 union。

8. 尽量早过滤
   这一优化策略其实最常见于索引的优化设计中(将过滤性更好的字段放得更靠前)。
   在 SQL 编写中同样可以使用这一原则来优化一些 Join 的 SQL。比如我们在多个表进行分页数据查询的时候，我们最好是能够在一个表上先过滤好数据分好页，然后再用分好页的结果集与另外的表 Join，这样可以尽可能多的减少不必要的 IO 操作，大大节省 IO 操作所消耗的时间。

9. 避免类型转换
   这里所说的“类型转换”是指 where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，人为在column_name 上通过转换函数进行转换，直接导致 MySQL(实际上其他数据库也会有同样的问题)无法使用索引，如果非要转换，应该在传入的参数上进行转换由数据库自己进行转换。
   如果我们传入的数据类型和字段类型不一致，同时我们又没有做任何类型转换处理，MySQL 可能会自己对我们的数据进行类型转换操作，也可能不进行处理而交由存储引擎去处理，这样一来，就会出现索引无法使用的情况而造成执行计划问题。

10. 优先优化高并发的 SQL，而不是执行频率低某些“大”SQL
    对于破坏性来说，高并发的 SQL 总是会比低频率的来得大，因为高并发的 SQL 一旦出现问题，甚至不会给我们任何喘息的机会就会将系统压跨。而对于一些虽然需要消耗大量 IO 而且响应很慢的 SQL，由于频率低，即使遇到，最多就是让整个系统响应慢一点，但至少可能撑一会儿，让我们有缓冲的机会。

11. 从全局出发优化，而不是片面调整
    SQL 优化不能是单独针对某一个进行，而应充分考虑系统中所有的 SQL，尤其是在通过调整索引优化 SQL 的执行计划的时候，千万不能顾此失彼，因小失大。

12. 尽可能对每一条运行在数据库中的SQL进行 explain
    优化 SQL，需要做到心中有数，知道 SQL 的执行计划才能判断是否有优化余地，才能判断是否存在执行计划问题。在对数据库中运行的 SQL 进行了一段时间的优化之后，很明显的问题 SQL 可能已经很少了，大多都需要去发掘，这时候就需要进行大量的 explain 操作收集执行计划，并判断是否需要进行优化。

索引优化

1. MySQL只会使用前缀，例如key(a, b) …where b=5 将使用不到索引。
2. 要选择性的使用索引。在变化很少的列上使用索引并不是很好，例如性别列。
3. 在Unique列上定义Unique index。
4. 避免建立使用不到的索引。
5. 在Btree index中（InnoDB使用Btree），可以在需要排序的列上建立索引。
6. 避免重复的索引。
7. 避免在已有索引的前缀上建立索引。例如：如果存在index（a，b）则去掉index（a）。
8. 控制单个索引的长度。使用key（name（8））在数据的前面几个字符建立索引。
9. 越是短的键值越好，最好使用integer。
10. 在查询中要使用到索引（使用explain查看），可以减少读磁盘的次数，加速读取数据。
11. 相近的键值比随机好。Auto_increment就比uuid好。
12. Optimize table可以压缩和排序index，注意不要频繁运行。
13. Analyze table可以更新数据。