pymysql ，主键， 索引

目录

1|0一、pymysql模块的使用

1|11. 安装pymysql
  • pymysql属于第三方库，要先下载安装。

1|22. 连接MySQL
  • 连接并操作MySQL的步骤

    1. 创建连接

    2. 创建游标

    3. 创建SQL语句

    4. 执行SQL语句

    5. 接收查询结果（当对数据进行查询时，才会有此步骤）fetchall()/fetchone()/fetchmany(size)

      查询结果是列表套字典的格式。若返回的是空，根据创建游标时，让返回的数据是列表套字典形式还是默认的元组形式。对应为[]或()

    6. 提交（当对数据进行增、删、修改时，才会有此步骤）即 commit()

    7. 关闭游标

    8. 关闭连接

  • 查询实例：
import pymysql
conn = pymysql.connect(host='localhost',user='root',password='123qwe',database='test',charset='utf8')
# cursor = conn.cursor() ### 默认返回的值是元组类型*************
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的值是字典类型 (*********)

sql = "select * from userinfo"
cursor.execute(sql)

# res = cursor.fetchall()  ###取出所有的数据 返回的是列表套字典
# res = cursor.fetchone()  ###取出一条数据 返回的是字典类型
res = cursor.fetchmany(12) ### 指定获取多少条数据 返回的是列表套字典，若超过最大数据行数，则只会返回全部的数据。

cursor.close()
conn.close()
  • 插入实例：
  • print(cursor.lastrowid) ### 获取最后一行的ID值在插入时使用才有效果，在其他sql语句中，返回的是None
import pymysql

### 连接数据库的参数
conn = pymysql.connect(host='localhost',user='root',password='123qwe',database='test',charset='utf8')
# cursor = conn.cursor() ### 默认返回的值是元组类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的值是字典类型 (*********)


sql = "insert into user (name, password) values (%s,  %s)"

cursor.execute(sql, ('dshadhsa', 'dbsjabdjsa'))  ### 新增一条数据

print(cursor.lastrowid)   ### 获取最后一行的ID值

# 一次性插入多条数据***************
# data 必须是列表套元组
# data = [
#     ('zekai1', 'qwe'),
#     ('zekai2', 'qwe1'),
#     ('zekai3', 'qwe2'),
#     ('zekai4', 'qwe3'),
# ]
# cursor.executemany(sql, data)  ### 新增多条数据

#### 加如下代码
conn.commit()

cursor.close()
conn.close()

1|33. sql注入问题

  • 产生原因：因为过于相信用户输入的内容，对输入的内容根本没有任何的检验，导致内容中一些关键字符传入，在执行含有这些关键字符的SQL语句时，返回了不正确的结果。

  • 实例：

    user_name = zekai ' or 1=1 #
pwd = dsadsa

# 我们即将提交的SQL语句
sql = 'select * from user where name = %s and password = %s' % (user_name ,pwd)

# 真正提交执行的SQL语句  '#'表示注释的意思
select * from user where name='zekai ' or 1=1 #' and password='dsadsa'

# 查询后返回的结果
# 由于判断密码的条件被#注释掉了，所以只会匹配前面的，而前面又有 关键字 or
[{'id': 1, 'name': 'zhangsan', 'password': ''},{'id': 2, 'name': 'zekai', 'password': '123'}]

  • 解决的方法：注意下面的星号中间的内容

  1. 对用户输入的内容进行检验之后，再写入SQL语句

  2. 利用pymysql的内置方法排查。

    以下是第二种方法的实例：

    user_name = zekai ' or 1=1 #
pwd = dsadsa

# 对用户输入的信息先不直接拼接进sql语句的字符串，对用户输入的信息先用 %s 代替。将用户输入的信息放在一个元组/列表 中，传入execute方法中。

 ****************************************************************************************************
# 此时要注意一点：就是pymysql的execute方法会把传入的元组或列表中的元素依次取出来，依次传给sql字符串中的 %s 。所以有多少个元素，就要有多少个%s 。
 ***************************************************************************************************

