当前位置：首页 > news >正文

【mySQL】数据库优化方案

news 来源：原创 2024/5/14 7:43:55

mysql开发中文博客：https://imysql.cn/

推荐阅读：https://www.cnblogs.com/pengyunjing/p/6591660.html《MySQL 性能优化的最佳20多条经验分享》

------

mysql 速度优化方案：http://www.imooc.com/article/291781

插入优化

1、批量插入代替单条插入

(一次插入多少条最快？测试发现，SQL语句大小为max_allowed_packet的一半时最快)

2、innodb引擎innodb_buffer_pool_size(值越大，IO读写就越少)、innodb_buffer_pool_instances 设置 (https://blog.csdn.net/bandaoyu/article/details/106216333)这个参数在高并发高I/O时正确的配置非常重要，InnoDB缓存表和索引数据的内存区域。m

3、注意锁(行锁、表锁)

(https://blog.csdn.net/bandaoyu/article/details/106196416)

4、事务管理

innodb_log_buffer_size和innodb_log_file_size。

innodb_log_buffer_size表示InnoDB写入到磁盘上的日志文件时使用的缓冲区的字节数，默认值为8M。一个大的日志缓冲区允许大量的事务在提交之前不写日志到磁盘。因此，如果你有很多事务的更新，插入或删除很操作，通过这个参数会大量的节省了磁盘I / O。

5、不让MySQL进行反向DNS解析(导致连接耗时)skip-name-resolve

查询优化：

1、没有加索引或者索引失效

避免全表扫描：

首先应考虑在 where 及 order by 涉及的列上建立索引。

避免以下导致引擎放弃使用索引而进行全表扫描的操作

1)应尽量避免在 where 子句中对字段进行 null 值判断

2)应尽量避免在 where 子句中使用!=或<>操作符，

3)应尽量避免在 where 子句中使用 or 来连接条件

4)5.in 和 not in 也要慎用，否则会导致全表扫描

5)select id from t where name like '%abc%'

6)如果在 where 子句中使用参数，也会导致全表扫描。

7)应尽量避免在 where 子句中对字段进行表达式操作，

select id from t where num/2=100 应改为: select id from t where num=100*2

8)应尽量避免在where子句中对字段进行函数操作

9)不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引

13.很多时候用 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)

17.尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。

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

https://www.cnblogs.com/carsonwuu/p/9669569.html

where条件使用如下语句会索引失效：null、！=、<>、or连接、in(非要使用，可用关键字exist替代)和not in、'%abc%'；
使用参数：num=@num、表达式操作：where num/2=100、函数操作：where substring(name，1,3)=‘abc’-name；
https://blog.csdn.net/qq_39416311/article/details/82315090

2：查询的数据量过大，返回不必要的行和列

只查询有用的字段，不要用*查询出所有字段。
采用多线程多次查询。如果查询条件是某段时间之类的范围条件，可以把时间条件切分，多次查询结果合并。

3：锁或者死锁

4： I/O吞吐量小，形成瓶颈效应。

5：内存不足。

少造对象，对象只在需要使用时创建，不要在整个上下文传递。
及时清理jvm内存。

6：网络速度慢。

5、不让MySQL进行反向DNS解析(导致连接耗时)skip-name-resolve

一些SQL优化方法1：如果索引是复合索引，必须使用该索引的第一个字段作为条件才能保证系统使用该索引，否则索引不会被引用，并且应尽可能的让字段顺序与索引顺序一致。

2：索引并不是越多越好，一个表索引最好不要超过6个。索引固然可以提高select效率，但是也降低了insert效率和update效率，因为insert和update会使索引重建，所以怎么建索引需要慎重考虑。

3：建表的一些优化：

尽量使用数字型字段，若数据只含有数值信息尽量不要设计成字符型，这会降低查询和连接的性能，并会增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中每个字符，而对于数字型而言只需比较一次就够了。尽量使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。4：任何地方都不要使用select * from table，用具体的字段列表代替*，不要返回用不到的任何字段。

6：并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段 sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。

7：尽量避免大事务操作，提高系统并发能力。

注意事项：
1：使用like时，一定要记得判空

