数据库

最新推荐文章于 2022-03-06 14:34:18 发布

_Yiz

最新推荐文章于 2022-03-06 14:34:18 发布

阅读量217

点赞数

本文链接：https://blog.csdn.net/zy56865/article/details/107405398

版权

控制命令（数据库名a 表名aa 列名a1）
MYSQL -u帐号 -p密码 -P端口 -hIP

CREATE DATABASE a;

ALTER DATABASE a; 修改

DROP DATABASE a; 删除

USE a；选择使用一个数据库

CREATE TABLE IF NOT EXISTS aa(
a1 SMALLINT,
…
) 创建数据表

INSERT 表名 (项目名1，项目名2，…) VALUES（‘asdf’，3112，…）

SELECT 项目名 FROM 表名

AUTO_INCREMENT – 只能是主键默认从1开始

DELETE FROM aa WHERE a1=1；删除一条记录

修改数据表：添加、删除列，修改列的约束
ALTER TABLE aa ADD a2 SMALLINT AFTER a1；单列不用括号
ALTER TABLE aa ADD (a2 SMALLINT, a3 SMALLINT, …) 多列带括号，且只能放最后
ALTER TABLE aa DROP a2;
ALTER TABLE aa ADD UNIQUE KEY(a1); 添加约束
ALTER TABLE aa ALTER a1 SET DEFAULT 15; 添加默认约束
ALTER TABLE aa ALTER a1 DROP DEFAULT; 删除默认约束
ALTER TABLE aa DROP PRIMARY KEY; 删除主键约束
ALTER TABLE aa DROP KEY a1;删除a1的唯一约束
ALTER TABLE aa DROP FOREIGN KEY fk_symbol; 删除外键约束
ALTER TABLE aa MODIFY a1 TINYINT UNSIGNED NOT NULL FIRST;修改位置和数据类型
ALTER TABLE aa CHANGE a1 a1_1 INT FIRST; 功能大于MODIFY，修改名称以及位置、数据类型
ALTER TABLE aa RENAME aa_1; || RENAME TABLE aa TO aa_1; 修改表的名字

对记录的修改：
插入：
- INSERT aa[(a1,a2,…)] VALUES({15| DEFAULT},…),(…)
- INSERT aa SET a1 = {15| DEFAULT}, a2 = … 可以使用子查询
- INSERT aa[(a1,a2,…)] SELECT …

更新：
- UPDATE aa SET a1 = a1 + 5;
- UPDATA aa SET a1 = a1 + 5 WHERE a2 % 2 = 0;

删除：
- DELETE FROM aa WHERE a1 = 1;

SELECT查询：
- 别名：SELECT a1 AS a1_1 FROM aa;
- 查询结果分组GROUP BY: SELECT a1 FROM aa GROUP BY a1 [ASC| DESC]; 升序降序
- 分组条件HAVING: SELECT a1,a2 FROM aa GROUP BY a1 HAVING a2 > 5;
- 分组结果排序ORDER BY: SELECT * FROM aa ORDER BY a1, a2 DESC; 升序为ASC
- 限制结果返回的数量LIMIT：SELECT * FROM aa LIMIT 2; 返回前两条记录（LIMIT 2, 3: 从2开始3条记录，从0计数）
- 选择插入：INSERT aa(a1) SELECT b1 FROM bb WHERE b2 > 9;
- DISTINCT去重：SELECT DISTINCT name FROM table;
- IF语句：IF(EX1,EX2,EX3) 如果EX1为TURE，返回EX2，否则返回EX3

查询条件：

查询条件	谓词
比较	=, >, <, >=, <=, !=, <>, !>, !<,
比较范围	BETWEEN AND, NOT BETWEEN AND（WHERE age BETWEEN 23 AND 25）
确定集合	IN, NOT IN(WHERE dept IN (‘CS’, ‘MA’,‘IS’))
字符匹配	LIKE, NOT LIKE(%: 任意长度的字符串，_: 任意单个字符) 后面加ESCPAE’\'转义
空值	IS NULL
逻辑运算	AND, OR, NOT

约束：

