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语句优化