MySQL优化

一、查询SQL尽量不要使用select *，而是具体字段。

1、反例

SELECT * FROM user

2、正例

SELECT id,username,tel FROM user

3、理由

1. 节省资源、减少网络开销。

2. 可能用到覆盖索引，减少回表，提高查询效率。

二、避免在where子句中使用 or 来连接条件 。

1、反例

SELECT * FROM user WHERE id=1 OR salary=5000

2、正例

（1）使用union all

SELECT * FROM user WHERE id=1 
UNION ALL
SELECT * FROM user WHERE salary=5000

（2）分开两条sql写

SELECT * FROM user WHERE id=1

SELECT * FROM user WHERE salary=5000

3、理由

1. 使用or可能会使索引失效，从而全表扫描；

2. 对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描；

3. 也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定；

4. 虽然mysql是有优化器的，出于效率与成本考虑，遇到or条件，索引还是可能失效的；

三、尽量使用数值替代字符串类型

1、正例

1. 主键（id）：primary key优先使用数值类型int，tinyint

2. 性别（sex）：0代表女，1代表男；数据库没有布尔类型，mysql推荐使用tinyint

2、理由

1. 因为引擎在处理查询和连接时会逐个比较字符串中每一个字符；

2. 而对于数字型而言只需要比较一次就够了；

3. 字符会降低查询和连接的性能，并会增加存储开销；

四、使用varchar代替char

1、反例

`address` char(100) DEFAULT NULL COMMENT '地址'

2、正例

`address` varchar(100) DEFAULT NULL COMMENT '地址'

3、理由

1. varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间；

2. char按声明大小存储，不足补空格；

3. 其次对于查询来说，在一个相对较小的字段内搜索，效率更高；

五、技术延伸，char与varchar2的区别？

1、char的长度是固定的，而varchar2的长度是可以变化的。

比如，存储字符串“101”，对于char(10)，表示你存储的字符将占10个字节（包括7个空字符），在数据库中它是以空格占位的，而同样的varchar2(10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

2、char的效率比varchar2的效率稍高。

3、何时用char，何时用varchar2?

char和varchar2是一对矛盾的统一体，两者是互补的关系，varchar2比char节省空间，在效率上比char会稍微差一点，既想获取效率，就必须牺牲一点空间，这就是我们在数据库设计上常说的“以空间换效率”。

varchar2虽然比char节省空间，但是假如一个varchar2列经常被修改，而且每次被修改的数据的长度不同，这会引起“行迁移”现象，而这造成多余的I/O，是数据库设计中要尽力避免的，这种情况下用char代替varchar2会更好一些。char中还会自动补齐空格，因为你insert到一个char字段自动补充了空格的,但是select后空格没有删除，因此char类型查询的时候一定要记得使用trim，这是写本文章的原因。

如果开发人员细化使用rpad()技巧将绑定变量转换为某种能与char字段相比较的类型（当然，与截断trim数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数trim很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是因为以上原因，定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合都要避免使用char类型。

六、where中使用默认值代替null

1、反例

SELECT * FROM user WHERE age IS NOT NULL

2、正例

SELECT * FROM user WHERE age>0

3、理由

1. 并不是说使用了is null或者 is not null就会不走索引了，这个跟mysql版本以及查询成本都有关；

2. 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效；

3. 其实是因为一般情况下，查询的成本高，优化器自动放弃索引的；

4. 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点；

七、避免在where子句中使用!=或<>操作符

1、反例

SELECT * FROM user WHERE salary!=5000

SELECT * FROM user WHERE salary<>5000

2、理由

1. 使用!=和<>很可能会让索引失效

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

3. 实现业务优先，实在没办法，就只能使用，并不是不能使用。

八、inner join 、left join、right join，优先使用inner join

三种连接如果结果相同，优先使用inner join，如果使用left join左边表尽量小。

• inner join 内连接，只保留两张表中完全匹配的结果集；

• left join会返回左表所有的行，即使在右表中没有匹配的记录；

• right join会返回右表所有的行，即使在左表中没有匹配的记录；

为什么？

• 如果inner join是等值连接，返回的行数比较少，所以性能相对会好一点；

• 使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少；

• 这是mysql优化原则，就是小表驱动大表，小的数据集驱动大的数据集，从而让性能更优；

九、提高group by语句的效率

1、反例

先分组，再过滤

select job, avg（salary） from employee 
group by job
having job ='develop' or job = 'test';

2、正例

先过滤，后分组

select job，avg（salary） from employee 
where job ='develop' or job = 'test' 
group by job;

3、理由

可以在执行到该语句前，把不需要的记录过滤掉

十、清空表时优先使用truncate

truncate table在功能上与不带 where子句的 delete语句相同：二者均删除表中的全部行。但 truncate table比 delete速度快，且使用的系统和事务日志资源少。

delete语句每次删除一行，并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

truncate table删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 drop table语句。

对于由 foreign key约束引用的表，不能使用 truncate table，而应使用不带  where子句的 DELETE 语句。由于 truncate table不记录在日志中，所以它不能激活触发器。

truncate table不能用于参与了索引视图的表。

十一、操作delete或者update语句，加个limit或者循环分批次删除

1、降低写错SQL的代价

清空表数据可不是小事情，一个手抖全没了，删库跑路？如果加limit，删错也只是丢失部分数据，可以通过binlog日志快速恢复的。

2、SQL效率很可能更高

SQL中加了limit 1，如果第一条就命中目标return， 没有limit的话，还会继续执行扫描表。

3、避免长事务

delete执行时,如果age加了索引，MySQL会将所有相关的行加写锁和间隙锁，所有执行相关行会被锁住，如果删除数量大，会直接影响相关业务无法使用。

4、数据量大的话，容易把CPU打满

如果你删除数据量很大时，不加 limit限制一下记录数，容易把cpu打满，导致越删越慢。

5、锁表

一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作。

十二、UNION操作符

UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：

select username,tel from user
union
select departmentname from department

这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。

十三、批量插入性能提升

1、多条提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程');

INSERT INTO user (id,username) VALUES(2,'妲己');

2、批量提交

INSERT INTO user (id,username) VALUES(1,'哪吒编程'),(2,'妲己');

3、理由

默认新增SQL有事务控制，导致每条都需要事务开启和事务提交，而批量处理是一次事务开启和提交，效率提升明显，达到一定量级，效果显著，平时看不出来。

十四、表连接不宜太多，索引不宜太多，一般5个以内

1、表连接不宜太多，一般5个以内

1. 关联的表个数越多，编译的时间和开销也就越大

2. 每次关联内存中都生成一个临时表

3. 应该把连接表拆开成较小的几个执行，可读性更高

4. 如果一定需要连接很多表才能得到数据，那么意味着这是个糟糕的设计了

5. 阿里规范中，建议多表联查三张表以下

2、索引不宜太多，一般5个以内

1. 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率；

2. 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间；

3. 索引表的数据是排序的，排序也是要花时间的；

4. insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定；

5. 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要；

十五、避免在索引列上使用内置函数

1、反例

SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW();

2、正例

SELECT * FROM user WHERE  birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);

