数据库 - 数据库--Mysql - 《JAVA全家桶系列》

基础+概念
连接池
MVCC
InnoDB的行锁模式
Explain查新执行计划
语句优化
- 表优化
高质量SQL(30条)
- 优化指南

基础+概念

mysql 的where 无法使用聚合函数，所以加了having子句。

聚合函数
- avg
- count
- sum
- max
- min
- last
- first

组成

Server
- 连接器：管理连接，权限验证。
- 分析器：词语分析。
- 优化器：执行计划生成，索引选择。
- 执行器：操作存储引擎，返回执行结果。
存储引擎

范式
第一：属性不可分。
第二：主键依赖。属性完全依赖于主键。
第三：消除传递依赖，属性不依赖其他非主属性属性跟主键是直接关系而不是间接关系，属性直接依赖于主键。表中不包含其他表的非主线字段。
总结：1、一列一个值2、每行能区分3、表中没有其他表的非主键字段。

恢复

备份+binlog(手动开启)

事务隔离级别
读未提交：可读别的事务未提交的
读已提交（Oracle、SqlServer默认），提交后才能看见。防止脏读。
可重复读（Mysql默认）未提交不可见，事务执行中的数据总是跟启动时看到的一致。。防止脏读、不可重复读。
串行化：同一记录读写都加锁，冲突阻塞。防止脏读、不可重复读、幻读。
Sql解析顺序

Sql执行顺序：

from—where–group by—having—select—order by

//完整语句
select distinct
<selcet_list>
from
<table_a> <join_type>
join <table_b> on <join_condition>
where
<where_condition>
group by
<group_by_list>
having
<having_condition>
order by
<order_by_condition >
limit <start>,<limit_number>
//执行顺序
from <table_a><join_type>
on <join_condition>
<join_type> join <table_b>
where <where_condition>
group by <group_by_list>
having<having_condition>
select
distinct<select_list>
order by <order_by_condition>
limit <start>,<limit_number>

海量数据的分页

数据量过大直接limit offset会慢，可配合其他如id:select xxx from t where id > #{ID} limit #{limit} ,要求id有序。

优化

explain执行计划。
尽量主键查询，减少回表查询。
频繁查询字段考虑覆盖索引。
组合索引最左匹配原则。

连接池

MVCC
多版本并发控制是MySQL中基于乐观锁理论实现隔离级别的方式，用于读已提交和可重复读取隔离级别的实现。在MySQL中，会在表中每一条数据后面添加两个字段：最近修改该行数据的事务ID，指向该行（undolog表中）回滚段的指针。Read View判断行的可见性，创建一个新事务时，copy一份当前系统中的活跃事务列表。意思是，当前不应该被本事务看到的其他事务id列表。

InnoDB的行锁模式
共享锁(S)：用法lock in share mode，又称读锁，允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。若事务T对数据对象A加上S锁，则事务T可以读A但不能修改A，其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。
排他锁(X)：用法for update，又称写锁，允许获取排他锁的事务更新数据，阻止其他事务取得相同的数据集共享读锁和排他写锁。若事务T对数据对象A加上X锁，事务T可以读A也可以修改A，其他事务不能再对A加任何锁，直到T释放A上的锁。在没有索引的情况下，InnoDB只能使用表锁。

Explain查新执行计划

explain模拟优化器执行SQL语句，在5.6以及以后的版本中，除过select，其他比如insert，update和delete均可以使用explain查看执行计划，从而知道mysql是如何处理sql语句，分析查询语句或者表结构的性能瓶颈。
作用1、表的读取顺序2、数据读取操作的操作类型3、哪些索引可以使用4、哪些索引被实际使用5、表之间的引用6、每张表有多少行被优化器查询
字段
- id:选择标识符。
- select_type：标识查询的类型。
- table:表名。
- partitions:匹配的分区。
- type:重要,表示连接使用了那种类型.从好到差:const,eq_reg,ref,range,index和all.(至少range以上).
- possible_keys:可能应用的索引.
- key:实际使用的索引.
- key_len:索引长度,不损失精度情况下,越小越好.
- ref:列与索引的比较。
- rowsmysql认为的检索行数.
- filtered：按表条件过滤的百分比
- extra:执行情况的描述和说明。
  
  语句优化

避免使用in 和 not in，效率低，因not in 不能命中索引，改成 not exists好很多。in值不应过多。

