DBA學習筆記

資料庫介紹：

一、什麼是資料庫？

簡單的說，資料庫（Database）就是一個存放資料的倉庫，這個倉庫是按照一定的資料結構（資料結構是指資料的組織形式或資料之間的聯系）來組織、存儲的，我們可以通過資料庫的多種方法來管理資料庫裡的資料。

關系型資料庫：

關系型資料庫模型是把複雜的資料結構歸結為簡單的二進制關系（即二維表格形式），在關系型資料庫中，對資料的操作幾乎全部建立在一個或多個關系表格上，通過對這些關聯的表哥分類、合并、連接配接或選取等運算賴實作資料的管理。

必記：

1.關系型資料庫類似于excel這種，二維表。

2.關系型資料庫使用結構化查詢語言sql語句來對資料程序存取。

3.關系型資料庫代表作品mysql，oracle資料庫。

非關系型資料（Nosql）庫種類：

1.鍵值（key-value）存儲資料庫

鍵值資料庫就類型傳統語言中使用的哈希表，可以通過key來添加、查詢或者删除資料，因為使用key主鍵通路，是以會獲得很高的性能及擴充性，

鍵值（key-value）資料庫主要是使用一個哈希表，這個表中有一個特定的鍵和一個指針指向特定的資料。key/value模型對于IT系統來說的優勢在于簡答、易部署、高并發。

典型産品：memcached、redis、memcacheDB、Berkeley DB

2.列存儲（Column-oriented）資料庫

列存儲資料庫将資料儲存在列族（column family）中，一個列族存儲經常被一起查詢的相關資料。

這部分資料庫通常是用來應對分布式存儲的海量資料，鍵仍然存在，但是他們的特點是指向了多個列，這些列是由列家族來安排的。

典型産品：Cassandra，HBase

3.面向文檔（Document-Oriented）資料庫

文檔型資料庫會将資料以文檔的形式儲存，每個文檔都是自包含的資料單元，是一個系列資料項的集合，每個資料項都有一個名稱與對應的值，值即可以是簡單的資料類型，如字元串、數字和日期等，也可以是複雜的類型，如有序清單和關聯對象。資料存儲的最小機關是文檔，同一個表中存儲的文檔屬性可以是不同的，資料可以使用xml、json或者jsonb等多種形式存儲。

典型産品：MongoDB、CouchDB

4.圖形（Graph）資料庫

圖形資料庫允許我們将資料以圖形方式存儲。

典型産品：Neo4J、InfoGrid

Mysql資料庫簡介：

Mysql是一種關系型資料庫管理系統，關系型資料庫的特點是将資料儲存在不同的表中，再講這些表放入不同的資料庫中，而不是将所有資料統一放在一個大倉庫裡，這樣設計增加了mysql的讀取速度，靈活性可管理性也得到了很大提高，通路以及管理MySQL資料庫的最常用标準化語言為SQL結構化查詢語言。

mysql優勢：

1.mysql性能卓越，服務穩定，很少出現異常當機

2.mysql開放源代碼且無版權制約，自主性及使用成本低

3.mysql曆史悠久，社群及使用者非常活躍，遇到問題，可以尋求幫助。

4.mysql軟體體積小，安裝簡單，并且易于維護，安裝及維護成本低

5.mysql品牌口碑效應，使得企業無需考慮就直接用之，lamp，lemp流行架構

6.mysql支援多種作業系統，提供多種API接口，支援多種開發語言，特别對流行的PHP語言有很好的支援

mysql 參數優化：

innodb_buffer_pool_size

1、單執行個體性能

2、執行個體需求

3、執行個體标準化

innodb_buffer_pool_instances

1、将innodb_buffer_pool劃分為不同的小的buffer pool 這樣就更好管理instance

2、每個instance獨立的LRU、FLUSH、FREE

3、獨立的mutex控制

優點：降低鎖的争用，降低LRU清單的查詢，提高并發。

innodb_log_file_size

設定redo 官方版本不能超過4GB

innodb_log_buffer_size

1、先寫入innodb_log_buffer

2、buffer寫滿或事務送出，重新整理資料

3、大事務頻繁，增加innodb_log_buffer_size大小

減少事務刷盤的頻率和次數

innodb_thread_concurrency

1、innodb_thread_concurrency=0 innodb内部自己控制

–kernel_mutex競争

– CPU上下文切換

2、innodb_thread_concurrency 設定為CPU的核心數

innodb_io_capacity

1、innodb每秒背景程序處理IO操作的資料頁上限

2、innodb_buffer_pool_size總的IO處理能力上限

3、innodb_buffer_pool_instances分割成多個記憶體塊時，每個記憶體塊的IO處理能力為：innodb_io_capacity/innodb_buffer_pool_instances=xxxx

innodb_max_dirty_pages_pct

1、innodb從innodb buffer中重新整理髒頁的比例

2、重新整理髒頁，産生checkpoint

3、髒頁重新整理innodb_max_dirty_pages_pct*innodb_io_capacity

innodb_flush_method

1、O_DSYNC：使用O_SYNC打開和重新整理log檔案，使用fsync()重新整理資料檔案

2、O_DIRECT：使用O_DIRECT打開資料檔案，使用fsync()重新整理日志檔案和資料檔案

在raid裝置上，為了避免資料被innodb_buffer和raid多次cache 設定O_DIRECT方式。

innodb_file_per_table

1、不同的表空間可以靈活設定資料目錄的位址

2、避免共享表空間産生的IO競争

innodb_flush_log_at_trx_commint

0：每秒将log buffer的内容寫事務日志并且重新整理到磁盤

1：每個事務送出後，将log_buffer的内容寫事務日志并資料磁盤

2：每個事務送出，将log_buffer内容寫事務日志，但不進行資料刷盤

sync_binlog

1、重新整理binlog的數目

2、雙1模式，即：innodb_flush_log_at_trx_commit=1 ,sync_binlog=1 ,這樣主備的資料是一緻的，不會丢失資料。

安裝mysql

修改配置檔案：

[client] 針對用戶端配置

port = 3306

socket = /tmp/mysql3306.sock

[mysqld] 針對于mysql- server配置

port = 3306

socket = /tmp/mysql3306.sock

（啟動的時候自動生成）

datadir = /home/mysql3306/mysql3306

資料目錄

innodb_buffer_pool_size = 1040M

Innodb緩存池大小

建議設定實體記憶體75%左右

32GB 建議設定 20GB

64設定50

初始化資料庫（初始化mysql的一些系統表和系統檔案）

/usr/local/mysql/scripts/mysql_install_db

–basedir=/usr/local/mysql

安裝目錄

–defaults-file=/etc/my.cnf

配置檔案目錄

–datadir=/home/mysql3306/mysql3306 -

資料目錄

-user=mysql

指定使用者

CS client server

服務端 mysqld

用戶端 mysql

/usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock 連接配接mysql

/usr/local/mysql/bin/mysql --help mysql幫助手冊

-S 指定socket檔案位置

-u 登入mysql使用者（root）

-p 密碼（空）

預設讀取/etc/my.cnf

mysql權限設定

檢視使用者，主機名，密碼

mysql> select user,host,password from mysql.user;

删除空使用者

mysql> delete from mysql.user where user=’’;

Query OK, 2 rows affected (0.01 sec)

列出使用者表

mysql> desc mysql.user;

重新整理權限

mysql> flush privileges;

給使用者賦權

mysql> grant all on . to [email protected]’%’ identified by ‘root’;

mysql> grant all on . to [email protected]‘localhost’ identified by ‘root’;

賦權本地登入權限

mysql> grant all on . to [email protected]’%’ identified by ‘root’ with grant option;

grant all on . to [email protected]’%’ identified by ‘root’ with grant option;

mysql權限報錯

ERROR 2003

沒有連接配接到mysql

先檢查mysqld有沒有該使用者

1.檢查網絡

2.檢查防火牆

3.檢查mysqld是否啟動

4.檢查權限

關閉資料庫

pkill mysql（慎用）

最好用這樣關閉資料庫

/usr/local/mysql/bin/mysqladmin -uroot -proot shutdown

Mysql環境變量設定

vim /etc/profile

export MYSQL_HOME=/usr/local/mysql

PATH= P A T H : PATH: PATH:MYSQL_HOME/bin

Mysql注冊開機啟動

vim /etc/rc.local

/usr/local/mysql/bin/mysqld_safe --user=mysql &

如果密碼丢失

mysqld_safe --skip-grant-tables --user=mysql &

然後更改密碼

更新資料庫密碼為123456 然後正常重新開機即可

mysql> update mysql.user set password=password(‘123456’) where user=‘root’;

Mysql 5.7的安裝

1.kill掉mysql

2.清空安裝目錄

rm -fr /usr/local/mysql

rm -fr /home/mysql3306/mysql3306

1 row in set (0.00 sec)

建立一個預設為gbk的字元集

mysql> create database oldboy_gbk DEFAULT CHARACTER SET gbk COLLATE gbk_chinese_ci;

檢視建立的gbk字元集

mysql> show create database oldboy_gbk\G;

*************************** 1. row ***************************

Database: oldboy_gbk

Create Database: CREATE DATABASE

oldboy_gbk

1 row in set (0.00 sec)

建立一個預設為utf8字元集的資料庫

mysql> create database oldboy_utf8 CHARACTER SET utf8 COLLATE utf8_general_ci;

Query OK, 1 row affected (0.01 sec)

檢視建立的utf8字元集的資料庫

mysql> show create database oldboy_utf8\G;

*************************** 1. row ***************************

Database: oldboy_utf8

Create Database: CREATE DATABASE

oldboy_utf8

1 row in set (0.00 sec)

提示：字元集的不一緻是資料庫亂碼的罪魁禍首，有關字元集的問題件後文。

提示：如果編譯的時候指定了特定的字元集，則，以後建立對應的資料庫就不需要指定字元集了。

-DDEFAULT_CHARSET=utf8

-DDEFAULT_COLLATION=utf8_general_ci

企業裡怎麼建立資料庫：

1.根據開發的程式确定字元集（建議UTF8）