3、理由

使用索引列上内置函数，索引失效，常见如WHERE/ON，索引列计算表达式。

索引列函数计算优化方式：

• 使用生成列上用索引，如ORDER BY/SELECT/GROUP BY上使用索引。

•  应用层面进行数据计算/函数处理。
•  EXPLAIN分析索引执行计划是否使用。

十六、组合索引

排序时应按照组合索引中各列的顺序进行排序，即使索引中只有一个列是要排序的，否则排序性能会比较差。

create index IDX_USERNAME_TEL on user(deptid,position,createtime);
select username,tel from user where deptid= 1 and position = 'java开发' order by deptid,position,createtime desc;

实际上只是查询出符合 deptid= 1 and position = 'java开发'条件的记录并按createtime降序排序，但写成order by createtime desc性能较差。

十七、复合索引最左特性

1、创建复合索引

ALTER TABLE employee ADD INDEX idx_name_salary (name,salary)

2、满足复合索引的最左特性，哪怕只是部分，复合索引生效

SELECT * FROM employee WHERE NAME='哪吒编程'

3、没有出现左边的字段，则不满足最左特性，索引失效

SELECT * FROM employee WHERE salary=5000

4、复合索引全使用，按左侧顺序出现 name,salary，索引生效

SELECT * FROM employee WHERE NAME='哪吒编程' AND salary=5000

5、虽然违背了最左特性，但MySQL执行SQL时会进行优化，底层进行颠倒优化

SELECT * FROM employee WHERE salary=5000 AND NAME='哪吒编程'

6、理由

复合索引也称为联合索引，当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则。

联合索引不满足最左原则，索引一般会失效。

十八、优化like语句

模糊查询，程序员最喜欢的就是使用like，但是like很可能让你的索引失效。

1、反例

select * from citys where name like '%大连' (不使用索引)

select * from citys where name like '%大连%' (不使用索引)

2、正例

select * from citys where name like '大连%' (使用索引) 。

LIKE优化方式：

•  前缀匹配要添加索引，同时添加where过滤条件，进一步限制返回范围。
•  如果无法走索引，可以添加where过滤条件，限制扫描范围。
•  如果相同的查询要频繁执行考虑应用层面使用缓存，减少数据库负载。
• 如果是后缀like匹配，使用辅助列+反向索引的方式来执行，反向匹配。

