MySQL数据库(Python操作MySQL｜SQL注入｜视图｜触发器｜事务｜流程控制｜索引)

0Jchen

已于 2023-11-27 22:10:00 修改

阅读量336

点赞数 10

分类专栏： MySQL数据库文章标签：数据库 mysql

于 2023-10-26 21:32:33 首次发布

本文链接：https://blog.csdn.net/achen_m/article/details/134060943

版权

MySQL数据库专栏收录该内容

7 篇文章 0 订阅

订阅专栏

MySQL数据库

一、Python操作MySQL

我们想要在Python中操作MySQL数据库，就需要用到一个第三方模块pymysql
安装模块两种方式：

在cmd中输入pip install pymysql
直接在pycharm中：File | Settings | Project: Pycharm_Project_Test | Python Interpreter安装pymysql模块即可

在这里插入图片描述

Python中的语法

	import pymysql  # 导入模块
	
	'建立数据库连接'
	conn = pymysql.connect(
		user = 'root',  # 数据库用户名
		password = '123',  # 数据库密码(没有可以不填)
		host = '127.0.0.1', # 数据库地址
		port = 3306,  # 端口号
		database = 'day',  # 数据库的库名
		charset = 'utf8', # 字符编码 
		autocommit = True  # 自动二次提交(这个后面说，可写可不写，建议都写上)
	)

	'获取游标，等待输入，相当于在cmd中光标处于等待输入'(用于操作数据库)
	cursor = conn.cursor()  # 括号内不填东西默认以元组的方式显示接收的内容
	# cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)  # 改变显示方式为列表内一个个字典
	
	'写入SQL语句'
	sql = 'select * from zero'

	'开始执行SQL语句'这里是执行获取的是条数
	rows = cursor.execute(sql)
	'也可以直接在这个执行SQL语句这里写入SQL语句，直接把写入、执行合并为一步'
	rows = cursor.execute('select * form zero')

	'如果在建立数据库连接的时候没有autocommit=True，那么在这里就需要二次提交'
	# conn.commit()

	'获取命令的执行结果'
	res = cursor.fetchall()  # 获取结果中的所有数据
	res = cursor.fetchone()  # 获取结果中的一条数据
	res = cursor.fetchmany() 
	'''
	获取结果中指定的几条数据(括号内写入的数字就是获取的条数，
	当超出结果的条数也不会报错，但是也只会显示应有的数据)
	'''
	print(res)
	'cursor类似于光标 第一次获取完，再次获取的消息就会丢失了基于当前位置往后移动'

	'关闭游标'
	cursor.close()

	'关闭连接'
	conn.close()
	
	'''Pycharm里面py格式的写入的SQL语句会飘黄，不用在意'''

二、SQL注入的问题

我们可以先写使用SQL编写的用户登录

	import pymysql
	# 连接mysql库
	conn = pymysql.connect(user='root',host='127.0.0.1',port=3306,database='day',charset='utf8')
	
	# 获取游标
	cursor = conn.cursor(cursor = pymysql.cursors.DictCursor)
	
	
	inp_user = input('username>>>:').strip()
	inp_pwd = input('password>>>:').strip()
	
	# 写SQL语句
	sql = "select * from userinfo where username='%s' and password='%s'" % (inp_user, inp_pwd)
	print(sql)
	
	# 需要执行二次提交
	conn.commit()
	
	# 执行sql语句
	rows = cursor.execute(sql)
	
	# 判断是否数据库中存在
	if rows: # 存在则走这里
	    print('login successful')
	else:  # 不存在走这里
	    print('username or password error')
	
	# 关闭游标
	cursor.close()
	
	# 关闭连接
	conn.close()

在这里插入图片描述
这样确实是登录匹配成功了，但是当前这种SQL语句写法会有一个重大问题
如下图：

在这里插入图片描述

所以SQL注入问题本质上就是利用SQL语法上的一些特殊符号的组合产生了特殊的含义从而跳脱了正常的业务逻辑

措施就是：针对用户输入的数据不能输入特殊符号什么的，这也就是为什么那么多网站、软件账号都不允许输入特殊符号的原因，所以我们可以让用户输入的数据不要自己处理，交给专门的方法自动过滤

在这里插入图片描述

三、视图

什么是视图

视图就是通过执行SQL语句查询结果得到一张虚拟表，然后保存下来，后面可以基于该表做其他操作

为什么要用视图