sql = "select * from user where name=%s and password=%s"

使用pymysql自带的方法，把要传的值放进去，就会自动校验用户输入内容的敏感字符
cursor.execute(sql, (user_name, pwd))

# 查询后返回的结果,为空
# []

2|0二、索引

2|11. 什么是索引
  • 索引在MySQL中也叫是一种“键”，是存储引擎用于快速找到记录的一种数据结构

2|22. 索引有什么用

  • 加快查询速度。

  • mysql中的primary key，unique，联合唯一也都是索引，这些索引除了加速查找以外，还有约束的功能

  • 注意1：虽然索引能加快查询速度，但并不是索引越多越好，当数据过多时，创建索引需要的时间也越多，而且还会占用更多的磁盘空间。 若索引太多，应用程序的性能可能会受到影响。而索引太少，对查询性能又会产生影响，要找到一个平衡点，这对应用程序的性能至关重要 。

  • 注意2：说白了，每一个索引就相当于备份了一个已经排好序的一张表，所以，增删改这3种操作都会因为有索引而影响他们的完成速度。其中增操作影响最大，删操作叫增来说影响较小，改操作只有涉及到建立索引的字段才受影响

2|33. 索引的底层原理
  • 索引底层的算法使用的是 B+树 。

2|44. 主键

1、数据库的每张表只能有一个主键，不可能有多个主键。

2、所谓的一张表多个主键，我们称之为联合主键。

注：联合主键就是用多个字段一起作为一张表的主键。

3、主键的作用是保证数据的唯一性和完整性，同时通过主键检索表能够增加检索速度。

2|55. MySQL中索引的分类
  • 普通索引INDEX：加速查找
  • 唯一索引：
    • 主键索引PRIMARY KEY：加速查找+约束（不为空、不能重复）
    • 唯一索引UNIQUE:加速查找+约束（不能重复）
  • 联合索引：
    • PRIMARY KEY(id,name):联合主键索引
    • UNIQUE(id,name):联合唯一索引
      • 添加了联合唯一索引的这些字段，我们可以对其任意一个再添加单个的普通索引。如unique(a,b) , 之后还可以 alter table 表名 add index 字段名1(a) alter table 表名 add index 字段名2(b)
      • 对于添加了联合唯一索引的字段，它的唯一性指的是全部字段数据作为一个整体，不能和其他整体相同。如 unique(a,b) a,b是字段名，int类型，我们插入(1,2)之后不能插入(1,2)了，但可以插入 (1.3)或(2，2)等等
    • INDEX(id,name):联合普通索引
      • 添加了联合普通索引的这些字段，我们可以对其任意一个再添加单个的普通索引

2|66. 索引的创建

1|0（0）创建索引的技巧：
  1. 在开发程序时，一边开发一边创建， 在需要的地方添加索引
  2. 对查询频率高的字段创建合适类型的索引
  3. 尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0
  4. 注意2：在向一张表中插入数据时，若之前已经对该表的字段建立了索引，则数据库会先把索引删掉，再插入数据，之后再把索引添加回来。所以，我们在向表中录入大量数据时：若是新表，录入数据之后再创建索引；若是已经有索引的表：先把原来的索引删掉，等数据录入完成后，再把索引恢复。通过这样的两种方法这样会大大提高数据库录入大量数据的效率。

其他注意的地方：

  • 避免使用select *
  • 避免使用count(*)
  • 创建表时尽量使用 char 代替 varchar
  • 表的字段顺序固定长度的字段优先
  • 组合索引代替多个单列索引（由于mysql中每次只能使用一个索引，所以经常使用多个条件查询时更适合使用组合索引）
  • 尽量使用短索引
  • 使用连接（JOIN）来代替子查询(Sub-Queries)，即代替嵌套查询
  • 连表时注意条件类型需一致
  • 索引散列值（重复少）不适合建索引，例：性别不适合