3、理由

• 首先尽量避免模糊查询，如果必须使用，不采用全模糊查询，也应尽量采用右模糊查询， 即like ‘…%’，是会使用索引的；

• 左模糊like ‘%...’无法直接使用索引，但可以利用reverse + function index的形式，变化成 like ‘…%’；

• 全模糊查询是无法优化的，一定要使用的话建议使用搜索引擎。

十九、使用explain分析你SQL执行计划

1、type

1. system：表仅有一行，基本用不到；

2. const：表最多一行数据配合，主键查询时触发较多；

3. eq_ref：对于每个来自于前面的表的行组合，从该表中读取一行。这可能是最好的联接类型，除了const类型；

4. ref：对于每个来自于前面的表的行组合，所有有匹配索引值的行将从这张表中读取；

5. range：只检索给定范围的行，使用一个索引来选择行。当使用=、<>、>、>=、<、<=、IS NULL、<=>、BETWEEN或者IN操作符，用常量比较关键字列时，可以使用range；

6. index：该联接类型与ALL相同，除了只有索引树被扫描。这通常比ALL快，因为索引文件通常比数据文件小；

7. all：全表扫描；

8. 性能排名：system > const > eq_ref > ref > range > index > all。

9. 实际sql优化中，最后达到ref或range级别。

2、Extra常用关键字

• Using index：只从索引树中获取信息，而不需要回表查询；

• Using where：WHERE子句用于限制哪一个行匹配下一个表或发送到客户。除非你专门从表中索取或检查所有行，如果Extra值不为Using where并且表联接类型为ALL或index，查询可能会有一些错误。需要回表查询。

• Using temporary：mysql常建一个临时表来容纳结果，典型情况如查询包含可以按不同情况列出列的GROUP BY和ORDER BY子句时；

 MySQL深度分页优化

 背景：MySQL涉及深度分页使用，性能会显著下降，这是因为MySQL需要扫描和跳过大量的行才会达到目标行。

二十、数据库层面优化

1、设计表的时候，所有表和字段都添加相应的注释。

2、SQL书写格式，关键字大小保持一致，使用缩进。

3、修改或删除重要数据前，要先备份。

4、很多时候用 exists 代替 in 是一个好的选择

5、where后面的字段，留意其数据类型的隐式转换。

未使用索引

SELECT * FROM user WHERE NAME=110

（1） 因为不加单引号时，是字符串跟数字的比较，它们类型不匹配； 

（2）MySQL会做隐式的类型转换，把它们转换为数值类型再做比较；

6、尽量把所有列定义为NOT NULL

NOT NULL列更节省空间，NULL列需要一个额外字节作为判断是否为 NULL的标志位。NULL列需要注意空指针问题，NULL列在计算和比较的时候，需要注意空指针问题。

7、伪删除设计

8、数据库和表的字符集尽量统一使用UTF8

（1）可以避免乱码问题；

（2）可以避免，不同字符集比较转换，导致的索引失效问题；

9、select count(*) from table；

这样不带任何条件的count会引起全表扫描，并且没有任何业务意义，是一定要杜绝的。

10、避免在where中对字段进行表达式操作

（1）SQL解析时，如果字段相关的是表达式就进行全表扫描 ；

（2）字段干净无表达式，索引生效；

11、关于临时表

（1）避免频繁创建和删除临时表，以减少系统表资源的消耗；

（2）在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log；

（3）如果数据量不大，为了缓和系统表的资源，应先create table，然后insert；

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

12、索引不适合建在有大量重复数据的字段上，比如性别，排序字段应创建索引

13、去重distinct过滤字段要少

1. 带distinct的语句占用cpu时间高于不带distinct的语句

2. 当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据

3. 然而这个比较、过滤的过程会占用系统资源，如cpu时间

14、尽量避免大事务操作，提高系统并发能力

15、所有表必须使用Innodb存储引擎

Innodb「支持事务，支持行级锁，更好的恢复性」，高并发下性能更好，所以呢，没有特殊要求（即Innodb无法满足的功能如：列存储，存储空间数据等）的情况下，所有表必须使用Innodb存储引擎。

16、尽量避免使用游标

因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。

SQL语句的执行顺序如下：

FROM子句：确定数据来源，包括JOIN的表。

ON：执行JOIN条件。

JOIN：如果有JOIN，根据JOIN类型（如INNER、LEFT），将两个表进行合并。

WHERE子句：过滤记录。

GROUP BY子句：根据指定的列分组记录。

HAVING子句：过滤分组。

SELECT子句：选取特定的列。

DISTINCT子句：去除重复数据。

ORDER BY子句：最后对结果进行排序。

LIMIT子句(或者是TOP，依赖于数据库)：最后的结果截取。

