1鹃愤、開啟mariadb之前進行的配置

cd /etc/my.cnf.d/
vim server.cnf ---在server語句塊里增加如下三行內容
skip_name_resolve = on  ---跳過名字解析
innodb_file_per_table = on ----每個數(shù)據(jù)庫中的每張表有單獨的文件
max_connections = 20000  ---最大并發(fā)連接
systemctl start mariadb
my_print_defaults ---可以查看配置文件的生效順序

2、mariadb常用的存儲引擎InnoDB和MyISAM的特點和區(qū)別

• InnoDB
  ①數(shù)據(jù)和索引存儲于表空間文件完域，也就是一個黑盒中软吐，用戶看不到空間里面有什么，表空間文件在所創(chuàng)建的數(shù)據(jù)庫目錄下的 tbl_name.ibd中吟税，表結構的定義在所創(chuàng)建的數(shù)據(jù)庫目錄的tbl_name.frm中
  ②支持事物
  ③基于MVCC（Mutli Version Concurrency Control）支持高并發(fā)凹耙；支持四個隔離級別，默認級別為REPEATABLE-READ肠仪；間隙鎖以防止幻讀肖抱；
  ④使用聚集索引（主鍵索引），聚集索引指目錄和書的內容在同一本書中异旧，找到索引就能找到內容意述，并且放索引的地方旁邊就是內容，非聚集索引指目錄和書的內容是分開的，索引一本書荤崇，內容放在另外一本書中拌屏，要查到內容需要兩本書
  ⑤鎖粒度：行級鎖；間隙鎖
• MyISAM
  ①每個表有三個文件术荤，存儲于創(chuàng)建的數(shù)據(jù)庫目錄中
  tbl_name.frm：表格式文件倚喂；
  tbl_name.MYD：數(shù)據(jù)文件；
  tbl_name.MYI：索引文件瓣戚；
  ②不支持事物
  ③使用非聚集索引
  ④鎖粒度：表級鎖
  ⑤崩潰無法保證表安全恢復
  ⑥支持全文索引（FULLTEXT index）端圈、壓縮、空間函數(shù)（GIS）
  適用場景：只讀或讀多寫少的場景子库、較小的表（以保證崩潰后恢復的時間較短）

MariaDB [hidb]> show engines;  ---查看存儲引擎的類型
MariaDB [hidb]> show table status \G  ---查看此數(shù)據(jù)庫中所有表的存儲引擎
MariaDB [hidb]> show table status like 'stu%' \G  ----查看此數(shù)據(jù)庫中某個表的存儲引擎
MariaDB [hidb]> show table status where engine='innodb' \G  ---查看此數(shù)據(jù)庫中存儲引擎為innodb的表
MariaDB [hidb]> create table students2(id int,name char(10)) engine=myisam;  ---創(chuàng)建表并指定存儲引擎
MariaDB [hidb]> show table status like 'students2'\G   ---可以查看到這個表的存儲引擎

3舱权、并發(fā)控制 鎖Lock

• 鎖類型 ：
  讀鎖：共享鎖，可被多個讀操作共享刚照，但不允許寫；
  寫鎖：排它鎖喧兄，獨占鎖无畔，建立寫鎖后其他的線程將不能讀這張表；
• 鎖粒度：
  表鎖：在表級別施加鎖吠冤，并發(fā)性較低浑彰；
  行鎖：在行級別施加鎖，并發(fā)性較高拯辙；維持鎖狀態(tài)的成本較大郭变；
• 鎖策略：在鎖粒度及數(shù)據(jù)安全性之間尋求一種平衡機制；
• 鎖類別：
  顯式鎖：用戶手動請求的鎖涯保；
  隱式鎖：存儲引擎自行根據(jù)需要施加的鎖诉濒；
• 顯式鎖的使用：
  (1) LOCK TABLES
  LOCK TABLES tbl_name read|write, tbl_name read|write, ...
  UNLOCK TABLES
  (2) FLUSH TABLES
  FLUSH TABLES tbl_name,... [WITH READ LOCK];
  UNLOCK TABLES;
  FLUSH的意思是把表的數(shù)據(jù)從內存中清除同步到磁盤上，我們在對表進行讀寫操作時必須先把表的數(shù)據(jù)讀入到內存中才可以夕春，內存中沒有此表的數(shù)據(jù)就無法進行讀寫操作了未荒，就相當于鎖住了這張表，解鎖相當于把磁盤數(shù)據(jù)讀到內存中及志。
  設置顯式鎖示例

