【Mysql】Mysql优化案例总结篇

本文主要参考文章：SQL优化最干货总结 - MySQL（2020最新版）
后续不断补充
索引失效场景文章：索引失效场景

【一】Mysql优化的思路

【1】硬件配置

（1）磁盘 I/O 瓶颈

如果数据库服务器的磁盘 I/O 性能较差，频繁的磁盘读写操作会成为查询的瓶颈。机械硬盘的读写速度相对较慢，在处理大量数据时容易出现性能问题。

（2）内存不足

如果服务器的内存不足，MySQL 无法将足够的数据和索引缓存到内存中，会导致频繁的磁盘 I/O 操作，影响查询性能。

【2】Mysql配置

并发数是指同一时刻数据库能处理多少个请求，由max_connections和max_user_connections决定。max_connections是指MySQL实例的最大连接数，上限值是16384，max_user_connections是指每个数据库用户的最大连接数。

MySQL会为每个连接提供缓冲区，意味着消耗更多的内存。如果连接数设置太高硬件吃不消，太低又不能充分利用硬件。一般要求两者比值超过10%，计算方法如下：

max_used_connections / max_connections * 100% = 3/100 *100% ≈ 3%

查看最大连接数与响应最大连接数：

show variables like '%max_connections%';show variables like '%max_user_connections%';

在配置文件my.cnf中修改最大连接数

[mysqld]max_connections = 100max_used_connections = 20

（1）缓冲区设置不合理

MySQL 的缓冲区（如 join_buffer_size、innodb_buffer_pool_size 等）用于存储中间结果和缓存数据。如果这些缓冲区设置过小，会导致频繁的磁盘 I/O 操作，从而影响查询性能。

（2）并发连接过多

当数据库的并发连接数过多时，会导致资源竞争激烈，每个查询能够获取的资源减少，从而使查询变慢。

【3】数据表设计

【4】监控获取慢sql

监控sql，开启慢日志获取那些执行比较慢的sql，showProfile，查看sql的执行时间，耗费的资源
set global slow_query_log=1局部开启慢日志
set global long_query_time=3设置超时阈值
cat localhost-slow.log查看慢查询日志
结合mysqldumpslow工具排查慢日志中访问次数最多的sql

【5】查看数据库连接（代码层面）

连接，查看数据库的连接，有时候mysql连接的太多也会导致很慢
例如在for循环中写sql，每个sql在执行的时候都要去连接一遍数据库，这是个很耗时的操作，mysql可以通过配置参数来修改最大连接数

然后在代码中可以借助MybatisLog插件打印sql日志，借助StopWatch统计代码运行时长。定位到代码和sql以后，优化代码逻辑，或者使用explain命令分析sql的执行效率，排查sql索引问题

【6】合理的设计数据库（库表设计层面）

（1）合理的设计表

（1）尽量选择用InnoDB引擎，而不用MyISAM引擎
（2）将字段很多的表分解成多个表
对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
（3）适当增加中间表
对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

常见的情况是存在多对多关系，例如 “用户” 表和 “指标” 表之间，一个用户可以收藏多个指标，一个指标也可以被多个用户收藏，这种情况下就需要一个中间表来记录用户和指标之间的收藏关系。

（4）适当冗余字段
对于需要联表查询的高频字段，可以考虑在表中冗余存储，避免关联表查询

（2）选择合适的字段类型

用尽量少的存储空间来存数一个字段的数据
（1）能使用int就不要使用varchar、char，能用varchar(16)就不要使用varchar(256)；
（2）IP地址最好使用int类型；
（3）固定长度的类型最好使用char，例如：邮编、工号等等；
（4）如果长度能够满足，能使用tinyint就不要使用smallint，int；
（5）比避免空值，最好给每个字段一个默认值，最好不能为null；
（6）精度要求较高的，比如金额，使用decimal类型，也可以使用BIGINT，比如精确两位小数就乘以100后保存。
DECIMAL类型是一种存储方式，它可以用于精确计算。在计算过程中它会转化成double类型，但是DECIMAL的精确计算代价很高。为了减少这种高消耗我们可以在保留精度的条件下使用BIGINT来存储数据。举例来说，比如要保留6位小数，那么我们在存储数据的时候就可以给每一个数乘以100万，然后在取数据的时候在给每一个数除以100万。以减少代价。
（7）存储时间尽量采用timestamp而非datetime。
datetime时间范围更广，但是占8字节；时间戳timestamp时间范围小，但是只占4字节

