python索引用法_MySQL python操作MySQL 索引

使用python 操作MySQL

先去客户端下载安装pymysql 模块: pip install pymysql

import pymysql

user = input('输入用户名：').strip()

pwd = input('输入密码：').strip()

连接数据库用到的参数

conn = pymysql.connect(host='localhost', # 指定连接本地服务器

user='root', # 登录服务器 用的用户名

password='123', # 登录服务器用的密码

database='test', # 指定目标数据库

charset='utf8')

# cursor = conn.cursor() # 调用conn.cursor()实例化一个对象命名为cursor，用于操作数据库。默认返回的值是元祖类型

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 规定返回的值为字典类型，否则默认返回元组类型

sql = "select * from user where name= %s and password= %s " # 编辑一条指令

cursor.execute(sql，(user,pwd)) # cursor调用execute方法执行sql指令,execute会先转译和检验参数，符合规范才传给sql。

conn.commit()

用res接收返回的数据fetchone返回一条(字典类型)数据，fetchall、fetchmany返回一个(包含多条字典数据)列表

res = cursor.fetchall() # 取出所有的数据 返回的是列表套字典

res = cursor.fetchone() # 取出一条数据 返回的是字典类型

res = cursor.fetchmany(12) # 制定获取多少条数据 返回的是列表套字典

print(res)

cursor.close()

conn.close()

sql注入问题

注入问题是指：通过输入特殊字符，让校验代码失效，从而通过验证。

输入用户名:xxx ' or 1=1 #

输入密码:xxxx

select * from user where name='xxx' or 1=1 #' and password='xxxx'

[{'id': 1, 'name': 'zhangsan', 'password': ''}, {'id': 2, 'name': 'zekai', 'password': '123'}, {'id': 3, 'name': 'kkk', 'password': ''}]

解决方法： 对用户输入的内容进行过滤和规范

sql = "select * from user where name=%s and password=%s"

cursor.execute(sql, (user, pwd))

查

fetchall() ： 取出所有的数据 返回的是列表套字典

fetchone() ： 取出一条数据 返回的是字典

fetchmany(size) ： 取出size条数据 返回的是列表套字典

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

增

增加一条数据：

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

cursor.execute(sql, ('frank', '111')) ### 新增一条数据

conn.commit()

增加多条数据：

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

data = [

('allen2','1232'),

('allen3','1233'),

('allen4','1234'),

('allen5','1235')

]

cursor.executemany(sql, data)

conn.commit()

改：

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

cursor.execute(sql, (hugo, 987, 2))

conn.commit()

删：有外键的数据用这个方法删不掉

sql = "delete from test1 where id=%s;"

cursor.execute(sql, id)

conn.commit()

索引

作用：提高查询效率

本质：是一个特殊的文件

原理：B+树

分类：

主键索引： 加速查找 + 不能重复 + 不能为空 primary key

唯一索引： 加速查找 + 不能重复 unique(name)

联合唯一索引：unique(name, email)

​例子：

​zekai 123@qq.com

​zekai 123@qq.cm

普通索引： 加速查找 index (name)

联合索引： index (name, email)

索引的创建和删除

主键索引

创建主键索引

方法一、创建表时创建主键索引 写法一：

create table biao (

id int auto_increment primary key

)

方法二、创建表时创建主键索引 写法二：

create table b (

id int auto_increment,

primary key(id)

)

方法三、alter table biao change id id int auto_increment primary key;

方法四、alter table biao add primary key (id);

删除主键索引：

alter table biao drop primary key ;

唯一索引：

创建索引

方法一、创建表时创建索引

create table biao(

id int auto_increment primary key,

name varchar(32) not null default '',

unique u_name(name)

)

方法二、 create unique index 索引名 on 表名(字段名)；

​create unique index ix_name on biao(name);

方法三、alter table biao add unique index ix_name(name);

删除： alter table biao 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 biao(name);

删除：alter table biao drop index u_name;

索引的优缺点：

优点： 提高查询效率

缺点： 占用磁盘空间

不会命中索引的情况：

​a. 不能在SQl语句中，进行四则运算， 会降低SQL的查询效率

​b. 使用函数

​select * from tb1 where reverse(email) = 'zekai';

​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';

​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='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 --->命中索引

​where a=2 and c=3 and d=4 ----> 未命中

explain：是一个SQL的执行工具，会返回一个报告

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_log_file="D:/mysql-5.7.28/data/myslow.log";

set global long_query_time=1;