Python学习日志0324 MySQL索引、事务、存储过程与SQL优化

一、MySQL索引

1.定义：帮助MySQL高效获取数据的数据结构，能显著提高数据库查询速度。

2.索引方法

（1）BTREE索引

要了解BTREE索引，首先需要了解B树和B+树的原理和特点：

B树：一种动态的多分支排序树，通过控制节点数保持树的良好结构。

特点：①设B树中最大节点数为m（m>2），则分支节点的儿子个数为[2,m]，根节点至少有两个分支。

②B树中每一个节点最多存放（m-1）个关键字，最少存放（m/2-1）（取上整）个关键字。

③非叶子结点关键字个数=指向儿子的指针个数-1.

④设非叶子结点的关键字为k(i)，指向儿子的指针为p(i)，则节点中存储内容为一个序列：（p(0),k(0),p(1),k(1)......k(m-1),p(m)），其中p(0)指向关键字小于k(0)的子树，p(m)指向关键字大于k(m-1)的子树，其他指针p(i)指向关键字属于[k(i-1),k(i)]的子树。

⑤所有叶子结点均位于同一层。

⑥关键字分布在整个B树中，每个关键字在树中只存在一次。

Python学习日志0324 MySQL索引、事务、存储过程与SQL优化

B+树：一种动态的多分支排序树，与B树结构相似，但概念更加简单，使用更加广泛。

特点：①设B+树中最大节点数为m（m>2），则分支节点的儿子个数为[2,m]，根节点至少有两个分支。

②B+树中每一个节点最多存放m个关键字，最少存放（m/2）（取上整）个关键字。

③非叶子结点的子树指针与关键字个数相同。

④B+树中所有的关键字都出现在叶子结点中，所有叶子结点的深度相同。

⑤分支节点的关键字不直接连接数据项，所有数据项都只与叶子结点的关键字相连。

Python学习日志0324 MySQL索引、事务、存储过程与SQL优化

根据BTree的特性，在B-Tree中按key检索数据的算法非常直观：首先从根节点进行二分查找，如果找到则返回对应节点的data，否则对相应区间的指针指向的节点递归进行查找，直到找到节点或找到null指针，前者查找成功，后者查找失败。

BTree_Search(node, key) {
if(node == null) return null;
foreach(node.key)
{
if(node.key[i] == key) return node.data[i];
if(node.key[i] > key) return BTree_Search(point[i]->node);
}
return BTree_Search(point[i+1]->node);
}
data = BTree_Search(root, my_key);

（2）HASH索引

当给某张表某列增加HASH索引时，将这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以HASH索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO，效率相对较低。因此，如果存储的数据重复度很低（也就是说基数很大），对该列数据以等值查询为主，没有范围查询、没有排序的时候，适合采用哈希索引。

缺点：①因为Hash索引比较的是经过Hash计算的值，所以只能进行等式比较，不能用于范围查询
   ②使用HASH索引时，每次查询都要全表扫描
   ③虽然哈希值是按照顺序排列的，但是哈希值映射的真正数据在哈希表中就不一定按照顺序排列，所以无法通过排序操作提高效率。
   ④不支持用部分索引键进行搜索，因为组合索引在计算哈希值的时候是一起计算的。
   ⑤当哈希值大量重复且数据量非常大时，其检索效率低于Btree索引。

语句：SELECT … FROM t WHERE C1 = ...;

二、事务

1.事务的基本要素（ACID）
①原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，
不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作
就像没有发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物
质构成的基本单位。
②一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破
坏。比如 A 向 B 转账，不可能 A 扣了钱，B 却没收到。
③隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务
之间彼此没有任何干扰。比如 A 正在从一张银行卡中取钱，在 A 取钱的过程结束前，B 不
能向这张卡转账。
④持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据
库，不能回滚。

2.事务的并发问题
①脏读：事务 A 读取了事务 B 更新的数据，然后 B 回滚操作，那么 A 读取到的数据
是脏数据
②不可重复读：事务 A 多次读取同一数据，事务 B 在事务 A 多次读取的过程中，
对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致。
③幻读：系统管理员 A 将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，
但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发
现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
注：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表。

3.事务隔离等级

Python学习日志0324 MySQL索引、事务、存储过程与SQL优化

三、存储过程

1.简介：SQL 语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。存储过程是可编程的函数，在数据库中创建并保存，可以由 SQL 语句和控制结构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

2.优点

(1).增强 SQL 语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).较快的执行速度：如果某一操作包含大量的 Transaction-SQL 代码或分被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的 Transaction-SQL 语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).减少网络流量：针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的 Transaction-SQL 语句被组织进存储过程，那么当在客户计算上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并
降低了网络负载。
(5).作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。

3.语法：CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名数据类型

[,[IN|OUT|INOUT] 参数名数据类型…]]) [特性 ...] 过程体

例：

CREATE PROCEDURE myproc(OUT s int) 
BEGIN 
SELECT COUNT(*) INTO s FROM students; 
END

4.参数
存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用","分割开。MySQL 存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT:

①IN 参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能
被返回，为默认值
②OUT:该值可在存储过程内部被改变，并可返回
③INOUT:调用时指定，并且可被改变和返回

例：in参数

DELIMITER // 
 CREATE PROCEDURE in_param(IN p_in int) 
 BEGIN 
 SELECT p_in; 
 SET p_in=2; 
 SELECT p_in; 
 END; 
 // 
DELIMITER ;
#调用 
SET @p_in=1; 
CALL in_param(@p_in);
SELECT @p_in;

注：p_in 虽然在存储过程中被修改，但并不影响@p_id 的值

OUT 参数

DELIMITER // 
 CREATE PROCEDURE out_param(OUT p_out int) 
 BEGIN 
 SELECT p_out; 
 SET p_out=2; 
 SELECT p_out; 
 END; 
 // 
DELIMITER ; 
#调用 
SET @p_out=1; 
CALL out_param(@p_out);
SELECT @p_out;

INOUT参数

DELIMITER // 
CREATE PROCEDURE inout_param(INOUT p_inout int) 
BEGIN 
SELECT p_inout; 
SET p_inout=2; 
SELECT p_inout; 
END; 
 // 
DELIMITER ; 
#调用 
SET @p_inout=1; 
CALL inout_param(@p_inout) ; 
SELECT @p_inout;

5.变量
语法：DECLARE 变量名 1[,变量名 2...] 数据类型 [默认值];

6.变量赋值
语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]

变量赋值
语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]
用户变量
用户变量一般以@开头
注：滥用用户变量会导致程序难以理解及管理

#在 MySQL 客户端使用用户变量 
SELECT 'Hello World' into @x; 
SELECT @x; 
SET @y='Goodbye Cruel World'; 
SELECT @y; 
SET @z=1+2+3; 
SELECT @z;

#在存储过程中使用用户变量 
CREATE PROCEDURE GreetWorld() SELECT CONCAT(@greeting,' World'); 
SET @greeting='Hello'; 
CALL GreetWorld(); 

#在存储过程间传递全局范围的用户变量 
CREATE PROCEDURE p1() SET @last_proc='p1'; 
CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc); 
CALL p1(); 
CALL p2();

Python学习日志0324 MySQL索引、事务、存储过程与SQL优化

相关推荐