pymysql操作 索引 慢日志配置
python 操作mysql
安装 pymysql
pip install pymysql
连接与增删改查
### 连接数据库的参数
conn =p ymysql.connect(
host='lockhost',user='root',
password='123qwe',database='text',
charset='utf8')
# cursor = cnn.cursor() 默认返回的值是元组类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 返回的是字典形式
sql = 'sql语句 ' # 用%s代替传入的数据
cursor.execute(sql,(传入正真的数据)) # 执行的语句
cursor.close()
conn.close()
# 查:
fetchal(): 取出所有的数据,返回的是列表套字典
fetchone():取出一条数据 返回的是字典
fetchmany():取出size条数据 返回的列表套字典
sql = "select * from user where name=%s and password=%s"
cursor.execute(sql, (user, pwd))
res = cursor.fetchall() ###取出所有的数据 返回的是列表套字典
# 增
sql = 'insert into user (name.password) values (%s,%s)'
cursor.execute(sql,('xxx','qwe'))
conn.commit()
# 改
sql = 'update user set name=%s where id=%s'
cursor.execute(sql,('xxx',2))
conn.commit()
# 删
sql = 'delete from user where id=%s'
cursor.execute(sql, ('dgsahdsa', 2))
conn.commit()
sql注入问题
输入用户名:zekai ' or 1=1 #
输入密码:dsadsa
select * from user where name='zekai ' or 1=1 #' and password='dsadsa'
当用户输入 ' or 1=1 # 时 会把语句传入sql指令中,那么最终执行的结果就会越过用户名和密码
[{'id': 1, 'name': 'zhangsan', 'password': ''}, {'id': 2, 'name': 'zekai', 'password': '123'}, {'id': 3, 'name': 'kkk', 'password': ''}]
产生的原因: 因为过于相信用户输入的内容,根本没有进行校验
解决方法:
sql = 'select * from user where name=%s and password=%s'
cursor.execute(sql,(user,pwd))
索引
索引的作用
使用索引是为了提高查询的效率
类比: 字典中的目录
索引的本质
一个特殊的文件
索引的底层原理
B+树
索引的种类
主键索引:
primary key
- 加速查找
- 不能重复
- 不能为空
主键索引的创建
# 方法一:
# 新增主键索引:
create table xxx(
id int auto_increment primary key
# 或者 primary key(id)
)charset utf8
# 方法二:
alter table xxx change id id int auto_increment primary key;
# 方法三:
alter table xxx add primary key(id);
# 删除主键索引:
alter table XXX drop primary key;
唯一索引
nuique 索引名 (字段名)
- 加速查找
- 不能重复
# 方法一:
# 新增唯一索引:
create table xxx(
id int auto_increment primary key,
name varchar(32) not null default'',
unique u_name (name)
)charset utf8
# 方法二:
create unique index ix_name on xxx(name);
# 方法三:
alter table xxx add unique ix_name (name);
# 删除唯一索引:
alter table XXX drop index u_name;
联合唯一索引
unique(name,email)
普通索引
- 加速查找
# 方法一:
# 新增普通索引:
create table xxx(
id int auto_increment primary key,
name varchar(32) not null default'',
index u_name (name)
)charset utf8
# 方法二:
create index ix_name on xxx(name);
# 方法三:
alter table xxx add index ix_name (name);
# 删除唯一索引:
alter table XXX drop index u_name;
索引的优缺点
通过观察*.idb文件可知:
- 索引加快了查询速度
- 但加了索引之后,会占用大量的磁盘空间
索引并不是加的越多越好
不会命中索引的情况
-
不能再SQL语句中 进行四则运算,会降低SQL的查询效率
-
使用函数
如:select * from tb1 where reverse(email)='zzz';
-
类型不一致
如果列是字符串类型,传入条件必须是用引号引起来
排序条件为索引,则select字段必须也是索引字段,否则无法命中
-
order by
select name from s1 order by email desc;
当根据索引排序时候,select查询的字段如果不是索引,则速度仍然很慢
select * from tb1 order by nid desc;
若对主键排序,则速度还是很快的
-
count(1) 或count(列) 代替 count(*)在mysql中没差别
-
组合索引最左前缀
什么时候会创建联合索引
根据公司的业务场景,在最常用的几列上添加索引
比如:select * from user where name ='123' and emael='23123';
正确添加索引:
index ix_name_email(name,email)
如果组合索引为:index ix_name_email(name,email)
where name='zekai' and email='xxx' ---命中索引
where name ='zekai' ---命中索引
where name = '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 ----> 未命中
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的相关变量
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记录的位置 |
+---------------------------+-----------------------------------------------+
# 查看mysql日志的属性
mysql> show variables like '%long%';
+----------------------------------------------------------+-----------+ | Variable_name | Value |
+----------------------------------------------------------+-----------+
| long_query_time | 10.000000 |
+---------------------------+------------------------------------------+
# 配置SQL的变量
set global 变量名 = 值
set global show_query_log=on # 开启慢日志
set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log"; # 保存的目录
set global log_query_time =1; # 记录查询时间大于1秒的SQL指令