插入优化

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

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

2、innodb引擎innodb_buffer_pool_size(值越大,IO读写就越少)、innodb_buffer_pool_instances 设置 ​​​这个参数在高并发高I/O时正确的配置非常重要,InnoDB缓存表和索引数据的内存区域。m

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

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 ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。

 

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

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​​大小的限制,其次是受到事务大小的限制。在开启事务的情况下使用批量插入,会节省不少事务的开销,如果要追求极致的速度的话,建议是开着事务插入的。不过需要注意一下,内存是有限且共享的,如果批量插入占用太多的事务内存,那么势必会对其他的业务操作等有一定的影响。

 

子查询优化查询速度

一次SQL查询优化原理分析(900W+数据,从17s到300ms)