mysql性能优化--建索引

MYSQL性能优化

一、索引简介

索引是帮助mysql高效获取数据的数据结构-------理解：排好序的快速查找数据结构
索引的目的：提高查询效率，可以类比字典
索引的优势：
（1）提高数据检索的效率，降低数据库的IO(输入输出)成本
（2）通过索引对数据进行排序，降低数据排序成本，降低了CPU的消耗
索引的劣势：
（1）占用空间
（2）降低了更新表的速度（不仅要保存数据，还要保存索引文件每次更新添加了索引列字段）
索引创建：
create index indexname on mytable(column name)

1、SQL性能下降

1、查询语句写的烂
2、索引失效
3、关联太多join
4、服务器调优及各个参数设置

2、常见通用的查询

1）SQL执行顺序

（8）Select
（9）distinct 字段名1,字段名2，
（6）[fun(字段名)sum()、avg()]  
（1）from 表1
（3）<join类型>join 表2 
（2）on <join条件> 
（4）where <where条件> 
（5）group by <字段> 
（7）having <having条件> 
（10）order by <排序字段> 
（11）limit <起始偏移量,行数>

首先from可以获得主表信息，然后join此时也获得了另一个表信息，执行join时要生成中间表，如果先进行join而不是on，此时则是两个大表之间进行笛卡尔乘积（sql里面表与表之间进行关联时是通过笛卡尔乘积生成中间表），而如果先通过on进行条件筛选，然后再进行join关联，则计算量会大大减少。
2、join几种
（1）A与B相交中只有A

select 字段1,字段2
from tableA left join tableB
on A.key=B.key
where B.key is null

（2）A与B相交中只有B

select 字段1,字段2
from tableA left join tableB
on A.key=B.key
where A.key is null

3、哪些情况适合建索引

1、频繁作为查询条件的字段应该创建索引
2、查询中与其他表关联的字段，外键关系建立索引
3、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
4、查询中统计或分组字段

4、哪些情况不适合建索引

1、表记录太少
2、经常增删改的表或字段
3、where条件里用不到的字段不创建索引
4、数据重复且分布平均的表字段（过滤性不好）

二、性能优化

1、Explain介绍

使用Explain关键字可以模拟优化器执行SQL查询语句，从而知道MYSQL是如何处理SQL语句,分析你的查询语句或表结构的性能瓶颈
Explain+SQL语句执行计划包含的信息：

（1）id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
id不同，id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行


（2）select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询
1）simple：简单的select查询，查询中不包含子查询或者union
2）primary：查询中若包含任何复杂的子部分，最外层查询则被标记为primary
3）derived：在from列表中包含的子查询被标记为derived（衍生），mysql会递归的执行这些子查询，把结果放在临时表里
4）subquery：在select或where列表中包含了子查询
5）dependent subquery：在select或where列表中包含了子查询，子查询基于外层
6）union：若第二个select出现在union之后，则被标记为union
7）union result：从union表获取结果的select
（3）type：查询使用了何种类型
system>const>eq_ref>ref>range>index>all（一般来说至少达到range级别）
system：表只有一行记录
const：表示通过索引一次就可以找到
eq_ref：唯一性索引扫描，对于每个索引健，表中只有一条记录与之匹配
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
range：只检索给定范围的行
index：出现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者利用索引进行排序分组
all：全表扫描
（4）ref
显示索引的哪一列被引用了
（5）Extra：包含不适合在其他列中显示但十分重要的额外信息
1）using filesort ：说明mysql会对数据使用一个外部索引排序，而不是按照表内的索引排序顺序进行读取（文件排序）
2）using temporary：使用临时表保存中间结果
3）using join buffer：使用了连接缓存

三、单表建索引

1、左前缀法则
如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左前列开始并且不跳过索引中的列

  EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = 'abcd'   --索引：(age，name)
  EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.deptid=1 AND emp.name = 'abcd' --索引：(deptid，name)不能(age，deptid，name)

2、索引列不做任何操作（自动或手动转换）
导致索引失效转向全表扫描 eg：left(name,4)
3、存储引擎不能使用索引中范围条件右边的列

SELECT  SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = 'abc' ; 
应该创建索引create index idx_age_name_deptid on emp(age,name,deptid)而不是(age,deptid,name)

4、使用不等于的时候无法使用索引会导致全表扫描
5、is not null 无法建索引但是is null 是可以使用索引
6、like以通配符开头（%july）索引会失效，（july%）不会失效，%位于右边
解决like（%字符串%）时索引不被使用的方法----------覆盖索引
7、字符串不加单引号索引会失效
8、少用or，用它连接时索引会失效

四、查询优化

1、关联查询优化

1）被驱动表join 字段已经被索引
2）left join时选择小表作为驱动表，大表作为被驱动表
3）能够直接多表关联的尽量直接关联，不用子查询
4）子查询尽量不要放在被驱动表，有可能使用不到索引

2、子查询优化

1、尽量不要用not in 或者not exists 用 left outer join on xxx where xxx is null 代替

3、排序分组优化

1、当范围条件和group by 或者 order by 的字段出现二选一时 ，优先观察条件字段的过滤数量，如果过滤的数据足够多，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。

SELECT * FROM emp WHERE age =30 AND empno <1000 ORDER BY NAME
思路：尽量让where的过滤条件和排序使用上索引
但是一共两个字段(age,empno)上有过滤条件，一个字段(name)有索引
1）CREATE INDEX idx_age_empno_name ON emp(age,empno,NAME);
name并没有用到索引。原因是因为empno是一个范围过滤，所以索引后面的字段不会再使用索引了。
2）CREATE INDEX idx_age_name ON emp(age,NAME);

3）CREATE INDEX idx_emo_name ON emp(age,empno);


选择范围过滤，放弃排序上的索引，结果竟然有 filesort的 sql 运行速度，超过了已经优化掉 filesort的 sql ，而且快了好多倍。
原因是所有的排序都是在条件过滤之后才执行的，所以如果条件过滤了大部分数据的话，几百几千条数据进行排序其实并不是很消耗性能，即使索引优化了排序但实际提升性能很有限。 相对的 empno<101000 这个条件如果没有用到索引的话，要对几万条的数据进行扫描，这是非常消耗性能的，所以索引放在这个字段上性价比最高，是最优选择。

4、覆盖索引

覆盖索引：（select 到 from 之间查询的列 <=使用的索引列+主键）
select的数据列只用从索引中就能取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注：如果要用覆盖索引，select列表中只取需要的列，不可select*，如果将所有字段一起索引会导致索引文件过大，查询性能下降。