如果要频繁使用一张虚拟表，可以不用重复查询

如何使用视图

	语法结构：
		create view 视图名 as(注意as是不能省略也不是重命名的意思) SQL语句
		ex:create view teacher2course as select * from teacher inner join course on 
		teacher.tid=course.teacher_id;

	视图原理：
		在硬盘中视图是有表结构文件，没有表数据文件(不能做增删改)视图通常是用于查询
		select * from teacher2course;

	"""
	创建好了之后 验证它的存在navicat验证 cmd终端验证
	最后文件验证 得出下面的结论 视图只有表结构数据还是来源于之前的表
	"""
	
	删除视图：delete from teacher2course where id=1;

在这里插入图片描述
思考：开发过程中会不会去使用视图？

不会！视图是mysql的功能，如果你的项目里面大量的使用到了视图，那意味着你后期想要扩张某个功能的时候这个功能恰巧又需要对视图进行修改，意味着你需要先在mysql这边将视图先修改一下，然后再去应用程序中修改对应的sql语句，这就涉及到跨部门沟通的问题，所以通常不会使用视图，而是通过重新修改sql语句来扩展功能

四、触发器

触发器概念

触发器类似于我们python中魔法方法到达什么条件自动触发
在满足对某张表数据的增、删、改的情况下，自动触发的功能称之为触发器

触发器作用

专门针对表数据的操作，定制个性化配套功能

触发器种类

表数据新增之前、新增之后
表数据修改之前、修改之后
表数据删除之前、删除之后

触发器语法结构

	语法结构：
		create trigger 触发器的名字 before/after insert/update/delete on 表名 for each row
		begin
			sql语句
		end

	触发器的名字一般情况下建议采用下列样式进行命名
		tri_after_insert_t1
		tri_before_update_t2
		tri_before_delete_t3

针对插入前后

	'插入前'
	create trigger tri_after_insert_t1 after insert on 表名 for each row
	begin
		sql代码
	end
	
	'插入后'
	create trigger tri_before_insert_t1 before insert on 表名 for each row
	begin
		sql代码
	end

针对删除前后

	'删除前'
	create trigger tri_after_delete_t1 after delete on 表名 for each row
	begin
		sql代码
	end
	
	'删除后'
	create trigger tri_before_delete_t1 before delete on 表名 for each row
	begin
		sql代码
	end

针对修改前后

	'修改前'
	create trigger tri_after_update_t1 after update on 表名 for each row
	begin
		sql代码
	end
	
	'修改后'
	create trigger tri_before_update_t1 before update on 表名 for each row
	begin
		sql代码
	end

触发器注意事项

触发器内部的SQL语句需要用到分号，但是分号又是SQL语句默认的结束符号
所以为了避免产生冲突完成完整的写出触发器代码，我们需要临时修改SQL语句中默认的结束符
修改默认符号我们需要用到 delimiter $$ 需要修改的符号(注意cmd里面的临时修改配置文件是永久修改)
注意：修改完结束后，记得在改回SQL语句的默认的结束符号 delimiter ;不然后面结束符就得用$$了

案例

	'模拟一个cmd 如果执行失败则写入errlog里面'

	1.	CREATE TABLE cmd(
			id INT PRIMARY KEY auto_increment,
			USER CHAR(32),
			prive CHAR(10),
			cmd CHAR(64),  # cmd命令
			sub_time datetime,  # 提交时间
			success enum('yes','no')  # no代表执行失败 
		);

	2.	CREATE TABLE errlog(
			id INT PRIMARY KEY auto_increment,
			err_cmd CHAR(64),	# 报错的cmd命令
			err_time datetime	# 报错时间
		);
	
	3.	delimiter $$  # 将mysql默认的结束符由;转换成$$

	4.	create trigger tri_after_insert_cmd after insert on cmd for each row
		begin
			if NEW.success = 'no' then  # 新记录都会被MySQL封装成NEW对象
				insert into errlog(err_cmd,err_time) values(NEW.cmd,NEW.sub_time);
			end if;
		end $$
		
	5.	delimiter ; # 结束之后记得再改回来，不然后面结束符都是$$了
	'往表cmd中插入记录，触发触发器，根据IF的条件决定是否插入错误日志'
	6.	INSERT INTO cmd(USER,priv,cmd,sub_time,success)VALUES
		('egon','0755','ls -l /etc',NOW(),'yes'),
	    ('egon','0755','cat /etc/passwd',NOW(),'no'),
	    ('egon','0755','useradd xxx',NOW(),'no'),
	    ('egon','0755','ps aux',NOW(),'yes');

	7.	select * from errlog; # 查询errlog表记录
	8.	drop trigger tri_after_insert_cmd; # 删除触发器

