Python操作MySQL以及数据库索引

目录

python操作MySQL

安装

pip3 install pymsql

使用

方法：

  • conn = pymysql.conn()：实例化对象，建立mysql连接

    --- host：连接的mysql主机ip

    --- user：连接mysql的用户

    --- password：连接mysql的密码

    --- database：连接mysql的数据库

    --- charset：连接mysql的字符编码

  • cursor = conn.cursor()：建立游标

  • cursor.execute(sql)：执行sql语句

  • conn.commit()：提交，如果要增删改的话需要提交才能执行sql

  • cursor.fetchall()：取出sql执行完毕的所有数据，默认以元组结果展示

  • cursor.fetchone()：取出sql执行完毕的一条数据

  • cursor.fetchmany(size)：取出sql执行完毕的size条数据

  • conn.commit()：增删改数据库数据的时候要写上这个代码

  • cursor.lastrowid：获取最后一行记录

  • cursor.close()：关闭连接

  • conn.close()：关闭连接

import pymysql

user = input("请输入用户名").strip()
pwd = input("请输入密码").strip()

# 建立连接
conn = pymysql.connect(
    host="192.168.32.130",
    user="root",
    password="123",
    database="work",
    charset="utf8"
)

# cursor = conn.cursor()    # 执行完毕之后的结果默认以元组形式输出
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)     # 指定输出类型为字典

# 定义sql语句
# sql = "insert into pymysql_test (user ,pwd) values (%s,%s)"       # 增
# sql = "delete from pymysql_test where id=2;"                      # 删
# sql = "update pymysql_test set pwd="123" where id=3;"             # 改
sql = "select * from pymysql_test where user=%s and pwd=%s"       # 查
sql = "select * from pymysql_test where user='%s' and pwd='%s'" % (user,pwd)      # 查 演示sql注入，注意，占位符会把字符串类型的引号去掉，让它不再是字符串类型
# sql = "select * from pymysql_test"                                # 查

# 执行sql语句
# cursor.execute(sql)
cursor.execute(sql,(user,pwd))

# 如果要 增删改数据的话 需要commit，查不需要
conn.commit()

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

# 接收执行结果
res = cursor.fetchall()     # 取出所有数据
# res = cursor.fetchone()     # 取出一条数据
# res = cursor.fetchmany(2)     # 取出指定条数数据，如果指定条数超过数据库里已有的条目则全部展示，不会报错
print(res)

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

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

SQL注入问题

那么以上我们对pymysql进行了简单的使用，那么接下来有一个安全上的问题，就是sql注入问题

之所以产生sql注入问题的原因是 我们没有对用户的数据进行约束，用户在输入的时候可能会随便输入，就会引发sql注入问题：

我们正常登陆是这样的：

请输入用户名qinyj
请输入密码123
sql语句是：select * from pymysql_test where user='qinyj' and pwd='123'

登陆成功

那么有些用户可能随便输入用户名也能登陆成功，比如以下这种：

输入用户名：qinyj' or 1=1 #
输入密码：dsadsa

请输入用户名qinyj' #
请输入密码123
sql语句是： select * from pymysql_test where user='qinyj' #' and pwd='123'

登陆成功

以上用户名输入错误还能登陆成功的情况，是因为有 #号，mysql在碰到 这个符号之后不会执行后面的sql语句，所以相当于select * from pymysql_test查所有的数据

这种情况我们就称之为sql注入问题，这是一个非常严重的问题，在公司中开发是绝对不允许的，我们要解决这个问题可以有两种方法：

--- 1. 自己编写对用户输入的规则，通过后才执行sql

--- 2. 使用pymysql自带的检验工具 cursor.execute(sql, (user, pwd))，将用户名、密码传入执行sql的参数中。

MySQL的索引

在介绍 MySQL索引之前，我们先创建一个表，表结构如下

mysql> desc pymysql_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| user  | varchar(32) | NO   |     |         |                |
| email | varchar(32) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

来对这个表插入500W条数据，方便下面的讲解。

import pymysql

conn = pymysql.connect(
    host="192.168.32.130",
    user="root",
    password="123",
    database="work",
    charset="utf8"
)

cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

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

data = []
for i in range(5000000):
    info = ("qinyj" + str(i),"qinyj" + str(i) + "@qq.com")
    data.append(info)
# print(data)

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


select * from pymysql_test;
...
5000000 rows in set (20.45 sec)

为什么使用索引

使用索引就是为了提高查询的效率，类似于新华字典中的目录

索引的本质就是一个特殊的文件，记录着索引信息

索引的底层原理：B + 树

索引的种类

  • 主键索引
  • 唯一索引
  • 普通索引
mysql> desc test;
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| name  | varchar(32) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)

主键索引

加速查找、不能重复、不能为空

创建主键索引：primary key

1、create table test(id int auto_increment primary key)charset=utf8;
2、alter table test modify id int auto_increment primary key;
3、alter table test add primary key(id);

删除主键索引：
注意：如果主键加上了自增的约束就不能被删除
alter table test drop primary key;

唯一索引

加速查找、不能重复 unique(xxx)

唯一联合索引：unique(xxx,xxx)

创建唯一索引：unique
1、create table test(id int auto_increment primary key,name varchar(32) not null default '',unique u_name(name))charset=utf8;
2、create unique index u_name on test(name);
3、alter table test add unique index u_name (name);