【7】合理的使用索引（索引设计层面）

（二）sql优化三点原则
最大化利用索引；
尽可能避免全表扫描；
减少无效数据的查询；

所以优化的主要内容就是 1-如何合理的创建索引，2-如何合理的使用创建的索引，3-如何避免索引失效

索引失效的底层原理：创建索引会生成B+树结构，所有的数据放在叶子结点中，每次查询时使用“二分查找法”，二分查找最终的原则就是保证要查的数据是有序的，要保证有序，就要保证每个索引查出来的值是唯一确定的，这样下一个索引对应的查找值才能是有序的。如果一个索引断掉，或者一个索引成了范围查找等等，就会导致后面索引面对的是无序的数值，自然索引也就会失效了。

（1）创建合理的索引

⭐️1-合理使用覆盖索引

select login_name, nick_name from member where login_name = ?

login_name，nick_name两个字段建立组合索引，比login_name简单索引要更快。

覆盖索引是指一个查询语句的所有列都可以从索引中直接获取，而不需要回表查询数据。也就是说，查询所需要的数据都被索引所覆盖，MySQL 只需要在索引树上进行查找，就能够满足查询需求，而不必再去数据文件中读取记录。

MySQL 的索引通常采用 B + 树结构存储。在覆盖索引中，索引不仅包含了索引列的值，还包含了可以直接用于满足查询的其他列的值。当查询语句中的列都在索引中存在时，数据库引擎可以直接从索引中获取这些列的数据，而无需访问数据行所在的物理磁盘块，从而减少了磁盘 I/O 操作，提高了查询性能。

2-单表索引数不超过5个、单个索引字段数不超过5个

3-🍓如何根据sql选择要加索引的字段

（2）合理的使用创建的索引

（3）避免索引失效的场景

1-【避免1】避免前后都有%的模糊查询

SELECT * FROM t WHERE username LIKE '%陈%'

【优化方案1】尽量在字段后面使用模糊查询

【补充说明】如果需求是要在前面使用模糊查询（待研究）
去除了前面的%查询将会命中索引，但是产品经理一定要前后模糊匹配呢？全文索引fulltext可以尝试一下，但Elasticsearch才是终极武器。

1.使用MySQL内置函数INSTR(str,substr) 来匹配，作用类似于java中的indexOf()，查询字符串出现的角标位置，可参阅《MySQL模糊查询用法大全（正则、通配符、内置函数等）》
2.使用FullText全文索引，用match against 检索
3-数据量较大的情况，建议引用ElasticSearch、solr，亿级数据量检索速度秒级
4-当表数据量较少（几千条儿那种），别整花里胡哨的，直接用like ‘%xx%’。

2-【避免2】避免使用in 和not in，会导致引擎走全表扫描

（1）用between替换in

SELECT * FROM t WHERE id IN (2,3)

【优化方案2】如果是连续数值，可以用between代替（用or也会失效）

SELECT * FROM t WHERE id BETWEEN 2 AND 3

（2）用exists替换in
如果是子查询，可以用exists代替。详情见《MySql中如何用exists代替in》如下：

-- 不走索引
select * from A where A.id in (select id from B);
-- 走索引
select * from A where exists (select * from B where B.id = A.id);

IN适合主表大子表小，EXIST适合主表小子表大。由于查询优化器的不断升级，很多场景这两者性能差不多一样了。

（3）用join替换in

select id from orders where user_id in (select id from user where level = 'VIP');

select o.id from orders o left join user u on o.user_id = u.id where u.level = 'VIP';

3-【避免3】避免使用or，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE id = 1 OR id = 3

【优化方案3】可以用union代替or

SELECT * FROM t WHERE id = 1
   UNION
SELECT * FROM t WHERE id = 3

4-【避免4】避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM t WHERE score IS NULL

【优化方案4】可以给字段添加默认值0，对0值进行判断

SELECT * FROM t WHERE score = 0

5-【避免5】避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

SELECT * FROM T WHERE score/10 = 9

【优化方案5】可以将表达式、函数操作移动到等号右侧

SELECT * FROM T WHERE score = 10*9

6-【避免6】避免查询条件用 <> 或者 !=

使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

7-【避免7】⭐️避免违背最佳左前缀原则，会导致后续的索引失效

