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	[e-mail]

​	zekai	[e-mail]	(error错误数据)

因为zekai  [e-mail] 已经存在了，所以下面的一条数据就不能重复再次出现，
所以执行的时候才会报错。

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='[e-mail]';
		如果遇到上述业务情况，错误的做法是添加两个普通索引：
		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='[e-mail]'	---未命中索引
		例子：
			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='[e-mail]'\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;