2.編譯的時候指定字元集。

3. 編譯的時候沒有指定字元集或者指定了和程式不同的字元集，如何解決

指定字元集建立資料庫即可

資料庫要支援建立庫的字元集,列如：

-DEXTRA_CHARSETS=gbk,gb2312,utf8,ascii

顯示資料庫：

指令 show databases；

檢視目前的資料庫：

mysql> select database();

删除資料庫：

指令：drop database <資料庫名>

mysql> drop database test2;

檢視目前資料庫的版本

mysql> select version();

±-----------+

| version() |

±-----------+

| 5.7.21-log |

±-----------+

1 row in set (0.00 sec)

檢視目前的使用者

mysql> select user();

檢視目前的時間

mysql> select now();

±--------------------+

| now() |

±--------------------+

| 2018-04-07 07:27:31 |

±--------------------+

1 row in set (0.00 sec)

删除mysql系統多餘賬号

文法：

drop user “user”@“主機名”

例如：

mysql> drop user “rep”@“192.168.10.168”;

使用者授權

使用幫助

mysql> help grant

官方執行個體：

CREATE USER ‘jeffrey’@‘localhost’ IDENTIFIED BY ‘password’;

GRANT ALL ON db1.* TO ‘jeffrey’@‘localhost’;

GRANT SELECT ON db2.invoice TO ‘jeffrey’@‘localhost’;

ALTER USER ‘jeffrey’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;

CREATE USER ‘shenliang’@‘localhost’ IDENTIFIED BY ‘123456’;

GRANT ALL ON db1.* TO ‘shenliang’@‘localhost’;

GRANT SELECT ON db2.invoice TO ‘shenliang’@‘localhost’;

GRANT USAGE ON . TO ‘shenliang’@‘localhost’ WITH MAX_QUERIES_PER_HOUR 90;

運維常用授權

GRANT ALL ON db1.* TO ‘shenliang’@‘localhost’ IDENTIFIED BY ‘123456’;

grant指令簡單文法：

grant all privileges on dbname.* to [email protected] identified by ‘password’;

清單說明：

grant all privileges on dbname.* to [email protected] identified by ‘password’

授權指令對應權限目标：庫和表使用者名和用戶端主機使用者密碼

檢視使用者權限：

mysql> show grants for [email protected]‘localhost’;

±------------------------------------------------------------+

| Grants for [email protected] |

±------------------------------------------------------------+

| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |

| GRANT ALL PRIVILEGES ON

test

.* TO ‘shenliang’@‘localhost’ |

±------------------------------------------------------------+

注意：USAGE 表示隻建立了使用者，不能登入，不能操作

二、使用create 和grant配合法

mysql> help create user

1.首先建立使用者username及密碼password，授權主機localhost

create user ‘username’@‘localhost’ identified by ‘123456’;

2.然後授權localhost主機上通過使用者username管理dbname 資料庫的所有權限，無需密碼

grant all ON dbname.* to ‘username’@‘localhost’;

權限分類

收回權限指令幫助

mysql> help revoke

mysql> show grants for [email protected]‘localhost’;

±------------------------------------------------------------+

| Grants for [email protected] |

±------------------------------------------------------------+

| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |

| GRANT ALL PRIVILEGES ON

test

.* TO ‘shenliang’@‘localhost’ |

±------------------------------------------------------------+

mysql> revoke insert ON test.* from ‘shenliang’@‘localhost’;

Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show grants for [email protected]‘localhost’;

±----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

| Grants for [email protected] |

| GRANT USAGE ON . TO ‘shenliang’@‘localhost’ |

| GRANT SELECT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, EVENT, TRIGGER ON

test

.* TO ‘shenliang’@‘localhost’ |

在linux指令行使用

mysql -uroot -p123 -e “show grants for [email protected]‘localhost’;”|grep -i grant|tail -1|tr ‘,’ ‘\n’ > all.txt

[[email protected] ~]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -u root -p123 -e “show grants for [email protected]‘localhost’;”|grep -i grant|tail -1|tr ‘,’ ‘\n’ > all.txt

權限清單

1	SELECT

 2	INSERT

 3	UPDATE

 4	DELETE

 5	CREATE

 6	DROP

 7	REFERENCES

 8	INDEX

 9	ALTER

10	CREATE TEMPORARY TABLES

11	LOCK TABLES

12	EXECUTE

13	CREATE VIEW

14	SHOW VIEW

15	CREATE ROUTINE

16	ALTER ROUTINE

17	EVENT

18	TRIGGER

重點：

企業生産環境如何授權：

1.對于部落格，CMS等産品的資料庫授權

對于web連接配接使用者授權盡量采取最小化原則，在安裝期間除了select，insert，update，delete 4個權限外，還需要create，drop等比較危險的權限。

mysql> grant select,insert,update,delete,create,drop on db1.* to ‘shenliang’@‘localhost’ identified by ‘123456’;

注意：正常情況下授權select,insert,update,delete 這4個即可

安裝完成之後，需要使用revoke收回create，drop權限

revoke create,drop on db1.* from ‘shenliang’@‘localhost’;

檢視使用者授權：

mysql> show grants for [email protected]‘localhost’;

建資料庫：

mysql> create database oldboy;

建表：

mysql> create table <表名> (

<字段名1><類型>，

…

<字段名n><類型n>

);

建表語句，執行個體：

mysql> create table student (

-> id int(4) not null,

-> name char(20) not null,

-> age tinyint(2) NOT NULL default '0',

-> dept varchar(16) default NULL

-> );

注意：int 整型整數 char 字元類型 tinyint 比較小的整型整數 varchar 變長的字元類型

mysql表的字段類型：

1.數字類型

列類型需要的存儲量

TINYINT 1位元組

SMALLINT 2位元組

MEDIUMINT 3個位元組

INT 4個位元組

INTEGER 4個位元組

BIGINT 8個位元組

FLOAT(X) 4如果x<=24 或8 如果25<=x<=53

FLOAT 4個位元組

DOUBLE 8個位元組

DOUBLE PRECISION 8個位元組

REAL 8個位元組

DECIMAL(M,D) M位元組（D+2，如果M<D）

NUMERIC(M,D) M位元組（D+2,如果M<D）

2.日期和時間類型，mysql以YYYY-MM-DD格式來顯示date值，但是允許你使用字元串或數字把值賦給date列

列類型需要的存儲量

DATE 3個位元組

DATATIME 8個位元組

TIMESTAMP 4個位元組

TIME 3個位元組

YEAR 1個位元組

3.串類型

列類型需要的存儲量

CHAR(M) M位元組，1<=M<=255

VARCHAR(M) L+1位元組，在此L<=M和1<=M<=255

TINYBLOB,TINYYEXT L+1位元組，在此L<2^8

BLOB,TEXT L+2位元組，在此L<2^16

MEDIUMBLOB,MEDIUMTEXT L+2位元組，在此L<2^24

LONGBLOB,LONGTEXT L+4位元組，在此L<2^32

ENUM(‘value1’,‘value2’,…) 1或2個位元組，取決于枚舉值的資料（最大值65535）

SET(‘value1’,‘value2’,…) 1,2,3,4或8個位元組，取決于集合成員的數量（最多64個成員）

1.INT(M)型：正常大小整數類型

2.CHAR(M)型：定長字元串類型，當存儲時，總是用空格填滿右邊到指定的長度

3.VARCHAR型：邊長的字元串類型

檢視建表結構：

mysql> desc student;

±------±------------±-----±----±--------±------+

±------±------------±-----±----±--------±------+

| id | int(4) | NO | | NULL | |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±------+

另外一種方法（不常用）

mysql> show columns from student;

±------±------------±-----±----±--------±------+

±------±------------±-----±----±--------±------+

| id | int(4) | NO | | NULL | |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±------+

顯示表資訊

mysql> show create table student\G;

mysql 索引

為表的字段建立索引：

索引就像書的目錄一樣，如果在字段上建立索引，那麼索引列為查詢條件時可以加快查詢資料的速度。

1.建立主鍵索引：

查詢資料庫，按主鍵索引查詢是最快的，每個表隻能有一個主鍵列，但是可以有多個索引列，主鍵列要求列的所有内容必須唯一，而索引列不要求内容必須唯一。

無論建立主鍵索引還是普通索引，都要在表的對應列上建立，可以對單列建立索引，也可以對多列建立索引。

建立索引的方法：

1.在建表時，可以增加建立主鍵索引的語句如下：

mysql>create table student (

id int(4) not null AUTO_INCREMENT,

 name char(20) not null,

 age tinyint(2) NOT NULL default '0',

 dept varchar(16) default NULL,

 primary key(id),

 KEY index_name(name)

   );

提示：

1.primary key(id) 主鍵

2.KEY index_name(name) name字段普通索引

優化：在唯一值多的列上建索引查詢效率更高

測試案列：

mysql> create table student (

->      id int(4) not null AUTO_INCREMENT,

->      name char(20) not null,

->      age tinyint(2) NOT NULL default '0',

->      dept varchar(16) default NULL,

->      primary key(id),

->      KEY index_name(name)

->        );

Query OK, 0 rows affected (0.04 sec)

mysql> desc student; ±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

2.建表後通過alter 指令增加主鍵索引（不建議）

檢視幫助alter

mysql> help alter table

mysql> create table student ( id int(4) not null,

name char(20) not null,

      age tinyint(2) NOT NULL default '0',

      dept varchar(16) default NULL,

      KEY index_name(name)

        );

mysql> desc student;

±------±------------±-----±----±--------±------+

±------±------------±-----±----±--------±------+

| id | int(4) | NO | | NULL | |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±------+

添加主鍵：

mysql> alter table student change id id int primary key auto_increment;

mysql> desc student;

±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

成功！

添加索引：

mysql> alter table student add index index_dept(dept);

mysql> desc student;

±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

删除索引：

mysql> alter table student drop index index_dept;

mysql> desc student;

±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

另外一種删除索引：

mysql> drop index ind_name_dept on student;

對字段的前n個字元建立普通索引

