day 36
目录
pymysql操作mysql
安装
pip install pymysql
连接
# 连接数据库的参数 conn = pymysql.connect(host='localhost', user='root', password='123456', 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,('xxx','qwe')) 新增一条数据 data =[ ('zekai','qwe'), ('zekai1','qwe1'), ('zekai2','qwe2'), ('zekai3','qwe3'), ] cursor.executrmany(sql,data) 新增多条数据 #### 加如下代码 conn.commit()
删
sql = "delete from user where id=%s" cursor.execute(sql,('wsedrf',2)) conn.commit() cursor.close() conn.close()
改
sql = "update user set name=%s where id=%s" cursor.excute(sql,('wsedrf',2)) conn.commit cursor,close() conn.close()
查
fetchall() : 取出所有的数据,返回的是列表套字典 fetchone() : 取出一条数据,返回的是字典 fetchmany(size) : 取出size条数据,返回的是列表套字典
索引
为什么使用索引以及索引的作用
使用索引就是为了提高查询效率的
类比
字典中的目录
索引的本质
一个特殊的文件
索引的底层原理
b+树
索引的种类(重点)
主键索引
加速查找 + 不能重复 + 不能为空 primary key
唯一索引
加速查找 + 不能重复 unique (name)
联合唯一索引
unique(name, email)
普通索引
加速查找 index (name)
联合索引
index (name,email)
索引的创建
主键索引
新增主键索引
create table xxx( id int auto_increment, primary key(id) ) alter table xxx change id id int auto_increment primary 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 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
文件可知:
优点:索引加快了查询速度
缺点:加了索引之后,会占用大量的磁盘空间
索引加的越多越好
并不是
不会命中索引的情况
a.不能再sql
语句中,进行四则运算,会降低sql
的查询效率
b.使用函数:
select * from tb1 where reverse(email) = 'zakai';
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='xxx'
---命中索引
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=4 and d=5
--未命中索引
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_flie="d:/mysql-5.7.28/data/myslow.log"; set global long_query_time=1;