NOT NULL非空
PRIMARY KEY 主键
UNIQUE KEY 唯一性（可空）
DEFAULT 默认值
FOREIGN KEY 外键约束: FOREIGN KEY (a1) REFERENCES bb(b1)
- 必须先有父表记录，再有子表记录，外键约束参数 ON DELETE
- CASCADE 跟随父表删除或更新
- SET NULL 父表删除或更新时，设置空
- RESTRICT 父表不能更新或删除
- NO ACTION 同上

整形：

数据类型	存储范围	字节数
TINYINT	-128到127	1
SMALLINT	-32768到32767	2
MEDIUMINT	$2^{15}$ 到 $2^{15}-1$	3
INT	$2^{31}$ 到 $2^{31}-1$	4
BITINT	$2^{63}$ 到 $2^{63}-1$	8

浮点型：

数据类型	存储范围
FLOAT[(M,D)]	$10^{38}$ 级，M为数字总位数，D是小数点后位数，7位小数
DOUBLE[(M,D)]	$10^{308}$ 级，M为数字总位数，D是小数点后位数

日期类型：

类型	存储需求
YEAR	1
TIME	3
DATE	3
DATIME	8
TIMETAMP	4

字符型：

类型	存储需求
CHAR(M)	255字节
VARCHAR(M)	65535
TINYTEXT	$2^{8}+1$
TEXT	$2^{16}+2$
MEDIUMTEXT	$2^{24}+3$
LONGTEXT	$2^{32}+4$
ENUM(‘value1’,‘value2’, …)	1或2字节，取决于枚举值个数（最多65535个值）
SET(‘value1’,‘value2’, …)	1、2、3、4或8字节，取决于成员个数（最多64个值）

一个列的约束：列级约束
多个列的约束：表级约束
列级约束可以在定义时声明，也可以在定义后声明
表级约束只能在列定义后声明

内分类INNER JOIN ，两表交集
左外分类LEFT JOIN，左表记录右表空
右外分类RIGHT JOIN，右表记录左表空

聚集函数：不能用于WHERE，只能用HAVING

COUNT()
SUM()
AVG()
MAX()
MIN()

HAVING与WHERE区别：作用对象不同，WHERE用户基表或视图，从中选择满足条件的元组，HAVING用于组，选择满足条件的组

集合查询：
UNION / UNION ALL 并
INTERSECT
EXCEPT

插入：
INSERT
INTO 表名 ()
VALUES()

修改：
UPDATE 表名
SET 列名=表达式，列名=表达式…
WHERE ；

删除：
DELETE FROM 表名
WHERE ;

视图：
定义：
CREATE VIEW
视图名（列名，列名）
AS 子查询
WITH CHECK OPTION

删除视图：
DROP VIEW 视图名 CASCADE （删除由他导出的视图）

视图更新：更新，插入，删除相同

执行顺序：
在这里插入图片描述

安全：

预防性措施	监控措施
用户身份鉴别、自主存取控制（DAC）、强制存取控制（MAC）、视图	审计

用户身份鉴别：账户/密码
自主存取控制（DAC）：赋予/回收某些权限（对数据操作）
强制存取控制（MAC）：读比自己低，写比自己高

授予权限：
GRANT 权限，权限
ON 对象类型对象名，对象类型，对象名
TO 用户，用户（）P
WITH GRANT OPTION （可以再转授权限）不允许循环授权

回收权限：
REVOKE 权限，权限(属性)
ON 对象类型对象名，TALBE student
FROM 用户，用户[CASCADE| RESTRICT]（传播的权限回收| 限制传播的权限回收后才能回收）

角色（批量权限授权、回收）
创建：
CREATE ROLE 角色名

授权角色：
GRANT 权限，权限
ON 对象类型对象名
TO 角色，角色

角色授权给用户：
GRANT 角色1，角色2
TO 角色3，用户1
WITH ADMIN OPTION （可转授）

回收：
REVOKE 权限，权限
ON 对象类型对象名
FROM 角色，角色

主体敏感度标记为许可证级别
客体的敏感度标记为密级
主体可以读不比自己高的客体，写不比自己低的客体，防止密级降低

视图来控制

审计：
审计日志：服务器事件、系统权限、语句事件、模式对象事件
AUDIT ALTER, UPDATE
ON student
对student表开启修改和更新审计功能

NOAUDIT ALTER, UPDATE
ON student

