mysql概念

约束 constraint

• 約束：明確有效數據範圍；定義數據時要有約束
• 域約束：數據類型約束
• 外鍵約束：引用完整性約束；是降低數據冗餘的手段；一個表中填的值一定要保證在另外一個表中有相同的值才允許使用。級連表示如果這張表被其他表依賴，如果刪除了這張表，被依賴的表也會被刪。一般不用。與外鍵約束有關。
• 主鍵約束：某字段能惟一標識此字段所屬的實體，並且不允許為空，不能有相同值。可當主鍵的鍵叫候選鍵。一個表只能有一個主鍵
• 唯一性約束：每一行的某字段都不允許出現相同值，可以為空，一張表可以有多個唯一鍵。
• 檢查性約束：age:int定義符合現實的邏輯。

mysql的rpm包

• mysql-client ：提供客戶端及組件
• mysql-debuginfo：可幫助調試mysql，一般開發人員用
• my-devel：開發組件，開發用的頭文件與庫文件
• mysql-embedded：嵌入式服務專用
• mysql-ndb-management ：Cluster server上專用的組件，平常不用
• mysql-server
• mysql-shared：共享庫
• mysql-shared-compat：為老版本的兼容庫，是shared的補充
• mysql-test：測試用組件
• mysql-version.platform.src.rpm：源碼格式的rpm包

安裝後的目錄結構

• bin：二進制程序，命令
• data：數據目錄
• include：頭文件
• lib：庫文件
• man：手冊
• mysql-test：測試組件
• scripts：初始化腳本
• share：語言版本
• sql-bench：基准測試
• support-files：腳本、樣例、等

linux mysql啟動時查找的配置文件

每次啓動，mysql都要讀取下面文件

1. /etc/my.cnf

2. /etc/mysql/my.cnf

3. $MYSQL_HOME/my.cnf
# mysql實例運行下my.cnf。一個數據庫服務器上可以提供多個web服務主機，mysql也可以提供多個不同的mysql服務器，只要它們監聽在不同的端口上就行，因爲不支持基於名稱的，所以要監聽在不同端口上，每個監聽在不同端口上的mysql服務稱爲一個mysql實例，每個實例都有自己的運行目錄，就叫MYSQL_HOME，所以每個實例都可以到自己的家目錄下找my.cnf；

4. /path/to/fiel when defaults-extra-file=/path/to/file is specified    
# 在啓動mysql服務器時可以給它傳遞一個參數，叫--defaults-extra-file

5. ~/.my.conf

/etc/my.cnf --> /etc/mysql/my.cnf --> $MYSQL_HOME/my.cnf --> --default-extra-file=/path/to/somefile --> ~/.my.cnf

配置文件中大多數的參數都可以命令行直接指定的也就是在啓動mysql時，在命令行可指定的參數也基本都可寫在配置文件中，寫在配置文件中時就不要加-了。如-host，在配置文件中直接寫host = ××× 就可以了。再如default-extra-file也可寫爲default_extra_file，這兩種格式都對，但建議寫爲統一格式，要寫在對應的段[×××]下。

windows mysql啟動時查找的配置文件

1. %WINDIR%\my.ini, %WINDIR%\my.cnf
2. C:\my.ini, C:\my.cnf
3. %INSTALLDIR%\my.ini, %INSTALLDIR%\my.cnf
4. /path/to/file when defaults-extra-file=/path/to/file is specified

請求過程