复合（联合）索引包含key_part1，key_part2，key_part3三列，如果开头的索引或者中间的索引顺序断掉，会导致断掉的索引后面的索引全部失效

select col1 from table where key_part2=1 and key_part3=2

（1）案例一：查询条件顺序不符合索引顺序
假设有一张 users 表，包含 id、name、age、city 字段，并且创建了一个联合索引 idx_name_age_city（name，age，city）。

执行查询语句 SELECT * FROM users WHERE age = 25 AND city = ‘Beijing’;，这个查询条件中没有包含联合索引的最左列 name，根据最佳左前缀原则，该索引无法被完全利用，MySQL 可能会选择全表扫描。

EXPLAIN SELECT * FROM users WHERE age = 25 AND city = 'Beijing';

在 EXPLAIN 结果中，key 字段可能显示为 NULL，表示没有使用到索引。

（2）如果中间字段没有命中索引会怎么样？索引是完全失效还是局部失效？如果是局部失效会失效哪一部分？
如果是中间断掉呢？SELECT * FROM users WHERE name = ‘张三’ AND city = ‘Beijing’;

如果中间字段没有命中索引，索引不会完全失效，而是局部失效。
联合索引遵循最左前缀原则，即只有当查询条件按照索引列的顺序从左到右依次使用时，才能充分利用索引的所有部分。当中间字段没有命中索引时，从该字段开始向右的索引部分将无法被使用。

例如，有一个联合索引 idx_a_b_c （a，b，c），如果查询语句为 SELECT * FROM table WHERE a = 1 AND c = 2;，此时虽然 a 字段命中了索引，但由于中间的 b 字段没有在查询条件中出现，所以 MySQL 只能利用索引中 a 字段对应的部分来进行查询，而 b 和 c 字段对应的索引部分就无法被利用，需要通过其他方式来获取 c 字段相关的数据，可能会导致额外的查询成本，如回表查询或全表扫描（具体取决于查询的其他条件和数据分布等因素）。
综上所述，中间字段未命中索引时，联合索引中该字段及右侧的索引部分失效，只有左侧连续命中的索引部分能发挥作用。

（3）使用函数导致不符合最佳左前缀原则
还是上述 users 表和索引 idx_name_age_city。执行查询语句 SELECT * FROM users WHERE UPPER(name) = ‘JOHN’;，这里对 name 字段使用了 UPPER 函数，这会导致 MySQL 无法使用 idx_name_age_city 索引，因为索引是基于原始字段值构建的，而不是基于函数处理后的值。

（4）解决方案
在查询中添加 name 字段的条件，使其符合最佳左前缀原则。例如：SELECT * FROM users WHERE name = ‘Alice’ AND age = 25 AND city = ‘Beijing’;，这样索引 idx_name_age_city 就可以被有效利用。

如果可能，尽量避免在索引列上使用函数。可以在应用程序中对输入进行处理，而不是在数据库中使用函数。如果无法避免，可以考虑创建基于函数的索引（在 MySQL 8.0 及以上版本支持）。

此时在 EXPLAIN 结果中，key 字段会显示为 idx_name_age_city，表示使用了索引。

8-【避免8】⭐️避免使用隐式“类型转换”造成不使用索引（传varchar转int可以，传int转varchar不可以）

如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。

select col1 from table where col_varchar=123; 

对于Int类型的字段，传varchar类型的值是可以走索引，MySQL内部自动做了隐式类型转换；相反对于varchar类型字段传入Int值是无法走索引的，应该做到对应的字段类型传对应的值总是对的。

#user_id是bigint类型，传入varchar值发生了隐式类型转换，可以走索引。
select id, name , phone, address, device_no from users where user_id = '23126';
#card_no是varchar(20)，传入int值是无法走索引
select id, name , phone, address, device_no from users where card_no = 2312612121;

【优化方案8】实在不行就放到后端代码里进行处理

9-【避免9】避免 order by 条件与 where 中条件不一致，否则 order by 不会利用索引进行排序

-- 不走age索引
SELECT * FROM t order by age;

【优化方案9】保持order by条件与where条件一致

-- 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：

• 第一步：根据where条件和统计信息生成执行计划，得到数据。
• 第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
• 第三步：返回排序后的数据。

当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。

这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

10-【避免10】避免在查询条件上进行列运算

（1）案例一

select id from order where date_format(create_time，'%Y-%m-%d') = '2019-07-01';

