4.mysql-進階

1.事務

將多個操作步驟變成一個事務,任何一個步驟失敗,則回滾到事務的所有步驟之前狀態,大白話:要成功都成功;要失敗都失敗。

如轉賬操作,A扣錢。B收錢,必須兩個步驟都成功,才認為轉賬成功

innodb引擎中支持事務,myisam不支持。

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `name` varchar(32) DEFAULT NULL,
  `amount` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

事務的具有四大特性(ACID):

  • 原子性(Atomicity)

    原子性是指事務包含的所有操作不可分割,要麼全部成功,要麼全部失敗回滾。
  • 一致性(Consistency)

    執行的前後數據的完整性保持一致。
  • 隔離性(Isolation)

    一個事務執行的過程中,不應該受到其他事務的干擾。
  • 持久性(Durability)

    事務一旦結束,數據就持久到數據庫

python代碼操作

import pymysql

conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
cursor = conn.cursor()

# 開啟事務
conn.begin()

try:
    cursor.execute("update users set amount=1 where id=1")cursor.execute("update tran set amount=2 where id=2")
except Exception as e:
    # 回滾
    conn.rollback()
else:
    # 提交
    print("提交")
    conn.commit()

cursor.close()
conn.close()

 

2.鎖

在用MySQL時,不知你是否會疑問:同時有很多做更新、插入、刪除動作,MySQL如何保證數據不出錯呢?

MySQL中自帶了鎖的功能,可以幫助我們實現開發過程中遇到的同時處理數據的情況。對於數據庫中的鎖,從鎖的範圍來講有:

– 表級鎖,即A操作表時,其他人對整個表都不能操作,等待A操作完之後,才能繼續。
– 行級鎖,即A操作表時,其他人對指定的行數據不能操作,其他行可以操作,等待A操作完之後,才能繼續。

MYISAM支持表鎖,不支持行鎖;
InnoDB引擎支持行鎖和表鎖。

即:在MYISAM下如果要加鎖,無論怎麼加都會是表鎖。
    在InnoDB引擎支持下如果是基於索引查詢的數據則是行級鎖,否則就是表鎖。

所以,一般情況下我們會選擇使用innodb引擎,並且在 搜索 時也會使用索引(命中索引)。

接下來的操作就基於innodb引擎來操作:

CREATE TABLE `L1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `count` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

在innodb引擎中,update、insert、delete的行為內部都會先申請鎖(排它鎖),申請到之後才執行相關操作,最後再釋放鎖。

所以,當多個人同時像數據庫執行:insertupdate、delete等操作時,內部加鎖後會排隊逐一執行。

而select則默認不會申請鎖。

2.1 排它鎖

排它鎖( for update),加鎖之後,其他事務不可以讀寫。

import pymysql
import threading


def task():
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', charset="utf8", db='userdb')
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    # cursor = conn.cursor()
    
    # 開啟事務
    conn.begin()

    cursor.execute("select id,age from tran where id=2 for update")
    # fetchall      ( {"id":1,"age":10},{"id":2,"age":10}, )   ((1,10),(2,10))
    # {"id":1,"age":10}   (1,10)
    result = cursor.fetchone()
    current_age = result['age']
    
    if current_age > 0:
        cursor.execute("update tran set age=age-1 where id=2")
    else:
        print("已售罄")

    conn.commit()

    cursor.close()
    conn.close()


def run():
    for i in range(5):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

 

2.2 共享鎖

共享鎖( lock in share mode),可以讀,但不允許寫。

加鎖之後,後續其他事物可以可以進行讀,但不允許寫(update、delete、insert)

SELECT * FROM parent WHERE NAME = 'Jones' LOCK IN SHARE MODE;

 

3.數據庫連接池

3.1 連接池創建

 

 

在操作數據庫時需要使用數據庫連接池。數據庫池可以避免頻繁的連接和斷開數據庫帶來的損耗

pip install pymysql
pip install dbutils
import threading
import pymysql
from dbutils.pooled_db import PooledDB

MYSQL_DB_POOL = PooledDB(
    creator=pymysql,  # 使用鏈接數據庫的模塊
    maxconnections=5,  # 連接池允許的最大連接數,0和None表示不限制連接數
    mincached=2,  # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建
    maxcached=3,  # 鏈接池中最多閑置的鏈接,0和None不限制
    blocking=True,  # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯
    setsession=[],  # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."]
    ping=0,
    # ping MySQL服務端,檢查是否服務可用。
    # 如:0 = None = never, 1 = default = whenever it is requested, 
    # 2 = when a cursor is created, 4 = when a query is executed, 7 = always
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


def task():
    # 去連接池獲取一個連接
    conn = MYSQL_DB_POOL.connection()
    cursor = conn.cursor(pymysql.cursors.DictCursor)
    
    cursor.execute('select sleep(2)')
    result = cursor.fetchall()
    print(result)

    cursor.close()
    # 將連接交換給連接池
    conn.close()

def run():
    for i in range(10):
        t = threading.Thread(target=task)
        t.start()


if __name__ == '__main__':
    run()

 

3.2 SQL工具類的使用

3.2.1 基於模塊創建單例模式

# db.py
import pymysql
from dbutils.pooled_db import PooledDB


class DBHelper(object):

    def __init__(self):
        # TODO 此處配置,可以去配置文件中讀取。
        self.pool = PooledDB(
            creator=pymysql,  # 使用鏈接數據庫的模塊
            maxconnections=5,  # 連接池允許的最大連接數,0和None表示不限制連接數
            mincached=2,  # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建
            maxcached=3,  # 鏈接池中最多閑置的鏈接,0和None不限制
            blocking=True,  # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯
            setsession=[],  # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."]
            ping=0,
            # ping MySQL服務端,檢查是否服務可用。# 如:0 = None = never, 1 = default = whenever it is requested, 2 = when a cursor is created, 4 = when a query is executed, 7 = always
            host='127.0.0.1',
            port=3306,
            user='root',
            password='root123',
            database='userdb',
            charset='utf8'
        )

    def get_conn_cursor(self):
        conn = self.pool.connection()
        cursor = conn.cursor(pymysql.cursors.DictCursor)
        return conn, cursor

    def close_conn_cursor(self, *args):
        for item in args:
            item.close()

    def exec(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        conn.commit()

        self.close_conn_cursor(conn, cursor)

    def fetch_one(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchone()

        self.close_conn_cursor(conn, cursor)
        return result

    def fetch_all(self, sql, **kwargs):
        conn, cursor = self.get_conn_cursor()

        cursor.execute(sql, kwargs)
        result = cursor.fetchall()

        self.close_conn_cursor(conn, cursor)

        return result


db = DBHelper()

 

3.2.2 基於上下文使用數據庫池

如果你想要讓他也支持 with 上下文管理。

# db_context.py
import threading
import pymysql
from dbutils.pooled_db import PooledDB

POOL = PooledDB(
    creator=pymysql,  # 使用鏈接數據庫的模塊
    maxconnections=5,  # 連接池允許的最大連接數,0和None表示不限制連接數
    mincached=2,  # 初始化時,鏈接池中至少創建的空閑的鏈接,0表示不創建
    maxcached=3,  # 鏈接池中最多閑置的鏈接,0和None不限制
    blocking=True,  # 連接池中如果沒有可用連接後,是否阻塞等待。True,等待;False,不等待然後報錯
    setsession=[],  # 開始會話前執行的命令列表。如:["set datestyle to ...", "set time zone ..."]
    ping=0,
    host='127.0.0.1',
    port=3306,
    user='root',
    password='root123',
    database='userdb',
    charset='utf8'
)


class Connect(object):
    def __init__(self):
        self.conn = conn = POOL.connection()
        self.cursor = conn.cursor(pymysql.cursors.DictCursor)

    def __enter__(self):
        return self

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.cursor.close()
        self.conn.close()

    def exec(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        self.conn.commit()

    def fetch_one(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchone()
        return result

    def fetch_all(self, sql, **kwargs):
        self.cursor.execute(sql, kwargs)
        result = self.cursor.fetchall()
        return result

 

4.索引

在數據庫中索引最核心的功能就是:**加速查找**

4.1 索引的原理

索引的底層是基於B+Tree的數據結構存儲的

據庫的索引是基於上述B+Tree的數據結構實現,但在創建數據庫表時,如果指定不同的引擎,底層使用的B+Tree結構的原理有些不同。

  • myisam引擎,非聚簇索引(數據 和 索引結構 分開存儲)

  • innodb引擎,聚簇索引(數據 和 主鍵索引結構存儲在一起)

在企業開發中一般都會使用 innodb 引擎(內部支持事務、行級鎖、外鍵等特點),在MySQL5.5版本之後默認引擎也是innodb

4.1.1 非聚簇索引(mysiam引擎)

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=myisam default charset=utf8;

4.1.2 聚簇索引(innodb引擎)

create table 表名(
    id int not null auto_increment primary key, 
    name varchar(32) not null,
    age int
)engine=innodb default charset=utf8;

 

 

4.2 常見的索引

**建議不要隨意添加索引,因為索引也是需要維護的,太多的話反而會降低系統的性能。**

開發過程中常見的索引類型有:

  • 主鍵索引:加速查找、不能為空、不能重複。
    create table 表名(
        id int not null auto_increment primary key,   -- 主鍵
        name varchar(32) not null
    );
  • 唯一索引:加速查找、不能重複。
    create table 表名(
        id int not null auto_increment primary key,
        name varchar(32) not null,
        email varchar(64) not null,
        unique ix_name (name),  -- 唯一索引
        unique ix_email (email),
    );
  • 普通索引:加速查找。
     create table 表名(    id int not null auto_increment primary key,    name varchar(32) not null,    email varchar(64) not null,    index ix_email (email),  -- 普通索引    index ix_name (name),);

     

  • 組合索引
    ALTER TABLE `table` ADD INDEX name_city_age (name,city,age); 

     

4.3 索引失效

會有一些特殊的情況,讓我們無法命中索引(即使創建了索引),這也是需要大家在開發中要注意的。

  • 類型不一樣
    select * from info where name = 123;    -- 未命中
    特殊的主鍵:
    select * from info where id = "123";    -- 命中
  • 使用不等於
    select * from info where name != "kunmzhao";                -- 未命中
    特殊的主鍵:
    select * from big where id != 123;    -- 命中
  • or的使用
    當or條件中有未建立索引的列才失效
    select * from big where id = 123 or password="xx";            -- 未命中
  • like的使用
    select * from big where name like "%u-12-19999";    -- 未命中
    select * from big where name like "wu-%-10";        -- 未命中
    
    特別的:
        select * from big where name like "wu-1111-%";    -- 命中
        select * from big where name like "wuw-%";        -- 命中
  • 排序的使用
    當根據索引排序時候,選擇的映射如果不是索引,則不走索引
    select * from big order by name asc;     -- 未命中
    select * from big order by name desc;    -- 未命中
    
    特別的主鍵:
        select * from big order by id desc;  -- 命中

     

  • 最左前綴原則, 如果是聯合索引,要遵循最左前綴原則。
    如果聯合索引為:(name,password)
        name and password       -- 命中
        name                     -- 命中
        password                -- 未命中
        name or password           -- 未命中

     

5.函數

MySQL中提供了很多函數,為我們的SQL操作提供便利,例如:

CHAR_LENGTH(str)
    返回值為字符串str 的長度,長度的單位為字符。一個多位元組字符算作一個單字符。
    對於一個包含五個二位元組字符集, LENGTH()返回值為 10, 而CHAR_LENGTH()的返回值為5。

CONCAT(str1,str2,...)
    字符串拼接
    如有任何一個參數為NULL ,則返回值為 NULL。
CONCAT_WS(separator,str1,str2,...)
    字符串拼接(自定義連接符)
    CONCAT_WS()不會忽略任何空字符串。 (然而會忽略所有的 NULL)。

CONV(N,from_base,to_base)
    進制轉換
    例如:
        SELECT CONV('a',16,2); 表示將 a 由16進制轉換為2進制字符串表示

FORMAT(X,D)
    將數字X 的格式寫為'#,###,###.##',以四捨五入的方式保留小數點後 D 位, 並將結果以字符串的形式返回。若  D 為 0, 則返回結果不帶有小數點,或不含小數部分。
    例如:
        SELECT FORMAT(12332.1,4); 結果為: '12,332.1000'
INSERT(str,pos,len,newstr)
    在str的指定位置插入字符串
        pos:要替換位置其實位置
        len:替換的長度
        newstr:新字符串
    特別的:
        如果pos超過原字符串長度,則返回原字符串
        如果len超過原字符串長度,則由新字符串完全替換
INSTR(str,substr)
    返回字符串 str 中子字符串的第一個出現位置。

LEFT(str,len)
    返回字符串str 從開始的len位置的子序列字符。

LOWER(str)
    變小寫

UPPER(str)
    變大寫

LTRIM(str)
    返回字符串 str ,其引導空格字符被刪除。
RTRIM(str)
    返回字符串 str ,結尾空格字符被刪去。
SUBSTRING(str,pos,len)
    獲取字符串子序列

LOCATE(substr,str,pos)
    獲取子序列索引位置

REPEAT(str,count)
    返回一個由重複的字符串str 組成的字符串,字符串str的數目等於count 。
    若 count <= 0,則返回一個空字符串。
    若str 或 countNULL,則返回 NULLREPLACE(str,from_str,to_str)
    返回字符串str 以及所有被字符串to_str替代的字符串from_str 。
REVERSE(str)
    返回字符串 str ,順序和字符順序相反。
RIGHT(str,len)
    從字符串str 開始,返回從後邊開始len個字符組成的子序列

SPACE(N)
    返回一個由N空格組成的字符串。

SUBSTRING(str,pos) , SUBSTRING(str FROM pos) SUBSTRING(str,pos,len) , SUBSTRING(str FROM pos FOR len)
    不帶有len 參數的格式從字符串str返回一個子字符串,起始於位置 pos。帶有len參數的格式從字符串str返回一個長度同len字符相同的子字符串,起始於位置 pos。 使用 FROM的格式為標準 SQL 語法。也可能對pos使用一個負值。假若這樣,則子字符串的位置起始於字符串結尾的pos 字符,而不是字符串的開頭位置。在以下格式的函數中可以對pos 使用一個負值。

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str) TRIM(remstr FROM] str)
    返回字符串 str , 其中所有remstr 前綴和/或後綴都已被刪除。若分類符BOTH、LEADIN或TRAILING中沒有一個是給定的,則假設為BOTH 。 remstr 為可選項,在未指定情況下,可刪除空格。

 

6.存儲過程

儲過程,是一個存儲在MySQL中的SQL語句集合,當主動去調用存儲過程時,其中內部的SQL語句會按照邏輯執行。

  1. 創建存儲過程
    delimiter $$
    create procedure p1()
    BEGIN
        select * from d1;
    END $$
    delimiter ;
  2. 執行存儲過程
    call p1();
    #!/usr/bin/env python
    # -*- coding:utf-8 -*-
    import pymysql
    
    conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='root123', db='userdb')
    cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)
    # 執行存儲過程
    cursor.callproc('p1')
    result = cursor.fetchall()
    
    cursor.close()
    conn.close()
    
    print(result)

     

  3. 刪除存儲過程
    drop procedure proc_name;