创建联合索引：
1、create table test(id int auto_increment primary key,name varchar(32) not null default '',address varchar(32) not null default '',age int not null default 0, unique u_name_age(name,age))charset=utf8;
2、alter table pymysql_test add unique index pt_name (user,email);


删除唯一索引：
alter table test drop index u_name;

普通索引

加速查找 index(xxx)

普通联合索引：index(xxx,xxx)

创建普通索引：index
1、create table test(id int auto_increment primary key,name varchar(32) not null default '',index ix_name(name))charset=utf8;
2、create index ix_name on test(name);
3、alter table test add index ix_name (name);

创建联合普通索引：
create table test(id int auto_increment primary key,name varchar(32) not null default '',age int not null default 0,index ix_name_age (name,age))charset=utf8;

删除普通索引
alter table test drop index ix_name;

索引优缺点

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

  • 索引加快了查询速度
  • 加了索引之后，会占用大量的磁盘空间

所以索引不是加的越多越好

不会命中索引的情况


mysql> desc pymysql_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| user  | varchar(32) | NO   |     |         |                |
| email | varchar(32) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

会降低sql的查询效率的情况：

  1. 在sql语句中进行四则运算

    select * from pymysql_test where id*2=5127;
    Empty set (6.55 sec)

  2. 使用聚合函数

select count(id) from pymysql_test;

1 row in set (4.06 sec)

  1. 查询类型不一致的时候，如果列是字符串类型，条件也必须用引号引起来表示字符串

    select * from pymysql_test where email=999;

    Empty set, 65535 warnings (9.20 sec)

  2. 排序 order by

    排序条件为索引，则查询字段必须也是索引字段，否则无法命中

    如果查询的字段与排序字段的不是主键的字段，那么速度依然很慢

    select email from pymysql_test order by email desc;

    如果查询的字段与排序的字段是主键的字段，那么速度还是很快

    select id from pymysql_test order by id desc;

    5000004 rows in set (9.48 sec)

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

    select count(*) from pymysql_test;

    select count(1) from pymysql_test;

    select count(id) from pymysql_test;

    1 row in set (4.29 sec)

  4. 组合索引最左前缀

    什么时候会创建联合索引？

    根据公司业务场景，在最常用的几列上添加索引

    mysql> desc pymysql_test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type        | Null | Key | Default | Extra          |
+-------+-------------+------+-----+---------+----------------+
| id    | int(11)     | NO   | PRI | NULL    | auto_increment |
| user  | varchar(32) | NO   |     |         |                |
| email | varchar(32) | NO   |     |         |                |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)


创建联合索引
alter table pymysql_test add unique index pt_name (user,email);
Query OK, 0 rows affected (1 min 4.77 sec)
Records: 0  Duplicates: 0  Warnings: 0

    创建完联合索引之后呢，我们使用组合查询效率会非常高，

    
联合查询：效率非常高
select * from pymysql_test where user="qinyj1324" and email="qinyj1324@qq.com";
1 row in set (0.00 sec)


查询最左前前缀：效率也是非常高
select * from pymysql_test where user="qinyj122432" ;
1 row in set (0.00 sec)

查询后面的：效率就低了
select * from pymysql_test where email="qinyj1324@qq.com"; 
1 row in set (5.96 sec)

    例子：

    组合索引：index (a,b,c,d)

    where a=2 and b=2 and c=2 and d=5 --> 命中索引

    where a=2 and c=2 and d=5 -->部分命中索引

    where d =5 -->没有命中

explain

explain关键字可以模拟优化器执行sql语句，从而知道MySQL是如何处理sql语句的，分析查询语句或是结构的性能瓶颈

我们只需要在查询sql语句前面加上explain就行了

explain select * from pymysql_test where email="qinyj1324@qq.com"\G;;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: pymysql_test
         type: index
possible_keys: NULL
          key: pt_name
      key_len: 196
          ref: NULL
         rows: 4988562
        Extra: Using where; Using index
1 row in set (0.00 sec)

参数解释：

  • type：索引指向，如果是all的话那就是使用索引
  • possible_keys：可能用到的索引
  • key：确实用到的索引
  • key_len：索引长度
  • rows：扫描的行数
  • Extra：描述信息，使用到了索引

索引覆盖

select id from pymysql_test where id=20000;

MySQL慢查询日志

查看慢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                                |
| slow_query_log_file       | /data/app/mysql/localhost-slow.log |
+---------------------------+------------------------------------+


show variables like '%long%';
+--------------------------------------------------------+-----------+
| Variable_name                                          | Value     |
+--------------------------------------------------------+-----------+
| long_query_time                                        | 10.000000 |

参数解释：

  • slow_query_log：慢查询日志开关，默认关闭
  • slow_query_log_file：慢查询存放位置
  • long_query_time：慢查询时间限制

配置慢查询的变量：

set global slow_query_log = on;
set global slow_query_log_file = '/data/app/mysql/myslow.log';
set global long_query_time = 1;

配置完毕之后退出重连一下就会有生成的慢查询日志了

ll /data/app/mysql/myslow.log
-rw-rw---- 1 mysql mysql 180 Oct 31 05:37 /data/app/mysql/myslow.log

posted @ 2019-10-31 22:01  GeminiMp  阅读(372)  评论(0)  编辑  收藏  举报