day44

目录

• pymysql操作mysql
  • 安装：
  • 使用的时候导入：
  • 连接数据库的参数：
• pymysql的sql注入
  • sql注入问题
  • 产生的原因：
  • 解决的方法：
  • 连接：
  • 查：
  • 增：
  • 修：
  • 删除：
• 索引
  • 为什么实用索引以及索引的作用：
  • 类比：
  • 索引的本质：
  • 索引的底层原理：
  • 索引的种类：（********************）
    • 主键索引：
    • 唯一索引：
      • 联合唯一索引：
    • 普通索引：
      • 联合（组合）索引:
  • 索引的创建：
    • 主键索引：
      • 新建主键索引：
      • 删除主键索引：
    • 唯一索引：
      • 新增：
      • 删除
    • 普通索引：
      • 新增：
      • 删除：
    • 索引的优缺点：
      • 不会命中索引的情况：
  • 慢查询日志：

pymysql操作mysql

安装：

pip install pymysql

使用的时候导入：

import pymysql

连接数据库的参数：

import pymysql
conn = pymysql.connect(host='localhost',user='root',password='123',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) # 指定获取多少条数据 返回的是列表套字典
print(res) # 元组类型
cursor.close()
conn.close()

pymysql的sql注入

sql注入问题

输入用户名： zekai ' or 1=1 #
输入密码： asd
select * from user where name = 'zekai' or 1=1 #' and password = 'asd'

产生的原因：

因为过于相信用户输入的内容，根本没有做任何的检验

解决的方法：

sql = f"select * from user where name ={user} and password ={pwd}"

cursor.execute(sql,(user,pwd))

连接：

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

查：

fetchall():取出所有的数据 返回的是列表套字典

fetchone():取出一条数据 返回的是字典

fetchmany(size):取出size条数据 返回的是列表套字典

增：

sql = 'insert omtp user (name,password)values(%s,%s)'
data = [
    ('zekai1','qwe'),
    ('zekai2','qwe1'),
    ('zekai3','qwe2'),
    ('zekai4','qwe3'),
]
cursor.executemany(sql,data)

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

修：

sql = 'update user set name =%s where id=%s'
cursor.execute(sql,('asd',2))
conn.commit()
cursor.close()
conn.close()

删除：

sql = 'delete from user where id=%s'
cursor.execute(sql,('asd',2))
conn.commit()
cursor.close()
conn.close()

索引

为什么实用索引以及索引的作用：

实用索引就是为了提高查询效率的

类比：

字典中的目录

索引的本质：

一个特殊的文件

索引的底层原理：

B+树

索引的种类：（********************）

主键索引：

加速查找 + 不能重复 + 不能为空 primary key

唯一索引：

加速查找 + 不能重复 unique(name)

联合唯一索引：

unique(name,email)

例子：

zekai 123@qq.com

普通索引：

加速查找 index(name)

联合（组合）索引:

index(name,email)

索引的创建：

主键索引：

新建主键索引：

create table xxx(
	id int auto_increment,
    primary key(id)
)
alter table xxx change id id int auto_crement paimary_key;
alter table t1 add primary key(id);

删除主键索引：

alter table t1 drop primary key;

唯一索引：

新增：

# 1.
create table t2(
	id int auto_increment primary key,
	name varchar(32) not null default '',
	unique u_name(name)
)charset utf8;

# 2.
create unique index 索引名 on 表名（字段名）;
create unique index ix_name on t2(name);

# 3.
alter table t2 add unique index ix_name(name);

删除

alter table t2 drop index u_name;

普通索引：

新增：

# 1.
create table t3(
	id int auto_increment primary key,
	name varchar(32) not null default'',
	index u_name(name)
)charset utf8;

# 2.
create index 索引名 on 表名（字段名）;
create index ix_name on t3(name);

# 3.
alter table t3 add index ix_name(name);

删除：

alter table t3 drop index u_name;

索引的优缺点：

通过观察 *.ibd文件可知：

1. 索引加快了查询速度
2. 但加了索引之后，会占用大量的磁盘空间

索引加的越多越好吗？

不是

不会命中索引的情况：

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(*)在mysql中没有差别了

f.组合索引最左前缀

​ 什么时候会创建联合索引？

根据公司的业务场景， 在最常用的几列上添加索引
						
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'                -- 未命中索引
					
例子：	
					
index (a,b,c,d)

where a=2 and b=3 and c=4 and d=5   --->命中索引
					
where a=2 and c=3 and d=4   ----> 未命中

g:

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   使用到了索引

索引覆盖：

select id from user where id =2000;

慢查询日志：

查看慢SQL的相关变量

				mysql> 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)
				
				mysql> show variables like '%long%';
				+----------------------------------------------------------+-----------+
				| Variable_name                                            | Value     |
				+----------------------------------------------------------+-----------+
				| long_query_time                                          | 10.000000 |
				

配置慢SQL的变量：

set global 变量名 = 值
				 
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;