目錄
一，Mysql索引介紹
1，索引概述
1，索引的優點
2，索引的缺點
2，索引作用
3，索引分類
普通索引
唯一索引
主鍵索引
組合索引
全文索引
4，查看索引
5，刪除索引
6，索引的應用場景
7，不適合使用索引的場景
二，Mysql事務
1，事務的四大特性(ACID)
2，事務控制語句
3，mysql事務的隔離級別
?4，使用mysql事務
一，Mysql索引介紹
1，索引概述
MySQL 索引是一種用于提高數據庫查詢效率的數據結構，
- 就像書籍的目錄一樣，能幫助數據庫快速定位到所需的數據，而不必全表掃描。例如，在一個包含大量用戶信息的表中，如果經常要根據用戶姓名來查詢記錄，那么為 “姓名” 字段建立索引后，查詢速度會顯著提升。
- 原理：索引通常是基于 B 樹（B - Tree）或哈希表等數據結構來實現的。以 B 樹為例，它將數據按照一定的規則組織成樹形結構，節點中的數據是有序排列的。當進行查詢時，數據庫可以通過比較查詢值與節點中的數據，快速決定是在當前節點的左子樹還是右子樹繼續查找，從而大大減少了查找的范圍和時間。
1，索引的優點
(1)，提高查詢速度
- 索引可以顯著加快數據檢索速度，特別是對大表查詢
- 類似于書籍的目錄，可以快速定位到所需數據
(2)，加速表連接
- 在多表連接操作時，索引能極大提高連接效率
(3)，保證數據唯一性
- 唯一索引可以確保列中數據的唯一性
(4)，優化排序和分組
- 對索引列進行ORDER BY或GROUP BY操作時效率更高
(5)，減少服務器掃描1數據量
- 數據庫引擎可以跳過不必要的數據行
2，索引的缺點
(1)：占用存儲空間
- 索引需要額外的磁盤空間存儲
- 對于大型表，索引可能占用相當可觀的存儲
(2)：減低寫入性能
- 插入、更新和刪除操作需要同時維護索引
- 每次數據修改都可能需要更新多個索引
(3)：維護成本
- 索引需要定期維護以保持高效
- 隨著數據變化，索引可能變得碎片化
(4)：優化器選擇問題
- 有時查詢優化器可能選擇不理想的索引
- 需要DBA監控和調整索引使用
(5)：設計復雜性
- 需要合理設計索引策略，過多或不當的索引反而會降低性能
2，索引作用
3，索引分類
在數據庫表中，對字段建立索引可以大大提高查詢速度。通過善用這些索引,可以令 MySQL 的查詢和運行更加高效。索引是快速搜索的關鍵。MySQL 索引的建立對于 MySQL 的高效運行是非常重要的。
? ? ?從物理存儲的角度來劃分，索引分為聚簇索引和非聚簇索引兩種，聚簇索引是按照數據存放的物理位置為順序的，而非聚簇索引就不一樣了;聚簇索引能提高多行檢索的速度，而非聚簇索引對于單行的檢索更快。
從邏輯的角度來劃分，索引分為普通索引、唯一索引、主鍵索引、組合索引和全文索引。
-
普通索引
普通索引是最基本的索引，它沒有任何限制，也是大多數情況下用到的索引
##準備創建索引的庫和表
create database auth; ##創建auth庫
use auth;
create table users (id int(10),user_name char(20),user_pass char(50)); ##創建表##直接創建索引：語法:create index 索引名 on 表名 索引的值;
create index aaa on users(user_name(20));##修改表結構添加索引
alter table users(表名) add index bbb(索引名) （user_pass(50) "表中的值");user_name(20)其中 20 是可選項。如果忽略 20 的值，則使用整個列的值作為索引。如果指定使用列前的 20個字符來創建索引,就是使用列的一部分來創建索引，這樣有利于減小索引文件的大小，節省索引列所占的空間。在某些情況下，只能對列的前綴進行索引。索引列的長度有一個最大上限255個字節(MyISAM和 InnoDB 表的最大上限為 1000 個字節)，如果索引列的長度超過了這個上限,就只能用列的前綴進行索引。另外，BLOB或TEXT類型的列也必須使用前綴索引。?
-
唯一索引
唯一索引與普通索引類似，不同的就是:唯一索引的索引列的值必須唯一，但允許有空值(注意和主鍵不同)。如果是組合索引，則列值的組合必須唯一。唯一索引創建方法和普通索引類似。
##創建唯一索引
create unique index bbb on users(id);
mysql> create unique index bbb on users(id);mysql> show index from users\G ##查看users表的索引
*************************** 1. row ***************************Table: usersNon_unique: 0Key_name: bbbSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
1 row in set (0.01 sec)##修改表結構的時候添加唯一索引：
alter table users add unique ccc(user_name);?
-
主鍵索引
主鍵索引是一種特殊的唯一索引，一個表只能有一個主鍵，不允許有空值般是在建表的時候同時創建主鍵索引。
創建主鍵語法：
create table 表名 （列名 數據類型 primary key,）;
##創建主鍵索引，表名為students
mysql> CREATE TABLE students (-> student_id INT PRIMARY KEY,-> name VARCHAR(50),-> age INT-> );
Query OK, 0 rows affected (0.03 sec)mysql> show index from students\G #查看表中的主鍵信息
*************************** 1. row ***************************Table: studentsNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: student_idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
1 row in set (0.00 sec)
?
-
組合索引
平時用的 SQL 查詢語句一般都有比較多的限制條件，所以為了進一步榨取MySQL 的效率，就要考慮建立組合索引。在組合索引的創建中，有兩種場景，即為單列索引和多列索引。
語法：
? ?CREATE TABLE 表名 (
? ? 列1 數據類型,
? ? 列2 數據類型,
? ? ...,
? ? INDEX 索引名 (列1, 列2, ...)
);
?
mysql> CREATE TABLE orders (-> order_id INT,-> customer_id INT,-> order_date DATE,-> INDEX idx_customer_order (customer_id, order_date)-> );mysql> show index from ordes\G ##查看創建的組合索引
ERROR 1146 (42S02): Table 'auth.ordes' doesn't exist
mysql> show index from orders\G
*************************** 1. row ***************************Table: ordersNon_unique: 1Key_name: idx_customer_orderSeq_in_index: 1Column_name: customer_idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
*************************** 2. row ***************************Table: ordersNon_unique: 1Key_name: idx_customer_orderSeq_in_index: 2Column_name: order_dateCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: YESIndex_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
2 rows in set (0.01 sec)
?
-
全文索引
對于較大的數據集，將資料輸入一個沒有FULLTEXT 索引的表中，然后創建索引，其速度比把資料輸入現有FULLTEXT 索引的速度更快。不過切記對于大容量的數據表，生成全文索引是一個非常消耗時間、非常消耗硬盤空間的做法。
語法：
CREATE TABLE 表名 (
? ? 列1 數據類型,
? ? 列2 數據類型,
? ? ...,
? ? FULLTEXT INDEX 索引名 (列名) [WITH PARSER ngram]
) ENGINE=InnoDB;
?
##創建全文索引
mysql> CREATE TABLE articles (-> id INT AUTO_INCREMENT PRIMARY KEY,-> title VARCHAR(200),-> content TEXT,-> FULLTEXT INDEX ft_idx_title_content (title, content)-> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.05 sec)##查看全文索引
mysql> show index from articles\G
*************************** 1. row ***************************Table: articlesNon_unique: 0Key_name: PRIMARYSeq_in_index: 1Column_name: idCollation: ACardinality: 0Sub_part: NULLPacked: NULLNull: Index_type: BTREEComment:
Index_comment: Visible: YESExpression: NULL
....../省略部分內容4，查看索引
MySQL 數據表索引已經創建好了，那么如何才能查看剛剛創建的索引?或者怎么去查看表內已經存在的索引?有以下兩種查看當前索引的方式。
##查看某個表的索引
show index from 表名;? ? ?
show keys from 表名
mysql> show index from users\G ##查看users表的索引
*************************** 1. row ***************************Table: users ##表的名稱Non_unique: 0 ##如果索引不能包括重復詞，則為0;如果可以，則為1。Key_name: bbb ##索引的名稱Seq_in_index: 1 ##索引中的列序號，從 1開始。Column_name: id ##列名稱Collation: A ##列以什么方式存儲在索引中。在 MySQL 中，有值'A’(升序)或 NULL(無分類)。Cardinality: 0 ##索引中唯一值數目的估計值。通過運行 ANALYZE TABLE 或myisamchk-a 可以更新。基數根據被存儲為整數的統計數據來計數，所以即使對于小型表，該值也沒有必要是精確的。基數越大，當進行聯合時，MySQL使用該索引的機會就越大。Sub_part: NULL ##如果列只是被部分地編入索引，則為被編入索引的字符的數目。
如果整列被編入索引，則為 NULL。Packed: NULL ##如果列含有 NULL，則含有YES。如果沒有，則該列含有 NO。Index_type: BTREE ##用過的索引方法(BTREE,FULLTEXT, HASH,RTREE)。Comment: ##備注。
5，刪除索引
索引在創建之后,是會占用一定的磁盤空間的,因此表內如果有不再使用的索引從數據庫性能方面考慮，最好是刪除無用索引。索引的刪除有如下兩種方法。
drop index 索引名 on 表名;
alter table 表名 drop index 索引名;
6，索引的應用場景
- 快速查找：在大型數據庫表中，通過索引可以快速定位到滿足特定條件的數據行。例如，在一個包含數百萬條記錄的用戶表中，根據用戶 ID 或用戶名進行查詢時，索引可以大大減少查詢時間。
- 多條件查詢：當查詢涉及多個條件時，索引可以幫助數據庫快速定位到滿足所有條件的數據。例如，在一個訂單表中，查詢特定日期范圍內、特定客戶的訂單，通過對訂單日期和客戶 ID 建立索引，可以快速找到符合條件的訂單記錄。
- 快速排序：索引可以按照特定的列進行排序，從而加快數據的排序速度。例如，在一個產品表中，按照價格對產品進行排序，如果價格列上有索引，數據庫可以直接使用索引來快速獲取排序后的結果，而無需對整個表進行排序操作。
- 分組排序：在進行分組查詢時，索引也可以幫助提高排序效率。例如，在一個銷售表中，按照地區對銷售數據進行分組，并對每個地區的銷售額進行排序。通過對地區列和銷售額列建立索引，可以快速完成分組和排序操作。
7，不適合使用索引的場景
- 數據量小的表：當表中的數據量較少時，全表掃描的成本很低，使用索引可能會增加額外的開銷，而不會帶來明顯的性能提升。例如，一個只有幾十條記錄的表，直接全表掃描查找數據可能比通過索引查找更快，因為索引的維護和查找本身也需要消耗一定的資源。
- 頻繁更新的表：對于頻繁進行插入、更新和刪除操作的表，索引的維護成本較高。每次數據發生變化時，都需要更新相應的索引，這會增加數據庫的負擔，降低數據更新的性能。例如，在一個實時交易系統中，交易記錄表可能會頻繁地插入新記錄，如果為該表的每個列都建立索引，那么每次插入新交易記錄時，都需要更新多個索引，這會大大影響系統的插入性能。
二，Mysql事務
MySQL 事務是數據庫管理系統執行過程中的一個邏輯單位，由一組 SQL 語句組成，這些語句要么全部成功執行，要么全部不執行。
1，事務的四大特性(ACID)
- 原子性（Atomicity）：事務中的所有操作要么全部完成，要么全部不完成，不會停留在中間某個環節。如果事務執行過程中發生錯誤，會被回滾到事務開始前的狀態。
- 一致性（Consistency）：事務將數據庫從一種一致狀態轉換到另一種一致狀態。例如，在轉賬操作中，無論事務是否成功，轉賬者和收款者的總金額應該保持不變。
- 隔離性（Isolation）：多個事務并發執行時，一個事務的執行不能被其他事務干擾，每個事務都感覺不到系統中其他事務在并發執行。
- 持久性（Durability）：事務一旦提交，它對數據庫的改變就應該是永久性的，不會因系統故障而丟失。
2，事務控制語句
- BEGIN?或?START TRANSACTION：顯式地開始一個事務。
- COMMIT：提交事務，將事務中所有的操作永久保存到數據庫中。
- ROLLBACK：回滾事務，撤銷事務中所有未提交的操作。
- SAVEPOINT：在事務中創建保存點，方便部分回滾。
- RELEASE SAVEPOINT：刪除指定的保存點。
- ROLLBACK TO SAVEPOINT：將事務回滾到指定的保存點。
3，mysql事務的隔離級別
MySQL 提供了四種隔離級別，用于控制事務之間的可見性和干擾程度：
- READ UNCOMMITTED（讀未提交）：最低的隔離級別，允許一個事務讀取另一個事務未提交的數據，可能會導致臟讀、不可重復讀和幻讀問題。
- READ COMMITTED（讀已提交）：一個事務只能讀取另一個事務已經提交的數據，避免了臟讀，但仍可能出現不可重復讀和幻讀。
- REPEATABLE READ（可重復讀）：MySQL 的默認隔離級別，確保在同一個事務中多次讀取同一數據的結果是一致的，避免了臟讀和不可重復讀，但可能存在幻讀。
- SERIALIZABLE（串行化）：最高的隔離級別，強制事務串行執行，避免了所有的并發問題，但會降低數據庫的并發性能。
?4，使用mysql事務
提交事務：
commit；
回滾事務；
rollback;
開始事務：
begin;
commit;
自動提交事務：
set autocommit=0? ?禁止自動提交
set autocommit=0? ?##開啟自動提交
mysql> set autocommit=0 ##關閉自動提交-> ;
Query OK, 0 rows affected (0.00 sec)mysql> insert into users value(2,'lisi','123456'); ##插入一個數據
Query OK, 1 row affected (0.00 sec)mysql> select * from users; ##查看表中的內容
+------+-----------+-----------+
| id | user_name | user_pass |
+------+-----------+-----------+
| 1 | zhangsan | 123456 |
| 2 | lisi | 123456 |
+------+-----------+-----------+
2 rows in set (0.00 sec)mysql> rollback; ##事務回滾
Query OK, 0 rows affected (0.00 sec)mysql> select * from users; ##回滾后數據會消失
+------+-----------+-----------+
| id | user_name | user_pass |
+------+-----------+-----------+
| 1 | zhangsan | 123456 |
+------+-----------+-----------+
1 row in set (0.00 sec)?
mysql> begin; ##開啟事務
Query OK, 0 rows affected (0.00 sec)mysql> insert into users value(3,'aaaa','123456');
Query OK, 1 row affected (0.00 sec)mysql> insert into users value(4,'bbb','123456');
Query OK, 1 row affected (0.00 sec)mysql> commit; ##結束事務
Query OK, 0 rows affected (0.00 sec)mysql> select * from users; ##查看表中的內容
+------+-----------+-----------+
| id | user_name | user_pass |
+------+-----------+-----------+
| 1 | zhangsan | 123456 |
| 3 | aaaa | 123456 |
| 4 | bbb | 123456 |
+------+-----------+-----------+
3 rows in set (0.00 sec)
