文章目录
数据库优化
前提:
数据库三范式
1.列不可分割
2.行中数据要有唯一性,主键
3.表中不能有冗余字段,外键
SQL的执行顺序
from -> where -> having -> group by ->select ->order by -> limit
什么时候需要数据库优化
1.高并发读写需求-----集群和分布式
2.海量数据的高效率读写
3.高扩展性和可用性
关系型数据库的优化技术
1.找出执行效率低的sql语句
2.分析慢sql的原因
3.进行优化,优先是单机优化实在满足不了需求再进行多机的集群和分布式。
1.找出执行效率低的sql语句
所有sql都是mysql执行,当它发现客户端执行sql慢的时候,会把结果记录下来。方便用户查找定定位。
常用命令
常用的sql | 作用 |
---|---|
show status like ‘uptime’; | 查看当前数据库运行了多长时间:单位秒 |
show status like ‘%Com_%’ | CRUD执行次数(这是选择存储引擎的依据) |
show status like ‘connections’ | 查询所有连接数 |
show status like ‘Max_used_connections’ | 查看服务器响应的最大连接数(默认100,最大链接数16384) |
定位慢查询语句
系统中所有sql都执行一遍,才能判断是否有慢sql
常用的sql | 作用 |
---|---|
show status like ‘slow_queries’ | 查询慢sql数,通常指花了2S以上的查询(默认10s) |
show variables like ‘long_query_time’ | 可以显示当前慢查询时间 |
set long_query_time=0.5 | 可以修改慢查询时间 |
set global long_query_time=0.5 | 设置定义服务器慢查询时间 默认不写是session 而如果要关闭窗口以后还是设置 加上global |
show status like ‘Max_used_connections’ | 查看服务器响应的最大连接数(默认100,最大链接数16384) |
分析sql
在慢sql语句前加上explain
关键字
2.单机优化
反三范式:
有时候为了提高查询效率的时候,就必须降低范式标准,适当保留冗余数据。
存储引擎:MyISAM 和 INNODB的区别
MySQL默认的是INNODB存储引擎
主要区别
1.MyISAM不支持事务,INNODB支持事务
2.MyISAM查询和添加更快,INNODB更慢
3.MyISAM支持全文检索,INNODB不支持
4.MyISAM支持表锁,INNODB支持行锁
5.MyISAM不支持外键,INNODB支持外键
使用场景
MyISAM对事务的要求不高,对查询效率有要求
INNODB对事务要求高,数据比较重要,如订单
索引
定义
索引(Index)是帮助DBMS高效获取数据的数据结构。MyISAM和INNODB都只支持BTREE索引类型
作用
查询速度的提高是以插入、更新、删除的速度为代价的(以空间换时间),完全可以接受。
1.索引分类
普通索引:允许重复的值出现
唯一索引:索引值要唯一,但可以为null(身份证,手机号)
主键索引:主键作为索引,非空且唯一(id)。数据库会自动随着设定主键而创建的
全文索引:用来对表中的文本域(char,varchar,text)进行索引, 全文索引针对MyISAM有用
2.使用索引
sql语句 | 作用 |
---|---|
show index(es) from 表名 | 查看表中是否创建索引 |
alter table 表名 drop index 索引名; | 删除索引 |
3.注意事项
索引是占用磁盘空间且对dml操作有影响,因为要维护索引变慢。所以我们在使用索引的时候是有一定的条件限制
创建索引的原则
1.在sql语句中经常作为where或者order by的条件字段
2.经常要进行修改的字段不适合作为索引
3.唯一性太差的字段不适合作为索引,如性别
4.复合索引和普通索引
普通索引(单列索引):该索引只在一个列上面创建
复合索引(多列索引):该索引只在多个列上面创建
5.索引失效
常用
1.对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引
2.对于使用like的查询,查询如果是’%aaa’不会使用到索引而‘aaa%’会使用到索引。
3.如果条件中有or,有条件没有使用索引,即使其中有条件带索引也不会使用。换言之,就是要求使用的所有字段,都必须单独使用时能使用索引.
4.如果列类型是字符串,那一定要在条件中将数据使用引号引用起来。否则不使用索引
#表中只有OUhcNKkXfL设置了索引值
#对于创建的多列索引(复合索引),不是使用的第一部分就不会使用索引。
explain select * from dept where dname="OUhcNKkXfL" #使用了索引
explain select * from dept where loc="mbvaQcPS" #不会用到索引
explain select * from dept where dname="OUhcNKkXfL" or loc="mbvaQcPS"#不会使用索引 or会让复合索引失效
explain select * from dept where dname="OUhcNKkXfL" and loc="mbvaQcPS"#会使用
explain select * from dept where dname like "%OUhcNKkXfL%"#不会使用
explain select * from dept where dname like "%OUhcNKkXfL"#不会使用
explain select * from dept where dname like "OUhcNKkXfL%"#会使用 like模糊查询前置匹配会使索引失效
分表
Mysql表数据一般达到百万级别,查询效率会很低,容易造成表锁,甚至堆积很多连接,直接挂掉;分表能够很大程度较少这些压力。分为水平分割(行)和垂直分割(列)
依据
水平(行):业务 id区间 时间 hash分表
hash分表:通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名,然后访问相应的表
最简单hash算法: T_user + Id%3+1 复杂hash算法
复杂度高的hash算法:HashMap的底层
垂直(列):将一些不经常查询的大的字段分一张表 用外键连接
3.SQL优化技巧
1.DDL优化
1.通过禁用索引来提供导入数据性能,变多次维护为一次维护
//去除键
alter table test3 DISABLE keys;
//批量插入数据
insert into test3 select * from test;
//恢复键
alter table test3 ENABLE keys;
2、 关闭唯一校验,变多次唯一校验为一次唯一校验
set unique_checks=0 关闭
//批量插入数据
insert into test3 select * from test;
set unique_checks=1 开启
3、修改事务提交方式(导入),变多次事务提交为一次事务提交
set autocommit=0 关闭
//批量插入
set autocommit=1 开启
2.DML优化
合并多条sql为一条
insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)
3.DQL优化
1.order by优化,多用索引进行排序,因为索引本身就是排好序的
2.group by优化,在group by是使用order by null,取消默认排序
3.subQuery嵌套优化
4.or优化
在两个独立索引上使用or的性能优于
1、 or两边都是用索引字段做判断,性能好!!
2、 or两边,有一边不用,性能差
5.limit优化 在分页是使用一个有索引的字段排序 过滤 是扫描方式由全表扫描变成索引覆盖的扫描
对于limit分页 当偏移量大的时候 使用一个带有索引的字段进行排序或者过滤 就不再是全表扫描