SQL优化的核心在于降低查询的执行时间和资源消耗,系统的稳定。
SQL优化:
1、SQL语句的优化
2、SQL索引的优化(关键)
3、SQL数据表优化
4、SQL数据库优化
SQL语句的优化
1、不要用*,可能用到覆盖索引,减少回表,提高查询效率。
SELECT * FROM user;
SELECT id,username FROM user;
2、不要在where子句中使用or来连接条件,会使索引失效,从而全表扫描;要么使用UNION ALL或者分开写SQL。
SELECT * FROM user WHERE id=1 OR salary=5000;
SELECT * FROM user WHERE id=1
UNION ALL
SELECT*FROMuserWHEREsalary=5000;
3、尽量使用数值替代字符串类型
主键(id):primary key优先使用数值类型int,tinyint
性别(sex):0代表女,1代表男;数据库没有布尔类型,mysql推荐使用tinyint
因为引擎在处理查询和连接时会逐个比较字符串中每一个字符;
而对于数字型而言只需要比较一次就够了;
字符会降低查询和连接的性能,并会增加存储开销。
4、使用varchar代替char
addr char(100) DEFAULT NULL COMMENT'地址';
addr varchar(100) DEFAULT NULL COMMENT '地址';
varchar字段按数据内容实际长度存储,存储空间小,可以节省存储空间;
char按声明大小存储,不足补空格;
对于查询来说,在一个相对较小的字段内搜索,效率更高;
5、where中使用默认值代替null
SELECT * FROM user WHERE age IS NOT NULL;
SELECT * FROM user WHERE age>0;
SELECT * FROM user WHERE salary!=5000;
SELECT * FROM user WHERE salary<>5000;
并不是说使用了is null或者 is not null就会不走索引了,这个跟mysql版本以及查询成本都有关;
如果mysql优化器发现,走索引比不走索引成本还要高,就会放弃索引,这些条件 !=,<>,is null,is not null经常被认为让索引失效;
其实是因为一般情况下,查询的成本高,优化器自动放弃索引的;
如果把null值,换成默认值,很多时候让走索引成为可能,同时,表达意思也相对清晰一点;
6、inner join 、left join、right join,优先使用inner join
inner join 内连接,只保留两张表中完全匹配的结果集;返回的行数比较少,所以性能相对会好一点;
left join会返回左表所有的行,即使在右表中没有匹配的记录;
所以左边表数据结果尽量小,条件尽量放到左边处理,意味着返回的行数可能比较少;
这是mysql优化原则,就是小表驱动大表,小的数据集驱动大的数据集,从而让性能更优;
right join会返回右表所有的行,即使在左表中没有匹配的记录;优化同了left join
7、提高group by语句的效率
先分组,再过滤(不推荐)
select job,avg(salary)from employee group by job having job='develop' or job='test';
先过滤,后分组
select job,avg(salary)from employee where job='develop' or job='test' group by job;
可以在执行到该语句前,把不需要的记录过滤掉
8、清空表时优先使用truncate
truncate table在功能上与不带 where子句的 delete语句相同:二者均删除表中的全部行。但 truncate table比 delete速度快,且使用的系统和事务日志资源少。
delete语句每次删除一行,并在事务日志中为所删除的每行记录一项。truncate table通过释放存储表数据所用的数据页来删除数据,并且只在事务日志中记录页的释放。
truncate table删除表中的所有行,但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值,请改用 DELETE。如果要删除表定义及其数据,请使用 drop table语句。
对于由 foreign key约束引用的表,不能使用 truncate table,而应使用不带 where子句的 DELETE 语句。由于 truncate table不记录在日志中,所以它不能激活触发器。
truncate table不能用于参与了索引视图的表。
9、批量插入性能提升
INSERT INTO user(id,username)VALUES(1,'张三');
INSERT INTO user(id,username)VALUES(2,'李四');
INSERT INTO user(id,username)VALUES(1,'张三'),(2,'李四');
默认新增SQL有事务控制,导致每条都需要事务开启和事务提交,而批量处理是一次事务开启和提交,效率提升明显,达到一定量级,效果显著,平时看不出来。
10、索引不宜太多,一般5个以内
索引并不是越多越好,虽其提高了查询的效率,但却会降低插入和更新的效率;
索引可以理解为一个就是一张表,其可以存储数据,其数据就要占空间;
索引表的数据是排序的,排序也是要花时间的;
insert或update时有可能会重建索引,如果数据量巨大,重建将进行记录的重新排序,所以建索引需要慎重考虑,视具体情况来定;
一个表的索引数最好不要超过5个,若太多需要考虑一些索引是否有存在的必要;
11、不要在索引列上使用内置函数
SELECT * FROM user WHERE DATE_ADD(birthday,INTERVAL7DAY)>=NOW;
SELECT * FROM user WHERE birthday>=DATE_ADD(NOW,INTERVAL7DAY);
不要在子句中的“=”左边进行函数、算术运算或其他表达式运算,否则索引失效。
12、复合索引最左特性
创建复合索引
ALTERTABLEemployeeADDINDEXidx_name_salary(name,salary)
满足复合索引的最左特性,哪怕只是部分,复合索引生效
SELECT*FROMemployeeWHERENAME='编程'
没有出现左边的字段,则不满足最左特性,索引失效
SELECT*FROMemployeeWHEREsalary=5000
复合索引全使用,按左侧顺序出现 name,salary,索引生效
SELECT*FROMemployeeWHERENAME='编程'ANDsalary=5000
虽然违背了最左特性,但MySQL执行SQL时会进行优化,底层进行颠倒优化
SELECT*FROMemployeeWHEREsalary=5000ANDNAME='编程'
复合索引也称为联合索引,当我们创建一个联合索引的时候,如(k1,k2,k3),相当于创建了(k1)、(k1,k2)和(k1,k2,k3)三个索引,这就是最左匹配原则。联合索引不满足最左原则,索引一般会失效。
13、优化like语句
模糊查询,程序员最喜欢的就是使用like,但是like很可能让你的索引失效。
select * from citys where name like'%大连'(不使用索引)
select * from citys where name like'%大连%'(不使用索引)
select * from citys where name like '大连%'(使用索引)。
首先尽量避免模糊查询,如果必须使用,不采用全模糊查询,也应尽量采用右模糊查询, 即like ‘…%’,是会使用索引的;
左模糊like ‘%...’无法直接使用索引,但可以利用reverse + function index的形式,变化成 like ‘…%’;
全模糊查询是无法优化的,一定要使用的话建议使用搜索引擎。
14、count(*)推荐使用
count(*) :它会获取所有行的数据,不做任何处理,行数加1。
count(1):它会获取所有行的数据,每行固定值1,也是行数加1。
count(id):id代表主键,它需要从所有行的数据中解析出id字段,其中id肯定都不为NULL,行数加1。
count(普通索引列):它需要从所有行的数据中解析出普通索引列,然后判断是否为NULL,如果不是NULL,则行数+1。
count(未加索引列):它会全表扫描获取所有数据,解析中未加索引列,然后判断是否为NULL,如果不是NULL,则行数+1。
由此,最后count的性能从高到低是:
count(*) ≈ count(1) > count(id) > count(普通索引列) > count(未加索引列)
可以为对应表key_len较小的列建立二级索引,以优化count(*)执行效率
**15、 慎用 IN 和 NOT IN**
IN 和 NOT IN 也要慎用,否则会导致全表扫描。对于连续的数值,能用 BETWEEN 就不要用 IN
select id from t where num between 1 and 3;
**注意:**
select id from t where num not in (1,2,null);
如果查询集合中有null,不适合用not in,容易出错(查询结果为空)。
为什么not in (1,2,null)会出错呢?
因为not in (1,2,null) 等价于x!=1 and x!=2 and x!=null ,**而在sql中,对于任意x,x!=null永远为false**,所以整体结果永远为false,所以查询结果永远为空。
**解决方法**
(1)仍使用not in ,但修改sql语句。
将in后的查询结果中的null过滤掉。
select name from a where name not in (
select name from b where name is not null
);
(2)使用not exists
共有的不要了,要独有的。
select * from a
where not exists(
select 1 from b where a.col = b.col
);
16、选择 exists 代替 in
**in**先查询子查询的表,然后将内表和外表做一个笛卡尔积,然后按照条件进行筛选。子查询为驱动表
当在两表查询的时候, 外表是大表(数据量多),内表是小表(数据量少),把外表(A)和内表(B)做hash连接,,先查询内表,再把内表结果与外表匹配,对外表使用索引(外表效率高),而内表都需要查询,不可避免,故外表大的使用in,可加快效率。
**exists**先执行主查询,再根据主查询的结果,执行子查询。主查询为驱动表
当在两表查询的时候, 外表是小表(数据量少),内表是大表(数据量多), 对外表做loop循环,每次loop循环再对内表进行查询,对内表的查询可使用索引(内表效率高),而外表大都需要遍历,不可避免(尽量用小表),故内表大的使用exists,可加快效率;
当子查询的结果较小,且主查询的表较大且有索引时,应用in。
当主查询的结果较小,而子查询的表较大且有索引时,应用exists。
SQL必须遵循“小表驱动大表“的原则
表A(小表),表B(大表)
select * from A where id in(select id from B) -->效率低,用到了A表上id列的索引;
select * from A where exists(select id from B where id =A.id ) -->效率高,用到了B表上id列的索引。
select * from B where id in(select id from A) -->效率高,用到了B表上id列的索引
select * from B where exists(select id from A where id=B.id) -->效率低,用到了A表上id列的索引。
17、where后面的字段,留意其数据类型的隐式转换。
SELECT *FROM user WHERE NAME=110;
因为不加单引号时,是字符串跟数字的比较,它们类型不匹配;
MySQL会做隐式的类型转换,把它们转换为数值类型再做比较;
18、为 WHERE 及 ORDER BY 涉及的列上建立索引
对查询进行优化,应尽量避免全表扫描,首先应考虑在 WHERE 及 ORDER BY 涉及的列上建立索引
19、选择重复值较低的字段建索引
在创建索引时,一定要选择重复值较低的字段。离散型非常的差,优化器可能直接就选择不走索引了,因为优化器可能认为,走索引和全表扫描差不多。值分布很稀少的字段不适合建索引,例如"性别"这种只有两三个值的字段不适合做索引。
20、join使用问题
尽量不要使用子查询,表设计好很关键
21、asc和desc混用,导致索引失效
select * from_t where a=1 order by b desc, c asc;
22、在新建临时表时,如果一次性插入数据量很大,那么可以使用 select into 代替 create table,避免造成大量 log , 以提高速度;
如果数据量不大,为了缓和系统表的资源,应先create table,然后insert。
SQL索引的优化-执行计划EXPLAIN
EXPLAIN可以分析SQL问题,写出更优质的SQL查询语句。使用方法,在select语句前加上EXPLAIN即可
EXPLAIN返回参数(重点关注加粗):
(1)id:执行计划中每个操作的唯一标识,多表联查的时候会出现多条数据id是一样的
(2)select_type:查询类型,常见类型有:SIMPLE、PRIMARY、UNION、SUBQUERY
(3)table:涉及到的表
(4)partitions:涉及到的分区
(5)type:查询时使用到的索引类型:优>差:system>const>eq_ref>ref>range>index>ALL
system:系统表
const:常数索引,通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量
eq_ref:唯一索引,使用primary key 或者unique key 索引的所有部分,只扫描索引树种的一个一个匹配行,返回唯一一条数据
ref:非唯一索引,而是使用普通索引或者唯一索引的部分前缀,只扫描索引树种一部分数据来查找匹配行,可能返回多条数据
range:范围索引,只扫描索引树种的一个范围来查找匹配的行
index:全索引扫描,使用某个二级索引,遍历索引树
ALL:全表扫描
(6)possible_keys:可能被查询优化器使用到的索引
(7)key:查询优化器选择的索引,会显示实际使用到的索引名称
(8)key_len:索引长度
(9)ref:索引的那一列被使用
(10)rows:估计要读取并检测的行数
(11)filtered:按表条件过滤的行百分比
(12)Extra:额外信息
Using where:表示使用了where过滤,过滤条件字段无索引 。
Using index:使用了覆盖索引,只需要扫描索引,无需回表检索(推荐)
using index condition:查询的列被索引覆盖,where筛选条件是索引列之一,确实命中了索引,但不是所有的列数据都在索引树上
Using temporary:创建了临时表用来存储结果,排序或者分组的情况下,需要创建索引(典型:group by和order by同时存在,且作用于不同的字段时,就会建立临时表)
Using filesort:表示(order by列)没有使用索引的排序,数据较小时从内存排序,否则从磁盘完成,需要创建索引
Using join buffer (Block Nested Loop):需要进行嵌套循环计算,这类SQL语句性能往往也较低,需要进行优化。典型的,两个关联表join,关联字段均未建立索引,就会出现这种情况。常见的优化方案是,在关联字段上添加索引,避免每次嵌套循环计算。
SQL数据库、表优化
分库分表是数据库设计中的一个重要策略,用于处理单个数据库实例无法处理的大量数据和高并发的情况。
1、分表
(1)纵向分表
就是把一张表中的字段,切分到多张表。
分表理由:表的字段太多,根据数据的活跃度进行分离(因为不同活跃的数据,处理方式是不同的)
案例:
对于一个博客系统,文章标题,作者,分类,创建时间等,是变化频率慢,查询次数多,而且最好有很好的实时性的数据,我们把它叫做冷数据。而博客的浏览量,回复数等,类似的统计信息,或者别的变化频率比较高的数据,我们把它叫做活跃数据。所以,在进行数据库结构设计的时候,就应该考虑分表,首先是 纵向分表的处理。
纵向分表后:
首先存储引擎的使用不同,冷数据使用MyIsam 可以有更好的查询数据。活跃数据,可以使用Innodb ,可以有更好的更新速度。
其次,对冷数据进行更多的从库配置,因为更多的操作是查询,这样来加快查询速度。对热数据,可以相对有更多的主库的横向分表处理。
其实,对于一些特殊的活跃数据,也可以考虑使用memcache ,redis之类的缓存,等累计到一定量再去更新数据库。或者mongodb一类的nosql 数据库。
(2)横向分表
就是是把大的单表结构,横向切割为同样结构的多表,如,用户信息表,user_1,user_2 等。表结构是完全一样,但是,根据某些特定的规则来划分的表,如根据用户ID来取模划分。
分表理由:根据数据量的规模来划分,保证单表的容量不会太大,从而来保证单表的查询等处理能力。
案例:
对于一个博客系统。当博客的量达到很大时候,就应该采取横向分割来降低每个单表的压力,来提升性能。例如博客的冷数据表,假如分为100个表,当同时有100万个用户在浏览时,如果是单表的话,会进行100万次请求,而现在分表后,就可能是每个表进行1万个数据的请求(因为,不可能绝对的平均,只是假设),这样压力就降低了很多很多。
2、分库
同分表策略
3、分库分表
分表+分库
4、分库分表采用的策略
(1) Range 范围
按某个字段的数据区间来进行切分。
比如:user表按照 user_id 的数据范围切分成多张表,每 1000万条数据存放一张表,切分后的表可以放到同一个数据库,也可以放 到不同的数据库,示例图如下
优点:
方便扩容,每次数据量达到 range值就新加一张表,可以通过代码实现自动化扩容;
缺点:
存在写偏移,可能有热点问题;
用户注册场景:user表,因为新注册的用户数据都是写新表,通常来说新用户的活跃度高,所以读写流量全部集中在最新的 user表,因此,新表可能存在热点问题。
(2)hash切分
通过对分表键 key 进行一定的运算(通常有取余、取模运算,比如:key % m,key / m,hash(key)/m 等等),通过运算结果来决定路由的库和表。目前大多数互联网公司主要采用该方法。
比如:user表信息,根据 user_id 对 10 取余,这样就可以通过 user_id 尾号 hash 到 user_0 到 user_9 10张表中:
优点:
数据分片比较均匀,大大降低热点问题;
缺点:
hash 算法选择不合理,后期扩容可能需要迁移数据;
数据被切分到不同的库和表中,可能存在跨节点查询和分页等问题;
(3)映射表
映射表其实是 Range范围 和 hash切分的混合模式,将分表键和数据库的映射关系记录在一个单独的表(表的形式可以是 数据库表,文件或者配置中心)。
某社区电商下单场景,因为全国仓库的数量有限,所以分库直接使用了仓编编码-数据库映射表(后期新增加仓库,只要在表中增加映射关系),为了保证履约的时效性,用户下单时,商城端会选择最近的仓库,服务器在映射表中根据仓库编码查询并路由到对应的数据库,最后在库中进行 order表的操作,交互如下图:
优点:
可以灵活设置路由规则;
缺点:
方案比较复杂;
映射表可能也会随着业务量的增大,同样需要分库分表,带来更多的问题;
5、分库分表的问题
(1)调试和维护难度
单库单表,可以很直观在表中查看数据,分库分表后,需要先根据 key找到库和表,这样在一定意义上增加了开发人员定位问题的难度,再因为库和表的增多,维护难度自然也上去了(公司有DBA可以交给他们)。
(2)分布式ID
单库单表,可以直接使用表自增主键保证全局唯一性,分库分表后,需要自己维护全局唯一的ID,常用的算法有:UUID、号段模式(数据库生成全局ID)、雪花算法。
对于公司内部没有分布式ID相关实现的,可以使用或借鉴 美团开源的Leaf ,该框架提供了雪花算法和号段模式两种方案。
(3)分布式事务
业务划分的时候规避分布式事务;
使用专业的的分布式框架,比如阿里开源的 Seata、Redission redis分布式锁;
(4)跨库关联/分页/排序
单库单表可以直接使用 MySQL limit 特性实现分页,分库分表后,可能会出现分页问题,解决方案有三种:
选择合适的分表字段,规避绝大部分高频查询场景出现跨库;
使用专业的分布式框架,比如开源框架:ElasticSearch;
业务代码中分别查询,然后组装数据;
(5)分库分表工具
客户端模式
客户端模式是指在客户端实现直连数据库,客户端通常是通过一些封装好的 jar来实现,常见的开源中间件有:Apache的Sharding-JDBC、淘宝的TDDL、美图的Zebra。
代理模式
代理模式是指需要单独部署服务,客户端连接代理服务,由代理服务再和数据库交互。
常见的开源中间件有:Apache的 Sharding-Proxy、阿里的 cobar、国产的 MyCat、360的 Atlas、 google的 vitess,它是基于 zookeeper,通过 RPC方式进行数据管理。
总结
两种方案的核心思想都是类似的,都是将分库分表的逻辑进行抽象封装,业务无需关注分库分表的实现细节,只需按照规则进行简单的配置和开发,就能正常的使用分库分表。
客户端模式比较轻量,性能也会比较好;代理模式需要部署额外的服务器,所以对于该服务器的稳定性和性能等都需要保障。