在这里插入图片描述

五、事务

什么是事务？

事务是用户定义的一系列数据库操作，这些操作可以视为一个完成的逻辑处理工作单元
要么全部执行，要么全部不执行，是不可分割的工作单元

通俗的说

开启一个事务可以包含一些SQL语句，这些SQL语句要么同时成功，要么一个都别想成功，称之为事务的原子性

事务的产生

	数据库中的数据是共享资源，因此数据库系统通常要支持多个用户的或不同应用程序的访问
	并且各个访问进程都是独立执行的，这样就有可能出现并发存取数据的现象，为了避免数据库的不一致性
	这种处理机制称之为"并发控制"，其中事务就是为了保证数据的一致性而产生的一种概念和手段(事务不是唯一手段)

事务的四大特性

为了保证数据库的正确性与一致性实物具有四个特征，这四个特征通常称为ACID特性。

原子性(Atomicity)：事务的原子性保证事务中包含的一组更新操作是原子的，不可分割的，不可分割是事务最小的工作单位，所包含的操作被视为一个整体，执行过程中遵循，要么都不执行，要不全部执行，不存在一半执行，一般不执行的情况

一致性(Consistency)：事务的一致性要求事务必须满足数据库的完整性约束，且事务执行完毕后会将数据库一个一致性的状态变为另一个一致性的状态事务的一致性与原子性是密不可分的

隔离性(Isolation)：事务的隔离性要求事务之间是彼此独立的隔离的及一个事务的执行不可以被其他事务干扰

持久性(Durability)：事物的持续性也称持久性是指一个事务一旦提交它对数据库的改变将是永久性的因为数据刷进了物理磁盘了其他操作将不会对它产生任何影响

具体使用

	 1.创建表及录入数据
     create table user(
      id int primary key auto_increment,
      name char(32),
      balance int
     );
      
	 insert into user(name,balance)values
	     ('jason',1000),
	     ('kevin',1000),
	     ('tank',1000);
	
	2.事务操作
	开启一个事务的操作
	start transaction;
	
	编写SQL语句(同属于一个事务)
	update user set balance=900 where name='jason';  # 买家支付100元
	update user set balance=1010 where name='kevin'; # 中介拿走10元
	update user set balance=1090 where name='tank';  # 卖家拿到90元
	
	事务回滚(返回执行事务操作之前的数据库状态)
	rollback;  # 执行完回滚,撤回到上一个状态(上一步)
	
	'开启事务之后，只要没有执行commit操作，数据其实都没有真正刷新到硬盘'
	事务确认(执行完事务的主动操作之后 确认无误之后 需要执行确认命令)
	commit;  # 开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作
	'开启事务检测操作是否完整，不完整主动回滚到上一个状态，如果完整就应该执行commit操作'

在这里插入图片描述

六、存储过程

存储过程包含了一些可执行的SQL语句，存储过程存放于MySQL中，通过调用它的名字可以执行其内部的一对SQL，类似于Python中的自定义函数

		类似于Python中的自定义函数
		
		语法结构
			delimiter $$ 	# 涉及到begin语法需要修改临时结束符
			create procedure 名字(参数,参数)
			begin
				sql语句;
			end 临时结束符
			delimiter ;		# 用完再修改回去

			# 相当于调用函数
			call 名字()

类似于有参函数:
delimiter $$
create procedure p1(
    in m int,  		# in表示这个参数必须只能是传入不能被返回出去
    in n int,  
    out res int)  	# out表示这个参数可以被返回出去，还有一个inout表示即可以传入也可以被返回出去
begin
    select name from teacher where tid > m and tid < n;		# name 表示用户设置的数字
    set res=0;  # 用来标志存储过程是否执行
end $$
delimiter ;		# 用完修改成默认结束符号


# 针对res需要先提前定义
set @res=10;  定义	# 相当于python中设置一个变量名值为10
select @res;  查看
call p1(1,5,@res)  调用
select @res  查看

查看存储过程具体信息
	show create procedure 名称;
查看所有存储过程
	show procedure status 名称;
删除存储过程
	drop procedure 名称;

七、函数