数据加密：存储加密，传输加密

推理控制、隐蔽信道

完整性：

实体完整性：主键存在

参照完整性：级联删除、拒绝执行、设置为空值

在设置外键时，加参照完整性约束：
FOREIGN KEY(Sno) REFFERENCE Student(Sno)
ON DELETE NO ACTION
ON UPDATE CASCADE
ON DELETE SET-NULL

用户定义的完整性：
NOT NULL
UNIQUE
CHECK检查列值是否满足一个条件表达式
属性约束：Grade SMALLINT CHECK(Grade>=0 AND Grade<=100)
元组级约束：CHECK(Ssex=‘女’ OR Sname NOT LIKE ‘Ms.%’)

完整性约束命名字句（方便引用、修改、删除约束）
定义：
CONSTRAINT 完整性约束条件名完整性约束条件（NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK）
删除、修改（删除后添加）：
ALTER TABLE
DROP CONSTRAINT C4

ALTER TABLE
ADD CONSTRAINT C1 CHECK(Sno BETWEEN 9000 AND 9999)

断言：可以定义多个表、聚集操作的复杂约束
CREATE ASSERTION 断言名 CHECK字句
CREATE ASSERTION asse_sc
CHECK(60 >= ALL(SELECT count(*)
FROM SC
GROUP BY cno));

删除：
DROP ASSERTION 断言名；

完整性约束为的是单个元组操作
断言比较复杂的情况

触发器：由事件驱动的特殊过程
定义：
CREATE TRIGGER 触发器名
{BEFORE | AGTER} 触发事件 ON 表名
REFERENCING NEW|OLD ROW|TABLE AS 变量（引用变量）
FOR EACH {ROW | STATEMENT}
[WHEN 触发条件] 触发动作体

ROW: 行级触发器，每改一行，触发一次
STATEMENT: 语句级触发器，执行完一条语句后，触发一次
NEW和OLD决定引用新值还是旧值，只有行级可以引用ROW，语句级不能引用ROW,可以引用TABLE

CREATE TRIGGER SC_T
AFTER UPDATE OF Grade ON SC
REFERENCING
OLD ROW AS OldTuple,
NEW ROW AS NewTuple
FOR EACH ROW
WHEN(NewTuple.Grade >= 1.1*OldTuple)
INSERT INTO SC_U(Sno,Cno,OldGrade,NewGrade)
VALUES(OldTuple.Sno,OldTuple.Cno,OldTuple.Grade,NewTuple.Grade)

CREATE TRIGGER Student_Count
AFTER INSERT ON Student
REFERENCING
NEW TABLE Delta \\新增的那一部分
FOR EACH STATEMENT
INSERT INTO StudentInsertLog(Numbers)
SELECT COUNT(*) FROM Delta

CREAT TRIGGER Insert_Or_Update_Sal
BEFORE INSERT OR UPDATE ON Teacher
FOR EACH ROW
BEGIN
IF(new.job=‘教授’) AND (new.Sal < 4000)
THEN new.Sal := 4000;
END IF;
END;

删除触发器：
DROP TRIGGER 触发器名 ON 表名

范式

第一范式1NF：所有数据项，都不可分
第二范式2NF：每一个非主属性都完全函数依赖于R的码（解决非主属性部分依赖）
第三范式3NF：不存在非主属性传递依赖于码（解决非主属性传递函数依赖）
BC范式BCNF：每一个决定因素都包含码（解决主属性部分依赖和传递依赖）

需求分析

概念模型

ER图
矩形：实体
椭圆：属性
菱形：联系
拓展：ISA联系：
三角子类指向父类（中间有X不相交）
双线连接完全特化–父类为纯虚函数
基数约束：min…max，min=0 – 非强制参与约束
PART-OF联系：整体破坏，部分实体不能存在–独占联系

UML：统一建模语言
概念结构设计
1、能属性就属性，属性不可分，不能与其他实体有联系
2、E-R图的集成：
子E-R图
解决冲突：属性冲突、命名冲突、结构冲突

逻辑结构设计

