建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營銷解決方案
MySQL的索引與事務、存儲引擎MyISA和InnoDB
MySQL索引與事務，視圖，存儲引擎MylSAM和lnnoDB
索引的概念
數(shù)據(jù)庫中的索引與書籍中的目錄類似：
1.一本書中，無須閱讀整本書,利用目錄就可以快速查找所需信息
2.書中的目錄是一個詞語列表，其中注明了包含各個詞的頁碼
站在用戶的角度思考問題，與客戶深入溝通，找到武義網(wǎng)站設計與武義網(wǎng)站推廣的解決方案，憑借多年的經(jīng)驗，讓設計與互聯(lián)網(wǎng)技術(shù)結(jié)合，創(chuàng)造個性化、用戶體驗好的作品，建站類型包括：成都網(wǎng)站建設、網(wǎng)站建設、企業(yè)官網(wǎng)、英文網(wǎng)站、手機端網(wǎng)站、網(wǎng)站推廣、國際域名空間、網(wǎng)站空間、企業(yè)郵箱。業(yè)務覆蓋武義地區(qū)。
數(shù)據(jù)庫索引：
1.在數(shù)據(jù)庫中，索引使數(shù)據(jù)庫程序無須對整個表進行掃描，就可以在其中找到所需數(shù)據(jù)
2.數(shù)據(jù)庫中的索弓|是某個表中一列或者若干列值的集合，以及物理標識這些值的數(shù)據(jù)頁的邏輯指針清單
索引的作用：
1.設置了合適的索引之后，數(shù)據(jù)庫利用各種快速的定位技術(shù)，能夠大大加快查詢速率
2.特別是當表很大時，或者查詢涉及到多個表時，使用索引可使查詢加快成干倍
3.可以降低數(shù)據(jù)庫的IO成本，并且索引還可以降低數(shù)據(jù)庫的排序成本
4.通過創(chuàng)建唯一性索引保證數(shù)據(jù)表數(shù)據(jù)的唯一性
5.可以加快表與表之間的連接
6.在使用分組和排序時，可大大減少分組和排序時間
索引的分類
普通索引：
是最基本的索引類型，而且它沒有唯一性之類的限制
唯一性索引：
這種索引和前面的“普通索引”基本相同，但有一個區(qū)別:索引列的所有值都只能出現(xiàn)一次，即必須唯一
主鍵：
主鍵是一種唯一性索引，但它必須指定為”PRIMARY KEY"
全文索引：
MySQL從3.23.23版開始支持全文索引和全文檢索。在MySQL中全文索引的索引類型為FULLTEXT,全文索引可以在VARCHAR或者TEXT類型的列上創(chuàng)建
單列索引與多列索引：
索引可以是單列上創(chuàng)建的索引，也可以是在多列上創(chuàng)建的索引
創(chuàng)建索引的原則依據(jù)
1.表的主鍵、外鍵必須有索引
2.數(shù)據(jù)量超過300行的表應該有索引
3.經(jīng)常與其他表進行連接的表，在連接字段.上應該建立索引
4.唯一性太差的字段不適合建立索引
5.更新太頻繁地字段不適合創(chuàng)建索引6.經(jīng)常出現(xiàn)在Where子句中的字段，特別是大表的字段,應該建立索引
7.索引應該建在選擇性高的字段上
8.索引應該建在小字段上，對于大的文本字段甚至超長字段，不要建索引
事務的概念
1.事務是一種機制、一個操作序列，包含了一組數(shù)據(jù)庫操作命令，并且把所有的命令作為-一個整體一起向系統(tǒng)提交或撤銷操作請求，即這一組數(shù)據(jù)庫命令要么都執(zhí)行,要么都不執(zhí)行
2.事務是一個不可分割的工作邏輯單元，在數(shù)據(jù)庫系統(tǒng)上執(zhí)行并發(fā)操作時，事務是最小的控制單元
3.適用于多用戶同時操作的數(shù)據(jù)庫系統(tǒng)的場景，如銀行、保險公司及證券交易系統(tǒng)等等
4.通過事務的整體性以保證數(shù)據(jù)的一致性
事務的ACID特點
原子性(Atomicity)：
1.事務是一個完整的操作，事務的各元素是不可分的(原子的)
2.事務中的所有元素必須作為一個整體提交或回滾
3.如果事務中的任何元素失敗，則整個事務將失敗
一致性(Consistency)：
當事務完成時，數(shù)據(jù)必須處于一 致狀態(tài):在事務開始之前，數(shù)據(jù)庫中存儲的數(shù)據(jù)處于一致狀態(tài);在正在進行的事務中，數(shù)據(jù)可能處于不一致的狀態(tài);當事務成功完成時，數(shù)據(jù)必須再次回到已知的一致狀態(tài)
隔離性(Isolation)：
1.對數(shù)據(jù)進行修改的所有并發(fā)事務是彼此隔離的，這表明事務必須是獨立的，它不應以任何方式依賴于或影響其他事務
2.修改數(shù)據(jù)的事務可以在另一個使用相同數(shù)據(jù)的事務開始之前訪問這些數(shù)據(jù)，或者在另一個使用相同數(shù)據(jù)的事務結(jié)束之后訪問這些數(shù)據(jù)
持久性(Durability)：
1.事務持久性指不管系統(tǒng)是否發(fā)生故障，事務處理的結(jié)果都是永久的
2.一旦事務被提交，事務的效果會被永久地保留在數(shù)據(jù)庫中
事務的操作
1.默認情況下MySQL的事務是自動提交的，當sql語句提交時事務便自動提交
2.手動對事務進行控制的方法：
①事務處理命令控制
②使用set設置事務處理方式
3.事務處理命令控制事務：
①begin：開始一個事務
②commit：提交一個事務
②rollback：回滾一個事務
4.使用set命令進行控制：
①set autocommit=0：禁止自動提交
②set autocommit=1：開啟自動提交
存儲引擎概念介紹
1.MySQL中的數(shù)據(jù)用各種不同的技術(shù)存儲在文件中,每一種技術(shù)都使用不同的存儲機制、索引技巧、鎖定水平并最終提供不同的功能和能力，這些不同的技術(shù)以及配套的功能在MySQL中稱為存儲引擎
2.存儲引擎就是MySQL將數(shù)據(jù)存儲在文件系統(tǒng)中的存儲方式或者存儲格式
3.目前MySQL常用的兩種存儲引擎：
①MyISAM（適合讀）
②InnoDB（適合寫）
4.MySQL存儲引擎是MySQL數(shù)據(jù)庫服務器中的組件，負責為數(shù)據(jù)庫執(zhí)行實際的數(shù)據(jù)I/O操作
5.使用特殊存儲引擎的主要優(yōu)點之一在于， 僅需提供特殊應用所需的特性，數(shù)據(jù)庫中的系統(tǒng)開銷較小，具有更有效和更高的數(shù)據(jù)庫性能
6.MySQL系統(tǒng)中，存儲引擎處于文件系統(tǒng)之.上，在數(shù)據(jù)保存到數(shù)據(jù)文件之前會傳輸?shù)酱鎯σ?，之后按照各個存儲引擎的存儲格式進行存儲
MyISAM的介紹
1.MyISAM存儲引擎是MySQ關(guān)系數(shù)據(jù)庫系統(tǒng)5.5版本之前默認的存儲引擎，前身是ISAM
2.ISAM是一個定義明確且歷經(jīng)時間考驗的數(shù)據(jù)表格管理方法，在設計之時就考慮到數(shù)據(jù)庫被查詢的次數(shù)要遠大于更新的次數(shù)
3.SAM的特點：
①ISAM執(zhí)行讀取操作的速度很快,
②它不支持事務處理
③而且不占用大量的內(nèi)存和存儲資源
④不能夠容錯
MyISAM適用的生產(chǎn)場景舉例：
1.公司業(yè)務不需要事務的支持
2.一般單方面讀取數(shù)據(jù)比較多的業(yè)務，或單方面寫入數(shù)據(jù)比較多的業(yè)務
3.MyISAM存儲引擎數(shù)據(jù)讀寫都比較頻繁場景不適合
4.使用讀寫并發(fā)訪問相對較低的業(yè)務
5.數(shù)據(jù)修改相對較少的業(yè)務
6.對數(shù)據(jù)業(yè)務一致性要求不是非常高的業(yè)務
7.服務器硬件資源相對比較差
InnoDB特點介紹
1.支持事務:支持4個事務隔離級別
2.行級鎖定，但是全表掃描仍然會是表級鎖定
3.讀寫阻塞與事務隔離級別相關(guān)
4.具有非常高效的緩存特性:能緩存索引，也能緩存數(shù)據(jù)
5.表與主鍵以簇的方式存儲
6.支持分區(qū)、表空間，類似oracle數(shù)據(jù)庫
7.支持外鍵約束，5.5以前不支持全文索引，5.5版本以后支持全文索引
8.對硬件資源要求還是比較高的場合
InnoDB適用生產(chǎn)場景分析
1.業(yè)務需要事務的支持
2.行級鎖定對高并發(fā)有很好的適應能力，但需確保查詢是通過索引來完成
3.業(yè)務數(shù)據(jù)更新較為頻繁的場景， 如:論壇，微博等
4.業(yè)務數(shù)據(jù)一致性要求較高，例如:銀行業(yè)務
5.硬件設備內(nèi)存較大， 利用Innodb較好的緩存能力來提高內(nèi)存利用率，減少磁盤IO的壓力
配置存儲引擎
1.在企業(yè)中選擇好合適的存儲引擎之后，就可以進行修改了
2.修改步驟：
①查看數(shù)據(jù)庫可配置的存儲引擎
②查看表正在使用的存儲引擎
③配置存儲引擎為所選擇的類型
3.使用show engines查看系統(tǒng)支持的存儲引擎查看表使用的存儲引擎
方法1: show table status from庫名where name='表名;
方法2: show create table表名
索引實操
1，進入數(shù)據(jù)庫，創(chuàng)建數(shù)據(jù)庫，創(chuàng)建表
[root@master2 ~]# mysql -uroot -p ##進入數(shù)據(jù)庫
Enter password: ##輸入密碼
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> create database school; ##創(chuàng)建數(shù)據(jù)庫school
Query OK, 1 row affected (0.00 sec)
mysql> use school; ##使用數(shù)據(jù)庫
Database changed
mysql> create table info( ##創(chuàng)建表
-> id int(4) not null primary key auto_increment, ##設置主鍵，自動增加
-> name varchar(10) not null, ##名字類型varchar不為空
-> address varchar(50) default 'nanjing', ##默認南京
-> age int(3) not null);
Query OK, 0 rows affected (0.02 sec)2，向表中插入數(shù)據(jù)
mysql> insert into info (name,address,age) values ('zhangsan','beijing',20),('lisi','shanghai',22);
##插入數(shù)據(jù)
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from info; ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> desc info; ##查看表結(jié)構(gòu)
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| address | varchar(50) | YES | | nanjing | |
| age | int(3) | NO | | NULL | |
+---------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)3，使用create，alter，創(chuàng)建表直接定義三種方式創(chuàng)建索引（普通索引，唯一性索引）
mysql> create index index_age on info (age); ##創(chuàng)建普通索引
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; ##查看表中的索引
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 1 | index_age | 1 | age | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index index_age on info; ##刪除表中的索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; ##查看表中的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.01 sec)
mysql> create unique index unique_name on info (name); ##創(chuàng)建唯一性索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; ##查看表中索引
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 0 | unique_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> drop index unique_name on info; ##刪除表中的索引
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; ##查看表中的索引
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> alter table info add unique index index_name (name); ##使用alter插入表索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from info; ##查看表中的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)4，創(chuàng)建兩個表，進行關(guān)聯(lián)，多表查詢
mysql> create table user( ##創(chuàng)建user表
-> id int(4) not null primary key auto_increment, ##設置主鍵和自動增加
-> name varchar(10) not null,
-> score decimal not null,
-> hobby int(2) not null default '1', ##默認1
-> index index_score (score)); ##設置索引score
Query OK, 0 rows affected (0.01 sec)
mysql> desc user; ##查看表結(jié)構(gòu)
+-------+---------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------+------+-----+---------+----------------+
| id | int(4) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| score | decimal(10,0) | NO | MUL | NULL | |
| hobby | int(2) | NO | | 1 | |
+-------+---------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
mysql> insert into user (name,score,hobby) values ('test01',88,1),('stu01',99,2),('wangwu',77,3);
##向表中插入數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from user; ##查看表內(nèi)容
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 1 | test01 | 88 | 1 |
| 2 | stu01 | 99 | 2 |
| 3 | wangwu | 77 | 3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)
mysql> create table hob( ##創(chuàng)建hob表
-> id int(2) not null primary key,
-> hob_name varchar(10) not null);
Query OK, 0 rows affected (0.00 sec)
mysql> desc hob; ##查看表結(jié)構(gòu)
+----------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+-------------+------+-----+---------+-------+
| id | int(2) | NO | PRI | NULL | |
| hob_name | varchar(10) | NO | | NULL | |
+----------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> insert into hob (id,hob_name) values (1,'看書'),(2,'運動'),(3,'聽歌'); ##插入表數(shù)據(jù)
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from hob; ##查看表內(nèi)容
+----+----------+
| id | hob_name |
+----+----------+
| 1 | 看書 |
| 2 | 運動 |
| 3 | 聽歌 |
+----+----------+
3 rows in set (0.00 sec)
mysql> select * from user inner join hob on user.hobby=hob.id; ##關(guān)聯(lián)user和hob兩張表
+----+--------+-------+-------+----+----------+
| id | name | score | hobby | id | hob_name |
+----+--------+-------+-------+----+----------+
| 1 | test01 | 88 | 1 | 1 | 看書 |
| 2 | stu01 | 99 | 2 | 2 | 運動 |
| 3 | wangwu | 77 | 3 | 3 | 聽歌 |
+----+--------+-------+-------+----+----------+
3 rows in set (0.00 sec)
mysql> select user.name,hob.hob_name from user inner join hob on user.hobby=hob.id;
##去除其他內(nèi)容顯示name和hob_name內(nèi)容
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | 看書 |
| stu01 | 運動 |
| wangwu | 聽歌 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> select u.name,h.hob_name from user u inner join hob h on u.hobby=h.id; ##設置簡易名稱
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | 看書 |
| stu01 | 運動 |
| wangwu | 聽歌 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> create view view_user as select u.name,h.hob_name from user u inner join hob h on u.hobby
##創(chuàng)建視圖
Query OK, 0 rows affected (0.00 sec)
mysql> select * from view_user; ##查看視圖
+--------+----------+
| name | hob_name |
+--------+----------+
| test01 | 看書 |
| stu01 | 運動 |
| wangwu | 聽歌 |
+--------+----------+
3 rows in set (0.00 sec)
mysql> update user set hobby=3 where name='test01'; ##修改user表中內(nèi)容
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from view_user; ##查看視圖，即視圖就是表的一個鏈接
+--------+----------+
| name | hob_name |
+--------+----------+
| stu01 | 運動 |
| test01 | 聽歌 |
| wangwu | 聽歌 |
+--------+----------+
3 rows in set (0.00 sec)5，全文索引，組合索引
mysql> select * from info; ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> show index from info; ##查看表的索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> create fulltext index full_addr on info (address); ##以address創(chuàng)建全文索引
Query OK, 0 rows affected, 1 warning (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 1
mysql> show index from info; ##查看表索引
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| info | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | |
| info | 0 | index_name | 1 | name | A | 2 | NULL | NULL | | BTREE | | |
| info | 1 | full_addr | 1 | address | NULL | 2 | NULL | NULL | YES | FULLTEXT | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> select * from user; ##查看user表內(nèi)容
+----+--------+-------+-------+
| id | name | score | hobby |
+----+--------+-------+-------+
| 1 | test01 | 88 | 3 |
| 2 | stu01 | 99 | 2 |
| 3 | wangwu | 77 | 3 |
+----+--------+-------+-------+
3 rows in set (0.00 sec)
mysql> create index index_name_score on user (name,score); ##創(chuàng)建name和score的組合索引
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show index from user; ##查看表索引
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| user | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |
| user | 1 | index_score | 1 | score | A | 3 | NULL | NULL | | BTREE | | |
| user | 1 | index_name_score | 1 | name | A | 3 | NULL | NULL | | BTREE | | |
| user | 1 | index_name_score | 2 | score | A | 3 | NULL | NULL | | BTREE | | |
+-------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)事務實操
1，開啟事務，往表中插入數(shù)據(jù)
mysql> select * from info; ##查看表內(nèi)容數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
+----+----------+----------+-----+
2 rows in set (0.00 sec)
mysql> insert into info (name,address,age) values ('wangwu','hangzhou',30)； ##插入數(shù)據(jù)
mysql> begin; ##開啟事務
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('zhaoliu','hangzhou',31); ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> savepoint a; ##設置保存節(jié)點a
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)
mysql> insert into info (name,address,age) values ('tianqi','hangzhou',32); ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> savepoint b; ##設置保存節(jié)點b
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##繼續(xù)插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> select * from info; ##查看表內(nèi)容
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
| 5 | tianqi | hangzhou | 32 |
| 6 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
6 rows in set (0.00 sec)2，利用另一個終端查看是否成功插入
[root@master2 ~]# mysql -uroot -p ##進入數(shù)據(jù)庫
Enter password: ##輸入密碼
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> use school; ##使用數(shù)據(jù)庫
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> select * from info; ##查看表內(nèi)容，此時并沒有提交
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)3，使用回滾，返回保存的節(jié)點
mysql> rollback to b; ##利用回滾到保存節(jié)點b
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
| 5 | tianqi | hangzhou | 32 |
+----+----------+----------+-----+
5 rows in set (0.00 sec)
mysql> rollback to a; ##回滾到保存節(jié)點a
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 4 | zhaoliu | hangzhou | 31 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)
mysql> rollback; ##回滾到初始，退出事務狀態(tài)
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
+----+----------+----------+-----+
3 rows in set (0.00 sec)4，使用commit提交事務
mysql> begin; ##開啟事務
Query OK, 0 rows affected (0.00 sec)
mysql> insert into info (name,address,age) values ('heiba','hangzhou',32); ##插入數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
mysql> commit; ##提交事務
Query OK, 0 rows affected (0.00 sec)
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)5，使用另一個終端查看
mysql> select * from info; ##查看表數(shù)據(jù)
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)6，另一種事務的操作方式
mysql> set autocommit=0; ##設置不自動提交事務
Query OK, 0 rows affected (0.00 sec)
mysql> update info set address='beijing' where name='heiba'; ##修改表數(shù)據(jù)
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from info; ##查看表信息
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | beijing | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)
##另一個終端查看
mysql> select * from info; ##查看表信息，并沒有修改
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | hangzhou | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)
mysql> set autocommit=1; ##開啟自動提交事務
Query OK, 0 rows affected (0.00 sec)
##另一個終端查看
mysql> select * from info; ##查看表數(shù)據(jù)，此時就已經(jīng)修改
+----+----------+----------+-----+
| id | name | address | age |
+----+----------+----------+-----+
| 1 | zhangsan | beijing | 20 |
| 2 | lisi | shanghai | 22 |
| 3 | wangwu | hangzhou | 30 |
| 7 | heiba | beijing | 32 |
+----+----------+----------+-----+
4 rows in set (0.00 sec)存儲引擎MyLSAM和InnoDB
1，查看系統(tǒng)默認存儲引擎
mysql> show engines; ##查看默認存儲引擎innodb
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
mysql> show create table info; ##查看創(chuàng)建的表的存儲引擎innodb
| info | CREATE TABLE "info" (
"id" int(4) NOT NULL AUTO_INCREMENT,
"name" varchar(10) NOT NULL,
"address" varchar(50) DEFAULT 'nanjing',
"age" int(3) NOT NULL,
PRIMARY KEY ("id"),
UNIQUE KEY "index_name" ("name"),
FULLTEXT KEY "full_addr" ("address")
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf82，修改MySQL配置文件，設置默認的存儲引擎
[root@localhost ~]# vim /etc/my.cnf ##修改配置文件
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1
default-storage-engine=Myisam ##添加默認存儲引擎為Myisam
[root@master2 ~]# systemctl restart mysqld.service ##重啟MySQL服務3，進入數(shù)據(jù)庫
[root@master2 ~]# mysql -uroot -p ##進入數(shù)據(jù)庫
Enter password: ##輸入密碼
mysql> use school; ##使用數(shù)據(jù)庫
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> create table a ( id int ); ##創(chuàng)建一個a表
Query OK, 0 rows affected (0.00 sec)
mysql> show create table a; ##查看表默認的存儲引擎Myisam
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| a | CREATE TABLE "a" (
"id" int(11) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> alter table a engine=innodb; ##修改表的存儲引擎為innodb
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table a; ##查看表的存儲引擎innodb
+-------+-------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------+
| a | CREATE TABLE "a" (
"id" int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------+
1 row in set (0.00 sec)當前標題：MySQL的索引與事務、存儲引擎MyISA和InnoDB
當前網(wǎng)址：http://www.dlmjj.cn/article/gjceic.html
