桌子哥的MySQL总结

 

MySQL作为常用的企业级的数据库，有着非常丰富的使用场景。

 

一、3NF设计

 

1）对于MySQL这样的关系型数据库，在进行设计表的时候需要考虑3NF的设计。NF是范式，范式是指对表的拆分。

 

对于1NF：即表的列具有原子性，不可再分解，即列的信息，不能分解，只要数据库是关系型数据库就自动满足1NF。

 

对于2NF：即表中的记录是唯一的，就满足2NF，通常我们设计一个主键来实现，因为主键不包含业务逻辑，因此数据比较稳定。（主键不含业务逻辑，一般是自增的）

 

对于3NF：即表中不应该有冗余数据。就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放。

 

2）对于3NF的数据表的标准化设计有以下的图作为参考：

 

第一范式，列原子性，第二范式非主键属性完全依赖于主键，第三范式消除传递依赖

 

2-1）第一范式：

 

将表格分割（叫做分解还是比较好）成单纯的二元表格，保证每列的原子性不可再分。

 

 

 

 

2-2）第二范式：范式是对表的一个拆分

 

通过主键确定其他列的值。

保证每个表只有一种数据（商品只能有商品，报表只能有报表）

 

 

 构建一个复合主键（但是这两个主键在总体中是作为外键来的），该复合主键由报表ID和商品ID组成。

 

 

 

第二范式：

 

1、必须满足第一范式

 

2、必须有一个主键。没有包含在主键中的列必须完全依赖于主键，而不能只依赖于主键的一部分。

 

2-3）第三范式：不能有传递的依赖关系

 

1、必须满足第二范式

 

2、

 

对于上图，在报表里，通过报表ID可以找到客户ID，然后通过客户ID可以找到客户名称，通过报表ID是无法找到客户名称的因为客户名称可能会存在重复的情况。因此在这里存在传递的依赖关系。

第三范式的拆分格式：

 

 

 

3）对于真实的数据表设计而言，非冗余的数据表设计也不一定是最好的。因此在实际的设计过程中会使用反3NF设计，也就是设计宽表来提升效率。

 

严格满足3NF的设计会导致使用的不方便，在表的1对N的情况下，为了提高效率，可能会在1这个表中设计字段通过进行字段的冗余来进行查询提速。这就是所谓的适度冗余。在一些情况下创建的宽表可以是为了反3NF来实现查询的方便。

 

二、MySQL三层结构

 

1）MySQL数据库普通表的本质仍然是文件。例如常见的MyISAM和InnoDB数据表引擎对应的文件类型是不一样的，但是都是文件。

 

2）所谓安装MySQL数据库，就是在主机安装一个数据库管理系统（DBMS），这个管理程序可以管理多个数据库。DBMS（database Management system）。

3）一个数据库中可以创建多个表，以保存数据（信息）。

 

 

 

指令通过网络通过端口3306，到mysqld.exe，经过解析找到相应的库和表进行文件操作。

 

三、SQL语句分类

 

DDL：数据定义语句【create库、表】

 

DML：数据操作语句【增insert、删delete、改update】

 

DQL：数据查询语句【select】

 

DCL：数据控制语句【管理数据库：比如用户权限，grant（授权），revoke（撤回）】

 

1）DDL：数据定义语句【create库、表】

 

1-1）创建数据库：

 

CREATE DATABASE [IF NOT EXISTS] db_name [create_specification[,create_specification]…]

 

create_specification:

 

CHARACTER_SET：指定数据库采用的字符集，如果不指定字符集，默认utf8

 

COLLATE：指定数据库字符集的校对准则（常用的utf8_bin[区分大小写]、utf8_general_ci[不区分大小写]，注意默认的是utf8_general_ci）

 

1-2）备份和恢复数据库：

 

备份数据库：

 

在DOS里进行操作

 

mysqldump -u 用户名 -p -B 数据库1 数据库2 数据库n > d:\\文件名.sql（可以带路径）

 

恢复数据库：

 

进入MySQL命令再执行

 

source 文件名.sql

 

对于备份文件而言，其就是对应的SQL文件。

 

对于恢复而言，还有一个笨办法是可以直接将bak.sql的内容放到查询编辑器中来执行。但是有的时候可能会比较慢如果sql脚本太大的话。

 

1-3）备份和恢复数据表：

 

备份数据库的表：

 

mysqldump -u用户名 -p 数据库表1 表2 表n > d:\\文件名.sql（注意：不写那个-B）

1-4）创建数据表：

 

 

 

MySQL列类型，即数据类型：

 

1-4-1）数值类型：

 

整形：

 

Tinyint[1字节]

 

Smallint[2字节]

 

Mediumint[3个字节]

 

Int[4个字节]

 

Bigint[8个字节]

 

数值型（整数）的基本使用：

 

1.说明：使用规范，在能够满足需求的情况下，尽量选择占用空间小的类型。

 

对于实际的数据取值范围，要考虑是否为带符号的还是无符号的。

 

 

CREATE TABLE t10 (id tinyint); 默认是有符号的

 

CREATA TABLE t11 (id tinyint unsigned); 无符号的

bit(m)，m在1-64之间，如果m为8表示一个字节，如果m为64表示8个字节。如果是一个字节的话显示的数据范围是0~255。这种类型的数据显示使用的是位，如果插入数据1显示为1，插入数据3显示为11，插入数据255显示为1111111。查询时仍然可以按照数来查询。如果一个值只有0,1，可以考虑使用bit(1)来节省空间，但是在实际的使用场景不多。

 

小数类型：

 

Float[单精度 4个字节]

 

Double[双精度 8个字节]

 

Decimal[M,D] [大小不确定] （M表示指定长度，D表示小数点后位数）

 

数值型（小数）的基本使用：

 

FLOAT/DOUBLE [UNSINGED]

 

FLOAT是单精度，DOUBLE是双精度。

 

DECIMAL[M,D] [UNSIGNED]

 

可以支持更加精确的小数位，M是小数位数（精度）的总数，D是小数点（标度）后的位数。

 

如果D是0，则值设置没有小数点或分数部分。M最大65，D最大30。如果D被省略，默认是0，如果M被省略，默认是10。

建议：如果希望小数的精度高，推荐使用decimal。

 

 

 Decimal可以存放很大的数据。在数据超级大的情况下，DECIMAL要比BIGINT效果好，BIGINT的取值范围比DECIMAL要小。

 

 

 

1-4-2）文本类型（字符串类型）：

 

Char 0 ~ 255

 

Varchar 0 ~ 65535 [0 ~ 2 ^ 16 - 1] （相当于2个字节）

 

Text 0 ~ 2 ^ 16 - 1

 

Longtext 0 ~ 2 ^ 32 – 1

 

字符串的基本使用

 

CHAR(size)，固定长度字符串最长255字符。

VARCHAR(size) 0~65535字节，可变长度字符串，最大65535字节。字符和字节有很大的区别，对于字节而言，不用的编码方式字符占用的字节长度不同。Utf8编码最大21844字符，1-3个字节用于记录大小。对于utf8格式，3个字节表示1个字符，所以有(65535-3)/3 = 21844。

 

 

 

CHAR(4)和VARCHAR(4)中的4都是表示字符，而不是字节。字符不区分汉字还是字母。

 

CHAR(4)是定长（固定的大小），如果只插入2个字符的话，也会占用分配的4个字符的空间。

 

VARCHAR(4)是变长（变化的大小），如果只插入2个字符的话，实际占用空间大小并不是4个字符，而是按照实际占用空间来分配（VARCHAR本身还需要占用1-3个字节来记录存放内容长度）

 

如果数据是定长，推荐使用char，比如邮编，手机号和身份证号等。

 

如果一个字段的长度是不确定的，则使用varchar，比如留言，文章等。

 