1|0（1）创建主键索引

  1. 创建表时创建主键索引

    1.
create table xxx(
    			id int auto_increment ,
				primary key(id) # 创建主键索引
					)charset utf8;
2.
create table xxx( id int auto_increment primary key # 创建主键索引
                )charset utf8;

  2. 创建表后创建主键索引

    1.
alter table 表名 change 原字段名 新字段名 字段类型 auto_increment primary key;
2.
alter table 表名 modify 字段名 字段类型 auto_increment primary key;
3.
alter table 表名 add primary key (字段名);

  3. 删除主键索引

    alter table 表名 drop primary key;

1|0（2）创建唯一索引

  1. 创建表时创建唯一索引

    create table t2(
						id int auto_increment primary key,
						name varchar(32) not null default '',
						unique u_name (name) #  创建唯一索引
					)charset utf8;

  2. 创建表后创建唯一索引

    1.
CREATE  UNIQUE   INDEX  索引名 ON 表名 (字段名) ;
2.
alter table 表名 add unique index 索引名 (字段名);

  3. 删除唯一索引

    alter table 表名 drop index 索引名;

1|0（3）创建普通索引

  1. 创建表时创建普通索引

    create table t3(
						id int auto_increment primary key,
						name varchar(32) not null default '',
						index u_name (name) # 创建普通索引
					)charset utf8;

  2. 创建表后创建普通索引

    1.
CREATE  INDEX  索引名 ON 表名 (字段名) 
2.
alter table 表名 add  index 索引名 (字段名)

  3. 删除普通索引

    alter table 表名 drop index 索引名;

1|0（4）联合索引的创建
  • 联合索引的创建只是在创建其他索引时，字段是多个而已
联合唯一索引：unique 索引名(name, email)
联合索引： index 索引名(name, email)
  • 什么时候创建联合索引
					根据公司的业务场景， 在最常用的几列上添加索引
					
					select * from user where name='zekai' and email='zekai@qq.com';
					
					如果遇到上述业务情况， 错误的做法：
						index ix_name (name),
						index ix_email(email)
					
					正确的做法：
						index ix_name_email(name, email)

2|77. 索引未命中情况
  1. 范围问题，或者说条件不明确，条件中出现这些符号或关键字：>、>=、<、<=、!= 、between...and...、like

​

a. 不能在SQl语句中，进行四则运算， 会降低SQL的查询效率
				
b. 使用函数
	select * from tb1 where reverse(email) = 'zekai';
c. 类型不一致
	如果列是字符串类型，传入条件是必须用引号引起来，否则不会命中索引
	select * from tb1 where email = 999;
				
#排序条件为索引，则select字段必须也是索引字段，否则无法命中
d. order by
	select name from s1 order by email desc;
	当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
					
	select email from s1 order by email desc;
	特别的：如果对主键排序，则还是速度很快：
	select * from tb1 order by nid desc;
						
e. ***********count(1)或count(*)代替count(列)*************
    1.执行效率上：
列名为主键，count(列名)和count(1) 和  count(*) 执行效率是一样的:因为 explain 中 type 类型都为 index
列名不为主键，而且列名没有创建索引  但是 其他字段创建了索引：count(1) = count（*） > count(列名)  ：因为expalin 中的 type 类型 count(1)  和 count(*) 类型都为 index  而 count(列名) 的 type 类型为 all

列名不为主键，但是 列名 创建索引  ：count(1) = count（*）= count(列名)   ：因为 explain 中 type 类型都为 index

如果表多个列并且没有主键，则 count（1） 的执行 = 优于 count（*） 
如果表只有一个字段，则 select count（*）和 select count（1） and select count（字段名）执行效率一样。

2. count(1) and count(字段) and count(*)

主要区别是

（1） count(1) 会统计表中的所有的记录数，包含字段为null 的记录。

（2） count(字段) 会统计该字段在表中出现的次数，忽略字段为null 的情况。即不统计字段为null 的记录。

  (3)    count(*) 会统计表中的所有的记录数，包含字段为null 的记录。 