方法一：
MariaDB [hidb]> lock tables students write;   ---對students這張表設置為寫鎖
用復制的方式打開另外一個會話片排，在此會話中進行如下操作
mysql
MariaDB [(none)]> use hidb;
MariaDB [hidb]> show processlist;  ---查看線程發(fā)現(xiàn)有兩個線程
MariaDB [hidb]> select * from students;   ---發(fā)現(xiàn)無法查看此表，說明此表被鎖定
在第一個會話中進行如下設置
MariaDB [hidb]> unlock tables;   ---解鎖
在第二個會話中進行如下操作
MariaDB [hidb]> select * from students;   ---發(fā)現(xiàn)可以查看此表
在第一個會話中進行如下設置
MariaDB [hidb]> lock tables students read;   ---設置讀鎖
在第二個會話中的操作
MariaDB [hidb]> select * from students;   ---發(fā)現(xiàn)可以查看此表
總結：說明寫鎖是獨占鎖速侈，不允許其他的進程進行讀和寫操作率寡，而讀鎖是共享鎖倚搬，允許其他的進程進行讀操作，但不允許寫操作
方法二：
在第一個會話中設置讀鎖
MariaDB [hidb]> flush tables with read lock; ---注意這種方式只能設置讀鎖
在第二個會話中的操作
MariaDB [hidb]> select * from students;   ---發(fā)現(xiàn)可以讀此表
MariaDB [hidb]> insert into students values(1,'yangguo',18,'M','anranxiaohun');   ---但不能寫命咐，必須解除鎖才可以

4醋奠、事物

• 事務：一組原子性的SQL查詢窜司、或者是一個或多個SQL語句組成的獨立工作單元竞膳；
• 事務日志：事物在內存中執(zhí)行時先保存到事物日志中孕蝉，然后再同步到數(shù)據(jù)文件中斯议，事物日志也在磁盤上坯临，只不過事物日志在記錄的時候是順序寫而不是隨機看靠，所以寫的速度比較快衷笋，事物之所以能進行回滾操作就是因為事先已經將老版本記錄到事物日志中了辟宗。

事物日志的參數(shù)設置
innodb_log_files_in_group  ---指明一組事物內有幾個事物日志文件，為了冗余容客，一般一組內要設置兩個
innodb_log_group_home_dir ---事物日志存儲的家目錄
innodb_log_file_size    ---每個日志文件的大小缩挑，默認是5M
innodb_mirrored_log_groups  ---日志組的鏡像有幾個谨湘，設置成1表示只有一組日志文件
MariaDB [hidb]> show variables \G ---查看mysql的變量參數(shù)紧阔，這些參數(shù)都是可以設定的擅耽，用于定義mysql的工作特性
MariaDB [hidb]> show global variables \G    ----查看全局的變量的所有參數(shù)，是默認值乃沙，對所有連接的會話都有效阳掐，注意后面沒有分號缭保，否則會報錯
MariaDB [hidb]> show session variables \G    ---查看當前會話的所有變量參數(shù)诸老，只對當前連接的會話有效
MariaDB [hidb]> show session variables like 'innodb_file_per_table';    ---可以過濾的查看設定的變量參數(shù)
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
1 row in set (0.00 sec)

MariaDB [hidb]> select @@global.innodb_file_per_table;   ---也可以這樣查看
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              1 |
+--------------------------------+
1 row in set (0.00 sec)
MariaDB [hidb]> set @@global.innodb_file_per_table=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hidb]> select @@global.innodb_file_per_table;
+--------------------------------+
| @@global.innodb_file_per_table |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

注意：session表示的是只對當前連接的會話有效，通過復制創(chuàng)建的會話屬于另外一個連接厘肮，所以通過設置session變量參數(shù)耍属，只對當前連接的有效厚骗，對復制的會話是無效的，但global是屬于全局變量提揍，通過設置global變量參數(shù)對全局都有效，對復制的會話也是有效的，這樣的設置只是在運行時修改杉武，重啟后就會失效，要想永久有效就要寫到配置文件里祈搜，就像剛剛開啟mariadb時設置的變量參數(shù)一樣，寫到配置文件里就可以了

• ACID測試：
  A：AUTOMICITY蘸秘，原子性；整個事務中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾粘舟；
  C：CONSISTENCY霞揉，一致性适秩；數(shù)據(jù)庫總是應該從一個一致性狀態(tài)轉為另一個一致性狀態(tài)；
  I：ISOLATION扬跋，隔離性；一個事務所做出的操作在提交之前，是否能為其它事務可見痪宰；出于保證并發(fā)操作之目的，隔離有多種級別淮韭；
  D：DURABILITY，持久性毫蚓；事務一旦提交，其所做出的修改會永久保存元潘；
• 自動提交：單語句事務
  mysql> SELECT @@autocommit;
  +------------------------+
  | @@autocommit |
  +------------------------+
  | 1 |
  +------------------------+
  mysql> SET @@session.autocommit=0君仆；
• 手動控制事務：
  啟動：START TRANSACTION
  提交：COMMIT
  回滾：ROLLBACK
