mysql的sql优化

查询优化

一、不使用子查询

例：SELECT * FROM t1 WHERE id (SELECT id FROM t2 WHERE name=’hechunyang’);

子查询在MySQL5.5版本里，内部执行计划器是这样执行的：先查外表再匹配内表，而不是先查内表t2，当外表的数据很大时，查询速度会非常慢。

在MariaDB10/MySQL5.6版本里，采用join关联方式对其进行了优化，这条SQL会自动转换为 SELECT t1.* FROM t1 JOIN t2 ON t1.id = t2.id;

但请注意的是：优化只针对SELECT有效，对UPDATE/DELETE子查询无效，固生产环境应避免使用子查询

二、避免函数索引

例：SELECT * FROM t WHERE YEAR(d) >= 2016;

由于MySQL不像Oracle那样支持函数索引，即使d字段有索引，也会直接全表扫描。

应改为—–> SELECT * FROM t WHERE d >= '2016-01-01';

三、用IN来替换OR

低效查询—–>SELECT * FROM t WHERE LOC_ID = 10 OR LOC_ID = 20;

高效查询—–>SELECT * FROM t WHERE LOC_IN IN (10,20);

四、LIKE双百分号无法使用到索引

SELECT * FROM t WHERE name LIKE  '%de%';    —–>    SELECT * FROM t WHERE name LIKE  'de%';

五、避免数据类型不一致

SELECT * FROM t WHERE id = '19';    —–>    SELECT * FROM t WHERE id = 19;

六、分组统计可以禁止排序

SELECT goods_id,count(*) FROM t GROUP BY goods_id;

默认情况下，MySQL对所有GROUP BY col1，col2…的字段进行排序。如果查询包括GROUP BY，想要避免排序结果的消耗，则可以指定ORDER BY NULL禁止排序。

—–>SELECT goods_id,count(*) FROM t GROUP BY goods_id ORDER BY NULL;

七、避免随机取记录

SELECT * FROM t1 WHERE 1=1 ORDER BY RAND() LIMIT 4;

MySQL不支持函数索引，会导致全表扫描 —–>SELECT * FROM t1 WHERE id >= CEIL(RAND()*1000) LIMIT 4;

注：CEIL()函数为向上取整，即35.12取36，RAND()*1000为1到1000随机数。

八、应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描

例：select id from t where num is null；

备注、描述、评论之类的可以设置为 NULL，其他的，最好不要使用NULL。

不要以为 NULL 不需要空间，比如：char(100) 型，在字段建立时，空间就固定了， 不管是否插入值（NULL也包含在内），都是占用 100个字符的空间的，如果是varchar这样的变长字段， null 不占用空间。

可以在num上设置默认值0，确保表中num列没有null值。

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

十、在where查询多个字段时，尽量避免使用or

如果一个字段有索引，一个字段没有索引，将导致引擎放弃使用索引而进行全表扫描，

如：select id from t where num=10 or Name = 'admin'

可以这样查询：

select id from t where num = 10

union all

select id from t where Name = 'admin'

十一、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

十二、为需要经常查询的字段建立索引

十三、避免返回用不到的字段

尽量不要使用 select * from t ，用具体的字段列表代替“*”。

其他优化

一、批量INSERT插入

INSERT INTO t (id, name) VALUES(1,'Bea');

INSERT INTO t (id, name) VALUES(2,'Belle');

INSERT INTO t (id, name) VALUES(3,'Bernice');

—–>INSERT INTO t (id, name) VALUES(1,'Bea'), (2,'Belle'),(3,'Bernice');

二、Update 语句，如果只更改1、2个字段，不要Update全部字段，否则频繁调用会引起明显的性能消耗，同时带来大量日志。 三、索引并不是越多越好

索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样 建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

四、尽量使用数字型字段

若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连 接时会 逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。

五、尽可能的使用 varchar/nvarchar 代替 char/nchar

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

六、根据业务可以考虑横向表

由于Mysql是按行读取，行数多读取会比较慢，如果按列存储，横向扩展列，行数大大减少，效率会提升很多。 

这里举个例子

业务场景：a、b、c三个商品，需要按照月份存储该商品的销量。

纵向表：

id	goods	month	sale_num
1	a	2018.1	10
2	b	2018.1	12
3	c	2018.1	8
4	a	2018.2	15
5	b	2018.2	17
6	c	2018.2	11
...	...	...	...

转换为：

横向表：

name	2018.1	2018.2	...
a	10	15	...
b	12	17	...
c	8	11	...

 

这样我们需要的是扩展列，从而提高查询效率

七、尽量避免使用游标 因为游标的效率较差，如果游标操作的数据超过1万行，那么就可以考虑改写查询方法了。

八、尽量避免过大的事务操作，提高系统并发能力。 九、尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。(虽然往往咱们开发人员并没有话语权。。。)