查询速度：char > varchar

 

在存放文本时，也可以使用Text数据类型，可以将Text列视为VARCHAR列，注意Text不能有默认值。大小0-2^16字节，如果希望存放更过字符，可以选择MEDIUMTEXT 0-2^24或者LONGTEXT 0-2^32。

 

1-4-3）二进制数据类型：

 

Blob [0 ~ 2 ^ 16 - 1]

 

Longblob [0 ~ 2 ^ 32 – 1]

 

1-4-4）日期类型：

 

Day [日期年月日]

 

Time[时间时分秒]

 

Datetime[年月日时分秒]

 

Timestamp[时间戳]

 

Year[年]

 

日期类型：

 

CREATE TABLE t14(

 

Birthday DATE,

 

Job_time DATETIME,

 

Login_time TIMESTAMP

 

       NOT NULL DEFAULT CURRENT_TIMESTAMP

 

       ON UPDATE CURRENT_TIMESTAMP); --在登录时使login_time自动更新

-- 如果我们更新t14表的某条记录，login_time列会自动的以当前时间进行更新。

 

 

 

2）DML：数据操作语句【增insert、删delete、改update】

以这个表为例：

 

 

 

2-1）ALTER细节：

 

练习：

 

2-1-1）员工表emp的上增加一个image列，varchar类型（要求在resume后面）

 

ALTER TABLE emp

 

ADD image VARCHAR(32) NOT NULL DEFAULT ‘ ’

 

AFTER `RESUME`

 

（使用AFTER和FIRST来控制位置）

 

在新增列的时候需要进行新的列的位置的控制，使用的关键字是AFTER和FIRST来确定位置。

 

2-1-2）修改job列，使其长度为60

 

ALTER TABLE emp

 

MODIFY job VARCHAR(60)

 

2-1-3）删除sex列

 

ALTER TABLE emp

 

DROP sex

 

2-1-4）表名该为employee

 

RENAME TABLE emp TO employee

 

2-1-5）修改表的字符集为utf8

 

ALTER TABLE employee CHARACTER SET utf8（或者是CHARSET utf8）

 

2-1-6）列名name修改为user_name

 

ALTER TABLE employee CHANGE `name` user_name VARCHAR(32) NOT NULL DEFAULT ‘ ’;

 

2-2）INSERT细节：

 

2-2-1）插入的数据应与字段的数据类型相同

 

如果字段的数据类型为int，而数据的数据为’30’也是可以的，因为底层会进行一个转化。

 

2-2-2）数据的长度应在列的规定范围内

 

2-2-3）在value中列出的数据位置必须与被加入的列的排列位置相对应

 

2-2-4）字符和日期型数据应包含在单引号中。

 

2-2-5）列可以插入空值[前提是该字段允许为空]，如果建表时字段后面有not null则不允许插入空值。

 

2-2-6）可以以insert into tab_name (列名…) values (), (), ()形式添加多条记录

 

2-2-7）如果是给表中的所有字段添加数据，可以不写前面的字段名称。

 

2-2-8）默认值的使用，当不给某个字段值时，如果有默认值就会添加默认值，否则会报错。如果某个列，没有指定not null，那么当添加数据时，没有给定值，则会默认为null。

 

2-3）UPDATE语句的细节：

 

UPDATE语句可以用新值更新原有表行中的各列。

 

SET子句指示要修改哪些列和要给予哪些值。

 

WHERE子句指定应更新哪些行。如果没有WHERE子句，则更新所有的行。

 

如果需要修改多个字段，可以通过set 字段1 = 值1, 字段2 = 值2,…

 

2-3-1）将所有员工薪水修改为5000元。[如果没有带where条件，会修改所有的记录，因此要小心]

 

UPDATE employee SET salary= 5000

 

2-3-2）将姓名为小妖怪的员工薪水修改为3000元

 

UPDATE employee SET salary = 5000 WHERE user_name = ‘小妖怪’

 

2-3-3）将老妖怪的薪水在原有基础上增加1000元

 

UPDATE employee SET salary = salary + 1000 WHERE user_name = ‘老妖怪’

 

2-4）DELETE语句的细节：

 

如果不使用where子句，将删除表中所有数据。

 

DELETE语句不能删除某一列的值[可以使用update设为null或者’’]

 

使用DELETE语句仅删除记录，不删除表本身。如果删除表，使用drop table语句，drop table tbl_name。

 

DELETE FROM tbl_name [WHERE where_condition]

 

删除表中名称为老妖怪的记录

 

DELETE FROM employee WHERE user_name = ‘老妖怪’

 

3）DQL：数据查询语句【select】

 

对于整个MySQL，SELECT是其中的重点。作为数据库业务逻辑实现的主要方式，在SELECT中可以对数据表进行相关的逻辑设计来实现想要的业务逻辑，获得正确的数据供运营部门和其他部门来使用。

 

SELECT部分的重点是业务的理解、数据表字段的使用掌握、关联规则的确定还有函数的正确使用。

 

这里先整理下SELECT的重点和难点。

 

3-1）要查询的记录，每个字段都相同，才会去重

 

SELECT DISTINCT `name`, english FROM student; （当name和english均相同时才会去重）

 

3-2）可以使用表达式对查询的列进行运算

 

在SELECT语句中可使用AS语句

 

模糊查询LIKE，NOT LIKE，在模糊查询中%代表0或多，例如’韩%’，可以匹配韩，韩寒，韩顺平等。

 

3-3）BETWEEN AND是闭区间

 

3-4）合计/统计函数 COUNT()

 

COUNT(*)和COUNT(列)的区别：

 

COUNT(*)返回满足条件的记录的行数

COUNT(列)统计满足条件的某列有多少个，但是会排除为null的情况。

 

 

 

3-5）合计函数SUM()

 

SUM函数返回满足where条件的行的和，一般使用在数值列上。

 

AVG()，求平均值。

 

MAX()，MIN()

 

3-6）使用group by子句对列进行分组

 

使用having子句对分组后的结果进行过滤（Having对分组后的结果进行过滤）

 

Having和group by混合使用。

3-7）字符串相关函数

 

 

 

3-8）dual亚元表，系统表可以作为测试表使用。

3-9）写SQL语句的思路是化繁为简，各个击破。

 

 

 

RAND()每次会返回不同的随机数，在[0,1]之间。

 

RAND(seed)也会返回随机数在[0,1]之间，如果seed不变，则返回的随机数也不变。

3-10）时间日期相关函数：

 

 

 

3-10-1）查询在10分钟之内发布的新闻

 

SELECT * FROM mes

 

WHERE DATE_ADD(send_time, INTERVAL 10 MINUTE) >= NOW();

 

3-10-2）DATE_ADD()和DATE_SUB()中的INTERVAL后面可以是year, month, day, hour, second

 

时间函数中的输入可以是：

 

Date类型：1986-11-11

 

Datatime类型：1986-11-11 11:10:10

Timestamp类型：是一个长串 unix_timestamp()，返回的是1970-1-1至今的秒数

 

 

 

FROM_UNIXTIME()：可以把一个UNIX_TIMESTAMP秒数，转成指定格式的日期。

 

‘%Y-%m-%d’的格式是固定好的，表示年月日，

 

‘%Y-%m-%d %h-%m-%s’

 

‘%Y-%m-%d %h:%m:%s’ 也可以

SELECT FROM_UNIXTIME(1618483484, ‘%Y-%m-%d’) FROM DUAL;

 

 

 

 

3-10-3）加密和系统函数

 

 

 

USER() 查询用户，可以查看登录到mysql的有哪些用户，以及登录的IP

 

DATABASE() 查询当前使用的数据库名称

 

MD5(str) 为字符串算出一个MD5 32的字符串，常用（用户密码）加密，数据库存放加密后的密码

 