當遇到表中比較大的列時，列内容的前n個字元在所有内容中已經接近唯一時，這時可以對列的前n個字元建立索引，而無需對整個列建立索引，這樣可以節省建立索引占用的系統空間，以及降低讀取和更新維護索引消耗的系統資源。

對字段的前n個字元建立普通索引文法：

mysql> create index index_dept on student(dept(8)); 對dept前8個字元建立索引

mysql> desc student;

±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

檢視詳細索引資訊：

mysql> show index from student\G;

*************************** 1. row ***************************

Table: student

Non_unique: 0

Key_name: PRIMARY

Seq_in_index: 1

Column_name: id

Collation: A

Cardinality: 0

Sub_part: NULL

   Packed: NULL

     Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 2. row ***************************

Table: student

Non_unique: 1

Key_name: index_name

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

   Packed: NULL

     Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 3. row ***************************

Table: student

Non_unique: 1

Key_name: index_dept

Seq_in_index: 1

Column_name: dept

Collation: A

Cardinality: 0

Sub_part: 8

   Packed: NULL

     Null: YES

Index_type: BTREE

Comment:

Index_comment:

3 rows in set (0.01 sec)

為表的多個字段建立聯合索引：

如果查詢資料的條件是多列時，我們可以為多個查詢的列建立聯合索引，甚至，可以為多列的前n個字元建立聯合索引

執行個體：

mysql> create index ind_name_dept on student(name,dept);

mysql> show index from student\G;

*************************** 4. row ***************************

Table: student

Non_unique: 1

Key_name: ind_name_dept

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: NULL

   Packed: NULL

     Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 5. row ***************************

Table: student

Non_unique: 1

Key_name: ind_name_dept

Seq_in_index: 2

Column_name: dept

Collation: A

Cardinality: 0

Sub_part: NULL

   Packed: NULL

     Null: YES

Index_type: BTREE

Comment:

Index_comment:

對前n個字元建立聯合索引：

mysql> create index ind_name_dept on student(name(8),dept(10));

mysql> show index from student\G;

*************************** 4. row ***************************

Table: student

Non_unique: 1

Key_name: ind_name_dept

Seq_in_index: 1

Column_name: name

Collation: A

Cardinality: 0

Sub_part: 8

   Packed: NULL

     Null:

Index_type: BTREE

Comment:

Index_comment:

*************************** 5. row ***************************

Table: student

Non_unique: 1

Key_name: ind_name_dept

Seq_in_index: 2

Column_name: dept

Collation: A

Cardinality: 0

Sub_part: 10

   Packed: NULL

     Null: YES

Index_type: BTREE

Comment:

Index_comment:

生産環境建立聯合索引：

提示：按條件查詢資料時，聯合索引是有字首生效特性的

index(a,b,c)僅a,ab,abc三個查詢條件列可以走索引，b，bc，ac，c等無法使用索引。

建立唯一索引（非主鍵）

說明：一般用來限制這個表的内容的，插入時候必須要唯一。

mysql> create unique index uni_ind_name on student(name);

Query OK, 0 rows affected (0.02 sec)

Records: 0 Duplicates: 0 Warnings: 0

mysql> desc student;

±------±------------±-----±----±--------±---------------+

±------±------------±-----±----±--------±---------------+

| id | int(11) | NO | PRI | NULL | auto_increment |

| age | tinyint(2) | NO | | 0 | |

±------±------------±-----±----±--------±---------------+

索引列的建立及生效條件

索引的缺點

1.索引占用系統空間

2.更新資料庫時還需要維護索引資料

是以：數十幾百行的小表上無需建立索引，寫頻繁，更新頻繁，讀少的表要少建立索引。

什麼時候需要建立索引

select user,host from mysql.user where host=…

索引一定要建立在where後的條件列上，而不是select後的選擇資料的列，另外我們要盡量選擇在唯一值多的大表上建立索引

mysql 插入資料

指令文法：

insert into <表名> [(<字段名1>[,<字段名n>])] values（值1）[,(值n)]

建立一個簡單的測試表test

mysql> create table

test

(

id int(4) NOT NULL AUTO_INCREMENT, 

name char(20) NOT NULL,

PRIMARY KEY(id) 

);

插入資料

mysql> insert into test(id,name) values(1,‘oldboy’);

檢視資料：

mysql> select * from test;

±—±-------+

| id | name |

±—±-------+

| 1 | oldboy |

±—±-------+

由于id建表時候為自增長，是以id可以忽略不寫。仍然按照序号自動增加。是以隻需要插入name值即可

mysql> insert into test(name) values(‘shenliang’);

Query OK, 1 row affected (0.01 sec)

mysql> select * from test;

±—±----------+

| id | name |

±—±----------+

| 1 | oldboy |

| 2 | shenliang |

±—±----------+

3.如果不指定列，就要按規矩為每列都插入恰當的值。

insert into test values(3,‘xiaoming’);

4.批量插入資料方法，提升效率

insert into test values(4,‘xiaohong’),(5,‘kaka’);

删除資料：

mysql> delete from test;

一次性插入多條，提升效率，優化方案之一。

mysql> insert into test values(1,‘xiaogang’),(2,‘shenliang’),(3,‘inco’),(4,‘xiaohong’),(5,‘kaka’);

備份資料庫：

[[email protected] ~]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p123 -B oldboy > /opt/oldboy.sql

檢視備份資訊

[[email protected] opt]# grep -E -v “#|/|^$|–” /opt/oldboy.sql

USE

oldboy

;

DROP TABLE IF EXISTS

student

;

CREATE TABLE

student