date_format函数会导致这个查询无法使用索引，改写后：

select id from order where create_time between '2019-07-01 00:00:00' and '2019-07-01 23:59:59';

【8】写规范的sql

（1）SELECT语句其他优化

1-【避免10】避免select *

使用select * 取出全部列，会让优化器无法完成索引覆盖扫描这类优化，会影响优化器对执行计划的选择，也会增加网络带宽消耗，更会带来额外的I/O,内存和CPU消耗。

建议提出业务实际需要的列数，将指定列名以取代select *。具体详情见《为什么大家都说SELECT * 效率低》：

2-【避免11】避免出现不确定结果的函数

特定针对主从复制这类业务场景。由于原理上从库复制的是主库执行的语句，使用如now()、rand()、sysdate()、current_user()等不确定结果的函数很容易导致主库与从库相应的数据不一致。另外不确定值的函数,产生的SQL语句无法利用query cache。

3-【避免12】（注意）多表关联查询时，小表在前，大表在后。（并且索引加在大表上时效果更好）

在MySQL中，执行 from 后的表关联查询是从左往右执行的（Oracle相反），第一张表会涉及到全表扫描，所以将小表放在前面，先扫小表，扫描快效率较高，在扫描后面的大表，或许只扫描大表的前100行就符合返回条件并return了。
【优化方案12】关联查询时如何加索引

3-【避免12】避免3表以上的join查询

4-【避免13】避免使用HAVING字句，用where语句替换

避免使用HAVING字句，因为HAVING只会在检索出所有记录之后才对结果集进行过滤，而where则是在聚合前刷选记录，如果能通过where字句限制记录的数目，那就能减少这方面的开销。HAVING中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where字句中。

where和having的区别：where后面不能使用组函数

5-【避免14】避免把过滤数据多的条件往后放

调整Where字句中的连接顺序，MySQL采用从左往右，自上而下的顺序解析where子句。根据这个原理，应将过滤数据多的条件往前放，最快速度缩小结果集。

6-【避免15】避免在查询中出现text类型的字段

如果select出现text类型的字段，就会消耗大量的网络和IO带宽，由于返回的内容过大超过max_allowed_packet设置会导致程序报错，需要评估谨慎使用。

#表request_log的中content是text类型。
select user_id, content, status, url, type from request_log where user_id = 32121;

7-【避免16】避免出现子查询

由于MySQL的基于成本的优化器CBO对子查询的处理能力比较弱，不建议使用子查询，可以改写成Inner Join。子查询构成的虚表是没有任何索引的。

select b.member_id,b.member_type, a.create_time,a.device_model from member_operation_log a inner join (select member_id,member_type from member_base_info where `status` = 1
and create_time between '2020-10-01 00:00:00' and '2020-10-30 00:00:00') as b on a.member_id = b.member_id;

（2）⭐️优化LIMIT分页查询的深分页问题

在系统中需要分页的操作通常会使用limit加上偏移量的方法实现，同时加上合适的order by 子句。如果有对应的索引，通常效率会不错，否则MySQL需要做大量的文件排序操作。

一个非常令人头疼问题就是当偏移量非常大的时候，例如可能是limit 10000,20这样的查询，这是mysql需要查询10020条然后只返回最后20条，前面的10000条记录都将被舍弃，这样的代价很高。

优化此类查询的一个最简单的方法是尽可能的使用索引覆盖扫描，而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。对于偏移量很大的时候这样做的效率会得到很大提升。

对于下面的查询：

select id,title from collect limit 90000,10;

该语句存在的最大问题在于limit M,N中偏移量M太大（我们暂不考虑筛选字段上要不要添加索引的影响），导致每次查询都要先从整个表中找到满足条件 的前M条记录，之后舍弃这M条记录并从第M+1条记录开始再依次找到N条满足条件的记录。如果表非常大，且筛选字段没有合适的索引，且M特别大那么这样的代价是非常高的。 试想，如我们下一次的查询能从前一次查询结束后标记的位置开始查找，找到满足条件的100条记录，并记下下一次查询应该开始的位置，以便于下一次查询能直接从该位置 开始，这样就不必每次查询都先从整个表中先找到满足条件的前M条记录，舍弃，在从M+1开始再找到100条满足条件的记录了。

（1）方法一：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

【9】索引下推、谓词下推

（1）开启索引下推