MD5的结果的长度为32个字符，因此需要CHAR(32)

使用方式：

 

 

 3-11）程控制函数：

 

 

 

判断是否为空要使用IS NULL，判断不为空要使用IS NOT NULL

 

如果是具体的值，则不能使用IS，要使用等号=，括号里为单独的一列

 

 

 

3-12）查询加强：

 

1）在MySQL中日期类型可以直接比较

 

使用where子句：如何查找1992.1.1后入职的员工，需要注意时间的格式

 

SELECT * FROM emp WHERE hiredate >= ‘1992-01-01’;

 

2）使用LIKE操作符进行模糊查询

 

%：表示0到多个任意字符

 

_：表示单个任意字符

 

如何显示首字符为S的员工姓名和工资

 

SELECT ename, sal FROM emp WHERE ename LIKE ‘S%’;

 

如何显示第三个字符为大写O的所有员工的姓名和工资

 

SELECT ename, sal FROM emp WHERE ename LIKE ‘__O%’; （包含连续两个_）

 

如何显示没有上级的雇员的情况（对于NULL来说要用IS）

 

SELECT * FROM emp WHERE mgr IS NULL;

 

3-13）查询表结构：

 

DESC emp；

 

3-14）分页查询：

 

基本语法：SELECT … LIMIT start, rows 表示从start + 1行开始取，取出row行，start从0开始计算。

 

按雇员的id号升序取出，每页显示3条记录，请分别显示第1页，第2页，第3页

 

第1页：

 

SELECT * FROM emp ORDER BY empno LIMIT 0,3；

 

第2页：

 

SELECT * FROM emp ORDER BY empno LIMIT 3,3；

 

第3页：

 

SELECT * FROM emp ORDER BY empno LIMIT 6,3；

 

公式：

 

SELECT * FROM emp

 

ORDER BY empno

 

LIMIT 每页显示记录数 * (第几页 - 1)，每页显示记录数

 

3-15）分组增强：

 

3-15-1）显示每种岗位的雇员总数、平均工资

 

SELECT COUNT(*), AVG(salary), job FROM emp GROUP BY job;

 

3-15-2）显示雇员总数，以及获得补助的雇员数

 

SELECT COUNT(*), COUNT(comm) FROM emp;

 

3-15-3）显示雇员总数，以及未获得补助的雇员数

 

SELECT COUNT(*), COUNT(IF (comm IS NULL, 1, NULL)) FROM emp;

 

SELECT COUNT(*), COUNT(*) – COUNT(comm) FROM emp;

 

3-15-4）显示管理者的总人数

 

SELECT COUNT(DISTINCT mgr) FROM emp;

 

3-15-5）显示雇员工资的最大差额

 

SELECT MAX(sal) – MIN(sal) FROM emp;

 

3-16）多子句查询：

 

如果一个select语句同时包含有group by, having, limit, order by那么他们的顺序为group by, having, order by, limit

 

SELECT column1, column2, column3, …

 

FROM table

 

GROUP BY column

 

HAVING condition

 

ORDER BY column

 

LIMIT start, rows;

 

3-17）笛卡尔集：

 

行数为A表的行数乘以B表的行数。A表的每一行和B表的每一行进行组合。

 

SELECT * FROM A, B; 这样的默认处理返回结果称为笛卡尔集。

 

解决多表的关键就是写好过滤条件。

 

3-18）MySQL多表查询：

 

自连接：

 

自连接是指在用一张表的连接查询[将同一张表看做两张表]

 

对于SQL而言，需要先写一个大致的结构，然后再往里面添加过滤条件。

 

自连接的特点：

 

1.把同一张表当做两张表使用。

 

2.需要给表取别名（表名表列名）。

 

3.列名不明确，可以指定列的别名（列名 AS 列的别名）。

 

单行子查询和多行子查询。

 

把子查询当做一张临时表可以解决很多很多复杂的查询。

 

3-19）All和any的使用：

 

3-19-1）使用ALL：全部

 

SELECT ename, salary, deptno

 

FROM emp

 

WHERE sal > ALL(

 

SELECT sal

 

FROM emp

 

WHERE deptno = 30)

 

也可以这样写：

 

SELECT ename, salary, deptno

 

FROM emp

 

WHERE sal > (

 

SELECT MAX(sal)

 

FROM emp

 

WHERE deptno = 30)

 

3-19-2）使用ANY：任一

 

SELECT ename, salary, deptno

 

FROM emp

 

WHERE sal > ANY(

 

SELECT sal

 

FROM emp

 

WHERE deptno = 30)

 

也可以这样写：

 

SELECT ename, salary, deptno

 

FROM emp

 

WHERE sal > (

 

SELECT MIN(sal)

 

FROM emp

 

WHERE deptno = 30)

 

3-20）多列子查询

 

多列子查询则是查询返回多个列数据的子查询语句

 

如果对于子查询而言，如果是一个字段与子查询来进行判断则是单列子查询。

多列子查询这是进行子查询之前判断是多列的。

 

 

 

4）其他：

 

4-1）表复制：

 

自我复制数据（蠕虫复制）

 

有时，为了对某个sql语句进行效率测试，我们需要海量数据时，可以使用此法为表创建海量数据。

 

表的复制

 

INSERT INTO my_tab01 SELECT * FROM my_tab01;

 

4-2）删掉一张表的重复记录：

 

需要借助一个临时表来完成

 

CREATE TABLE my_tab02 LIKE emp;

 

把emp表的结构（列），复制到my_tab02中。

 

考虑去重：my_tab02

 

思路：

 

（1）先创建一张临时表my_tmp，该表的结构和my_tab02一样

 

CREATE TABLE my_tmp LIKE my_tab02;

 

（2）把my_tmp的记录通过distinct关键字处理后把记录复制到my_tmp

 

INSERT INTO my_tmp SELECT DISTINCT * FROM my_tab02;

 

（这时可以rename下就行）

 

（3）清除掉my_tab02记录

 

DELETE FROM my_tab02;

 

（4）把my_tmp表的记录复制到my_tab02

 

INSERT INTO my_tab02 SELECT * FROM my_tmp;

 

（5）DROP掉临时表

 

DROP TABLE my_tmp;

 

4-3）合并查询：

 

UNION ALL不去重

 

UNION去重

 

4-4）SQL表外连接：

 

左外连接：左侧的表完全显示则为左外连接。

 

右外连接：右侧的表完全显示则为右外连接。

 

但是绝大多数情况下还是使用内连接。

 

 

 

四、主键与外键

 

1）主键：

 

Primary key（主键）

 

字段名字段类型 primary key

 

用于唯一的表示表行的数据，当定义主键约束后，该列不能重复。主键列的值是不能重复的。

 

主键使用的细节讨论：

 

1.primary key不能重复而且不能为null。

 

2.一张表中只能有一个主键，但可以是复合主键。

 

创建一个复合主键：（复合主键）

 

CREATE TABLE t18

 

(id INT,

 

`name` VARCHAR(32),

 

email VARCHAR(32),

 

PRIMARY KEY(id, `name`));

 

在实际的开发过程中，每个表往往都会设计一个主键。

 

2）MySQL约束：

 

not null：如果在列上定义了not null，那么当插入数据时，必须为列提供数据。

 

unique：当定义了唯一约束后，该列值是不能重复的。

 

id INT UNIQUE NOT NULL

 

Unique细节：

 

1.如果没有指定not null，则unique字段可以有多个null。

 

2.一张表可以有多个unique字段。

 

3.如果一个列（字段），是unique not null使用效果类似primary key。

 

3）外键：

3-1）外键举例：

 

 

 

