mysql sql优化

1,mysql拥有自身的sql优化器
mysql常见瓶颈：CPU，磁盘io,服务器硬件
2，explain
使用explain关键字模拟sql优化器执行sql语句，分析sql语句和表结构的性能瓶颈
使用：explain + sql语句
作用：表的读取顺序
id——select_type——table——type——possible_keys——key——key_len——ref——row——extra
id :select查询的序列号，id相同是，执行顺序从上往下。id不同时，id值大的先执行
select_type :查询类型
simple 简单查询，不包含子查询或者union
primary 查询包含子查询
subquery 子查询
derive 衍生子查询，在from后的子查询
union union后的子查询
union result 从union表获取结果的select
type :文件查询类型
从最好到最差
system->const->eq_ref->ref->range->index->all
一般来说，查询优化到range
system 一张表只有一条记录
const 表示通过常量索引，一次就查找到，常用与比较主键或者唯一索引
eq_ref 唯一索引扫描，常用与比较主键或者唯一索引
ref 非唯一索引扫描，返回与条件值对应的多行记录
index 全表通过索引文件扫描
all 标明通过磁盘文件全表扫描
possible_keys: 查询涉及的字段如果有索引，将被列出，但是不一定被查询实际使用
key :实际使用的索引，没有使用或者索引失效，则为null。查询中如果使用了复合索引，则该索引只出现在key列。
key_len:表示索引使用的字节数，该列显示的是索引字段的最大可能长度，并非实际使用的长度。key_len是根据表字段的定义得来的。
ref：显示索引的那一列被使用了或常量
rows: 根据表的统计信息和索引的选用情况，估计 找出查询的结果 需要读取的行数
extra: 不适合在其他列显示的额外信息
分类：using filesort mysql无法使用索引完成的排序操作称作文件排序
using temporary 使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by 和分组 group by
using index 表明相应的select操作中使用了覆盖索引，没有在表中查找数据行。
using where 表明索引被用来执行索引键值的查找，如果没有同时出现using where 则表明索引用来读取数据而非执行查找动作。
覆盖索引：查询的列被所建的索引覆盖
!!!ps
索引优化
1）mysql range查询类型后面的索引无效
2）多表连接：左连接特性，left join条件决定如何从右表搜索行，左表一定都有，所以右表是关键点，一定需要建立索引。
3）索引最好建在经常查询的字段中

join优化
1）尽可能减少join语句嵌套循环的循环总次数，永远用小表驱动大表。

2. 优先优化嵌套循环的内层循环
   3)保证join语句中被驱动的表上join on字段已经建立索引
   4)当无法保证被驱动的表上join字段建立索引，且内存资源充足的情况下，可以增加joinbuffer的设置
   索引失效
   1.1 不在索引列上做任何操作（包括计算，函数，自动or手动类型转换），会导致索引失效
   1.2 存储引擎不能使用范围条件（<,>,in,between and ）。
   1.3 尽量使用覆盖索引，减少select *
   1.4 mysql在使用不等于（!=,<>）的时候，无法使用索引会导致全表扫描
   1.5 is null ,is not null 无法使用索引
   1.6 like以通配符开头（%abc）会导致索引失效，%，like加右边
   1.7 字符串不加单引号索引失效
   1.8 少用or，用它连接时会索引失效
   1.9 最佳左匹配法则
   1.10 group by分组必排序，会用到临时表。
   抄袭口诀
   全值匹配我最爱，最左前缀要遵守
   带头大哥不能死，中间兄弟不能断
   索引列上少计算，范围之后全失效
   like %写最右，覆盖索引不写*
   不等空值还有or，索引失效要少用
   字符引号不能丢，SQL高级也不难

3，查询截取优化