python — 索引与pymysql模块
1. 索引
1.1 索引原理
1.什么是索引 ?-- 目录
索引就是建立起的一个在存储表阶段就有的一个存储结构,能在查询的时候加速。
2.索引的重要性:
读写比例 为 10:1,所有读(查询)的速度就至关重要了。
3.索引的原理:
block 磁盘预读原理
相当于读文件操作的 :for line in f
每个索引块可以存4096个字节
读硬盘的io操作的时间非常的长,比cpu执行指令的时间长很多,尽量的减少io次数才是读写数据的主要要解决的问题。
1.2 数据库的存储方式
1.数据库的存储方式:
-
1.新的数据结构 —— 树
缺点:容易出现数据只在一端,造成读取慢(io操作次数多)。
-
2.平衡树 balance tree - b树
缺点:存储的数据长度大、能存储的数据条数有限,造成书的高度比较大,读取效率很低。
-
3.在b树的基础上进行了改良 - b+树
- 1.分支节点和根节点都不在存储实际的数据了,让分支和根节点能存储更多的索引的信息,就降低了树的高度,所有的实际数据都存储在叶子节点中
- 2.在叶子节点之间加入了双向的链式结构,方便在查询中的范围条件。
4.mysql当中所有的b+树索引的高度都基本控制在3层:
- 1.io操作的次数非常稳定
- 2.有利于通过范围查询
5.什么会影响索引的效率? —— 树的高度
- 1.对哪一列创建索引,选择尽量短的列做索引
- 2.对区分度高的列建索引,重复率超过了10%,那么就不适合创建索引。
1.3 聚集索引和辅助索引
-
在innodb中 :聚集索引和辅助索引并存的
聚集索引 - 主键 、更快
只有主键是聚集索引
-
数据直接存储在树结构的叶子节点
辅助索引 - 除了主键之外所有的索引都是辅助索引 、稍慢
-
数据不直接存储在树中
在myisam中 :只有辅助索引,没有聚集索引
1.4 索引的种类
1.索引的种类:
-
primary key 主键 ,是聚集索引 ,约束的作用:非空 + 唯一
联合主键
-
unique 自带索引 ,是辅助索引 ,约束的作用:唯一
联合唯一
-
index 是辅助索引,没有约束作用
联合索引
注意:按大项分有三个种类:primary key 、unique 、index
细分有6个种类:primary key 、联合主键 、unique 、联合唯一 、index 、联合索引
2.看一下如何创建索引、创建索引之后的变化
create index 索引名字 on 表(字段)
删除索引 :drop index 索引名 on 表名字;
3.索引是如何发挥作用的?
select * from 表 where id = xxxxx;
- 以email为条件查询:
- 不添加索引的时候,肯定慢
- 查询的字段不是索引字段,也慢
- id作为条件的时候:
- 在id字段没有索引的时候,效率低
- 在id字段有索引之后,效率高
1.5 索引不生效的原因
1.索引不生效的原因:
<1.>要查询的数据的范围大
与范围相关的:
1.< >= <= !=(!=几乎命中不了索引)
-
2.between and
select * from 表 order by age limit 0,5;
select * from 表 where id between 1000000 and 1000005;
-
3.like
- 结果的范围大 索引不生效
- 如果 abc% 索引生效,%abc索引就不生效
<2.>如果一列内容的区分度不高,索引也不生效
- 如:name列
<3.>索引列不能在条件中参与计算
- select * from s1 where id*10 = 1000000; 索引不生效
<4.>对两列内容进行条件查询
-
and :and条件两端的内容,优先选择一个有索引的,并且树形结构更好的,来进行查询(效率也会更高)。两个条件都成立才能完成where条件,先完成范围小的,缩小后面条件的压力。
- select * from s1 where id =1000000 and email = 'eva1000000@oldboy';
-
or :带or条件的,不会进行优化,只是根据条件从左到右依次筛选。
条件中带有or的要想命中索引,这些条件中所有的列都是索引列。
- select * from s1 where id =1000000 or email = 'eva1000000@oldboy';
<5.>联合索引
创建联合索引:create index ind_mix on s1(id,name,email);
select * from s1 where id =1000000 and email = 'eva1000000@oldboy'; 能命中索引
-
1.在联合索引中如果使用了or条件索引就不能生效:
select * from s1 where id =1000000 or email = 'eva1000000@oldboy'; 不能命中索引
-
2.最左前缀原则 :在联合索引中,条件必须含有在创建索引的时候的第一个索引列。
select * from s1 where id =1000000; 能命中索引 select * from s1 where email = 'eva1000000@oldboy'; 不能命中索引 # 联合索引 (a,b,c,d) a,b 、 a,c 、 a 、 a,d 、 a,b,d 、 a,c,d 、 a,b,c,d # 等含有a索引的都能命中索引 # 不含a索引的其他索引都不能命中。
-
3.在整个条件中,从开始出现模糊匹配的那一刻,索引就失效了
select * from s1 where id >1000000 and email = 'eva1000001@oldboy'; 不能命中索引 select * from s1 where id =1000000 and email like 'eva%'; 能命中引
2.什么时候用联合索引?
- 只对a 、对abc 条件进行索引,而不会对b,对c进行单列的索引的时候。
3.对于单列的索引:
- 选择一个区分度高的列建立索引,条件中的列不要参与计算,条件的范围尽量小,使用and作为条件的连接符
4.使用or来连接多个条件:
- 在满足上述条件(单列的索引)的基础上,对or相关的所有列分别创建索引。
1.6 一些索引名词
1.覆盖索引
如果我们使用索引作为条件查询,查询完毕之后,不需要回表查,就是覆盖索引。
explain select id from s1 where id = 1000000;
explain select count(id) from s1 where id > 1000000;
2.合并索引
对两个字段分别创建索引,由于sql的条件让两个索引同时生效了,那么这个时候这两个索引就成为了合并索引
3.执行计划 explain
如果你想在执行sql语句之前就知道sql语句的执行情况,那么可以使用执行计划。
# 情况1: 如果有30000000条数据,使用sql语句查询需要20s, explain sql语句 --> 并不会真正的执行sql,而是会给你列出一个执行计划 # 情况2: 20条数据 --> 30000000 explain sql
4.建议
<1>建表、使用sql语句的时候注意的:
- char 代替 varchar
- 连表 代替 子查询
- 创建表的时候 :固定长度的字段放在前面
<2> utf8 与 utf8mb4 :
- utf8 不是能全量显示中文的编码,如很多不常用的生僻字 和 emoji 表情(emoji 是一种特殊的 unicode 编码,常见于 ios 和 android 手机上),以及任何新增的 unicode 字符等等
- utf8mb4 能全量显示中文的编码
以后如果遇到使用 utf8 出现乱码的情况,可以更改为 utf8mb4 进行编码。
1.7 慢查询优化的基本步骤
- 先运行看看是否真的很慢,注意设置sql_no_cache
1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
2.explain查看执行计划,是否与第1步预期一致(从锁定记录较少的表开始查询)
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务方使用场景
5.加索引时参照建索引的几大原则
6.观察结果,不符合预期继续从0分析
1.8 慢日志管理
1.慢日志
- 执行时间 > 10
- 未命中索引
- 日志文件路径
2.配置:
-
内存
show variables like '%query%';
show variables like '%queries%';
set global 变量名 = 值
-
配置文件
mysqld --defaults-file='e:\wupeiqi\mysql-5.7.16-winx64\mysql-5.7.16-winx64\my-default.ini'
my.conf内容:
- slow_query_log = on
- slow_query_log_file = d:/....
注意:修改配置文件之后,需要重启服务
3.日志管理
详见网址:https://www.cnblogs.com/eva-j/articles/10126413.html#_label8
2. pymysql模块
2.1 使用pymysql模块
python相当于是客户端
import pymysql conn = pymysql.connect(host='127.0.0.1', user='root', password="123",database='day40') # python与mysql连接 cur = conn.cursor() # 创建 数据库操作符:游标 # 增加数据 cur.execute('insert into employee(emp_name,sex,age,hire_date) ' 'values ("郭凯丰","male",40,20190808)') # 删除数据 cur.execute('delete from employee where id = 18') conn.commit() # 提交 conn.close() # 查询数据 import pymysql conn = pymysql.connect(host='127.0.0.1', user='root', password="123",database='day40') cur = conn.cursor(pymysql.cursors.dictcursor) # 想要输出为字典格式时加上pymysql.cursors.dictcursor cur.execute('select * from employee where id > 10') ret = cur.fetchone() # 查询第一条数据 print(ret['emp_name']) ret = cur.fetchmany(5) # 查询5条数据 ret = cur.fetchall() # 查询所有的数据 print(ret) conn.close()
2.2 数据备份和事务
1.数据库的逻辑备份
语法:mysqldump -h 服务器 -u用户名 -p密码 数据库名 > 备份文件.sql
#示例: #单库备份 mysqldump -uroot -p123 db1 > db1.sql mysqldump -uroot -p123 db1 table1 table2 > db1-table1-table2.sql #多库备份 mysqldump -uroot -p123 --databases db1 db2 mysql db3 > db1_db2_mysql_db3.sql #备份所有库 mysqldump -uroot -p123 --all-databases > all.sql
2.数据恢复
#方法一: [root@egon backup]# mysql -uroot -p123 < /backup/all.sql #方法二: mysql> use db1; mysql> set sql_log_bin=0; #关闭二进制日志,只对当前session生效 mysql> source /root/db1.sql
3.事务
begin; # 开启事务 select * from emp where id = 1 for update; # 查询id值,for update添加行锁; update emp set salary=10000 where id = 1; # 完成更新 commit; # 提交事务(解锁)
注意3个关键点:
- begin
- commit
- for update
2.3 sql注入
create table userinfo( id int primary key auto_increment, name char(12) unique not null, password char(18) not null ) insert into userinfo(name,password) values('alex','alex3714') # 用户名和密码到数据库里查询数据 # 如果能查到数据 说明用户名和密码正确 # 如果查不到,说明用户名和密码不对 username = input('user >>>') password = input('passwd >>>') sql = "select * from userinfo where name = '%s' and password = '%s'"%(username,password) print(sql) -- :表示注释掉--之后的sql语句 select * from userinfo where name = 'alex' ;-- and password = '792164987034'; select * from userinfo where name = 219879 or 1=1 ;-- and password = 792164987034; select * from userinfo where name = '219879' or 1=1 ;-- and password = '792164987034';
上面的输入情况都能查询到结果,所以存在安全隐患,这种存在安全隐患的情况就叫 sql注入。
为了避免 sql注入,在用pymysql时,不要再自己去拼接sql语句了,要让mysql模块自己去拼接。
import pymysql conn = pymysql.connect(host = '127.0.0.1',user = 'root', password = '123',database='day41') cur = conn.cursor() username = input('user >>>') password = input('passwd >>>') sql = "select * from userinfo where name = %s and password = %s" cur.execute(sql,(username,password)) # 让mysql模块去拼接 print(cur.fetchone()) cur.close() conn.close()
上一篇: 平安好医生借助大数据释放医疗服务产能
下一篇: linux进程管理总结
推荐阅读
-
Python3.5内置模块之time与datetime模块用法实例分析
-
Python中time模块与datetime模块在使用中的不同之处
-
Python模块、包(Package)概念与用法分析
-
Python函数与模块编程
-
Python3.5 Pandas模块缺失值处理和层次索引实例详解
-
Python3.5基础之NumPy模块的使用图文与实例详解
-
python 之 数据库(多表查询之连接查询、子查询、pymysql模块的使用)
-
python — 索引与pymysql模块
-
python PrettyTable模块的安装与简单应用
-
Python参数解析模块sys、getopt、argparse使用与对比分析