通过把索引过滤条件下推到存储引擎，来减少 MySQL 存储引擎访问基表的次数以及 MySQL 服务层访问存储引擎的次数。

# 查看索引下推是否开启
select @@optimizer_switch
# 开启索引下推
set optimizer_switch="index_condition_pushdown=on";
# 关闭索引下推
set optimizer_switch="index_condition_pushdown=off";

表结构为

CREATE TABLE employees (
    emp_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(255),
    dept_id INT,
    salary DECIMAL(10, 2),
    hire_date DATE,
    INDEX idx_dept_salary (dept_id, salary)
) ENGINE=InnoDB;

对比效果

EXPLAIN SELECT emp_id, emp_name FROM employees WHERE dept_id = 5 AND salary BETWEEN 50000 AND 80000;

上面是开启了，下面是关闭的

（2）谓词下推（先过滤再联立）

谓词下推，就是在将过滤条件下推到离数据源更近的地方，最好就是在table_scan时就能过滤掉不需要的数据，在关系代数中谓词是可以左右上下移动的，由于join查询的特殊性，在优化join condition中的谓词时，对应不同的join 内型，有不同的策略

（1）inner join

inner join的结果集是左表和有表都要满足条件，所以inner join condtion中的条件都是可以下推的，比如下面的查询

select e.* from emp e inner join dept d on e.deptno = d.deptno and emp.deptno=5;

优化后的查询

select e. from (select * from emp where emp.deptno =5) e inner join (select * from deptno where deptno =5 ) on on e.deptno = d.deptno;

（2）left join
由于左表是保留表，所有join condition 中的左表条件会失效，右表的条件可以下推，对right join. 则相反；

（3）outter join
对于outter join 由于左表和右表都是保留表，则都不能下推；

（4）anti join
对于anti join,由于查询的结果集是不满足join 条件的集合，如果谓词下推，查询结果集将会为null, 当然有一种思路可以在谓词时下推时将条件取非，获取正确结果；

【10】联立查询优化

联立查询（通常指多表连接查询）是常见操作，但随着数据量的增加，可能会导致性能下降。以下从不同角度给出联立查询的优化方案：

（1）索引优化

（1）合理创建索引
在经常用于连接条件和查询条件的列上创建索引。例如，在执行

SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.city = 'New York'; 

时，需要在 orders.customer_id、customers.id 和 customers.city 列上创建索引。这样可以加快连接操作和过滤操作的速度。

-- 在 orders 表的 customer_id 列创建索引
CREATE INDEX idx_orders_customer_id ON orders(customer_id);
-- 在 customers 表的 id 列创建索引
CREATE INDEX idx_customers_id ON customers(id);
-- 在 customers 表的 city 列创建索引
CREATE INDEX idx_customers_city ON customers(city);

（2）使用复合索引
当查询条件涉及多个列时，使用复合索引可以提高效率。如果经常同时根据 customer_id 和 order_date 查询 orders 表，可以创建复合索引 (customer_id, order_date)。

CREATE INDEX idx_orders_customer_date ON orders(customer_id, order_date);

（2）查询语句优化

（1）选择合适的连接类型
根据业务需求选择合适的连接类型，如 INNER JOIN、LEFT JOIN、RIGHT JOIN 或 FULL OUTER JOIN。避免使用不必要的连接类型，减少数据处理量。如果只需要获取两个表中匹配的记录，使用 INNER JOIN 即可。

（2）避免在连接条件中使用函数
在连接条件中使用函数会导致索引失效。避免使用

SELECT * FROM orders JOIN customers ON YEAR(orders.order_date) = customers.register_year; 

这种写法，可以将函数计算结果提前存储在表中，或者在应用层进行处理。

（3）减少不必要的列
只选择需要的列，避免使用 SELECT *。这样可以减少数据传输量和处理时间。例如，

SELECT orders.order_id, customers.customer_name FROM orders JOIN customers ON orders.customer_id = customers.id;

（3）数据库配置优化

（1）调整 join_buffer_size
join_buffer_size 是 MySQL 用于存储连接结果的缓冲区大小。适当增大该参数可以提高连接查询的性能，但要注意不要设置得过大，以免占用过多内存。可以通过以下命令查看和修改该参数：

-- 查看当前 join_buffer_size 值
SHOW VARIABLES LIKE 'join_buffer_size';
-- 修改 join_buffer_size 值
SET GLOBAL join_buffer_size = 262144; -- 设置为 256KB

