mysql优化

在sql查询中为了提高查询效率,我们常常会采取一些措施对查询语句进行sql优化:

①应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引

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

2.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如： 

select id from t where num=10 or num=20	
可以这样查询：	
select id from t where num=10	
union all	
select id from t where num=20

3.in 和 not in 也要慎用，否则会导致全表扫描，如：	
select id from t where num in(1,2,3)	
对于连续的数值，能用 between 就不要用 in 了：	
select id from t where num between 1 and 3

可以使用not exists或exists代替，如:
SELECT DISTINCT
 t.id,
 tg.spu_id,
 t.is_default,
 t.sku_no,
 t.sku_name,
 t.sku_cover
FROM
 temp_goods t
LEFT JOIN third_goods_mapping tg ON t.third_spu_id = tg.third_spu_id
where not exists (select * from goods_sku gs where gs.sku_no= t.sku_no)

4.尽可能的使用 varchar 代替 char ，因为首先变长字段存储空间小，可以节省存储空间， 

其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

5.索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率， 

因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。	
一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要

6.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段

7.下面的查询也将导致全表扫描：（like模糊查询%在前面会使索引失效，但如果%只在字段后面，索引依然有效）

select id from t where name like ‘%abc%’

若要提高效率，可以考虑全文检索。

8.mysql Using join buffer (Block Nested Loop) join连接查询优化：应该在连接的条件列上加索引，并且链接的条件列需要在索引的首位（这点之前发现建索引的时候，2张结构几乎完全一样的表，相同的字段都建立了索引，但连接查询后发现，一张表使用的索引，另一张表没有使用，之后改变下顺序，优化效果立现，ps:这2张表的结构相似，索引建立后刷新索引发现索引顺序不一样）

9.查询sql语句中使用了in的都尽量使用inner join 优化

10.使用联合索引时遵循最左原则

11.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描

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

13.index_merge：对多个索引分别进行条件扫描，然后将它们各自的结果进行合并(intersect/union)。

MySQL在分析执行计划时发现走单个索引的过滤效果都不是很好，于是对多个索引分别进行条件扫描，然后将多个索引单独扫描的结果进行合并的一种优化操作。合并的方式分为三种：intersection、union和sort_union。

 

14.force index(indexname)：mysql可能并不总会选择合适且效率高的索引去查询，这时适当的force index(indexname) 强制告诉mysql使用什么索引尤为重要

 

 

explain中的列详解：

id列:id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的

select_type列：

a>.simple：简单查询。查询不包含子查询和union

b>.primary：复杂查询中最外层的 select

c>.subquery：包含在 select 中的子查询（不在 from 子句中）

d>.derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

e>.union：在 union 中的第二个和随后的 select

f>.union result：从 union 临时表检索结果的 select

 

key_len列：索引字段的最可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

ref列：显示索引的哪一列或常量被用于查找索引列上的值。

type列:依次从最优到最差分别为：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

Extra列：

a>.Using filesort： 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
b>.Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
c>.Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using where，表示索引用来读取数据而非执行查找动作。
覆盖索引（Covering Index） ：也叫索引覆盖，就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。
d>.Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
e>.Using where： 表明使用了where 过滤
f>.Using join buffer： 表明使用了连接缓存
g>.impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
h>>distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

filtered列：一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

通过explain的参数介绍，我们可以得知:
1 表的读取顺序(id)
2 数据读取操作的操作类型(type)
3 哪些索引被实际使用(key)
4 表之间的引用(ref)
5 每张表有多少行被优化器查询(rows)

 

总结：

1 索引是排好序且快速查找的数据结构。其目的是为了提高查询的效率。
2 创建索引后，查询数据变快，但更新数据变慢。
3 性能下降的原因很可能是索引失效导致。
4 索引创建的原则，经常查询的字段适合创建索引，频繁需要更新的数据不适合创建索引。
5 索引字段频繁更新，或者表数据物理删除容易造成索引失效。
6 擅用 explain 分析sql语句
7 除了优化sql语句外，还可以优化表的设计。如尽量做成单表查询，减少表之间的关联。设计归档表等。

 

https://www.cnblogs.com/itdragon/p/8146439.html

 

join的原理与索引注意事项参考：

https://blog.csdn.net/qq_22996201/article/details/97950839

mysql索引底层原理分析参考：

https://blog.csdn.net/alex_xfboy/article/details/82818753#%E5%89%8D%E8%A8%80%EF%BC%9Ainnodb%E5%92%8Cmyisam%E7%9A%84%E5%8C%BA%E5%88%AB

二叉树，平衡二叉树，红黑树，B-树、B+树、B*树的区别参考：

https://blog.csdn.net/wyqwilliam/article/details/82935922

group by 优化参考：

https://blog.csdn.net/xiaobing_blog/article/details/17166471

Order By 优化参考：

https://blog.csdn.net/weixin_36826009/article/details/82988278

 

 

mysql 批量insert或update数据：

insert into tableName(type1,type2,type3) values (“q1”,”w1”,”e1”),( “q2”,”w2”,”e2”) ,( “q3”,”w3”,”e3”)；

UPDATE app_history_statistics_by_hour SET h00 = CASE statisticalType WHEN "qqq" THEN 11111 WHEN "www" THEN 22211 WHEN 'eee' THEN 333111 ELSE 0 END WHERE weeks='222';

UPDATE app_history_statistics_by_hour 

    SET h00 = CASE id 

        WHEN 1 THEN 3 

        WHEN 2 THEN 4 

        WHEN 3 THEN 5 

    END, 

    h01 = CASE id 

        WHEN 1 THEN 'New Title 1'

        WHEN 2 THEN 'New Title 2'

        WHEN 3 THEN 'New Title 3'

    END

WHERE id IN (1,2,3)；