表操作
一般情况下，我们针对只要通过索引列去搜搜都可以 命中 索引（通过索引结构加速查找）。
但是，还是会有一些特殊的情况，让我们无法命中索引（即使创建了索引），这也是需要大家在开发中要注意的。
类型不一致
select * from big where name = 123; -- 未命中 select * from big where email = 123; -- 未命中 特殊的主键： select * from big where id = "123"; -- 命中
使用不等于
select * from big where name != "xx"; -- 未命中 select * from big where email != "wupeiqi@live.com"; -- 未命中 特殊的主键： select * from big where id != 123; -- 命中
or，当or条件中有未建立索引的列才失效。
select * from big where id = 123 or password="xx"; -- 未命中 select * from big where name = "wupeiqi" or password="xx"; -- 未命中 特别的： select * from big where id = 10 or password="xx" and name="xx"; -- 命中
排序，当根据索引排序时候，选择的映射如果不是索引，则不走索引。
select * from big order by name asc; -- 未命中 select * from big order by name desc; -- 未命中 特别的主键： select * from big order by id desc; -- 命中
like，模糊匹配时。
select * from big where name like "%u-12-19999"; -- 未命中 select * from big where name like "_u-12-19999"; -- 未命中 select * from big where name like "wu-%-10"; -- 未命中 特别的： select * from big where name like "wu-1111-%"; -- 命中 select * from big where name like "wuw-%"; -- 命中
使用函数
select * from big where reverse(name) = "wupeiqi"; -- 未命中 特别的： select * from big where name = reverse("wupeiqi"); -- 命中
最左前缀，如果是联合索引，要遵循最左前缀原则。
如果联合索引为：(name,password) name and password -- 命中 name -- 命中 password -- 未命中 name or password -- 未命中
事务
innodb引擎中支持事务，myisam不支持。
例如：xx给oo 转账 100，那就会涉及2个步骤。
xx账户 减100
oo账户 加 100
这两个步骤必须同时完成才算完成，并且如果第一个完成、第二步失败，还是回滚到初始状态。
事务，就是来解决这种情况的。 大白话：要成功都成功；要失败都失败。
事务的具有四大特性（ACID）：
原子性（Atomicity）
原子性是指事务包含的所有操作不可分割，要么全部成功，要么全部失败回滚。
一致性（Consistency）
执行的前后数据的完整性保持一致。
隔离性（Isolation）
一个事务执行的过程中,不应该受到其他事务的干扰。
持久性（Durability）
事务一旦结束,数据就持久到数据库
begin; update users set amount=amount-2 where id=1; update users set amount=amount+2 where id=2; commit;
import pymysql conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor() # 开启事务 conn.begin() try: cursor.execute("update users set amount=1 where id=1") int('asdf') cursor.execute("update tran set amount=2 where id=2") except Exception as e: # 回滚 print("回滚") conn.rollback() else: # 提交 print("提交") conn.commit() cursor.close() conn.close()
锁
在用MySQL时，不知你是否会疑问：同时有很多做更新、插入、删除动作，MySQL如何保证数据不出错呢？
MySQL中自带了锁的功能，可以帮助我们实现开发过程中遇到的同时处理数据的情况。对于数据库中的锁，从锁的范围来讲有：
表级锁，即A操作表时，其他人对整个表都不能操作，等待A操作完之后，才能继续。
行级锁，即A操作表时，其他人对指定的行数据不能操作，其他行可以操作，等待A操作完之后，才能继续。
MYISAM支持表锁，不支持行锁；
InnoDB引擎支持行锁和表锁。
即：在MYISAM下如果要加锁，无论怎么加都会是表锁。
在InnoDB引擎支持下如果是基于索引查询的数据则是行级锁，否则就是表锁。
在innodb引擎中，update、insert、delete的行为内部都会先申请锁（排它锁），申请到之后才执行相关操作，最后再释放锁。
所以，当多个人同时像数据库执行：insert、update、delete等操作时，内部加锁后会排队逐一执行。
而select则默认不会申请锁。
select * from xxx;
for update，排它锁，加锁之后，其他不可以读写。
begin; select * from L1 where name="武沛齐" for update; -- name列不是索引（表锁） commit; begin; -- 或者 start transaction; select * from L1 where id=1 for update; -- id列是索引（行锁） commit;
lock in share mode ，共享锁，加锁之后，其他可读但不可写。
begin; select * from L1 where name="武沛齐" lock in share mode; -- 假设name列不是索引（表锁） commit; begin; -- 或者 start transaction; select * from L1 where id=1 lock in share mode; -- id列是索引（行锁） commit;
排它锁
排它锁（ for update），加锁之后，其他事务不可以读写。
应用场景：总共100件商品，每次购买一件需要让商品个数减1 。
A: 访问页面查看商品剩余 100
B: 访问页面查看商品剩余 100
此时 A、B 同时下单，那么他们同时执行SQL：
update goods set count=count-1 where id=3
由于Innodb引擎内部会加锁，所以他们两个即使同一时刻执行，内部也会排序逐步执行。
但是，当商品剩余 1个时，就需要注意了。
A: 访问页面查看商品剩余 1
B: 访问页面查看商品剩余 1
此时 A、B 同时下单，那么他们同时执行SQL：
update goods set count=count-1 where id=3
这样剩余数量就会出现 -1，很显然这是不正确的，所以应该怎么办呢？
-- 这种情况下，可以利用 排它锁，在更新之前先查询剩余数量，只有数量 >0 才可以购买，所以，下单时应该执行： begin; -- start transaction; select count from goods where id=3 for update; -- 获取个数进行判断 if 个数>0: update goods set count=count-1 where id=3; else: -- 已售罄 commit;
import pymysql import threading def task(): conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb') cursor = conn.cursor(pymysql.cursors.DictCursor) # cursor = conn.cursor() # 开启事务 conn.begin() cursor.execute("select id,age from tran where id=2 for update") # fetchall ( {"id":1,"age":10},{"id":2,"age":10}, ) ((1,10),(2,10)) # {"id":1,"age":10} (1,10) result = cursor.fetchone() current_age = result['age'] if current_age > 0: cursor.execute("update tran set age=age-1 where id=2") else: print("已售罄") conn.commit() cursor.close() conn.close() def run(): for i in range(5): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
数据库连接池
import threading import pymysql from dbutils.pooled_db import PooledDB MYSQL_DB_POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=3, # 链接池中最多闲置的链接，0和None不限制 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, # ping MySQL服务端，检查是否服务可用。 # 如：0 = None = never, 1 = default = whenever it is requested, # 2 = when a cursor is created, 4 = when a query is executed, 7 = always host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) def task(): # 去连接池获取一个连接 conn = MYSQL_DB_POOL.connection() cursor = conn.cursor(pymysql.cursors.DictCursor) cursor.execute('select sleep(2)') result = cursor.fetchall() print(result) cursor.close() # 将连接交换给连接池 conn.close() def run(): for i in range(10): t = threading.Thread(target=task) t.start() if __name__ == '__main__': run()
推荐使用上下文管理方式：
# db_context.py import threading import pymysql from dbutils.pooled_db import PooledDB POOL = PooledDB( creator=pymysql, # 使用链接数据库的模块 maxconnections=5, # 连接池允许的最大连接数，0和None表示不限制连接数 mincached=2, # 初始化时，链接池中至少创建的空闲的链接，0表示不创建 maxcached=3, # 链接池中最多闲置的链接，0和None不限制 blocking=True, # 连接池中如果没有可用连接后，是否阻塞等待。True，等待；False，不等待然后报错 setsession=[], # 开始会话前执行的命令列表。如：["set datestyle to ...", "set time zone ..."] ping=0, host='127.0.0.1', port=3306, user='root', password='root123', database='userdb', charset='utf8' ) class Connect(object): def __init__(self): self.conn = conn = POOL.connection() self.cursor = conn.cursor(pymysql.cursors.DictCursor) def __enter__(self): return self def __exit__(self, exc_type, exc_val, exc_tb): self.cursor.close() self.conn.close() def exec(self, sql, **kwargs): self.cursor.execute(sql, kwargs) self.conn.commit() def fetch_one(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchone() return result def fetch_all(self, sql, **kwargs): self.cursor.execute(sql, kwargs) result = self.cursor.fetchall() return result
from db_context import Connect with Connect() as obj: # print(obj.conn) # print(obj.cursor) ret = obj.fetch_one("select * from d1") print(ret) ret = obj.fetch_one("select * from d1 where id=%(id)s", id=3) print(ret)
