文章目录
SQL高级教程
top返回指定条数的记录
select * from user limit 5; // mysql
select * from user where rownum <= 5 // oracle
select into创建表的备份文件
从一个表中查询数据插入另一个表中
select * into user_backup from user;
sql约束
alter table user add unique (id) // 增加唯一约束
alter table user add constraint un_user unique(id, name) //增加命名唯一约束
alter table user drop index un_user //删除唯一约束
alter table user add primary key(id);
alter table user add constraint key_user primary key(id); //命名or多列主键
alter table user drop primary key;
alter table order add foreign key(order_id) reference user(user_id)
alter table order constraint fk_user foreign key(order_id) reference user(user_id);
alter table order drop constraint fk_user
alter table user add check (score>0)
alter table user add constraint ck_user check(phone=‘13366248789’)
alter table drop check ck_user
alter table user alter column city set default ‘bj’
alter table user alter city drop default
创建索引,修改表结构
create index index_name on order(user_id)
create unique index index_name on order(user_id) //创建唯一索引,使任意两条记录都不能有相同的索引值
alter table user drop index index_name
alter table user add column_new date; //新增某列
alter table user alter column column_old varchar; //修改某列
alter table user dro column column_old varchar; //删除某列
创建视图
create view view_name as select * from user where condition; //创建视图,每次查询都会重建视图
create or replace view view_name as select * from user where condition; //更新视图
drop view if exists view_name; //删除视图
sql日期
now() //返回当前日期+时间
curdate() //返回当前日期
curtime() //返回当前时间
date(date) //返回时间表达式的日期部分
extract(unit from date) //返回日期表达式的年、月、日、小时、分钟
date_add(date,Interval expr unit) //向日期添加指定的时间间隔
date_sub(date,Interval expr unit) //向日期减去指定的时间间隔
datediff(date1,date2) //返回两个日期之间的天数
date_format() //以不同的格式显示日期/时间数据
mysql日期数据类型
Date - 格式YYYY-MM-DD
DateTime -格式YYYY-MM-DD HH:MM:SS
TimeStamp -格式YYYY-MM-DD HH:MM:SS
Year -格式YYYY或YY
sql null值处理
SELECT LastName,FirstName,Address FROM Persons where Address IS NULL
SELECT LastName,FirstName,Address FROM Persons where Address IS NOT NULL
sql函数
- avg(column_name)函数 返回数值列的平均值,null值不包括在内
- count(column_name)函数 返回指定列的记录条数,null值不包括在内
- first(column_name)函数 返回指定列中第一个记录的值
- last(column_name)函数 返回指定列中最后一个记录的值
- max(column_name)函数 返回指定列的最大值
- min(column_name)函数 返回指定列的最小值
- sum(column_name)函数 返回指定数值列的总数
- group by函数 根据指定列进行分组
SELECT Customer,SUM(OrderPrice) FROM Orders Group BY Customer - having函数
where 关键字无法与合计函数一起使用
SELECT Customer,SUM(OrderPrice) FROM Orders Group BY Customer
HAVING SUM(OrderPrice)<2000 //查找订单总金额小于2000的客户a) 求出每个客户的订单总金额 b) 过滤出总金额小于2000的客户
关联查询总结
应用场景
- 为了在多张表获取完整结果
- 为了通过一张表过滤另一张表
外连接
用户表(user_id,order_id)
订单表(order_id,price)
- inner join关键字
左表至少存在一个匹配行时,就返回该行
例如:列出所有人的订购 - left join关键字=(left outer join)
左表会返回所有的行
例如:列出所有人,以及他们 - right join关键字=(rightouter join)
右表会返回所有的行
例如:列出所有的订单,以及他们订购的人 - full join关键字
左右表会返回所有的行
例如:列出所有的人,以及他们的订单;所有的订单,以及订购他们的人
SQL优化
怎么判断sql优化的
- 系统级别表象
CPU消耗严重+IO等待严重+页面响应时间过长+日志超时等错误
top命令查看
Prometheus、Grafana监控工具查看 - sql语句级表象
语句冗长+执行时间过长+全表扫描+执行计划中rows、cost很大
执行计划告诉我们本次查询走了全表扫描Type=ALL,rows很大(9950400)基本可以判断这是一段"有味道"的SQL
sql编写技巧
- 合理使用索引
索引少了查询慢;索引多了占用空间长,而且修改操作还需要动态维护索引;所以通常将where频繁使用的列,join列建立索引。 - 使用Union all代替Union,因为Union执行时需要进行重新排序
执行计划的使用
SQL优化一定要先阅读执行计划,执行计划会告诉你哪些地方效率低,哪里可以需要优化。
字段 | 解释 |
---|---|
id | 标识执行的操作,id值越大越先执行 |
select_type | 查询中每个select 字句的类型 |
table | 被操作的对象名称,通常是表名 |
partitions | 匹配的分区信息(对于非分区表值为NULL) |
type | 连接操作的类型 |
possible_keys | 可能用到的索引 |
key | 优化器实际使用的索引(最重要的列) 从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL。当出现ALL时表示当前SQL出现了全表扫描 |
key_len | 被优化器选定的索引键长度,单位是字节 |
ref | 表示本行被操作对象的参照对象,无参照对象为NULL |
rows | 查询执行所扫描的元组个数 |
filtered | 条件表上数据被过滤的元组个数百分比 |
extra | 执行计划的重要补充信息,当此列出现Using filesort , Using temporary 字样时就要小心了,很可能SQL语句需要优化 |
优化案例
CREATE TABLE `a`
(
`id` int(11) NOT NULLAUTO_INCREMENT,
`seller_id` bigint(20) DEFAULT NULL,
`seller_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL,
`gmt_create` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `b`
(
`id` int(11) NOT NULLAUTO_INCREMENT,
`seller_name` varchar(100) DEFAULT NULL,
`user_id` varchar(50) DEFAULT NULL,
`user_name` varchar(100) DEFAULT NULL,
`sales` bigint(20) DEFAULT NULL,
`gmt_create` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
);
CREATE TABLE `c`
(
`id` int(11) NOT NULLAUTO_INCREMENT,
`user_id` varchar(50) DEFAULT NULL,
`order_id` varchar(100) DEFAULT NULL,
`state` bigint(20) DEFAULT NULL,
`gmt_create` varchar(30) DEFAULT NULL,
PRIMARY KEY (`id`)
);
三表关联,查询当前用户再当前时间前后10个小时的订单情况
select a.seller_id,
a.seller_name,
b.user_name,
c.state
from a,
b,
c
where a.seller_name = b.seller_name
and b.user_id = c.user_id
and c.user_id = 17
and a.gmt_create
BETWEEN DATE_ADD(NOW(), INTERVAL – 600 MINUTE)
AND DATE_ADD(NOW(), INTERVAL 600 MINUTE)
order by a.gmt_create;
3. 初步优化思路
a) where字段类型和表结构一致,避免隐式转换
b) 因为存在b表和c表关联,所以对关联键user_id创建索引
c) 因为存在a表和b表关联,所以对关联键seller_name创建索引
d) 利用复合索引消除临时表和排序
alter table b modify `user_id` int(10) DEFAULT NULL;
alter table c modify `user_id` int(10) DEFAULT NULL;
alter table c add index `idx_user_id`(`user_id`);
alter table b add index `idx_user_id_sell_name`(`user_id`,`seller_name`);
alter table a add index `idx_sellname_gmt_sellid`(`gmt_create`,`seller_name`,`seller_id`);
继续优化alter table a modify “gmt_create” datetime DEFAULT NULL;
优化步骤总结
- 查看执行计划explain
- 若有告警信息,通过show warings查看告警信息
- 查看sql涉及的表结构和索引信息
- 根据执行计划查看可能的优化点
- 按照可能的优化点执行表结构变更、增加索引、sql改写等操作
- 查看优化后的执行计划和时间,然后循环往复
组合索引
组合索引的使用效果:
- 组合索引是有序的,遵守最左原则。
多列索引先按第一列进行排序,然后在第一列基础上再对第二列进行排序。 - 组合索引的使用
例如组合索引(a,b,c),从前往后依此生效,若中间有断点的情况,断点前起作用,断电后不起作用
where a=3 and c=5… 这种情况下b就是断点,a发挥了效果,c没有效果
where b=3 and c=4… 这种情况下a就是断点,在a后面的索引都没有发挥作用,这种写法联合索引没有发挥任何效果;
where b=45 and a=3 and c=5 … 这个跟第一个一样,全部发挥作用,abc只要用上了就行,跟写的顺序无关
mysql只能使用一次索引么
MySQL5.0之前,一个表一次只能使用一个索引,无法同时使用多个索引分别进行条件扫描。但是从5.1开始,引入了 index merge 优化技术,对同一个表可以使用多个索引分别进行条件扫描,然后将它们各自的结果进行合并
index merge 算法根据合并算法的不同分成了三种:intersect, union, sort_union.
index merge 之 intersect
index intersect merge将多个索引条件扫描的结果进行交集运算,所以在多个索引提交之间是 AND 运算时,才会出现 index intersect merge
- 使用到复合索引所有字段or左前缀字段
- 主键上任何范围条件
SELECT * FROM innodb_table WHERE primary_key < 10 AND key_col1=20;
index merge 之 union
index union merge将多个索引条件结果进行并集运算,显然是多个条件之间进行or运算
- 使用复合索引中所有字段or左前缀字段
- 主键上任何范围条件
- 任何符合index union merge的where条件
index merge 之 sort_union
index sort_union merge将多个条件结果进行并集运算,包括索引条件结果和非索引条件结果
index merge的局限
(a AND b) OR c = (a OR c) AND (b OR c)
(a OR b) AND c = (a AND c) OR (b AND c)
如果我们的条件比较复杂,用到多个 and / or 条件运算,而MySQL没有使用最优的执行计划,那么可以使用上面的两个等式将条件进行转换一下
如果where条件中有 >, <, >=, <=等条件,那么优化器不会使用 index merge,而且还会忽略其他的索引,不会使用它们,哪怕他们的选择性更优
index merge进一步优化
index merge让我们可以使用到多个索引同时进行扫描,然后将结果进行合并,但是如果出现了 index intersect merge,那么一般同时也意味着我们的索引建立得不太合理,因为 index intersect merge 是可以通过建立 复合索引进行更一步优化的
SELECT * FROM t1 WHERE key1=1 AND key2=2 AND key3=3;
我们可以在这三个字段建立一个复合索引来进行优化,这样就可以只需扫描一个索引一次,而不是扫描3次
复合索引的最左前缀原则
mysql的索引使用的B+Tree,先按照复合索引的 第一个字段的大小来排序,插入到 B+tree 中的,当第一个字段值相同时,在按照第二个字段的值比较来插入的
注意最左前缀,并不是是指:一定要按照各个字段出现在where中的顺序来建立复合索引的
复合索引,哪个字段放在最前面,需要根据哪个字段经常出现在where条件中,哪个字段的选择性最好来判断的。
Index column size too large. The maximum column size is 767 bytes.是什么原因导致的,如何处理
- 原因
对于行格式为REDUNDANT和COMPACT的InnoDB表来说,索引的最大长度为767字节
对于行格式为DYNAMIC和COMPRESSED格式的InnoDB表最大索引长度允许达到3072字节 - 索引长度计算规则
a) 通常key_len等于索引列类型的字节长度。int为4字节
b) 若是字符串类型,需考虑字符集(latin1为1字节/gbk为2字节/utf8为3字节/utf8mb4为4字节) 例如CHAR(30) UTF8则key_len至少是90字节
c) 若是日期类型需要考虑精度值,datetime为5字节+精度值 timestamp为4字节+精度值
d) 若允许null,则再加1字节
e) 若是变长类型,则再加2字节 - 处理方法
a) 采用部分索引而不是整列索引
b) 修改innodb_file_format为Barracuda,同时修改表的row format为DYNAMIC
举几个你遇到的mysql中隐式转换类型
- 表中定义字符类型,where条件传入数字类型
- 关联字符类型不一致导致,比如驱动表int传入被驱动表字符串中关联。t1.a int,t2.a varchar(10),select * from t1 left join t2 on t1.a=t2.a
- 关联字符集效验规则不一致导致
怎么判断出现隐式转换呢??
- 在命令行查看执行计划,通过show warnings观察到due to type or collation conversion on field
- key那列是null,没有使用到索引
mysql执行sql时一个表只能用到一个索引么
并不是,以下几种情况一个表可能会使用多个索引
- index merge
在执行计划的type列显示index_merge,key显示了使用的索引,Extran列显示Using intersect/union/sort/union - 表自关联
例如:c1、c2是两个单列索引,SQL如下:select * from t as t1 join t as t2 on t1.c1=t2.c2 where t1.c2=1;
mysql前缀索引有什么特点??使用上有什么限制
前缀索引:需要为前面几个字符创建索引
索引选择性:不重复的索引/总记录数,因为索引选择性越高,越能过滤更多的行,查询效率越高。例如唯一索引的查询效率最高
alter table xw_user_applyloan add index uname( old_kefuid(4) )
怎么确定前缀索引长度
- 通过length函数计算前n个字符长度的记录数占比来决定索引长度,一般达到80%-90%即可
select count(*) from t where length(c1)<=15;
优缺点
优点:对列的部分长度建立索引,减少索引的大小key_len,达到节省空间,提高查询效率的目的
缺点:1. 前缀索引无法使用覆盖索引的特性,因此必须回表获取数据
2. 因为只对部分记录做索引,所以无法利用前缀索引完成分组和排序
使用场景
1、适合乱序数字或前n个字符可选性高的情况或者是text/blob这种无法整列创建索引的大字段
2、不适合于前n个字符重复率很高或者需要利用覆盖索引优化的场景
数据库相关面试题
事务的四个特性及含义
数据库事务transanction正确执行的四个基本要素。ACID包括原子性(Atomicity)、一致性(Correspondence)、隔离性(Isolation)、持久性(Durability)。
原子性:当事务提交后,事务中的操作要么全部完成,要么全部不完成。若事务执行过程中发生错误,会回滚到事务开始前的状态。
一致性:当事务提交后所有变化都协调一致的作用于系统,例如A转钱给B,A扣减100,B增加100,这样帐才能对的上,这就是一致性
隔离性:多个并发事务同时访问数据库时,事务之间没有干扰。
持久性:当事务提交后,该事务对数据库的操作会持久化的保存到数据库中。
事务的隔离级别
MySql默认的隔离级别为Repeatable Read,因此只会出现幻读的情况。
例如:现在有一张表,里面记录了很多牛人的名字
- 第一天,事务A添加牛人Bruce Leeqee到表中,但没有提交事务;这时另一个事务B要查询所有牛人的名字,可以查到Bruce Leeqee。这就导致了脏读(读到了未提交的数据)
- 第二天,事务A查询id=1的牛人叫Bruce Leeqee,这时事务B将id=1的牛人改成了BruceLi,接着事务A又查询id=1的数据却发现不是Bruce Leeqee,这就是不可重复读(两次读取到的数据不同) 修改操作
3.第三天, 事务A查询所有牛人的名字,这时事务B添加了一个新牛人BruceLi,接着事务A又查询所有牛人的名字,却发现了多了一个BruceLi这就是幻读(两次读取到的数据条数不同) 插入和删除
不可重复读测试「读-读」,而幻读侧重「读-写」
幻读和脏读有点类似,脏读是事务 B 里面修改了数据,幻读是事务 B 里面新增了数据。
数据库的隔离级别
- 读未提交:可以读到未提交的内容,查询不加锁,可能会产生“脏读”“不可重复读”“幻读”
- 读提交:只能读到已经提交的内容。快照读
- 可重复读:事务启动不允许修改操作。
- 串行化:
MVVC多版本并发(读)控制
- 每次更新都会复制一条新得记录,创建时间为当前事务id
- 读不加锁,读写不冲突
- InnoDb存储引擎中,每行数据包含了一些隐藏字段DATA_TRX_ID,DATA_ROLL_PTR,DB_ROW_ID,DELETE BIT
DATA_TRX_ID:记录数据的创建和删除时间,时间指的是事务Id
DATA_ROLL_PTR:指向当前数据的undo log记录,进行回滚
DELETE BIT:表示该记录是否被删除,真正的删除是通过GC来进行删除的
具体DML: - insert: 创建一条新数据,DATA_TRX_ID为当前事务id,DATA_ROLL_PTR指向null
- update:将当前行的DATA_TRX_ID设置为当前事务id,DELETE BIT设置为1
- delete:复制一行,将行的DATA_TRX_ID设置为当前事务id,DELETE BIT设置为null
DATA_ROLL_PTR指向上一个版本记录,事务提交后将DATA_ROLL_PTR置 null
因为是多版本读,肯定读不到隔壁事务新插入的数据,所以解决了幻读
MVVC与隔离级别
Read Uncommitted每次都会读取记录最新版本,会出现脏读,为实现MVVC
Serializable对所有读操作都加锁,未实现MVVC
查找时InnoDb检查每行数据,确保它们符合两个条件
- 只查找创建时间<当前事务id的记录,保证读取的行是已经存在的,或由当前事务创建or修改的行
- 若DELETE BIT为1时,查找删除时间>当前事务id的记录,保证读取的行没有被删除
- 每次重新从最大事务id中读取数据,并且读取的是Roll_Pt指向的记录
非锁定读可以限制RR的幻读,但仍然存在insert/update是遇到的幻读现象,因为
Next-Key Lock
其实更多的幻读是由于 写操作 造成的。
如SELECT了3条数据,UPDATE的时候可能返回了4个成功结果,或者INSERT某条不在的数据时忽然报错说唯一索引冲突等
首先了解一下InnoDb的锁机制
Record Lock:单个行记录上的锁
Gap Lock:间隔锁,锁定一个范围,但不包括记录本身。目的为了防止同一事务的两次读,出现幻读的情况。
Next-Key Lock:前两个锁的加和。锁定一个范围,并且锁定记录本身。目的是解决幻读的问题。
如果是排他锁操作(insert/update/delete+select from update/lock in share mode),它们默认都是在记录上加了Next-Key Lock,记录本身和记录周围加锁,但是会导致有冲突的事务阻塞而导致超时。
隔离性级别越高,并发度越差,性能越差。
优秀博客https://www.jianshu.com/p/47e6b959a66e