如果我们要求，每个学生所在的班级号class_id是存在的班级编号，就可以把class_id做成外键约束。学生表的class_id和班级表的id进行外键约束。在这个关系里班级表被称为主表。

 

对于上图，如果对于学生表和班级表，班级表的id有100和200，学生表的class_id有100，200和300，那个class_id为300的那条数据是不会插入成功的。

 

如果在班级表中想删除id为200的这条数据，也会失败，因为与学生表存在外键约束，想删除需要先删除学生表中的class_id为200的那条数据。

 

3-2）外键：FOREIGN KEY

 

用于定义主表和从表之间的关系：外键约束要定义在从表上，主表则必须具有主键约束或是unique约束。当定义外键约束后，要求外键列数据必须在主表的主键列存在或是为null。

FOREIGN KEY（本表字段名）REFERENCES 主表名（主键名或unique字段名）

 

 

 

在上图中class_id就是外键。

 

3-3）外键的细节说明：

 

1、外键指向的表的字段，要求是primary key或者是unique。

 

2、表的类型是Innodb，这样的表才支持外键。

 

3、外键字段的类型要和主键字段的类型一致（长度可以不同）。

 

4、外键字段的值，必须在主键字段中出现过，或者为null[前提是外键字段允许为null]

 

5、一旦建立主外键的关系，数据就不能随意删除了。

 

3-4）约束：

 

MySQL中check是不起作用的，如果要进行check操作则可以使用枚举类型来进行校验。

 

CREATE TABLE test4 ( 

 

     id BIGINT UNSIGNED  PRIMARY KEY AUTO_INCREMENT, 

 

     brand VARCHAR(255) NOT NULL, 

 

     color ENUM('RED','GREEN','BLUE')

 

  ) ENGINE = InnoDB;

 

但是不要在ENUM中设置数值。

 

3-5）自增长：

 

在某张表中，存在一个id列（整数），我们希望在添加记录的时候，该列从1开始，自动的增长

 

字段名整型 primary key auto_increment

 

自增长使用细节：

 

1、一般来说自增长是和primary key配合使用的。

 

2、自增长也可以单独使用[但是需要配合一个unique]

 

3、自增长修饰的字段为整数型的（虽然小数也可以但是非常非常少这样的使用）

 

4、自增长默认从1开始，也可以通过如下命令修改alter table 表名 auto_increment = xxx

 

5、如果你添加数据时，给自增长字段（列）指定的有值，则以指定的值为准。如果指定了自增长，一般来说，就按照自增长的规则来添加数据。

 

添加自增长的字段方式

 

INSERT INTO XXX (字段1,字段2,…) values (null, ‘值’, …) （字段1对应的是null）

 

INSERT INTO XXX (字段2,…) values(‘值’,…)

 

INSERT INTO XXX values(null, ‘值1’,…)

 

修改自增长：

ALTER TABLE t25 AUTO_INCREMENT = 100

 

 

 

在进行自增长的时候，是找到当前最大的id值来进行自增长，在上面的例子里首先将自增长的id设定为100，而到后面直接对id进行赋值为666，经过这个操作在此之后的自增长需要从667开始。

 

 

 

五、索引

 

1）索引的介绍

 

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调SQL，查询速度就可能提高百倍千倍。

 

索引分为：主键索引、唯一索引（UNIQUE）、普通索引（INDEX）和全文索引。

 

创建索引：

 

例子：

 

在没有创建索引前，emp.ibd文件大小是524M。

 

创建索引后emp.ibd文件大小是655M[索引本身也会占用空间]

 

empno_index索引名称

 

ON emp(empno)：表示在emp的表empno列创建索引

 

CREATE INDEX empno_index ON emp(empno)

 

2）索引的原理：

 

当我们没有使用索引的时候，查询数据的思路是全表扫描。如果需要查询的数据在第一条的话，同样也是会向下扫描，因为不知道后面还有没有这条数据。

 

索引的原理：

构造一个二叉树（使用二叉树索引的方式）

 

 

 

查找数就是一个比较的过程，如果比较了30次，则可以覆盖的表的范围2^30。

 

使用索引为什么会快？形成了一个索引的数据结构，比如二叉树等（还可能为B树和B+树等）

 

没有索引为什么会慢？因为全表扫描。

 

3）索引的代价：

 

3-1）磁盘占用

 

3-2）对dml（update、delete、insert）语句的效率影响

 

如果对表进行dml操作会对索引进行维护，对速度有影响。

 

但是创建索引带来的更多的还是好处。

 

查询请求数量要远远大于修改和发布请求的数量。

 

4）详解索引

 

在实际的数据库查询任务中会遇到慢SQL查询的问题，因此这时需要使用合理的使用索引来提升查询的效率。

 

索引是帮助MySQL高效获取数据的排好序的数据结构。索引是一种数据结构。

 

常见的可以作为索引的数据结构有：

 

（1）二叉树、（2）红黑树、（3）Hash表、（4）B-Tree

 

这些数据结构又各有优缺点。

 

对于二叉树而言，随着数据量的增加有时候会退化成链表。

 

对于红黑树而言，如果一侧比另一侧大很多会旋转，有时候树的高度可能非常高。

 

对于Hash表而言，其性能甚至在一定情况下是优于B+树的，但是也存在一些问题，存在hash冲突问题，仅能满足“=”，“IN”，不支持范围查询。

 

对于B树而言，B树适合范围查询。

 

5）B树和B+树详解

 

B树可以说是MySQL中的一个核心了。

 

为了更小的树的高度，将树的高度设置在4以下就用B+树。

 

让一个节点可以横向存储更多数据，就使用B树。

 

Mysql在B+树的节点大小为16K

 

数据库主键自带索引

 

 

 

 

一个3层的B+树可以存储2000多万数据。

 

 

 

查找是放到内存RAM中折中查找。去RAM中比较大小。耗时主要是在IO上。上图中第一层和第二层会常驻RAM，只需要一次IO操作就可以完成。

 

6）存储引擎与索引：

 

聚集索引：聚簇索引，就是InnoDB的主键索引。叶节点包含了完整的数据记录。

 

非聚集索引：稀疏索引，就是MyISAM的主键索引。

 

聚集和非聚集是根据数据和索引是否放在一块来定义的。

 

聚集索引效率高。

 

6-1）MyISAM

 

存储引擎是形容数据表的。存储引擎是对应表的，这个非常重要。

MyISAM索引文件和数据文件是分离的，非聚集。

 

 

 

MyISAM引擎的数据主要frm、MYD和MYI的文件里。

数据在MYD中，索引在MYI中。FRM存储表结构。

 

 

 

6-2）InnoDB

 

现在主要是用的InnoDB，因为有事务。InnoDB数据和索引没有分开是聚集的。

 

InnoDB的存储文件只有两个frm和ibd。Frm是结构，ibd是存放索引和数据。

 

在InnoDB中，索引和数据放到一块，找到索引很快就能找到数据。

在InnoBD中的二级索引，也需要回表，但是主键索引是不需要回表的。

 

 

 

如果在InnoDB中没有构建主键，底层会找现有的列如果每个数都不同则将这个列作为主键，如果不存在，则会构建一个隐藏列rowId来作为主键来将数据维护为一个B+树中。因此在InnoDB中要尽量构建主键。让DB做的事情越少越好。使用自增的方式，树的构建效率更高。

 

定位索引就是比大小，所以使用整型会效率更高。

 

6-3）联合索引：

 

联合索引的底层存储结构：

索引是排好序的数据结构。

 

 

 

对于这样的联合索引，name，age，position

 

首先按照第一个字段name进行排序，如果第一个字段相同则按照第二个字段age来进行排序。以此类推。

 

使用联合索引有最左前缀优化原则。跳过前面字段，查询后面字段的话就不会走索引。

 

6-4）B树和B+树

