Mysql基礎 --- 索引+事務

lio-mengxiang發表於2019-03-19
MySql索引

1、索引的概念

  索引用於快速找出在某個列中有一特定值的行，不使用索引，MySQL必須從第一條記錄開始讀完整個表，直到找出相關的行，表越大，查詢資料所花費的時間就越多，如果表中查詢的列有一個索引，MySQL能夠快速到達一個位置去搜尋資料檔案，而不必檢視所有資料，那麼將會節省很大一部分時間。

  例如：有一張person表，其中有2W條記錄，記錄著2W個人的資訊。有一個Phone的欄位記錄每個人的電話號碼，現在想要查詢出電話號碼為xxxx的人的資訊。

  如果沒有索引，那麼將從表中第一條記錄一條條往下遍歷，直到找到該條資訊為止。

  如果有了索引，那麼會將該Phone欄位，通過一定的方法進行儲存，好讓查詢該欄位上的資訊時，能夠快速找到對應的資料，而不必在遍歷2W條資料了。其中MySQL中的索引的儲存型別有兩種：BTREE、HASH。也就是用樹或者Hash值來儲存該欄位，要知道其中詳細是如何查詢的，就需要會演算法的知識了。我們現在只需要知道索引的作用，功能是什麼就行。

2、索引分類

  • key: 普通索引
  • unique key: 唯一索引
  • primary key: 主鍵索引
  • fulltext: 全文索引（對於中文儲存沒什麼用，需要藉助第三方）
  • 多列索引
  • 冗餘索引

2.1 unique key 和 key的使用

建表

create table t16 (
    name char(10),
    email char(20),
    key name(name),
    unique key email(email)
)engine myisam charset utf8;
複製程式碼

這裡name欄位使用key作為普通索引 eamil欄位作為唯一索引

Mysql基礎 --- 索引+事務
插入資料

insert into t16 
values
('lisi', 'lisi@qq.com');
複製程式碼

Mysql基礎 --- 索引+事務
   如果再次插入上面的資料，就會報錯，因為unique key不能重複

insert into t16 
values 
('lisi', 'lisi@qq.com');
複製程式碼

Mysql基礎 --- 索引+事務

2.2 primary key的使用

例如在2.1表的基礎上增加id欄位

create table t17 (
    id int,
    name char(10),
    email char(20),
    primary key (id),
    key name(name),
    unique key email(email)
)engine myisam charset utf8;
複製程式碼

Mysql基礎 --- 索引+事務

2.3 索引長度

建立索引時，可以索引列的前一部分內容，比如，只索引前10個字元

建表

create table t18 (
    name char(10),
    email char(20),
    unique key email(email(10))
)engine myisam charset utf8;
複製程式碼

這裡只取eamil的前十個字元作為索引

2.4 多列索引

就是把2列或者多列的值看成整體，建立索引,例如

create table t19 (
    xing char(2),
    ming char(10),
    key xm(xing, ming)
)engine myisam charset utf8;
複製程式碼

這裡的key xm(xing, ming)就建立了多列索引 檢視索引

show index from t19 \G;
複製程式碼

Mysql基礎 --- 索引+事務
其中key_name代表索引名，column_name代表生效的列名

insert into t19 values ('朱', '元璋');
複製程式碼

Mysql基礎 --- 索引+事務
需要注意的是，注意以下兩個句子的對比

select * from t19 where xing="朱" and ming="元璋";
複製程式碼

這條查詢語句會用到索引

select * from t19 where ming="元璋";
複製程式碼

這條語句索引是不會發生作用的 請看下圖

explain select * from t19 where ming='元璋' \G;
複製程式碼

Mysql基礎 --- 索引+事務

記住索引的原則就是左字首，比如朱開頭，在左邊，就能有索引，元璋是右邊的，所以無法根據右半部分無法建立索引，因為可能還有叫李元璋，張元璋，根本不知道左邊是誰，但是根據左邊能推出右邊是誰。

3、索引的操作

3.1 檢視索引

show index from 表名或create table 表名也能看到
複製程式碼

3.2 刪除索引

alter table 表名 drop index 索引名
或者
drop index 索引名 on 表名
複製程式碼

3.3 新增索引

alter 表名 add[index/unique]索引名(列名)

新增主鍵索引
alter 表名 add primary key(列名)
複製程式碼

刪除主鍵索引

alter table 表名 drop primairy key
複製程式碼

4、事務

4.1 事務的定義

Transaction

事務：一個最小的不可再分的工作單元；通常一個事務對應一個完整的業務(例如銀行賬戶轉賬業務，該業務就是一個最小的工作單元)

一個完整的業務需要批量的DML(insert、update、delete)語句共同聯合完成 事務只和DML語句有關，或者說DML語句才有事務。這個和業務邏輯有關，業務邏輯不同，DML語句的個數不同

4.2 事務的特徵

  • 原子性：確保事務內的所有操作都成功完成，否則事務將被中止在故障點，以前的操作將回滾到以前的狀態。
  • 一致性：對於資料庫的修改是一致的。
  • 隔離性：事務是彼此獨立的，不互相影響
  • 永續性：確保提交事務後，事務產生的結果可以永久存在。

4.3 關於事務的一些術語

  • 開啟事務：Start Transaction
  • 事務結束：End Transaction
  • 提交事務：Commit Transaction
  • 回滾事務：Rollback Transactio

4.4 例項詳解事務的特徵

建立1個表，用來觀察事務的特徵

# 表1 注意mysql引擎是innodb，myisam不支援事務
create table account(
    id int,
    name char(10),
    money int
)engine innodb charset utf8;
複製程式碼

表1插入資料

insert into account values 
(1, 'zhangsan', 5000);
insert into account values 
(2, 'lisi', 5000);
複製程式碼

Mysql基礎 --- 索引+事務

4.5 例項理解事務的原子性和隔離性

# 開啟事務
start transaction;
# 給lisi加500
update account set money=money+500 where id=2;
複製程式碼

Mysql基礎 --- 索引+事務
此時，我們再開一個命令列視窗，檢視account賬戶情況

Mysql基礎 --- 索引+事務

注意這裡account並沒有給lisi加上那500 接著執行命令

# 給zhangsan減去500
update account set money=money-500 where id=1;
# 提交事務
commit;
複製程式碼

然後我們檢視account表的資料

Mysql基礎 --- 索引+事務
這既是隔離性，事務裡面的狀態並不會實時影響到這張表的資料，事務只顯示事務前和事務提交成功後的狀態。

原子性就是，如果我們事務中其中一項執行失敗，則回滾到初始狀態

# 開啟事務
start transaction;
# 給lisi加500
update account set money=money+500 where id=2;
# 此時我們想結束這個事務，並且狀態還原
rollback；
複製程式碼

一致性指的是，前後的資料改變必須一致。比如給zhangsan+500,肯定是從lisi那裡扣的500也就是-500,，所以必須資料的改變要一致

相關文章