实体型转换：一个实体型转换为一个关系模式
联系的转换：
1：1联系：A）将联系转换为一个独立关系模式，B）与任意一端合并
1：n联系：A）将联系转换为一个独立关系模式，B）与n端合并
n：m联系：将联系转换为一个独立关系模式
相同码合并
数据模型优化：
1、确定数据依赖
2、消除冗余
3、分析、范式
设计用户子模式：视图机制

物理结构设计

创建索引：经常查询、聚集函数的参数、连接操作的连接条件
CREATE [UNIQUE] INDEX 索引名字 ON 表名 [USING 索引方法]（列名，列名）
B-tree, Hash, R-tree, Bitmap
各种索引方法：
B+树：
多分平衡树，存取效率高
随机查找，顺序查找
增删改操作可以保持平衡树
Hash
聚簇：把一类实体存放在连续物理块中，提高按这个属性查询速度
创建：CREATE CLUSTER 聚簇名（聚簇码）SIZE（大小）
一个基本表只能一个聚簇
只能提高特定性能
建立和维护开销大
适用：很少增删、很少变长修改

数据库实施和维护

ETL：数据抽取、转换、载入

数据库编程

高级语言嵌入式SQL

建立数据库连接
EXEC SQL CONNECT TO target [AS connection-name]
[USER user-name];
target：数据库服务器名字
定义主变量与数据通讯区
EXEC SQL BEGIN DECLARE SECTION;
char Deptname[20];
int HSage;
int NEWAGE;
EXEC SQL END DECLARE SECTION;
long SQLCODE;
EXEC SQL INCLUDE SQLCA; //定义SQL通讯区
查询
EXEC SQL SELECT Sdept, Sage
INTO:Hdept, :Hage //保存查询结果
FROM Student
WHERE Sno=:givensno;
前面有":"为主变量，主语言变量
修改
EXEC SQL UPDATE SC
SET Sgrade=:newgrade
WHERE Sno=:givensno AND Cno=‘1’;
关闭连接
EXEC SQL DISCONNECT [connection]

操作成功后，sqlca.sqlcode==0

指示变量：主语言中的负值来代表数据库中的空
EXEC SQL INSERT
INTO SC(Sno, Cno, Grade)
VALUES(:HSno,:HCno,:HGrade :gradeid);
//如果gradeid是负数，则插入一个空值

EXEC SQL SELECT Sno, Cno, Grade
INTO :HSno, :HCno, :HGrade :gradeid
FROM SC
WHERE Sno=:givensno AND Cno=:givencno
//如果gradeid<0，则代表该学生成绩为空值
游标：数据库设的一个数据缓冲区，存放SQL语句执行结果
必须使用游标：查询结果为多条记录、CURRENT形式的UPDATE、DELETE语句
1、查询结果为多条记录：
a)申明游标
EXEC SQL DECLARE 游标名 CURSOR
FOR SELECT语句
b)打开游标
EXEC SQL OPEN 游标名
c)推进游标指针并取当前记录
EXEC SQL FETCH 游标名
INTO 主变量[指示变量]，主变量
d)关闭游标
EXEC SQL CLOSE 游标名
2、对查询结果进行交互式修改、删除（CURRENT形式的UPDATE、DELETE语句）
EXEC SQL UPDATE Student
SET Sage = :newage
WHERE CURRENT OF SX;
//对当前游标指向的学生年龄进行更新
…
EXEC SQL CLOSE SX; //关闭游标SX
EXEC SQL COMMIT WORK; //提交更新结果
动态SQL：主变量存放SQL语句
EXEC SQL BEGIN DECLARE SECTION;
const char* stmt=“CREATE TABLE test(a int);”;
EXEC SQL END DECLARE SECTION;
…
EXEC SQL EXECUTE IMMEDIATE :stmt; //执行动态SQL语句

使用动态参数：
在动态语句中用“？”来声明
EXEC SQL BEGIN DECLARE SECTION;
const char* stmt=“INSERT INTO test VALUES(?)”
EXEC SQL END DECLARE SECTION;
…
EXEC SQL PREPARE mystmt FROM :stmt; //准备语句
…
EXEC SQL EXECUTE mystmt USING 100; //执行语句
EXEC SQL EXECUTE mystmt USING 200;

过程化SQL（PL/SQL）

基本结构是块

ODBC编程

事务

不可分割

