Mysql
一条SQL语句怎么执行
通过连接器登录数据库
查询缓冲(8.0后已删除)
进入分析器进行词法分析和语法分析
通过优化器选择最优方案
通过执行器执行命令
三大范式
1NF:字段不可分
2NF:有主键
3NF:非主键字段不能相互依赖
查询
--基本查询
SELECT * FROM <TABLE> WHERE <CONDITION> NOT/AND/OR <CONDITION1>;
--投影查询
SELECT KEY1,KEY2,KEY3 KEY3_NEWNAME FROM <TABLE>;
--正序查询
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION>;
--倒序查询
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION> DESC;
--补充倒序查询(先查条件1再查条件2)
SELECT KEY1,KEY2 FROM <TABLE> ORDER BY <CONDITION1> DESC,<CONDITION2>;
--分页查询,每页<PAGES_SIZE>条记录,PAGEINDEX从1开始,是页数
SELECT KEY FROM <TABLE> ORDER BY <CONDITION> LIMIT <PAGES_SIZE> OFFSET <PAGES_SIZE*(PAGEINDEX-1)>;
--聚合查询
SELECT COUNT/SUM/AVG/MAX/MIN(KEY) KEY_NEWNAME FROM <TABLE> WHERE <CONDITION>;
--分组聚合查询
SELECT COUNT/SUM/AVG/MAX/MIN(KEY) KEY_NEWNAME FROM <TABLE> WHERE <CONDITION> GROUP BY KEY;
--多表查询(笛卡尔查询),返回记录数为乘积,有风险
SELECT * FROM <TABLE1>,<TABLE2>;
--多表查询,使用别名投影
SELECT
t1.<KEY1> KEY1_NEWNAMEFROM
t1.<KEY2> KEY2_NEWNAMEFROM
t2.<KEY1> KEY1_NEWNAMEFROM
FROM <TABLE1> t1,<TABLE2> t2
WHERE <CONDITION>;
--内连接查询
SELECT KEY1, KEY2, KEY3
FROM <TABLE1> t1
INNER JOIN <TABLE2> t2
ON t1.KEY1 = t2.KEY4;
--外连接查询
SELECT KEY1, KEY2, KEY3
FROM <TABLE1> t1
LEFT/RIGHT/FULL OUTER JOIN <TABLE2> t2
ON t1.KEY1 = t2.KEY4;
假设查询语句是:
SELECT KEY1, KEY2, KEY3
FROM <TABLEA> A
LEFT/RIGHT/FULL OUTER JOIN <TABLEB> B
ON A.KEY1 = B.KEY4;

