用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的结果集, 然后再查bc的结果集

  • 索引的实现原理

    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()
Last modification:February 20, 2024
如果觉得我的文章对你有用,请随意赞赏