注意与存储过程的区别，MySQL内置的函数只能在SQL语句中使用！

	"ps:可以通过help 函数名    查看帮助信息!"
	# 1.移除指定字符
	Trim、LTrim、RTrim
	 
	# 2.大小写转换
	Lower、Upper
	 
	# 3.获取左右起始指定个数字符
	Left、Right
	 
	# 4.返回读音相似值(对英文效果)
	Soundex
	"""
	eg:客户表中有一个顾客登记的用户名为J.Lee
	        但如果这是输入错误真名其实叫J.Lie,可以使用soundex匹配发音类似的
	        where Soundex(name)=Soundex('J.Lie')
	"""
	 
	# 5.日期格式:date_format
	'''在MySQL中表示时间格式尽量采用2022-11-11形式'''
	CREATE TABLE blog (
	    id INT PRIMARY KEY auto_increment,
	    NAME CHAR (32),
	    sub_time datetime
	);
	INSERT INTO blog (NAME, sub_time)
	VALUES
	    ('第1篇','2015-03-01 11:31:21'),
	    ('第2篇','2015-03-11 16:31:21'),
	    ('第3篇','2016-07-01 10:21:31'),
	    ('第4篇','2016-07-22 09:23:21'),
	    ('第5篇','2016-07-23 10:11:11'),
	    ('第6篇','2016-07-25 11:21:31'),
	    ('第7篇','2017-03-01 15:33:21'),
	    ('第8篇','2017-03-01 17:32:21'),
	    ('第9篇','2017-03-01 18:31:21');
	select date_format(sub_time,'%Y-%m'),count(id) from blog group by date_format(sub_time,'%Y-%m');
	 
	1.where Date(sub_time) = '2015-03-01'
	2.where Year(sub_time)=2016 AND Month(sub_time)=07;
	# 更多日期处理相关函数 
	    adddate    增加一个日期 
	    addtime    增加一个时间
	    datediff    计算两个日期差值
	  ...

八、流程控制

	if条件语句
		delimiter //					# 设置结束符号
		CREATE PROCEDURE proc_if ()		# 创建一个存储过程
		BEGIN
		
		    declare i int default 0;	# 声明变量
		    if i = 1 THEN				# 如果i 等于1 那么 THEN(那么)
		        SELECT 1;				# 选择1
		    ELSEIF i = 2 THEN			# elif i 等于2 那么 THEN(那么)
		        SELECT 2;				# 选择2
		    ELSE						
		        SELECT 7;				#  否则 选择7
		    END IF;						# 结束if判断
		
		END //							# 结束语句
		delimiter ;						# 修改回结束符号
	
	
	while循环
		delimiter //
		CREATE PROCEDURE proc_while ()	# 设置循环
		BEGIN
		    DECLARE num INT ;			# 声明变量
		    SET num = 0 ;				# 设置变量
		    WHILE num < 10 DO			# 当num小于10 做
		        SELECT
		            num ;
		        SET num = num + 1 ;		# num += 1
		    END WHILE ;					# 结束循环
		
		END //
		delimiter ;

九、索引

	什么是索引？
		索引像是一本书的目录 它能让你更快的找到想要的内容
		让获取的数据更有目的性 从而提升数据库检索数据的性能
	
	索引在MySQL中也叫做键 是存储引擎用于快速找到记录的一种数据结构
	Primary key		Unique key		Index key
	上述三种键在数据查询的时候都可以快速的查询
	Primary key		Unique key	除了可以加快查询数据还有额外的限制(非空且唯一 唯一)
	Index key 只能加快数据查询 本身没有任何的额外限制
	
	真正理解索引加快数据查询的意义
		索引的存在可以加快数据的查询 但是会减慢数据的增删
	
	索引相关概念
		聚集索引  Primary key
			 聚集索引就是表的主键 在Innodb存储引擎规定下一张表只能有一个主键 (可以看着主目录)
		辅助索引  Unique key	 Index key
			辅助索引就是除了主键之外的键都是辅助索引(可以看出附加的目录)
			查询数据的时候不可能都是用ID作为筛选条件 这个时候就用不到聚集索引了
			索引就需要给其他字段建立索引 这些索引就叫做辅助索引(无论有多少个索引都基于主键去查)
		覆盖索引
			只在辅助索引的叶子节点中就已经找到了我们想要的数据
			ex: slelect name from user where name = 'LebronJames'	# 条件就是结果 这就是覆盖索引
		非覆盖索引
			select age from user where name = 'LebronJames'
			上述语句叫做非覆盖索引 虽然查询的时候有了索引字典name 但是要查是age字段 所以还需要利用主键去查找

