## 常见优化策略
1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num is null
可以在num上设置默认值0,确保表中num列没有null值,然后这样查询:
select id from t where num=0
4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:
select id from t where num=10 or num=20
可以这样查询:
select id from t where num=10
union all
select id from t where num=20
5、下面的查询也将导致全表扫描:(不能前置百分号)
select id from t where name like ‘%c%’
下面走索引
select id from t where name like ‘c%’
若要提高效率,可以考虑全文检索。
6、in 和 not in 也要慎用,否则会导致全表扫描,如:
select id from t where num in(1,2,3)
对于连续的数值,能用 between 就不要用 in 了:
select id from t where num between 1 and 3
7、如果在 where 子句中使用参数,也会导致全表扫描。因为SQL只有在运行时才会解析局部变量,但优化程序不能将访问计划的选择推迟到运行时;它必须在编译时进行选择。然 而,如果在编译时建立访问计划,变量的值还是未知的,因而无法作为索引选择的输入项。如下面语句将进行全表扫描:
select id from t where num=@num
可以改为强制查询使用索引:
select id from t with(index(索引名)) where num=@num
8、应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where num/2=100
应改为:
select id from t where num=100\*2
9、应尽量避免在where子句中对字段进行函数操作,这将导致引擎放弃使用索引而进行全表扫描。如:
select id from t where substring(name,1,3)=’abc’ –name以abc开头的id
select id from t where datediff(day,createdate,’2005-11-30′)=0 –’2005-11-30′生成的id
应改为:
select id from t where name like ‘abc%’
select id from t where createdate>=’2005-11-30′ and createdate<’2005-12-1′
10、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。
11、在使用索引字段作为条件时,如果该索引是复合索引,那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引,否则该索引将不会被使 用,并且应尽可能的让字段顺序与索引顺序相一致。
12、不要写一些没有意义的查询,如需要生成一个空表结构:
select col1,col2 into #t from t where 1=0
这类代码不会返回任何结果集,但是会消耗系统资源的,应改成这样:
create table #t(…)
13、很多时候用 exists 代替 in 是一个好的选择:
select num from a where num in(select num from b)
用下面的语句替换:
select num from a where exists(select 1 from b where num=a.num)
14、并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。
15、索引并不是越多越好,索引固然可以提高相应的 select 的效率,但同时也降低了 insert 及 update 的效率,因为 insert 或 update 时有可能会重建索引,所以怎样建索引需要慎重考虑,视具体情况而定。一个表的索引数较好不要超过6个,若太多则应考虑一些不常使用到的列上建的索引是否有 必要。
16.应尽可能的避免更新 clustered 索引数据列,因为 clustered 索引数据列的顺序就是表记录的物理存储顺序,一旦该列值改变将导致整个表记录的顺序的调整,会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列,那么需要考虑是否应将该索引建为 clustered 索引。
17、尽量使用数字型字段,若只含数值信息的字段尽量不要设计为字符型,这会降低查询和连接的性能,并会增加存储开销。这是因为引擎在处理查询和连接时会 逐个比较字符串中每一个字符,而对于数字型而言只需要比较一次就够了。
18、尽可能的使用 varchar/nvarchar 代替 char/nchar ,因为首先变长字段存储空间小,可以节省存储空间,其次对于查询来说,在一个相对较小的字段内搜索效率显然要高些。
19、任何地方都不要使用 select \* from t ,用具体的字段列表代替“\*”,不要返回用不到的任何字段。
20、尽量使用表变量来代替临时表。如果表变量包含大量数据,请注意索引非常有限(只有主键索引)。
21、避免频繁创建和删除临时表,以减少系统表资源的消耗。
22、临时表并不是不可使用,适当地使用它们可以使某些例程更有效,例如,当需要重复引用大型表或常用表中的某个数据集时。但是,对于一次性事件,较好使 用导出表。
23、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log ,以提高速度;如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
24、如果使用到了临时表,在存储过程的最后务必将所有的临时表显式删除,先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。
25、尽量避免使用游标,因为游标的效率较差,如果游标操作的数据超过1万行,那么就应该考虑改写。
26、使用基于游标的方法或临时表方法之前,应先寻找基于集的解决方案来解决问题,基于集的方法通常更有效。
27、与临时表一样,游标并不是不可使用。对小型数据集使用 FAST\_FORWARD 游标通常要优于其他逐行处理方法,尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时 间允许,基于游标的方法和基于集的方法都可以尝试一下,看哪一种方法的效果更好。
28、在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ,在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONEINPROC 消息。
29、尽量避免向客户端返回大数据量,若数据量过大,应该考虑相应需求是否合理。
30、尽量避免大事务操作,提高系统并发能力。
## 更多可以学习
[https://www.cnblogs.com/sochishun/p/7003513.html](https://www.cnblogs.com/sochishun/p/7003513.html)
- 前言
- 读者须知
- 第一章 Linux
- HTTP
- 简介
- 状态码
- 特点
- URL
- Request
- Response
- 请求方式
- 工作原理
- 生命周期
- GET和POST区别
- 组成
- 端口
- 命令
- 常用命令
- chmod命令详解
- ubuntu apt-get命令
- 用户和用户组
- Nginx
- 四个基本功能
- 进程
- 进程管理[ps命令]
- 进程管理[top命令]
- 进程管理[kill命令]
- 进程管理[进程优先级]
- 进程管理[netstat命令]
- 定时任务
- crontab
- 实现每秒执行
- >/dev/null 2>&1说明
- 文件管理
- 工作管理
- 资源管理
- 第二章 NGINX
- 介绍
- 入门
- 特性
- 安装启动
- 基础必会
- 常用功能
- 反向代理
- 负载均衡
- 正向代理
- HTTP服务器
- 动静分离
- 技能点汇总
- 显示乱码
- 打开目录浏览功能
- 错误码原因和解决方案
- location用法
- 常用正则
- rewrite
- 全局变量
- if语句块
- https
- php后端处理(fast-cgi)
- flag标志位
- 过期功能
- gzip压缩
- 会话保持时间
- 配置nginx worker进程最大打开文件数
- sendfile
- 单个工作进程的最大连接数
- 选择事件驱动模型
- 隐藏ngxin版本号
- 网络连接的优化
- 缓存原理及机制
- 限流
- 日志配置
- 灰度发布
- 配置一键生成
- 第三章 MySQL
- 入门
- 简介
- 术语
- 特点
- 三范式
- 8.0 新特性
- 数据类型
- 数据类型详解
- 常用函数
- 命令速查
- MyISAM与InnoDB区别
- 服务器构成
- 事务
- 本质
- 特性
- 分类
- 隔离级别
- PHP中使用事务实例
- MVCC
- 问题和解决
- 调优原则
- 分布式事务
- 索引
- 简介
- 索引的分类
- 创建索引
- 删除索引
- 哈希索引
- btree索引和hash索引的区别
- 单列索引和多列索引
- 索引优化
- 查看SQL语句对索引的使用情况
- 锁
- 技能点
- 开发规范
- 导入导出数据库
- blob和text的区别
- char与varchar类型区别
- SQL查询语句优化
- 事务隔离和锁操作需要在语言级别来做吗
- 58到家数据库30条军规解读
- 数据迁移
- SKU数据库设计
- RBAC数据库设计
- 第四章 Redis
- 入门
- 简介
- 应用场景
- 安装启动
- 生命周期
- 事务
- 配置项
- 缓存
- 数据持久化
- 安全
- 数据类型
- string
- hash
- list
- set
- zset
- php代码实战
- 字符串缓存实战
- 队列实战
- 发布订阅实战
- 计数器实战
- 排行榜实战
- 字符串悲观锁实战
- 事务的乐观锁实战
- 高级应用
- 分片机制
- 主从复制
- 缓存问题
- 解决 Redis 并发竞争 Key 问题
- 淘汰策略
- 第五章 PHP
- composer
- 什么是composer
- composer常用概念解析
- 使用composer的正确姿势
- 消息队列
- 为何使用消息队列
- Beanstalkd
- PSR规范
- PSR-0
- PSR-1
- PSR-2
- PSR-3
- PSR-4
- OOP基础
- 面向对象概念
- 类和对象
- 类
- 操作对象成员
- this使用
- 构造方法和析构方法
- 封装
- __set(),__get(),__isset(),__unset()四个方法的应用
- 继承
- 重载新的方法(parent::)
- 访问类型(public,protected,private)
- final关键字的应用
- static和const关键字的使用(self::)
- static关键字
- __toString()方法
- 克隆对象__clone()方法
- __call()处理调用错误
- 抽象方法和抽象类(abstract)
- 接口(interface)
- 多态
- 把对象串行化serialize()方法,__sleep()方法,__wakeup()方法
- 自动加载类 __autoload()函数
- OOP进阶
- 语法糖
- 异常处理
- 后期静态绑定
- 后期静态绑定在框架的运用
- 代码优化思路
- Closure(闭包)
- 巧用PHP内置方法
- 数组操作的奇技淫巧
- 设计模式
- 单例模式(Singleton Pattern)
- 工厂模式(Factor Pattern)
- 建造者模式(Builder Pattern)
- 原型模式(Prototype Pattern)
- 适配器模式(Adapter Pattern)
- 装饰器模式(Decorator Pattern)
- 代理模式(Proxy Pattern)
- 外观模式(Facade Pattern)
- 桥接模式(Bridge Pattern)
- 组合模式(Composite Pattern)
- 享元模式 (Flyweight Pattern)
- 策略模式 ( Strategy Pattern )
- 模板模式 (Template Pattern)
- 观察者模式 (observer Pattern)
- 迭代模式(Iterator Pattern)
- 责任链模式(Chain of Responsibility Pattern)
- 命令模式 (Command Pattern)
- 备忘录模式(Memento Pattern)
- 状态模式 (State Pattern)
- 访问者模式(Visitor Pattern)
- 中介者模式(Mediator Pattern)
- 解释器模式(Interpreter Pattern)
- 数据映射模式(Data Mapper Pattern)
- 注册树模式(Registry Pattern)
- 空对象模式(Null Object Pattern)
- 搜索引擎
- Elasticsearch
- 安装
- 入门
- 实践
- 集群
- 查询
- API
- 接口调用
- cURL
- Guzzle
- RPC
- yar
- session
- 概念
- 客户端实现形式
- cookie与session的区别
- Cookies的安全性
- JWT
- 组成
- 入门
- 应用
- 知识点
- 常见
- $_SERVER
- php的引用
- 第六章 技术栈扩展
- 使用第三方静态资源服务
- 七牛对象存储实战
- 七牛对象存储之客户端上传
- aliyunOSS服务端文件上传
- aliyunOSS客户端文件上传
- 第三方支付
- 微信支付
- 支付宝支付
- SEO排名影响因素
- PHP架构师之路
- CTO职能
- web宏观分析
- 常见的企业软件系统
- 负载的优化思路
- 从容应对负载并发的前期准备
- 第七章 网络安全
- XSS
- CSRF
- DDoS
- SQL注入
- 停用js
- 文件上传
- 点击劫持
- APT
- 会话劫持
- 第八章 运维
- devops
- devops简介
- 常用工具
- 搭建运行环境
- Centos7 lnmp环境搭建
- ubuntu lnmp环境搭建
- Apache多站点配置
- docker
- 轻松使用和理解docker
- lnamp产品级环境搭建
- lnamp产品级环境搭建【第二版】
- 基于 Docker 容器的沙盒化评测系统
- vagrant
- vagrant入门
- vagrant之Vagrantfile
- vagrant之集成jenkins
- homestead
- gitlab
- gitlab简介
- webhook
- ssh堡垒机
- 第九章 测试
- 压力测试
- 单元测试
- 第十章 团队协作
- 软件开发模式
- 边做边改模型
- 瀑布模型
- 迭代模型
- 快速原型模型
- 增量模型
- 螺旋模型
- 敏捷软件开发
- 演化模型
- 喷泉模型
- 智能模型
- 混合模型
- 模型对比
- TDD
- git
- git_入门
- git_使用
- git_进阶
- git workflow
- git_高级
- git_小技巧
- okr工作法
- API接口文档管理系统
- 敏捷协作工具
- 第十一章 技术灯塔
- github项目
- 社区好货
- 纸质书
- 第十二章 代码之外
- 面试官的角度看面试
- 程序员的壮年思考