MySQL数据库优化

数据库优化

前提
数据库三范式
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分页 当偏移量大的时候 使用一个带有索引的字段进行排序或者过滤 就不再是全表扫描

30种sql语句优化

发布了12 篇原创文章 · 获赞 36 · 访问量 4060
展开阅读全文

没有更多推荐了,返回首页

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客

分享到微信朋友圈

×

扫一扫,手机浏览