索引的底层原理

		索引底层用的是树 那是什么是树呢 ？
		树是一个数据结构 主要用于优化数据查询 就是用来快速的查找数据

在这里插入图片描述

	这个就是树 第一层树根 第二层树枝 第三层树叶  来说说他的底层原理
	我们现在找一个数字30 如果我们不按照树来找 我们肯定是从1开始数类似于一页一页的翻
	哪有了这个索引有了这个数是怎么找的呢？
	假设第一层是主键 会涉及到磁盘 我们磁盘会把数据分为段 一段一段的来查询 先读一层 它会自动分层
	第一层会分为三小层 第一层会从1-28 第二层会到28-65 第三层65-100 依次寻找 依次分层 总共经历三层就找到了 这就是树
	但是数分为很多种
	
	二叉树
		所有的数据都是基于二叉树来的 每次都会分为两层 
	B数
		除了叶子节点可以有多个分支 其他节点最多只能两个分支(叶子结点就是树叶)
		所有的节点都可以直接存放完整数据(每一个数据块是有固定大小的 树的层级越高表示读取时间越慢 )
		缺点树的存储数据量太小了所以有了另外一个树
	B+树
		只有叶子节点存放真正的数据 其他节点只存主键值(辅助索引值)
		所以第一层级能够存储的ID主键就更多了 就意味着树的层级越矮 查找速度越快(所以我们就知道创建表的时候为什么把ID当做主键值了)
			
	B*树
		在树节点添加了通往其他节点的通道 减少查询次数

在这里插入图片描述

慢查询优化

		在查询数据的时候 有些人用30分组有些人10分组有些人1分钟这是为什么呢 ？
		我们怎么优化这些SQL语句呢！ 这个时候就有一个关键字explain语句

explain命令用法

	explain select 字段名 from 表名;

在这里插入图片描述

		常见的索引扫描类型
			index	range	ref	eq_ref	const	system	null 
		从左到右 性能从最差到最好 我们认为至少要达到range级别
		index：Full Index Scan index与ALL区别为index类型只遍历索引树
		
		range：索引范围扫描 对索引的扫描开始于某一点 返回匹配值域的行 
		显而易见的索引范围扫描是带有between或者where子句里带有<,>查询

mysql> alter table city add index idx_city(population);
mysql> explain select * from city where population>30000000;

		ref：使用非唯一索引扫描或者唯一索引的前缀扫描 返回匹配某个单独值的记录行

mysql> alter table city drop key idx_code;
mysql> explain select * from city where countrycode='chn';
mysql> explain select * from city where countrycode in ('CHN','USA');
mysql> explain select * from city where countrycode='CHN' union all select * from city where countrycode='USA';

	eq_ref：类似ref 区别就在使用的索引是唯一索引 对于每个索引键值 表中只有一条记录匹配 
			简单来说 就是多表连接中使用primary key或者 unique key作为关联条件A
			
	const、system：当MySQL对查询某部分进行优化 并转换为一个常量时 使用这些类型访问
				  如将主键置于where列表中 MySQL就能将该查询转换为一个常量
				  
	NULL：MySQL在优化过程中分解语句 执行时甚至不用访问表或索引 
		  例如从一个索引列里选取最小值可以通过单独索引查找完成

建立索引的原则(规范)

		为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。
		1、选择唯一性索引
			唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。
		2、为经常需要排序、分组和联合操作的字段建立索引
		3、为常作为查询条件的字段建立索引
			如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。
			因此，为这样的字段建立索引，可以提高整个表的查询速度。
		4、尽量使用前缀来索引
			如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段
			进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。
		5、限制索引的数目
			索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。
			修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
		6、删除不再使用或者很少使用的索引
			表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。
			数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

