MySQL性能优化总结之一

一、查看需要优化的SQL语句 

执行命令：show processlist

processlist表是MySQL数据库安装好以后自带的四个数据库（information_schema，performance_schema，mysql，sys）之一information_schema数据库里面的表。查询mysql数据库自带的表无需使用select ，直接使用show table_name;

从返回结果中我们可以了解该线程执行了什么命令/SQL 语句以及执行的时间。实际应用中，查询的返回结果会有 N 条记录。

其中，返回的 State 的值是我们判断性能好坏的关键，其中state字段的值常见有以下几种，则该行记录的 SQL 语句需要优化：

1. Converting HEAP to MyISAM          # 查询结果太大时，把结果放到磁盘，严重，必须优化sql

2. Create tmp table                #创建临时表，严重，需要优化

3. Copying to tmp table on disk          #把内存临时表复制到磁盘，严重，需要优化

4. locked              #被其他查询锁住，严重，需要优化

5. loggin slow query         #记录慢查询

6. Sorting result               #排序

二 ，优化手段

1. 查询优化

1) 避免 SELECT *，需要什么数据，就查询对应的字段。

2) 小表驱动大表，即小的数据集驱动大的数据集。如：以 A，B 两表为例，两表通过 id 字段进行关联。

当 B 表的数据集小于 A 表时，用 in 优化 exist；使用 in ，两表执行顺序是先查 B 表，再查 A 表

select * from A where id in (select id from B)

当 A 表的数据集小于 B 表时，用 exist 优化 in；使用 exists，两表执行顺序是先查 A 表，再查 B 表

select * from A where exists (select 1 from B where B.id = A.id)

3) 一些情况下，可以使用连接代替子查询，因为使用 join，MySQL 不会在内存中创建临时表。

4) 适当添加冗余字段，减少表关联。

5) 合理使用索引（下文介绍）。如：为排序、分组字段建立索引，避免 filesort 的出现。更多：来一份MySQL索引数据结构及优化清单

2. 使用索引

2.1 适合使用索引的场景

1) 主键自动创建唯一索引

2) 频繁作为查询条件的字段

3) 查询中与其他表关联的字段

4) 查询中排序的字段

5) 查询中统计或分组字段

2.2 不适合使用索引的场景

1) 频繁更新的字段

2) where 条件中用不到的字段

3) 表记录太少

4) 经常增删改的表

5) 字段的值的差异性不大或重复性高

2.3 索引创建和使用原则

1) 单表查询：哪个列作查询条件，就在该列创建索引

2) 多表查询：left join 时，索引添加到右表关联字段；right join 时，索引添加到左表关联字段

3) 不要对索引列进行任何操作（计算、函数、类型转换）

4) 索引列中不要使用 !=，<> 非等于

5) 索引列不要为空，且不要使用 is null 或 is not null 判断

6) 索引字段是字符串类型，查询条件的值要加''单引号,避免底层类型自动转换

违背上述原则可能会导致索引失效，具体情况需要使用 explain 命令进行查看

2.4 索引失效情况

除了违背索引创建和使用原则外，如下情况也会导致索引失效：

1) 模糊查询时，以 % 开头

2) 使用 or 时，如：字段1（非索引）or 字段2（索引）会导致索引失效。

3) 使用复合索引时，不使用第一个索引列。

index(a,b,c) ，以字段 a,b,c 作为复合索引为例：