B树：排好序，一个节点可以有多个元素

 

 

 B+树：排好序，一个节点可以有多个元素，叶节点之间有指针，非叶子节点在叶子节点上有冗余

叶子节点有了指针变为B+树。

 

 

 有主键索引的话如果插入是无序的但是显示结果是有序的，在数据插入的时候就进行排序了

 

 

 

6-4）以InnoDB为例讲述索引

 

InnoDB页：InnoDB_page是最小单位

InnoDB_page默认大小为16KB，InnoDB取数的最小单位为page。这样的话只是在第一次查询的时候使用了一次磁盘IO，后面的直接在内存中进行，速度很快。一页一页的放入内存中。就是局部性原理。

 

 

 

MySQL根据索引导入和查询数据的原理：

 

在插入的时候会按照主键的顺序来进行排序。

提高链表的性能可以使用页目录。

 

 

 

页目录的优化：

 

一个查找的主键会在页目录中进行二分查找来快速定位。

 

如果第一页的16KB满了之后，则会开辟新的一页。

 

如果插入主键为5的，此时第一页已经满了，这时需要把主键为8的移动到第二页，把主键为5的留在第一页。因此在使用InnoDB的时候尽量主键使用自增的，其他的会影响效率。

 

 

 

 

 

 

 随着页数的增多，可以新开辟一页，来存放每一页页目录的最小值。这就是使用空间来换时间的方式。

 

 

 这样就构成了B+树。这个例子为两层B+树。

 

 

 

6-5）联合索引的B+树创建

 

联合索引：

 

CREATE INDEX idx_t1_bcd ON t1(b,c,d);

 

每创建一个索引，不是就会生成一个B+树，这样会导致数据的冗余，因为原数据是一致的，可能只是顺序的不同。

因此有一个折中方案，对于联合索引存储的数据是主键。

 

 

 

 

 

然后从主键索引中去查找。

 

联合索引情况下，SELECT * FROM t1 WHERE b = 1; 表示查询1**的数据，用的是索引。

 

但是如果是SELECT * FROM t1 WHERE b > 1;则使用的是全表扫描。这种情况是可以走索引的，但是最后还是选择了全表扫描。找索引会使用很多次的回表，最后选择了全表扫描。当进行回表的次数变少的时候，上述的查询就会变成走索引。

 

7）MySQL索引的使用：

 

索引的类型：

 

7-1）键索引：主键自动的为主索引（类型primary key）

 

7-2）唯一索引：UNIUQ

 

7-3）普通索引：INDEX

 

7-4）全文索引：FULLTEXT，适用于MyISAM（一种MySQL的存储引擎）

 

对于全文索引在实际开发中考虑使用：全文搜索Solr或ElasticSearch（ES）

 

全文索引：

创建全文索引：

 

 

 插入数据：

 

 

 使用全文索引：

 

 

 

SELECT * FROM XXX WHERE match(index1,index2) against(‘key word’);

 

对于全文索引的说明：

 

1、在MySQL中fulltext索引只针对MyISAM生效。

 

2、MySQL自己提供的fulltext针对英文生效——>sphinx(coreseek)技术处理中文

 

3、使用方法是match(字段名) against(‘关键字’)

 

4、全文索引存在一个叫停止词，因为在一个文本中，创建索引是一个无穷大的数，因此，对一些常用词和字符，就不会创建，这些词，就称为停用词。

 

在创建表后，再去创建唯一索引

 

CREATE UNIQUE INDEX 索引名 ON 表名(列表)

 

也就是：

 

CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)][ASC|DESC],…);

 

7-5）索引使用：

 

7-5-1）添加索引：（两种方式）

 

（1）CREATE [UNIQUE] INDEX index_name ON tbl_name (col_name[(length)][ASC|DESC],…);

 

（2）ALTER TABLE tbl_name ADD INDEX [index_name] (index_col_name,…)

 

7-5-2）查看索引类型：

 

使用SHOW INDEXES FROM t25;

 

如果在结果中Non_unique列显示的是0，则说明是unique索引，如果是0则不是。

 

使用UNIQUE索引的前提是认为该索引的数据是不会重复的。

 

7-6）如果选择索引类型：

 

7-6-1）如果某列的值，是不会重复的，则优先考虑使用UNIQUE索引，否则使用普通索引。

7-6-2）添加普通索引方式2

 

 

 7-6-3）添加主键索引：

 

 

 

7-7）删除索引：

 

删除索引：

 

DROP INDEX id_index ON t25;

 

删除主键索引：

 

ALTER TABLE t26 DROP PRIMARY KEY;

 

7-8）修改索引：

 

修改索引：

 

先删除，再添加新的索引。

 

7-9）查询索引：

 

7-9-1）方式一

 

SHOW INDEX FROM t26;

 

7-9-2）方式二

 

SHOW INDEXES FROM t26;

 

7-9-3）方式三：

 

SHOW KEYS FROM t26;

 

7-9-4）方式四：

DESC t26;

 

 

 

7-10）创建索引的规则：

 

哪些列上适合使用索引：

 

7-10-1）较为频繁的作为查询条件字段应该创建索引。

 

7-10-2）唯一性太差的字段不适合单独创建索引，即便频繁作为查询条件。

 

7-10-3）更新非常频繁的字段不适合创建索引。

 

7-10-4）不会出现在WHERE子句中字段不该创建索引。

 

7-11）对于唯一索引：

 

unique，在建表时字段有unique就是唯一索引。

但是对于唯一索引，其对应的字段可以为null，且可以为多个，但是如果对应的字段为空串’’,则只能有一个（这个时候空串就是表示具体的内容）。空串表示具体的内容

 

 

 

7-12）使用索引的注意事项：

 

如果我们的表中有复合索引（索引作用在多列上），此时需要注意：

7-12-1）对于创建的多列索引，只要查询条件使用了最左边的列，索引一般就会被使用。

 

 

 对于上述的例子，最左表的列是dname，如果使用了dname则索引就会被使用。如果没有用dname而是用的loc，则索引不会被使用。

 

 

 

对于EXPLAIN而言：explain将告诉我们如何执行sql语句。

 

Explain可以帮助我们在不真正执行某个sql语句时，就知道mysql怎样执行，这样利于我们去分析sql指令。

 

Id：查询序列号

 

Select_type：查询类型

 

Table：查询的表名

 

Type：扫描的方式，all表示全表扫描

 

Possible_keys：可能使用到的索引

 

Key：实际使用的索引

 

Rows：该sql语句扫描了多少行，可能得到记录

 

Extra：sql语句的额外信息，比如排序方式filesort

 

7-12-2）对于使用LIKE的查询，查询如果是’%aaa’不会使用到索引，而’aaa%’和’aa%a’会使用到索引。在LIKE查询时，’关键字’最前面不能使用%或者是_这样的字符。如果一定要前面有变化的值，则考虑使用全文索引。

 

7-12-3）如果条件中有or，即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段，都必须建立索引，我们建议大家尽量避免使用OR关键字。

 

7-12-4）如果列类型是字符串，那一定要将条件中将数据使用引号引起来，否则不使用索引

 

7-12-5）如果MySQL估计使用全表扫描要比使用索引快，则不使用索引。

 

7-13）如何查看索引使用的情况：

 

SHOW STATUS LIKE ‘Handler_read%’;

 

Handler_read_first这个值越高，说明索引使用率越高。

 

Handler_read_rnd_next这个值越低，越好。这个值越高说明查询效率越低。

 

 

 

六、存储引擎

 

1）MyISAM存储：如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用MyISAM存储引擎，比如BBS中的发帖表，回复表等。

 

2）InnoDB存储：对事务要求高，保存的数据都是重要数据，建议使用InnoDB，比如订单表，账号表等。

 

