MySQL优化

MySQL优化

数据库优化方向

可以从这几个维度回答这个问题：
分析慢查询日志
使用explain查看执行计划
索引优化
深分页优化
避免全表扫描
避免返回不必要的数据（如select具体字段而不是select*）
使用合适的数据类型（如可以使用int类型的话，就不要设计为varchar）
优化sql结构（如join优化等等）
适当分批量进行 (如批量更新、删除)
定期清理无用的数据
适当分库分表
读写分离

为什么会有慢查询

参考链接：https://mp.weixin.qq.com/s?__biz=MzkyMzU5Mzk1NQ==&mid=2247506481&idx=1&sn=ff69fdb1ca325538ca382b6f3ac372b8&source=41#wechat_redirect

1）SQL没加索引
很多时候，我们的慢查询，都是因为没有加索引。如果没有加索引的话，会导致全表扫描的。因此，应考虑在where的条件列，建立索引，尽量避免全表扫描。

2）SQL索引不生效
有时候我们明明加了索引了，但是索引却不生效。

3）limit深分页问题
limit深分页问题，会导致慢查询。limit深分页，导致SQL变慢原因有两个：
1、limit语句会先扫描offset+n行，然后再丢弃掉前offset行，返回后n行数据。也就是说limit 100000,10，就会扫描100010行，而limit 0,10，只扫描10行。
2、limit 100000,10 扫描更多的行数，也意味着回表更多的次数。

4）单表数据量太大
一个表的数据量达到好几千万或者上亿时，加索引的效果没那么明显啦。性能之所以会变差，是因为维护索引的B+树结构层级变得更高了，查询一条数据时，需要经历的磁盘IO变多，因此查询性能变慢。

5）join或者子查询过多
一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。

join过多的问题：
一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的IO就不快，还要关联。一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。

6）in元素过多
如果使用了in，即使后面的条件加了索引，还是要注意in后面的元素不要过多。in元素一般建议不要超过500个，如果超过了，建议分组，每次500一组进行。如果我们对in的条件不做任何限制的话，该查询语句一次性可能会查询出非常多的数据，很容易导致接口超时。尤其有时候，我们是用的子查询，in后面的子查询，都不知道数量有多少，更容易踩坑。

7）数据库在刷脏页
什么是脏页：当内存数据页跟磁盘数据页内容不一致的时候，我们称这个内存页为“脏页”。内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称为“干净页”。一般有更新SQL才可能会导致脏页。

为什么会出现脏页：更新SQL只是在写内存和redo log日志，等到空闲的时候，才把redo log日志里的数据同步到磁盘中。这时内存数据页跟磁盘数据页内容不一致，就出现脏页。

为什么刷脏页会导致SQL变慢：redo log写满了，要刷脏页，这时候会导致系统所有的更新堵住，写性能都跌为0了，肯定慢呀。一般要杜绝出现这个情况。一个查询要淘汰的脏页个数太多，一样会导致查询的响应时间明显变长。

8）order by文件排序
order by不一定会导致慢查询，但是数据量一上来，还是走文件排序的话，很容易有慢SQL的。order by排序，分为全字段排序和rowid排序。它是拿max_length_for_sort_data和结果行数据长度对比，如果结果行数据长度超过max_length_for_sort_data这个值，就会走rowid排序，相反，则走全字段排序。rowid排序，一般需要回表去找满足条件的数据，所以效率会慢一点。

如何优化order by的文件排序：
因为数据是无序的，所以就需要排序。如果数据本身是有序的，那就不会再用到文件排序。而索引数据本身是有序的，我们通过建立索引来优化order by语句。我们还可以通过调整max_length_for_sort_data、sort_buffer_size等参数优化。

9）拿不到锁
有时候，我们查询一条很简单的SQL，但是却等待很长的时间，不见结果返回。一般这种时候就是表被锁住了，或者要查询的某一行或者几行被锁住了。我们只能慢慢等待锁被释放或者杀死锁住的进程。

10）delete+in子查询不走索引
当delete遇到in子查询时，即使有索引，也是不走索引的。而对应的select+in子查询，却可以走索引。