當用戶需要連到數據庫上來完成某種操作時，由連接器負責接收用戶請求，並將請求轉發給線程管理器，線程管理器給它創建一個線程，線程管理器將線程權限轉給用戶模塊，用戶模塊來驗證用戶是否有訪問權限，如果沒有就終止，有就建立連接。之後用戶發起命令，由命令分發模块來完成用戶請求內容是否從緩存中直接返回，是否記錄日志，如果緩存中沒有的話， 可能就要將命令語句轉給解析器分析生成執行树，並交由底層的對應模塊處理，如果是SELECT語句就交給優化器，來生成更優的執行過程，如果用戶發起與數據定義相關的命令都要交給表定義模塊，如INSERT,DELECT等，如果用戶要維護一張表，就交給表維護模塊來完成，用戶每發起一次命令語句，狀態信息都要更新，最終，不論用戶是哪種請求都要判斷用戶是否有對應的訪問權限，這由訪問控制模塊來完成，如果有，就由表管理器負責後續的動作，如讀取表結構、修改表結構、施加表鎖，最終由存儲引擎來負責到對應文件中去找對應的數據。存儲引擎是真正與磁盤打交道的接口。這就是mysql的微觀結構

mysql基礎架構

面對用戶的一端是連接管理器，接收請求並建立連接後的下一層是查詢緩存和分析器，如果查詢緩存中有結果就直接返回給用戶，查詢緩存只與讀操作有關，與寫無關，且只與讀操作的查詢操作有關，如果緩存中沒有結果，就由分析器來分析，分析的結果如果發現緩存中有結果還會從緩存中返回結果，如果沒有結果就交給優化器完成優化，而後就交給存儲引擎，或交給執行引擎再交給存儲引擎完成操作。這就是mysql的基礎架構。連接管理器就是監聽在某個套接字上接收用戶請求，接收後創建一個線程予以響應，因為創建線程很消耗資源，也為了加速線程的創建，所以有了重用線程的概念，用戶退出後線程不被銷毀，而是放到空閒線程池中，有新的用戶請求時直接從空閒線程中拿一個給用戶響應，創建連接後會驗證用戶的連入權限，連入的所有用戶的操作都在它自身的線程地址空間內完成，因CPU有限，每個用戶發起的操作都在CPU上輪流進行，如果有一個非常慢的話會使其他用戶請求非常慢。在遠程連接mysql服務器時數據通過tcp/ip協議傳輸，是明文的，如果是機密的數據，會話管理器（連接管理器）還要進行加密

當我們發起查詢請求後mysql服務器會負責解析每一個查詢，並在mysql進程內部創建一個解析樹，它會去解析每一個請求，並為這個請求創建一個解析樹，解析執行過程並優化，最後計算出最優執行路徑，這就是解析器和優化器的作用。優化器的優化是自我判定的，與實際場景可能有出入，所以結果未必是最佳的。作為用戶可以給優化器提示，在執行語句中，這就避免優化器再去查找。優化器在優化中不會考慮存儲引擎的不同，每一個查詢執行結束之後，如果結果是決定性的，mysql會將結果緩存下來，但視查詢結果大小，不是所有結果都可以緩存的，一定是小於某個上限的才緩存。

緩存（一段內存空間）中的內容被分成了三個區域，每個區域中有一個叫作緩存槽的東西，每個查詢結果都會佔用這個槽的，為了便於管理，槽是有下限的，因為每個結果都要佔用一個槽，如果結詢結果比槽小的多，還要進行緩存，這會浪費槽，如果不浪費，自動去減小緩存空間，也會使緩存中產生大量的碎片，這也會影響執行效率。為了發揮緩存的效能提高效率，對緩存要進行優化，要有限定。非確定性結果，每次結果都不同，如查看時間（SELECT CURRENT_TIME()）一般不緩存。兩個語句一樣也不一定就能緩存，比如兩個查詢的用戶權限不同，有一方沒有查詢權限，就不能緩存。沒有緩存就由解析樹處理，如果有緩存就要查在緩存中是否命中，這會使每條語句都去找一次緩存，成了額外開銷，如此要計算益處與開銷的平衡

讀寫操作同時請求同一張表，任何時候（不只mysql）有兩個用戶同時讀寫同一個文件的數據時都會有並發控制的問題