（2）优化 innodb_buffer_pool_size
对于使用 InnoDB 存储引擎的数据库，innodb_buffer_pool_size 是非常重要的参数，它决定了 InnoDB 用于缓存数据和索引的内存大小。适当增大该参数可以减少磁盘 I/O，提高查询性能。一般建议将其设置为物理内存的 70% - 80%。

（4）表结构优化

（1）使用垂直拆分
如果表中的列很多，而每次查询只需要部分列，可以考虑将表进行垂直拆分，将不常用的列拆分到另一个表中。这样可以减少每次查询的数据量，提高查询性能。

（2）使用水平拆分
当表中的数据量非常大时，可以考虑将表进行水平拆分，将数据分散到多个表中。例如，按照时间范围将 orders 表拆分为多个表，如 orders_2023、orders_2024 等。

（5）分析和监控

（1）使用 EXPLAIN 分析查询计划
使用 EXPLAIN 关键字可以查看查询的执行计划，了解 MySQL 是如何执行查询的，是否使用了索引，以及表的连接顺序等信息。根据执行计划进行优化。

EXPLAIN SELECT * FROM orders JOIN customers ON orders.customer_id = customers.id WHERE customers.city = 'New York';

（2）监控数据库性能
使用 MySQL 的性能监控工具，如 SHOW STATUS、SHOW PROCESSLIST 等，实时监控数据库的性能指标，及时发现和解决性能问题。

（6）联表查询慢的原因

当参与联表查询的表数据量非常大时，即使有索引，查询也可能变慢。因为需要处理的数据量巨大，磁盘 I/O 操作和 CPU 计算量都会增加。例如，两个表都有几百万条记录，连接操作会产生大量的中间结果，处理这些结果需要耗费大量时间。

（7）驱动表的选择

1-小表驱动大表

一般情况下，应该选择数据量小的表作为驱动表。因为在连接查询时，驱动表中的每一行数据都会与被驱动表中的数据进行匹配。如果驱动表的数据量小，那么匹配的次数就会相对较少，从而减少整体的查询开销。

例如，有 orders 表和 customers 表，orders 表有 1000 条记录，customers 表有 10000 条记录。在进行 JOIN 查询时，优先选择 orders 表作为驱动表。

SELECT * 
FROM orders
JOIN customers ON orders.customer_id = customers.id;

这里 orders 表数据量小，可作为驱动表。

2-有索引的表优先

如果参与连接的表中有一个表的连接列上有索引，那么优先选择该表作为驱动表。因为索引可以加快数据的查找速度，使用有索引的表作为驱动表，能更快地定位到需要匹配的数据。
例如，orders 表的 customer_id 列有索引，customers 表的 id 列没有索引，那么在连接查询时，可选择 orders 表作为驱动表。

SELECT * 
FROM orders
JOIN customers ON orders.customer_id = customers.id;

3-基于查询条件

当查询中包含过滤条件时，选择经过过滤后数据量最小的表作为驱动表。这样可以减少后续连接操作的数据量。
例如，查询语句为：

SELECT * 
FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.order_date > '2023-01-01' AND customers.city = 'New York';

如果 orders 表经过 order_date > ‘2023-01-01’ 过滤后的数据量比 customers 表经过 city = ‘New York’ 过滤后的数据量小，那么选择 orders 表作为驱动表。

4-选择驱动表的原理

MySQL 执行联表查询时，通常采用嵌套循环连接（Nested - Loop Join）算法。该算法的基本思想是：

（1）先从驱动表中读取一行数据。
（2）然后用这一行数据去匹配被驱动表中的每一行数据，找出满足连接条件的记录。
（3）重复上述步骤，直到驱动表中的所有行都被处理完。

由于嵌套循环连接算法需要对驱动表的每一行数据都与被驱动表进行匹配，所以驱动表的数据量越小，需要进行的匹配次数就越少，查询的效率也就越高。

此外，当驱动表的连接列上有索引时，在匹配被驱动表的数据时可以利用索引快速定位，减少了查找的时间开销，从而提高了查询性能。

【11】使用分区

（1）案例背景
假设你有一个电商网站的订单表 orders，用于存储订单信息，随着业务的增长，表中数据量不断增大，达到了数百万甚至更多条记录。此时，对订单表的查询、插入、删除等操作性能逐渐下降。为了提高数据库性能，决定使用分区来优化 orders 表。