11）group by使用临时表
group by一般用于分组统计，它表达的逻辑就是根据一定的规则，进行分组。日常开发中，我们使用得比较频繁。如果不注意，很容易产生慢SQL。group by使用不当，很容易就会产生慢SQL问题。因为它既用到临时表，又默认用到排序。有时候还可能用到磁盘临时表。如果执行过程中，会发现内存临时表大小到达了上限（控制这个上限的参数就是tmp_table_size），会把内存临时表转成磁盘临时表。如果数据量很大，很可能这个查询需要的磁盘临时表，就会占用大量的磁盘空间。

12）系统硬件或网络资源
如果数据库服务器内存、硬件资源，或者网络资源配置不是很好，就会慢一些。这时候可以升级配置。如果数据库压力本身很大，比如高并发场景下，大量请求到数据库来，数据库服务器CPU占用很高或者IO利用率很高，这种情况下所有语句的执行都有可能变慢。

分析慢查询日志

日志参数含义

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

-- 连接数据库
mysql -h localhost -P 3306 -u root -padmins
-- 查看数据库版本
select @@version;
-- 查看所有日志参数
show variables like '%log%';

常见日志参数含义：
slow_query_log：是否开启慢查询日志，1表示开启，0表示关闭。

log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log。

slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log。

long_query_time：慢查询阈值，当查询时间多于设定的阈值时，记录日志。

log_queries_not_using_indexes：未使用索引的查询也被记录到慢查询日志中（可选项）。

log_output：日志存储方式。log_output='FILE'表示将日志存入文件，默认值是'FILE'。log_output='TABLE'表示将日志存入数据库，这样日志信息就会被写入到mysql.slow_log表中。MySQL数据库支持同时两种日志存储方式，配置的时候以逗号隔开即可，如：log_output='FILE,TABLE'。日志记录到系统的专用日志表中，要比记录到文件耗费更多的系统资源，因此对于需要启用慢查询日志，又需要能够获得更高的系统性能，那么建议优先记录到文件。

配置慢查询日志

-- 查看是否开启慢查询日志
show variables like 'slow_query_log';

-- 查看日志储存方式，文件或数据库
show variables like '%log_output%';

-- 设置慢查询日志的位置， D:\workapp\mysql-5.7.23-winx64\data\XDZY-slow.log
set global slow_query_log_file='/var/lib/mysql/mysql-host-slow.log'

-- 开启慢查询日志，使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
set global slow_query_log=1;
-- 配置文件修改如下
slow_query_log=1
slow_query_log_file='/var/lib/mysql/mysql-host-slow.log'

-- 查看记录阈值（默认10s），在mysql源码里是判断大于long_query_time，而非大于等于
show variables like 'long_query_time';
-- 大于1秒钟的数据记录到慢日志中，如果设置为默认0，则会有大量的信息存储在磁盘中，磁盘很容易满掉
set global long_query_time=1;
-- 查看修改之后的阈值
show global variables like 'long_query_time';

-- 如果调优的话，建议开启这个选项。另外，开启了这个参数，其实使用full index scan的sql也会被记录到慢查询日志。
show variables like 'log_queries_not_using_indexes';
set global log_queries_not_using_indexes=on;
或 set global log_queries_not_using_indexes=1;

-- log_slow_admin_statements表示是否将慢管理语句例如ANALYZE TABLE和ALTER TABLE等记入慢查询日志
show variables like 'log_slow_admin_statements';

-- 查询有多少条慢查询记录
show global status like '%slow_queries%';

-- ---------------------------------------------------
-- 下面是更多永久生效的配置内容（配置在my.ini），需要重启数据库才能生效
-- 是否开启慢查询日志
slow_query_log=1
-- 指定保存路径及文件名，默认为数据文件目录，
slow_query_log_file="bxg_mysql_slow.log"
-- 指定多少秒返回查询的结果为慢查询
long_query_time=1
-- 记录所有没有使用到索引的查询语句
log_queries_not_using_indexes=1
-- 记录那些由于查找了多于1000次而引发的慢查询
min_examined_row_limit=1000
-- 记录那些慢的optimize table，analyze table和alter table语句
log_slow_admin_statements=1
-- 记录由Slave所产生的慢查询
log_slow_slave_statements=1
-- 数据文件目录
datadir=C:/ProgramData/MySQL/MySQL Server 5.7\Data

