sql 执行顺序
form on where group by having select distinct onder by limit
批量更新
update table a ,table b set a.filed = b.filed where a.id = b.id
mysql8 递归查询
with resursive cte as
(select * from table where id = ? union select t.* from table t,cte tcte where t.filed = tcte.id)
select id from cte where filed = ?
1、定义一个cte,cte是最终的结果，就是我们想要的递归等到的树结构，resursive 代表当前cte 是递归
2、第一个select 为出十结果集
3、第二个select 为递归部门，利用粗还是结果集进行查询得到新的结果集，直到递归部门结果集返回为null,结束查询
4、最终 union all 会将上述步骤的索引结果集合并。**union disttinct会进行去重** 在通过select * from cte 拿到所有结果集
sql分类
1、DQL 数据库查询语言
2、DML 数据库操作语言
3、DDL 数据库定义语言
4、TCL 事务和事务处理
函数
ifnull() 将null 替换成自定义字段
left( field, number)
right( field,number)
substr()
concat() 拼接
length()
now()
str_to_date 字符串转化成日期格式
mod() 取模
truncate 截取
聚合函数
1、sum
2、avg
3、max
4、min
5、count count(条件 or null)
- sum avg 用于处理数值型
- max min count 处理任意类型
- 聚合函数一听查询的字段要求是group by 后的字段
- 聚合函数是在group by 之后进行计算
分组函数
group by having
where、having 后边的筛选条件必须是一个聚合函数或者select 后查询的列
条件
if
if(?,value1,value2)
case 类似与Java的 swict
case 字段 when 常量 then 返回值;else 默认值 end
case 类似于Java的 if...else
case when 条件 then 返回值或者是语句; else 默认值 end
连接
内连接
连接结果仅包含符合连接条件的行，参与连接的两个表都应该符合连接条件
等值（内连接）
elect a.id,b.id, table1 a inner join table2 b on a.id = b.id
非等值（内连接）
select a.id,b.id, table1 a inner join table2 b on a.id bteween b.fileda and b.filedb
外连接
连接结果不仅包含符合连接条件的行，同时也包含自身不符合的行
左/右外连接
select * from table a left join table b on a.id = b.id
子查询
出现在其他语句中的select 语句，称为子查询
出现位置
- select 后面用做计数
- from 表子查询
- where,having 标量子查询
- in any all 列子查询
- exists
- union 联合查询
in，not in, exists, not exists
in
适用于外表大内表小
确定给定的值是否在子查询或者列表中的值相匹配，in在查询的时候，首先查询子查询的表，然后将内表与外边做一个笛卡尔积，然后按照条件进行筛选
exists
适用于外表小而内表大的情况，exists 执行table.lenght次
首先查询主表内容，根据表的每一条记录，执行语句，依次判断where 后面的条件，返回true,false true 保留该行，false删除该行
not in
查询语句使用not in 内外表都进行了全部扫描，没有用到索引
not exists
子查询依然能够用到，所以 not exists 比 not in 效率高
in,exists 区别
- 子查询得出的记录较少，主查询中的表较大且又有索引时，应该用in
- 主查询记录较少，子查询中的表大，又有索引时用exists
- in 和 exists 主要是造成了驱动顺序的改变，如果是exists,以外层表为驱动表，先被访问。如果是in,那么先执行子查询。所以我们会以驱动表的快速返回为目标，那么就会考虑到索引及结果集的关系了，另外，in不对null进行处理
- in 是把外表和内表做hash连接，而exists是对外表做loop循环，每次loop循环在对内表进行查询
约束
1、not null 非空约束
2、default 默认值
3、primary key 主键约束
4、unique 唯一
5、check 检查约束
6、foreign key 外键约束
事务
1、原子性，一个事务不可分割，要么都执行要么都不执行
2、一致性，一个事务执行会使数据从一个状态切换到另外一个状态
3、隔离性，一个事务的执行不受其他事务的干扰
4、持久性，一个事务一旦提交，则会永久的改变数据库的数据
视图
创建视图
create view viewname as sql
修改视图
alter view viewname as sql
删除视图
drop view viewname,...,...
查看视图结构
desc viewname
show create view viewname
索引
帮助mysql高效获取数据的数据结构，索引的目的在于提高查询效率 排好序的快速查找数据结构
优势
- 提高数据检索的效率，降低数据库的IO成本
- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU 的消耗
劣势
- 索引也是一张表，该表保存了主键雨索引字段，并执行实体表的记录，索引列也是占空间的
- 索引提高了查询数据，但是却会降低更新表的速度
分类
- 普通索引：最基本的索引
- 组合索引：多个字段上建立的索引，能够加速符合索引条件的检索
- 唯一索引：索引列的值必须唯一，允许有空值
- 组合唯一索引：列值的组合必须唯一
- 主键索引：唯一标识数据库中的某一条记录，不允许有空值
- 全文索引：海量文本的查询
语法
创建
create [unique] index name on table(列名)
alter table add [unique] index name on (列名)
删除
drop index name on table
查看
show index from table
创建复合索引
create index name on table(col1,col2,....)
结构
- Btree
- Hash
Hash
- hash适合等值查询，无法进行范围查找
- hash没办法利用索引完成排序
- hash不支持多列联合索引的最左匹配规则
- 如有大量重复键的情况，hash索引的效率会很低，因为出现了hash碰撞
Btree 与 Hash 区别与联系
hash
hash索引的底层是hash表，hash表是一种key-value 存储的数据结构，索引多个数据存储之间是没有任何关系的，所以hash只能是等值查找
B+ tree
B+ tree是一种多路平衡树，他的节点是天然有序的 ** 左子节点小于父节点，父节点小于右子节点** 所以B+ tree进行范围查询时时不应全表扫描的
使用索引情况
- 主键自动创建唯一索引
- 频繁作为查询条件的自动应该创建索引
- 查询中与提起表关联的自动，外键关系建立索引
- 查询中统计或分组字段
- 组合索引比单键索引有优势
- 查询中排序，排序字段若通过索引访问将大大提高排序速度
- where 条件里用不到字段不创建索引
不适用索引的情况
- 表记录太少
- 经常增删改的表
- 频繁更新的字段
索引失效
- 索引字段like查询时，通配符在左边时
- or语句左右没有同时使用索引字段，语句左右查询字段只有一个时索引字段时
- 组合索引，第一列不使用索引
- 数据类型出现隐式转换
- 在索引列上使用 is null is not null 索引字段是不能为空值的
- 在索引字段上使用 not <> !=
- 在索引字段上使用函数或者计算
- 全表扫描更快时
避免索引失效
- 全值匹配
- 最佳左前缀法则
- 不在索引列上做任何计算操作
- 存储引擎不能使用索引中所有中范围条件右边的列
- 尽量使用覆盖所有 减少select *
- mysql 不使用 != <> is null is not null
- like 以通配符结尾
- 字符串加单引号
- 少用or
索引优化
- 合理使用覆盖索引
- 字段唯一性太低，增加索引没有意义
- 字符串可以前缀索引，前缀长度控制在5-8个字符
- 单表索引数不超过5个，单个索引字段数不超过5个
- 分页查询很重要，如查询数据量超过30%，mysql不会使用索引
全值匹配
查询的列数与创建联合索引的个数相同
最佳左前缀法则
查询是从索引的最左前列开始并且不跳过索引中的列
join查询优化
join的实现是采用Nested Loop join 算法，就是通过驱动表的结果集作为基础数据，通过该数据作为过滤条件到下一个表中循环查询，然后合并结果，如果有多个join，则将前面的结果集作为循环数据，再次到后一个表中查询数据
- 量表查询，左外右外，索引反这加，在从表中添加索引
- 尽可能减少join语句中的nestedloop 的循环总次数，永远都是小表驱动大表
- 优先优化nestedloop的内层循环
- 保证join语句中被驱动表上join添加字段已经被索引
- 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，joinbuffer的设置可以调大
- 驱动表和被驱动表尽可能增加查询条件，满足on的条件而少用where，用小结果集驱动大结果集
- 被驱动表的join字段上加上所有，无法建立索引时，设置足够的 join buffer size
- 禁止join连接三个以上的表，查实增加冗余字段
[nestedloop] 详细解释 blog.csdn.net/u010841296/…
查询优化
- 小表驱动大表
- in 主表数据集大于内表数据集
- exists 外表数据集小于内表数据集
order by 排序优化
mysql 支持fileSort，index 排序 index效率高
index 排序
1、满足order by 语句使用索引最左前缀法则
2、使用where 子句与order by 子句条件列组合满足索引最左前缀法则
order by 的排序字段与创建索引的组合字段位置，顺序，个数一致，并且排序方式相同，会触发index 排序，否则是fileSort
使用order by 时不能使用select * 查询
group by分组优化
1、大致与order by 相同
2、wehere 高于having 能写在where 限定的条件就不去having 限定
limit 优化
参数含义 第几行，查几条
limit 分页查询是越往后翻性能越差
缩小扫描范围
select * from table order by id desc limit 100000,10
先筛选出ID缩小查询范围
select * from table where id > (select id form table order by id desc limit 100000,1) order by id desc limit 1,10
如查询条件仅有主键ID
select id from table where id betweent 100000 and 100010 order by id desc
游标，jdbc使用游标实现分页查询
like 优化
like 优化基本上是不让索引失效，如必须前后模糊匹配，可以尝试全文索引fulltext
<> or 优化
操作符<>,or 都是无法命中索引的，可以使用 union 代替
text类型优化
抽取出来放在子表里，业务主键关联
避免空值
mysql 中字段为null时依然占用空间，会使索引，索引统计更加复杂，从null 值更新到非null无法做到原地更新，荣耀发生送一分裂影响性能，尽可能将null值用有意义的值代替，也能避免sql语句里面包含is not null
基本原则
- 充分利用但不滥用索引，索引也是消耗磁盘和upc
- 不推荐按使用数据库函数格式化数据
- 不推荐使用外键约束，用程序保证数据准确性
- 多写读少的场景，不推荐使用唯一索引
- 适当冗余字段，尝试创建中介表，用程序计算中间结果，空间换时间
- 不循线执行极度耗时的事务，配合程序拆分成更小的事务
- 数据库少干活，程序多干活