3.执行效果上：  
count(*)包括了所有的列，相当于行数，在统计结果的时候，不会忽略列值为NULL  
count(1)包括了忽略所有列，用1代表代码行，在统计结果的时候，不会忽略列值为NULL  
count(列名)只包括列名那一列，在统计结果的时候，某个字段值为NULL时，不统计

				
f. 组合索引最左前缀***********************************************
   只有包含最左边即首部第一个字段的where条件，才会命中索引，否则不会命中索引。如例子：	
					
	index (a,b,c,d)
						
	where a=2 and b=3 and c=4 and d=5   --->命中索引
	where a=2 and b=3 and c=4 ——>命中
                        
	where a=2 and c=3 and d=4   ----> 命中
    where b=2 and c=3	----->未命中
					
	什么时候会创建联合索引？
						
	根据公司的业务场景， 在最常用的几列上添加索引
						
	select * from user where name='zekai' and email='zekai@qq.com';
						
	如果遇到上述业务情况， 错误的做法：
	index ix_name (name),
	index ix_email(email)
						
	正确的做法：
	index ix_name_email(name, email)
										
	如果组合索引为：
    ix_name_email (name,email) ************
					
	where name='zekai' and email='xxxx'       -- 命中索引
					
	where name='zekai'   -- 命中索引
	where email='zekai@qq.com'                -- 未命中索引

1|08. explain查看SQL语句执行情况

 
explain 关键字的作用：
					
mysql> explain select * from user where name='zekai' and email='zekai@qq.com'\G
					*************************** 1. row ***************************
							   id: 1          
					  select_type: SIMPLE    
							table: user
					   partitions: NULL
							 type: ref       索引指向 all
					possible_keys: ix_name_email     可能用到的索引
							  key: ix_name_email     确实用到的索引
						  key_len: 214            索引长度
							  ref: const,const
							 rows: 1            扫描的行数
						 filtered: 100.00
							Extra: Using index   使用到了索引

注意：判断是否命中索引最准确的还是要看 rows 这一行的结果。

2|89. 索引覆盖

 
select id from user where id=2000; 
#where 条件的字段和查询字段相同

​

2|910.慢日志管理

步骤：

  1. 查看慢日志的变量

    show variables like '%slow%';

+---------------------------+-----------------------------------------------+
				| Variable_name             | Value                                         |
				+---------------------------+-----------------------------------------------+
				| log_slow_admin_statements | OFF                                           |
				| log_slow_slave_statements | OFF                                           |
				| slow_launch_time          | 2   # 指定最大查询时间，超过就会被记录到日志                                          |
				| slow_query_log            | OFF   # 默认关闭慢SQl查询日志， on                                          |
				| slow_query_log_file       | D:\mysql-5.7.28\data\DESKTOP-910UNQE-slow.log | # 慢SQL记录的位置
				+---------------------------+-----------------------------------------------+
				5 rows in set, 1 warning (0.08 sec)


配置慢SQL的变量：
		
			
set global slow_query_log = on; # 开启日志功能
			
set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log"; # 设定日志保存的目录
			
set global long_query_time=1; # 设定最大查询时间

__EOF__

本文作者：BigSun丶
本文链接：https://www.cnblogs.com/Mcoming/p/11773021.html
关于博主：评论和私信会在第一时间回复。或者直接私信我。
版权声明：本博客所有文章除特别声明外，均采用 BY-NC-SA 许可协议。转载请注明出处！
声援博主：如果您觉得文章对您有帮助，可以点击文章右下角【推荐】一下。您的鼓励是博主的最大动力！
posted @   BigSun丶  阅读(1720)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 25岁的心里话
· 闲置电脑爆改个人服务器（超详细） #公网映射 #Vmware虚拟网络编辑器
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· 零经验选手，Compose 一天开发一款小游戏！
· 一起来玩mcp_server_sqlite，让AI帮你做增删改查！！
点击右上角即可分享
微信分享提示