关闭数据库缓存

Query Cache会缓存select查询，安装时默认是开启的，但是如果对表进行INSERT, UPDATE, DELETE, TRUNCATE, ALTER TABLE, DROP TABLE, or DROP DATABASE等操作时，之前的缓存会无效并且删除。这样一定程度上也会影响我们数据库的性能。所以对一些频繁的变动表的情况开启缓存是不明智的。还有一种情况我们测试数据库性能的时候也要关闭缓存，避免缓存对我们测试数据的影响。

-- 查看数据库缓存配置，其中query_cache_type表示是否开启缓存
show VARIABLES like '%cache%';

-- 查看缓存命中情况，Qcache_hits表示缓存数量
show status like '%qcache%';

-- 临时关闭缓存，如果配置文件中为关闭缓存的话，不能通过命令开启缓存
set global query_cache_size=0;
set global query_cache_type=0;

-- 配置文件修改（重启永久生效）
query_cache_type=0
query_cache_size=0

-- 查询语句禁用缓存
-- 不缓存
Select sql_no_cache count(*) from tableName;
-- 缓存（也可以不加，默认缓存已经开启了）
Select sql_cache count(*) from tableName; 

查看慢查询日志

直接打开日志文件

-- 表示查询的执行时间
# Time: 2022-07-18T13:42:56.103836Z
-- 执行SQL的主机信息
# User@Host: root[root] @ localhost [127.0.0.1]  Id:     2
-- SQL的执行信息，Query_time表示SQL的查询时间，Lock_time表示锁定时间，Rows_sent表示所发送的行数，Rows_examined表示锁扫描的行数
# Query_time: 0.642205  Lock_time: 0.000284 Rows_sent: 273375  Rows_examined: 273375
-- SQL执行时间戳
SET timestamp=1658151776;
-- SQL的执行内容
SELECT * from ha03_allcstm_ship_busi_ic;

使用mysqldumpslow工具

这个工具是最常用的工具，通过安装mysql进行附带安装，但是该工具统计的结果比较少，对我们的优化锁表现的数据还是比较少。下面命令在mysql数据库所在的服务器上查看，而不是在mysql>命令行中
Windows：mysqldump --help
Linux：mysqldumpslow -h

得到返回记录集最多的10个SQL：
-s是表示按照何种方式排序（c: 访问计数，l: 锁定时间，r: 返回记录，t: 查询时间，al:平均锁定时间，ar:平均返回记录数，at:平均查询时间）
-t是top n的意思，即为返回前面多少条的数据
mysqldumpslow -s r -t 10 /database/mysql/mysql06_slow.log

得到访问次数最多的10个SQL：
mysqldumpslow -s c -t 10 /database/mysql/mysql06_slow.log

得到按照时间排序的前10条里面含有左连接的查询语句：
-g后边可以写一个正则匹配模式，大小写不敏感的
mysqldumpslow -s t -t 10 -g "left join" /database/mysql/mysql06_slow.log

另外建议在使用这些命令时结合 | 和 more 使用 ，否则有可能出现刷屏的情况：
mysqldumpslow -s r -t 20 /mysqldata/mysql/mysql06-slow.log | more

更多命令合集：https://www.cnblogs.com/moss_tan_jun/p/8025504.html

使用mysqlsla工具

https://developer.aliyun.com/article/59260

使用pt-query-digest工具

https://blog.csdn.net/seteor/article/details/24017913

Explain执行计划

Explain特点：Explain不考虑各种Cache；Explain不能显示MySql在执行查询时所作的优化工作；部分统计信息是估算的，并非精确值；Explain只能解释SELECT操作，其他操作要重写为SELECT后查看执行计划；Explain不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况。

-- 经常使用的方式，查看SQL的执行计划
EXPLAIN SELECT 

-- 将执行计划反编译成SELECT语句，运行SHOW WARNINGS，可得到被MySQL优化器优化后的查询语句
EXPLAIN EXTENDED SELECT

-- 用于分区表的EXPLAIN生成QEP的信息，用来查看索引是否正在被使用，并且输出其使用的索引的信息
EXPLAIN PARTITIONS SELECT

