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

友儿

September 6, 2022

754 views

No comments

4399 words

Python

用python的pymysql包连接数据库

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()
    data_dict = []
    for field in mysql_cursor.description:
        data_dict.append(field[0])
    mysql_cursor.close()
    print(query_set,data_dict)

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

(('店铺_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()

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