MYSQL查询插入优化 | 索引相关知识

查询优化

避免使索引失效的操作

查询可以利用索引提高速度
一、会使索引失效的操作

1. 隐式转换——比如字符串字段查询时候没加引号
2. 对索引字段操作—— NOT IN、<>、!= （但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的）和 数学运算、复杂函数处理，例如left() FROM_UNIXTIME()不能
3. like 语句后第一有效字符如果是%号索引会失效，like ‘%aaa%’不会使用索引，like ‘aaa%’可以使用索引
4. 列中包含有null值，会使索引失效
5. or 连接的两个字段必须都为索引列，否则查询不会应用索引。

PS:

• MySQL查询只能使用一个索引，如果where中使用了，order by 中就不会使用了（尽量不要使用排序）

• 并不是所有隐士转换都会发生索引失效
  大神地址:MySQL性能优化：MySQL中的隐式转换造成的索引失效

慢sql分析定位

如果show profiles没有结果，可以set profiling=on;

> SELECT * FROM `t` limit 100000; # 用时17s
> show profiles;
ID  Duration    Query         
108	0.0010325	SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/0.004335*100,3), '%') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=105 GROUP BY STATE ORDER BY SEQ
109	0.000193	SELECT * FROM `test`.`t` LIMIT 0
110	0.000604	SHOW COLUMNS FROM `test`.`t`
111	0.000129	SET PROFILING=1
112	0.0008745	SHOW STATUS
113	0.00079825	SHOW STATUS
114	14.6238845	SELECT * FROM `t` limit 100000  # 用时最长
115	0.00084075	SHOW STATUS
116	0.00089075	SELECT QUERY_ID, SUM(DURATION) AS SUM_DURATION FROM INFORMATION_SCHEMA.PROFILING GROUP BY QUERY_ID
117	0.00083275	SELECT STATE AS `Status`, ROUND(SUM(DURATION),7) AS `Duration`, CONCAT(ROUND(SUM(DURATION)/14.623887*100,3), '%') AS `Percentage` FROM INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID=114 GROUP BY STATE ORDER BY SEQ
118	0.0001865	SELECT * FROM `test`.`t` LIMIT 0
119	0.000481	SHOW COLUMNS FROM `test`.`t`
120	0.000126	SET PROFILING=1
121	0.00079825	SHOW STATUS
122	0.00076075	SHOW STATUS

> show profile for query 114;
Status                 Duration 
starting	           6.7E-5
checking permissions   6E-6
Opening tables	       1.8E-5
init	               1.9E-5
System lock	           1E-5
optimizing	           5E-6
statistics	           1.1E-5
preparing	           1.7E-5
executing	           3E-6
Sending data           14.623626  # 用时最长（也有可能是其他项时间长，这里只是例子）
end	                   3.5E-5
query end	           7E-6
closing tables	       1.9E-5
freeing items	       1.2E-5
logging slow query	   3E-6
cleaning up	           2.9E-5

> SELECT id FROM `t` limit 100000; # 用时6s（我的数据库挺垃圾的，但至少能看出来减少了，稍后研究为啥这么慢）
# 大佬地址：https://blog.youkuaiyun.com/reblue520/article/details/80553344
# sending data=“收集 + 发送 数据”，这里的关键是为什么要收集数据，原因在于：
# mysql使用“索引”完成查询结束后，mysql得到了一堆的行id，如果有的列并不在索引中，
# mysql需要重新到“数据行”上将需要返回的数据读取出来返回个客户端。explain分析方法，正确添加索引。

插入优化

一、 大量插入时可以先禁用索引，

ALTER TABLE  test DISABLE KEYS;

重新开启索引语句如下

ALTER TABLE test ENABLE KEYS;

二、 优化INSERT语句 ，一次插入多条比一条一条多次插入速度要快

三、 LOAD DATA INFILE 比INSERT语句快