sql日常优化分享

1.1sql优化的重要性

优化成本：硬件>系统配置>数据库表结构>SQL及索引
优化效果：硬件<系统配置<数据库表结构<SQL及索引

总结：效果好、成本低、立竿见影
1.2 sql优化的五原则
1、减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘io
2、返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io
3、减少交互次数： 批量DML操作，函数存储等减少数据连接次数
4、减少服务器CPU开销： 尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
5、利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源
总结到SQL优化中，就三点:
最大化利用索引；尽可能避免全表扫描；减少无效数据的查询；
PS：SQL优化，主要就是在优化索引
1.3 索引介绍
索引：相当于书的目录
索引：index是帮助MYSQL高效获取数据的数据结构。索引是数据结构（树：B树（默认）、Hash树…）

索引的弊端：
1、索引本身很大，可以存放在内存/硬盘（通常为硬盘）
2、索引不是所有情况均适用：a、少量数据 b、频繁更新的字段 c、很少使用的字段
3、索引会降低增删改的效率

索引的优势：
1、提高查询效率（降低IO使用率）
2、降低CPU使用率（例如order by，因为B树索引本身就是一个排好序的结构，因此在排序时可以直接使用）

体会使用索引如何提升效率：

索引分类：
单值索引：单列，一个表可以有多个单值索引(name、age)
主键索引：不能重复（id）不能为null
唯一索引：不能重复（id）可以为null
复合索引：多个列构成的索引(相当于二级目录)
索引创建：
方式一：create 索引类型 索引名 on 表(字段)
方式二：alter table 表名 索引类型 索引名(字段)
单值：
create index dept_index on tb(dept);
alter table tb add index dept_index(dept);
唯一：
create unique index name_index on tb(name);
alter table tb add unique index name_index(name);
复合索引：
create index dept_name_index on tb(dept,name);
alter table tb add index dept_name_index(dept,name);
注意：
如果一个字段是primary key，则该字段默认就是主键索引。
删除索引：
drop index 索引名 on 表名；
drop index name_index on tb;
查询索引：
show index from 表名；

1.4 sql执行顺序
理解SQL优化原理 ，首先要搞清楚SQL执行顺序：
SELECT语句 - 语法顺序：

SELECT语句 - 执行顺序：

1.5 sql性能分析
a、分析SQL的执行计划：explain模拟SQL优化器执行语句，知道自己编写的SQL状况。
b、Mysql查询优化器会干扰我们的优化；
InnoDB（mysql默认）：事务优先--------适合高并发操作、行锁
MyISAM：性能优先---------表锁

mysql之explain详解：
例如：
explain select * from system_dict where type = ‘1’;

各参数详解总述：（12个参数）

补充：
partitions表示查询访问的分区（一般不做数据库分区可以不关注）

1、id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id的结果共有3中情况：

2、select_type

分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
SIMPLE 简单的select查询，查询中不包含子查询或者UNION
PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY 在SELECT或WHERE列表中包含了子查询
DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中
UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT 从UNION表获取结果的SELECT

3、table
这一列表示 explain 的一行正在访问哪个表
4、type
这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行。
依次从最优到最差分别为：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说，得保证查询至少达到range级别，最好能达到ref,避免出现ALL

5、possible_keys
这一列显示查询可能使用哪些索引来查找。
explain 时可能出现 possible_keys 有列，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。
如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
6、key
这一列显示mysql实际采用哪个索引来优化对该表的访问。
如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index。
7、key_len
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。
举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。
8、ref
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

9、rows
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
（衡量查询效率的重要参数，越小越好）

10、filtered
显示了通过条件过滤出的行数的百分比估计值，只要执行两条sql语句，一条有where，一条无where，就可以看出filtered的作用。

11、Extra
包含不适合在其他列中显式但十分重要的额外信息
distinct: 一旦mysql找到了与行相联合匹配的行，就不再搜索了
Using index：这发生在对表的请求列都是同一索引的部分的时候，返回的列数据只使用了索引中的信息，而没有再去访问表中的行记录。是性能高的表现。
Using where：mysql服务器将在存储引擎检索行后再进行过滤。就是先读取整行数据，再按 where 条件进行检查，符合就留下，不符合就丢弃。需要回表查询
Using temporary：mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。常见于group by 语句中
Using filesort：mysql 会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。此时mysql会根据联接类型浏览所有符合条件的记录，并保存排序关键字和行指针，然后排序关键字并按顺序检索行信息。这种情况下一般也是要考虑使用索引来优化的。常见于order by 语句中。
1.6 避免不走索引的场景
1.尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE username LIKE ‘%陈%’;
优化方式：尽量在字段后面使用模糊查询。如下
SELECT * FROM t WHERE username LIKE ‘陈%’;

如果需求是要在前面使用模糊查询，使用MySQL内置函数INSTR(str,substr) 来匹配，如下：（附带函数使用说明）