3）Memory存储：比如我们的数据频繁变化，不需要入库，同时又需要频繁的查询和修改，这时我们考虑使用Memory。

 

4）MyISAM和InnoDB的区别：

 

4-1）事务安全

 

4-2）查询和添加速度

 

4-3）支持全文索引

 

4-4）锁机制（MyISAM是表锁，InnoDB是行锁）

4-5）MyISAM不支持外键，InnoDB支持外键

 

 

 

4-6）MyISAM注意事项：

 

对于存储引擎是MyISAM的数据库，如果经常做删除和修改记录的操作，要定时执行optimize table table_name，该功能对表进行碎片整理。

 

如果你的数据库的存储引擎是MyISAM，请一定记住要定时进行碎片整理。对于MyISAM而言，如果一开始创建好一个表其所占空间很大，即使删除掉其中的部分数据，那张表所占用的空间也不会变小。

 

对于这样的情况，需要定时对MyISAM数据库进行清理。

进行碎片整理前：

 

 

 进行碎片整理之后：

 

 

 

5）MySQL表类型和存储引擎：

 

基本介绍：

 

1、MySQL的表类型由存储引擎（Storage Engines）决定，主要包括MyISAM、InnoDB和Memory等。

 

2、MySQL数据库主要支持六种类型，分别是：CSV、Memory、ARCHIVE、MRG_MYISAM、MYISAM和InnoBDB

3、这六种又分为两类，一类是“事务安全型”，比如：InnoDB；其余都属于第二类，为“非事务安全型”（事务安全型指支持事务）

 

 

 

 

 

MyISAM存储引擎

 

CREATE TABLE t28(

 

id int,

 

`name` VARCHAR(32)) ENGINE MYISAM;

 

1、添加速度快

 

2、不支持外键和事务

 

3、支持表级锁

 

因为不支持事务，所以不能回退。

 

 

 

Memory存储引擎：

 

1、数据存储在内存中[关闭了MySQL服务，数据丢失，但是表结构还在]

 

2、执行速度很快（没有IO读写）

 

3、默认支持索引（Hash表）

 

CREATE TABLE t28(

 

id int,

`name` VARCHAR(32)) ENGINE MEMORY;

 

 

 对于Memory存储引擎的一个应用场景，在线[聊天]软件的状态，离线，在线，挂起等等。

 

 

 

修改存储引擎：

 

ALTER TABLE `表名` ENGINE = 存储引擎;

 

 

 

七、事务

 

1）事务包括：事务管理、savepoint、rollback、commit、隔离级别和ACID。

 

2）什么是事务：事务用来保证数据的一致性。

事务用于保证数据的一致性，它由一组相关的dml语句组成，该组dml语句要么全部成功，要么全部失败。如：转账就要用事务来处理，用以保证数据的一致性。

 

 

 

3）事务和锁：

 

当执行事务操作时（dml语句），MySQL会在表上加锁，防止其他用户修改表的数据，这对用户来说是非常重要的。

 

4）MySQL数据库控制台事务的几个重要操作：

 

1、start transaction --开始一个事务

 

2、savepoint 保存点名 --设置保存点

 

3、rollback to 保存点名 --回退事务

 

4、rollback --回退全部事务

 

5、commit --提交事务，所有的操作生效，不能回退

 

5）事务的重要操作和概念：

两个前提：开始事务和设置保存点。

 

 

 

举例子：

 

5-1）创建一张测试表：

 

CREATE TABLE t27 (

 

id INT,

 

`name` VARCHAR(32)

 

);

 

5-2）开始事务

 

START TRANSACTION;

 

5-3）设置保存点

 

SAVEPOINT a

 

5-4）执行DML操作

 

INSERT INTO t27 VALUES(100, ‘tom’);

SELECT * FROM t27;

 

 

 

5-5）设置保存点

 

SAVEPOINT b

 

5-6）执行DML操作

INSERT INTO t27 VALUES(200, ‘jack’);

 

 

 

5-7）回退到b

ROLLBACK TO b

 

 

 

5-8）继续回退到a

ROLLBACK TO a

 

 

 

如果直接使用ROLLBACK则直接回退到事务开始的状态。

 

如果使用了COMMIT语句提交了事务则不能回退，没有后悔药可以吃了。

6）回退事务：

保存点是事务中的点，用于取消部分事务，当事务结束时（COMMIT），会自动的删除该事务所定义的所有保存点。当执行回退事务时，通过指定保存点可以回退到指定的点。

 

7）提交事务：

 

使用COMMIT语句可以提交事务。当执行了COMMIT语句后，会确认事务的变化、结束事务、删除保存点、释放锁，数据生效。当使用COMMIT语句结束事务后，其他会话[其他连接]将可以查看到事务变化后的新数据[所有的数据就正式生效]。（如果多个客户对一个MySQL数据库来进行操作，如果一个客户端（1）对MySQL数据库进行操作但是还没有提交前，另一个客户端（2）是不一定能够看到1的全部操作，因为存在隔离级别）。

 

8）事务细节讨论：

 

1、如果不开始事务，默认情况下，dml操作是自动提交的，不能回滚。

 

2、如果开始一个事务，但是没有创建保存点，可以执行rollback，默认就是回到事务开始的状态。

 

3、可以在事务没有提交之前，创建多个保存点。SAVEPOINT aaa; dml SAVEPOINT bbb;

 

4、可以在事务没有提交之前，选择回退到哪个保存点。

 

5、MySQL的事务机制需要innodb的存储引擎才可以使用，myisam不好使。Innodb存储引擎支持事务。

 

6、开始一个事务START TRANSACTION，SET AUTOCOMMIT = off。这两种方式都可以。

 

9）事务隔离级别：

MySQL隔离级别定义了事务与事务之间的隔离程度。

 

 

 

演示MySQL的隔离级别：

 

1、开了两个MySQL控制台

 

2、查看当前MySQL的隔离级别

 

SELECT @@tx_isolation;

 

 

 

mysql> SELECT @@tx_isolation

 

    -> ;

 

+-----------------+

 

| @@tx_isolation  |

 

+-----------------+

 

| REPEATABLE-READ |

 

+-----------------+

 

 

 

3、把其中一个控制台的隔离级别设置为Read uncommited

 

SET SESSION TRANSACTION LEVEL READ UNCOMMITED

 

注意：隔离级别是与事务相关的，离开事务就不要谈隔离级别。

 

 

 

4、两个控制台都开启事务

 

 

5、在REPEATABLE-READ的控制台创建一个表

 

 

 

 

 创建好表后，在两个控制台都可以对其进行查询，因为目前表中还没有数据，因此两个控制台的查询结果均为空。

 这时在隔离级别为REPEATABLE-READ的工作台中向表中插入一条数据，但是没有提交，此时在隔离级别为READ UNCOMMITED的工作台中的表可以查询到刚才插入其中的一条数据。

 

 

 此时在左边的工作台进行修改和插入，此时进行了提交操作，此时在右边的工作台会产生不可重复读和幻读的现象。因为左边工作台的提交而影响了右边工作台的相关操作。对于右边工作台，其不需要看到左边工作台对数据表操作的结果。右边工作台只是想得到其在连接到数据表的那一刻的数据，而不想要在那个时间节点之后的被左边工作台修改后的数据。这个概念针对与某一个事件点来说，在某个时间点之后会怎么样的变化。

 

 

 此时修改下右边工作台的隔离级别为READ COMMITTED：

 

 

 此时由于右边工作台隔离级别的增强，从而在右边工作台进行查询时没有看到左边工作台所插入的新的数据。

 

 

 但是在隔离级别为READ COMMITTED的情况下，还是会出现不可重复读和幻读的现象。左边的工作台提交了事务。

 

 

 可串行化Serializable 会加锁

 

 

 

 

 

 此时左边工作台没有提交事务，这时右边工作台的查询时没有反应的，一直在那里等待直到超时，原因是加锁了。左边工作台一提交则右边工作台会查到相应的结果。

 

 

 