定义事务：
显示定义：
BEGIN TRANSACTION
SQL
SQL
…
COMMIT //提交事务
ROLLBACK //回滚到开始
如果不显示定义，那么一条SQL就是一个事务
四个特性ACID
原子性Atomicity：全做
一致性Consistency：从一个一致性状态到另一个一致性状态（全部成功）
隔离性Isolation：一个事务的执行不能被其他事务干扰
持续性Durability：执行完后，永久有效

故障

事务故障
系统故障
介质故障
计算机病毒

并发控制

并发带来问题：
丢失修改：后者覆盖前者
不可重复读：两次读取结果不同，多了或少了一些记录
脏读：t1修改，t2读，t1回滚，导致t2读的值错误
幻读：幻读，并不是说两次读取获取的结果集不同，幻读侧重的方面是某一次的 select 操作得到的结果所表征的数据状态无法支撑后续的业务操作。更为具体一些：select 某记录是否存在，不存在，准备插入此记录，但执行 insert 时发现此记录已存在，无法插入，此时就发生了幻读。
主要技术
封锁（Locking）
时间戳(Timestamp)
乐观控制法
多版本并发控制（MVCC）

封锁技术

排它锁（X锁）（写锁）：不能再加其他锁
共享锁（S锁）（读锁）：别人可以继续加S锁，但不能加X锁

封锁协议

一级封锁协议：写前加X锁，事务完解锁（防丢失修改）（长写锁+无读锁）
二级封锁协议：写前加X锁，读前加S锁，读完解锁（防脏数据）（长写锁+短读锁）
三级封锁协议：写前加X锁，事务读前加S锁，事务结束后解S锁（防不可重复读）（长写锁+长读锁）

活锁和死锁

活锁：发生饥饿，FCFS可以解决
死锁
1、防止死锁发生：
a）一次封锁法，一次性将所有需要的数据加锁；（导致并发度降低，难确定对象）
b）顺序封锁法，规定一个封锁顺序；（维护成本高，难实现）
2、死锁诊断
a）超时法，一个事务超过规定时间，判定为发生死锁（可能误判，不能及时发现）
b）等待图法，有向图，指向等待事务释放锁，有回路表示有死锁
解除死锁：撤销一个处理代价最小的锁

并发调度可串行性

可串行化：调度方法与串行操作结果相同
冲突可串行化：冲突操作次序不变，交换两个事务不冲突操作次序后，如果得到串行则是一个冲突可串行化的调度（写写，读写是冲突）

两段锁协议

事务分为两个阶段：第一阶段获得锁（扩展阶段），第二阶段释放锁（收缩阶段），一定是一个可串行化的调度

封锁粒度

多粒度封锁：加锁时查看上面，查看下面，效率低
意向锁：先对上层加意向锁，再对该结点加基本锁；加锁时只看上面不用看下面
意向共享锁（IS锁）：后代有S锁
意向排它锁（IX锁）：后代有X锁
共享意向排它锁（SIX锁）：当前结点有S锁，后代有X锁，从上而下加锁，从下而上解锁

MYSQL:

IFNULL() 函数用于判断第一个表达式是否为 NULL，如果为 NULL 则返回第二个参数的值，如果不为 NULL 则返回第一个参数的值。
CHANGE用来修改【字段名】以及【类型】，MODIFY用来修改【字段类型】，ALTER column…SET用来修改【字段数据】（缺省值）
字符函数
- length(…) 【字节】个数（只有length是字节其他都是字符）
- concat(…, …) 拼接
- upper, lower 大小写
- substr, substring 截取子串（索引从1开始计数）用法：
  - 1、（字符串，开始位）–从开始位到结尾
  - 2、（字符串，开始位，截取长度）
- instr(字符串，子串) 返回子串在字符串中的起始索引，否则返回0
- trim(字符串) 去掉前后空格，trim(子串 FROM 字符串)去掉字符串前后的子串
- LPAD(字符串，长度，字符) 用指定字符左填充到指定长度，RPAD
- REPLACE(字符串1，字符串2，字符串3) 在字符串1中，用字符串3替换出现的字符串2
数学函数
- ROUND 四舍五入，ROUND(1.567,2)保留两位小数
- CEIL 向上取整
- FLOOR 向下取整
- TRUNCATE 截断 TRUNCATE(1.567,1) 丢弃小数点后第1位后的小数
- MOD 取余 == %
- RAND() 返回0~1之间的随机数
日期函数
- NOW() 系统日期+时间
- CURDATE() 系统日期
- CURTIME() 系统时间
- 获取指定的部分：年、月、日、时、分、秒
  - YEAR(NOW())
  - MONTH(NOW()) \ MONTHNAME(NOW())
