MySql进阶
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySql进阶,小编现在分享给大家,供广大互联网技能从业者学习和参考。文章包含28782字,纯文字阅读大概需要42分钟。
内容图文
MySql进阶
Mysql
一、MySql逻辑架构
1. MySql逻辑架构介绍
和其它数据库相比,MySQL有点与众不同,它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上,插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
-
连接层
最上层是一些客户端和连接服务,包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念,为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。如,navicat -
服务层
第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如过程、函数等。在该层,服务器会解析查询并创建相应的内部解析树,并对其完成相应的优化如确定查询表的顺序,是否利用索引等,最后生成相应的执行操作。如果是select语句,服务器还会查询内部的缓存。如果缓存空间足够大,这样在解决大量读操作的环境中能够很好的提升系统的性能。 -
引擎层
存储引擎层,存储引擎真正的负责了MySQL中数据的存储和提取,服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同,这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB -
存储层
数据存储层,主要是将数据存储在运行于裸设备的文件系统之上,并完成与存储引擎的交互。
2.数据库引擎
通过show engines可以查询数据库支持的所有引擎, 我们常用的引擎有MyISAM和InnoDB。下面我们主要讨论这两个引擎。
查询结果中
Engine参数指存储引擎名称:
Support参数说明MySQI是否支持该类型引擎;
Comment参数表示对该引擎的评论:
Transaction参数表示是否支持事务处理:
XA参数表示是否分布式交易处理的XA规范:
Savepoints参数表示是否支持保存点,以方便事务的回滚操作
那么怎么看mysql当前默认的存储引擎:
mysql> show variables like ‘%storage_engine%’;
上面显示,默认存储引擎是InnoDB,当前存储引擎也是InnoDB。
那么这两个存储引擎有什么区别呢?
现在最常用的存储引擎是InnoDB,它从MySQL 5.5.5版本开始成为了默认存储引擎。
黑色背景为重点
扩展:
那么阿里用什么呢,在几年前,阿里的确也用Mysql。
Percona 为 MySQL 数据库服务器进行了改进,在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具;另外有更多的参数和命令来控制服务器行为。
该公司新建了一款存储引擎叫xtradb完全可以替代innodb,并且在性能和并发上做得更好,
阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。
AliSql+AliRedis
3.Sql执行顺序
笛卡尔积大家都学过,复习一下,两张表,T1 5条记录,T2 8条记录,执行select * from T1,T2,一共有多少条,很简单5*8=40条。
这么多条数据,肯定可以把数据都查出来,但是数据太乱了,因此需要where过滤,那么有没有一条公式能让我们写出标准的sql语句呢。
上图就是写Sql语句的规范,按照这个规范可以写出好的sql语句。
不知道大家有没有思考过一个问题,我们写sql代码的时候,先select … 然后在from…,那么计算机执行这条语句的时候也是这样的顺序吗?
其实机读的时候的顺序是从from开始,因为计算机需要知道你要操作哪个表。
整理的鱼刺图如下。
二、事务
1. 事务概览
事务由单独单元的一个或多个SQL语句组成,在这个单元中,每个MySQL语句是相互依赖的。而整个单独单元作为一个不可分割的整体,如果单元中某条SQL语句一旦执行失败或产生错误,整个单元将会回滚。所有受到影响的数据将返回到事物开始以前的状态;如果单元中的所有SQL语句均执行成功,则事物被顺利执行。
三大关键点:
- 在MySQL中只有使用了Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性,保证成批的SQL语句要么全部执行,要么全部不执行。
- 事务用来管理insert,update,delete语句,也即写操作才用到事务
事务控制
在MySQL命令行的默认设置下,事务都是自动提交的既后面自动加一条COMMIT,即执行SQL语句后就会马上执行COMMIT操作。因此要显式地开启一一个事务务须使用命令BEGIN或START,TRANSACTION或者执行命令SET AUTOCOMMIT=O,用来禁止使用当前会话的自动提交。
MYSQL事务处理主要有两种方法:
- 用BEGIN, ROLLBACK, COMMIT来实现
(1)BEGIN或START TRANSACTION开始一个事务
(2)ROLLBACK事务回滚
(3)COMMIT事务确认 - 直接用SET来改变MySQL的自动提交模式:
(1)SET AUTOCOMMIT=O禁止自动提交
(2)SET AUTOCOMMIT=1开启自动提交
2. 数据一致性
脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供定的事务隔离机制来解决。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上“串行化”进行,这显然与“并发”是矛盾的。
同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。
Mysql支持4种事务隔离级别。
Mysql默认命事务隔离级别为: REPEATABLE READ
查看当前的隔离级别两种方法:
每启动一个mysql程序,就会获得一一个单独的数据库连接.每个数据库连接都有一
个全局变量@@tx_isolation,表示当前的事务隔离级别。
- 查看当前的隔离级别: SELECT @ @tx isolation;
- 查看当前数据库的事务隔离级别: show variables like ‘tx_isolation’;
SELECT @ @tx isolation;
show variables like ‘tx_isolation’;
3. 代码演示数据一致性
SET SESSION TRANSACTION ISOL ATION LEVEL READ UNCOMMITTED; --未提交读
SET SESSION TRANSACTION ISOL ATION LEVEL READ COMMITTED; --已提交读
SET SESSION TRANSACTION ISOL ATION LEVEL REPEATABLE READ; --可重复读
SET SESSION TRANSACTION ISOL ATION LEVEL SERIALIZABLE; --可序列号
未提交读- READ UNCOMMITED
首先我们打开两个Sql窗口,分别将事务级别设置成READ UNCOMMITED
接着我们在左窗口插入一条数据(注意,没有COMMIT),由于READ UNCOMMITED的特性,右窗口也可以得到数据,所以右窗口拿着数据去办事了,在右窗口得到数据之后,左窗口rollback(此时仍未提交),rollback之后,左窗口的数据已经修改,但是右窗口却已经拿着之前错误的数据办事去了。
举个例子,小明抄班长考试题,班长写一个,小明抄一个,直到小明把所有的题都抄完了,交卷了,这时候班长发现好多题都做错了,要逐个修改答案。最后的结果就是,班长100分,小明挂科。
对于这种读到错误的数据,叫做脏读。
由于左边窗口回滚了,所以刚刚插入的数据也没有了,此时右边窗口在select,会发现和上次查询的结果不一样,这就叫做不可重复读。
举个例子,小明抄班长考试题,小明问班长第一题选什么,班长说A,过了一会班长修改了答案,小明此时闲的无聊问班长,为什么第一题选A啊,班长说,我选的B啊,没选A,此时小明蒙了,不知道这道题到底选A还是选B。
对于这种同一事务,多次查询同一资源但是结果却不同的现象,叫做不可重复读。
以提交读- READ COMMITED
首先我们打开两个Sql窗口,分别将事务级别设置成READ COMMITED
我们可以看到,左窗口插入了一条数据,但是没提交。
右窗口查询tx表,什么也没查到。
区别于 READ UNCOMMITED, 我们发现READ COMMITED没有有脏读现象,所以READ COMMITED避免了脏读。
当左窗口COMMIT提交之后,右窗口就可以查到数据了,既事务提交之后,其他会话才能访问此次事务的数据。
但是我们也发现,右窗口在同一事务对同一资源查询却查询到了不同的结果,因此存在不可重读的现象。
可重复读- READ COMMITED
首先我们打开两个Sql窗口,分别将事务级别设置成REPEATABLE READ
右窗口首先查询数据,发现表中存在一个数据,‘1’, 左窗口在一个事务中新增一条数据,‘2’,此时提交事务,右窗口再次查询数据,发现还是1。
在同一事务中,查询同一资源多次,得到的结果是一样的,因此REPEATABLE READ可以避免不可重复读。只有当前事务提交之后,再次查询,才能查到更新后的数据。
但也正是这种现象,导致了左窗口在事务提交之前明明插入了一条数据,但是右窗口却查不到,这就仿佛产生了幻觉,这种现象叫做幻读。
脏读也可以避免,就不演示了。
可序列化-SERIALIZABLE;
首先我们打开两个Sql窗口,分别将事务级别设置成SERIALIZABLE。
哎,世界上最难过的事情就是解决了旧的问题又会产生新的问题。
左窗口插入了一条数据,‘3’,此时还没有提交事务,这时右窗口查询,发现处于阻塞等待状态。这时候大家可能猜到为什么了。那就是冰糖胡芦,吃了第一个才能吃第二个。
当左窗口COMMIT之后,右窗口才查出数据。很明显,这样的机制使得上述三种麻烦都不会产生,但是对于并发效率是极其低的。
三、join
1. join概览
Join分为左连接,右连接,内连接,A表独有,B表独有,全连接,AB各有。
有点糊涂?没关系,我们用例子来介绍Join。
参考Sql语句,所用引擎都为INNODB。
CREATE TABLE `tbl_dept` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`deptName` VARCHAR(30) DEFAULT NULL,
`locAdd` VARCHAR(40) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
CREATE TABLE `tbl_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(20) DEFAULT NULL,
`deptId` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fk_dept_id` (`deptId`)
#CONSTRAINT `fk_dept_id` FOREIGN KEY (`deptId`) REFERENCES `tbl_dept` (`id`)
) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;
INSERT INTO tbl_dept(deptName,locAdd) VALUES('RD',11);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('HR',12);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MK',13);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('MIS',14);
INSERT INTO tbl_dept(deptName,locAdd) VALUES('FD',15);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z3',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z4',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('z5',1);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w5',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('w6',2);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s7',3);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s8',4);
INSERT INTO tbl_emp(NAME,deptId) VALUES('s9',51);
部门表,id,部门名,楼层
员工表,id, name, 部门id
分别对这些表进行插入操作。
查询部门表
查询员工表
简单解释下插入的表
在部门表中,一共有4个部门,ID为5的部门没有被引用,因为他是一个特殊的部门,就像老板的部门是8888一样。
在员工表中,ID1-7为公司正式员工,ID为8的员工为试用员工,故分配临时部门。
在演示之前,我们先对两个表进行笛卡尔积查询,发现
查询部分
我们发现40条记录,结果正常。
现在我们开始演示
2.内连接
问题:求出公司所有正式员工的信息。
提示:部门表没有被正式员工引用的部门和实习员工都不应该显示出来,故求A∩B。
如图所示
3.左连接
问题:展示出公司所有员工信息。
提示:员工表除了正式员工外,试用员工也应该查询出来,如果没有对应部门,那部门信息应该留空。故应该取员工表独有部分,和A∩B,故就是求A表所有信息。
如图所示:
4.右连接
与左连接相反,不做解释。
5.A表独有
问题:求出没有被分配正式部门的员工
提示:就是试用员工,他只被分配了一个临时部门,实际上部门表并没有他引用的部门。
如图所示:
6.B表独有
与A表独有相反,不做解释。
7.全连接
问题:求出AB两表所有信息。
提示:特殊部门和试用员工也要查出来。
如图所示
咦?怎么报错了,看起来和图上的语法一样啊。是不是哪里写错了?其实不是,按照SQL99的规范,Mysql不支持FULL OUTET的关键字,那是不是就没办法了?
解决方案如图所示
我们发现实际上,全连接就是A的独有+B的独有,但是我们也知道,A和B连个独有叠加在一起,相交的部分会重复所以要对它们去重。
参考上图,第一行是左连接,也就是A的独有,第三行是右连接是B的独有,union是合并加去重。
8.AB各有
问题:找出实习员工以及特殊部门。
提示:就是找出AB独有去掉AB共有,代码实现原理和全连接相反。
如图所示
四 、PL/SQL - 函数和存储过程
1. 函数和存储过程简介
不知道大家的数据库里面有没有千万条数据。如果你想在你的数据库里插入千万条数据怎么作?一条一条插?还是用JAVA写个循环?
那日常中我们非常常用的一个需求,比如更新A表之后,B、C两个表的也要随着A表更新而更新那怎么做?
例如同一插入操作,我希望在1、3、5 周插入A表,2、4、6 周插入B表,我又该怎么做。
有人可能会问了,你说这些东西,涉及到了逻辑操作,我们日常学到SQL都是增删改查啊,这些问题可能要借助编程语言实现了。
其实完全不用,Mysql支持PL/SQL,简单的说就是可以让你的SQL做简单的逻辑操作,以此代替java代码,是不是很酷。
既然已经说过了,SQL做简单的逻辑操作,那是不是它也可以向其他语言一样,可以有函数呢,别说还真有,Msql支持函数和存储过程。
和java的方法一样,PL/SQL的函数也支持参数,返回值等。
函数是有返回值的逻辑集合体。如now()/max()/min()… 这些都是系统自带的。
由于Mysql对自己的过度保护,我们自定义的函数( sql封装体 ) Mysql是不认可的,我们需要将给Mysql信任白名单告知。
那么存储过程是什么呢?存储过程其实就是没有返回值的函数。
下面通过例子一起学习函数和存储过程的用法。
那么我们依然用老办法,实践是检验真理的最好方式。上代码。
# 新建库
create database bigData;
use bigData;
#1 建表dept
CREATE TABLE dept(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
dname VARCHAR(20) NOT NULL DEFAULT "",
loc VARCHAR(13) NOT NULL DEFAULT ""
) ENGINE=INNODB DEFAULT CHARSET=GBK ;
#2 建表emp
CREATE TABLE emp
(
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/
hiredate DATE NOT NULL,/*入职时间*/
sal DECIMAL(7,2) NOT NULL,/*薪水*/
comm DECIMAL(7,2) NOT NULL,/*红利*/
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/
)ENGINE=INNODB DEFAULT CHARSET=GBK ;
简单的建两张表。
本次的目标是,随机往表里插入1000W数据
接下来我们就写函数吧!
等等…
好像添加一个白名单。
设置参数log_bin_trust_function_creators
为什么要设置这个参数?
当开启二进制日志后(可以执行show variables like 'log_bin’查看是否开启),
如果变量log_bin_trust_function_creators为OFF,那么创建或修改存储函数就会报
“ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
or READS SQL DATA in its declaration and binary logging is enabled
(you *might* want to use the less safe log_bin_trust_function_creators variable)”
这样的错误。
【解决方法】
show variables like ‘log_bin_trust_function_creators’;
set global log_bin_trust_function_creators=1;
这样添加了参数以后,如果mysqld重启,上述参数又会消失,永久方法:
windows下my.ini[mysqld]加上log_bin_trust_function_creators=1
linux下 /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1
那么我们按照操作一步一步来。
我们的二进制日志已经开启。如图
查询log_bin_trust_function_creators,发现是关闭的,不可以创建函数。
接下来我们把他开启。
再次查询发现已经是NO了。
我们PL/SQL函数的语法和JAVA有很多相近之处,我们写一个随机生成字符串的函数。
2. 使用自定义函数随机生成字符串
#换行符的分割标识,原来是以;结尾,由于函数中也要用到;所以用$$代替。
DELIMITER $$
#创建函数 rand_string(n INT), 参数为INT型的变量n,返回值类型为VARCHAR长度为255
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
#函数的开始
BEGIN
#定义varchar型变量,chars_str ,初始化为26个字母,用来后期随机从中挑选字符。
DECLARE chars_str VARCHAR(100) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
#定义一个varchar型的return_str,默认为空字符串,用来返回结果。
DECLARE return_str VARCHAR(255) DEFAULT '';
#定义一个变量i,默认为0
DECLARE i INT DEFAULT 0;
#循环,条件为i<n,n为想返回多少位随机数,每次循环生成一个随机数
WHILE i < n DO
#CONCAT是将后面的参数追加到return_str这个参数中
#SUBSTRING和java一样,就是切割字符串,chars_str为源串,
#1+RAND*52为生成52个随机数
#FLOOR(1+RAND*52),由于1+RAND*52生成的书是浮点数,故用FLOOR来取整
#floor函数在官方的解释是,返回小于等于该值的最大整数。
#这句的作用就是将随机生成的一个字符追加到return_str
SET return_str =CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
#i++
SET i = i + 1;
#循环体结束
END WHILE;
#返回随机字符串
RETURN return_str;
END $$
#假如要删除
#drop function rand_string;
运行结果如下
我们在写一个随机生成数字
3. 使用自定义函数随机生成数字
#用于随机产生部门编号
DELIMITER $$
CREATE FUNCTION rand_num( )
RETURNS INT(5)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(100+RAND()*10);
RETURN i;
END $$
#假如要删除
#drop function rand_num;
运行结果
函数是有返回值的,因此我们创建了具有返回值的随机字符串和随机数字,我们可以用这些随机数据作为我们即将插入需要动态变化的资源字段。那么批量插入字段是不用返回值的,因此我们下一步采用存储过程插入数据。
4. 使用存储过程插入员工表
我们创建一个插入员工表 的存储过程,insert_emp();
DELIMITER $$
#创建存储过成,START为员工开始编号,max_num为插入多少条数据
CREATE PROCEDURE insert_emp(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
#set autocommit =0 把autocommit设置成0 关闭自动提交
SET autocommit = 0;
REPEAT #存储过程中的循环
SET i = i + 1; #游标
#插入员工
#员工号对应Start+i如100,101,102
#员工姓名为我们自己写的随机生成的6位字符串
#部门编号为随机生成的3位数
INSERT INTO emp (empno, ename ,job ,mgr ,hiredate ,sal ,comm ,deptno ) VALUES ((START+i) ,rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
#循环终止条件
UNTIL i = max_num
#循环体结束
END REPEAT;
#手动提交
COMMIT;
END $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_emp;
5. 使用存储过程插入部门表
然后在定义一个部门插入存储过程
#执行存储过程,往dept表添加随机数据
DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
DECLARE i INT DEFAULT 0;
SET autocommit = 0;
REPEAT
SET i = i + 1;
INSERT INTO dept (deptno ,dname,loc ) VALUES ((START+i) ,rand_string(10),rand_string(8));
UNTIL i = max_num
END REPEAT;
COMMIT;
END $$
#删除
# DELIMITER ;
# drop PROCEDURE insert_dept;
做一个简单的测试:
两个插入存储过程都写完了,自动插入的感觉太爽了,那是不是可以直接插入千万条数据呢,如果不想让你的电脑卡死,我建议你分批插入。
我插入了500000条数据用了41.78秒。。。。
这是查询的结果,从员工编号可以确定我们插入了500000条数据
五 、MySql索引结构
1.基础回顾
我们在大学的时候都学过计算机组成原理,在这门课中,我们学习了硬盘的结构以及原理。
一个硬盘结构图如下图所示。
在写入数据的时候,距离盘面 3 纳米的磁头会利用电磁铁,改变磁盘上磁性材料的极性来记录数据,两种极性分别对应 0 或 1 。
而读取数据时,旁边的读取器可以识别磁性材料的不同极性,再还原成 0 或 1 。
一片磁盘分为若干个磁道,每个磁道又分为各个扇区。扇区是磁盘存储的最小数据块,大小一般是 512 字节。
因此,磁头要想读取某个文件,必须在电机驱动下,先找到对应的磁道,再等磁盘转到对应扇区才行,一般会有十几毫秒的延迟,这就让机械硬盘在读取分散于磁盘各处的数据时,速度将大幅降低。
而在磁道寻址的过程是很耗费时间的,就像我们查询新华字典一样,如果不用目录查找一个字的话,一页一页的翻需要很长时间,如果用事先约定好的规则(如偏旁部首)查询可以很快很轻松的查询到我们要查的文件。
Mysql也一样,索引的目的在于提高查询效率,可以类比字典,
如果要查“mysql”这个单词,我们肯定需要定位到m字母,然后从上往下找到y字母,再找到剩下的sql。每查询一个字母都是O(n)的时间复杂度。
如果没有索引,那么你可能需要a----z,如果我想找到Java开头的单词呢?或者Oracle开头的单词呢?
是不是觉得如果没有索引,这个事情根本无法完成?
所以使用索引好处多多。
- 索引能极大的减少存储引擎需要扫描的数据量
- 索引可以把随机IO变成顺序IO
- 索引可以帮助我们在进行分组、排序等操作时,避免使用临时表
2. 索引检索原理
本章用到了很多数据结构,如果数据结构不好的同学,建议自学数据结构。
那么索引的检索原理是什么呢?
你可以简单理解为“排好序的快速查找B树数据结构”
B+树中的B代表平衡(balance)而不是二叉(binary)
在讨论B+树前,我们先讨论一下二叉树。
二叉树是利用了折半查找的方式,
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据。
这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引。下图就是一种可能的索引方式示例:
左边是数据表,一共有两列七条记录,最左边的是数据记录的物理地址
为了加快Col2的查找,可以维护一个右边所示的二叉查找树,每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针,这样就可以运用二叉查找在一定的复杂度内获取到相应数据,从而快速的检索出符合条件的记录。
那么前面我们说过了,Mysql的索引是用B+树(加强版多路平衡查找树)作为基础数据结构的,那么为什么要采用B+树呢,别的数据结构不好吗?
我们知道很多很多可以用来快速查询的数据结构与算法,比如数组,Hash,二分查找,又如二叉搜索树,再例如AVL平衡树,B树(Balance Tree多路平衡查找树)等。
下面我们就聊聊索引算法的发展史。
3. 索引数据结构的发展史
开头放一个时间复杂度图,忘记的同学复习一下。
其实对于查找,我们可以大致分为三类
-
数组
作为一个人尽皆知的数据结构,数组的单点插入时间复杂度为O(N),单点查询的时间复杂度为O(1)。 -
Hash
不得不承认Hash是一个很优秀的算法,单点插入时间复杂度为O(1),单点查询的时间复杂度为O(1),那么为什么Mysql不采用Hash作为索引算法呢? -
树
二叉树的算法都是O(Log n),在这可以发现,单按照效率而言,Hash是优于树的,比如在总数据量为8的序列中查询,树需要log2 8 = 3
需要3次可以查到,而Hash只需要1次,但是不要忽略一个致命问题,那就是区间查找和排序,对数树来说,区间查找的时间复杂度依然是O(log n),而Hash却已经变成了O(n),两害相衡取其轻,综合考虑,选择树作为索引的算法。
既然奠定算法,我们复习下树的知识。
二叉树
二叉树的特点:
1、一个节点只能有两个子节点,也就是一个节点度不能超过2
2、左子节点 小于 本节点;右子节点大于等于 本节点,比我大的向右,比我小的向左
对该二叉树的节点进行查找发现:
深度为1的节点的查找次数为1,
深度为2的节点的查找次数为2,
深度为N的节点的查找次数为N,
结论:因此其平均查找次数为 (1+2+2+3+3+3) / 6 = 2.3次
二叉树看起来不错嘛,为什么要用B+?
极端情况?左右倾错误。
第1种情况
如果id的值是持续递增的话,建立出的树会是什么样的结构?
第2种情况
很可怕吧,极端的情况下,时间复杂度会退化成恐怖的O(n)。
那么我们能不能在建树的时候,把树 “正” 过来呢,不要这么偏激。
平衡二叉树
什么是平衡二叉树
平衡二叉树的特点:
1、一个节点只能有两个子节点,也就是一个节点度不能超过2
2、左子节点 小于 本节点;右子节点大于等于 本节点,比我大的向右,比我小的向左
从算法的数学逻辑来讲,平衡二叉树的查找速度和比较次数都是较小的,说明磁盘IO的次数也很少,那为什么我们选择BTREE?
理想丰满,现实骨感。我们不得不考虑一个最坑爹的问题。我们以二叉树作为索引结构,举个例子:
假设树高是4,查找的值是10,我们的流程如下:
初始加载树
第1次查找:
第2次查找:
第3次查找:
第4次查找:
从上一步的查找过程中发现的规律?
磁盘的IO次数是由什么决定的?
树高,也即磁盘的IO次数最坏的情况下就等于树的高度。
平衡二叉树产生的问题:
树高度问题导致磁盘IO过多
还是不行,哎
我们需要将"瘦高"的身形变成"矮胖",通过降低树的高度达到减少IO的次数
那么有没有什么办法把树压扁呢,如果三叉呢?
B树
我们发现B树不是二叉树,因为它可能会有三个叉,所以又叫二三树。后面会详细介绍B树。
我们发现,B树比平衡二叉树树高要低。
别急,在了解B树的检索原理前,我们先了解一下磁盘页/块
那如果一个数据库中的内容很多,需要将所有的索引都加载进来吗?
-
数据库索引是存储在磁盘上的,如果数据很大,必然导致索引的大小也会很大,超过几个G(好比新华字典字数多必然导致目录厚)
-
当我们利用索引查询时候,是不可能将全部几个G的索引都加载进内存的,我们能做的只能是:逐一加载每一个磁盘页,因为磁盘页对应着索引树的节点。
树只是逻辑结构,物理上数据还是存储在磁盘中,每个节点存储在磁盘页中。
磁盘页/块
首先我们用SQL查询一下页的信息
SHOW GLOBAL STATUS LIKE 'Innodb_page_size';
Innodb_page_size
INNODB page size (DEFAULT 16KB).
Many VALUES are counted IN pages; the page size enables them TO be easily converted TO bytes
说明每个页的大小是16kb。
底层原理
系统从磁盘读取数据到内存时是以磁盘块(block) 为基本单位的,位于同一个磁盘块中的数据会被一次性读取出来,而不是需要什么取什么(防止重复读取浪费时间)。
InnoDB存储引擎中有页(Page)的概念,页是其磁盘管理的最小单位。
系统一个磁盘块的存储空间往往没有这么大,因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。(争取塞满整个页)
InnoDB在把磁盘数据读入到磁盘时会以页为基本单位,在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置,这将会减少磁盘I/O次数,提高查询效率。
B树检索原理
每个节点占用一个盘块的磁盘空间,一个节点上有两个升序排序的关键字和三个指向子树根节点的指针,指针存储的是子节点所在磁盘块的地址。
模拟查找关键字29的过程:
根据根节点找到磁盘块1,读入内存。【磁盘I/O操作第1次】
比较关键字29在区间(17,35),找到磁盘块1的指针P2。
根据P2指针找到磁盘块3,读入内存。【磁盘I/O操作第2次】
比较关键字29在区间(26,30),找到磁盘块3的指针P2。
根据P2指针找到磁盘块8,读入内存。【磁盘I/O操作第3次】
在磁盘块8中的关键字列表中找到关键字29。
分析上面过程,发现需要3次磁盘I/O操作,和3次内存查找操作。由于内存中的关键字是一个有序表结构,可以利用二分法查找提高效率。而3磁盘I/O操作是影响整个BTree查找效率的决定因素。BTree相对于AVLTree缩减了节点个数,使每次磁盘I/O取到内存的数据都发挥了作用,从而提高了查询效率。
结论:B树比平衡二叉树减少了一次IO操作
其实b树就已经很好了,但是丧心病狂的工程师们还不满意,继续优化。
B+树
B+树和B树的最明显的区别就是所有数据都拿到了叶子节点,叶子节点采用链表数据结构。
图中可以看出所有data信息都移动叶子节点中,而且子节点和子节点之间会有个指针指向,这个也是B+树的核心点,这样可以大大提升范围查询效率,也方便遍历整个树
- 非叶子节点不再存储数据,数据只存储在同一层的叶子节点上;
- 叶子之间,增加了链表,获取所有节点,不再需要中序遍历;
B+树的检索原理
B树(注意是B树) 的结构图中可以看到每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的,如果data数据较大时将会导致每个节点(即一个页)能存储的key的数量很小,当存储的数据量很大时同样会导致B树的深度较大,增大查询时的磁盘I/O次数进而影响查询效率。
但在B+树中,所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上,而非叶子节点上只存储key值信息,这样可以大大加大每个节点存储的key值数量,降低B+树的高度。
- InnoDB存储引擎的最小存储单元是页,页可以用于存放数据也可以用于存放键值+指针, 在B+树中叶子节点存放数据,非叶子节点存放键值+指针。
- 索引组织表通过非叶子节点的二分查找法以及指针确定数据在哪个页中,首先找到根页进而在去数据页中查找到需要的数据
B+树算法: 通过继承了B树的特征,B+树相比B树,新增叶子节点与非叶子节点关系。
- 叶子节点中包含了键值和数据,
- 非叶子节点中只是包含键值和子节点引用,不包含数据。
- 通过非叶子节点查询叶子节点获取对应的数据,所有相邻的叶子节点包含非叶子节点使用链表进行结合,叶子节点是顺序排序并且相邻节点有顺序引用的关系 。
结论:从B树到B+树
B+树是在B树基础上的一种优化使其更适合实现外存储索引结构,InnoDB存储引擎就是用B+树实现其索引结构。
B+树相对于B树有几点不同?
-
非叶子节点只存储键值信息。
-
所有叶子节点之间都有一个链指针。
-
数据记录都存放在叶子节点中。
这章的最后,我们看一下上述结构算法复杂度
大O表示法
六、索引优化
1. 索引基本类型与语法规则
如果我们想买一本书,肯定去搜索书名而不是去搜索ISBN码,那么一个商品库里有那么多的树,当我们 WHERE book = ’ XX '的时候,想想是不是很恐怖,所以我们要在对应字段上建立索引。
索引大致上分为以下几类。
1.单值索引
即一个索引只包含单个列,一个表可以有多个单列索引
如:
create index idx_book_ bName on t_book (bookName) //单值索引
select * from t_book where bookName=' core java ‘
2.唯一索引
索引列的值必须唯一,但允许有空值。
如:
create index unique idx_book_ bName on t_book (bookName) //单值索引
select * from t_book where bookName=' core java ‘
主键就是唯一索引
3.复合索引
即一个索引包含多个列
create index idx_ book_ bNameathpci on t_book (bookName, author, price)//复合索引
select * from t_book where bookName=' core java' and author=' z3’;
有人想问,那我多建立几个单值索引不就好了嘛?你可以这么用,但是那不是闲的嘛。。。。不推荐
索引语法
1.创建
- 方法1:
使用cerate [] 里的内容为可选,选中就是唯一索引
CREATE [UNIQUE ] INDEX indexName ON mytable(columnname(length));
- 方法二
和下面的ALTER区分
ALTER TABLE 表名 ADD [UNIQUE ] INDEX [indexName] ON (columnname(length))
2.删除
DROP INDEX [indexName] ON mytable;
3.查看
SHOW INDEX FROM table_name\G
4.使用ALTER命令
方法1:
该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
方法2:
这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list):
方法3:
添加普通索引,索引值可出现多次。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
操作好像挺简单,但是
索引建立了就可以了吗?索引建立了查表就会块吗,效率就会高吗,索引建立的好不好?是否被引用?如果索引建立了,不被用那不就是垃圾吗。下面继续
2. 索引和EXPLAIN
前面我们了解了我们手写SQL语句和机读SQL语句顺序是不同的,所以说Mysql是会自动优化我们写的SQL语句。
因此我们可以,使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。
借用官网的文档,大致了解下
http://dev.mysql.com/doc/refman/5.5/en/explain-output.html
explain可以分析我们的查询语句或是表结构的性能瓶颈,具体有什么用呢?大致分为以下几点
- 表的读取顺序
- 数据读取操作的操作类型
- 哪些索引可以使用
- 哪些索引被实际使用
- 表之间的引用
- 每张表有多少行被优化器查询
这是官网的原话,有没有觉得哪个字都是中国字,但是就是读不懂,不要急,慢慢来。
explain语法很简单如下图
sql写的好不好我们要眼见为实,数据说话。
但是分析出来的数据都是什么意思呢?
其实我们在说这些之前,要在了解以下一条SQL的执行流程。如图所示
图很容易懂,就不解释了。
本次是以5.5的版本进行讲解,5.8之后略有不同,但是大致一样。
执行计划包含的信息如图如图所示(就是分析出的字段)
3. 分析字段解释
id(重点)
这可不是数据表中的id
select查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序,一般情况下id越多表越复杂。
id字段的内容又分为以下三种情况
1.id相同,执行顺序由上至下
我们发现id都是1,table的顺序是t1,t3,t2,表明三个表是顺着加载,但是有人一定会有疑惑,我们form的顺序明明是t1,t2,t3啊,怎么到了执行顺序中就变成了,t1,t3,t2了呢,我们前面说过,手写和机读的顺序是不同的。
2.id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行
我们都知道,括号的优先级是很高的,被括号修饰的语句肯定是最先执行的,我们在分析表中的id,发现是1,2,3,再看对应的table字段,发现是t2,t1,t3,正好对应id越大表越先被执行。
所以 如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行。
因此在下一条开始前,先记一句口诀,id相同顺着走,id不同大的先走。
3.id相同不同,同时存在
我们依然还是分析下语句,还是先执行括号里的语句,括号里面查询到信息生成了一个临时表s1,然后和t2合作共同查询出了t3.id。
在分析下,查询出的表id为2的字段是最大的,发现对应的表是t3,t3是在括号里面的最先用到的表,然后再看第一行和第二行,发现id都是1,因此他们对应的表在按照从上到下顺序执行。
细心的朋友一定发现了,第一行对应的表名是derived2,这是什么意思呢?其实这是临时表的意思,derived后面的2对应的是id为2的表t3,意思是这个临时表是通过t3表诞生的。既s1表。
select_type
select_type查询的类型,主要是用于区别普通查询、联合查询、子查询等的复杂查询。
select_type全部字段如下:
天哪,怎么有这么多字段,想想脑袋都烦!
内容总结
以上是互联网集市为您收集整理的MySql进阶全部内容,希望文章能够帮你解决MySql进阶所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错,欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 gblab@vip.qq.com 举报,一经查实,本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。