python连接mysql和索引优化

用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()
  

课后作业

• 练习pymysql的使用, 报错单语句执行, 多语句执行, 查询语句, 事务的使用
• 练习索引的创建
• 将索引的内容背诵