個人整理，感謝觀看

一、索引
- 1.1 概念
- 1.2 索引的作用
- 1.3 索引的分類
二、創建索引的原則依據
三、查看、洗掉、索引的方法
四、事務
- 4.1 概念
- 4.2 事務的ACID特點
- 4.3 事務控制陳述句
五、存盤引擎
- 5.1 介紹
- 5.2 MyISAM
- 5.3 MyISAM適用的生產場景
- 5.4 InnoDB介紹
- 5.5 適用生產場景分析
- 5.6 企業選擇存盤引擎依據
六、修改存盤引擎方法

一、索引

1.1 概念

排序的串列，索引值和這個值所對應的位置，物理地址；
方便查詢，通過物理地址就可以找到所需資料；
表中一列或若干列值排序的方法；
占用額外的磁盤空間；

1.2 索引的作用

快速定位，加快查詢速率；
當資料表很大后查詢多個表示，效率提高；
降低資料庫的IO成本，降低資料庫的排序成本；
創建唯一索引保證資料表資料的唯一性；
加快資料表與表之間的連接；
使用分組和排序時，可大大的減少組合排序時間；

1.3 索引的分類

普通索引

最基本的索引型別，沒有唯一性之類的限制
創建普通的索引的方式
1、用create方法創建普通索引

mysql> create index index_age on info (age);

2、用alter方法創建普通索引

mysql> alter table info add index index_age (age);

3、創建表的時候創建普通索引

mysql> create table 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',
    -> index index_scrore (score));

唯一索引

與 ‘普通索引’ 基本相同
區別普通索引：索引列的所有值只能出現一次，必須唯一
1、用create unique方法，創建唯一索引

mysql> create unique index unique_name on info (name);

2、用alter table方法創建唯一索引

mysql> alter table info add unique index_name (name);

3、用新建表的方法創建唯一索引

mysql> create table user1 (
    -> 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',
    -> unique index index_scrore (score));

主鍵索引

是一種特殊的唯一索引，指定為‘primary key’ （小寫了）
一個表只能有一個主鍵，不允許有空值

mysql> create table user2 (
    -> id int(4) not null  auto_increment,
    -> name varchar(10) not null,
    -> age int(3) not null,
    -> primary key (`id`));

組合索引（單列與多列）

可以是單列上創建的，也是可以是多列上創建的
最左原則，從左往右依次執行

mysql> create table user3 (
    -> name varchar(10) not null,
    -> age int(3) not null,
    -> sex tinyint(1) not null,
    -> index user3(name,age,sex));

全文索引

mysql從3.23.23版本開始支持全文索引和全文檢索
索引型別為fulltext
可以在 CHAR,VARCHAR或者TEXT型別的列上創建

mysql> create table user4 (
    -> id int(11) not null auto_increment,
    -> tile char(255) character set utf8 collate utf8_general_ci not null,
    -> content text character set utf8 collate utf8_general_ci not null,
    -> primary key (`id`),
    -> fulltext (content));

二、創建索引的原則依據

表的主鍵，外鍵必須有索引
記錄數超過300行的表應該有索引
經常與其他表進行連接的表，在連接欄位上建立索引
經常出現在 where 子句中的欄位，特別是大表的欄位
選擇性比較高的欄位上
小欄位也可以建立
唯一性太差的欄位不適合建立索引
更新太頻繁的欄位不適合創建索引

三、查看、洗掉、索引的方法

查看索引

>show    index   from   表名;
>show    keys     from   表名;
例如：
show index from IT_salary;
show keys from IT_salary;

洗掉索引

>drop  index   索引名  on   表名;
>alter  table    表名  drop   索引名;
例如：
drop index index_puid on info;
alter table info drop index index_puid;

四、事務

4.1 概念

一種保護機制吧，一個操作序列，包含了一組資料庫操作命令，并且把所有的命令作為一個整體一起向系統提交或撤銷操作請求，即這組資料庫命令要么都執行，要么都不執行
不可分割的作業邏輯單元，在資料庫系統上執行并發操作時，事務是最小的控制單元
適用于多用戶同時操作的資料庫系統的場景，資料比較敏感的關鍵性操作等，如：銀行，證券交易系統…
通過事務的整體性以保證資料的一致性！！

4.2 事務的ACID特點

原子性