• 事務支持savepoints：
  SAVEPOINT identifier
  ROLLBACK [WORK] TO [SAVEPOINT] identifier
  RELEASE SAVEPOINT identifier
• 事務隔離級別：
  READ-UNCOMMITTED：讀未提交 --> 臟讀牲距；別人在一個會話中修改了钥庇，即使沒有提交难述，我在另外一個會話中也會看到修改的數(shù)據(jù)
  READ-COMMITTED：讀提交--> 不可重復讀胁后；別人在一個會話中修改了攀芯，只有提交了敲才，我在另外一個會話中才能看到修改的數(shù)據(jù)，但不可重復讀阻星，只能看到別人提交的數(shù)據(jù)
  REPEATABLE-READ：可重復讀 --> 幻讀更舞；別人在一個會話中修改了，也提交了原杂，你也看不到修改的數(shù)據(jù)际看，出現(xiàn)幻讀
  SERIALIZABLE：串行化兴喂；別人在一個會話中修改了，要么回滾 汗菜，要么提交，否則我在另外一個會話中會阻塞，直到別人進行回滾或者提交操作我才能看到數(shù)據(jù)
  mysql> SELECT @@session.tx_isolation;
  +----------------------------------+
  | @@session.tx_isolation |
  +----------------------------------+
  | REPEATABLE-READ |
  +----------------------------------+
• 查看InnoDB存儲引擎的狀態(tài)信息：
  SHOW ENGINE innodb STATUS;

示例1：事物回滾
MariaDB [hidb]> select @@autocommit;   ---查看自動提交功能是否打開
MariaMariaDB [hidb]> set @@session.autocommit=0;  ----關閉自動提交功能
DB [hidb]> select @@autocommit   ---查看自動提交功能是否關閉
MariaDB [hidb]> start transaction;  ---啟動一個事物
MariaDB [hidb]> insert into students values(2,'xiaolongnv',22,'F','zuoyouhubo');   ---在表中插入數(shù)據(jù)
MariaDB [hidb]> roback;   ---回滾
MariaDB [hidb]> select * from students;---發(fā)現(xiàn)又回滾到原來的捌木，前面插入的數(shù)據(jù)也沒有了
MariaDB [hidb]> insert into students values(2,'xiaolongnv',22,'F','zuoyouhubo');   ---插入數(shù)據(jù)
MariaDB [hidb]> savepoint first;   ---設置一個保存點位first
MariaDB [hidb]> rollback to first;    ---回滾到第一個保存點
MariaDB [hidb]> select * from students;   ----發(fā)現(xiàn)剛剛插入的數(shù)據(jù)還在
MariaDB [hidb]> insert into students values(3,'guojing',40,'M','xianglongshibazhang');
MariaDB [hidb]> savepoint second;
MariaDB [hidb]> rollback to sencond;   ---設置保存點后就相當于把數(shù)據(jù)保存了极舔，回滾到指定的保存點后數(shù)據(jù)就不會丟失
MariaDB [hidb]> commit;  ----提交
MariaDB [hidb]> rollback;   ---全部回滾，發(fā)現(xiàn)回滾不回去了，說明事物一旦提交就無法回滾了
MariaDB [hidb]> select * from students;
示例2：事物隔離級別
MariaDB [hidb]> select @@session.autocommit;    查看是否已經設置為手動提交事務玄柠，0表示關閉自動提交这弧，因為設置的是只對當前連接的會話有效蛋辈，通過復制打開的多個會話都是無效的
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+
MariaDB [hidb]> select @@global.autocommit;   ---對于全局的還是自動提交事務的挂洛。因為只是設置的對當前會話有效
+---------------------+
| @@global.autocommit |
+---------------------+
|                   1 |
+---------------------+
1 row in set (0.00 sec)

MariaDB [hidb]> select @@session.tx_isolation;   ---查看事物的隔離級別励堡，默認是重復可讀
MariaDB [hidb]> set @@session.tx_isolation='read-uncommitted';   ---在一個會話中設置事物隔離級別為讀未提交
用復制的方式打開另外一個會話揩慕，也設置為讀未提交
然后在兩個會話中都開啟一個事物
在其中一個會話中進行如下操作
MariaDB [hidb]> select * from students2;
+------+------------+
| id   | name       |
+------+------------+
|    1 | yangguo    |
|    2 | xiaolongnv |
|    3 | ouyangfeng |
|    4 | hongqi     |
+------+------------+
4 rows in set (0.00 sec)

MariaDB [hidb]> delete from students2 where id=3;   ---刪除一個字段
Query OK, 1 row affected (0.01 sec)