修改数据
--插入多条
INSERT INTO <TABLE> (KEY1, KEY2) VALUES (VALUE1, VALUE2),(VALUE3, VALUE4);
--更新多条数据(修改where为一个范围即可)
--SET后可以为一个计算表达式
UPDATE <TABLE> SET KEY1=VALUE1, KEY2=VALUE2 WHERE <CONDITION>;
--删除多条记录(修改where为一个范围即可)
DELETE FROM <TABLE> WHERE <CONDITION>;
MYSQL操作
# 列出所有库
SHOW DATABASES;
# 建库
CREATE DATABASE <TABLE>;
# 删库
DROP DATABASE <TABLE>;
# 列出所有表
SHOW TABLES;
# 查看表结构
DESC <TABLE>;
# 查看创建表的SQL语句
SHOW CREATE TABLE <TABLE>;
# 建表
CREATE TABLE <TABLE>;
# 删表
DROP TABLE <TABLE>;
# 修改表——新增列,类型为VARCHAR(10)
ALTER TABLE <TABLE> ADD COLUMN <COLNAME> VARCHAR(10) NOT NULL;
# 修改表——删除列
# 修改表——改名和改类型为VARCHAR(20)
ALTER TABLE <TABLE> CHANGE COLUMN <COLNAME> <COLNAME_NEWNAME> VARCHAR(20) NOT NULL
# 快速建表
CREATE TABLE <TABLE> (
KEY1 BIGINT NOT NULL AUTO_INCREMENT,#AUTO_INCREMENT即定义自增主键
KEY2 BIGINT NOT NULL,
KEY3 DOUBLE NOT NULL,
PRIMARY KEY (id)
);
索引类型
通过UNIQUE关键字添加一个索引
ADD UNIQUE INDEX uni_name (name);
聚集索引和非聚集索引的区别
- 聚集索引(主键索引):在数据库里面,所有行数都会按照主键索引进行排序。
- 非聚集索引:就是给普通字段加上索引。
- 联合索引:就是好几个字段组成的索引,称为联合索引。
联合索引最左前缀匹配
例如:key idx_age_name_sex('age','name','sex')
如果where内有age,无论age在and连接的哪个位置,都会使用索引;如果age是个范围,就只有age使用索引
如果没有age就不使用;如果有!=也不使用索引
索引失效
1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时(数据少);
MySQL 索引为什么采用B+树?
B+树能显著减少IO次数,提高效率
B+树的查询效率更加稳定,因为数据放在叶子节点
B+树能提高范围查询的效率,因为叶子节点指向下一个叶子节点
一条sql执行的慢的原因
偶尔慢:
数据库在刷新脏页
等待锁的时候
一直慢:
没有用到索引
数据库选错索引
事务
把多条语句作为一个整体进行操作的功能,被称为数据库事务
ACID四大属性
- A:Atomic,原子性,将所有SQL作为原子工作单元执行,要么全部执行,要么全部不执行;
- C:Consistent,一致性,事务完成后,所有数据的状态都是一致的,即A账户只要减去了100,B账户则必定加上了100;
- I:Isolation,隔离性,如果有多个事务并发执行,每个事务作出的修改必须与其他事务隔离;
- D:Duration,持久性,即事务完成后,对数据库数据的修改被持久化存储。
使用BEGIN开启一个事务,使用COMMIT提交事务
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
隔离级别
对于两个并发执行的事务,如果涉及到操作同一条记录的时候,可能会发生问题。因为并发操作会带来数据的不一致性,包括脏读、不可重复读、幻读等。数据库系统提供了隔离级别来让有针对性地选择事务的隔离级别,避免数据不一致的问题。
SQL标准定义了4种隔离级别,分别对应可能出现的数据不一致的情况:
| Isolation Level | 脏读(Dirty Read) | 不可重复读(Non Repeatable Read) | 幻读(Phantom Read) |
|---|---|---|---|
| Read Uncommitted | Yes | Yes | Yes |
| Read Committed | - | Yes | Yes |
| Repeatable Read | - | - | Yes |
| Serializable | - | - | - |
Read Uncommitted:读到了另一个事务修改但未提交的数据,出现脏读

Read Committed:多次读数据的时候读到了另一个事务修改后的数据,出现数据不一致的情况,也就是不可重复读

Repeatable Read:幻读是指,在一个事务中,第一次查询某条记录,发现没有,但是,当试图更新这条不存在的记录时,竟然能成功,并且,再次读取同一条记录,它就神奇地出现了。

Serializable是最严格的隔离级别,所有事务依次执行,不会出现脏读、不可重复读、幻读。但这样是串行执行,效率会受影响。
MySQL中,默认会使用Repeatable Read的隔离级别。
锁
死锁:
悲观锁是利用了数据库锁的机制实现的,具有独占性和排他性,分为读锁和写锁,即都能读但只有一个能获取到写的权限
乐观锁通过CAS实现,多个线程尝试使用CAS同时更新变量时,先根据版本进行冲突检测,只有一个能成功,其他的都会失败并获取失败信息且可以再次尝试
MySQL实现了什么锁?怎么实现的
全局锁
表级锁
表锁
元数据锁
行锁
innodb和myisam对比及索引原理区别
| innodb | myisam |
|---|---|
| 具备事务 | 没有事务 |
| 支持事务和行级锁 | 只支持表级锁 |
| 两个文件:【表名(表定义).frm、表名(日志文件.)ibd】 | 三个文件:【表名(表定义).frm、表名(数据文件).MYD、表名(索引文件).MYI】 |
| 索引组织表【聚簇索引】 | 堆组织表【非聚簇索引】 |
| 与事务隔离级别相关 | 读写互相阻塞 |
| 速度相对慢 | 速度相对快 |
大表优化
字段优化
索引优化
查询SQL优化
升级硬件
读写分离
使用缓存
垂直拆分
水平拆分
将日志、监控、统计类、弱结构化数据迁移到NoSQL
本文详细介绍了MySQL数据库的基本操作,包括SQL语句的执行流程、数据的增删改查、事务处理和隔离级别。此外,还探讨了索引的类型、优化策略以及B+树作为索引结构的优势。同时,分析了SQL执行慢的原因,并提出了性能优化的多种方法,如索引使用、事务管理和表结构优化。

被折叠的 条评论
为什么被折叠?