-- 案例如下
EXPLAIN SELECT * FROM big_data WHERE cstm_code in (select cstm_code from big_data where year BETWEEN 2019 and 2023);

执行结果如下所示：

各参数含义详解：

==========================id
包含一组数字，表示查询中执行select子句或操作表的顺序，id相同执行顺序由上至下。如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。

==========================select_type
所使用的SELECT查询类型，包括以下常见类型：
1）SIMPLE：表示为简单的SELECT，查询中不包含子查询或者UNION。
2）PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
3）SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY。
4）UNION：表连接中的第二个或后面的SELECT语句，若第二个SELECT出现在UNION之后，则被标记为UNION。
5）DERIVED：DERIVED（衍生）用来表示包含在FROM子句中的子查询的SELECT。若UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。MySql会递归执行并将结果放到一个临时表中。服务器内部称为"派生表"，因为该临时表是从子查询中派生出来的。
6）UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULT。
7）DEPENDENT：意味着SELECT依赖于外层查询中发现的数据。
8）UNCACHEABLE：意味着SELECT中的某些特性阻止结果被缓存于一个item_cache中。

==========================table
所使用的的数据表的名字，他们按被读取的先后顺序排列。

==========================type
表示MySql在表中找到所需行的方式，又称访问类型。取值按优劣排序为 NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL。一般来说，得保证查询至少达到range级别，最好能达到ref。下面是参数的含义：
1）ALL：Full Table Scan全表扫描，MySql将遍历全表以找到匹配的行。
2）index：Full Index Scan全索引扫描，index与ALL区别为index类型只遍历索引树。
3）range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行。显而易见的索引范围扫描是带有between或者where子句里带有<，>查询。当MySql使用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范围扫描）,当然性能上面是有差异的。
4）ref_or_null：该联接类型如同ref，但是添加了MySql可以专门搜索包含NULL值的行。
5）index_merge：该联接类型表示使用了索引合并优化方法。
6）unique_subquery：该类型替换了下面形式的IN子查询的ref: value IN (SELECT primary_key FROM single_table WHERE some_expr) 。unique_subquery是一个索引查找函数，可以完全替换子查询，效率更高。
7）index_subquery：该联接类型类似于unique_subquery。可以替换IN子查询，但只适合下列形式的子查询中的非唯一索引: value IN (SELECT key_column FROM single_table WHERE some_expr)。
8）ref：就是连接程序无法根据键值只取得一条记录，使用索引的最左前缀或者索引不是primary key或unique索引的情况。当根据键值只查询到少数几条匹配的记录时，这就是一个不错的连接类型。
9）eq_ref：类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者unique key作为关联条件。
10）const、system：当MySql对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySql就能将该查询转换为一个常量。注：system是const类型的特例，当查询的表只有一行的情况下，使用system。
11）NULL：MySql在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。如：explain select * from address where id = (select min(id) from person);

==========================possible keys
指出MySql能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。

==========================key
显示MySql在查询中实际使用的索引，若没有使用索引，显示为NULL。

==========================key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，key_len显示的值为索引字段的最大可能长度，并非实际使用长度。如果键是NULL，则长度为NULL。

==========================ref
显示索引的哪一列被使用了，有时候会是一个常量，表示哪些列或常量被用于用于查找索引列上的值，可能值为库.表.字段、常量、null。

==========================rows
MySql根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。

==========================filtered
显示了通过条件过滤出的行数的百分比估计值。

