目录

MySQL基础

基础语句

CREATE TABLE docpreview.file_info (
    id INTEGER(16) NOT NULL AUTO_INCREMENT COMMENT '用户ID',
    file_name VARCHAR(50) COMMENT '文件名',
    file_path VARCHAR(50) COMMENT '文件路径',
    file_type VARCHAR(10) COMMENT '文件类型，后缀',
    file_size INTEGER(16) COMMENT '文件大小，字节',
    create_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '上传时间',
    modify_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
    PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

启动命令

alter user 'root'@'%' identified with mysql_native_password by '新密码';修改本机连接时用的密码(可以与"远程连接"设置为相同的)

alter user 'root'@'localhost' identified with mysql_native_password by '新密码';

mysql数据库

高级查询

with toronto_ppl as (
    SELECT DISTINCT name
    FROM population
    WHERE country = "Canada"
      AND city = "Toronto"
), avg_female_salary as (
        SELECT AVG(salary) as avgSalary
        FROM salaries
        WHERE gender = "Female")
SELECT name, salary
FROM People
WHERE name in (SELECT DISTINCT FROM toronto_ppl)
  AND salary >= (SELECT avgSalary FROM avg_female_salary)

CREATE TEMPORARY FUNCTION get_seniority(tenure INT64) AS (
   CASE WHEN tenure < 1 THEN "analyst"
        WHEN tenure BETWEEN 1 and 3 THEN "associate"
        WHEN tenure BETWEEN 3 and 5 THEN "senior"
        WHEN tenure > 5 THEN "vp"
        ELSE "n/a"
   END
);
SELECT name, get_seniority(tenure) as seniority
FROM employees

执行计划

字段解释

  1. id列：select查询序列号，有几个select语句就有几个id，表示查询中执行select语句或操作表的顺序。
  1. select_type列：查询类型，例如普通查询、联合查询、子查询等
  1. table列：表示当前select查询执行时所涉及的表。当from子句中有子查询时，table列是格式<derived2>，表示当前查询依赖id=2的查询，于是先执行id=2的查询。当有union时，UNION RESULT的table列的值为<union1,2>，1和2表示参与union的select行的id
  2. partitions列：如果查询是基于分区表，则会显示查询要访问的分区表信息
  3. type列：该查询使用了哪种类型，是sql优化的重要指标
  1. possible_key列：表示查询可能使用哪些索引来查找。可能出现possible_key列有数据，而key列显示为NULL的情况，这是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。如果该列为NULL，表示没有使用相关的索引，需要进行SQL优化。
  2. key列：表示该查询执行时mysql实际采用的是哪个索引，如果没有使用索引则为NULL。如果要强制mysql使用或忽略possible_key列中的索引，在查询中使用：force index、ignore index即可。
  3. key_len列：表示mysql在索引中使用的字节数，通过这个值key算出具体使用了索引中的哪些列，在不损失精确性的情况下，长度越短越好。key_len列显示的值为索引字段的最大可能长度，并非实际使用的长度，即key_len是根据表定义计算而得，不是通过表内检索出的。索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。key_len大小的计算规则是：
  1. ref列：表示在key列记录的索引中，SQL执行时所用到的列或常量。常见的有：const，字段名等
  2. rows：表示mysql估计要读取并检测的行数，注意这不是结果集里的行数，数值越少越好
  3. filtered列：该列是一个百分比值，rows*filtered/100可用估算出将要和explain中前一个表进行连接的行数
  4. Extra列：比较重要的额外信息

分析执行计划

SQL优化

优化建议

  1. 使用合适的索引
  1. 优化join操作
  1. 减少返回的数据量
  1. 避免在查询中使用子查询
  1. 优化排序和分组操作
  1. 使用数据库缓存
  1. 优化数据库结构和设计
  1. 调整mysql配置

尽量不要做的事情

  1. 尽量不在 where 条件中使用 != 或 <>
  2. 尽量不用 is null 和 is not null
  3. 尽量不用 or
  4. 尽量不用 like，如果一定要用就用右模糊 name like 'xx%'
  5. 尽量不用 in 和 not in
  6. 尽量不在 = 左边计算或使用函数，如 where to_char(name)='xx'
  7. 尽量不用字符串作为主键
  8. 不要用 select *，明确选择需要的字段
  9. 不要用 group by having 过滤，而是先在 where 条件过滤后再 group by
  10. 尽量连接查询不要超过5个
  11. 索引不是越多越好，会降低插入更新速度，控制在5个以内
  12. 尽量避免使用游标
  13. 索引不适合建立在有大量重复数据的字段上，比如性别，排序字段应创建索引
  14. 尽量不存储图片、文件等大数据
  15. 单表数据尽量不超过500w，超过2000w速度明显变慢
  16. in 内数据不要太多，如果连续使用between
  17. 不要在 varchar 字段上使用数字查询，会导致索引失效
  18. 复合索引遵循最左前缀原则：复合索引(a,b,c)，a, ab, abc, ac都能用索引，其他情况索引失效
  19. 主键自带唯一索引，不需要在主键重复建立索引

尽量要做的事情

  1. 复合索引应该要第一个作为条件，否则不生效
  2. 能用 between 不要用 in
  3. exists 代替 in
  4. 数字型字段尽量用 number，别用varchar
  5. 查询条件尽量用上索引
  6. varchar代替char
  7. left join 左边放小表
  8. 尽量使用 limit 可以提高查询速度，避免全表扫描
  9. 批量插入提高性能
  10. 查询使用最频繁的列放在联合索引的最左侧
  11. 财务、银行相关的金额字段必须使用 decimal 类型，非精准浮点：float,double，精准浮点：decimal。Decimal类型为精准浮点数，在计算时不会丢失精度；占用空间由定义的宽度决定，每4个字节可以存储9位数字，并且小数点要占用一个字节；可用于存储比bigint更大的整型数据；
  12. 建议把BLOB或TEXT列分离到单独的扩展表中

exists

Show profile

Show profile是mysql提供的用来分析当前sql语句执行的资源消耗情况的工具。在执行完select语句后，执行show profile语句，只显示最近发送给服务器的sql语句，默认是已执行的15条，可以通过peofiling_history_size重新设置，最大值为100

select @@have_profiling：查看mysql版本是否支持show profile工具

show variables like 'profliling'：查看是否开启show profile工具

show profile使用及查询参数：

SHOW PROFILE [type [,type]...]
[FOR QUERY query_id]
[LIMIT row_count [OFFSET offset]]

type参数：

写SQL技巧

索引

基础语句

-- 创建一个普通索引（方式①）
create index 索引名 ON 表名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引（方式②）
alter table 表名 add index 索引名(列名(索引键长度)[ASC|DESC]);
-- 创建一个普通索引（方式③）
CREATE TABLE tableName(
  columnName1 INT(8) NOT NULL,
  columnName2 ....,
.....,
  index [索引名称](列名(长度))
);
-- 后续其他类型的索引都可以通过这三种方式创建


-- 创建一个唯一索引
create unique 索引名 ON 表名(列名(索引键长度)[ASC|DESC]);

-- 创建一个主键索引
alter table 表名 add primary key 索引名(列名);

-- 创建一个全文索引
create fulltext index 索引名ON表名(列名);

-- 创建一个前缀索引
create index 索引名ON表名(列名(索引键长度));

-- 创建一个空间索引
alter table表名add spatial key 索引名(列名);

-- 创建一个联合索引
create index 索引名 ON 表名(列名1(索引键长度),列名2,...列名n);

-- 查看一张表上的所有索引
show index from 表名;

-- 删除一张表上的某个索引
drop index 索引名 on 表名;

-- 强制指定一条SQL走某个索引查找数据
select * from 表名 force index(索引名)where.....;

-- 使用全文索引（自然搜索模式）
select * from 表名 where match(索引列) against('关键字');
-- 使用全文索引（布尔搜索模式）
select * from 表名 where match(索引列) against('布尔表达式'inboolean mode);
-- 使用全文索引（拓展搜索模式）
select * from 表名 where match(索引列) against('关键字'with query expansion);

-- 分析一条SQL是否命中了索引
explain select * from 表名 where 条件....;

索引分类

建立索引时需要遵守的原则

同时，除开上述一些建立索引的原则外，在建立索引时还需有些注意点：

索引失效

mysql事务

基本概念

-- 方式①：查询当前数据库的隔离级别
SELECT @@tx_isolation;
-- 方式②：查询当前数据库的隔离级别
show variables like '%tx_isolation%';

-- 设置隔离级别为RU级别（当前连接生效）
set transaction isolation level read uncommitted;
-- 设置隔离级别为RC级别（全局生效）
set global transaction isolation level read committed;
-- 设置隔离级别为RR级别（当前连接生效）
-- 这里和上述的那条命令作用相同，是第二种设置的方式
set tx_isolation = 'repeatable-read';
-- 设置隔离级别为最高的serializable级别（全局生效）
set global.tx_isolation = 'serializable';
ℹ️note

如果想要让设置的隔离级别在全局生效，一定要记得加上global关键字，否则生效范围是当前会话，也就是针对于当前数据库连接有效，在其他连接中依旧是原本的隔离级别。

MySQL的事务实现原理

❗️important

redo-log是一种WAL(Write-ahead logging)预写式日志，在数据发生更改之前会先记录日志，也就是在SQL执行前会先记录一条prepare状态的日志，然后再执行数据的写操作。

MySQL锁机制

链接

锁的划分

ℹ️note

不同的存储引擎的表锁在使用方式上也有些不同，比如InnoDB是一个支持多粒度锁的存储引擎，它的锁机制是基于聚簇索引实现的，当SQL执行时，如果能在聚簇索引命中数据，则加的是行锁，如无法命中聚簇索引的数据则加的是表锁

MySQL日志

undo-log回滚日志（InnoDB独有）

redo-log重做日志（InnoDB独有）

bin-log变更日志

其它日志

MERGE

MERGE INTO 目标表 [AS] 目标别名
USING 源表或子查询 [AS] 源别名
ON (匹配条件)
[WHEN MATCHED [AND 额外条件] THEN 操作]
[WHEN NOT MATCHED [BY TARGET] [AND 额外条件] THEN 操作]
[WHEN NOT MATCHED BY SOURCE [AND 额外条件] THEN 操作];

-- 示例（操作的始终是目标表）
MERGE INTO customer_accounts t
USING (
    SELECT customer_id, new_balance, is_active 
    FROM transaction_updates
    WHERE transaction_date > CURRENT_DATE - 7
) s
ON (t.customer_id = s.customer_id)
-- 当目标表和源数据匹配(customer_id相同)且源数据的new_balance与目标表的balance不相等时
-- 更新目标表的balance和last_updated字段
WHEN MATCHED AND s.new_balance != t.balance THEN
    UPDATE SET 
        t.balance = s.new_balance,
        t.last_updated = SYSDATE
-- 当目标表和源数据匹配且源数据的is_active标志为0时
-- 从目标表中删除该记录
WHEN MATCHED AND s.is_active = 0 THEN
    DELETE
-- 当源数据中的记录在目标表中找不到匹配时(即新客户)
-- 向目标表插入新记录
WHEN NOT MATCHED THEN
    INSERT (customer_id, balance, open_date)
    VALUES (s.customer_id, s.new_balance, SYSDATE)
-- 当目标表中的记录在源数据中找不到匹配且目标表记录的最后更新时间超过365天
-- 从目标表中删除这些记录
WHEN NOT MATCHED BY SOURCE AND t.last_updated < SYSDATE - 365 THEN
    DELETE;
💡tip
  • 比如现在有目标表记录1,2，源表记录1,3，
  • 那么两表中的1是WHEN MATCHED，表示两表中都有，执行更新
  • 2是WHEN NOT MATCHED BY SOURCE表示目标中有，源表中没有，执行删除
  • 3是WHEN NOT MATCHED BY TARGET表示目标中没有，源表中有，执行插入
目标表(Target)源表(Source)匹配类型执行操作
记录1记录1WHEN MATCHEDUPDATE
记录2-WHEN NOT MATCHED BY SOURCEDELETE
-记录3WHEN NOT MATCHED [BY TARGET]INSERT
  • WHEN NOT MATCHED BY TARGET：意味着目标中匹配不上，目标中没有，源表中有
  • WHEN NOT MATCHED BY SOURCE：意味着是源表中匹配不上，目标中有，源表没有
-- sql server
MERGE INTO target_table AS target
USING source_table AS source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.value = source.value
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (source.id, source.name, source.value)
WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

-- oracle
MERGE INTO target_table target
USING (SELECT id, name, value FROM source_table) source
ON (target.id = source.id)
WHEN MATCHED THEN
    UPDATE SET target.name = source.name, target.value = source.value
WHEN NOT MATCHED THEN
    INSERT (id, name, value) VALUES (source.id, source.name, source.value);
    
-- pg
INSERT INTO target_table (id, name, value)
SELECT id, name, value FROM source_table
ON CONFLICT (id) DO UPDATE
SET name = EXCLUDED.name, value = EXCLUDED.value;

-- mysql
-- 方式1: INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO target_table (id, name, value)
VALUES (1, 'test', 100)
ON DUPLICATE KEY UPDATE
name = VALUES(name), value = VALUES(value);

-- 方式2: 使用REPLACE语句(实际是先DELETE后INSERT)
REPLACE INTO target_table (id, name, value)
VALUES (1, 'test', 100);

-- sqlite
INSERT INTO target_table (id, name, value)
VALUES (1, 'test', 100)
ON CONFLICT(id) DO UPDATE
SET name = excluded.name, value = excluded.value;

窗口函数

常见错误

过滤条件写在连接条件中

select a left join b on a.id = b.id and b.age = 18;
select a left join b on a.id = b.id where b.age = 18;
💡tip
  • ON 条件中的过滤：在 JOIN 时过滤，不影响左表记录数
  • WHERE 条件中的过滤：在 JOIN 后过滤，会减少结果集记录数

not in 中包含 null

SELECT * FROM table_a 
WHERE id NOT IN (
    SELECT a_id FROM table_b 
    WHERE a_id IS NOT NULL  -- 关键！
);

-- NOT EXISTS 正确处理 NULL
SELECT * FROM table_a a
WHERE NOT EXISTS (
    SELECT 1 FROM table_b b 
    WHERE b.a_id = a.id
);

-- 使用 LEFT JOIN 和 NULL 检查
SELECT a.* 
FROM table_a a
LEFT JOIN table_b b ON a.id = b.a_id
WHERE b.a_id IS NULL;