select from 表A where id in (select id from 表B) select from 表A where exists(select * from 表B where 表B.id=表A.id) 区分in和exists主要是造成了驱动顺序的改变（这是性能变化的关键），如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。所以IN适合于外表大而内表小的情况；EXISTS适合于外表小而内表大的情况。
select指明字段。
排序没用到索引，就少排序。
or前后有一个没索引，索引失效。
不要在列上进行计算。，将导致索引失效而全表扫描。
索引不会包含有null值的列。不要让字段默认值为null。
union 去重，union all不去重。尽量union all去重。
where子句中不要进行null判断，引擎会放弃索引扫描器全表。
where子句对字段进行表达式操作。

select user_id,user_project from user_base where age*2=36;（低效） select user_id,user_project from user_base where age=36/2;（建议）
避免隐式类型转换(索引失效)

where子句中出现column字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型。
force index强制走正确的索引

表优化

大表优化
- 限定范围
- 读写分离
- 垂直分区：电话，姓名
- 水平分区：偶数
分库分表id处理：需要全局唯一id
- UUID：长，无序查询效率低。
- 自增：不同库设置不同步长，需要独立部署数据库实例，成本高，性能瓶颈
- redis辅助，灵活，引入组件变复杂
- twitter的snowflake
- 美团的leaf分布式id生成系统

高质量SQL(30条)

优化指南

只要需要的字段 —内存
设置合理的Fetch Size(分页大小) —内存
使用正确索引—速度
减少交互次数（批量提交） —速度
总的可分为三类
- 语句优化
  - 语义明确 ：
    - 大小写统一
    - 连表使用别名
- 索引相关
  - 防止索引失效：
    - like的%在前；
    - or前后字段有一个没有索引；
    - 隐式类型转换；
    - 字段运算；
    - 字段使用内置函数；
    - 重复性大的字段（优化器放弃索引）；
    - !=、<>操作优化器放弃索引；（普通索引）
    - 默认字段替代nul（null导致放弃索引）
    - 尽量使用索引：
    - order by;
    - limit；大量数据先过滤
    - 覆盖索引；
    - 组合索引（最左匹配）
    - 索引不能过多：
    - 影响插入更新效率；
- 资源消耗
  - 减少查询次数：
    - 避免循环查表；
  - 减少返回结果体积：
    - 不用select *；
    - 分页；
    - union all 替代union
  - 减少扫描行数：
    - 小表驱动大表（表连接、嵌套、in和exist）
根据业务场景来
explain：查看执行计划

不要select * 。
因：节省资源，覆盖索引。
确定结果只有一条或找最值，加上limit 1; （待测试无效）
加上后找到结果旧不继续进行扫描了；防止全表扫描
若为唯一索引就没必要了。

避免where子句中使用or连接

select * from user where user = '233' or user = '2';
--优化后
select * from user where user = '233' ；
union all --all允许重复，union还要去重
select * from user where  user = '2';

or可能导致索引失效从而全表扫描，若无索引无效。
or前后没有同时使用索引会导致索引失效。

limit优化，
偏移量过大导致低效

--原
select id,name from user where  limit 10000,10。
--方案一，where（最好主索引排序）
select id,xx,name from user where id >10000 limit 10。
--方案二，order by+ 索引（没看懂，但实测有效）
select id,name from user order by id limit 10000,10;
--方案三, 业务允许的情况下限制页数。

like优化
%在前索引失效，在后不失效
保证用到索引

使用where条件限定要查询的数据，避免返回多余的行(撤单)