並發控制是為了提高性能，可以使兩個並發的操作不會互相影響。不能使用串行的方法，一個用戶操作完下個用戶再操作的機制。很多服務器都引入了多版本並發控制的機制，叫作MVCC，每個用戶操作的都是一個數據的副本或快照，最後再完成快照合並，快照會有一個時間值，用來判定快照的先後。時間快照是數據庫必備的功能，要實現時間並發控制就要有鎖功能，多版本並發控制是用其他功能實現的

客戶端命令：mysql、mysqladmin、mysqldump、mysqllimport、mysqlcheck

服務器：mysqld、mysqld_safe、mysqld_multi（不同端口的mysql3306、3307、3308運行在一臺服務器上，相當於多臺服務器）

數據如何存儲到磁盤

磁盤按塊加載數據到內存，如果數據只佔了塊的一部分，也要加載整個塊。多個磁盤塊可組成一個數據塊。由存儲引擎管理。一般使用定長表結構，而不用變長，這樣可以使存取速度加快。因爲塊中的行會被刪除，就空出了空間，所以要做碎片整理，碎片會使裝載速度變慢，爲了管理這樣的數據，每個塊要有塊頭，塊頭裏要有行的信息，有多少空閒行等。這個塊不一定都是磁盤塊，可能是多個磁盤塊組成的數據塊，數據塊由存儲引擎來管理，只用磁盤塊也可以。定長的表的執行速度更快。

文件中記錄組織

• 堆文件組織：一條記錄可以放在文件中的任何地方，維護簡便，管理不便
• 順序文件組織：根據“搜索碼”的值順序存放
• 散列文件組織：分成N個塊，叫桶，將某行的不同列進行hash運算，結果相同的放在一起。hash索引

程序語言連接數據的方式有兩種

• 動態SQL：通過函數或方法與數據庫服務建立連接，然後通過協議將查詢語句直接送到服務器端；簡單的方式屬於動態SQL
• 嵌入式SQL：用程序開發語言的格式寫的，通過API直接連到服務器上，連接之前要先編譯

语句中的错误提示