==========================Extra
包含不适合在其他列中显示但十分重要的额外信息，提供了与关联操作有关的信息，没有则什么都不写。
1）Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）。MySql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件包含所有满足查询需要的数据的索引称为覆盖索引（Covering Index）。注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
2）Using where：表示MySql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where字句的查询都会显示"Using where"。有时"Using where"的出现就是一个暗示：查询可受益与不同的索引。
3）Using temporary：表示MySql需要使用临时表来存储结果集，常见于排序和分组查询。这个值表示使用了内部临时（基于内存的）表。一个查询可能用到多个临时表。有很多原因都会导致MySql在执行查询期间创建临时表。两个常见的原因是在来自不同表的查询上使用了DISTINCT或者使用了不同的ORDER BY和GROUP BY列。可以强制指定一个临时表使用基于磁盘的MyISAM存储引擎。这样做的原因主要有两个：内部临时表占用的空间超过min(tmp_table_size,max_heap_table_size)系统变量的限制；使用了TEXT/BLOB列。
4）Using filesort：MySql中无法利用索引完成的排序操作称为“文件排序”。 
5）Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进性能。
6）Impossible where：这个值强调了where语句会导致没有符合条件的行。
7）Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行。
8）Index merges：当MySql决定要在一个给定的表上使用超过一个索引的时候，就会出现以下格式中的一个，详细说明使用的索引以及合并的类型。
Using sort_union(...)
Using union(...)
Using intersect(...)

Profiler诊断分析

要想优化一条Query，就须要清楚这条Query的性能瓶颈到底在哪里，是消耗的CPU计算太多，还是需要的IO操作太多，要想能够清楚地了解这些信息，可以通过Query Profiler功能得到。Query Profiler是MySql自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方。通常我们是使用的explain以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU、IO等，以及该SQL执行所耗费的时间等。

-- 开启QueryProfiler功能
set global profiling=on;

-- 查看相关变量
show VARIABLES like '%profiling%';

-- 设置保存数量默认15条，最大值为100
set profiling_history_size=100;

-- 在开启Query Profiler功能之后，MySql就会自动记录所有执行的Query的profile信息，下面执行n条Query作为测试
select * from big_data limit 1000,100;

-- 获取当前系统中保存的多个Query的profile的概要信息
show profiles;

-- 针对单个Query获取详细的profile信息。可以根据概要信息中的Query_ID来获取某个Query在执行过程中详细的profile信息。例如查看cpu和io的详细信息
show profile cpu,block io for query 501;

-- 显示所有信息
show profile ALL for query 501;

参数说明如下：
BLOCK IO：块设备IO输入输出次数
CONTEXT SWITCHES：上下文切换相关开销
CPU：用户和系统的CPU使用情况
IPC：显示发送和接收消息的相关消耗
MEMORY：内存消耗情况
PAGE FAULTS：显示主要和次要页面故障相关的开销
SOURCE：显示和Source_function,Source_file,Source_line相关的开销信息
SWAPS：显示交换次数相关的开销

注意：profiling被应用在每一个会话中，当前会话关闭后，profiling统计的信息将丢失。

查看SQL执行时间

查上一个查询的代价，而且它是io_cost和cpu_cost的开销总和，它通常也是我们评价一个查询的执行效率的一个常用指标。last_query_cost对于简单的查询可以精确的得到计算，但于包含子查询或union的复杂查询值是0。
show status like 'last_query_cost';

如果是用命令行来执行的话，有一点要注意，就是在select timestampdiff(second,@d,now());后面，一定要多copy一个空行，不然最后一个sql要自己按回车执行，这样就不准了。这种方法有一点要注意，就是三条sql语句要尽量连一起执行，不然误差太大，根本不准。
set @d=now();
select id from person where lname='x8RJWmQX';
select timestampdiff(second,@d,now());

第三方MySql客户端工具都自带sql执行时间显示功能，如navicat、sqlyog等等。

数据库查询优化

查询优化方案

1）SELECT子句中避免使用*号
获取什么字段就写入什么字段，尽量全部大写SQL。从数据库里读出越多的数据，那么查询就会变得越慢。并且如果你的数据库服务器和WEB服务器是两台独立的服务器的话，这还会增加网络传输的负载。

2）避免在where子句中使用!=或<>操作符
应尽量避免在where子句中使用!=或<>操作符，否则引擎放弃使用索引而进行全表扫描。

3）对查询进行优化，应尽量避免全表扫描
首先应考虑在where及order by涉及的列上建立索引，用索引可以提高查询。

4）用UNION来代替OR
采用UNION语句，返回的结果一样，但是速度要快些。

5）like语句避免前置百分号，前置百分号会导致索引失效

6）避免where子句中使用参数
如果在where子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num
可以改为强制查询使用索引：
select id from t with(index(索引名)) where num=@num