事務是一個完整的操作，事務的各元素是不可分的
事務中的所有元素必須作為一個整體提交或回滾
如果事務中的任何元素失敗，則整個事務將失敗

一致性

當事務完成時，資料必須處于一致狀態
在事務開始前，資料庫中存盤的資料處于-致狀態
在正在進行的事務中，資料可能處于不一致的狀態
當事務成功完成時，資料必須再次回到已知的一致狀態

隔離性

對資料進行修改的所有并發事務是彼此隔離的，表明事務必須是
獨立的，它不應以任何方式依賴于或影響其他事務
修改資料的事務可在另一個使用相同資料的事務開始之前訪問這
些資料，或者在另一個使用相同資料的事務結束之后訪問這些數
據

持久性

指不管系統是否發生故障，事務處理的結果都是永久的
一旦事務被提交，事務的效果會被永久地保留在資料庫中

4.3 事務控制陳述句

Mysql事務默認是自動提交的，當sql陳述句提交時事務便自動提交
控制陳述句

BEGIN或START TRANSACTION
COMMIT
ROLLBACK
SAVEPOINT 事務名
RELEASE SAVEPOINT 事務名
ROLLBACK TO 事務名
SET TRANSACTION

控制陳述句解釋：（按自上到下順序解釋：）

顯示開啟一個事務；
提交事務，并使已對資料庫進行的所有修改變為永久性；
回滾，會結束用戶的事務，并撤銷正在進行的所有未提交的修改；
允許在事務中創建一個保存點，一個事務中可以有多個
洗掉一個事務的保存點，當沒有指定保存點是，執行該陳述句會顯示例外
把事務會滾到保存的事務點；
用來設定事務的隔離級別，lnnonDB 存盤引擎提供事務的隔離級別，有read uncommitted , read committed …

先搭建庫，表，添加資料；

創建資料庫：
mysql> create database uou;
Query OK, 1 row affected (0.00 sec)

          
創建資料表：
mysql> create table lol(
    -> id int(4) not null primary key auto_increment,
    -> name varchar(10) not null,
    -> address varchar(50) default 'taihe',
    -> age int(3) not null);
    
在表中添加資料：
mysql> insert into lol
    -> (name,address,age) values
    -> ('小明','北京',23),
    -> ('小剛','南京',20);

查看資料記錄
mysql> select * from lol;
+----+--------+---------+-----+
| id | name   | address | age |
+----+--------+---------+-----+
|  1 | 小明   | 北京    |  23 |
|  2 | 小剛   | 南京    |  20 |
+----+--------+---------+-----+

事務操作

mysql> begin;   
Query OK, 0 rows affected (0.00 sec)

mysql> insert into lol
    -> (name,address,age)
    -> values
    -> ('小紅','深圳',22);


mysql> select * from lol;
+----+--------+---------+-----+
| id | name   | address | age |
+----+--------+---------+-----+
|  1 | 小明   | 北京    |  23 |
|  2 | 小剛   | 南京    |  20 |
|  3 | 小紅   | 深圳    |  22 |
+----+--------+---------+-----+
3 rows in set (0.00 sec)

mysql> savepoint a;          #保存到一個事務點名a；
Query OK, 0 rows affected (0.00 sec)


mysql> insert into lol   #寫入資料驗證
    -> (name,address,age)
    -> values
    -> ('小蘭','上海',25);
Query OK, 1 row affected (0.00 sec)

mysql> select * from lol;
+----+--------+---------+-----+
| id | name   | address | age |
+----+--------+---------+-----+
|  1 | 小明   | 北京    |  23 |
|  2 | 小剛   | 南京    |  20 |
|  3 | 小紅   | 深圳    |  22 |
|  4 | 小蘭   | 上海    |  25 |
+----+--------+---------+-----+
4 rows in set (0.00 sec)



mysql> rollback to a;    #回滾到保存點a；
Query OK, 0 rows affected (0.00 sec)

mysql> select * from lol;
+----+--------+---------+-----+
| id | name   | address | age |
+----+--------+---------+-----+
|  1 | 小明   | 北京    |  23 |
|  2 | 小剛   | 南京    |  20 |
|  3 | 小紅   | 深圳    |  22 |
+----+--------+---------+-----+
3 rows in set (0.00 sec)

mysql> insert into lol      #重新寫入資料
    -> (name,address,age)
    -> values
    -> ('小蘭','上海',25);
