MySQL知识整理
写在前面的话:秋招来临,博主对MySQL知识进行了整理,全文包括数据库优化和数据库基础两部分
数据库优化
1. 优化的目的
[1] 避免页面访问的出错(5xx错误;慢查询无法加载;阻塞无法提交)
[2] 稳定性(低效的查询)
[3] 优化用户体验
2. 慢查日志
3. SQL语句优化
[1] 需要优化的情况:查询次数多且占用时间长;IO大的SQL;未命中索引的SQL
[2] EXPLAIN执行计划:
a) table:查询的哪张表
b) type:const(常量查找,一般主键和唯一索引);eq_reg(范围查找,一般唯一索引或主键的范围查找);
ref(连接的查询中,基于某一个索引);range(基于某一个索引的范围查找);index(索引的扫描);
ALL(表扫描)
c) possible_key:可能用到的索引有哪些
d) key:实际使用的索引
e) key_len:索引的长度,越大越好
f) ref:索引的哪一列被使用
g) rows:表扫描的行数
h) using filesort(文件排序);using temporary(使用临时表,常用在ORDER BY和GROUP BY上)
[3] MAX()全表扫描的情况:在字段上建立索引,例CREATE INDEXidx_paydate ON payment(payment_date);
//在payment表建立payment_date关键字的索引
[4] GROUP BY优化:
原来:SELECT actor.first_name,actor.last_name,COUNT(*) FROM sakila.film_actor INNER JOIN
sakila.actor USING(actor_id) GROUP BY film_actor.actor_id; //临时表,文件排序
改写:SELECT actor.first_name,actor.last_name,COUNT(*) FROM sakila.film_actor INNER JOIN(
SELECT actor-id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id)AS c USING(actor-id);
//子查询
[5] LIMIT优化:
原来:SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50,5; //文件排序,全表扫描
改写1:SELECT film_id, description FROM sakila.film ORDERBY film_id LIMIT 50,5; //使用主键film_id,
翻到后面响应速度慢改写1优化:SELECT film_id, description FROM sakila.film WHERE film_id>55 and
film_id<=60 ORDER BY film_id LIMIT 1,5; //通过上一次查找返回的film_id
4. 索引优化
[1] 如何选择合适的列建立索引
a) 在WHERE/GROUP BY/ORDER BY/ON从句中出现的列
b) 覆盖索引
c) 索引字段越小越好
d) 离散度大的列放到联合索引的前面
[2] 一般索引多有利于查询(索引过多,分析变慢),降低写入效率
[3] 冗余索引:例:前缀列相同/在联合索引中包含主键索引,(InnoDB)实际上每一个索引后面都会包含主键
[4] 删除不使用的索引
5. 数据库结构优化
[1] 数据类型的选择:最小的;简单的;尽量NOT NULL;IP/时间
[2] 范式化(数据库设计的规范):第三范式:数据表中不存在非关键字段对任意候选关键字段的传递函数依赖
(不满足,会数据冗余)
[3] 反范式化:关联多,会影响查询效率,可以适当增加冗余,以达到优化查询效率的目的,反范式化是以
空间换时间的操作
[4] 表的垂直拆分:把原来一个有很多列的表拆分成多个表,解决表过宽的问题,原则:
a) 把不常用的字段单独存放到一个表中
b) 把经常一起使用的字段放到一个表中
c) 把大字段独立放到一个表中
[5] 表的水平拆分:
a) 概念:解决表单数据量过大的问题,每个表结构一样
b) 拆分方法:hash拆分,进行哈希运算(例:mod(costomer_id,5)拆分为5份),针对不同的hashID
把数据存到不同的表中(后台汇总表)
6. 系统配置优化
[1] 网络方面:增加TCP支持的队列数,加快资源回收
[2] 打开文件数的限制(默认1024),MySQL每一个表都是一个文件
[3] 硬件防火墙代替软件
[4] 配置文件:
a) 只有InnoDB,InnoDB缓冲池(75%);
b) 缓冲池个数(增加并发性);
c) 日志最长每秒钟刷新,log缓冲设置为较小;
d) 变更刷新到磁盘,0每一秒把变更刷新到磁盘,1每一次提交都会把变更刷新到磁盘(默认),2每一次
提交刷新到缓冲区,每一秒把缓冲区刷新到磁盘
e) IO进程数
f) 共享表空间(IO瓶颈,顺序操作);控制每一个表使用独立的表空间(增加并发和回收速度)
g) 刷新InnoDB表的统计信息,人为找一个闲的时间段刷新
7. OLTP(On-Line Transaction Processing)和OLAP(On-line AnalyticalProcessing)
[1] OLTP是传统的关系型数据库的主要应用,主要是基本的、日常的事务处理,记录即时的增、删、改、查,
比如在银行存取一笔款,就是一个事务交易OLAP即联机分析处理,是数据仓库的核心部心,支持复杂的分析
操作,侧重决策支持,并且提供直观易懂的查询结果。典型的应用就是复杂的动态报表系统。
[2] OLTP是面向顾客的,用于事务和查询处理。OLAP是面向市场的,用于数据分析
8. 服务器硬件优化
[1] 不超过32核
[2] 磁盘阵列(RAID):RAID0(条带,把多个磁盘连接成一个磁盘使用,高并发),RAID1(至少两个磁盘,存储
相同的数据),RAID5(至少3个磁盘,合成一个逻辑盘使用,奇偶校验信息(分布式)和数据存储到不同盘上,可用
奇偶校验信息回复数据),目前使用 (RAID1+0安全加高并发)
数据库基础
1. 什么是SQL(Structured Query Language)
[1] SQL是最重要的关系数据库操作语言,它不要求用户指定对数据的存放方法,也不需要用户了解具体的数据存放
方式,所以具有完全不同底层结构的不同数据库系统, 可以使用相同的结构化查询语言作为数据输入与管理的接口。
2. 语句规范
[1] 关键字,函数名称大写
[2] 数据库名称,表名称,字段名全部小写
[3] SQL语句必须以分号结尾
[4] Windows下默认不区分大小写;Linux下列与列的别名忽略大小写
3. 事务的特性(ACID)
[1] 事务:保证数据库的完整性
a) 原子性(Atomicity):一个事务被视为一个不可分割的最小单元,要么全部提交成功,要么全部回滚
b) 一致性(Consistency):从一个一致性的状态转换到另一个一致性的状态,如果系统崩溃,事务最终没有
提交,所以事务的修改也不会保存到数据库中
c) 隔离性(Isolation):通常来说,一个事务所做的修改最终提交以前,对其他事物是不可见的
1) READ UNCOMMITTED(未提交读):事务中的修改,即使未提交,对其他事务也都是可见的
2) READ COMMITTED(提交读):一个事务从开始直到提交前,所做的任何修改对其他事务都是不可见的
3) REPEATABLE READ(可重复读):保证了在同一事务中多次读取同样记录的结果是一样的(MySQL默
认)
4) SERIALIZABLE(可串行化):强制事务串行执行
5) 脏读:读取未提交的数据
6) 不可重复读:当前事务先进行了一次数据读取,然后再次读取到的数据是别的事务修改成功的数据,导致
两次读取到的数据不匹配,也就照应了不可重复读的语义。
7) 幻读:当某个事务在读取某个范围内的记录时,另一个事务又在该范围插入了一个新的记录,之前的事务
再次读取该范围的记录时,会产生幻行
隔离级别 |
脏读可能性 |
不可重复度可能性 |
幻读可能性 |
加锁读 |
READ UNCOMMITTED |
Yes |
Yes |
Yes |
No |
READ COMMITTED |
No |
Yes |
Yes |
No |
REPEATABLE READ |
No |
No |
Yes |
No |
SERIALIZABLE |
No |
No |
No |
Yes |
d) 持久性(Durability):一旦事务提交,则其所做的修改就会永久保存到数据库中
4. 死锁
[1] 死锁是指两个或者多个事务在同一资源上互相占用,并请求锁定对方占用的资源,从而导致恶性循环的现象
5. 记录的查找
[1] 简单格式SELSECT expr,… FROM tbl_name;
[2] 完整格式:SELSECT select_expr [,select_expr] [ FROM table_references //哪个表
[WHERE table_condition] //条件
[GROUP BY {col_name|position} [ASC|DESC], …] //分组
[HAVING where_condition] //分组条件,必须为聚合函数或字段出现在SELECT中
[ORDER BY {col_name|expr|posiiton} [ASC|DESC], …] //排序,默认升序,例id,age先按id排序再按age排序
[LIMIT {[offset,] row_count|row_count OFFSET offset}] //限制返回的数量,x表示返回的条数;x,y表示返回
第x条起的y条记录,从0开始计数] //ASC为升序,DESC为降序,position为前面查找对应的位子
[3] ‘*’表示所有列。Table.*表示table的所有列
[4] 别名查询:SELECT id AS usersId,username FROM users; //usersId为
别名,结果以usersId显示
6. 子查询
[1] 什么是子查询:出现在其他SQL语句内的SELECT子句;例:SELECT *FROM t1 WHERE col1=(SELSECT
col2 FROM t2); //’<>’表示不等于
[2] ‘ANY’/’SOME’任何一个’ALL’全部;例>ANY
[3] 使用[NOT] IN的子查询;使用[NOT] EXISTS的子查询
7. 关联查询
[1] 例:SELECT actor.first_name,actor.last_name,COUNT(*) FROM sakila.film_actor
INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;
8. 多表更新和连接(外键的逆向操作)
[1] 格式:UPDATE table_references SET col_name1={expr1|DEFAULT}
[,col_name2={expr2|DEFAULT]… [WHERE where_condition];
[2] UPDATE tdb_goods(//需要更新的表) INNER JOIN tdb_goods_cates(//连接的表) ON goods_cate=
cate_name(//连接条件) SET goods_cate=cate_id(//更新的值); //字段相同,可以在前面加表名
[3] INNER JOIN=JOIN=CROSS JOIN内连接(仅显示符合连接条件的记录);LEFT [OUTER] JOIN,左外连接
(显示左表的全部内容,对应不上的用NULL);RIGHT [OUTER] JOIN右外连接
[4] 连接格式:table_reference {[INNER|CROSS]JOIN|{LEFT|RIGHT} [OUTER] JOIN} table_reference ON conditional_expr;
[5] 例:SELECT goods_id,goods_name,cate_name FROM tdb_goods INNER JOIN tdb_goods_cates ON
tdb_gooods.cate_id=tdb_goods_cates.cate_id;
[6] 无线级分类表设计(自身连接)
9. 外键和索引
[1] 外键:保证数据一致性的一种策略
[2] 索引:是对数据表中一列或者多列值进行排序的一种结构
10. 属性/约束
[1] 空值与非空:
a) NULL,字段可为空值,默认
b) NOT NULL,字段禁止为空
[2] AUTO_INCREMENT(自动编号):必须与主键组合使用
[3] PRIMARY KEY(主键约束):每张数据表只有一个主键;记录的唯一性(主键也是唯一索引);
自动为NOT NULL;自动创建索引
[4] UNIQUE KEY(唯一约束):保证记录的唯一性;可以为空值;每张数据表可以存在多个唯一约束,创建索引
[5] DEFAULT(默认约束):没有明确赋值,自动赋予默认值;例sex ENMU(‘1’,’2’,’3’) DEFAULT ‘3’
[6] FOREIGN KEY(外键约束):
a) 例:FOREIGN KEY (pid)(//子表的关键字)REFERENCES procinces(id) (//父表的参照列)
b) 需满足的条件:
1) 父表和子表必须具有相同的存储引擎(InnoDB)
2) 外键列和参照列必须具有相似的数据类型
3) 数字的长度或是否有符号位必须相同;字符的长度可以不同
4) 外键列和参照列必须创建索引,外键列若不存在索引将自动创建
c) 外键约束的参照操作:
1) CASCADE:父表删除或更新,子表也自动删除或更新;例 FOREIGN KEY (pid) REFERENCES
procinces(id) ON DELETE CASCADE
2) SET NULL: 父表删除或更新,并设置子表中的外键列为NULL,前提是子表列没有设置为NOT NULL
3) RESTRICT / NO ACTION:拒绝对父表的删除或更新操作
[7] 约束针对两列及两列以上为表级约束
11. 聚合函数
[1] AVG() //平均数
[2] COUNT() //计数
[3] MAX() //最大值
[4] MIN() //最小值
[5] SUM() //求和
12. 加密函数
[1] MD5 //摘要的计算32位,例:SELECT MD5(‘admin’);web页面。
[2] PASSWORD() //进行密码的计算,修改客户端密码
13. 存储引擎
特点 |
MyISAM |
InnoDB |
Memory |
Archive |
存储限制 |
256TB |
64TB |
有 |
无 |
事务安全 |
- |
支持 |
- |
- |
支持索引 |
支持 |
支持 |
支持 |
|
锁颗粒 |
表锁 |
行锁 |
表锁 |
行锁 |
数据压缩 |
支持 |
- |
- |
支持 |
支持外键 |
- |
支持 |
- |
- |
[1] MyISAM:适用于事务处理不多的情况
[2] InnoDB:适用于事务处理比较多,需要外键支持的情况
[3] Memory
[4] CSV: //不支持索引,文本型
[5] Archive
14. 并发控制
[1] 概念:当多个连接对记录进行修改时保证记录的一致性和完整性
[2] 锁:
a) 共享锁(读锁):在同一时间内,多个用户可以读取同一个资源,读取过程中数据不会发生任何变化
b) 排他锁(写锁):在任何时候只能有一个用户写入资源,当进行写锁时会阻塞其他的读锁或者写锁操作
c) 锁粒度(颗粒):
1) 表锁,是一种开销最小的锁策略
2) 行锁,是一种开销最大的锁策略(支持最大并发操作)
15. 记录的插入
[1] 格式:INSERT [INTO] tbl_name [(col_name,…)] VALUES(value1,..);
[2] 给全部值赋值:INSERT tb1 VALUES('Tom’,25,7863.25);
[3] 给全部值(具有默认)赋值:INSERT tb1 VALUES(NULL,25,7863.25); // NULL/DEFAULT默认的,和自增的
[4] 部分赋值: INSERT tb1(username,salary) VALUES('John’,25,4500.69);
[5] 插入多个值:INSERT tb1 VALUES('Tom’,25,7863.25), ('Jhon’,25,7863.25);
[6] 允许子查询的插入:INSERT users SET username=’Ben’,password=’456’;
[7] SELECT插入:INSERT users SELECT…; //列数要对应
16. 创建和删除数据库
[1] CREATE {DATABASE| SCHEMA}(//无区别) [IF NOT EXISTS] (//如果已存在数据库,忽略错误信息)
db_name [DEFAULT] CHARACTER SET [=]charset_name(//编码方式)
[2] 例:CREATE DATABASE t1; //创建数据库t1
[3] 删除数据库:DROP {DATABASE| SCHEMA} [IF EXISTS] db_name;
17. 创建数据表
[1] 格式:CREATE TABLE [IF NOT EXISTS] table_name(
column_name data_type,
…
);
[2] 例:CREATE TABLE tb1(
username VARCHAR(20),
age TINYINT UNSIGNED,
salary FLOAT(8,2) UNSIGNED
);
18. 默认端口号3306
19. 启动和关闭MySQL服务(Windows)
[1] net start mysql //启动mysql服务
[2] net stop mysql //关闭mysql服务
20. 登录和退出
[1] mysql –uroot(//用户名) –p(//后接密码) –P3306(//端口)–h127.0.0.1(//本地)
[2] exit; quit; \q;
21. 打开数据库
[1] USE test; //打开test数据库
22. 数据类型
[1] 整型:TINYINT(128-127),SMALLINT(-32768-32767),MEDIUMINT,INT,BIGINT
[2] 字符型:CHAR(M)(//M=0-255 定长类型),VARCHAR(M)(//M=0-65535变长类型),ENUM(‘values1’,’values2’,…)
(最对65535个值,从其中选一个),SET(‘value1’,’value2’)(最多64个成员,集合中的任意排列组合)
[3] 浮点型:FLOAT[(M,D)](//一共M位,小数点D位),大约精确到小数点后7位;DOUBLE[(M,D)]
[4] 时间型:YEAR,TIME,DATE,DATETIME,TIMESTAMP(//1970-2037)
23. 查看数据表
[1] SHOW TABLES [FROM db_name]; //查看具有的数据表
[2] SHOW COLUMNS FROM tb1; //查看数据表的结构
24. 单表更新记录
[1] 格式:UPDATE [LOW_PRIORITY] [IGNORE] table_reference SET col_name1={expr1|DEFAUL}
[,col_name2={expr2|DEFAUL}]…[WHERE where_condition];
[2] 例:
a) 更新所有UPDATE users set age=age+5;
b) 条件更新:UPDATE users set age=age+5 WHERE id%2=0; //id为偶数的更新
25. 单表和多表删除记录
[1] 格式:DELETE FROM tbl_name [WHERE where_conditon];
[2] 例:更新所有DELETE FROM users WHERE id=6;
[3] 多表删除:DELETE t1 FROM tdb_goods AS t1 LEFT JOIN t2 ONt1.goods_name=t2.goods_name
WHERE t1.goods_id>t2.goods_id;
26. 数据表的修改
[1] 添加单列:
a) ALTER TABLE tble_name ADD [COLUMN] col_name column_definition [FIRST|AFTER col_name];
//FIRST位于列的前端;AFTER在某列之后
b) 例:ALTER TABLE users1 ADD age TINYINT UNSIGNED NOT NULL DEFAULT 10 AFTER username;
[2] 添加多列:ALTER TABLE tble_name ADD [COLUMN] (col_namecolumn_definition,…);
[3] 删除列:ALTER TABLE users1 DROP truename,DROP age,…; //删除truename和age
[4] 修改主键约束:
a) 添加:ALTER TABLE tbl_name ADD [CONSTRAINT [symble]] PRIMARY KEY [index_type](index_col_name,
…) ;
b) 例:ALTER TABLE users2 ADD CONSTRAINT PE_users2_id PRIMARY KEY(id) ; //修改id为主键
c) 删除:ALTER TABLE tbl_name DROP PRIMARY KEY;
[5] 修改唯一约束:
a) 添加:ALTER TABLE tbl_name ADD [CONSTRAINT [symble]] UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,…) ;
b) 例:ALTER TABLE users2 ADD UNIQUE(username) ; //修改username为唯一约束
c) 删除:ALTER TABLE tbl_name DROP {INDEX|KEY} index_name;
d) 例:ALTER TABLE users2 DROP INDEX username;
[6] 修改外键约束:
a) 添加:ALTER TABLE tbl_name ADD [CONSTRAINT [symble]] FOREIGN KEY [index_name]
(index_col_name,…) reference-definition;
b) 例:ALTER TABLE users2 ADD FOREIGN KEY (gid) REFERENCE province(id) ; //修改id为外键约束
c) 删除:ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;
d) 事实的外键和物理的外键(加了FORIGEN KEY)
[7] 修改添加/删除默认约束:
a) ALTER TABLE tbl_name ALTER [COLUMN] col_name {SET DEFAULT literal|DROP DEFAULT};
b) 例:ALTER TABLE users2 ALTER age SET DEFAULT 15 ; //修改age为默认约束;ALTER TABLE users2
ALTER age DROP DEFAULT ; //删除age为默认约束;
[8] 修改列定义:
a) 修改数据位置:ALTER TABLE users2 MODIFY id SMALLINT UNSIGNED NOT NULL FIRST; //定义不变,
添加位置即可
b) 修改列名称:ALTER TABLE users2 CHANGE pid (//原来的) p_id(//新的) TINYINT UNSIGNED NOT NULL;
c) 修改表名称:ALTER TABLE users2(//原来的) RENAME users3(//新的);
27. 字符函数
[1] CONCAT() //字符连接 例:SELECT CONCAT(‘immoc’,’MySQL’);
[2] CONCAT_WS() //使用指定的分隔符进行字符连接,例:SELECT CONCAT_WS(‘|’,‘immoc’,’MySQL’);
结果immoc| MySQL
[3] FORMAT() //数字格式化 例:SELECT FORMAT(12560.75,1); 结果:12560.8
[4] LOWER() //转换成小写字母
[5] UPPER() //转化成大写字母
[6] LEFT() //获取左侧字符 例:SELECT LEFT(’MySQL’,2); 结果My
[7] RIGHT() //获取右侧字符
[8] LENGTH() //字符长度
[9] LTRIM() //删除前导空格
[10] RTRIM() //删除后续空格
[11] TRIM() 例:SELECT TRIM(LEADING (//TRAILING后续 BOTH全部)‘?’ FROM ‘??MySQL???’) 结果:MySQL???
[12] SELECT REPLACE(‘?? My??SQL???’,’?’,’’); 结果:MySQL //替换
[13] SUBSTRING() //从1开始计数,可为负数,从尾开始
[14] [NOT] LIKE //匹配,%任意字符,_任意一个字符
28. 数值运算符
[1] CEIL() //向上取整
[2] FLOOR() //向下取整
[3] DIV //整数运算,抹去小数点
[4] MOD / % //取模,向上取整
[5] SELECT POWER(3,3); //9
[6] ROUND //四舍五入 SELSECT ROUND(3.652,1); 结果3.7
[7] TRUNCATE() //数字截断
[8] [NOT] BETWEEN…AND…; //例:SELECT 35 BETWEEN 1 AND 22;结果0
[9] [NOT] IN(); //同上
[10] IS [NOT] NULL;
29. 日期和时间
[1] NOW() //当前时间日期加时间
[2] CURDATE() //当前日期
[3] CURTIME() //当前时间
[4] DATADIFF() //时间差
[5] DATE_ADD() //例DATE_ADD(‘2014-2-12’,INTERVAL 365/-365 DAY/YEAR/WEEK);
30. 信息函数
[1] DATEBASE() //当前的打开的数据库
31. 自定义函数(和内置函数相同)
[1] 不带参数:CREATE FUNCTION f1()(//函数名) RETURNS VARCHAR(30) (//返回值)RETURN
DATE_FORMAT(NOW(),‘%Y年%m月%d日 %H点:%i分:%s秒’)(//函数定义);
[2] 带参数:CREATE FUNCTION f2(num1 SMALLINT UNSIGNED, num2 SMALLINT UNSIGNED)
RETURNS FLOAT(10,2) UNSIGNED RETURN (num1+num2)/2;
[3] 插入函数:(需要先修改结束符DELIMITER //,执行后修改回来)
CREATE FUNCTION addusers(username VARCHAR(20))
RETURN INT UNSIGNED
BEGIN
INSERT test(username) VALUES(username);
RETURN LAST_INSERT_ID();
END
//
32. 存储过程
[1] 优点:
a) 省略语法分析和编译(除第一次),实现较快的执行速度
b) 增强SQL语句的功能和灵活性(可以写控制语句)
c) 减少网络流量
[2] 格式:
CREATE
[DEFINER={user|CURRENT_USER}] //默认当前用户 PROCEDURE sp_name([proc_parameter[,…]])
[characteristic…] toutine_body proc_parameter:
[IN|OUT|INOUT] param_name type //IN该参数的值必须在调用存
储过程时指定,不能被返回;OUT可以改变,可以返回;INOUT调用时指定,可以改变和返回。
[3] 例:(需要先修改结束符DELIMITER //,执行后修改回来DELIMITER ;)
a) 不带参数:CREATE PROCEDURE sp1() SELECT VERSION();
CALL sp1() / sp1;
b) 带IN参数:CREATE PROCEDURE removeUserById(IN p_id INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
END
//
CALL removeUserById(22);
c) 带返回的:
CREATE PROCEDURE removeUserAndReturnUserNums(IN p_id INT UNSIGNED, OUT userNums
INT UNSIGNED)
BEGIN
DELETE FROM users WHERE id=p_id;
SELECT count(id) FROM users INTO(//将select结果的表达式放入参数) usersNums;
END
//
CALL removeUserAndReturnUserNums(27,@nums); //带@的为用户变量
SELECT @nums;
[4] 存储过程和自定义函数的区别
a) 存储过程可以有多个返回值,函数只能有一个返回值
b) 存储过程一般独立执行,而函数一般作为其它SQL语句的组成部分来实现
c) 存储过程实现的功能要复杂一些,而函数的针对性更强