MySQL高级查询之索引创建、删除、增加、修改、慢sql、explain解释sql
day04数据库
昨日知识点回顾
1.单表操作
1.单表的操作
条件查询的优先级别：
where > group by >having > order by > limit;
分组：group by
select gender count(id) from xxx group by gender;
过滤筛选：having
select gender count(id) from xxx group by gender having count(id)>13;
排序：order by asc升序，默认，desc 降序
order by 要排序的字段名(id);
升序：asc 例子：order by id asc;
降序：desc 例子：order by id desc;
order by age asc,id desc;
先根据年龄升序，然后如果年龄一样的时候，再根据id进行降序。
分页：
limit offset,size;
offset:行数据的索引 size：取多少航数据
limit 起始值，终止值;
select * from 表名 limit 0,10; 取出前十条数据
2.多表关系
表与表之间的关系分为：一对多、一对一、多对多关系。
一对多：
create table department(
id int,
name varchar(32) not null default ''
)charset utf8;
create table userinfo(
id int,
name varchar(32) not null default '',
depart_id int,
constraint 外键名 foreign key(depart_id) references department(id)
)charset utf8;
多对多：
create table boy(
id int,
bname varchar(32) not null default ''
)charset utf8;
create table girl(
id int,
gname varchar(32) not null default ''
)charset utf8;
create table boy2girl(
id int,
bid int,
gid int,
constraint 外键名 foreign key(bid) references boy(id),
constraint 外键名 foreign key(gid) references girl(id)
)charset utf8;
一对一关系：
create table userinfo(
id int,
name varchar(32)
)charset utf8;
create table priv(
id int,
salary decimal(20,3),
pid int,
unique(pid),
constraint 外键名 foreign key(pid) references userinfo(id)
)charset utf8;
3.多表查询
多表查询中的语法：
left join......on
right join.....on
inner join......on
举例：
select * from department left join userinfo on department.id=userinfo.depart_id;
今日内容
2.python 操作MySQL数据库
安装pymysql
sql注入问题
输入用户名：zekai 'or 1=1 #
输入密码：dsadsa
select * from user where name='zekai' or 1=1 #' and password='dsdfssdd''
直接就可以登录成功，产生问题的原因在于：
特殊符号没有过滤，以及使用的关键字没有过滤，没有任何的校验。#相当于是注释，or只要满足一个条件即可，and是需要同时满足两个条件。
解决sql注入问题的方法:
通过execute执行函数，这是Python内部封装好的方法，只需要拿来使用即可。
sql='select * from user where name=%s and password=%s'
cursor.execute(sql,(user,pwd))
连接数据库：
conn=pymysql.connect(
host='localhost',
user='root',
password='root',
database='test',
charset='utf8'
)
cursor=conn.cursor(cursor=pymysql.cursors.DictCursor)##返回的是字典类型
查：
fetchall() :取出所有的数据，返回的是列表套字典
fetone() :取出一条数据，返回的是字典
fetchmany(size) :取出size条数据，返回的是列表套字典
增：
sql='insert into user(name,password) values(%s,%s)'
#cursor.execute(sql,('xxx','qwe'))这个是新增一条数据
下面是新增多条数据：
data=[
('zekai','qqq'),
('zekai2','www'),
('zekai3','eee'),
('zekai4','rrr'),
('zekai5','ttt')
]
cursor.executemany(sql,data) 新增多条数据
conn.commit() 加上这句代码，表示提交数据
打印最后一行的ID值：
print(cursor.lastrowid)
修改：
sql='update user set name=%s where id=%s'
cursor.execute(sql,('huaheshang',300))
conn.commit()
cursor.close()
conn.close()
删除：
sql='delete from user where id=%s'
cursor.execute(sql,(2,))
conn.commit()
cursor.close()
conn.close()
索引
为啥使用索引以及索引的作用：
使用索引就是为了提高查询效率的。
类比：
字典中的目录
索引的本质：
一个特殊的文件
索引的底层原理：
B+ 树
索引的种类：************************
分为：主键索引、唯一索引、联合唯一索引、普通索引
1.主键索引：加速查找+不能重复+不能为空 primary key
2.唯一索引：加速查找+不能重复 unique(字段名)
2.1联合唯一索引：unique（name,email） 就是这两个字段都是唯一并且不会重复的字段
联合唯一索引：unique（name,email） 就是这两个字段都是唯一并且不会重复的字段
例子如下：
zekai 123@qq.com
zekai 123@qq.com (error错误数据)
因为zekai 123@qq.com 已经存在了，所以下面的一条数据就不能重复再次出现，
所以执行的时候才会报错。
3.普通索引：加速查找 index（name）
3.1联合普通索引：index（name,email）
索引的创建：
主键索引：
新增主键索引：
增加主键索引的3种方式：
1.
create table xxx(
id int auto_increment primary key,
)charset utf8;
2.
alter table xxx change id id int auto_increment primary key;
3.
alter table t1 add primary key(id);
删除主键索引：
alter table t1 drop primary key;
唯一索引：
增加索引的3种方式：
1.
create table t2(
id int auto_increment primary key,
name varchar(60) 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);
删除索引的sql命令：
alter table t2 drop index u_name;
普通索引：
新增的三种方式：
1.
create table t3(
id int auto_increment primary key,
name varchar(60) 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.但是在加了索引之后，会占用大量的磁盘空间
索引是不是加的越多越好？
不是！！！！！！
不会命中索引的情况：
1.不能在sql语句中，进行四则运算，会降低sql的查询效率
2.使用函数，也会降低sql的查询效率
select * from tb1 where reverse(email)='zekai';
3.查询的数据类型不一致，也会降低sql的查询效率
如果列是字符串类型，where条件必须用引号引起来，不然会被警告
select * from tb1 where email=999; #这里面正确的应该是email='999'
4.排序条件为索引，则select查询的字段name必须也是索引字段，否则无法命中
order by
select name from s1 order by name desc; ----快
当根据索引排序的时候，select查询的字段如果不是索引，则速度仍然很慢
select email from s1 order by email desc; ----慢
特别的：
如果对主键排序，则还是速度很快,如下：
select * from tb1 order by id desc;
5.我们使用count(列名)对要查询的分组计算数量，更加具有针对性，不推荐使用count(*)
6.组合索引最左前缀
什么时候会创建联合索引？
根据公司的业务场景，在最常用的几个列上添加索引！！！
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 ----命中索引，因为只要a存在就可以
where a=2 and c=3 and d=4 ---命中索引，因为只要a存在就可以
where c=3 and d=4 ---未命中索引，因为a不存在查询失败
explain解释sql查询语句
explain select * from user where name='zekai' and email='zekai@qq.com'\G
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的相关变量
展示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.28910UNQE-slow.log | ## 慢SQL记录的位置
+---------------------------+------------------------------------+
5 rows in set, 1 warning (0.08 sec)
展示sql慢查询的时间变量：
show variables like '%long%';
+----------------------------------------------+-----------+
| Variable_name | Value |
+----------------------------------------------------------+
| long_query_time | 10.000000 |
配置慢sql的变量：
固定语法为：
set global 变量名 = 值
设置慢sql查询日志打开：
set global slow_query_log = on;
设置慢sql查询文件的保存位置：
set global slow_query_log_file = "D:/mysql-5.7/day004";
设置慢sql查询文件的时间：【sql查询时间超过1秒的sql语句，都会保存到指定的慢sql查询文件中】
set global long_query_time = 1;