Query OK, 1 row affected (0.00 sec)

mysql> commit;	        #提交資料之后，保證一致性，才會顯示！！

退出當前管理用戶賬號，從新登錄進來，會看到資料已經寫進去！

五、存盤引擎

5.1 介紹

MySQL中的資料用各種不同的技術存盤在檔案中，每
一種技術都使用不同的存盤機制、索引技巧、鎖定水平
并最終提供不同的功能和能力，這些不同的技術以及配
套的功能在MySQL中稱為存盤引擎
存盤引擎是MySQL將資料存盤在檔案系統中的存盤方
式或者存盤格式
MySQL常用的存盤引擎
●MyISAM
●InnoDB
MySQL資料庫中的組件，負責執行實際的資料I/0操作
MySQL系統中，存盤引擎處于檔案系統之上，在資料
保存到資料檔案之前會傳輸到存盤引擎，之后按照各個
存盤引擎的存盤格式進行存盤

5.2 MyISAM

MyISAM不支持事務，也不支持外鍵
訪問速度快
對事務完整性沒有要求
MyISAM在磁盤.上存盤成三個檔案
●.frm檔案存盤表定義
●資料檔案的擴展名為.MYD (MYData)
●索引檔案的擴展名是.MYI (MYIndex)

■表級鎖定形式，資料在更新時鎖定整個表
■資料庫在讀寫程序中相互阻塞
●會在資料寫入的程序阻塞用戶資料的讀取
●也會在資料讀取的程序中阻塞用戶的資料寫入
■資料單獨寫入或讀取，速度程序較快且占用資源相對少
■MyIAM支持的存盤格式
●靜態表
●動態表
●壓縮表

5.3 MyISAM適用的生產場景

公司業務不需要事務的支持
單方面讀取或寫入資料比較多的業務
MyISAM存盤引擎資料讀寫都比較頻繁場景不適合
使用讀寫并發訪問相對較低的業務
資料修改相對較少的業務
對資料業務-致性要求不是非常高的業務
服務器硬體資源相對比較差

5.4 InnoDB介紹

支持4個事務隔離級別
行級鎖定，但是全表掃描仍然會是表級鎖定
MyISAM存盤引擎資料讀寫都比較頻繁場景不適合
使用讀寫并發訪問相對較低的業務
資料修改相對較少的業務
對資料業務-致性要求不是非常高的業務
服務器硬體資源相對比較差

5.5 適用生產場景分析

業務需要事務的支持
行級鎖定對高并發有很好的適應能力，但需確保查詢是
通過索引來完成
業務資料更新較為頻繁的場景
如:論壇，微博等
業務資料一致性要求較高
如:銀行業務
硬體設備記憶體較大，利用Innodb較好的快取能力來提
高記憶體利用率，減少磁盤IO的壓力

5.6 企業選擇存盤引擎依據

需要考慮每個存盤引擎提供的核心功能及應用場景
支持的欄位和資料型別
所有引擎都支持通用的資料型別
但不是所有的引|擎都支持其它的欄位型別,如二進制物件
鎖定型別:不同的存盤引擎支持下同級別的鎖定
表鎖定
行鎖定
索引的支持
建立索弓|在搜索和恢復資料庫中的資料時能顯著提高性能
不同的存盤弓|擎提供不同的制作索引|的技術
有些存盤引擎根本不支持索引
事務處理的支持
提高在向表中更新和插入資訊期間的可靠性
可根據企業業務是否要支持事務選擇存盤弓|擎

六、修改存盤引擎方法

方法1：
alter table 表名  engine=引擎；

方法2：
vi  /etc/my.cnf
在[mysqld]配置段內添加，指定默認引擎并重啟服務；
default-storage-engine=innoDB
重啟mysqld

方法3：
create table  表名  (欄位....)  engine = 引擎；

方法4：
Mysql_convert_table_format--user=root--password=密碼 --sock=/tmp/mysql.sock --engine = 引擎  庫名  表名

yum -y install perl-DBI perl-DBD-MySQL
/usr/local/mysql/bin/mysql_convert_table_format --user=root --password='123456' --sock=/tmp/mysql.sock auth

轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/23494.html

標籤：其他

上一篇：資料庫綜合練習

下一篇：LeetCode 1,26題

理論+實驗：MySQL索引、事物與存盤引擎