2、尽量避免使用in 和not in，会导致引擎走全表扫描。如下：
SELECT * FROM t WHERE id IN (2,3);
优化方式：如果是连续数值，可以用between代替。如下：
SELECT * FROM t WHERE id BETWEEN 2 AND 3;
如果是子查询，可以用exists代替。如下：
– 不走索引
select * from A where A.id in (select id from B);
– 走索引
select * from A where exists (select * from B where B.id = A.id);

3、尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE id = 1 OR id = 3;
优化方式：可以用union代替or。如下：
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3

4、尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描。如下：
SELECT * FROM t WHERE score IS NULL;
优化方式：可以给字段添加默认值0，对0值进行判断。如下：
SELECT * FROM t WHERE score = 0;

5、尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描。
可以将表达式、函数操作移动到等号右侧。如下：
– 全表扫描
SELECT * FROM T WHERE score/10 = 9
– 走索引
SELECT * FROM T WHERE score = 10*9
6、当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描。如下：
SELECT username, age, sex FROM T WHERE 1=1;
优化方式：用代码拼装sql时进行判断，没 where 条件就去掉 where，有where条件就加 and。

7、查询条件不能用 <> 或者 !=
使用索引列作为条件进行查询时，需要避免使用<>或者!=等判断条件。如确实业务需要，使用到不等于符号，需要在重新评估索引建立，避免在此字段上建立索引，改由查询条件中其他索引字段代替。

8、where条件仅包含复合索引非前置列
如下：复合（联合）索引包含key_part1，key_part2，key_part3三列，但SQL语句没有包含索引前置列"key_part1"，按照MySQL联合索引的最左匹配原则，不会走联合索引。
select col1 from table where key_part2=1 and key_part3=2;

9、隐式类型转换造成不使用索引
如下SQL语句由于索引对列类型为varchar，但给定的值为数值，涉及隐式类型转换，造成不能正确走索引。
select col1 from table where col_varchar=123;

10、order by 条件要与where中条件一致，否则order by不会利用索引进行排序
– 不走age索引
SELECT * FROM t order by age;
– 走age索引
SELECT * FROM t where age > 0 order by age;

对于上面的语句，数据库的处理顺序是：
第一步：根据where条件和统计信息生成执行计划，得到数据。
第二步：将得到的数据排序。当执行处理数据（order by）时，数据库会先查看第一步的执行计划，看order by 的字段是否在执行计划中利用了索引。如果是，则可以利用索引顺序而直接取得已经排好序的数据。如果不是，则重新进行排序操作。
第三步：返回排序后的数据。
当order by 中的字段出现在where条件中时，才会利用索引而不再二次排序，更准确的说，order by 中的字段在执行计划中利用了索引时，不用排序操作。
这个结论不仅对order by有效，对其他需要排序的操作也有效。比如group by 、union 、distinct等。

11、正确使用hint优化语句
MySQL中可以使用hint指定优化器在执行时选择或忽略特定的索引。一般而言，处于版本变更带来的表结构索引变化，更建议避免使用hint，而是通过Analyze table多收集统计信息。但在特定场合下，指定hint可以排除其他索引干扰而指定更优的执行计划。

USE INDEX 在你查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。例子: SELECT col1 FROM table USE INDEX (mod_time, name)…
IGNORE INDEX 如果只是单纯的想让 MySQL 忽略一个或者多个索引，可以使用 IGNORE INDEX 作为 Hint。例子: SELECT col1 FROM table IGNORE INDEX (priority) …
FORCE INDEX 为强制 MySQL 使用一个特定的索引，可在查询中使用FORCE INDEX 作为Hint。例子: SELECT col1 FROM table FORCE INDEX (mod_time) …
在查询的时候，数据库系统会自动分析查询语句，并选择一个最合适的索引。但是很多时候，数据库系统的查询优化器并不一定总是能使用最优索引。如果我们知道如何选择索引，可以使用FORCE INDEX强制查询使用指定的索引。
————————————————
例如：
SELECT * FROM students FORCE INDEX (idx_class_id) WHERE class_id = 1 ORDER BY id DESC;

12、表字段字符集导致查询索引不生效
xxxx项目案例分享：
现象：
关联查询区域表索引一直无法使用，导致区域表一直是全扫描，limit查询时间超过8s

排查问题：
排查发现revolution_basic_info表和revolution_organization表的area_code的字符集有问题，改为uft-8和utf8_general_ci后，明显使得system_area表的code字段的索引生效了。查询时间1s之内出结果。

查看mysql服务字符编码配置情况：
show variables like ‘%char%’;

注意：
1、除了character_set_filesystem外，其它全部改为utf8。
2、修改编码只对之后创建的数据库生效，建议第一时间修改编码。
1.7 主动监控慢查询
系统层面开启慢查询监控，查询时间阈值可调，一般msql慢查询日志记录目录为：/mysqldata/mysql/mysql-slow.log
每次处理完慢查询优化后记得手动做日志结转，方便下次监控。

=========================================
内容如有不当之处，请予留言指正，谢谢！