sql性能调优-整理

sql性能调优

参考博客 Sql性能优化看这一篇就够了

mysql处理流程

sql执行顺序

1.from -->2.join -->3.on -->4.where–>5.group by–>6.聚合（sum~）–>7.having -->8.select -->9.distinct–>10.order by

explain解析器

1. id sql执行顺序 越大越先被执行

2. Select_type: 一共有9中类型,只介绍常用的4种:

    SIMPLE: 简单的 select 查询,不使用 union 及子查询
    PRIMARY: 最外层的 select 查询
    UNION: UNION 中的第二个或随后的 select 查询,不 依赖于外部查询的结果集
    DERIVED: 用于 from 子句里有子查询的情况。 MySQL 会 递归执行这些子查询, 把结果放在临时表里。
   

3. Type: 从优到差的顺序如下:

system–>const–>eq_ref–>ref–>ref_or_null–>index_merge–>unique_subquery–>index_subquery–>range–>index–>a

    system:  表仅有一行。这是 const 连接类型的一个特例。

const:  const 用于用常数值比较 PRIMARY KEY 时。
eq_ref: 查询使用了索引为主键或唯一键的全部时使用。即：通过索引关键字可能查找到一个符合条件的行。
ref:  通过索引关键字可能查找到多个符合条件的行。
ref_or_null:  如同 ref, 但是 MySQL 必须在初次查找的结果里找出 null 条目,然后进行二次查找。
index_merge: 说明索引合并优化被使用了。
unique_subquery:  在某些 IN 查询中使用此种类型,而不是常规的 ref:valueIN (SELECT primary_key FROM single_table WHERE some_expr)
index_subquery:  在 某 些 IN 查 询 中 使 用 此 种 类 型 , 与unique_subquery 类似,但是查询的是非唯一 性索引
range:  检索给定范围的行。当使用 <>、>、>=、<、<=、BETWEEN 或者 IN 操作符时,会使用到range。
index:  全表扫描,只是扫描表的时候按照索引次序进行而不是行。主要优点就是避免了排序, 但是开销仍然非常大。
all: 最坏的情况,从头到尾全表扫描。

4. possible_keys 哪些索引可能有助于查询。如果为空,说明没有可用的索引

5. key 实际使用的索引

6. key_len: 使用的索引的长度。在不损失精确性的情况 下,长度越短越好

7. rows: 请求数据返回的大概行数

8. extra: 其他信息，出现Using filesort、Using temporary 意味着不能使用索引,效率会受到重大影响。应尽可能对此进行优化。

    Using filesort: 没有办法利用现有索引进行排序，需要额外排序，建议：根据排序需要，创建相应合适的索引
    
    Using temporary: 需要用临时表存储结果集，通常是因为group by的列列上没有索引。也有可能是因为同
    时有group by和order by，但group by和order by的列又不一样 
    
    Using index ： 利用覆盖索引，无需回表即可取得结果数据（即数据直接从索引文件中读取），这种结果是好的。
   

其中重要的几个就是 key、type 、rows、extra，其中key为null、all 、index时，需要调整、优化索引。一般需要达到 ref、eq_ref 级别，范围查找需要达到 range，extra有Using filesort、Using temporary 的一定需要优化，根据rows可以直观看出优化结果。

优化手段

1. sql优化

1. 避免使用select* 只查询需要的字段
2. 小表去驱动大表，
    当A表数据大于B表时  select cloum from A  where a.id in(select id from b ),in  先查询 in 再查询 A 
    当A表数据小于B表时 select a.id from A where exists (select id from b where b.id=a.id ) 先查询 A 再查询Exists
3. 尽量使用联表查询替换子查询，使用jion时 mysql不会在内存中创建临时表

2. 优化索引

 尽量使用主键查询，而非其他索引，因为主键不会触发回表
 不做列运算，把计算放入业务中
 查询语句尽量简单，大语句拆分小语句，减少锁时间
or查询改成union查询
    如果or的条件中有个条件不带索引的话，那这条sql就不会使用到索引了
    多表查询下or查询可以拆分为union查询，可以优化
    单表没用
不用函数和触发器 
避免使用%xx查询 可以使用select * from  t where reverse(f) like reverse('%abc')
组合索引最左前缀原则 
避免类型不一致的隐式函数调用不走索引
少用join，连表查询尽量少用，笛卡尔积很可怕
避免在where使用不等于<> !=
列表分页不要太大
避免在索引上使用is null 和is not null 

3.表结构优化

使用存下数据的最小类型
尽可能的not null 字符串默认" 数字0
减少text字段，必须的话建议单独表
尽量使用 timestamp，而非 datetime。
单表不要有太多字段，建议在 20 个字段以内

showprofiles分析sql性能

触发器

触发器为什么不用
触发器也是一种无惨的特殊存储过程

1. 触发器的隐形调用，不易于维护
2. 触发器移植性不好，迁库时一般都会重写触发器
3. 触发器/存储过程 使得业务逻辑与代码分离，一部分在数据库中，增加维护成本
4. 触发器使用不好，可能出现多个触发器死锁

reverse

• 主要功能是把一个字符产反转 (like查询时XX%走索引，%xx不走索引，可以先reverse(clounm) like 可以先reverse(’%xx’) 【待验证】

• 参考文章

select REVERSE('hello,world')

将得到如下的输出：dlrow,olleh

mysql数据库引擎

myISAM VS InnoDB

myISAM 不支持事务 查询效果更好
Innodb 支持高级事务 插入更新效率更高

数据库读写分离

1. 一般数据库都会采用双机热备，保证数据的高可用
2. 读写分离 一个服务器操作数据的增删改，一个数据库操作查询，但是这样会降低双机热备的稳定性，增加了不稳定性，没钱才干

双机热备

主从服务器实现数据库的稳定，主服务器故障，双机软件监控到后自动切换到备服务器上

实现方式

1. 共享方式，两个服务器连接一个共享的存储或存储网络，通过安装双机软件实现热备份
2. 纯软件方式或软件同步方式，即两台服务器的数据存储在各自的服务器中，不适用共享设备

基于存储共享的实现方式是双机热备最标准的方式

like 索引原理

• 参考like原理

• 参考聚簇索引和非聚簇索引

  • 聚簇索引 innoDB 索引包含数据 主键索引即是，b+树结构，子节点为主键， 叶节点为数据，
  • 非聚簇索引 innoDb （辅助索引，二级索引） 关键字索引到主键，主键再通过聚簇索引查询到数据