（2）表结构
orders 表结构如下：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50),
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2)
);

（3）分区方案
考虑到订单数据与时间密切相关，通常按时间范围进行查询，比如查询某个月、某个季度的订单数据。因此，可以选择按日期对 orders 表进行分区。这里以按月分区为例，创建分区表的 SQL 语句如下：

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(50),
    customer_id INT,
    order_date DATE,
    order_amount DECIMAL(10, 2)
) PARTITION BY RANGE (YEAR(order_date) * 100 + MONTH(order_date)) (
    PARTITION p202301 VALUES LESS THAN (202302),
    PARTITION p202302 VALUES LESS THAN (202303),
    -- 依次类推，创建每个月的分区
    PARTITION p202401 VALUES LESS THAN (202402)
);

（4）优化效果
1-查询性能提升
未分区前：执行查询语句

SELECT * FROM orders WHERE order_date BETWEEN '2023-05-01' AND '2023-05-31';

MySQL 需要全表扫描整个 orders 表来获取符合条件的数据，当数据量很大时，查询速度会很慢。
分区后：由于数据已经按照月份分区，MySQL 只需要扫描 p202305 分区，大大减少了扫描的数据量，查询速度显著提高。通过 EXPLAIN 命令可以看到，分区后的查询执行计划中 partitions 字段会显示只访问了 p202305 分区。

2-删除数据更高效
未分区前：如果要删除 2023 年 1 月的所有订单数据，需要执行 DELETE FROM orders WHERE order_date BETWEEN ‘2023-01-01’ AND ‘2023-01-31’;，这会逐行删除符合条件的数据，效率较低。
分区后：可以直接使用 ALTER TABLE orders DROP PARTITION p202301; 语句，直接删除整个 p202301 分区，瞬间完成删除操作，比逐行删除要快得多。

3-数据备份与恢复方便
在进行数据备份和恢复时，可以只针对特定的分区进行操作。例如，只备份 2023 年的数据，只需要备份对应的 p202301 到 p202312 分区即可，而不需要备份整个表，提高了备份和恢复的效率。

通过对订单表按照时间进行分区，有效地提高了查询、删除等操作的性能，同时也方便了数据的管理和维护。实际应用中，可以根据业务需求和数据特点选择合适的分区键和分区策略，以达到最佳的优化效果。

【12】多线程

【13】适当的时机使用mysql集群

《阿里巴巴Java开发手册》提出单表行数超过500万行或者单表容量超过2GB，才推荐分库分表。性能由综合因素决定，抛开业务复杂度，影响程度依次是硬件配置、MySQL配置、数据表设计、索引优化。500万这个值仅供参考，并非铁律。

分库分表是个周期长而风险高的大活儿，应该尽可能在当前结构上优化，比如升级硬件、迁移历史数据等等，实在没辙了再分。

【14】代码层面控制

（1）查询需要的字段
例如查询数据构建树结构，其实只需要描述和编码两个字段，则其他字段是可以不用查询的

（2）必要时才联立表查询
代码判断是否需要关联表查询

（3）

（二）select语句执行顺序

FROM
<表名> # 选取表，将多个表数据通过笛卡尔积变成一个表。
ON
<筛选条件> # 对笛卡尔积的虚表进行筛选
JOIN <join, left join, right join…>
<join表> # 指定join，用于添加数据到on之后的虚表中，例如left join会将左表的剩余数据添加到虚表中
WHERE
<where条件> # 对上述虚表进行筛选
GROUP BY
<分组条件> # 分组
<SUM()等聚合函数> # 用于having子句进行判断，在书写上这类聚合函数是写在having判断里面的
HAVING
<分组筛选> # 对分组后的结果进行聚合筛选
SELECT
<返回数据列表> # 返回的单列必须在group by子句中，聚合函数除外
DISTINCT
#数据除重
ORDER BY
<排序条件> # 排序
LIMIT
<行数限制>

（三）sql优化策略实例

【1】select查询添加索引实例（需要继续补充）

（1）【优化案例1】多表关联查询

select sql_no_cache  * from user left join article on(user.id = article.user_id)
where  user.name like 'user_4%';

没有使用缓存，user表的id是主键，article表除主键外没有任何索引，这种情况下，百万级数据查询情况如下

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:24:45] 500 rows retrieved starting from 1 in 4 s 681 ms (execution: 1 s 312 ms, fetching: 3 s 369 ms)