MariaDB [hidb]> select * from students2;
+------+------------+
| id   | name       |
+------+------------+
|    1 | yangguo    |
|    2 | xiaolongnv |
|    4 | hongqi     |
+------+------------+
3 rows in set (0.00 sec)
在另外一個會話中查詢發(fā)現(xiàn)也會缺少刪除的字段八堡，說明隔離級別
設置成讀未提交后溶耘，即使不提交饭望，在另外一個會話中也會看到修
改的數(shù)據(jù)斟珊，這樣會造成臟讀，也就是別人可能將數(shù)據(jù)回滾后再提
交，這時候你就會發(fā)現(xiàn)缺少的字段又回來了岛杀，不精準嗤形，會看到別
人未提交的數(shù)據(jù)

5叶组、MySQL的索引

可以參考：http://www.cnblogs.com/linhaifeng/articles/7274563.html

• 索引：索引是創(chuàng)建在表字段上的一個獨特的數(shù)據(jù)結構；
• 索引的作用：加速查詢操作酪劫；副作用：降低寫操作性能；
• 索引優(yōu)點：
  降低需要掃描的數(shù)據(jù)量挟裂，減少IO次數(shù)狐肢；
  可以幫助避免排序操作，避免使用臨時表部脚；
  幫助將隨機IO轉為順序IO想邦；
• 高性能索引策略：
  (1) 在WHERE中獨立使用列，盡量避免其參與運算委刘；
  WHERE age+2 > 32 ;
  (2) 左前綴索引：索引構建于字段的最左側的多少個字符丧没，要通過索引選擇性來評估
  索引選擇性：不重復的索引值和數(shù)據(jù)表的記錄總數(shù)的比值；
  (3) 多列索引：
  AND連接的多個查詢條件更適合使用多列索引锡移，而非多個單鍵索引呕童；
  (4) 選擇合適的索引列次序：選擇性最高的放左側；
• 索引類型：B+ TREE淆珊，HASH
  示例

[root@centos7 ~]#declare -a gender=(F M)    ---定義一個數(shù)組
[root@centos7 ~]#for i in {1..1000};do mysql -e "insert into hidb.students(name,age,gender)values('stu$i',$[RANDOM%100],'${gender[$[RANDOM%2]]}')";done   ---mysql -e可以直接在shell里實現(xiàn)對數(shù)據(jù)庫的操作夺饲，定義一個腳本，實現(xiàn)在表中增加數(shù)據(jù)
MariaDB [hidb]> explain select name from students where age>90;   ---用explain可以查看索引是否有用施符，因為age上沒有索引往声，所以此時查詢時沒有用到索引
MariaDB [hidb]> create index age on students(age);  ---創(chuàng)建一個索引
MariaDB [hidb]> show indexes from students \G; ---查看索引
MariaDB [hidb]> explain select name from students where age>90;   ---發(fā)現(xiàn)查詢時用到了索引
MariaDB [hidb]> create index age_and_name on students(age,name);   ---在兩個字段創(chuàng)建索引，并且索引名字是相同戳吝，一個字段可以有多個索引
MariaDB [hidb]> explain select name,age from students where age>(select avg(age) from students);---- select avg(age) from students表示取age的平均值

6浩销、忘記管理員密碼的解決辦法

(1) 啟動mysqld進程時，使用--skip-grant-tables和--skip-networking選項听哭；
CentOS 7：mariadb.service
CentOS 6：/etc/init.d/mysqld
(2) 通過UPDATE命令修改管理員密碼慢洋；
(3) 以正常方式啟動mysqld進程塘雳；
示例

[root@centos7 ~]#systemctl stop mariadb
[root@centos7 ~]#vim /usr/lib/systemd/system/mariadb.service 
ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
[root@centos7 ~]#systemctl daemon-reload ---加載一下
[root@centos7 ~]#systemctl start mariadb
[root@centos7 ~]#mysql
MariaDB [(none)]> update mysql.user set password=password('centos') where user='root';  ---修改user表中的root用戶的密碼
[root@centos7 ~]#systemctl stop mariadb
[root@centos7 ~]#vim /usr/lib/systemd/system/mariadb.service 
將--skip-grant-tables --skip-networking刪除
[root@centos7 ~]#systemctl daemon-reload 
[root@centos7 ~]#systemctl start mariadb
[root@centos7 ~]#mysql -uroot -pcentos ---發(fā)現(xiàn)可以登錄了

centos6的修改方法
vim /etc/my.cnf 
[mysqld]
skip-grant-tables #加上此條，
service mysqld restart
mysql #直接就可以連接到數(shù)據(jù)庫普筹，不需要輸入密碼
mysql> update user set authentication_string=password('123') where user='root'; 
#5.7版本的mysql數(shù)據(jù)庫password 字段改成authentication_string  ,password函數(shù)還是原來的password函數(shù)
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1
mysql>quit
vim /etc/my.cnf 
[mysqld]
#skip-grant-tables #將此行注釋掉
service mysqld restart
mysql -uroot -p123