SQL优化（mysql进阶）

SQL优化在提升系统性能中是：（成本最低 && 优化效果最明显） 的途径。如果你的团队在SQL优化这方面搞得很优秀，对你们整个大型系统可用性方面无疑是一个质的跨越。

 

• 优化成本：硬件>系统配置>数据库表结构>SQL及索引。
• 优化效果：硬件<系统配置<数据库表结构<SQL及索引。
• MySQL层优化我一般遵从五个原则：

• 减少数据访问： 设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO
  返回更少的数据： 只返回需要的字段和数据分页处理 减少磁盘io及网络io
  减少交互次数： 批量DML操作，函数存储等减少数据连接次数
  减少服务器CPU开销： 尽量减少数据库排序操作以及全表查询，减少cpu 内存占用
  利用更多资源： 使用表分区，可以增加并行操作，更大限度利用cpu资源

  总结到SQL优化中，就三点:

• 最大化利用索引；
• 尽可能避免全表扫描；
• 减少无效数据的查询；

sql优化的方式 

一:sql性能分析

SQL优化首先要对sql的消耗时间,或者使用频率进行分析,然后在进行其他操作

二:   explain关键字  

 使用explain关键字来查看当前sql语句的执行情况,来对症下药

三:   正确的建立索引

sql优化的具体操作

一、避免不走索引的场景

1. 尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

2. 尽量避免使用in 和not in，会导致引擎走全表扫描

3. 尽量避免使用 or，会导致数据库引擎放弃索引进行全表扫描

4. 尽量避免进行null值的判断，会导致数据库引擎放弃索引进行全表扫描

5.尽量避免在where条件中等号的左侧进行表达式、函数操作，会导致数据库引擎放弃索引进行全表扫描

6. 当数据量大时，避免使用where 1=1的条件。通常为了方便拼装查询条件，我们会默认使用该条件，数据库引擎会放弃索引进行全表扫描

7. 查询条件不能用 <> 或者 !=

8. where条件仅包含复合索引非前置列

9. 隐式类型转换造成不使用索引 

10. order by 条件要与where中条件一致，否则order by不会利用索引进行排序

二、SELECT语句其他优化

1. 避免出现select *

2. 避免出现不确定结果的函数

3.多表关联查询时，小表在前，大表在后。

4. 使用表的别名

5. 用where字句替换HAVING字句

6.调整Where字句中的连接顺序

7:对于联合索引来说，要遵守最左前缀法则: 

8.需要插入多条数据的时候 使用批量插入.(多次插入需要频繁的建立连接.浪费资源) 

9.多次插入数据时,采用手动提交事务

10.order by 排序优化 (排序时,使用有索引的字段进行排序)

11.count 优化     速度:count(*)>count(1)>count(字段)

三、其他

update优化  (避免出现表锁)

innodb引擎使用update时,会有行锁/表锁两种模式, 如果where 字段没有索引的时候会升级成表锁,

update table set xx=1 where  name=xx   (name没有索引,此时是表锁)
update table set xx=1 where  id=xx  (id有索引,此时是行锁)

explain关键字分析:
    explain是非常重要的关键字,要善于运用它.  通过explain我们可以获得以下信息：

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
   使用方法：explain + sql语句。会出现下方截图.根据下方的字段来进行解析sql的问题所在

例如:  explain SELECT user_name FROM `user` WHERE id ="1060"