day36-pymysql模块的使用、索引

目录

• pymysql模块的使用
  • 连接数据库
  • SQL注入问题
  • 增加数据
  • 修改数据
  • 删除数据
  • 插入大量数据
• 索引
  • 索引的目的
  • 索引的本质
  • 索引的底层原理
  • 索引的种类
    • 主键索引
    • 唯一索引
      • 联合唯一索引
    • 普通索引
      • 联合索引
  • 索引的创建
    • 主键索引
      • 新增
      • 删除
    • 唯一索引
      • 新增
      • 删除
    • 普通索引
      • 新增
      • 删除
  • 索引的优缺点
  • 不会命中索引的情况
  • 索引覆盖
  • 慢查询日志

pymysql模块的使用

连接数据库

import pymysql
#连接数据库
conn = python.connect(
	host='localhost',
	user='root',
	password='123456',
    database='test',
    charset='utf8',
    autocommit='True'  #自动提交
)

#获取游标,并设置返回类型为字典
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql = 'select * from t1'
cursor.execute(sql)

#fetch返回的是列表套字典
res = cursor.fetchall() #取出所有数据
#res = cursor.fetchone() 取出一条数据
#res = cursoe.fetchmany() 取出指定数量的数据
print(res)

#关闭游标
cursor.close()
#关闭连接
conn.close()

SQL注入问题

因为用户可能会输入SQL语句关键字

import pymysql

user = input('输入用户名:').strip()
pwd = input('输入密码:').strip()

#解决方式一
#在用户输入信息后，自定义检测


#### 接下来对用户输入的值进行检验

### 连接数据库的参数
conn = pymysql.connect(host='localhost',user='root',password='123qwe',database='test',charset='utf8')
# cursor = conn.cursor() ### 默认返回的值是元祖类型
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) ### 返回的值是字典类型 (*********)

# sql = "select * from user where name='%s' and password='%s'" % (user, pwd)
#解决方式二 pysql自带优化
sql = "select * from user where name=%s and password=%s"

cursor.execute(sql, (user, pwd))
res = cursor.fetchall()  ###取出所有的数据 返回的是列表套字典
print(res)

cursor.close()
conn.close()

if res:
    print('登录成功')
else:
    print('登录失败')

增加数据

sql = "insert into user (name, password) values (%s,  %s)"

cursor.execute(sql, ('dshadhsa', 'dbsjabdjsa'))  ### 新增一条数据
# cursor.executemany(sql, data)  ### 新增多条数据

#print(cursor.lastrowid)   ### 获取最后一行的ID值

修改数据

sql = "update user set name=%s where id=%s"

cursor.execute(sql, ('dgsahdsa', 2))

删除数据

sql = "delete from t3 where id=%s"

cursor.execute(sql, (1,))

插入大量数据

sql = "insert into user (name, email) values (%s,  %s)"

data = []
for i in range(3000000):
    info = ('zekai'+str(i), 'zekai' + str(i) + '@qq.com' )
    data.append(info)
#在插入大量数据时，应先生成数据，再提交，速度快

cursor.executemany(sql, data)  ### 新增多条数据

索引

索引的目的

为了提高查询效率

索引的本质

一个特殊的文件

索引的底层原理

B+树

索引的种类

主键索引

加速查找，不能重复，不能为空primary key

唯一索引

加速查找，不能重复 unique

联合唯一索引

-- unique(列1，列2，...)

普通索引

加速查找 index(字段)

联合索引

index(字段1，字段2，。。。)

索引的创建

主键索引

新增

create table xxx(
						id int auto_increment ,
						primary key(id)
					)

-- alter table 表名 change 旧字段名 新字段名 新字段属性 primary key;

-- alter table t1 add primary key (id);

删除

alter table t1 drop primary key;

唯一索引

新增

create table t2(
   id int auto_increment primary key,
   name varchar(32) not null default '',
   unique u_name (name)
)charset utf8

-- CREATE  UNIQUE   INDEX  索引名 ON 表名 (字段名) ;
create  unique index ix_name on t2(name);

alter table t2 add unique index ix_name (name)

删除

alter table t2 drop index u_name;

普通索引

新增

create table t3(
   id int auto_increment primary key,
   name varchar(32) not null default '',
   index u_name (name)
)charset utf8

CREATE  INDEX  索引名 ON 表名 (字段名) ;
   create   index ix_name on t3(name);

alter table t3 add  index ix_name (name);

删除

alter table t3 drop index u_name;

索引的优缺点

通过观察 *.ibd文件可知：

1. 加快了查询速度
2. 会占用大量的磁盘空间

不会命中索引的情况

1. 不能再SQL语句中，进行四则运算，会降低SQL的查询效率

2. 使用函数，例如reverse

3. 类型不一致

   -- 如果列是字符串类型，传入条件是必须用引号引起来
   select * from tb1 where email = '999@qq.com';
   

4. order by

   -- 排序条件为索引，则select字段必须也是索引字段，否则无法命中
   
   #当根据索引排序时候，select查询的字段如果不是索引，则速度仍然很慢
   select name from s1 order by email desc;
   
   #特别的：如果对主键排序，则还是速度很快：					
   select email from s1 order by id desc;
   					
   

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

6. 组合索引最左前缀

   -- index(字段1，字段2，...) 需要是包含最左(字段1)的子集
   

7. 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的字段和where的判断条件的字段一致
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_log_file="D:/mysql-5.7.28/data/myslow.log";
      
      set global long_query_time=1;