MySQL的delete的子查询不走索引的问题.

CREATE TABLE old_account (
                             id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
                             name varchar(255) DEFAULT NULL COMMENT '账户名',
                             balance int(11) DEFAULT NULL COMMENT '余额',
                             create_time datetime NOT NULL COMMENT '创建时间',
                             update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                             PRIMARY KEY (id),
                             KEY idx_name USING BTREE (name)
) ENGINE = InnoDB AUTO_INCREMENT = 1570068 CHARSET = utf8 COMMENT '老的账户表';


CREATE TABLE account (
                             id int(11) NOT NULL AUTO_INCREMENT COMMENT '主键Id',
                             name varchar(255) DEFAULT NULL COMMENT '账户名',
                             balance int(11) DEFAULT NULL COMMENT '余额',
                             create_time datetime NOT NULL COMMENT '创建时间',
                             update_time datetime NOT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
                             PRIMARY KEY (id),
                             KEY idx_name USING BTREE (name)
) ENGINE = InnoDB AUTO_INCREMENT = 1570068 CHARSET = utf8 COMMENT '账户表';

 SELECT 的方式可以走索引

EXPLAIN SELECT *
        FROM account
        WHERE name IN (
            SELECT name
            FROM old_account
        );

 通过SHOW WARNINGS;命令可以看到MySQL对SELECT的子查询优化为join方式 

 

[2024-08-31 17:07:26] [HY000][1003] /* select#1 */ select `test`.`account`.`id` AS `id`,`test`.`account`.`name` AS `name`,`test`.`account`.`balance` AS `balance`,`test`.`account`.`create_time` AS `create_time`,`test`.`account`.`update_time` AS `update_time` from `test`.`account` semi join (`test`.`old_account`) where (`test`.`old_account`.`name` = `test`.`account`.`name`)
[2024-08-31 17:07:26] 2 rows retrieved starting from 1 in 56 ms (execution: 7 ms, fetching: 49 ms)
test> SHOW WARNINGS

explain DELETE FROM account
WHERE name IN (
    SELECT name
    FROM old_account
);
SHOW WARNINGS;

DELETE的子查询是没有进行优化的. 

 优化方式1：

表名称添加别名

EXPLAIN DELETE a
        FROM account a
        WHERE a.name IN (
            SELECT name
            FROM old_account
        )

优化方式2:

改为inner join的方式.

explain delete a  from account a inner join old_account b on a.name=b.name;

索引相关的问题，一般能用explain命令帮助分析。通过它能看到用了哪些索引，大概会扫描多少行之类的信息。

mysql会在优化器阶段里看下选择哪个索引，查询速度会更快。

一般主要考虑几个因素，比如：

• 选择这个索引大概要扫描多少行（rows）

• 为了把这些行取出来，需要读多少个16kb的页

• 走普通索引需要回表，主键索引则不需要，回表成本大不大？ 

如果上面的流程比较慢的话，我们可以通过开启profiling看到流程慢在哪。

mysql> set profiling=ON;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set (0.00 sec)

然后正常执行sql语句。

这些SQL语句的执行时间都会被记录下来，此时你想查看有哪些语句被记录下来了，可以执行 show profiles;

mysql> show profile for query 1;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000074 |
| checking permissions | 0.000010 |
| Opening tables       | 0.000034 |
| init                 | 0.000032 |
| System lock          | 0.000027 |
| optimizing           | 0.000020 |
| statistics           | 0.000058 |
| preparing            | 0.000018 |
| executing            | 0.000013 |
| Sending data         | 0.067701 |
| end                  | 0.000021 |
| query end            | 0.000015 |
| closing tables       | 0.000014 |
| freeing items        | 0.000047 |
| cleaning up          | 0.000027 |
+----------------------+----------+
15 rows in set, 1 warning (0.00 sec)

通过上面的各个项，大家就可以看到具体耗时在哪。比如从上面可以看出Sending data的耗时最大，这个是指执行器开始查询数据并将数据发送给客户端的耗时，因为我的这张表符合条件的数据有好几万条，所以这块耗时最大，也符合预期。

一般情况下，我们开发过程中，耗时大部分时候都在Sending data阶段，而这一阶段里如果慢的话，最容易想到的还是索引相关的原因。

1、索引不符合预期

实际开发中有些情况比较特殊，比如有些数据库表一开始数据量小，索引少，执行sql时，确实使用了符合你预期的索引。但随时时间边长，开发的人变多了，数据量也变大了，甚至还可能会加入一些其他重复多余的索引，就有可能出现用着用着，用到了不符合你预期的其他索引了。从而导致查询突然变慢。

这种问题，也好解决，可以通过force index指定索引。比如