测试索引

	1. 准备表
		create table s1(id int,
						name varchar(20),
						gender char(6),
						email varchar(50));
						
	2. 创建存储过程，实现批量插入记录
		delimiter $$ #声明存储过程的结束符号为$$
		create procedure auto_insert1()
		BEGIN
		    declare i int default 1;
		    while(i<3000)do
		        insert into s1 values(i,'LebronJames','male',concat('LebronJames',i,'@king'));
		        set i=i+1;
		    end while;
		END$$ #$$结束
		delimiter ; #重新声明分号为结束符号
		
	3. 查看存储过程
		show create procedure auto_insert1\G 
	4. 调用存储过程
		call auto_insert1();
	
	# 表没有任何索引的情况下
	select * from s1 where id=300;
	# 避免打印带来的时间损耗
	select count(id) from s1 where id = 300;
	select count(id) from s1 where id = 1;
	
	# 给id做一个主键
	alter table s1 add primary key(id);  # 速度很慢
	select count(id) from s1 where id = 1;  # 速度相较于未建索引之前两者差着数量级
	select count(id) from s1 where name = 'LebronJames'  # 速度仍然很慢

	范围问题
	# 并不是加了索引，以后查询的时候按照这个字段速度就一定快   
	select count(id) from s1 where id > 1;  # 速度相较于id = 1慢了很多
	select count(id) from s1 where id >1 and id < 3;
	select count(id) from s1 where id > 1 and id < 100;
	select count(id) from s1 where id != 3;
	alter table s1 drop primary key;  # 删除主键 单独再来研究name字段
	select count(id) from s1 where name = 'LebronJames';  # 又慢了
	
	create index idx_name on s1(name);  # 给s1表的name字段创建索引
	select count(id) from s1 where name = 'LebronJames'  # 仍然很慢！！！
	"""
	再来看b+树的原理，数据需要区分度比较高，而我们这张表全是LebronJames，根本无法区分
	那这个树其实就建成了“一根棍子”

	select count(id) from s1 where name = 'xxx';  
	# 这个会很快，我就是一根棍，第一个不匹配直接不需要再往下走了
	select count(id) from s1 where name like 'xxx';
	select count(id) from s1 where name like 'xxx%';
	select count(id) from s1 where name like '%xxx';  # 慢 最左匹配特性
	
	# 区分度低的字段不能建索引
	drop index idx_name on s1;
	
	# 给id字段建普通的索引
	create index idx_id on s1(id);
	select count(id) from s1 where id = 3;  # 快了
	select count(id) from s1 where id*12 = 3;  # 慢了  索引的字段一定不要参与计算
	
	drop index idx_id on s1;
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id = 3 and email = 'xxx';
	# 针对上面这种连续多个and的操作，mysql会从左到右先找区分度比较高的索引字段，先将整体范围降下来再去比较其他条件
	create index idx_name on s1(name);
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id = 3 and email = 'xxx';  # 并没有加速
	
	drop index idx_name on s1;
	# 给name，gender这种区分度不高的字段加上索引并不难加快查询速度
	
	create index idx_id on s1(id);
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id = 3 and email = 'xxx';  # 快了  先通过id已经讲数据快速锁定成了一条了
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id > 3 and email = 'xxx';  # 慢了  基于id查出来的数据仍然很多，然后还要去比较其他字段
	
	drop index idx_id on s1
	
	create index idx_email on s1(email);
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id > 3 and email = 'xxx';  # 快 通过email字段一剑封喉

联合索引

	select count(id) from s1 where name='LebronJames' and gender = 'male' and id > 3 and email = 'xxx';  
	# 如果上述四个字段区分度都很高，那给谁建都能加速查询
	# 给email加然而不用email字段
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id > 3; 
	# 给name加然而不用name字段
	select count(id) from s1 where gender = 'male' and id > 3; 
	# 给gender加然而不用gender字段
	select count(id) from s1 where id > 3; 
	
	# 带来的问题是所有的字段都建了索引然而都没有用到，还需要花费四次建立的时间
	create index idx_all on s1(email,name,gender,id);  # 最左匹配原则，区分度高的往左放
	select count(id) from s1 where name='LebronJames' and gender = 'male' and id > 3 and email = 'xxx';  # 速度变快

0Jchen

关注

10
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
MySQL数据库(Python操作MySQL｜SQL注入｜视图｜触发器｜事务｜流程控制｜索引)

视图就是通过执行SQL语句查询结果得到一张虚拟表，然后保存下来，后面可以基于该表做其他操作事务是用户定义的一系列数据库操作，这些操作可以视为一个完成的逻辑处理工作单元要么全部执行，要么全部不执行，是不可分割的工作单元通俗的说开启一个事务可以包含一些SQL语句，这些SQL语句要么同时成功，要么一个都别想成功，称之为事务的原子性。
复制链接

扫一扫