(

id

int(11) NOT NULL AUTO_INCREMENT,

name

char(20) COLLATE utf8mb4_unicode_ci NOT NULL,

age

tinyint(2) NOT NULL DEFAULT ‘0’,

dept

varchar(16) COLLATE utf8mb4_unicode_ci DEFAULT NULL,

PRIMARY KEY (

id

UNIQUE KEY

uni_ind_name

(

name

KEY

index_dept

(

dept

(8)),

KEY

ind_name_dept

(

name

(8),

dept

(10))

) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES

student

WRITE;

UNLOCK TABLES;

DROP TABLE IF EXISTS

test

;

CREATE TABLE

test

(

id

int(4) NOT NULL AUTO_INCREMENT,

name

char(20) COLLATE utf8mb4_unicode_ci NOT NULL,

PRIMARY KEY (

id

)

) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

LOCK TABLES

test

WRITE;

INSERT INTO

test

VALUES (1,‘xiaogang’),(2,‘shenliang’),(3,‘inco’),(4,‘xiaohong’),(5,‘kaka’);

UNLOCK TABLES;

mysql 查詢資料

查詢表的所有資料行：

1.指令文法：

select <字段1，字段2，。。。> from <表名> where <表達式>

其中，select，from ，where 是不能随便改的，是關鍵字，支援大小寫

優化：不要用select * 盡量用select id，name

mysql> select id,name from test limit 2; 查詢到2條資料

±—±----------+

| id | name |

±—±----------+

| 1 | xiaogang |

| 2 | shenliang |

±—±----------+

mysql> select id,name from test limit 0,3; 從0行開始查，查3行記錄

±—±----------+

| id | name |

±—±----------+

| 1 | xiaogang |

| 2 | shenliang |

| 3 | inco |

±—±----------+

根據條件進行查詢：

mysql> select id,name from test where id=3;

±—±-----+

| id | name |

±—±-----+

| 3 | inco |

±—±-----+

mysql> select id,name from test where name=‘shenliang’; 字元串需要加引号

±—±----------+

| id | name |

±—±----------+

| 2 | shenliang |

±—±----------+

mysql> select id,name from test where name=‘shenliang’ and id=2; 多條件查詢

±—±----------+

| id | name |

±—±----------+

| 2 | shenliang |

±—±----------+

mysql> select id,name from test where name=‘shenliang’ or id=5;

±—±----------+

| id | name |

±—±----------+

| 2 | shenliang |

| 5 | kaka |

±—±----------+

mysql> select id,name from test where id>2 and id<4;

±—±-----+

| id | name |

±—±-----+

| 3 | inco |

±—±-----+

其他查詢功能：

1、排序功能

mysql> select id,name from test order by id asc;

±—±----------+

| id | name |

±—±----------+

| 1 | xiaogang |

| 2 | shenliang |

| 3 | inco |

| 4 | xiaohong |

| 5 | kaka |

±—±----------+

mysql> select id,name from test order by id desc;

±—±----------+

| id | name |

±—±----------+

| 5 | kaka |

| 4 | xiaohong |

| 3 | inco |

| 2 | shenliang |

| 1 | xiaogang |

±—±----------+

多表查詢：

建立一個測試表：

mysql> create table student (

-> Sno int(10) NOT NULL COMMENT '學号',

-> Sname varchar(16) NOT NULL COMMENT '姓名',

-> Ssex char(2) NOT NULL COMMENT '性别',

-> Sage tinyint(2) NOT NULL default '0' COMMENT '學生年齡',

-> Sdept varchar(16) default NULL COMMENT '學生所在系别',

-> PRIMARY KEY (Sno),

-> key index_Sname (Sname)

-> );

mysql> create table course (

Cno int(10) NOT NULL COMMENT '課程号', 

Cname varchar(64) NOT NULL COMMENT '課程名', 

Ccredit tinyint(2) NOT NULL COMMENT '學分', 

PRIMARY KEY (Cno) 

);

mysql> create table SC (
SCid int(12) NOT NULL auto_increment COMMENT '主鍵',
Cno int(10) NOT NULL COMMENT '課程号',
Sno int(10) NOT NULL COMMENT '學号',
Grade tinyint(2) NOT NULL COMMENT '學習成績',
PRIMARY KEY (SCid)
);

插入學生表資料：

insert into student values(0001,‘宏志’,‘男’,30,‘計算機網絡’),(0002,‘王碩’,‘男’,30,‘computer application’),(0003,‘oldboy’,‘男’,28,‘物流管理’),(0004,‘脈動’,‘男’,29,‘computer application’),(0005,‘oldgirl’,‘女’,26,‘計算機科學與技術’),(0006,‘瑩瑩’,‘女’,22,‘護士’);

插入課程表資料：

insert into course values(1001,‘Linux中進階運維’,3),(1002,‘Linux進階架構師’,5),(1003,‘mysql進階DBA’,4),(1004,‘python運維開發’,4),(1005,‘java web 開發’,3);

插入選課表

insert into SC(Sno,Cno,Grade) values(0001,1004,4),(0001,1002,3),(0001,1003,1),(0001,1004,6);

insert into SC(Sno,Cno,Grade) values(0002,1001,3),(0002,1002,2),(0002,1003,2),(0002,1004,8);

insert into SC(Sno,Cno,Grade) values(0003,1004,4),(0003,1002,4),(0003,1003,2),(0003,1004,8);

insert into SC(Sno,Cno,Grade) values(0004,1001,1),(0004,1002,1),(0004,1003,2),(0004,1004,3),(0005,1001,5),(0005,1002,3),(0005,1003,2),(0005,1004,9);

mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SSC.Sno and course.Cno=SC.Cno;

±----±--------±---------------------±------+

±----±--------±---------------------±------+

| 1 | 宏志 | python運維開發 | 4 |

| 1 | 宏志 | Linux進階架構師 | 3 |

| 1 | 宏志 | mysql進階DBA | 1 |

| 1 | 宏志 | python運維開發 | 6 |

| 2 | 王碩 | Linux中進階運維 | 3 |

| 2 | 王碩 | Linux進階架構師 | 2 |

| 2 | 王碩 | mysql進階DBA | 2 |

| 2 | 王碩 | python運維開發 | 8 |

| 3 | oldboy | python運維開發 | 4 |

| 3 | oldboy | Linux進階架構師 | 4 |

| 3 | oldboy | mysql進階DBA | 2 |

| 3 | oldboy | python運維開發 | 8 |

| 4 | 脈動 | Linux中進階運維 | 1 |

| 4 | 脈動 | Linux進階架構師 | 1 |

| 4 | 脈動 | mysql進階DBA | 2 |

| 4 | 脈動 | python運維開發 | 3 |

| 5 | oldgirl | Linux中進階運維 | 5 |

| 5 | oldgirl | Linux進階架構師 | 3 |

| 5 | oldgirl | mysql進階DBA | 2 |

| 5 | oldgirl | python運維開發 | 9 |

±----±--------±---------------------±------+

mysql> select student.Sno,student.Sname,course.Cname,SC.Grade from student,course,SC where student.Sno=SC.Sno and course.Cno=SC.Cno order by Sname; 進行排序

檢視執行計劃：

mysql> explain select * from test where name=‘shenliang’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test

partitions: NULL

type: ALL

possible_keys: NULL 可能走索引：null 沒可能

key: NULL 索引：null 沒有

key_len: NULL

ref: NULL

rows: 5 掃描了5行

filtered: 20.00

Extra: Using where

建立索引：

用來判斷是否走索引

explain

mysql> create index index_name on test(name);

mysql> select * from test where name=‘shenliang’;

±—±----------+

| id | name |

±—±----------+

| 2 | shenliang |

±—±----------+

1 row in set (0.01 sec)

mysql> explain select * from test where name=‘shenliang’\G

*************************** 1. row ***************************

id: 1

select_type: SIMPLE

table: test

partitions: NULL

type: ref

possible_keys: index_name

key: index_name

key_len: 80

ref: const

rows: 1

filtered: 100.00

Extra: Using index

說明成功了，索引已經生效了。

修改表中的資料：

1、修改表中指定條件固定列的資料

指令文法： update 表名 set 字段=新值，。。。 where條件（一定要加條件）

修改指定的行字段内容

檢視要修改的表

mysql> select * from test;

±—±----------+

| id | name |

±—±----------+

| 3 | inco |

mysql> update test set name=‘gongli’ where id=3;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1 Changed: 1 Warnings: 0

mysql> select * from test;

±—±----------+

| id | name |

±—±----------+

| 3 | gongli |

| 5 | kaka |

| 2 | shenliang |

| 1 | xiaogang |

| 4 | xiaohong |

±—±----------+

mysql 防止誤操作：

登入加 -U

[[email protected] mysql3306]# /usr/local/mysql/bin/mysql -S /tmp/mysql3306.sock -u root -p123 -U

測試不加where 不可以執行：

mysql> update test set name=‘liming’;

ERROR 1175 (HY000): You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column

可以用别名方式：

[[email protected] mysql3306]# alias mysql=‘mysql -U’

還可以寫入系統變量中

删除表中的資料：

指令用法：

delete from 表名 where 表達式

實踐：删除表test中編号為1的記錄

mysql> delete from test where id=1;

删除id大于3的記錄：

mysql> delete from test where id>3;

全部清空表中資料（慎用）：

mysql> truncate test;

注意：truncate 和 delete 差別

1.truncate 速度更快

2.truncate簡單了解為實體删除，delete為邏輯删除。

增删改表的字段：

指令文法：

alter table 表名 add 字段類型其他

測試表資料,執行個體在test表中添加字段sex，age，qq 類型分别為char(4),int(4),varchar(15)

mysql> alter table test add sex char(4);

mysql> alter table test add age int(4) after name; 插入年齡字段，并且在name後面使用after

mysql> select * from test;

±—±----------±-----±-----+

| id | name | age | sex |

±—±----------±-----±-----+

±—±----------±-----±-----+

mysql> alter table test add qq varchar(15) first; 第一個

mysql> select * from test;

±-----±—±----------±-----±-----+

| qq | id | name | age | sex |

±-----±—±----------±-----±-----+

±-----±—±----------±-----±-----+

增加多個字段：

mysql> alter table test add mobile varchar(32) default NULL COMMENT ‘mobile’,add addr varchar(255) default NULL COMMENT ‘位址’;

改變字段：

修改字段類型：

mysql> alter table test modify age char(4) after name;

mysql> desc test;

±-------±-------------±-----±----±--------±---------------+

±-------±-------------±-----±----±--------±---------------+

| qq | varchar(15) | YES | | NULL | |

| id | int(4) | NO | PRI | NULL | auto_increment |

| age | char(4) | YES | | NULL | |

| sex | char(4) | YES | | NULL | |

±-------±-------------±-----±----±--------±---------------+

rename 更改表名：

mysql> rename table test to test1;

mysql 插入中文資料亂碼

set name utf-8

mysql不亂碼的5種方式：

（1）登入mysql，先做 set names latinl 然後再執行更新語句或執行語句檔案

set names latinl；

source test.sql

（2）在sql檔案中指定set names latinl；然後登入mysql

mysql> source test.sql

（3）在sql檔案中指定set names latinl；然後通過mysql 指令導入資料

mysql -uroot -p’oldboy123’ oldboy < test.sql

mysql -uroot -p’olgboy123’ oldboy -e “set names latinl;select * from oldboy.test”

（4）通過指定mysql指令的字元集參數實作–default-character-set=latinl

cat test.sql

insert into student values(9.zhangsan);

mysql -uroot -p’oldboy123’ --default-character-set=laninl oldboy < test.sql

（5）在配置檔案裡設定用戶端及伺服器相關參數

[client]

default-character-set=latinl

提示：無需重新開機服務，退出重新登入就生效了，相當于set names latinl

2.更新my.cnf參數

[mysqld]

default-character-set=latinl 适合5.1及以前版本

charater-set-server=latinl 适合5.5

檢視建資料庫幫助

mysql> show character set;

CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name

[create_specification] ...

create_specification:

[DEFAULT] CHARACTER SET [=] charset_name

| [DEFAULT] COLLATE [=] collation_name

create database oldboy1 DEFALUT CHARACTER SET UTF8 COLLATE utf8_general_ci;

需要保持全部對應

linux系統字元集：

centos 6

cat /etc/sysconfig/i18n

centos 7

[[email protected] opt]# cat /etc/locale.conf

LANG=“en_US.UTF-8”

mysql檢視配置變量：

mysql> show variables\G;

檢視狀态：

mysql> show status;

mysql> show global status;

資料庫常見的字元集：

常用字元集長度說明

GBK 2 不是國際标準

UTF-8 3 中英文混合的環境，建議使用此字元集

latinl 1 mysql的預設字元集

UTF8mb4

4 UTF-8 Unicode

檢視mysql 目前字元集：

mysql> show variables like ‘character_set%’;

±-------------------------±---------------------------------+

| Variable_name | Value |

±-------------------------±---------------------------------+

| character_set_client | utf8 |

| character_set_connection | utf8 |

| character_set_database | utf8mb4 |

| character_set_filesystem | binary |

| character_set_results | utf8 |

| character_set_server | utf8mb4 |

| character_set_system | utf8 |

| character_sets_dir | /usr/local/mysql/share/charsets/ |

±-------------------------±---------------------------------+

必須保持統一字元集

資料字元集的修改步驟：

對于已有資料庫想修改字元集不能直接通過 alter database chaacter set xxxx 或者 alter database character set xxxx 這兩個指令都沒有更新已有記錄的字元集，而隻是對新建立的表或者記錄生效

已經有記錄的字元的調整，必須先将資料導出，經過修改字元集後重新導入後才可完成

修改資料庫密碼編碼

alter database [your db name ] charset [your character setting]

mysqldump 參數說明：

1.導出表結構

mysqldump -uroot -p --default-character-set=latin1 -d dbname > altable.sql

2.編輯alltable.sql将latin1改成GBK

3.確定資料庫不再更新，導出所有資料

mysqldump -uroot -p --quick --no-create-info --extended-insert --default-character-set=latin1 dbname > alldate.sql

參數說明：

-d 隻導出表結構

–quick：用于轉儲大的表，強制mysqldump從伺服器一次一行的檢索資料而不是檢索所有航，并輸出前cache到記憶體中

–no-create-info：不建立create tables語句

–extended-insert：使用包括幾個values清單的多行insert文法，這樣檔案更小，IO也小，導入資料時會非常快

–default-character-set=latin1：按照原有字元集導出資料，這樣導出的檔案中，所有中文都是可見的不會儲存成亂碼

–master-data=1

-F 重新整理binlog日志

4.打開alldate.sql将set name latin1修改成 set names gbk；

5.建庫

create database dbname default charset gbk；

6.建立表，執行alltable.sql

mysql -uroot -p dbname < alltable.sql

7.導入資料:

mysql -uroot -p dbname < alldata.sql

注意：選擇目标字元集時，要注意最好大于等于源字元集（字庫更大），否則，可能丢失不被支援的資料。

總結：latin1—>utf8

1、建庫建表的語句導出，sed批量修改為utf8

2、導出所有資料

3、修改mysql服務端和用戶端編碼為utf8

4、删除原有的庫表及語句

5、導入新的建庫及建表的語句

6、導入mysql的所有資料

mysql 備份

/usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p123 -B oldboy > /opt/oldboy.sql

備份最好加-B

對比-B多了下面建表和連接配接資料庫的作用

< CREATE DATABASE /!32312 IF NOT EXISTS/

oldboy

;

< USE

oldboy

;

mysql 多庫備份：

–master-data=1 表示列印position

[[email protected] opt]# /usr/local/mysql/bin/mysqldump -S /tmp/mysql3306.sock -uroot -p’123’ --master-data=1 oldboy

CHANGE MASTER TO MASTER_LOG_FILE=‘mysql-bin.000027’, MASTER_LOG_POS=154;

mysqldump 的關鍵參數說明：

1、-B 指定多個庫，增加建庫語句和use語句

2、–compact 去掉注釋，适合調試輸出，（生産環境禁止使用）

3、-A所有庫

4、-F 重新整理binlog日志

5、–master-data 增加binlog日志檔案檔案名及對應的位置點

6、-x是–lock-all-tables

7、-l是–lock-tables 意思是lock all tables for read 隻讀鎖表

8、-d 隻備份表結構

9、-t 隻備份資料

10、–single-transaction 适合innodb事務資料庫備份

說明：innodb表在備份時，通常啟用選項–single-transaction來保證備份的一緻性，實際上它的工作原理是設定本次會話的隔離級别，repeatable read 以確定本次（dump）時，不會看到其他會話已經送出的資料

myisam：

mysqldump -utoot -p123 -A -B --master-data=1 -x --events |gzip > /opt/all.sql.gz

innodb:

mysqldump -uroot -p123 -A -B --naster-data=1 --events --single-transation|gzip > /opt/all.sql.gz

說明：如果混合了myisam和innodb引擎，那麼使用myisam引擎為主

mysql分庫備份後恢複

gzip -d *

for dbname in

ls *.sql

|sed ‘s#_bak.sql##g’;do mysql -uroot -poldboy123 < ${dbname}_bak.sql;done

mysqlbinlog

作用：用來記錄mysql内部增删改查等對mysql資料庫有更新的内容的記錄。

拆庫恢複(隻恢複某個庫)：

mysqlbinlog -d oldboy mysql-bin.000020 > oldboy.sql

mysqlbinlog位置點恢複：

mysqlbinlog mysql-bin.000020 --start-position=365 --stop-position=456 -r pos.sql

mysqlbinlog時間點恢複：

mysqlbinlog

/usr/local/mysql/bin/mysqlbinlog /home/mysql3306/mysql3306/mysql-bin.000026 --start-datetime=‘180413 16:33:59’ --stop-datetime=‘180413 16:36:04’ -r /opt/time.sql

sql的分類

–基本查詢語句（DQL）：

SELECT * | {[column,] group_function(column),…}

FROM table

[WHERE condition]

[GROUP BY column]

[DRDER BY column];

–大寫關鍵字不可簡寫中間不可換行

– | 管道符多選一

– [] 中括号可選項

– ， … 後面可以有多個元素

– 小寫需要使用具體的值替代

– 上面文法幫助中每一行我們稱之為一個字句

SELECT 子句

FROM 子句

where 子句

group by 子句

order by 子句;

–查詢表中的所有列從表明

SELECT * FROM EMP

–查詢特定的列

SELECT empno,ename,sal

FROM emp;

加減乘除：

select 1+2*3/4

from dual;

其中 dual 是空表，虛拟表，并不存在，因為from後面不加表會報錯

–别名和特殊别名

mysql> select ename,empno,sal,SAL*12 AS year_SAL from emp;

±-------±------±-----±---------+

±-------±------±-----±---------+

| SMITH | 7369 | 800 | 9600 |

| ALLEN | 7499 | 1600 | 19200 |

| WARD | 7521 | 1250 | 15000 |

±-------±------±-----±---------+

計算出員工12個月工資，年薪.

其中AS 可以忽略，不寫。

mysql> select ename,empno,sal,SAL*12 AS ‘year SAL’ from emp;

±-------±------±-----±---------+

±-------±------±-----±---------+

| SMITH | 7369 | 800 | 9600 |

| ALLEN | 7499 | 1600 | 19200 |

| WARD | 7521 | 1250 | 15000 |

±-------±------±-----±---------+

其中如果不想用_ 想用中間空格，那麼可以用‘’ 或者“” 不推薦使用這種方式下劃線_比較好

– || 字元串連接配接

查詢員工的名字，職位

oracle用法

select ename|| ‘IS’ ||job from emp;

mysql用法

mysql> select concat(ename,job) from emp;

–檢視部門編号

mysql> select DEPTND from emp;

±-------+

| DEPTND |

±-------+

| 20 |

| 30 |

| 20 |

| 30 |

| 10 |

| 20 |

| 10 |

| 30 |

| 20 |

| 30 |

| 20 |

| 10 |

±-------+

–去除重複值

mysql> select distinct deptnd from emp;

±-------+

| deptnd |

±-------+

| 20 |

| 30 |

| 10 |

±-------+

–查找雇員編号為7788的員工資訊 where empno=7788 為過濾條件 = 比較運算符

= < <= != <>

mysql> select * from emp where empno=7788;

±—±------±------±--------±-----±-----------±-----±-----±-------+

| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |

±—±------±------±--------±-----±-----------±-----±-----±-------+

| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |

±—±------±------±--------±-----±-----------±-----±-----±-------+

–查找工資高于2000的員工資訊

mysql> select * from emp where sal >2000;

±—±------±------±----------±-----±-----------±-----±-----±-------+

| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |

±—±------±------±----------±-----±-----------±-----±-----±-------+

| 4 | 7566 | JONES | MANAGER | 7839 | 1981-01-05 | 2975 | | 20 |

| 6 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-07 | 2850 | | 30 |

| 7 | 7782 | CLARK | MANAGER | 7839 | 1981-01-08 | 2450 | | 10 |

| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |

| 9 | 7839 | KING | PRESIDENT | | 1981-01-10 | 5000 | | 10 |

| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |

±—±------±------±----------±-----±-----------±-----±-----±-------+

–查找員工工資大于3000 并包含3000的員工資訊

mysql> select * from emp where sal >=3000;

±—±------±------±----------±-----±-----------±-----±-----±-------+

| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |

±—±------±------±----------±-----±-----------±-----±-----±-------+

| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |

| 9 | 7839 | KING | PRESIDENT | | 1981-01-10 | 5000 | | 10 |

| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |

±—±------±------±----------±-----±-----------±-----±-----±-------+

–查詢員工小于1100 并包含1100的員工資訊

mysql> select * from emp where sal <= 1100 ;

±—±------±------±------±-----±-----------±-----±-----±-------+

| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |

±—±------±------±------±-----±-----------±-----±-----±-------+

| 1 | 7369 | SMITH | CLERK | 7902 | 1981-01-02 | 800 | | 20 |

| 11 | 7876 | ADAMS | CLERK | 7788 | 1981-01-12 | 1100 | | 20 |

| 12 | 7900 | JAMES | CLERK | 7698 | 1981-01-13 | 950 | | 30 |

±—±------±------±------±-----±-----------±-----±-----±-------+

–查詢員工小于1100的員工資訊

mysql> select * from emp where sal <1100 ;

–查詢員工工資不包含5000的員工資訊

mysql> select * from emp where sal != 5000 ;

mysql> select * from emp where sal <> 1300 ;

–between and >= <=

–查找工資範圍在1500到3000的員工資訊

mysql> select * from emp where sal between 1500 and 3000;

±—±------±-------±---------±-----±-----------±-----±-----±-------+

| id | EMPNO | ENAME | JOB | MGR | HIREDATE | SAL | COMM | DEPTND |

±—±------±-------±---------±-----±-----------±-----±-----±-------+

| 2 | 7499 | ALLEN | SALESMAN | 7698 | 1981-01-03 | 1600 | 300 | 30 |

| 4 | 7566 | JONES | MANAGER | 7839 | 1981-01-05 | 2975 | | 20 |

| 6 | 7698 | BLAKE | MANAGER | 7839 | 1981-01-07 | 2850 | | 30 |

| 7 | 7782 | CLARK | MANAGER | 7839 | 1981-01-08 | 2450 | | 10 |

| 8 | 7788 | SCOTT | ANALYST | 7566 | 1981-01-09 | 3000 | | 20 |

| 10 | 7844 | TURNER | SALESMAN | 7698 | 1981-01-11 | 1500 | 0 | 30 |

| 13 | 7902 | FORD | ANALYST | 7566 | 1981-01-14 | 3000 | | 20 |

±—±------±-------±---------±-----±-----------±-----±-----±-------+

– IN 查找工資等于 5000 或 3000 的員工資訊

mysql> select * from emp where sal in (5000,3000);

– null 值代表的是不确定并不是沒有

select * from emp where comm is null;

select 1*2+3/4+null from dual;

–模糊查詢 like

– %通配符代表是0個帶N個字元

– _ 通配符代表一個字元

select * from emp where ename like ‘k%’;

select * from emp where ename like ‘k__’

–查詢員工的名字包含五個字元

select * from emp where ename like ‘_____’’

– 多條件的組合 and 同時滿足條件

–查找部門10中工資高于2000的員工資訊

select * from emp where deptno=10 and sal >2000;

–or 隻需要滿足其中一個條件就可以，當然同時滿足兩個條件也可以

mysql> select * from emp where deptnd=10 or sal >2000;

–not 運算符

–查找工資大于等于2000的員工資訊

mysql> select * from emp where sal>=2000;

–查找工資不小于2000的員工資訊

mysql> select * from emp where not sal<2000; (員工工資不低于2000)

–查找員工傭金不是null的資訊

mysql> select * from emp where comm is not null;

–查找名字不含字母k的資訊

mysql> select * from emp where ename not like ‘%k%’;

– 優先級

– and 的優先級是高于or的

select * from emp where deptno=10 and sal >2000 or job like ‘sa%’

mysql> select * from emp where deptnd=10 and (sal >2000 or job like ‘sa%’);

– 排序按照工資排序

mysql> select * from emp order by sal;

– 查詢員工的名字，部門編号，工資，年薪

– order by 後加列名

mysql> select ename,deptnd,sal,sal*12 YEAR_SAL from emp order by sal;

– order by 後加列别名

mysql> select ename,deptnd,sal,sal*12 year_sal from emp order by year_sal;

– order by 後加列在select 子句中的位置 asc升序（預設可以不寫） desc（降序必須寫）

select ename,comm,deptnd,sal,sal*12 year_sal from emp order by 3; （不推薦）

mysql> select ename,deptnd,sal,sal*12 year_sal from emp order by 3 desc;

– 按多列排序

– 按部門編号升序排列，如果部門編号相同，再按照員工編号降序排列

mysql> select * from emp order by deptnd asc , empno desc;

– 按照傭金排序升序 null值在最後

mysql> select * from emp order by comm；

– 按照傭金排序升序 null值再前面

mysql> select * from emp order by comm null first;

– 按照傭金排序降序

mysql> select * from emp order by comm desc;

– 函數是一個資料庫對象

– 函數可以有0-N個參數但是隻能有而且必須有一個傳回值

– 函數可以在sql中直接調用

–字元函數

大小寫轉換函數***

– upper

mysql> select ‘aBc’,upper(‘aBc’) from dual;

– lower

mysql> select ‘aBc’,lower(‘aBc’) from dual;

– initcap （首字母大寫）mysql不可用

mysql> select ‘aBc’,initcap(‘aBc’) from dual;

mysql> select ‘aBc’,initcap(‘aBc def’) from dual;

輸出為 Abc Def 中間空格會識别為兩個獨立單詞

– 補位函數

*補位函數

– LPAD (要補位的字元串，補位後的長度，使用什麼字元進行補位）

mysql> select lpad(‘1’,10,’*’) from dual;

±-----------------+

| lpad(‘1’,10,’*’) |

±-----------------+

| *********1 |

±-----------------+

– RPAD

mysql> select rpad(‘1’,10,’*’) from dual;

±-----------------+

| rpad(‘1’,10,’*’) |

±-----------------+

| 1********* |

±-----------------+

左右之分

清除函數*****

– trim

mysql> select ’ abc ’ ,trim(’ abc ') from dual;

±-------------------±----------------------+

| abc | trim(’ abc ') |

±-------------------±----------------------+

| abc | abc |

±-------------------±----------------------+

mysql> mysql> select ‘aaabbbaaaa’ ,trim(‘a’ from ‘aaabbbaaaa’) from dual;

±-----------±----------------------------+

| aaabbbaaaa | trim(‘a’ from ‘aaabbbaaaa’) |

±-----------±----------------------------+

| aaabbbaaaa | bbb |

±-----------±----------------------------+

– ltrim 去除左邊的，預設去的是空格

mysql> select ’ aaa ‘,ltrim(’ d aaa d ') from dual;

±-----------±-------------------------+

| aaa | ltrim(’ d aaa d ') |

±-----------±-------------------------+

| aaa | d aaa d |

±-----------±-------------------------+

– rtrim 去除右邊的，預設去的是空格

mysql> select ’ aaa ‘,rtrim(’ d aaa d ') from dual;

±-----------±-------------------------+

| aaa | rtrim(’ d aaa d ') |

±-----------±-------------------------+

| aaa | d aaa d |

±-----------±-------------------------+

長度函數*

– length 計算字元

mysql> select length(‘aaa’) from dual;

±--------------+

| length(‘aaa’) |

±--------------+

| 3 |

±--------------+

– lengthb 計算位元組

mysql> select lengthB(‘中文’) from dual; mysql不适用

字元串連接配接***

– concat

mysql> select concat(‘ABC’,‘DEF’,‘hhh’) from dual;

±--------------------------+

| concat(‘ABC’,‘DEF’,‘hhh’) |

±--------------------------+

| ABCDEFhhh |

±--------------------------+

字元串查找**********

– inset (目标字元串，要查找的字元（串），從第幾個字元開始查找（預設是1，找這個字元串的第幾次出現）)

mysql> select instr(‘abcabc’,‘c’) from dual;

字元串截取*******

– substr（目标字元串，從第幾個字元開始截取，截取多長）

mysql> select substr(‘123456789’,1,2) from dual;

±------------------------+

| substr(‘123456789’,1,2) |

±------------------------+

| 12 |

±------------------------+

如果不寫截取長度，從該字元開始截取到末尾

mysql> select substr(‘123456789’,4) from dual;

±----------------------+

| substr(‘123456789’,4) |

±----------------------+

| 456789 |

±----------------------+

位置可以寫負數，含義是倒數幾個字元

mysql> select substr(‘123456789’,-4,2) from dual;

±-------------------------+

| substr(‘123456789’,-4,2) |

±-------------------------+

| 67 |

±-------------------------+

–數字函數

舍入函數*

– round (目标值，小數位)

– 保留到小數點後兩位

mysql> select round(123.456,2) from dual;

– 不寫小數位，保留整數

mysql> select round(123.456) from dual;

– 保留到小數點前兩位

mysql> select round(123.456,-2) from dual;

**截斷函數

– trunc （目标值，小數位）（oracle）

select trunc(123.456,2) from dual;

– 如果不寫小數位，隻保留整數

select trunc(123.456) from dual;

– 小數位如果為負數,則舍棄小數點前幾位的數值

select trunc(123.456,-2) from dual;

***取餘函數

– mod（被除數，除數）

select mod(4,3) from dual;

*******絕對值函數

– abs

selec abs(-123213) from dual;

****向上向下取整

– ceil

select ceil(1.00000000001) from dual;

select ceil(-1.00000000001) from dual;

– fioor

select floor(1.00000000001) from dual;

select floor(-1.00000000001) from dual;

–日期函數

**目前時間

– sysdate 目前時間

mysql> select sysdate() from dual;

– 時間運算

select sysdate,sysdate +1 from dual;

***月份運算

– add_months(日期，月份數)

mysql> SELECT ADDDATE(NOW(), INTERVAL +120 MONTH);

– month_between

– 查詢員工的名字，如入職日期，工作月份數

–轉換函數資料類型間的轉換

–通用函數函數的使用不受限于資料類型

–單行函數和多行函數·

記憶體配置設定

檔案存儲

線程

算法+資料結構

innodb體系結構

buffer pool

緩存資料（既緩存索引頁緩存資料）

MyISAM隻緩存索引，不緩存資料

FREE連結清單

沒有被使用的buffer塊，被放置在FREE連結清單裡面

Mysql一個資料頁16kb （page） oracle一個資料塊8k（block）

如果先緩存的資料，從FREE連結清單拿空塊，如果FREE沒有空塊，在LRU連結清單利用LRU算法淘汰舊的資料塊。

LRU連結清單

實作LRU算法的連結清單

LRU----最近最少未使用算法

innodb_max_dirty_pages_pct

這個參數控制髒頁的比例如果是innodb_plugin或是mysql5.5以上的版本，建議這個參數可以設定到75-90%。如果是大量寫入，而且寫入的資料不是太活躍，可以考慮把這個值設的低一點，如果寫入或是更新的資料也就是熱資料可以考慮把這個值設為95%

innodb_old_blocks_time

表示頁讀入mid位置後需要等待多久才會被加入到LRU清單的熱端

Mysql 5.7新特性資料預熱

mysql buffer pool資料預熱參數

innodb_buffer_pool_load_at_startup = 1

innodb_buffer_pool_dump_at_shutdown = 1

innodb_buffer_pool_dump_pct = 40

轉儲每個bp instance LRU上最熱的page的百分比

innodb_flush_neighbors

預設是開的，這個一定要着，充分利用順序IO去寫資料

ib_buffer_pool

持久化熱資料的檔案，存儲路徑位置/home/mysql3306/mysql3306

FLUSH連結清單

髒塊被刷入FLUSH連結清單，然後會在一段時間内進行落盤，髒塊在LRU連結清單裡面和FLUSH裡面都存在，LRU連結清單管理資料塊的可用性，FLUSH用于資料塊的寫入磁盤。

Redo

1.redo 實體邏輯日志，記錄資料頁的改變的日志。

2.mysql 的redo buffer到達1/2，oracle的redo buffer是到達1/3，1S 10S 重新整理redo

3.commit時候落盤

存儲路徑位置 /home/mysql3306/mysql3306/ib_logfile0 和 ib_logfile1

innodb_log_files_in_group

innodb每個redo log file的成員數量，預設2

innodb_log_file_size

日志檔案的大小，預設值：5242880（5m）

innodb_log_buffer_size

Log buffer的大小，就是日志緩沖的大小，重做日志緩沖，一般情況下8MB足夠使用，如果不放心，可以使用16MB

innodb_lock_wait_timeout

事務等待擷取資源等待的最長時間，超過這個時間還未配置設定到資源則會傳回應用失敗。

innodb_flush_log_at_trx_commit （重點）

控制redo重新整理政策的參數

這個參數有三個值可以設定

0 （效率最高，同時安全性最低，如果當機，可能丢失一秒的事務）每一條重新整理一次redo，同時每秒flush到disk

log buffer每秒寫log file一次（資料庫），并且lofile的磁盤flush重新整理同步進行（系統），這種情況下，log buffer僅僅在master thread的每秒循環中執行。

1 （最安全的參數，效率最低）（一般情況下使用，最安全）

每次事務送出都會進行log buffer的寫入到log file（資料庫），但是flush操作是每秒進行一次（系統）

2 每次commit，都會進行redo的刷入，但是每秒flush到disk

（如果資料庫當機，不會丢失資料，如果伺服器當機，丢一秒，因為資料緩存在作業系統緩存中，可以了解記憶體中）

核心業務，雙1設定，提高安全性

導入大批量的sql，設定為0，提高很高的效率

undo

1.DML操作導緻的資料記錄變化，均需要将記錄的前鏡像寫入undo日志（邏輯日志）

2.undo頁面的修改，同意需要記錄redoi日志

3.rollback–反向使用事務的所有undo日志，復原整個事務所做的修改

Mysql 5.6新特性 undo表空間的單獨設定

Mysql 5.7新特性線上回收undo表空間

mysql5.7 undo配置

innodb_undo_directory = /home/mysql3306/mysql3306/

innodb_undo_logs = 128

Log段的個數，必須大于等于35，預設128

innodb_undo_tablespaces = 3

Undo表空間的個數

innodb_undo_log_truncate = 1

開啟undo的線上回收

innodb_max_undo_log_size = 1000M

Undo的log最大檔案限制，超過了就可以線上回收了

innodb_purge_rseg_truncate_frequency = 128

資料通路頻率的參數，預設即可

檢查點(checkpoint)

檢查點解決的問題

縮短資料庫恢複時間
緩沖池不夠用的時候，重新整理髒頁到磁盤
重做日志不夠用的時候，重新整理髒頁·

當資料庫發生當機的時候，資料庫不需要恢複所有的頁面，因為檢查點之前的頁面都已經重新整理回磁盤了。故資料庫隻需要對檢查點以後的日志進行恢複，這就大大減少了恢複時間。

ckpt 刷髒頁

Sharp ckpt 刷所有髒頁到硬碟，比如在關閉資料庫的時候

fuzzy ckpt 重新整理部分髒頁

1.master thread checkpoint ：master每一秒或者十秒落盤

2.sync check point ： redo不可用的時候，這時候重新整理到磁盤是從髒頁連結清單中重新整理的。

3.Flush_lru_list check point : 重新整理flush list的時候，落盤的操作是異步的，是以不會阻塞其他事務執行。

檢查點的作用：

縮短資料庫的恢複時間

緩沖池不夠用的時候，将髒頁重新整理到磁盤

重做日志不可用的時候，重新整理髒頁（循環使用redo檔案，當舊的redo要被覆寫的時候，需要重新整理髒頁，造成檢查點）

Innodb 的關鍵特性的講解

1.插入緩沖

（insert buffer）

ibuf

5.5版本以後開始支援del等dml操作，change buffer

索引

導入大批量資料，先把索引清空，導入資料，建立索引

索引的優點：

1.優化查詢（select dml）

2.優化排序（索引資料庫有序，可以通過索引規避排序）

缺點：

1.針對dml，增加維護成本

2.占用空間

對于沒有被緩存到記憶體的索引，資料庫使用插入緩沖的兩種條件

1.索引是輔助索引

2.索引不是唯一的

在合并插入緩沖的過程中，資料庫并不去判斷唯一性

使用insert buffer過程

1.判斷輔助索引是否在記憶體中

2.若在則直接插入

3.如果不在，則先插入insert buffer

4.merge insert buffer

insert buffer的優點

1.減少磁盤的離散讀取

2.将多個插入合并成一次操作（配合異步IO）

insert buffer 的合并操作

1.通過master thread定期進行合并

2.使用者在讀取輔助的時候進行合并

2.兩次寫 double write

部分寫問題：頁面的重新整理會遇到部分寫的問題，也就是說對于隻寫了其中一個頁面，隻寫了一部分的内容，innodb的頁面大小是16kb，但是寫入過程中隻寫了4kb（作業系統僅僅保證512位元組寫入的完整性），這個是時候因為頁面不是完整的，是以不能通過redo來進行恢複，redo恢複的前提條件是頁是完成的。

在資料庫崩潰後，傳統的資料庫會使用redo log來程序恢複，恢複的原理是通過redo對資料也進行重新進行實體操作，但是如果這個資料頁本身也發生了損壞，那麼redo對其進行重做也是沒有作用的，innodb的二次寫，在寫入的時候，創造了一個關于頁的副本，這樣即使在發生寫失效後，也可以通過副本頁，對還原重做。

double write的實作機制

double write分為兩部分

一部分是記憶體中的double write buffer ，大小為2MB （16k一個頁，一共128頁）

第二部分是磁盤共享表空間的128個資料頁，

在對髒頁進行落盤的時候，并不是直接進行落盤，而是先複制到double write buffer，然後再分别寫入到共享空間，然後再寫入表空間

innodb_doublewrite

開啟關閉double_doublewrite

自适應hash索引

維護索引葉頁面中所有記錄的索引鍵值或鍵值字首到索引葉頁面為孩子的hash映射關系，能夠根據索引鍵值（字首）快速定位到葉頁面滿足條件記錄的offset，減少了B+樹從root頁面到leaf頁面的路徑定位，優化為hash index的快速查詢

适用的sql

where a=1

where a=1 and b=2

不适用的sql

where a>1

選項參數：

innodb_adaptive_hash_index

開啟關閉自适應hash索引

背景線程

Mysql有很多背景線程，其中包括了負責IO的相關線程

IO thred

1.write thread 預設四個，負責資料塊的寫入

2.read thread 預設四個，負責資料塊的讀取

檢視方法

mysql> show variables like ‘%write%’;

±------------------------±---------------------+

| Variable_name | Value |

±------------------------±---------------------+

| delay_key_write | ON |

| innodb_doublewrite | ON |

| innodb_write_io_threads | 4 |

| max_write_lock_count | 18446744073709551615 |

| net_write_timeout | 60 |

±------------------------±---------------------+\

mysql> show variables like ‘%read_io%’;

±-----------------------±------+

| Variable_name | Value |

±-----------------------±------+

| innodb_read_io_threads | 4 |

±-----------------------±------+

mysql> show variables like ‘%innodb_io_capaci%’;

innodb_io_capacity 是硬碟性能關系

設定參考（預設值200）

nnodb_io_capacity

磁盤配置

200

單盤SAS/SATA

2000

SAS*12 RAID 10

5000

SSD

50000

FUSION-IO

±-----------------------±------+

| Variable_name | Value |

±-----------------------±------+

| innodb_io_capacity | 200 |

| innodb_io_capacity_max | 2000 |

±-----------------------±------+

innodb_page_cleaners

在mysql 5.6中，開啟了一個獨立的page cleaner線程來進行刷lru list 和 flush list 。預設每隔一秒運作一次

mysql 5.7可以設定多個page cleaner線程提高髒頁面重新整理效率

1.5.6版本以前，髒頁的清理工作交由master線程的。

2.page cleaner thread是5.6.2引入的一個新線程（單線程），從master線程中卸下buffer pool刷髒頁的工作獨立出來的線程（預設是一個線程）

3.5.7開始支援多線程刷髒頁

下面配置參數如果值很大，則需要增加innodb_page_cleaners值，同時增加寫線程。

mysql> show global status like ‘%wait_free%’;

±-----------------------------±------+

| Variable_name | Value |

±-----------------------------±------+

| Innodb_buffer_pool_wait_free | 0 |

±-----------------------------±------+

根據上面的介紹可以發現，緩沖池的大小，直接影響了資料庫的性能，通過增加緩沖池的大小，可以提升資料庫的性能，因為這樣的情況下，資料庫的性能可以提升性能

mysql> show variables like ‘%innodb_buffer_pool_size%’;

±------------------------±-----------+

| Variable_name | Value |

±------------------------±-----------+

| innodb_buffer_pool_size | 1090519040 |

±------------------------±-----------+

在5.7 buffer pool 線上修改buffer pool大小

innodb_buffer_pool_size = 536870912

innodb_buffer_pool_chunk_size = 134217728

在動态調整buffer pool大小的時候，必須是chunk_size的整數倍

配置參數(僅5.7有效)：

mysql> set global innodb_buffer_pool_size=805306368;

配置多個buffer pool 配置多個緩沖池執行個體（重點）

mysql> show variables like ‘innodb_buffer_pool_instances’; ±-----------------------------±------+ | Variable_name | Value | ±-----------------------------±------+

| innodb_buffer_pool_instances | 1 | ±-----------------------------±------+

一般1G 建議配置為1

4GB 2

8GB 2

32 4

64 8

mysql參數調整：

innodb系統表空間的資料目錄 /home/mysql3306/mysql3306/ibdata1

innodb_data_file_path

如果方多個，隻能在最後表空間檔案設定

innodb_file_per_table

獨立表空間，預設開啟

3.自适應hash索引

4.重新整理臨頁

緩存池中大部分資訊都是放置的b+tree索引的非葉子節點和葉子節點，自适應hash通過根據b+tree的通路模式對頁中的記錄建立hash索引，是完全的記憶體結構。

異步IO

Mysql在5.6之後開啟了異步IO，預設情況下使用linux原生aio，檢視mysql是否開啟異步IO

參數：

Variable_name: innodb_use_native_aio

Value: ON

可以通過開啟和關閉Native AIO功能來比較InnoDB性能的提升。官方的測試顯示，啟用Native AIO，恢複速度可以提高75%。
在InnoDB存儲引擎中，髒頁的重新整理，即磁盤的寫入操作則全部由AIO完成。

vim /etc/my.cnf

#— INNODB —#

可以修改,但是預設為innidb

default_storage_engine = InnoDB

innodb_data_file_path = ibdata1:1024M:autoextend

innodb_buffer_pool_size = 1040M

innodb_buffer_pool_instances = 1

innodb_additional_mem_pool_size = 16M

innodb_log_files_in_group = 2

innodb_log_file_size = 256MB

innodb_log_buffer_size = 16M

innodb_flush_log_at_trx_commit = 2

innodb_lock_wait_timeout = 30

innodb_flush_method = O_DIRECT

innodb_max_dirty_pages_pct = 75

innodb_io_capacity = 200

innodb_thread_concurrency = 32

innodb_open_files = 65535

innodb_file_per_table = 1

transaction_isolation = REPEATABLE-READ

innodb_locks_unsafe_for_binlog = 0

#innodb_purge_thread = 4

skip_name_resolve = 1

MyISAM

檢視innodb引擎狀态

mysql> show engine innodb status;

FILE I/O

show engine innodb status

FILE I/O

I/O thread 0 state: waiting for completed aio requests (insert buffer thread)

I/O thread 1 state: waiting for completed aio requests (log thread)

I/O thread 2 state: waiting for completed aio requests (read thread)

I/O thread 3 state: waiting for completed aio requests (read thread)

I/O thread 4 state: waiting for completed aio requests (read thread)

I/O thread 5 state: waiting for completed aio requests (read thread)

I/O thread 6 state: waiting for completed aio requests (write thread)

I/O thread 7 state: waiting for completed aio requests (write thread)

I/O thread 8 state: waiting for completed aio requests (write thread)

I/O thread 9 state: waiting for completed aio requests (write thread)

擴容innodb

單機擴容（/home---->/data）

1.關閉mysql

2.拷貝資料檔案到新的目錄

3.修改配置檔案

4.重新開機資料庫

擴容系統表空間

1.關閉mysql服務 2.如果上一個資料檔案使用autoextend定義改為固定大小檢查資料檔案大小，将其設定到最接近的大小

第三步:将新的資料檔案添加到innodb_data_file_path的末尾，可選地使該檔案自動擴充。注意:隻能将innodb_data_file_path中的最後一個資料檔案指定為自動擴充。

innodb_data_home_dir = innodb_data_file_path = /data/mydb/ibdata1:12M:autoextend

innodb_data_home_dir = innodb_data_file_path = /data/mydb/ibdata1:28M;/disk1/ibdata2:100M:autoextend

縮小線上的ibddata1的大小

1.使用mysqldump導出所有表結構

2.關閉MySQL

3.删除所有表空間檔案

4.重新初始化MySQL

5.重新開機資料庫

6.導入資料

mysql innodb表空間遷移

把一張表從執行個體遷移到B執行個體

将表空間遷移到其他執行個體

1.在目标執行個體上建立一個相同的表

2.在目标庫上執行ALTER TABLE t DISCARD TABLESPACE; 3.在源庫上執行FLUSH TABLES t FOR EXPORT;生成.cfg檔案 4.講.ibd檔案和.cfg檔案拷貝到目标執行個體

5.在源庫執行unlock tables;

6.在目标庫執行ALTER TABLE t IMPORT TABLESPACE;

遷移過程

在source server A上，建立一個表

mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

在destination server B上，也建立同樣的表結構 mysql> use test;

mysql> CREATE TABLE t(c1 INT) engine=InnoDB;

在destination server B上，discard 該表(這一步是必須的)

mysql> ALTER TABLE t DISCARD TABLESPACE;

discard的意思就是從資料庫detached,會删除ibd檔案，保留frm檔案。
也就意味着，你可以對frm檔案操作，比如:rename table,drop table ，但是不能對ibd檔案操作，比如:dml

在source server A上，執行 FLUSH TABLES … FOR EXPORT , 該表這時候處于quiesce狀态，隻讀，且建立.cfg metadata檔案 mysql> use test;

mysql> FLUSH TABLES t FOR EXPORT;

此時，.cfg檔案在InnoDB的data directory中

flush tables … for export 會加鎖，這時候，千萬不能退出終端或session，否則加鎖無效且.cfg檔案自動删除。

拷貝.ibd & .cfg 從source server A 到在destination server B

shell> scp /path/to/datadir/test/t.{ibd,cfg} destination-server:/path/to/datadir/test 修改權限

在source server A，執行unlock tables 來釋放FLUSH TABLES … FOR EXPORT 加的locks mysql> use test;

mysql> UNLOCK TABLES;

在destination server B上，導入tablespace mysql> use test;

mysql> ALTER TABLE t IMPORT TABLESPACE;

innodb可傳輸表空間注意事項 ----------------------------- 必須開啟 innodb_file_per_table 當這個表處于quiesced狀态,甚至不能被select 兩邊執行個體的page size 一緻

5.7 版本之前，不支援分區表transport

外鍵相關的表，必須設定 foreign_key_checks=0 才能成功

ALTER TABLE … IMPORT TABLESPACE 不需要.cfg metadata file . 但是，這樣的話，MySQL就不會對schema進行verificate 5.6以及更高版本，import&export 版本必須在同一個series

在replication環境中，master & slave 都必須開啟 innodb_file_per_table

對于InnoDB general tablespace，不支援discard & import tablespace

如果兩邊伺服器的table row_format設定的不一樣，會導緻schema mismatch error

加密過的InnoDB tablespace 必須要拷貝.cfp 檔案

MyISAM

在目标資料庫建立表結構，

建議flush table with read lock

然後直接拷貝MYI MYD

mysql日志

如果删掉錯誤日志，需要執行：

mysql> falsh logs;

MySQL general日志

會記錄所有執行過的sql文本檔案

一般情況下，我們不開啟general日志，因為對于資料庫的影響比較嚴重，一般選擇關閉，在某些特殊情況下，比如要審計sql，或者統計sql執行頻次，臨時打開，然後關閉

mysql> show variables like ‘%general%’;

±-----------------±-------------------------------+

| Variable_name | Value |

±-----------------±-------------------------------+

| general_log | OFF |

| general_log_file | /home/mysql3306/logs/mysql.log |

MySQL慢查詢日志

執行時間長于某個點的sql被記錄

| slow_query_log | ON |

| slow_query_log_file | /home/mysql3306/logs/mysql-slow.log |

超過該閥值的sql，被記錄到慢日志（s）

mysql> show variables like ‘%long%’;

±-------------------------------------------------------±---------+

| Variable_name | Value |

±-------------------------------------------------------±---------+

| long_query_time | 2.000000 |

log_queries_not_using_indexes 記錄沒有用索引的查詢，預設是關閉的

mysql> show variables like ‘%not_using_indexes%’;

±---------------------------------------±------+

| Variable_name | Value |

±---------------------------------------±------+

| log_queries_not_using_indexes | OFF |

log_throttle_queries_not_using_indexes

設定每分鐘記錄到日志的未使用索引的語句數目，超過這個數目後隻記錄語句數量和花費的總時間

min_examined_row_limit

要檢查的行數大于等于N時才記錄為慢查詢，前提是必須滿足long_query_time和 log-queries-not-using-indexes限制。

mysql> show variables like ‘%min_examined_row_limit%’;

±-----------------------±------+

| Variable_name | Value |

±-----------------------±------+

| min_examined_row_limit | 0 |

±-----------------------±------+

2018/3/19

mysql備份

xtrabackup

是一個實體熱備，備份資料檔案

innobackupex --defaults-file=/etc/my3306.cnf --no-timestamp --user root --port 3306

/data/xtrabackup/all-20170402.bak

增量備份：

全量備份

sysbench

介紹了GTID的概念和變量，現在說下MySQL5.7下GTID增強的一些特性：

①：線上開啟GTID。MySQL5.6開啟GTID的功能需要重新開機伺服器生效。

複制代碼

mysql> set global gtid_mode=on;ERROR 1788 (HY000): The value of @@GLOBAL.GTID_MODE can only be changed one step at a time: OFF <-> OFF_PERMISSIVE <-> ON_PERMISSIVE <-> ON. Also note that this value must be stepped up or down simultaneously on all servers. See the Manual for instructions.

rows affected ( rows affected (

mysql> set global gtid_mode=ON;ERROR 3111 (HY000): SET @@GLOBAL.GTID_MODE = ON is not allowed because ENFORCE_GTID_CONSISTENCY is not ON.

mysql> set global enforce_gtid_consistency=on;Query OK, 0 rows affected (0.00 sec)

mysql> set global gtid_mode=ON;Query OK, 0 rows affected (0.16 sec)

複制代碼

線上開啟GTID的步驟：不是直接設定gtid_mode為on，需要先設定成OFF_PERMISSIVE，再設定成ON_PERMISSIVE，再把enforce_gtid_consistency設定成ON，最後再将gtid_mode設定成on，如上面所示。若保證GTID重新開機伺服器繼續有效，則需要再配置檔案裡添加：

gtid-mode=onenforce-gtid-consistency=on

DBA學習筆記

Innodb 的關鍵特性的講解

insert buffer 的合并操作

1.通過master thread定期進行合并

2.使用者在讀取輔助的時候進行合并

一般1G 建議配置為1

4GB 2

8GB 2

32 4

64 8

innodb_file_per_table

獨立表空間，預設開啟

show engine innodb status

FILE I/O

繼續閱讀

MySQL 故障診斷：如何排查行鎖逾時？

OpenDaylight你不可不知的十大問題——OpenDaylight是什麼？

Cisco HDLC

給一個新手程式員的建議

如何擁有技術上司力

Pdfjs-dist 填坑日記第一版

如何生成android端可用so

[題記]三維形體的表面積-leetcode

IIS6.0架構1. 概述2. HTTP.SYS3. W3SVC

IO端口和IO記憶體的差別轉Linux系統對IO端口和IO記憶體的管理

【算法提高班】貪婪政策

比較低功率無線技術(第3部分)---凱利訊半導體

MFC類

Java Spring 架構

多線程擷取随機數性能對比（C vs C++）

實訓成果

DBA學習筆記

Innodb 的關鍵特性的講解

insert buffer 的合并操作 1.通過master thread定期進行合并 2.使用者在讀取輔助的時候進行合并

一般1G 建議配置為1 4GB 2 8GB 2 32 4 64 8

innodb_file_per_table 獨立表空間，預設開啟

show engine innodb status

FILE I/O

繼續閱讀

insert buffer 的合并操作

1.通過master thread定期進行合并

2.使用者在讀取輔助的時候進行合并

一般1G 建議配置為1

4GB 2

8GB 2

32 4

64 8

innodb_file_per_table

獨立表空間，預設開啟