通过explain可以看出，加了force index之后，sql就选用了idx_age这个索引了。

2、走了索引还是很慢

有些sql，用explain命令看，明明是走索引的，但还是很慢。一般是两种情况：

第一种是索引区分度太低，比如网页全路径的url链接，这拿来做索引，一眼看过去全都是同一个域名，如果前缀索引的长度建得不够长，那这走索引跟走全表扫描似的，正确姿势是尽量让索引的区分度更高，比如域名去掉，只拿后面URI部分去做索引。

第二种是索引中匹配到的数据太大，这时候需要关注的是explain里的rows字段了。

它是用于预估这个查询语句需要查的行数的，它不一定完全准确，但可以体现个大概量级。

当它很大时，一般常见的是下面几种情况。

• 如果这个字段具有唯一的属性，比如电话号码等，一般是不应该有大量重复的，那可能是你代码逻辑出现了大量重复插入的操作，你需要检查下代码逻辑，或者需要加个唯一索引限制下。

• 如果这个字段下的数据就是会很大，是否需要全部拿？如果不需要，加个limit限制下。如果确实要拿全部，那也不能一次性全拿，今天你数据量小，可能一次取一两万都没啥压力，万一哪天涨到了十万级别，那一次性取就有点吃不消了。你可能需要分批次取，具体操作是先用order by id排序一下，拿到一批数据后取最大id作为下次取数据的起始位置。

3、连接数过小

索引相关的原因我们聊完了，我们来聊聊，除了索引之外，还有哪些因素会限制我们的查询速度的。

我们可以看到，mysql的server层里有个连接管理，它的作用是管理客户端和mysql之间的长连接。

正常情况下，客户端与server层如果只有一条连接，那么在执行sql查询之后，只能阻塞等待结果返回，如果有大量查询同时并发请求，那么后面的请求都需要等待前面的请求执行完成后，才能开始执行。

因此很多时候我们的应用程序，比如go或java这些，会打印出sql执行了几分钟的日志，但实际上你把这条语句单独拎出来执行，却又是毫秒级别的。这都是因为这些sql语句在等待前面的sql执行完成。

怎么解决呢？

如果我们能多建几条连接，那么请求就可以并发执行，后面的连接就不用等那么久了。

而连接数过小的问题，受数据库和客户端两侧同时限制。 

数据库连接数过小

Mysql的最大连接数默认是100, 最大可以达到16384。

可以通过设置mysql的max_connections参数，更改数据库的最大连接数。

mysql> set global max_connections= 500;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 500   |
+-----------------+-------+
1 row in set (0.00 sec)

上面的操作，就把最大连接数改成了500。

应用侧连接数过小

数据库连接大小是调整过了，但貌似问题还是没有变化？还是有很多sql执行达到了几分钟，甚至超时？

那有可能是因为你应用侧（go，java写的应用，也就是mysql的客户端）的连接数也过小。

应用侧与mysql底层的连接，是基于TCP协议的长链接，而TCP协议，需要经过三次握手和四次挥手来实现建连和释放。如果我每次执行sql都重新建立一个新的连接的话，那就要不断握手和挥手，这很耗时。所以一般会建立一个长连接池，连接用完之后，塞到连接池里，下次要执行sql的时候，再从里面捞一条连接出来用，非常环保。

我们一般写代码的时候，都会通过第三方的orm库来对数据库进行操作，而成熟的orm库，百分之一千万都会有个连接池。

而这个连接池，一般会有个大小。这个大小就控制了你的连接数最大值，如果说你的连接池太小，都还没有数据库的大，那调了数据库的最大连接数也没啥作用。

buffer pool太小

通过 show status like 'Innodb_buffer_pool_%';可以看到跟buffer pool有关的一些信息。

Innodb_buffer_pool_read_requests表示读请求的次数。

Innodb_buffer_pool_reads 表示从物理磁盘中读取数据的请求次数。

所以buffer pool的命中率就可以这样得到：

buffer pool 命中率 = 1 - (Innodb_buffer_pool_reads/Innodb_buffer_pool_read_requests) * 100%

比如我上面截图里的就是，1 - (405/2278354) = 99.98%。可以说命中率非常高了。

一般情况下buffer pool命中率都在99%以上，如果低于这个值，才需要考虑加大innodb buffer pool的大小。

当然，还可以把这个命中率做到监控里，这样半夜sql变慢了，早上上班还能定位到原因，就很舒服。

我们在前面的数据库查询流程里，提到了进了innodb之后，会有一层内存buffer pool，用于将磁盘数据页加载到内存页中，只要查询到buffer pool里有，就可以直接返回，否则就要走磁盘IO，那就慢了。

也就是说，如果我的buffer pool 越大，那我们能放的数据页就越多，相应的，sql查询时就更可能命中buffer pool，那查询速度自然就更快了。

