连接mysql和索引优化

python连接mysql和索引优化

用python连接数据库

• pymysql

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

• 连接数据库

  • 数据库设置

    MYSQL_CONF = {
        "host": "127.0.0.1",
        "user": "root",
        "password": "qwe369",
        "db": "test"
    }
    1
    2
    3
    4
    5
    6

  • 连接

    # 连接数据库
    mysql_con = pymysql.connect(**MYSQL_CONF)
    
    # 简单理解真正执行语句的线程
    mysql_cursor = mysql_con.cursor()
    1
    2
    3
    4
    5

• 执行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()
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15

  • 多条执行

    • 如果其中有一条记录报错, 那么整个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()
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15

  • 获取返回值

    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
    2
    3
    4
    5
    6

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

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

索引和优化

• 创建索引

  未建索引: 0.287s

  建了索引后: 0.016s

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

  CREATE INDEX 索引名 ON 表名(字段1, 字段2...)
  
  CREATE INDEX store_name_index ON store_perf(store_name)
  1
  2
  3

• 创建联合索引

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

• 查看索引

  SHOW INDEX FROM store_perf
  1

• 删除索引

  ALTER TABLE store_perf DROP INDEX  store_name_index
  1

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

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

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

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

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

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

  • 原理

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

• 索引的实现原理

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

如何优化mysql

• 合理地建立索引

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

• 加缓存

  • 数据库缓存

    show VARIABLES LIKE '%query_cache%'
    1

  • 用redis做缓存

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

• 换固态硬盘

事务

• 事务的提交, 回滚

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

• 业务中使用事务

  将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()
  1
  2
  3
  4
  5
  6
  7
  8
  9
  10
  11
  12
  13

课后作业

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