MySQL事务隔离级别：

1、查看当前会话隔离级别：

SELECT @@tx_isolation;

2、查看系统当前隔离级别

SELECT @@global.tx_isolation;

3、设置当前会话隔离级别

SET SESSION TRANSACTION LEVEL READ UNCOMMITED（可以更换，有4种可以选择的方案）

4、设置系统当前隔离级别

SET GLOBAL TRANSACTION LEVEL [你设置的级别]

5、MySQL默认的事务隔离级别是repeatable read，一般情况下，没有特殊要求，没有必要修改（因为该级别可以满足绝大部分项目要求）

10）MySQL事务ACID：

事务的ACID特性：

1、原子性（Atomicity）：

原子性是指事务是一个不可分割的工作单位，事务中的操作要么都发生，要么都不发生。

2、一致性（Consistency）：

事务必须是数据库从一个一致性状态变换到另一个一致性状态。通过提交来完成，将一个事务提交了数据库才会从一个一致性状态变换到另一个一致性状态。

3、隔离性（Isolation）：

事务的隔离性是多个用户并发访问数据库时，数据库为每一个用户开启的事务，不能被其他事务的操作数据所干扰，多个并发事务之间要相互隔离。

4、持久性（Durability）：

持久性是指一个事务一旦被提交，它对数据库中数据的改变是永久性的，接下来及时数据库发生故障也不应该对其有任何影响。

八、视图

视图和对应真实表（基表）之间的关系

 

 

 

对视图的总结：

1、视图是根据基表（可以是多个）来创建的，视图是虚拟的表。

2、视图也有列，数据来源于基表。

3、通过视图可以修改基表的数据。

4、基表的变化也会影响到视图的数据。

 

视图的基本使用：

1、CREATE VIEW视图名 AS SELECT语句

2、ALTER VIEW视图名 AS SELECT语句

3、SHOW CREATE VIEW 视图名

4、DROP VIEW视图名1,视图名2

5、DESC 视图名

 

注意事项：

1、创建视图之后，到数据库中去看，对应视图只有一个视图结构文件（形式：视图名.frm）

2、视图的数据变化会影响到基表，基表的数据变化也会影响到视图[insert update delete]

3、视图中可以再使用视图，数据仍然来源与基表。

视图最佳实践：

1、安全

2、性能

3、灵活

 

 

 

 

 

 

对于灵活而言，如果不想用表A和表B，可以将表A和表B的有用的数据映射到一个视图中，从而可以实现上述表A和表B的替代。

 

九、MySQL的权限管理：

root用户可以给其他用户授权。

基本语法：

授予用户权限：

GRANT 权限列表 ON 库.对象名 TO ‘用户名’@’允许登录的位置’ [identified by ‘密码’]

 

 

 

回收用户权限：

REVOKE 权限列表 ON 库.数据对象名 FROM ‘用户名’@’允许登录的位置’;

 

权限生效指令：

如果权限没有生效，可以执行下面命令

FLUSH PRIVILEGES;

 

 

 赋予testdb.news SELECT和INSERT的权限。

 

 

 

对于REVOKE而言，收回所有权限的话，可以是把所有的权限都列出来，也可以是用ALL来代替。

 

MySQL管理：

1、在创建用户的时候，如果不指定host，则为%，%表示所有IP都有连接权限。所有IP。

CREATE USER XXX;

2、也可以这样：

CREATE USER ‘XXX’@’192.168.1.%’ 表示XXX用户在192.168.1.*的IP可以登录MySQL。某一个IP段。

3、在删除用户的时候，如果host不是%，需要明确指定’用户’@’host值’

所有IP

 

 

 IP段：

 

 

 删除host为非%的用户：

 

 

 

十、MySQL数据库优化常用技术：

对MySQL优化是一个综合性的技术，主要包括：

a：表的设计合理化（符合3NF）

b：添加适当的索引

c：分表技术（水平分割、垂直分割）

d：读写分离[写：UPDATE/DELETE/ADD]

e：存储过程[模块化编程，可以提高速度]

f：对MySQL配置优化[配置最大并发数，修改my.ini文件；调整缓存的大小]（max_connections = 100（默认），最好为一般网站调整到1000左右）

g：MySQL数据库硬件升级

h：定时去清除不需要的数据，定时进行碎片整理（MyISAM）

下面为具体的措施：

1）SQL语句本身的优化：

问题是：如何从一个大项目中，迅速定位执行速度慢的语句。（定位慢查询）

1、首先我们了解MySQL数据库的一些运行状态如何查询（比如想知道当前mysql运行的时间/一共执行了多少次select/delete/等/还有当前连接的数目）

 

存储引擎的选择要根据实际的那种类型的语句偏多来进行进一步的选择。

 

SHOW STATUS;（返回的结果有很多）

常用的：

SHOW STATUS LIKE ‘uptime’; （查看运行的时间）

SHOW STATUS LIKE ‘com_select’;（查看select的次数）（其中select可以换成insert、update、delete等）

 

SHOW [session|global] STATUS LIKE …，如果不写[session|global]的话默认是session对话，指取出当前窗口的执行，如果想看到所有的话（从mysql启动到现在），则应该是global。

SHOW STATUS LIKE ‘connections’;（查看连接的客户端数）

SHOW STATUS LIKE ‘slow_queries’;（查询显示慢查询次数）

2、如何定位慢查询：

默认情况下，mysql认为10秒才是一个慢查询。

修改MySQL的慢查询：

mysql> SHOW VARIABLES LIKE 'long_query_time';

+-----------------+-----------+

| Variable_name   | Value     |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

 

修改：

将慢查询时间修改为1s

SET long_query_time = 1;

mysql> SHOW VARIABLES LIKE 'long_query_time';

+-----------------+----------+

| Variable_name   | Value    |

+-----------------+----------+

| long_query_time | 1.000000 |

+-----------------+----------+

2）压力测试构建大表：

构建大表——>大表中记录有要求，记录是不同才有用，否则测试效果和真实相差很大。

为了存储过程能够正常执行，我们需要把命令执行结束符修改。

delimiter $$

这时对于SELECT * FROM table$$才能执行结束。或者是SELECT * FROM table;$$

 

 

 

数据库可以有各种数据对象：表、存储过程、视图、函数、触发器

存储过程如下：

 

 

 

调用存储过程：使用CALL来调用

CALL insert_emp(100001, 4000000);

进行慢查询：

SHOW STATUS LIKE ‘slow_queries’;

结果显示有两次超过了慢查询的设定的时间。

第一次为插入数据，第二次为查询某具体数据。

 

如何把慢查询的SQL记录到我们的日志中去，在默认的情况下，我们的MySQL不会记录慢查询，需要在启动的时候，指定记录慢查询才可以。

 

 

 如果设置了慢查询日志，需要先关闭MySQL再启动。如果启用了慢查询日志，默认把这个文件放在my.ini文件中记录的位置

 

 

 

这个地址没有特殊情况不要轻易的做变动，对导致引擎和索引的失效，变动不是简单的文件的变化的问题。

Mysqld.exe –safe-mode –slow-query-log是一条指令

日志中的慢查询记录：

 

 

 

3）MySQL表的分割：

一个表太大了，需要分割。

分享一句话：我们在提供检索时，应该按照业务的需求，找到分表的标准，并在检索页面，约束用户检索的方式。如果有大表检索的需求，也是少数。