//需要什么就查什么，避免返回不必要的数据，节省开销
//X
List<Long> userIds = sqlMap.queryList("select userId from user where isVip = 1:);
boolean isVip = userIds.contains(userId);
//√
List<Long> userId = sqlMap.queryObject("select userId from user where userand isVip = '1:);
boolean isVip = userId !=null;

避免索引列使用内置函数
函数会索引失效。若索引列不加函数，还走索引。

--no
select userid,time from loginuser where Date_ADD(time,Interval 7DAY) >= now();
--yes  
select userid,time from loginuser where time> = Date_ADD(NOW(),INTERVAL - 7DAY);

避免where子句对字段进行表达式操作，导致放弃索引扫描全表。
数值变化，索引迷路…
```
select * from user where age -=10;--X
select * from user where age =11;
```

连接优先内连接；左连接左表尽量小。

--X
select * from t1 left join t2 on t1.age = t2.age where t1.age<17;
--√
select * from(select * from t1 where age>17) t1 left join t2 on t1.age = t2.age;

自联结是等值连接，或许返回行数少，
左连接表小同理
10.避免where子句使用=、 <>操作符，会放弃索引扫全表。
11.联合索引最左匹配原则。

--若有索引(name,age)
--X索引失效
select * from user where age = 10;
--√
select * from user where name='xx' and age = 10;
select * from user where name = 'xx';

对查询优化，考虑经常的where 和order by 列建立索引。
13.插入过多，批量插入

//X
for(User u : list){
insert into user(name,age) values(#name,#age)
}
//本地处理数据，减少数据库开销
//yes
insert into user(name,age) values
<foreach collection="list" item="item" index="index" sqparator=",">
(#(item.name),#(item.age))
</foreach>

覆盖索引，减少回表查询

慎用distinct关键字
量大时效率低

select distinct * from user;--no
select distinct name from user;--yes

删除冗余和重复索引
因索引需要维护，
避免循环操作数据库

where子句中考虑使用默认值代替null

--优化器放弃索引
select * from user where age is not null;--no
select * from user where age >0;--yes

不要超过5个以上的表连接

exist & in 的合理使用

--in先查B，再查A
select * from a where depid in (select depin from b)
--exists，先查A，再查B
select * from a where exists (select 1 from b where a.depid = b.depid)

//in抽象循环就是
List<> resultSet;
for(int i = 0;i<b.length;i++){
for(int j=0;j<a.length;j++){
if(a[i].id == b[j].id){
    resultSet.add(a[i]);
}
break;
}
}
//exists抽象循环就是
List<> resultSet;
for(int i = 0;i<a.length;i++){
for(int j=0;j<b.length;j++){
if(a[i].id == b[j].id){
    resultSet.add(a[i]);
}
break;
}
}

数据库最费劲的就是跟程序的连接和释放。
原则小表驱动大表，层循环尽量小。

尽量union all 代替union

union 会去重，根据业务来。

索引不宜太多，5个以内。

索引降低插入和更新的效率
insert 和 update 有可能重建索引

尽量使用数字型字段，若只含有数值信息的字段尽量不要设计为字符型
相较于数字型，字符型会降低查询和连接性能
```
--not
kind_id varchar(20) not null comment '守护者id'
--yes
kind_id int(20) not null comment '守护者id'
```
索引不适合建在有大量重复数据的字段上，如性别。
因sql优化器根据表中数量进行查询优化，若发现大量重复数据，优化器推算不走索引成本更低，就会放弃索引。
避免向客户端返回过多数量，分页查询。
sql连接多表，使用别名，易读。语义清晰。
varchar/varnchar代替char/nchar；
边长字段节省空间，灵活。
查询小字段效率高

为order by语句效率，可在执行到该语句前，把不需要的记录过滤掉。

--not
select job.avg(salary) from employee group by job having job = 'president' or job='managent';
--yes
select job.avg(salary) from employee where job='president' or job='management'  group by job ;

隐式类型转换

字符串一定加引号，否则跟整数比较，mysql会做隐式类型转换，索引失效。

select * from user where id=123;--not
select * from user where id='123';--yes

explain 分析执行计划。

字段
- id:选择标识符。
- select_type：标识查询的类型。
- table:表名。
- partitions:匹配的分区。
- type:重要,表示连接使用了那种类型.从好到差:const,eq_reg,ref,range,index和all.(至少range以上).
- possible_keys:可能应用的索引.
- key:实际使用的索引.
- key_len:索引长度,不损失精度情况下,越小越好.
- ref:列与索引的比较。
- rowsmysql认为的检索行数.
- filtered：按表条件过滤的百分比
- extra:执行情况的描述和说明。

参考

语句尽量大写
- 因oracle总是先解析SQL语句，把小写字母转换成大写的再执行。
选择最有效率的表名顺序。
- 数据库的解析器按照从右到左的顺序处理from子句中的表名，from子句中写在最后的表将被最先处理。
where子句中的连接顺序
- 数据库采用自右向左的顺序解析where子句，根据这个原理，表之间的连接必须卸载其他where条件左边，那些可过滤最大数量记录的条件必须写在where子句右边。（小表驱动大表）
用>=代替>
- where age>3定位到=3然后扫描大于这个位置的
- where age >=4直接定位到4
in代替or
where 代替having
- having会在检索出记录之后才对结构进行过滤，where是在聚合前刷新记录，若能通过where子句限制记录的数目。having中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在where之中。
union要去重，union all不用。
建表优化
尽量数字型（如性别），因比较字符串要比每个字符，数字一次就够了。
可变长类型代替固定长类型。可变长的null不占空间，固定长占用。
总结
- 最大化利用索引
- 尽可能避免全表扫描
- 减少无效数据的查询

数据库--Mysql

基础+概念

聚合函数

组成

范式

恢复

事务隔离级别

Sql解析顺序

Sql执行顺序：

优化

连接池

MVCC

InnoDB的行锁模式

Explain查新执行计划

语句优化

表优化

高质量SQL(30条)

优化指南