mysql电子书 eqpe_MySQL知识整理 - osc_wejvjxio的个人空间 - OSCHINA - 中文开源技术交流社区...

写在前面的话：秋招来临，博主对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)        存储过程实现的功能要复杂一些，而函数的针对性更强