水平分割：水平分割是把同一张表拆分到不同的数据库中去，按照地点、年龄、季节等来进行分割。水平拆分不是将表的数据做分类，而是按照某个字段的某种规则来分散到多个库之中，每个表中包含一部分数据。简单来说，我们可以将数据的水平切分理解为是按照数据行的切分，就是将表中 的某些行切分到一个数据库，而另外的某些行又切分到其他的数据库中。

垂直分割：如果一个表中的某个字段很大，因此其会对查询速度有很大的影响，因此我们可以把这个字段单独提取出来，放到另外一个表中。垂直分割是将某些字段拿出去。

把某个表的某些字段，这些字段，在查询时，并不是关心的，但是数据量很大，建议可以把这些字段单独提取出来放到另外一张表，从而提高效率。但是提出来的表和原表的要有一个关联关系。

 

 

 

选择适当的字段类型，特别是主键。

选择字段的一般原则是保小不保大，能用占用字节小的字段就不用大字段。比如主键，建议使用自增类型，这样省空间，空间就是效率。

tinyint(1)，int(1)，smallint(4)的这样的小括号里有数字表示的含义：表示零填充，如果括号里是2，但是输入的数据为1的话，则最后数据库里的结果是01；同理如果括号里是3，但是输入的数据为2的话，则最后数据库里的结果是002。但是需要指定zerofill，如果没有指定的话则没有任何的显示。

 

 

 零填充：

 

 

 

关于网站图片和视频存放：在数据库中一般只存放路径，资源是放在文件系统（往往配合独立的服务器）

4）数据库的分库分表：

4-1）为什么要分库分表

分库分表更多的是对关系型数据库数据存储和访问机制的一种补充，而不是颠覆。

1）换数据库，nosql例如MongoDB

2）sql、索引、字段优化

3）读写分离

4）分库分表

 

4-2）什么是分库分表

去中心化

 

4-3）分库分表的几种方式

垂直：

（1）每个库（表）的结构都不一样

（2）每个库（表）的数据都（至少有一列）都一样

（3）每个库（表）的并集是全量数据

优点：

（1）拆分后业务清晰（专库专用按业务区分）

（2）数据维护简单，不同业务放到不同的机器上

缺点：

（1）如果单表的数据量大，写读压力大。

（2）受某种业务来决定、或者被限制。也就是说一个业务往往会影响到数据库的瓶颈（性能问题）。

（3）部分业务无法关联join，只能通过Java程序接口去调用，提高了开发复杂度（商品、订单信息、会员信息）

 

 

 

需要分为分库和分表两个方面。

一般一个mySQL数据库就是支持3~500W的数据量。

垂直拆分数据库：按照业务进行拆分，适合在项目开始之前

 

 

 垂直拆分数据表：其实也是按照业务进行拆分

 

 

 

水平

水平分库示意图

 

 水平分表示意图：水平拆分保证均衡性

 

 

水平：适合在项目开展之后

水平（横向）分库：

（1）每个库（表）的结构都一样

（2）每个库（表）的数据都不一样

（3）每个库（表）的并集是全量数据

优点：

（1）单库（表）的数据保持在一定的量（减少），有助于新能提高

（2）提高了系统的稳定性和负载能力

（3）拆分的表的结构相同，程序改造较少

缺点：

（1）数据的扩容很有难度维护量大

（2）拆分规则很难抽象出来

（3）分片事务的一致性的问题部分业务无法关联join，只能通过java程序接口去调用

互联网 要求对用户层面多一些，一条订单数据可能被多次使用，也就是查多改少。

 

4-4）分库分表带来的问题

（1）分布式事务，ACID无法保证跨库事务

（2）跨库join查询

（3）分布式全局唯一ID

（4）开发成本 程序员要求高

 

5）开源框架：

jdbc直连层：

shardingsphere、tddl

proxy代理层：

mycat、mysql-proxy

代理层支持跨语言

 

shardingsphere解决分库分表带来的问题：

jdbc是在内存中进行处理：

去中心化

 

 

proxy是跨线程进行处理：

中心化

 

 

Jdbc在内存中通讯较少，只需2次

Proxy需要4次

 

 使用proxy对于开发人员而言开发成本和难度降低了

十一、InnoDB架构

 

 

1）Buffer Pool内存结构详解与优化策略分析

磁盘里的数据会复制一份到Buffer Pool。

在Buffer Pool的内存空间也是128M，创造出一个以页为单位的二维数组。页从磁盘复制到Buffer Pool中。也就是说同样一个数据既在磁盘中存在又在Buffer Pool中存在。经过UPDATE操作之后Buffer Pool中的页会持久化复制到磁盘中。经过UPDATE操作之后，原先在Buffer Pool中的页，会空出。

Buffer Pool中会存在一个free链表来管理空白页。Buffer Pool中有多少空白页，free链表就有多少节点。

 

 

2）在经过UPDATE命令之后，在Buffer Pool中会产生脏页（此时还没有commit）。

在后台的线程中来找Buffer Pool中的脏页，需要flush链表。Flush链表的控制块会指向脏页。

3）如果Buffer Pool中128M的内存空间满了，则可以进行淘汰。使用的淘汰策略是最近最少。LRU链表也是存在一些控制块。在LRU链表中，越是靠前的控制块，则表示对应的那一页是最近被访问的，越是在后面则表示在之前被访问的。在实际的操作过程中，LRU链表的前5/8被称为热数据区，后3/8被称为冷数据区。每次从磁盘中取出一个页，先进入到冷数据区，然后冷数据区最后一个控制块去除。如果在冷数据区，两次访问（对某个页的第一次访问和第二次访问）的时间间隔超过了1秒，则将该页从冷数据区放入到热数据区。如果小于1秒的话则仍然存在于冷数据区。

4）进行UPDATE操作：

4-1）第一种方式：

1、修改Buffer pool里面的页数据—脏页

2、生成一个redo log，在log buffer中

3、redo log持久化，顺序型（当事务提交的时候才会进行持久化）

4、bin log持久化

5、undo log反向操作日志，保存update之后修改的某个字段的修改的之前的值。来支持回滚操作。

6、修改成功

如果MySQL挂掉了，需要使用redo log对数据进行修复。

在事务中的时候，如果没有提交，之前的操作是在log buffer中，如果提交了的话才会对log进行持久化操作。

对于redo log：redo log是用来在磁盘上进行持久化操作

（1）Redo Log：

Ib_logfile1初始化大小为48M

Ib_logfile2 初始化大小为48M

如果都满了则存在一个技术概念check point检查点

如果Ib_logfile1和Ib_logfile2都满了则触发一次检查点。这时找Buffer Poll中哪一页与redo log里面是对应的。然后将在Buffer Poll中的页持久化到磁盘上来。将logfile中的所有的对应的页全部持久化到磁盘中来，才会加入新的redo log。

这里存在一个调优的办法就是将redo log的大小调大，或者将日志文件个数调多。

（2）redo log参数化设置

 

 

1表示的操作有write和flush

2表示的操作只有write，flush工作交给操作系统（操作系统缓冲区）

4-2）第二种方式：

1、修改Buffer pool里面的页数据—脏页

2、修改磁盘中的页数据

3、修改成功

5）对于一个事务，除了会生成redo log还会生成bin log和undo log

Bin log数据库主从。

Bin log与redo log的区别：

Bin log不是InnoDB里的概念，而是MySQL里的概念。Redo log属于InnoDB里的概念。

在InnoDB里面主要记录在一页里的东西。Redo log记录某一页里的物理地址。

Bin log里面则是记录一个sql语句。

因此Redo log对于数据的恢复和备份效率要比Bin log高。

Bin log主要涉及主从。

6）Buffer Pool调节：

Buffer Poll的大小可以调节，调大热数据在内存中多。

调优：内存，线程，磁盘 （主要是分布在这三个方面）

 

整理完毕，自己这段时间的总结，也希望对看到的人有用，欢迎大家来讨论。