7）避免在where子句中对字段进行表达式操作
应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：
select id from t where num/2=100
应改为：
select id from t where num=100*2

8）避免在where子句中对字段进行函数操作
应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：
-- name以abc开头的 id
select id from t where substring(name,1,3)=’abc’;
-- ’2005-11-30′生成的id
select id from t where datediff(day,createdate,’2005-11-30′)=0;
应改为：
select id from t where name like ‘abc%’; 
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′;

9）避免无意义查询
不要写一些没有意义的查询，如需要生成一个空表结构：
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：
create table #t(…)

10）用exists代替in
很多时候用exists代替in是一个好的选择：
select num from a where num in(select num from b)
用下面的语句替换：
select num from a where exists(select 1 from b where num=a.num)

11）尽量使用数字型字段
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

12）使用varchar/nvarchar代替char/nchar
尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

13）大临时表使用select into代替create table
在新建临时表时，如果一次性插入数据量很大，那么可以使用select into代替create table，避免造成大量log，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。

14）临时表先truncate table，然后drop table
如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定。

15）存储过程使用SET NOCOUNT ON
在所有的存储过程和触发器的开始处设置SET NOCOUNT ON ，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONEINPROC消息。

16）避免向客户端返回大数据量
尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。

17）应尽量避免在where子句中对字段进行is null值判断，否则将导致引擎放弃使用索引而进行全表扫描，使用IS NOT NULL。where子句中使用or来连接条件，也会导致引擎放弃使用索引而进行全表扫描。
如可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
在MySql5.7版本中该条建议已经不用考虑了，因为null判断也能使用索引了

18）有外键约束的话会影响增删改的性能，如果应用程序可以保证数据库的完整性那就去除外键。

19）sql语句全部大写，特别是列名大写，因为数据库的机制是这样的，sql语句发送到数据库服务器，数据库首先就会把sql编译成大写在执行，如果一开始就编译成大写就不需要了把sql编译成大写这个步骤了。

20）如果应用程序可以保证数据库的完整性，可以不需要按照三大范式来设计数据库，反范式设计。

21）其实可以不必要创建很多索引，索引可以加快查询速度，但是索引会消耗磁盘空间。

22）如果是jdbc的话，使用PreparedStatement不使用Statement，来创建SQl，PreparedStatement的性能比Statement的速度要快，使用PreparedStatement对象SQL语句会预编译在此对象中，PreparedStatement对象可以多次高效的执行。

23）in和not in也要慎用，否则会导致全表扫描。

Join语句优化

1）尽可能减少Join语句中Nested Loop的循环总次数
最有效的办法是让驱动表的结果集尽可能地小，“永远用小结果集驱动大结果集”。比如，当两个表（表A和表B）Join时，如果表A通过WHERE条件过滤后有10条记录，而表B有20条记录。如果选择表A作为驱动表，也就是被驱动表的结果集为20，那么我们通过Join条件对被驱动表（表B）的比较过滤就会进行10次。反之，如果选择表B作为驱动表，则须要进行20次对表A的比较过滤。

2）优先优化Nested Loop的内层循环
不仅在数据库的Join中应该这样做，实际上在优化程序语言时也有类似的优化原则。内层循环是循环中执行次数最多的，每次循环节约很少的资源，就能在整个循环中节约很多的资源。

3）保证Join语句中被驱动表的Join条件字段已经被索引
其目的正是基于上面两点的考虑，只有让被驱动表的Join条件字段被索引了，才能保证循环中每次查询都能够消耗较少的资源，这也正是内层循环的实际优化方法。

4）不要太吝惜Join Buffer的设置
当无法保证被驱动表的Join条件字段被索引且内存资源充足时，不要太吝惜Join Buffer的设置。在Join是 All、Index、range或index_merge类型的特殊情况下，Join Buffer才能派上用场。在这种情况下，Join Buffer的大小将对整个Join语句的消耗起到非常关键的作用。

GROUP BY语句优化