【添加索引】
1-重点在on后面和where后面添加索引，那就是考虑给article表的user_id和user表的name加个索引（user表的id已经是主键了，默认有索引）
2-记得小表放前面，大表放后面，使用left join

CREATE INDEX user_id ON article (user_id);

执行效果

sql> select sql_no_cache  * from user left join article on(user.id = article.user_id)
     where  user.name like 'user_4%'
[2020-05-17 13:27:22] 500 rows retrieved starting from 1 in 142 ms (execution: 112 ms, fetching: 30 ms)

【关联查询总结】
1-在被驱动表上创建索引才会生效
当使用left join时，左表是驱动表（小表），右表是被驱动表（大表）。在sql优化中，永远是以小表驱动大表。驱动表有索引不会使用到索引，驱动表无论如何都会被全表扫描，被驱动表建立索引会使用到索引，所以关联查询的时候主要给【被驱动表/右表/大表】创建索引。之所以使用小表驱动大表是因为可以缩小结果集，如果两个表数量差不多则都可以作为驱动表。

select * from A a（驱动表/小表） left join B b（被驱动表/大表） on a.code=b.code

2-子查询不要放在被驱动表，因为子查询是虚表，没法创建索引，而索引只有在右表被驱动表上才有效。

SELECT ed.name '人物',c.name '掌门' 
FROM (SELECT e.name,d.ceo from t_emp e LEFT JOIN t_dept d on e.deptid=d.id) ed 
LEFT JOIN t_emp c on ed.ceo= c.id;

能够直接多表关联的尽量直接关联，不用子查询

3-关联查询join的时候需要distinct ，没有索引的话distinct消耗性能较大，所以在使用关联查询的时候一定注意添加索引。

（2）【优化案例2】单表查询添加索引

SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

【添加索引】
1-where后面的数据category_id和comments，但是因为comments字段后面跟的是不等号，会导致索引失效，所以排除掉
2-order by后面的字段views

所以创建一个category_id和views的联合索引

create index idx_article_cv on article(category_id,views);

（3）【优化案例3】order by添加索引

使用where子句与order by子句条件列组合满足索引最左前列
where子句中如果出现索引的范围查询（就是explain出现range）会导致order by索引失效

（4）【优化案例4】子查询转换成join方式

【查询目的】 查找语文考100分的考生
Student几十条数据，SC几万条数据
【sql版本一】

select s.* from Student s where s.s_id in (select s_id from SC sc where sc.c_id = 0 and sc.score = 100 )

效果非常的慢，主要是因为没有索引，在添加索引之前分析语句的格式
分析1：使用了范围查找in，
分析2：使用了子查询是虚表会导致索引失效

【sql版本二】
给子查询中的c_id和score添加索引（也可以是联合索引）

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

再次执行，缩短大表select s_id from SC sc where sc.c_id = 0 and sc.score = 100的查询速度

【sql版本三】
改用关联查询
清空之前的索引
给s_id创建索引，发现效率反而慢了，因为s_id添加索引后会先走on关联语句，然后再走where过滤语句
在where过滤之前就进行关联，肯定数据更多
应该先where过滤，再进行关联join，这样数据会少很多

SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100

【sql版本四】
上面的索引s_id不变
添加索引

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

按照从左往右的执行顺序
1-实现子查询的过滤：SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100
2-关联join

SELECT s.* FROM (SELECT * FROM SC sc WHERE sc.c_id = 0 AND sc.score = 100 ) t INNER JOIN Student s ON t.s_id = s.s_id

【sql版本五】
保持上面的两个索引不变

CREATE index sc_c_id_index on SC(c_id);
CREATE index sc_score_index on SC(score);

SELECT s.* from Student s INNER JOIN SC sc on sc.s_id = s.s_id where sc.c_id=0 and sc.score=100

【总结】
1-mysql嵌套子查询效率确实比较低， 可以将其优化成连接查询
2-学会分析sql执行计划，mysql会对sql进行优化，所以分析执行计划很重要
3-内连接查询的时候，不管谁是左表右表，执行结果都一样。因为mysql会自动把小结果集的表选为驱动表（ 驱动表无论如何都会被全表扫描 ），大结果集的表选为被驱动表，被驱动表上的索引才生效。所以一般都是先执行where过滤，用到大表中的索引，然后再把小表和过滤后的大表关联到一起
4-简单来说就是小表驱动大表，大表索引过滤