day36

一、python操作mysql

1. pymysql的安装
   pip install pymysql

2. sql注入问题
   产生的原因

因为过于相信用户输入的内容，根本没有做任何的检验

解决的方法

sql = "select * from user where 字段1=%s and 字段2=%s"

cursor.execute(sql, (值1, 值2))

3. 连接

连接数据库的参数

conn=pymysql.connect(host='localhost',user='root',password='123qwe',database='test',charset='utf8')

cursor = conn.cursor() ### 默认返回的值是元祖类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的值是字典类型 (*********)

4. 增

设定sql语句

sql = sql增加数据语句

设定要加入的数据

data = [(),(),()...]

执行到数据库中

cursor.executemany(sql,data)

提交

conn.commit()
5. 查
fetchall() ： 取出所有的数据 返回的是列表套字典
fetchone() ： 取出一条数据 返回的是字典
fetchmany(size) ： 取出size条数据 返回的是列表套字典

6. 改

设定sql修改语句

sql = sql修改数据语句

执行到数据库中

cursor.execute(sql,(sql语句中要添加的数据))

提交

cursor.commit()
7. 删

设定SQL删除语句

sql = sql删除数据语句

执行到数据库中

cursor.execute(sql,(sql语句中要删除的数据))

提交

cursor.commit()
二、索引

1. 为啥使用索引以及索引的作用
   使用索引就是为了提高查询效率

2. 索引的本质
   索引本质上是一个特殊的文件

3. 索引的底层原理
   B+树

4. 索引的种类
   主键索引：primary key

特点：加快查找+不能重复+不能为空

唯一索引：unique(字段名)

联合唯一索引：unique(字段名1，字段名2)
特点：加快查找+不能重复

普通索引：index(字段名)

联合索引：index(字段名1，字段名2)
特点：加快查找

5. 索引的创建与删除
   5.1 创建主键索引

在建表的时候创建：

create table 表名(
id int auto_increment primary key,
name varchar(32)
)charset=utf8;
在已有的一张表上添加：

1. alter table 表名 change 旧字段名 新字段名 类型约束 primary key;

2. alter table 表名 add primary key (字段名);
   5.2 删除主键索引

alter table 表名 drop primary key;
5.3 创建唯一索引

在建表的时候创建：

1.1 直接在创建字段语句后面加 unique，此时不用写索引名，默认索引名为字段名。

create table 表名(
id int auto_increment primary key,
name varchar(32) not null default '' unique
)charset=utf8;

1.2 在创建完所有字段后，再在之后添加唯一索引。

create table 表名(
id int auto_increment primary key,
name varcher(32) not null default '',
unique u_name (name)
)charset=utf8;
在一张已有的表上添加唯一索引

1. create unique index 索引名 on 表名 (字段名);

2. alter table 表名 add unique index 索引名 (字段名)；

3. alter table 表名 change 旧字段名 新字段名 类型约束 unique;
   5.4 删除唯一索引

alter table t2 drop index 唯一索引名;
5.5 创建普通索引

在建表的时候创建：

create table 表名(
id int auto_increment primary key,
name varcher(32) not null default '',
index u_name (name)
)charset=utf8;
在一张已有的表上创建普通索引

1. create index 索引名 on 表名 (字段名);

2. alter table 表名 add index 索引名 (字段名)；
   5.6 删除普通索引

alter table t3 drop index 索引名；
6. 索引的优缺点
通过观察*.ibd文件(MySQL表文件)可知：

优点：索引加快了查询速度
缺点：加了索引后，文件的变大了很多，会占用更多的磁盘空间
7. 不会命中索引的情况
不能在SQL语句中，进行四则运算，不然会降低SQL的查询效率

使用函数也会命不中索引

select * from t1 where reverse(id) = 123;
类型不一致

如果列是字符串类型，传入条件是必须用引号引起来，不然可能会无法命中索引
select * from t1 where name = tbw;

order by

排序条件为索引，则select字段必须也是索引字段，否则无法命中(order by)

select name from t1 order by email desc;

当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢

select email from s1 order by email desc;

特别的，如果对主键排序，则还是速度很快：

select * from tb1 order by id desc;

count(1)或count(列)代替count(*)在mysql中没有差别了

组合索引最左前缀

如果组合索引为：ix_name_email (name,email)：

where name = 值1 and email = 值2; 命中索引
where name = 值1; 命中索引
where email = 值2; 未命中索引
如果组合索引为：ix_name_email_age (name, email, age):

where name = 值1 and email = 值2 and age = 值3; 命中索引
where name = 值1 and email = 值2; 命中索引
where name = 值1 and age = 值3; 命中索引
where email = 值2 and age = 值3; 未命中索引
where age = 值3; 未命中索引
结论：只要索引中有组合索引的最左索引字段，就可以命中索引（善用explain）

三、慢查询日志

1. 查看慢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.28dataDESKTOP-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 |
2. 配置慢sql的变量
set global 变量名 = 值

set global slow_query_log = on;

set global slow_query_log_file="D:/mysql-5.7.28/data/myslow.log";

set global long_query_time=1;