一、索引

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