- STR_TO_DATE(‘2-3-1995’,’%d-%c-%Y’) 指定格式解析字符串代表的日期
- DATE_FORMAT(‘2018-6-6’,’%Y年%c月%d日’)将日期转换为指定格式的字符串
其他函数
- VERSION(), DATABASE(), USER();
流程控制函数
- IF(A,B,C) A为真，返回B，否则返回C
- CASE函数：
  - switch case用法
    CASE 要判断的字段或表达式
    WHEN 常量1 THEN 要显示的值或（语句；）
    WHEN 常量2 THEN 要显示的值或（语句；）
    …
    ELSE 默认显示的值或（语句；）
    END
  - ifelse用法
    CASE
    WHEN 条件1 THEN 要显示的值或（语句；）
    WHEN 条件2 THEN 要显示的值或（语句；）
    …
    ELSE 默认显示的值或（语句；）
    END
聚集函数 ↑
DELETE删除可以带where条件，执行后返回影响的行数，自增字段从原来的最大值继续计数，删除后可以回滚
TRUNCATE只能删除一整张表，效率高，无返回值，自增字段从1开始计数，删除后无法回滚
复制表
1、复制表结构：CREATE TABLE 新表名 LIKE 旧表名；
2、复制结构+数据：
CREATE TABLE 新表名
SELECT * FROM 旧表名；
FLOAT(5,2) 小数位+整数位一共5位，小数有2位
DECIMAL(x,y) 定点小数，默认为(10,0)，精度高，适用货币精确操作
字符型：
枚举类型 ENUM
c1 ENUM(‘a’,‘b’,‘c’);
日期型：
设置保存点：
…
SAVEPOINT a;
…
ROLLBACK TO a;
回滚到a，而a之前的操作提交
变量系统变量@@ 用户变量@（局部若紧跟select后加@）
多重分支IF，只能在BEGIN…END中间
IF 条件1 THEN 语句1；
ELSEIF 条件2 THEN 语句2；
…
ELSE 语句n；
循环 while, loop, repeat
iterater == continue
leave == break
【while:
标签: WHILE 循环条件 DO
循环体；
END WHILE 标签；】
【LOOP:
标签：LOOP
循环体；
END LOOP 标签；】
【REPEAT:
标签：REPEAT
循环体；
UNTIL 结束循环的条件
END REPEAT 标签；】

在这里插入图片描述

MySQL高级篇

写好SQL，建好索引且不失效
数据库优化分析：
1、慢日志查询
2、explain+慢SQL分析
3、show profile查询SQL的执行细节和生命周期
4、数据库调优

创建索引的情况：
1、主键自动创建唯一索引
2、频繁作为查找条件的字段
3、与其他表关联的字段
4、where常用字段
5、经常查询排序的字段
6、常用于统计或者分组的字段（分组需排序）
7、单值/组合索引（高并发倾向组合）
不适合创建索引：
1、数据少
2、经常增删改的表
3、数据重复且分布平均的字段不合适
EXPLAIN：
- 是什么：模拟优化器执行SQL，从而知道MySQL是如何处理SQL的，分析自己的查询语句或者表结构
- 功能：
  1、表的读取顺序 id ★
  2、数据读取操作的操作类型 selet_type
  3、哪些索引可以使用 possible_key
  4、哪些索引实际被使用 key ★
  5、表之间的引用 ref
  6、每张表有多少行被优化器查询 rows ★

id★	select_type	table	type★	possible_keys	key★	key_len	ref	rows★	Extra★
执行顺序	查询类型	表	访问类型\查询类型	可能会用到的索引	实际用到的索引	索引中使用的字节数	索引使用的列或常数	读取的行数	额外信息