二十一、系统上线SQL脚本优化

1 、漏脚本了

我们上线时执行的SQL脚本，出现次数最多的问题，应该是漏脚本了。

• 有时候少加了一个字段。

• 有时候字段的注释没有及时修改。

• 有时候有些新表没创建。

• 有时候字段类型忘了修改。

那么，如何解决这个问题呢？

答：将SQL脚本做成代码的一部分。在项目的代码中，创建一个专门的sql目录，在该目录下根据每个迭代创建一个子目录，比如：mv3.2.1，将SQL脚本存放到mv3.2.1下。

我们在开发环境任何对表的相关操作，比如：增加字段、修改字段类型、修改注释、增加索引、创建表等等，都需要通过SQL语句操作，然后把该SQL语句，整理到SQL脚本中。

最后提交到公司的GitLab上，我们在测试环境和生产环境发版时，去GitLab上找相关迭代版本的SQL脚本执行。

通过该方式基本可以解决漏脚本的问题。

2、脚本语法错误

有些小伙伴看到这个标题可能有点懵，SQL脚本不是已经在测试环境执行过了吗？为什么还会出现语法错误？

比如说有这样的场景：原本你的SQL脚本没问题的，但没有按照规范，给一张表的添加多个字段，你写了多条ALTER语句。

alter table t_user add `work` varchar(30) DEFAULT NULL COMMENT '工作',
add `provice` varchar(10) DEFAULT NULL  COMMENT '籍贯';

因此，不管是什么SQL语句，要养成好习惯，只要修改了一定要记得到开发环境的数据库中，先执行测试一下，切勿直接提到生产环境，即使你有很大的把握，也需要再更慎重一些。

这样基本可以避免SQL语法错误的问题。

3 、脚本顺序不对

有些时候，我们在上线系统时，DBA在执行SQL脚本的时候，没有报错，但最后的数据就是不对。

有可能是脚本顺序不对导致的。

比如有这样一种场景：你往某张表通过insert初始化了一条数据。

DBA先把他的SQL工单审核通过了，先update数据，此时通过id是没法找到那条数据的，影响行数为0。

然后DBA再审核你的SQL工单，审核通过了，插入了一条数据。

由于SQL脚本的顺序不对，导致最终系统上线时的数据不对。

那么这个问题要如何解决呢？

双方要事先沟通好，把另外一个同事的SQL脚本加到你的初始化脚本中，你的脚本在初始化时，直接去修改数据即可。

4、 执行时机不对

有些系统功能已经上线了，在后面的迭代中，为了尽量避免少影响线上功能，可以增加一个pre（即预生产环境）。

该环境跟生产环境是差不多的，连接了相同的数据库，使用了相同的apollo配置。

但唯一的区别是pre环境没有实际的用户流量，只能公司内部人员才能访问。

一般在迭代版本上线之前，先要把系统功能发布到pre环境中，测试通过之后，才能发布到prod（即生产环境）。

但有些SQL脚本，却没法再pre环境中执行，不然会影响生产环境。

比如：修改了字段类型，int改成varchar了，或者初始化数据时，初始化了一条新加的枚举数据。

由于pre环境是运行的最新代码，但prod环境还是运行的老代码。

如果在发布pre环境时，直接执行SQL脚本，可能会导致prod环境的功能异常。

因此要搞清楚SQL脚本的执行时机，哪些是要在pre环境执行的，哪些是要在prod环境执行的。

我们在提SQL工单时，千万不要一股脑就提了，一定要区分时机。

在发pre环境时，要么不提发prod环境的SQL脚本。要么，在工单的名称上做区分，比如增加prod_开头的标识。

这样可以解决SQL脚本执行时机的问题。

5 、搞错数据库了

有时候，我们的数据库做了分库分表，或者增加备份库。

在执行SQL脚本的时候，由于我们自己的疏忽，提SQL工单时选错数据库了，或者DBA的疏忽，在执行SQL工单时搞错数据库了，就会出现问题。

建议我们的SQL脚本增加库名，比如：

alter table sue.t_user add `work` varchar(30) DEFAULT NULL COMMENT '工作';

这里增加库名：sue。

这样基本可以避免选错数据库的问题。

6、 脚本耗时太长

有时候，我们的SQL脚本需要批量修改生产环境的一些数据，正常情况下一条update语句就能搞定。

update user set status=0 where status=1;

但由于user表的数据量非常大，我们在执行该SQL脚本之前，没有预先评估该SQL脚本的耗时情况，而选择直接在生产环境的数据库中执行。

假如该SQL脚本耗时非常长，比如要10分钟才能执行完，可能会导致user表长期锁表，影响正常的业务功能。