->：语句未结束，继续輸入
'>：缺單引號的後一半
">：缺雙引號的後一半
`>：缺反引號的後一半
/*>：缺多行注釋的後一半

補全

要把數據庫中的表都載入內存中才能補全

名稱補全：mysql連接時用-A或–no-auto-rehash或–disable-auto-rehash來禁用補全功能；默認是有此功能的，或在mysql中用rehash或#命令就可以名稱補全了，但是是對新建命令的補全。

mysql不能啟動問題的原因

啟動時會在datadir =/mydata/data 指定的路徑下生成一個hostname.err的錯誤日志，mysql啟動後的錯誤信息都在此日志中，如果啟動錯誤還沒有錯誤日志，應該就是數據目錄指錯了。修改配置文件定義datadir =即可。测试中因为用的是mariadb，没有在目录中找到hostname.err日志，只有/var/log中的mariadb的日志，且只有一个mariadb.log

1. 此前的服務未關閉套接字被佔用，用killall mysqld就可以了
2. 數據初始化失敗
3. 數據目錄位置錯誤
4. 數據目錄權限問題

如果解決不了可重新初始化數據庫試一下

數據類型

• 數值型，不需要指定長度

  • 精確數值

    • int：整型（TINYINT微整型1字節、SMALLINT小整型2、MEDIUMINT中整型3、INT整型4、BIGINT大整型8）

    • DECIMAL：精確定點數

  • 近似數值

    • FLOAT：單精度浮點型4
    • DOUBLE：雙精度浮點型8
    • real：實數
    • NUMERIC：定點數值
    • DECIMAL：定點數值

// 數值後加數字表示顯示幾位數字

• 字符型，一定要指定長度

  • 定長：CHAR(#)（不區分大小寫），BINARY（區分）

  • 變長:VARCHAR(#)（不區分大小寫），VARBINARY（區分）

  • text（不區分大小寫）

  • BLOB：二進制大對象（區分）

  • ENUM：枚舉（在給定的選項中選）

  • SET：集合（1-64個字符串，一組數值的組合值）

// CHAR最多存儲255個字符，VARCHAR最多存儲65535個字符，每個VARCHAR存儲時會多佔一個字節，因為要有一個結束符，VARCHAR在255個字符內只需要一個結束符，超出255就需要兩個結束符。text上不能索引整個字段，CHAR上可以

• 字符串修飾屬性

  • NOT NULL：不能為空
  • NULL：可為空（默認）
  • DEFAULT：給一個默認值
  • CHARACTER SET：字符集
  • COLLATION：排序規則

• 二進制大對象，區分大小寫，因爲按字節排序

  • TINYBLOB 255
  • MEDIUMBLOB 16M
  • LONGBLOB 4G
  • BLOB 64K
  • BINARY 255
  • VARBINARY 65535

// 大對象存儲在一個單獨的位置，表上只是一個指針，故無法在表上建索引

• 日期時間型

  • date：日期
  • time：時間
  • datetime：日期時間
  • timestamp：時間戳
  • YEAR：年

• 數據類型的作用

  1. 存入值的類型；
  2. 使用空間大小；
  3. 定長還是變長的；
  4. mysql如何對其進行比較和排序；
  5. 是否可以創建索引

// 域屬性修飾符就是定義域限制的

• 只要在一個域上定義了數據類型，它能作出以下限定
  1. 它能表示哪個种類的數據
  2. 定義最大最小值，定義空間
  3. 確定是變長還是定長的
  4. mysql如何比較排序字符，是否区分大小寫
  5. 這樣的類型是否能夠創建索引，是否能夠索引

查看表结构文件

ls /var/lib/mysql  
# 查看mysql的datadir目录，这里的目录就是数据库中的表，如mysql,test等，在目录内有表结构文件

對myISAM引擎來說每表三個文件，MyISAM表是無事務，支持表鎖
1.  .frm: 表結構定義文件
2.  .MYD：表數據文件
3. .MYI：表索引文件

對InnoDB引擎來說，支持事務，行鎖；要打開每表一個單獨的表空間。每表一個表空間會創建下面兩個文件。所有表共享一個表空間文件；建議：每表一個獨立的表空間文件
1. .frm: 表結構文件
2. .ibd：表空間（同时存储表數據和表索引）
# 建議打開innodb_file_per_table一項為ON，實現每表一個獨立表空間，在mysql中用SHOW GLOBAL VARIABLES LIKE '%innodb%';命令查看全局变量中与innodb相关的項，其中的innodb_file_per_table应该是ON。5.6中默認已打開了此項，不然可以在my.cnf中加入innodb_file_per_table = ON或1一行即可打開。

db.opt標記當前庫的字符集和排序規則的定义，也就是数据库的默认选项

存储引擎

SHOW ENGINES;         
# 查看存儲引擎

MyISAM
# 不支持事務，默認只支持表鎖，讀性能更好，寫性能差，不支持外鍵，支持B樹索引、FULLTEXT索引、空間索引、支持表壓縮；.frm（表格式）  .MYD（數據文件）  .MYI（索引文件）

InnoDB
# 支持事務，支持行級鎖、支持B樹索引、聚簇索引、自適應hash索引，使用表空間、raw磁盤設備； .frm（表格式）  .ibd（表空間）
# 將innodb_file_per_table改成ON就會為每表創建一個表空間了

MRG_MYISAM:
# 可將兩個表合成一張表，也就是建一個兩張表合在一起的存儲引擎

CSV
# 方便移植，一般不用

ARCHIVE
# 用來實現歸檔的

MEMORY
# 在內存中建立存儲引擎

BLACKHOLE
# 黑洞
# 也可用第三方存儲引擎，不建議混合使用存儲引擎