Python连接mysql，sql优化

每日一记

Mysql优化：
1，避免全表扫描 ----索引
2，查询条件列不能有null
3，查询条件表达式不能有 != <>
4，查询条件表达式不能有or (除非左右列都有索引) 用union all 替代
5，in和not in要慎用，会导致全表扫描，对于连续的数据能用 between and 就不要in
替代： exists()
6，like 也要慎用 （%column%）
7，避免在where子句中对字段进行函数操作
8，where子句中 “=”号 左侧不能有函数、算数运算或者其他表达式运算
9，如果只更改1、2个字段，不要update全部字段
10，多张大数据表，要先分页在join
11，不带任何条件的count会引起全表扫描
13，尽量使用数字型字段
14，尽可能的使用varchar/nvarchar 代替char/nchar
15，禁止使用*
16，尽量避免使用游标，因为游标的效率较差
17，尽量避免向客户端返回大数据量

Mysql事务：
一，事务的基本要素（ACTD）
1，原子性（Atomicity）：事务开始后所有操作，要么全部做完，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没发生一样。也就是说事务是一个不可分割的整体，就像化学中学过的原子，是物质构成的基本单位。
2、一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏 。比如 A 向 B 转账，不可能 A 扣了钱，B 却没收到。
3、隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。比如 A 正在从一张银行卡中取钱，在 A 取钱的过程结束前，B 不能向这张卡转账。
4、持久性（Durability）：事务完成后，事务对数据库的所有更新将被保存到数据库，不能回滚。

二、事务的并发问题：
1，脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。
2，不可重复读：事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3，幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为 ABCDE 等级，但是系统管理员 B 就在这个时候插入了一条具体分数的记录，当系统管理员 A 改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

三、MySQL 事务隔离级别：
事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是 （一般用这个）
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

存储过程简介：
SQL 语句需要先编译然后执行，而存储过程（Stored Procedure）是一组为了
完成特定功能的 SQL 语句集，经编译后存储在数据库中，用户通过指定存储过
程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
存储过程是可编程的函数，在数据库中创建并保存，可以由 SQL 语句和控制结
构组成。当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能
时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象
方法的模拟，它允许控制数据的访问方式。
***存储过程的优点：
(1).增强 SQL 语言的功能和灵活性：
存储过程可以用控制语句编写，有很强的
灵活性，可以完成复杂的判断和较复杂的运算。
(2).标准组件式编程：
存储过程被创建后，可以在程序中被多次调用，而不必重
新编写该存储过程的 SQL 语句。而且数据库专业人员可以随时对存储过程进行
修改，对应用程序源代码毫无影响。
(3).较快的执行速度：
如果某一操作包含大量的 Transaction-SQL 代码或分别
被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编
译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最
终被存储在系统表中的执行计划。而批处理的 Transaction-SQL 语句在每次运
行时都要进行编译和优化，速度相对要慢一些。
(4).减少网络流量：
针对同一个数据库对象的操作（如查询、修改），如果这一
操作所涉及的 Transaction-SQL 语句被组织进存储过程，那么当在客户计算机
上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并
降低了网络负载。
(5).作为一种安全机制来充分利用：
通过对执行某一存储过程的权限进行限制，
能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保
证了数据的安全。

python连接数据库：
安装pymysql第三方库
用cmd命令行打印：pip install pymysql 开始安装

下面代码是向mysql数据库中emp表插入两条数据：

import pymysql
#创建连接
conn = pymysql.connect(host="127.0.0.1", port=3306, user="root", password="root", db="syx190310", charset="utf8")
#创建游标
cursor = conn.cursor()
#执行sql，并返回影响行数
effect_row = cursor.execute("select * from emp")
#查询所有数据
#print(cursor.fetchall())
#查询前几条数据
#print(cursor.fetchmany(3))
#查询一条数据
#print(cursor.fetchone())
try:
    #effect_row = cursor.execute("insert into emp(empno,ename,sal) values(6666,'zs',222)")
    effect_row = cursor.executemany("insert into emp(empno,ename,sal) values(%s,%s,%s)" ,[(6,'ll',8),(7,'kk',5)])
    print(effect_row)
    conn.commit()

except(Exception):
    print("插入失败")
finally:
    cursor.close()
    conn.close()