在该SQL脚本执行的过程中，极有可能会出现业务功能操作，导致的死锁问题。

因此，建议这种大批量的数据更新操作，要在用户较少的凌晨，分批多次执行。

我们要尽可能少的影响线上用户的功能。

此外，在生产环境增加字段，增加索引等操作，也能会导致长期锁表。也要避免在用户访问高峰期执行相关的SQL脚本。

7 、脚本无法回滚

绝大多数系统上线是能够成功的，虽然过程中会遇到很多问题，但如果能够及时解决，也能够上线成功。

但如果有些问题，没法再规定的时间内解决，很有可能会导致上线失败。

如果上线失败，意味着代码和数据库的SQL脚本要回滚。

如果只回滚了代码，不回滚数据库，可能会导致很多系统异常。

因此，我们在准备SQL语句时，要留点心眼，顺便想想该SQL语句能否回滚。

对于update语句可以加上修改时间：

update t_user set age=25,time=now(3) where id=1;

这样可以通过该时间追溯一次SQL操作修改的数据，方便后面做回滚。

有些时候我们要update的数据，是要通过多条sql语句查询出来的，比如：需要使用的id。

为了方便回滚我们可以增加临时表，保存这些id，后面就能追溯了。

当然有些开源的数据库管理平台，比如：Archery，是有自带SQL审核和回滚的功能。

8 、忘了加索引

我们在增加了字段之后，非常容易忽略的一件事是：加索引。

特别是当前表数据量很大，而且增加的字段是另外一张表的id时，这种情况强烈建议增加索引。

如果我们上线系统时，在SQL脚本中，忘了给该字段增加索引。如果该id字段被大批量访问，全部走的全表扫描，可能会导致数据库性能直线下降，出现大量的超时问题。

所以建议我们在开发的时候，如果要增加字段的话，要养成良好习惯，想一想这个字段需不需要建索引。

如果不确定数据量的话，可以先到生产环境查询一下真实的用户数据，不然后续可能会引起比较大的生产事故。

9 、字段改名

对于生产环境的表字段，通常情况下，我们不允许修改名称。

如果你在发布pre环境时，通过SQL脚本把某张表的某个字段名称修改了，pre环境代码使用了新的名称，系统没有问题。

但prod环境还是使用老的名称，所有使用该名称的sql语句，在代码执行过程中都会报错。

因此，禁止在生产环境通过SQL脚本修改字段名称。

当然系统上线时除了SQL脚本的这些坑之外，还有系统发版失败，代码合错分支，mq消息被pre消费了，无法回滚等等，还有很多问题。

二十二、哪些情况不适合加索引

• 在where中使用不到的字段，不要设置索引

WHERE条件(包括GROUP BY，ORDER BY)里用不到的字段不需要创建索引，索引的价值是快速定位，如果起不到定位的字段通常是不需要创建索引的。

• 数据量小的表最好不要使用索引 

如果表记录太少，比如少于1000个，那么是不需要创建索引的。表记录太少。是否创建索引对查询效率的影响并不大。甚至说，查询花费的时间可能比遍历索引|的时间还要短，索引可能不会产生优化效果。

• 有大量重复数据的列上不要建立索引

在条件表达式中经常用到的不同值较多的列上建立索引，但字段中如果有大量重复数据，也不用创建索引，比如在学生表的“性别“字段上只有“男”与“女”两个不同值，因此无须建立索引。如果建立索引，不但不会提高查询效率，反而会严重降低数据更新速度 。
结论:当数据重复度大，比如高于18% 的时候，也不需要对这个字段使用索引。

• 不建议用无序的值作为索引

 例如身份证、UUID(在索引比较时需要转为ASCII, 井且插入时可能造成页分裂)、MD5. HASH. 无序长字符串等。

• 删除不再使用或很少使用的索引 

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一-些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引|对更新操作的影响。

附录：CASE+WHEN用法

CASE WHEN 语句是 SQL 中一种常用的条件表达式，它允许我们根据不同的条件返回不同的结果。无论是数据查询、数据转换，还是复杂的条件判断，CASE WHEN 语句都能提供极大的灵活性 

CASE WHEN 语句在数据处理中非常有用，适用于多种场景，如：

• 动态计算列值

• 基于条件的筛选

• 自定义排序规则

• 分组和过滤数据

• CASE: 表达式的开始。

• WHEN condition: 条件判断，如果条件成立，则返回对应的结果。

• THEN result: 条件成立时返回的值。

• ELSE result_else: 当所有条件都不满足时返回的值（可选）。

• END: 表达式的结束。

2.1 CASE WHEN 的基本语法

SELECT 
  CASE 
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE result_default
  END AS alias_name
FROM table_name;

