Mysql_优化

优化思路:

1.加索引
2.sql只查字段，内存里处理业务逻辑
3.能用内连接就不要用左右连接，以减小每次连接的笛卡尔积
4.多表连接时尽量在每次连接的on条件里过滤不需要的数据以减小每次连接的笛卡尔积，而不是在where后面一次性过滤

索引优化思路

1.索引种类

主键索引:(Primary key)  索引列的值必须唯一，不允许有空值
单值索引(index)：一个索引只包含单个列，一个表可以有多个单列索引
唯一索引(ubique):  索引列的值必须唯一，但允许有空值
复合索引(index):  一个索引包含多个列,复合索引比单值索引所需要的开销更小(对于相同的多个列建索引)

2.索引的优劣

索引的弊端：
    以下情况不适合索引:a.少量数据  b.频繁更新的字段  c.很少使用的字段
    索引会降低增删改的效率
索引的优势:
    提高查询效率(降低IO使用率)
    降低CPU使用率(索引本身有序)

3.explain + sql  属性分析

id
    编号
    id值相同，从上往下，顺序执行
    id值越大，越优先查询（本质：嵌套子查询时，先查内层，再查外层）

select_type
    查询类型：
    primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为    primary。且只有一个。
    subquery：非from中包含的子查询可能是(非最外层)
    simple：不包含union，子查询的简单select查询
    derived：from字句中出现的子查询。
    union：union连接的select查询，除了第一个表外，第二个及以后的表select_type都是union。

table
    表名

patitions
    分区表分区情况，非分区表为null

type
    查询用到的索引类型：
    (system>const>eq_ref>ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery>range>index>ALL)
    system：表中只有一行数据或者是空表
    const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件
    eq_ref：唯一性索引，对于每个索引键查询，返回唯一一条数据(有且只有一个)，常见于唯一索引，主键索引（index索引很难保证列值唯一）
    ref：非唯一性索引，对于每个索引键查询，返回匹配的所有数据(0，1，多)
    range：索引范围扫描，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。
    index：索引全表扫描，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
    all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

possible_keys
    预测用到的索引

keys
    实际使用的索引

key_length
    实际使用的索引长度
    用于判断符合索引是否被完全使用(a,b,c)，Mysql的utf8里，一个字符占3个字节，cher(20) - 长度为60字节，如果索引字段可以为Null,会使用1个额外字节标识，如果是varchar,会使用2个额外字节标识其为可变长度

ref
    表之间的引用，指明当前表参考的字段
    如果是常数等值查询，这里会显示const
    如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
    如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows
    通过索引查询到的数据量

filtered
    返回记录占总搜索行数百分比

Extra
    额外信息
    using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。
    using where：查询时需要回表查询

4.explain 例子

1
alter table hero add index a(power)
explain select power from hero where power = 2
extra： using index，using where    其中，using where 表示使用了where 条件过滤

2.
alter table hero add index a(power)
explain select power from hero 
extra： using index
using filesort：排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中，需要额外的排序查询，性能消耗大
1.单值索引 index(a)
select * from table where a = "" order by b . 
查询和排序的不是同一个字段 ，就会出现using filesort
2.复合索引，不能跨列使用(最佳左前缀)  index(a,b,c)
select * from table where a = "" order by c 
跨了b会出现 using filesort  
select * from table where b = "" order by c 
跨了a会出现 using filesort 
select * from table where a = "" order by b 
不会出现 using filesort 
总结:复合索引where 和 orderby 严格按照 复合索引顺序使用 ，不要跨列和无序使用
using temporary：查询用到了临时表，性能损耗打，常见于group by 语句中

1.
select avg(id) from hero group by heroName  
Extra:using temporary ，用到了根据heroName 分组的临时表
2.
alter table hero add index a(power);
explain select power from hero  GROUP BY power
Extra:Using index for group-by 
3.
alter table hero add index a(power);
explain select power,agile from hero where agile = '88' GROUP BY power
Extra:Using where
4.
alter table hero add index a(power);
explain select power,agile from hero where power = '123' GROUP BY agile
Extra:using temporary 

索引优化思路

符合索引不要跨列使用:

explain select a1,a2,a3,a4 from a where a1 = '' 
Extra:Using index
explain select a1,a2,a3,a4 from a where a1 = '' and a2 = '' and a3 = '' and a4 = '';(推荐写法)
Extra:Using index
explain select a1,a2,a3,a4 from a where a4 = '' and a3 = '' and a2 = '' and a1 = '';(mysql优化器优化)
Extra:Using index
explain select a1,a2,a3,a4 from a where a1 = ''  and a3 = '';
Extra:Using where; Using index    跨了a2，需要回表查询 a2,a3,a4
explain select a1,a2,a3,a4 from a where a1 = '' and a2 = ''  and a4 = '' order by a3
Extra:Using where; Using index   where a2 跨了 a3 所以 Useing where , where a1,a2 order by a3 ，没跨列，所以没有Using filesort
explain select a1,a2,a3,a4 from a where a1 = '' and a4 = '' order by a3
Using where; Using index; Using filesort  where a1 order by a3 ，跨列 所以 Using filesort
explain select a1,a2,a3,a4 from a where a1 = '' and a3 = '' order by a3
Using where; Using index  因为回表查了a3 所以 没有Using filesort
总结: 复合索引和索引使用顺序一致，则索引全部使用，否则部分使用