1. 纵向、横向分割表，减少表的尺寸(sp_spaceuse);
2. 根据查询条件,建立索引,优化索引、优化SQL Server数据库访问方式，限制结果集的数据量。注意填充因子要适当(最好是使用默认值0)。索引应该尽量小，使用字节数小的列建索引好(参照索引的创建),不要对有限的几个值的字段建单一索引如性别字段;
3. 如果是使用like进行查询的话，简单的使用index是不行的，但是全文索引，耗空间。 like ‘a%’ 使用索引 like ‘%a’ 不使用索引用 like ‘%a%’ 查询时，查询耗时和字段值总长度成正比,所以不能用CHAR类型，而是VARCHAR。对于字段的值很长的建全文索引;
4. Commit和rollback的区别 Rollback:回滚所有的事物。 Commit:提交当前的事物. 没有必要在动态SQL里写事物，如果要写请写在外面如： begin tran exec(@s) commit trans 或者将动态SQL 写成函数或者存储过程;
5. 在查询Select语句中用Where字句限制返回的行数,避免表扫描,如果返回不必要的数据，浪费了服务器的I/O资源，加重了网络的负担降低性能。如果表很大，在表扫描的期间将表锁住，禁止其他的联接访问表,后果严重;
Sql执行顺序:
(8) SELECT(9) DISTINCT column,…
选择字段 、去重
(6) AGG_FUNC(column or expression),…
聚合函数
(1) FROM [left_table]
选择表
(3) <join_type> JOIN <right_table>
链接
(2) ON <join_condition>
链接条件
(4) WHERE <where_condition>
条件过滤
(5) GROUP BY <group_by_list>
分组
(7) HAVING <having_condition>
分组过滤
(10) ORDER BY <order_by_list>
排序
(11) LIMIT count OFFSET count;
分页
语句优化:
1. 查询SQL尽量不要使用select *，而是具体字段
- 字段多时，大表能达到100多个字段甚至达200多个字段
- 只取需要的字段，节省资源、减少网络开销
- select * 进行查询时，很可能不会用到索引，就会造成全表扫描
-
--反例 SELECT * FROM student --正例 SELECT id,NAME FROM student
2. 避免在where子句中使用or来连接条件
- 使用or可能会使索引失效，从而全表扫描
-
对于or没有索引的salary这种情况，假设它走了id的索引，但是走到salary查询条件时，它还得全表扫描。也就是说整个过程需要三步：全表扫描+索引扫描+合并。如果它一开始就走全表扫描，直接一遍扫描就搞定。虽然mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引还是可能失效的
-
--反例 SELECT * FROM student WHERE id=1 OR salary=30000 --正例1--使用union all SELECT * FROM student WHERE id=1 UNION ALL SELECT * FROM student WHERE salary=30000 --正例2--分开两条sql写 SELECT * FROM student WHERE id=1 SELECT * FROM student WHERE salary=30000
3. 使用varchar代替char
- varchar变长字段按数据内容实际长度存储，存储空间小，可以节省存储空间
- char按声明大小存储，不足补空格
- 其次对于查询来说，在一个相对较小的字段内搜索，效率更高
-
--反例 `deptname` char(100) DEFAULT NULL COMMENT '部门名称' --正例 `deptname` varchar(100) DEFAULT NULL COMMENT '部门名称'
4. 尽量使用数值替代字符串类型
- 主键（id）：primary key优先使用数值类型int，tinyint
- 性别（sex）：0-代表女，1-代表男；数据库没有布尔类型，mysql推荐使用tinyint
- 支付方式（payment）：1-现金、2-微信、3-支付宝、4-信用卡、5-银行卡
- 服务状态（state）：1-开启、2-暂停、3-停止
- 商品状态（state）：1-上架、2-下架、3-删除
5. 查询尽量避免返回大量数据
- 如果查询返回数据量很大，就会造成查询时间过长，网络传输时间过长。同时，大量数据返回也可能没有实际意义。如返回上千条甚至更多，用户也看不过来。
- 通常采用分页，一页习惯10/20/50/100条。
6. 使用explain分析你SQL执行计划
- SQL很灵活，一个需求可以很多实现，那哪个最优呢？SQL提供了explain关键字，它可以分析你的SQL执行计划，看它是否最佳。Explain主要看SQL是否使用了索引。
- https://blog.csdn.net/wang5701071/article/details/117782061
-
EXPLAIN SELECT * FROM student WHERE id=1
7. 是否使用了索引及其扫描类型
- type：
-
ALL 全表扫描，没有优化，最慢的方式
index 索引全扫描
range 索引范围扫描，常用语<，<=，>=，between等操作
ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
eq_ref 类似ref，区别在于使用的是唯一索引，使用主键的关联查询
const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理
null MySQL不访问任何表或索引，直接返回结果
System 表只有一条记录(实际中基本不存在这个情况)
-
- 性能排行：
- System > const > eq_ref > ref > range > index > ALL
- possible_keys：
- 显示可能应用在这张表中的索引
- key：
- 真正使用的索引方式
8. 创建索引
-
--添加PRIMARY KEY（主键索引） ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) --添加UNIQUE(唯一索引) ALTER TABLE `table_name` ADD UNIQUE ( `column` ) --添加INDEX(普通索引) ALTER TABLE `table_name` ADD INDEX index_name ( `column` ) --添加FULLTEXT(全文索引) ALTER TABLE `table_name` ADD FULLTEXT ( `column`) --添加多列索引 ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
9. 优化like语句
- like很可能让你的索引失效
-
--反例1 EXPLAIN SELECT id,NAME FROM student WHERE NAME LIKE '%1' --反例2 EXPLAIN SELECT id,NAME FROM student WHERE NAME LIKE '%1%' --正例 EXPLAIN SELECT id,NAME FROM student WHERE NAME LIKE '1%'
10. 字符串怪现象
- 为什么第一条语句未加单引号就不走索引了呢？这是因为不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换，把它们转换为数值类型再做比较
-
--反例--未使用索引 EXPLAIN SELECT * FROM student WHERE NAME=123 --正例--使用索引 EXPLAIN SELECT * FROM student WHERE NAME='123'
11. 索引不宜太多，一般5个以内
- 索引并不是越多越好，虽其提高了查询的效率，但却会降低插入和更新的效率
- 索引可以理解为一个就是一张表，其可以存储数据，其数据就要占空间
- 再者，索引表的一个特点，其数据是排序的，那排序要不要花时间呢？肯定要
- insert或update时有可能会重建索引，如果数据量巨大，重建将进行记录的重新排序，所以建索引需要慎重考虑，视具体情况来定
- 一个表的索引数最好不要超过5个，若太多需要考虑一些索引是否有存在的必要
12. 索引不适合建在有大量重复数据的字段上
- 如性别字段。因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。
13. where限定查询的数据
- 需要什么数据，就去查什么数据，避免返回不必要的数据，节省开销
-
--数据中假定就一个男的记录 --反例 SELECT id,NAME FROM student WHERE sex='男' --正例 SELECT id,NAME FROM student WHERE id=1 AND sex='男'
14. 避免在索引列上使用内置函数
-
使用索引列上内置函数，索引失效
-
--反例 EXPLAIN SELECT * FROM student WHERE DATE_ADD(birthday,INTERVAL 7 DAY) >=NOW(); --正例 EXPLAIN SELECT * FROM student WHERE birthday >= DATE_ADD(NOW(),INTERVAL 7 DAY);
15. 避免在where中对字段进行表达式操作
- SQL解析时，如果字段相关的是表达式就进行全表扫描
-
--反例 EXPLAIN SELECT * FROM student WHERE id+1-1=+1 --正例 EXPLAIN SELECT * FROM student WHERE id=+1-1+1
16. 避免在where子句中使用!=或<>操作符
- 使用!=和<>很可能会让索引失效
- 应尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
- 实现业务优先，实在没办法，就只能使用，并不是不能使用
-
--反例1 EXPLAIN SELECT * FROM student WHERE salary!=3000 --反例2 EXPLAIN SELECT * FROM student WHERE salary<>3000
17. 去重distinct过滤字段要少
- 带distinct的语句占用cpu时间高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，如cpu时间
-
--反例--索引失效 EXPLAIN SELECT DISTINCT * FROM student --正例--索引生效 EXPLAIN SELECT DISTINCT id,NAME FROM student
18. where中使用默认值代替null
- 并不是说使用了is null 或者 is not null 就会不走索引了，这个跟mysql版本以及查询成本都有关
- 如果mysql优化器发现，走索引比不走索引成本还要高，就会放弃索引，这些条件 !=，<>，is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃索引的
- 如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思也相对清晰一点
-
--反例 EXPLAIN SELECT * FROM student WHERE age IS NOT NULL --正例 EXPLAIN SELECT * FROM student WHERE age>0
19. 注意范围查询语句
- 对于联合索引来说，如果存在范围查询，比如between、>、<等条件时，会造成后面的索引字段失效
- 解决办法: 业务允许的情况下,使用 >= 或者<= 这样不影响索引的使用
20. count 优化 速度:count(*)>count(1)>count(字段)
21. 指定查询的索引
--use index(索引): 推荐使用指定的索引 (最终用不用该索引,还需要mysql自己判断) select * from use index(索引A) --ignore index(索引) : 忽略掉这个索引 select * from ignore index(索引A) --force index(索引): 强制使用该索引 select * from force index(索引A)
22. update优化 (避免出现表锁)
--innodb引擎使用update时,会有行锁/表锁两种模式, 如果where 字段没有索引的时候会升级成表锁 update table set xx=1 where name=xx (name没有索引,此时是表锁) update table set xx=1 where id=xx (id有索引,此时是行锁)
批量插入性能提升
- 默认新增SQL有事务控制，导致每条都需要事务开启和事务提交；而批量处理是一次事务开启和提交。自然速度飞升
- 大量数据提交，上千，上万，批量性能非常快，mysql独有
--反例 INSERT INTO student (id,NAME) VALUES(4,'name1'); INSERT INTO student (id,NAME) VALUES(5,'name2'); --正例 INSERT INTO student (id,NAME) VALUES(4,'name1'),(5,'name2');
批量删除优化
- 一次性删除太多数据，可能造成锁表，会有lock wait timeout exceed的错误，所以建议分批操作
--反例 --一次删除10万或者100万+？ delete from student where id <100000; --采用单一循环操作，效率低，时间漫长 for（User user:list）{ delete from student; } --正例 #分批进行删除，如每次500 for(){ delete student where id<500; }
伪删除设计
- 这里的删除只是一个标识，并没有从数据库表中真正删除，可以作为历史记录备查
- 通过where state=1或者where state=2过滤掉数据，这样伪删除的数据用户就看不到了，从而不影响用户的使用
提高group by语句的效率
--反例--先分组后过滤 select job，avg（salary） from employee group by job having job ='president' or job = 'managent'; --正例--先过滤后分组 select job，avg（salary） from employee where job ='president' or job = 'managent' group by job;
复合索引最左特性
- 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则
- 例如组合索引(id,name,sex) 使用的时候,可以id 或者id,name . 禁止直接name,或者sex.会导致联合索引失败
--创建复合索引，也就是多个字段 ALTER TABLE student ADD INDEX idx_name_salary (NAME,salary) --满足复合索引的左侧顺序，哪怕只是部分，复合索引生效 EXPLAIN SELECT * FROM student WHERE NAME='name1' --没有出现左边的字段，则不满足最左特性，索引失效 EXPLAIN SELECT * FROM student WHERE salary=3000 --复合索引全使用，按左侧顺序出现 name,salary，索引生效 EXPLAIN SELECT * FROM student WHERE NAME='陈子枢' AND salary=3000 --虽然违背了最左特性，但MYSQL执行SQL时会进行优化，底层进行颠倒优化 EXPLAIN SELECT * FROM student WHERE salary=3000 AND NAME='name1'
排序字段创建索引
- 原则就是where和order by中常出现的字段就创建索引
--使用*，包含了未索引的字段，导致索引失效 EXPLAIN SELECT * FROM student ORDER BY NAME;
删除冗余和重复的索引
SHOW INDEX FROM student --创建索引index_name ALTER TABLE student ADD INDEX index_name (NAME) --删除student表的index_name索引 DROP INDEX index_name ON student ; --修改表结果，删除student表的index_name索引 ALTER TABLE student DROP INDEX index_name ; --主键会自动创建索引，删除主键索引 ALTER TABLE student DROP PRIMARY KEY ;
不要有超过5个以上的表连接
inner join 、left join、right join，优先使用inner join
in子查询的优化
- 使用SQL就远不如程序实现，特别当数据量巨大时
尽量使用union all替代union
- union和union all的区别是，union会自动去掉多个结果集合中的重复结果，而union all则将所有的结果全部显示出来，不管是不是重复
- union：对两个结果集进行并集操作，不包括重复行，同时进行默认规则的排序
- union在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION
