无标签

Java

发布日期: 2024-07-02

尚硅谷JavaEE技术之MySQL高级

SQL语法顺序

存储引擎

1. 各引擎简介

InnoDB存储引擎

InnoDB是MySQL的默认事务型引擎，它被设计用来处理大量的短期(short-lived)事务。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
MyISAM存储引擎

MyISAM提供了大量的特性，包括全文索引、压缩、空间函数(GIS)等，但MyISAM不支持事务和行级锁，有一个毫无疑问的缺陷就是崩溃后无法安全恢复。
Archive引擎

Archive档案存储引擎只支持INSERT和SELECT操作，在MySQL5.1之前不支持索引。

Archive表适合日志和数据采集类应用。

根据英文的测试结论来看，Archive表比MyISAM表要小大约75%，比支持事务处理的InnoDB表小大约83%。
Blackhole引擎

Blackhole引擎没有实现任何存储机制，它会丢弃所有插入的数据，不做任何保存。但服务器会记录Blackhole表的日志，所以可以用于复制数据到备库，或者简单地记录到日志。但这种应用方式会碰到很多问题，因此并不推荐。
CSV引擎

CSV引擎可以将普通的CSV文件作为MySQL的表来处理，但不支持索引。

CSV引擎可以作为一种数据交换的机制，非常有用。

CSV存储的数据直接可以在操作系统里，用文本编辑器，或者excel读取。
Memory引擎

如果需要快速地访问数据，并且这些数据不会被修改，重启以后丢失也没有关系，那么使用Memory表是非常有用。Memory表至少比MyISAM表要快一个数量级。
Federated引擎

Federated引擎是访问其他MySQL服务器的一个代理，尽管该引擎看起来提供了一种很好的跨服务器的灵活性，但也经常带来问题，因此默认是禁用的。

2. MyISAM和InnoDB的区别(重点)

对比项	MyISAM	InnoDB
外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表不适合高并发的操作	行锁,操作时只锁某一行，不对其它行有影响适合高并发的操作
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
关注点	节省资源、消耗少、简单业务	并发写、事务、更大资源
默认安装	Y	Y
用户表默认使用	N	Y
自带系统表使用	Y	N

MySQL Full Join的实现

因为MySQL不支持FULL JOIN,下面是替代方法/

1.UNION 会自动去重

select * from t1 left join t2 on t1.id = t2.id
union
select * from t1 right join t2 on t1.id = t2.id;

2.UNION ALL 的方式 (取左表的全部和右表独有)

select * from t1 left join t2 on t1.id = t2.id
union all
select * from t1 right join t2 on t1.id = t2.id
where t1.id is null;

数据页大小

查看mysql文件页大小（16K）：

SHOW GLOBAL STATUS LIKE '%page_size%'

为什么mysql页文件默认16K？
假设我们一行数据大小为1K，那么一页就能存16条数据，也就是一个叶子节点能存16条数据；再看非叶子节点，假设主键ID为bigint类型，那么长度为8B，指针大小在Innodb源码中为6B，一共就是14B，那么一页里就可以存储16K/14B=1170个(主键+指针)

一颗高度为2的B+树能存储的数据为:1170*16=18720条，
一颗高度为3的B+树能存储的数据为：1170*1170*16=21902400（千万级）

索引的使用场景

哪些情况需要创建索引：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不要创建索引：

表记录太少
经常增删改的表或者字段。

Why：提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
Where条件里用不到的字段不创建索引
过滤性不好的不适合建索引
有大量重复数据的列上。

索引优化

主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引：索引列的值必须唯一，但允许有空值
复合索引：即一个索引包含多个列

CREATE TABLE customer (
    id INT (10) UNSIGNED AUTO_INCREMENT,
    customer_no VARCHAR (200),
    customer_name VARCHAR (200),
    PRIMARY KEY (id), # 主键索引
    KEY idx_name (customer_name), # 单值索引
    UNIQUE KEY uk_name (customer_name), # 唯一索引
    KEY idx_no_name (customer_no, customer_name) # 复合索引
);

explain能干什么

表的读取顺序
那些索引可以使用
数据读取操作的操作类型
那些索引被实际使用
表之间的引用
每张表有多少行被物理查询

在explain中的extra有额外的优化信息。 using join buffer(多表联查)、using filesort(排序)和 using temporary(使用临时表) 需要考虑优化。

实例分析,Using temporary无论是基于内存，还是基于硬盘。构建临时表的过程是比较耗时的一个操作

Using filesort就不用说了，没有走索引导致的。重点来看Using temporary。为何会产生临时表。

mysql它以表中数据最小的一张表作为驱动表(也就是基表)，而另一张表就叫做非驱动表，首先处理驱动表中每一行符合条件的数据，之后的每一行数据和非驱动表进行连接匹配操作，直到循环结束，最后合并结果、返回结果给用户。对于驱动表的字段它是可以直接排序的，然而对于非驱动表的字段排序需要通过循环查询的合并结果（临时表）进行排序，因此，order by o.order_time 时，就先产生了 using temporary（使用临时表）。

前面我们知道 order_payment 的数据量只有11w，那么理所当然的order_payment是驱动表。所以，为了避免 using temporary，就必须使用order作为驱动表，这个时候STRAIGHT_JOIN关键字就来了。强指定驱动表

explain select o.id, op.money 
from `order` o straight_join order_payment op on o.id = op.order_id 
where o.order_status = '4' order by o.order_time DESC;

分析结果，你会发现Using temporary 已经消失了。

最好解决掉Using filesort就很简单了，只需要给order_time增加索引就好了。

但是注意，在这个查询场景下，不能只对order_time一个字段增加索引，这样是避免不了 Using filesort的。因为innerDB索引的特性，B TREE 树的叶子节点存储的是索引列数据和主键ID。当只有一个order_time索引时，因为还有查询条件order_status在，所以无法使用到该索引

最佳索引方案

create index i_order_status_time on `order`(`order_status`,`order_time`)

这样B+ TREE树的叶子节点存储的会是索引列数据包含（order_status和order_time）和主键ID。该索引既能排序又能走查询。在上述条件查询中做到最佳性能。
当不能避免建立临时表时，要把表变得尽量小一点。

原来的sql: 用t表直接和a表join

select 
a.project,
a.`name` ,
a.phone_number ,
a.email ,
SUM(CASE WHEN document_type =9 THEN 1 ELSE 0 END) '下载量'
from download t 
INNER JOIN account a ON substring_index(t.user_id,'_CC',1)=a.email 
where a.type=1 and t.document_type =9 
GROUP BY t.school_id,t.user_id;

优化后：在内联操作前，借助子查询的方式，缩小连接表t的大小

select 
a.project,
a.name,
a.phone_number,
a.email,
tt.jc '下载量'
from 
(SELECT school_id,user_id,`subject`,SUM(CASE WHEN document_type =9 THEN 1 ELSE 0 END) AS jc FROM download t WHERE t.document_type =9 GROUP BY t.school_id,t.user_id) tt
INNER JOIN account a ON substring_index(tt.user_id,'_CC',1)=a.email 
where a.type=1;

单表优化

不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作
mysql 在使用**不等于(!=或者<>)**的时候无法使用索引会导致全表扫描
is not null 也无法使用索引，但是is null是可以使用索引的
字符串不加单引号索引失效
全值匹配我最爱
符合最左原则：不跳过索引中的列。
如果where条件中是OR关系，加索引不起作用
存储引擎不能使用索引中范围条件右边的列

关联查询

驱动表的数据一定要全查。

对于下面3个SQL

EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;

EXPLAIN SELECT * FROM class INNER JOIN book ON class.card = book.card;

如果我对Book创建了索引。

第一条。class作为驱动表要全查，book有索引，查的很快。 16*1

第二条。book作为驱动表要全查，没有被优化. 20*16

第三条。inner join会自动选择记录少的作为驱动表。 class为驱动表， 16*1

综上，肯定是把20行变为1行好，所以应该是大表创建索引，然后将其作为被驱动表(小表驱动大表)。

子查询优化

尽量不要使用not in 或者 not exists

尽量不要使用子查询

排序优化

以下三种情况不走索引：

无过滤，不索引
顺序错，不索引
方向反，不索引

create index idx_age_deptid_name on emp (age,deptid,name)
# 下面没有where或limit，属于无过滤，则不会走索引。
explain  select SQL_NO_CACHE * from emp order by age,deptid; 
# 下面这种会走索引
explain  select SQL_NO_CACHE * from emp order by age,deptid limit 10; 
explain  select * from emp where age=45 order by deptid;
# 顺序错，不索引
explain select * from emp where age=45 order by  deptid desc, name desc ;
# 方向反 不索引
explain select * from emp where age=45 order by  deptid asc, name desc ;

filesort算法

如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

MySQL在4.1版本之前是双路排序。从磁盘取排序字段，再buffer进行排序，再从磁盘取其他字段。

在MySQL4.1之后，出现了第二种改进的算法，就是单路排序。

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。

结论及引申出的问题：由于单路是后出的，总体而言好过双路。

但可能取出的数据的总大小超出了sort_buffer的容量，这样就会导致多次IO，就得不偿失了。

针对于filesort算法的优化策略：

Order by时select * 是一个大忌，只Query需要的字段，这点非常重要。在这里的影响是

当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size。

尝试提高 sort_buffer_size

不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整

尝试提高 max_length_for_sort_data

提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。1024-8192之间调整

分组优化

group by 使用索引的原则几乎跟order by一致，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

group by 先排序再分组，遵照索引建的最佳左前缀法则
当无法使用索引列，增大max_length_for_sort_data和sort_buffer_size参数的设置
where高于having, 能写在where限定的条件就不要写在having中了

只要对分组列创建索引即可

覆盖索引

简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

索引包含了查询正在查找的数据

sql实战

阿里开发手册：

【强制】在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区
分度决定索引长度。
说明：索引的长度与区分度是一对矛盾体，一般对字符串类型数据，长度为 20 的索引，区分度会高达 90%以上，可以使用 count(distinct left(列名，索引长度)) / count(*) 的区分度来确定。

时间日期处理

SELECT DATE_FORMAT(NOW() , '%Y年%m月%d日 %H时%i分%s秒');
SELECT NOW();
SELECT * FROM ucenter_member WHERE DATE(gmt_create) = '2019-01-02';
SELECT * FROM ucenter_member WHERE DATE_FORMAT(gmt_create , '%Y-%m-%d') = '2019-01-02';

行转列

SELECT * FROM t_score;

需求：行转列显示学生直观显示学生各科成绩

SELECT stuid ,
SUM(IF(SUBJECT = 'Java基础' , score , NULL)) 'Java基础',
SUM(IF(SUBJECT = 'mysql' , score , NULL)) 'mysql',
SUM(IF(SUBJECT = 'Javaweb' , score , NULL)) 'Javaweb',
SUM(IF(SUBJECT = 'ssm' , score , NULL)) 'ssm'
FROM t_score
GROUP BY stuid;

删除重复行

SELECT * FROM t_score ORDER BY stuid,SUBJECT;

需求：每个学生同一学科有多个成绩的，保留分数高的

DELETE FROM t_score WHERE id NOT IN(
    SELECT tmp.id FROM
        (SELECT id FROM t_score t1 JOIN (
            SELECT stuid , SUBJECT , MAX(score) m_score 
            FROM t_score 
            GROUP BY stuid , SUBJECT) t2
        ON t1.`stuid` = t2.stuid 
        AND t1.`subject` = t2.subject 
        AND t1.`score` = t2.m_score)tmp
);

窗口函数

mysql8的新特性。窗口函数感觉就是mysql自己已经写好的一些函数，可以供你调用。

按照功能划分，可以把MySQL支持的窗口函数分为如下几类:

# 序号函数：没有参数
row_number()/rank()/dense_rank()
# 分布函数：没有参数
percent_rank()：所在行数/总行数的百分比
cume_dist()：累积分布值
# 前后函数:参数有3个(expr：列名；n：偏移量；default_value：超出记录窗口的默认值)
lag(): 从当前行开始往前获取第N行，缺失则使用默认值
lead():从当前行开始往后获取第N行，缺失则使用默认值
# 头尾函数： 参数1个(expr：列名)
first_value():返回分组内截止当前行的第一个值
last_value():返回分组内截止当前行的最后一个值
# 其他函数: 
-- 参数有2个(expr：列名；n：偏移量)
nth_value():返回分组内截止当前行的第N行
-- 参数有1个(expr：列名；)
ntile():返回当前行在分组内的分桶号
/*
  语法结构：
    window_function ( expr ) OVER (
    PARTITION BY ...
    ORDER BY ...
    )

其中，window_function 是窗口函数的名称；
expr 是参数，有些函数不需要参数；OVER子句包含三个选项：
1、分区（PARTITION BY）
PARTITION BY选项用于将数据行拆分成多个分区（组），它的作用类似于GROUP BY分组。如果省略了 PARTITION BY，所有的数据作为一个组进行计算
2、排序（ORDER BY）
OVER 子句中的ORDER BY选项用于指定分区内的排序方式，与 ORDER BY 子句的作用类似

OVER后面括号中的内容可以抽取：
     WINDOW w AS ( 
         PARTITION BY ...
        ORDER BY ...
    )
*/

测试窗口函数的使用：

-- 1、查询员工信息和他部门年龄升序排列前一名员工的年龄
SELECT * , lead(age , 1,-1) over(
        PARTITION BY deptId
    ) last_emp_age 
FROM t_emp;

-- 2、查询每个员工在自己部门由大到小的年龄排名
select * ,
    row_number() over(PARTITION BY deptid ORDER BY age DESC) as row_num,
from t_emp;
# 或者
SELECT * ,
    row_number() over w AS row_num # w代表使用的
FROM t_emp
WINDOW w AS(PARTITION BY deptid ORDER BY age DESC);

接下来，我们来实现这么一个需求：查询员工表中每个部门的的年龄前两名

-- 查询每个员工所在部门的其他员工 如果年龄大于等于自己的小于等于两个，则保留自己的数据
SELECT * FROM t_emp t1
WHERE (SELECT COUNT(1) FROM t_emp t2 WHERE t2.`deptId`=t1.`deptId` AND t2.age>=t1.`age`)<=2
ORDER BY t1.`deptId` DESC, t1.age DESC;

上面的SQL是不是不好理解，接下来我们采用窗口函数看看如何优雅的实现同样的功能

select * from(
  select row_number() over(partition by deptid order by age desc) as row_num,
  id,name,sal,deptid
  from t_emp
) t where row_num <= 2

视图

视图是将一段查询sql封装为一个虚拟的表。这个虚拟表只保存了sql逻辑，不会保存任何查询结果。他和临时表是不一样的。

所以如果你创建了视图，我又新增了一条数据，然后我查询视图，我是可以查出来新数据的。

主要作用:

封装复杂sql语句，提高复用性
逻辑放在数据库上面，更新不需要发布程序，面对频繁的需求变更更灵活

常用场景:

共用查询结果
报表

语法:

创建

CREATE VIEW view_name AS SELECT column_name(s) FROM table_name  WHERE condition

使用

#查询
select * from view_name 
#更新
CREATE OR REPLACE VIEW view_name 
AS SELECT column_name(s) FROM table_name WHERE condition

#删除
DROP VIEW view_name;

MySQL锁

mysql支持读锁和写锁(都属于悲观锁)

读锁（共享锁，S锁(Shared)）：针对同一份数据，多个读操作可以同时进行而不会互相影响

写锁（排它锁，X锁(eXclusive)）：当前写操作没有完成前，它会阻断其他写锁和读锁

从对数据操作的粒度分，分为表锁和行锁

表锁

每次操作锁住整张表。开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；

一般用在整表数据迁移的场景。

-- 加锁
lock table 表1 read(write),表2 read(write);
-- 查看表锁
show open tables;
-- 删除本次session的表锁
unlock tables;

案例：一个session添加A表的写锁，另一个session添加B表的写锁，第一个session测试A、B表数据读取

行锁

每次操作锁住一行数据，锁定粒度最小，发生锁冲突的概率最低，并发能力强。但是开销大、加锁慢、可能会出现死锁。InnoDB存储引擎在执行查询语句SELECT时(非串行隔离级别)，不会加锁。但是update、insert、delete操作会加行锁。

查看行锁：

show status like 'innodb_row_lock%';

对各个状态量的说明如下：

Innodb_row_lock_current_waits: 当前正在等待锁定的数量
Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg: 每次等待所花平均时间(*)
Innodb_row_lock_time_max：从系统启动到现在等待最长的一次所花时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数(*)
Innodb_row_lock_waits: 等待总次数(*)

测试行锁：

Session_1执行：select * from account where id=1 for update;
Session_2执行：select * from account where id=2 for update;
Session_1执行：select * from account where id=2 for update;
Session_2执行：select * from account where id=1 for update;
-- 查看近期死锁日志信息：
show engine innodb status\G;

小结

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

合理设计索引，尽量缩小锁的范围

尽量控制事务大小，减少锁定资源量和时间长度，涉及事务加锁的sql尽量放在事务最后执行

尽可能低级别事务隔离

主从集群

mycat 无侵入，但是配置修改繁琐。逐渐被shardingsphere取代。详见文章

爱敲代码の鱼儿

http://19shuidiph.github.io/2024/07/02/shang-huo-gu-javaee-ji-zhu-zhi-mysql-gao-ji/

本博客所有文章除特別声明外，均采用 CC BY 4.0 许可协议。转载请注明来源爱敲代码の鱼儿 !

无标签

尚硅谷JavaEE技术之JUC&&JVM

2024-07-05 Java

尚硅谷JavaEE技术之分布式事务

2024-06-30 Java

尚硅谷JavaEE技术之MySQL高级

尚硅谷JavaEE技术之MySQL高级

SQL语法顺序

存储引擎

1. 各引擎简介

2. MyISAM和InnoDB的区别(重点)

MySQL Full Join的实现

数据页大小

索引的使用场景

索引优化

explain能干什么

单表优化

关联查询

子查询优化

排序优化

filesort算法

分组优化

覆盖索引

sql实战

时间日期处理

行转列

删除重复行

窗口函数

视图

MySQL锁

表锁

行锁

小结

主从集群

你的赏识是我前进的动力