假设我们有一个包含学生成绩的表 students_scores，我们想要根据分数段给学生评级：

SELECT 
  student_name,
  score,
  CASE 
    WHEN score >= 90 THEN '优秀'
    WHEN score >= 75 THEN '良好'
    WHEN score >= 60 THEN '及格'
    ELSE '不及格'
  END AS grade
FROM students_scores;

2.2 CASE WHEN 与 ELSE 的结合使用

ELSE 子句在 CASE WHEN 语句中用于指定默认结果，当所有条件都不满足时，返回 ELSE 后的值。

SELECT 
  product_name,
  price,
  CASE 
    WHEN price > 100 THEN '高端产品'
    ELSE '普通产品'
  END AS product_type
FROM products;

这里，ELSE 确保如果价格不高于 100 的产品将被分类为 "普通产品"。

2.3 CASE WHEN 的嵌套使用

在一些复杂的业务场景中，可能需要在 CASE WHEN 中再嵌套使用 CASE WHEN。这是处理多层条件判断的有效方法。

SELECT 
  employee_name,
  CASE 
    WHEN department = '销售' THEN 
      CASE 
        WHEN sales > 10000 THEN '顶级销售员'
        ELSE '普通销售员'
      END
    ELSE '非销售部门'
  END AS employee_category
FROM employees;

 这段代码根据部门和销售额对员工进行分类。

CASE WHEN的高级用法

CASE WHEN 在 SELECT 子句中非常有用，可以根据条件动态计算或转换列值。

SELECT 
  order_id,
  order_date,
  CASE 
    WHEN order_date < '2024-01-01' THEN '旧订单'
    ELSE '新订单'
  END AS order_type
FROM orders;

这个示例根据订单日期判断订单是旧的还是新的。

3.2 在 WHERE 中使用 CASE WHEN

CASE WHEN 可以用于 WHERE 子句，以根据条件动态生成查询条件。

SELECT 
  product_name,
  price
FROM products
WHERE 
  CASE 
    WHEN category = '电子产品' THEN price > 500
    ELSE price > 100
  END;

这个查询根据产品类别设置不同的价格过滤条件。

3.3 在 ORDER BY 中使用 CASE WHEN

CASE WHEN 还能用于 ORDER BY 子句，实现自定义排序。

SELECT 
  employee_name,
  department,
  salary
FROM employees
ORDER BY 
  CASE 
    WHEN department = '人事' THEN salary
    ELSE salary DESC
  END;

这里，如果员工属于人事部门，工资按升序排序，否则按降序排序。

3.4 在 GROUP BY 和 HAVING 中使用 CASE WHEN

在数据分组和过滤时，CASE WHEN 能帮助实现更灵活的分组和筛选。

SELECT 
  department,
  COUNT(*),
  CASE 
    WHEN AVG(salary) > 5000 THEN '高薪部门'
    ELSE '普通部门'
  END AS department_type
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

这个示例先按部门分组，再根据平均工资分类，同时过滤出员工数超过 5 的部门。

四、案例分析

4.1 实现多条件的复杂查询

在实际业务中，常常需要对复杂的条件进行判断。CASE WHEN 可以帮助我们将这些条件逻辑简化。

SELECT 
  order_id,
  customer_name,
  CASE 
    WHEN order_amount >= 10000 AND order_date >= '2024-01-01' THEN '大单'
    WHEN order_amount < 10000 AND order_date >= '2024-01-01' THEN '普通单'
    ELSE '历史订单'
  END AS order_category
FROM orders;

这个查询根据订单金额和日期将订单分类。

4.2 数据清洗与转换

在数据清洗过程中，CASE WHEN 常用于数据格式的转换或数据的标准化。

SELECT 
  customer_name,
  phone_number,
  CASE 
    WHEN phone_number LIKE '1%' THEN CONCAT('+86 ', phone_number)
    ELSE phone_number
  END AS formatted_phone_number
FROM customers;

这个查询将以 1 开头的电话号码格式化为中国的国际电话号码格式。

4.3 统计与聚合操作中的应用

在统计和聚合操作中，CASE WHEN 可以用于条件统计，从而提供更细粒度的数据分析。

SELECT 
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = '已完成' THEN 1 ELSE 0 END) AS completed_orders,
  SUM(CASE WHEN status = '未完成' THEN 1 ELSE 0 END) AS pending_orders
FROM orders;

这个示例展示了如何统计已完成和未完成的订单数。

五、最佳实践与注意事项

5.1 编写高效的 CASE WHEN 语句

为了提高查询性能，编写高效的 CASE WHEN 语句是关键：

• 尽量避免嵌套过多的 CASE WHEN 语句，以减少计算复杂度。

• 对于经常使用的复杂条件，考虑将其封装为视图或函数。