... where name like ‘%’.变量名.'%'; (变量值是从外面传进来的)

如果：变量是空，就变成如下sql

...where name like '%%'; -- 这个条件造成的后果就是 ‘选出全部数据 or 更新全部数据 or 删除全部数据’ 相当于没有写条件，出现后是相当严重的问题。

2：like 配合通配符：%和_ 的使用

通配符的分类:
%百分号通配符: 表示任何字符出现任意次数**(可以是0次)**.
_下划线通配符:表示只能匹配单个字符,不能多也不能少,就是一个字符.

like操作符:
LIKE作用是指示mysql后面的搜索模式是利用通配符而不是直接相等匹配进行比较.
注意: 如果在使用like操作符时,后面的没有使用通用匹配符效果是和=一致的,SELECT * FROM products WHERE products.prod_name like '1000';只能匹配的结果为1000,而不能匹配像JetPack 1000这样的结果.

1)%通配符使用:
匹配以"yves"开头的记录:(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like 'yves%';

匹配包含"yves"的记录(包括记录"yves")
SELECT * FROM products WHERE products.prod_name like '%yves%';

匹配以"yves"结尾的记录(包括记录"yves",不包括记录"yves ",也就是yves后面有空格的记录,这里需要注意)
SELECT * FROM products WHERE products.prod_name like '%yves';

2)_通配符使用:
SELECT * FROM products WHERE products.prod_name like '_yves';
匹配结果为: 像"yyves"这样记录.

SELECT * FROM products WHERE products.prod_name like 'yves__';
匹配结果为: 像"yvesHe"这样的记录.(一个下划线只能匹配一个字符,不能多也不能少)

补充说明：

1、(一次插入是涉及到事务和锁的，在插入索引的时候，要判断缓冲区的剩余情况，所以插入并不能仅仅只考虑max_allowed_packet的问题，也要考虑到缓冲区的大小)

另外对于innodb引擎来说，因为存在插入缓存(Insert Buffer)这个概念，所以在插入的时候也是要耗费一定的缓冲池内存的。当写密集的情况下，插入缓冲会占用过多的缓冲池内存，默认最大可以占用到1/2的缓冲池内存，当插入缓冲占用太多缓冲池内存的情况下，会影响到其他的操作。

插入缓冲受到缓冲池大小的影响，缓冲池大小为：

mysql> show variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+

换算后的结果为：128M，也就是说，插入缓存最多可以占用64M的缓冲区大小。这个大小要超过咱们设置的sql语句大小，所以可以忽略不计。

批量插入的一些优化

1、SQL语句的大小限制

SQL是max_allowed_packet的1/2最好

SQL不能大于innodb插入缓冲的限制innodb_buffer_pool_size的1/2

2、使用事务提升效率

还有一种说法，使用事务可以提高数据的插入效率，这是因为进行一个INSERT操作时，MySQL内部会建立一个事务，在事务内才进行真正插入处理操作。通过使用事务可以减少创建事务的消耗，所有插入都在执行后才进行提交操作。大

START TRANSACTION;
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('0', 'userid_0', 'content_0', 0);
INSERT INTO `insert_table` (`datetime`, `uid`, `content`, `type`) 
    VALUES ('1', 'userid_1', 'content_1', 1);
...
COMMIT;

这种写法和批量写入的效果差不多，只不过sql语句还是单句的，然后统一提交。

3、innodb_log_buffer_size配置

事务需要控制大小，事务太大可能会影响执行的效率。MySQL有innodb_log_buffer_size配置项，超过这个值会把innodb的数据刷到磁盘中，这时，效率会有所下降。所以比较好的做法是，在数据达到这个这个值前进行事务提交。
这种写法和批量写入的效果差不多，只不过sql语句还是单句的，然后统一提交。

一个瓶颈是SQL语句的大小，一个瓶颈是事务的大小。当我们在提交sql的时候，首先是受到sql大小的限制，其次是受到事务大小的限制。在开启事务的情况下使用批量插入，会节省不少事务的开销，如果要追求极致的速度的话，建议是开着事务插入的。不过需要注意一下，内存是有限且共享的，如果批量插入占用太多的事务内存，那么势必会对其他的业务操作等有一定的影响。