用python连接数据库、索引、优化、事务

友儿

发布于 2022-09-28 13:25:01

3550

发布于 2022-09-28 13:25:01

文章被收录于专栏：友儿

用python连接数据库

pymysql

pip install pymysql

#如果让你装vs环境, 执行以下命令升级pip即可
python -m pip install --upgrade pip

连接数据库

数据库设置

MYSQL_CONF = {
    "host": "127.0.0.1",
    "user": "root",
    "password": "qwe369",
    "db": "test"
}

连接

# 连接数据库
mysql_con = pymysql.connect(**MYSQL_CONF)

# 简单理解真正执行语句的线程
mysql_cursor = mysql_con.cursor()

执行SQL语句

单条执行

执行时间15.31s

@clock_it_deco
def insert_one():
    for i in range(10**4):
        store_name = f"店铺_{i}"
        amount = format(random.uniform(10**3, 10**6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")

        SQL = f"""INSERT INTO store_perf(store_name, amount, department, sta_date)
                  VALUES ('{store_name}', {amount}, '{department}', '{sta_date}')"""
        print(SQL)
        mysql_cursor.execute(SQL)

        # 显示执行commit, 避免cursor执行, 但是数据库未收到的执行指令的情况
        mysql_con.commit()

多条执行

如果其中有一条记录报错, 那么整个values插入都会失败

@clock_it_deco
def insert_many():
    values = []
    for i in range(10**4):
        store_name = f"店铺_{i}"
        amount = format(random.uniform(10**3, 10**6), '.2f')
        department = f"事业部_{random.randint(1, 10)}"
        sta_date = time.strftime("%Y-%m-%d")
        values.append((store_name, amount, department, sta_date))

    SQL = """INSERT INTO store_perf(store_name, amount, department, sta_date)
             VALUES (%s, %s, %s, %s)"""
    print(values)
    mysql_cursor.executemany(SQL, values)
    mysql_con.commit()

获取返回值

def get_shops():
    SQL = "select store_name, amount, sta_date from store_perf where department='事业部_1' LIMIT 2"
    mysql_cursor.execute(SQL)
    # 获取返回值
    query_set = mysql_cursor.fetchall()
    print(query_set)

返回的是一个元组, 元组中表示记录的也是一个元组

(('店铺_1', Decimal('823471.56'), '2021-02-06'), ('店铺_6', Decimal('726927.02'), '2021-02-06'))

索引和优化

创建索引

未建索引: 0.287s 建了索引后: 0.016s

设置索引的字段不可以超过191个字符长度, 也就是767个bytes

CREATE INDEX 索引名 ON 表名(字段1, 字段2...)

CREATE INDEX store_name_index ON store_perf(store_name)

创建联合索引

CREATE INDEX store_name_sta_date_department_index ON store_perf(store_name, sta_date, department)

查看索引

SHOW INDEX FROM store_perf

删除索引

ALTER TABLE store_perf DROP INDEX  store_name_index

查看当前查询语句有没有命中索引

EXPLAIN语句查看当前语句执行性能
如果key有值, 说明命中了索引, 且key值为索引名

EXPLAIN SELECT * from store_perf WHERE store_name = "店铺_224123"

单个字段可以命中联合索引吗?

联合索引涉及到一个叫左缀查询的规则

如果想命中索引, 查询语句中涉及到字段必须是联合索引创建时从左到右顺序

原理在a_b_c_index这样一个联合索引当中, 实质执行中是先查出a的结果集, 然后再查b或c的结果集

索引的实现原理

B+树, 一种特殊的链表, 用来实现二分查找.

如何优化mysql

合理地建立索引

频繁作为查询条件的字段应该建立索引
唯一性太差的字段不适合单独建立索引
更新非常频繁的字段不适合建立索引
避免不经过索引的操作
- not in, !=等反向逻辑
- BETWEEN范围查找
- or逻辑两边都必须命中索引才会走索引
- 联合索引, 不按左缀查询规则

加缓存

数据库缓存

show VARIABLES LIKE '%query_cache%'

用redis做缓存

请求 -> redis -> 未命中 -> mysql -> 返回

换固态硬盘

事务

事务的提交, 回滚

START TRANSACTION;
INSERT INTO store_perf(store_name, amount, department, sta_date) VALUES ('店铺_1764', 753294.41, '事业部_8', '2021-02-05');
ROLLBACK;
COMMIT;

业务中使用事务

将class_2中的同学转移到class_1, 如果SQL_2报错, 会导致class_2中的同学丢失.

def transaction():
    try:
        SQL = "DELETE FROM class_2 where name='name_13'"
        mysql_cursor.execute(SQL)
        SQL_2 = "INSERT INTO class_1 VALUES(name)"
        mysql_cursor.execute(SQL_2)

    except Exception as e:
        print("raise Exceptions", e.args[0])
        print("rollback")
        mysql_con.rollback()
    finally:
        mysql_con.commit()

本文参与腾讯云自媒体同步曝光计划，分享自作者个人站点/博客。

如有侵权请联系 cloudcommunity@tencent.com 删除

缓存