【mysql】基础语法/索引部分优化

本文前半部分详情请跳转原文：https://blog.youkuaiyun.com/m0_56864757/article/details/125217302

针对于mysql的优化主要是考虑一下几个方面：

1. 减少访问的数据：合理字段类型，合理使用索引减少磁盘IO。
2. 返回更少数据：杜绝返回大量不需要数据，增加io以及网络io。
3. 减少交互次数：可以批量操作的尽量DML批量操作。
4. 减少cpu开销：减少排序等的不必要的复杂操作以及全表查询减少cpu内存占用。
5. 利用更多资源：可以使用分区操作，增加并行操作，更大限度利用CPU。

主要总结为两点：

1. 最大化合理化利用索引。（不可以无脑上索引需要看维护成本）
2. 避免全表扫描以及无效数据的额外查询。

mysql语法部分优化：

1.sql语句中in后面如果是子查询话，会导致全表扫描，因为子查询的结果是未知的，不可以作为外层的索引判断。情况2：in后续跟的是固定的值（1,2,3）这种形式，如果包含的值很多索引失效。

解决：如果后续的固定值是连续的值的话,可以用between代替。如果in后续跟的是子查询，可以用关联的方式代替in（效率会高很多）。

2.Select查询必须指明字段，减少返回额外数据。

3.限制条件中有其他字段没有索引，减少使用or。（或者用union代替）

4.Union和union all的使用。不涉及去重使用union。Union all需要合并进行唯一性条件过滤，涉及到排序等的操作增加cpu消耗。

In 和 exists not in not exists 以及  inner join：

5.in和exists以及not in 和 not exists的使用：

In和exists 最大的区别是谁驱动的问题，不能说exists 的效率一定比in高。in的驱动表是子查询，而exists 的驱动表是 外层表。所以 子查询数据比较小用in，外层数据小用exists 。（in是将子查询的id和外层做hash连接，exists 是循环外层然后再对内表进行查询）

6.not in 和not exists的使用：

Not in内外表都会扫描没有用到扫描，所以不管那个表大，not  exists的效率都更高。（not  exists 的子查询可以使用索引）

7.in 和 join：

结果集比较小的情况下，in的效率更高。结果集较大，join的效率优于in。

子查询有创建临时表删除临时表的过程。

总结：小数据量几种方式的差距不是很明显，大数据量下

Join 高于 exists 和in   （exists 和in 取决于那个是驱动表）

Join高于 not exists  高于 not in

8.where过滤判断null，like 模糊查询 ‘%%’ 数值头部迷糊查询。。。详情看下面的索引失效情况整理。

9.索引失效：联合索引，其中一个字段有 between 等的 范围查询，索引失效

10.驱动表优化：关联问题，尽可能使用小表驱动大表。Inner join默认数据量小的表作为小表。

驱动表循环遍历次数越少，会减少io和cpu的压力。

11.分页优化：数据量大的时候 直接  limit  1000000,20 比较慢，可以 >100000 limit 20。

下面整理了一些索引会失效的情况：

本文后半部分原文详情请跳转：你知道mysql哪些查询情况不走索引吗_Mysql_脚本之家

语法上针对索引优化主要是考虑索引失效的情况：

1.索引列如果在where等的条件后 有计算，不走索引。

举例：  where   money+10=20；（不走索引）

2.索引列应用了函数，可能不走索引

举例： where  concat(name,’tmp’)=zytmp;(不走索引)

举例： where  name=concat(zy,’tmp’);(走索引)

可以将函数等的操作放在等号后面的值上面，避免函数操作索引列。

3.索引列使用了like模糊查询，可能不走索引。

Like模糊查询不能从头开始模糊查询，会导致索引失效。如果直走尾部模糊查询，可以走索引。

举例： where name like  ‘%jack%’; (不走索引)

where name like  ‘jack%’; (模糊查询只模糊尾部，走索引)

4.数据有隐式转换即数据类型不一致（字符串和数字类型直接比较），不走索引。

5.Where 后续的过滤有or关键字，只要是or连接的字段，有一个字段没有索引，整个语句就不走索引。

6.where 过滤时出现不等于（！= 或者 <>）的操作：

Where   id!=2  或者  Where   id<>2 （不走索引）

7.出现空值的判断 is  null，is not null的情况，不走索引。

8.索引列使用in语句，in的数据量大的时候不走索引。

举例： where  name in  （‘jack’）;（走索引）（name是varchar类型）

where  name in  （123）;（不走索引）（数据类型不一致）

Where   name in  （大量数据）;（不走索引）
9.查询结果集是原表的大部分数据，应该是 25%以上，就不走索引了。