1）group by实质是先排序后分组，遵照索引的最佳左前缀。group by后面的字段加索引可以优化查询。
2）当无法使用索引列，增大max_length_for_sort_data参数的设置和增大sort_buffer_size参数的设置。
3）where高于having，能写在where限定的条件就不要去having去限定了。
4）如果你的需求并不需要对结果集进行排序，可以使用order by null。
5）尽量只使用内存临时表。如果group by需要统计的数据不多，我们可以尽量只使用内存临时表；因为如果group by 的过程因为内存临时表放不下数据，从而用到磁盘临时表的话，是比较耗时的。因此可以适当调大tmp_table_size参数，来避免用到磁盘临时表。
6）使用SQL_BIG_RESULT优化。如果预估数据量比较大，我们使用SQL_BIG_RESULT这个提示直接用磁盘临时表。MySQl优化器发现，磁盘临时表是B+树存储，存储效率不如数组来得高。因此会直接用数组来存。如：select SQL_BIG_RESULT city ,count(*) as num from staff group by city。执行计划的Extra字段可以看到，执行没有再使用临时表，而是只有排序。

select city ,count(*) as num from staff group by city;
group by执行流程解析，由于创建了临时表和排序，所以会导致查询慢：
1）创建内存临时表，表里有两个字段city和num。
2）全表扫描staff的记录，依次取出city = 'X'的记录。
3）判断临时表中是否有为city='X'的行，没有就插入一个记录(X,1)。
4）如果临时表中有city='X'的行的行，就将x这一行的num值加1。
5）遍历完成后，再根据字段city做排序，得到结果集返回给客户端。

大表查询优化

1）使用limit进行分页，翻到10000多页后效率低。原因在于limit offset会逐行查找，是先查询再跳过。当ID是连续的时候，可以使用下面语句进行优化，中间的数据不能删，否则id为9999的数据并不是第9999个记录
select * from person where id>9999 limit 100;
或延迟关联，这样只查询id列，实现了索引覆盖，就会很快
select id from person limit 9999,100;
通过内连接再获取分页后每条记录的详细信息
select p.* from person p inner join (select id from person limit 999900 ,100) as tmp on
p.id=tmp.id;

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

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

4）添加数据缓存机制，使用redis等中间件。

5）垂直拆分数据库表：垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。

6）水平拆分数据库表：水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放。分表仅仅是解决了单一表数据过大的问题，但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以水平拆分最好分库 。 

数据库表结构优化

1）永远为每张表设置一个ID
我们应该为数据库里的每张表都设置一个ID做为其主键，而且最好的是一个INT型的（推荐使用UNSIGNED），并设置上自动增加的AUTO_INCREMENT标志。就算是你users表有一个主键叫email的字段，你也别让它成为主键。使用VARCHAR类型来当主键会使用得性能下降。另外，在你的程序中，你应该使用表的ID来构造你的数据结构。而且，在MySQL数据引擎下，还有一些操作需要使用主键，在这些情况下，主键的性能和设置变得非常重要，比如集群、分区。在这里，只有一个情况是例外（中间表），那就是关联表的外键，也就是说，这个表的主键，通过若干个别的表的主键构成。我们把这个情况叫做外键。比如有一个学生表有学生的ID，有一个课程表有课程ID，那么，成绩表就是关联表了，其关联了学生表和课程表，在成绩表中，学生ID和课程ID叫外键其共同组成主键。

2）有限值字段使用ENUM而不是VARCHAR
ENUM类型是非常快和紧凑的。实际上，其保存的是TINYINT，但其外表上显示为字符串。这样一来，用这个字段来做一些选项列表变得相当的完美。如果你有一个字段，比如性别、国家、民族、状态、部门，你知道这些字段的取值是有限而且固定的，那么，你应该使用ENUM而不是VARCHAR。MySQL也有一个建议，告诉你怎么去重新组织你的表结构。当你有一个VARCHAR字段时， 这个建议会告诉你把其改成ENUM类型。使用PROCEDURE ANALYSE()你可以得到相关的建议。

3）固定长度的表会更快
如果表中的所有字段都是固定长度的，整个表会被认为是static或fixed-length。例如这些类型的字段：VARCHAR、TEXT、BLOB。只要你包括了其中一个这些字段，那么这个表就不是固定长度静态表了，这样，MySQL引擎会用另一种方法来处理。固定长度的表会提高性能，因为MySQL搜寻会更快一些，因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。并且，固定长度的表也更容易被缓存和重建。不过，唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配那么多的空间。使用垂直分割技术，你可以分割你的表成两个，一个是定长的一个则是不定长的。