id：相同则由上至下；不同则越大优先级越高
select_type：
1、SIMPLE：简单的select，不包含子查询或UNION
2、PRIMARY：最外层查询
3、SUBQUERY：SELECT或WHERE列表中的子查询
4、DERIVED：衍生，在FROM列表中包含的子查询被标记为DERIVED。递归执行子查询，把结果放在临时表里
5、UNION：在UNION后的SELECT被标记为UNION，若在FROM子查询中则为DERIVED
6、UNION RESULT：从UNION表获得的结果
type：
最好到最差：system>const>eq_ref>ref>range>index>ALL
1、system：只有一行记录（等于系统表）
2、const：通过索引一次找到，primary key或unique索引的等值查询，只有一条
3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常用于主键或唯一索引【扫描】，一个键一个记录的扫描
4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行，一个键多个记录的扫描
5、range：只检索给定范围的行，使用一个索引来选择行。
6、index：遍历索引树
7、ALL：全表扫描
possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用
key：实际被用到的索引。如果NULL，则没用或者没有。
key_len：索引中使用的字节数，计算使用的索引长度。不损失精确性的情况下，越短越好。key_len为索引字段最大可能的长度，并非实际使用长度。
ref：显示哪一列被使用了。哪些列或常量被用于查找索引列上的值
rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra：
1、Using filesort ★：无法利用索引完成的排序操作称为“文件排序”，没用建立的索引，而是另起了一个排序
2、Using temporary ★：使用了临时表保存了中间结果，MySQL在对查询结果排序时使用临时表。常见于order by和 group by
3、Using index ★：表示相应的select操作中使用了覆盖索引，避免访问了表的数据行。如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有出现using where，表明索引用来读取数据而非执行查找动作。（覆盖索引：查询的列，被索引覆盖，直接读索引，不用通过索引查找）
4、using where
5、using join buffer：使用了连接缓存
6、impossible where：where子句总是false，不能用来获得元组

索引优化

索引分析

单表：range类型查询字段后面的索引无效，把联合索引中范围查找的索引去掉
两表外连接，左连接加右表索引
三表：同上

索引失效

全值匹配最好
最佳左前缀法则：查询从索引的最左前列开始，并且不跳过索引中的列
不在索引列上做任何操作（计算、函数、类型转换），会导致索引失效而转为全表扫描
范围查询之后的索引全失效
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
!= <> 不等于的时候无法使用索引会导致全表扫描
is null 和 is not null 也无法使用索引
like以通配符开头也会导致索引失效而全表扫描，可以建立覆盖索引来解决（查的内容为索引）
字符串不加单引号索引失效
少用or，用它来连接时会索引失效

查询截取分析

查询优化

小标驱动大表：IN适用于小表通过条件查询出来一个较小的结果，然后大表进行匹配；EXISTS适用于一个对小表做查询，条件是一个大表，通过EXISTS把每个小表中的元素当作条件放到大表中查询是否存在
order by优化（避免出现Using_filesort）：最佳左前缀原则；数据库参数
group by优化：最佳左前缀法则；能用where不用having；数据库参数

慢日志查询

set global slow_query_log=1 打开
set global long_query_time=3 设置阈值为3秒
日志分析工具：mysqldumpslow

show profile：
1、converting HEAP to MyISAM：查询结果太大，内存不够用了，启用了虚拟内存
2、Creating tmp talbe：创建了临时表
3、Copying to tmp talbe on dis：把内存中的临时表复制到了磁盘
4、locked
全局查询日志：

锁的优化建议

1、尽量让所有数据检索都通过索引来完成，避免无索引导致行锁变表锁
2、合理设计索引，尽量缩小锁的范围
3、尽可能减少检索条件，避免间隙锁
4、尽量控制事务大小，减少锁定资源量和时间长度
5、尽可能降低隔离级别

_Yiz

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
数据库

控制命令MYSQL -u帐号 -p密码 -P端口 -hIPCREATE DATABASE a;ALTER DATABASE a; 修改DROP DATABASE a; 删除USE a；选择使用一个数据库CREATE TABLE IF NOT EXISTS aa(a1 SMALLINT,…) 创建数据表INSERT 表名 (项目名1，项目名2，…) VALUES（‘asdf’，3112，…）SELECT 项目名 FROM 表名AUTO_INCREMENT – 只能是主键默认从1开
复制链接

扫一扫