4）尽可能的使用NOT NULL
除非你有一个很特别的原因去使用NULL值，你应该总是让你的字段保持NOT NULL。首先，问问你自己Empty和NULL有多大的区别（如果是INT，那就是0和NULL)，如果你觉得它们之间没有什么区别，那么你就不要使用NULL。不要以为NULL不需要空间，其需要额外的空间，并且，在你进行比较的时候，你的程序会更复杂。当然，这里并不是说你就不能使用NULL了，现实情况是很复杂的，依然会有些情况下，你需要使用NULL值。

深分页解决方案

我们可以通过减少回表次数来优化。一般有标签记录法和延迟关联法。
1）标签记录法
就是标记一下上次查询到哪一条了，下次再来查的时候，从该条开始往下扫描。就好像看书一样，上次看到哪里了，你就折叠一下或者夹个书签，下次来看的时候，直接就翻到啦。
假设上一次记录到100000，则SQL可以修改为：
select id,name,balance FROM account where id > 100000 limit 10;
这样的话，后面无论翻多少页，性能都会不错的，因为命中了id索引。但是这种方式有局限性：需要一种类似连续自增的字段。

2）延迟关联法
延迟关联法，就是把条件转移到主键索引树，然后减少回表。假设原生SQL是这样的的，其中id是主键，create_time是普通索引：
select id,name,balance from account where create_time> '2020-09-19' limit 100000,10;
使用延迟关联法优化，如下：
select acct1.id,acct1.name,acct1.balance FROM account acct1 INNER JOIN 
(SELECT a.id FROM account a WHERE a.create_time > '2020-09-19' limit 100000, 10) AS acct2 on acct1.id= acct2.id;
优化思路就是，先通过idx_create_time二级索引树查询到满足条件的主键ID，再与原表通过主键ID内连接，这样后面直接走了主键索引了，同时也减少了回表。

实战优化案例

发现问题：发现网站页面打开非常慢，首先登录服务器使用top查看当前进程信息，发现排名第一的是MySql，占用cpu达到了100%以上，这就明确了是MySql的问题。登录MySql，使用show processlist查看下当前执行状态，发现了大量LOCK操作，也有多个Copying to tmp table的操作，说明有SQL出现了问题，操作过于复杂，对临时表使用频繁，把其他操作阻塞了。

解决问题：
1）调整MySql配置（my.cnf），重启生效
1、临时表
既然涉及了到了临时表，就先查看下目前临时表的信息
-- 查看临时表的使用状态。发现created_tmp_disk_tables值过高，需要增加此值。
show global status like 'created_tmp%';
-- 再看一下现在临时表的大小
show variables like '%tmp_table_size%';
-- 在现在值的基础上增加一些，重新设置临时表大小

2、线程缓存数
-- 看当前线程情况。发现threads_created的值过大，表明MySql服务器一直在创建线程
show global status like 'Thread%';
-- 查看当前值
show variables like 'thread_cache_size';
-- 此参数需要调高

3、打开表数量
-- 查看打开表的情况。发现opened_tables数量过大，说明table_cache的值可能太小。
show global status like 'open%tables%';
-- 查看当前值
show variables like 'table_cache';
-- 此参数需要调高

4、最大连接数
-- 查看当前允许的最大连接数
show variables like 'max_connections';
-- 查看服务器连接数的峰值
show global status like 'Max_used_connections';
-- 峰值还没到最大限制，不需要修改
join buffer 和 sort buffer
-- 查看现有值
SELECT @@sort_buffer_size;
SELECT @@join_buffer_size;
-- 是默认值，需要修改

确定了要修改的参数后，修改my.cnf，例如：
table_cache = 64
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 300
thread_concurrency = 8
tmp_table_size = 246M

扩展：SHOW VARIABLES LIKE '%timeout%'可以查看数据库超时信息

2）SQL语句优化
从show processlist结果集中找出主要的复杂语句，对其进行explain和profile分析，进行索引优化，把复杂的SQL根据业务拆分为多个小的SQL等。