MySQL高級(jí)知識(shí)之 架構(gòu)介紹

mysql視頻教程欄目介紹mysql高級(jí)知識(shí)的架構(gòu)

推薦（免費(fèi)）：mysql視頻教程

1、MySQL 簡(jiǎn)介

概述

• MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB公司開發(fā)，目前屬于Oracle公司。

• MySQL是一種關(guān)聯(lián)數(shù)據(jù)庫(kù)管理系統(tǒng)，將數(shù)據(jù)保存在不同的表中，而不是將所有數(shù)據(jù)放在一個(gè)大倉(cāng)庫(kù)內(nèi)，這樣就增加了速度并提高了靈活性。

• Mysql是開源的，所以你不需要支付額外的費(fèi)用。

• Mysql支持大型的數(shù)據(jù)庫(kù)。可以處理?yè)碛猩锨f(wàn)條記錄的大型數(shù)據(jù)庫(kù)。

• MySQL使用標(biāo)準(zhǔn)的SQL數(shù)據(jù)語(yǔ)言形式。

• Mysql可以允許于多個(gè)系統(tǒng)上，并且支持多種語(yǔ)言。這些編程語(yǔ)言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。

• Mysql對(duì)PHP有很好的支持，PHP是目前最流行的Web開發(fā)語(yǔ)言。

• MySQL支持大型數(shù)據(jù)庫(kù)，支持5000萬(wàn)條記錄的數(shù)據(jù)倉(cāng)庫(kù)，32位系統(tǒng)表文件最大可支持4GB，64位系統(tǒng)支持最大的表文件為8TB。

• Mysql是可以定制的，采用了GPL協(xié)議，你可以修改源碼來(lái)開發(fā)自己的Mysql系統(tǒng)。

高級(jí)MySQL涉及到知識(shí)

• mysql內(nèi)核
• sql優(yōu)化攻城獅
• mysql服務(wù)器的優(yōu)化
• 各種參數(shù)常量設(shè)定
• 查詢語(yǔ)句優(yōu)化
• 主從復(fù)制
• 軟硬件升級(jí)
• 容災(zāi)備份
• sql編程

• 完整的mysql優(yōu)化需要很深的功底，大公司甚至有專門的DBA寫上述

2、MySQL Linux版的安裝

• 此次安裝的是 MySQL 5.5， 安裝環(huán)境 CentOS 6.5
• 版本下載地址 官網(wǎng)下載地址
  • 下載 MySQL-Client 和 MySQL-Server
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-server-5.5.16-1.rhel4.i386.rpm
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-client-5.5.16-1.rhel4.i386.rpm
  • http://downloads.skysql.com/archives/mysql-5.5/MySQL-devel-5.5.16-1.rhel4.i386.rpm
• 檢查當(dāng)前系統(tǒng)是否安裝過MySQL
  • 查詢命令: ?rpm -qa|grep -i mysql
  • 刪除命令: ?rpm -e –nodeps RPM包全名
• 安裝mysql服務(wù)端(注意提示)

  

  • 設(shè)置密碼提示

• 安裝mysql客戶端
• 查看MySQL安裝時(shí)創(chuàng)建的mysql用戶和mysql組
  • # cat /etc/passwd | grep mysql
  • # cat /etc/group | grep mysql
• mysql服務(wù)的啟+停
  • 查看MySQL啟停狀態(tài): ?# ps -ef | grep mysql
  • 啟停操作:
    • # /etc/init.d/mysql start
    • # /etc/init.d/mysql stop
    • 或者
    • #service mysql start
    • #service mysql stop
  • 設(shè)置MySQL 自啟服務(wù)
    • #chkconfig mysql on ? 設(shè)置自動(dòng)啟動(dòng)
    • # chkconfig –list | grep mysql ? 檢查是否設(shè)置了自動(dòng)啟動(dòng)
  • 修改配置文件位置
    • 拷貝當(dāng)前5.5版本： cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
    • 5.6版本 ?cp /usr/share/mysql/my-default.cnf /etc/my.cnf
  • 修改字符集和數(shù)據(jù)存儲(chǔ)路徑
    • 查看字符集
      • show variables like ‘character%’;
      • show variables like ‘%char%’;

      • 

• 字符集

  • 默認(rèn)的是客戶端和服務(wù)器都用了latin1，所以會(huì)亂碼。
  • 修改字符集，修改之前copy 的配置文件。（詳細(xì)后續(xù)代碼）
  • MySQL的安裝位置
    • 在linux下查看安裝目錄 ps -ef|grep mysql

MySQL安裝位置

[client] #password = your_password port = 3306 socket = /var/lib/mysql/mysql.sock  # 這一行需要設(shè)置字符集 default-character-set=utf8   # The MySQL server [mysqld] port = 3306  # 還有這三行 character_set_server=utf8 character_set_client=utf8 collation-server=utf8_general_ci  socket = /var/lib/mysql/mysql.sock skip-external-locking key_buffer_size = 384M max_allowed_packet = 1M table_open_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8   [mysql] no-auto-rehash # 還有這一行 default-character-set=utf8

3、Mysql配置文件

主要配置文件

• 二進(jìn)制日志log-bin
  • 主從復(fù)制

• 錯(cuò)誤日志log-error
  • 默認(rèn)是關(guān)閉的,記錄嚴(yán)重的警告和錯(cuò)誤信息，每次啟動(dòng)和關(guān)閉的詳細(xì)信息等。
• 查詢?nèi)罩緇og
  • 默認(rèn)關(guān)閉，記錄查詢的sql語(yǔ)句，如果開啟會(huì)減低mysql的整體性能，因?yàn)橛涗浫罩疽彩切枰南到y(tǒng)資源的
• 數(shù)據(jù)文件
  • 兩系統(tǒng)
    • windows
      • D:devSoftMySQLServer5.5data目錄下可以挑選很多庫(kù)
    • Linux:
      • 默認(rèn)路徑 #cd /var/lib/mysql/
      • 看看當(dāng)前系統(tǒng)中的全部庫(kù)后再進(jìn)去 #ls -1F | grep ^d
  • frm文件: 存放表結(jié)構(gòu)
  • **myd文件: ** 存放表數(shù)據(jù)
  • **myi文件: ** 存放表索引
• 如何配置
  • Windows: my.ini文件
  • Linux: ?/etc/my.cnf文件

4、Mysql邏輯架構(gòu)介紹

總體概覽

• 和其它數(shù)據(jù)庫(kù)相比，MySQL有點(diǎn)與眾不同，它的架構(gòu)可以在多種不同場(chǎng)景中應(yīng)用并發(fā)揮良好作用。主要體現(xiàn)在存儲(chǔ)引擎的架構(gòu)上，插件式的存儲(chǔ)引擎架構(gòu)將查詢處理和其它的系統(tǒng)任務(wù)以及數(shù)據(jù)的存儲(chǔ)提取相分離。這種架構(gòu)可以根據(jù)業(yè)務(wù)的需求和實(shí)際需要選擇合適的存儲(chǔ)引擎。

  

• 1、連接層
  • 最上層是一些客戶端和連接服務(wù)，包含本地sock通信和大多數(shù)基于客戶端/服務(wù)端工具實(shí)現(xiàn)的類似于tcp/ip的通信。主要完成一些類似于連接處理、授權(quán)認(rèn)證、及相關(guān)的安全方案。在該層上引入了線程池的概念，為通過認(rèn)證安全接入的客戶端提供線程。同樣在該層上可以實(shí)現(xiàn)基于SSL的安全鏈接。服務(wù)器也會(huì)為安全接入的每個(gè)客戶端驗(yàn)證它所具有的操作權(quán)限。
  • 2、服務(wù)層
    • 第二層架構(gòu)主要完成大多少的核心服務(wù)功能，如SQL接口，并完成緩存的查詢，SQL的分析和優(yōu)化及部分內(nèi)置函數(shù)的執(zhí)行。所有跨存儲(chǔ)引擎的功能也在這一層實(shí)現(xiàn)，如過程、函數(shù)等。在該層，服務(wù)器會(huì)解析查詢并創(chuàng)建相應(yīng)的內(nèi)部解析樹，并對(duì)其完成相應(yīng)的優(yōu)化如確定查詢表的順序，是否利用索引等，最后生成相應(yīng)的執(zhí)行操作。如果是select語(yǔ)句，服務(wù)器還會(huì)查詢內(nèi)部的緩存。如果緩存空間足夠大，這樣在解決大量讀操作的環(huán)境中能夠很好的提升系統(tǒng)的性能。
  • 3、引擎層
    • 存儲(chǔ)引擎層，存儲(chǔ)引擎真正的負(fù)責(zé)了MySQL中數(shù)據(jù)的存儲(chǔ)和提取，服務(wù)器通過API與存儲(chǔ)引擎進(jìn)行通信。不同的存儲(chǔ)引擎具有的功能不同，這樣我們可以根據(jù)自己的實(shí)際需要進(jìn)行選取。后面介紹MyISAM和InnoDB
  • 4、存儲(chǔ)層
    • 數(shù)據(jù)存儲(chǔ)層，主要是將數(shù)據(jù)存儲(chǔ)在運(yùn)行于裸設(shè)備的文件系統(tǒng)之上，并完成與存儲(chǔ)引擎的交互。

查詢說明

• 首先，mysql的查詢流程大致是：
  • mysql客戶端通過協(xié)議與mysql服務(wù)器建連接，發(fā)送查詢語(yǔ)句，先檢查查詢緩存，如果命中，直接返回結(jié)果，否則進(jìn)行語(yǔ)句解析
  • 有一系列預(yù)處理，比如檢查語(yǔ)句是否寫正確了，然后是查詢優(yōu)化（比如是否使用索引掃描，如果是一個(gè)不可能的條件，則提前終止），生成查詢計(jì)劃，然后查詢引擎啟動(dòng)，開始執(zhí)行查詢，從底層存儲(chǔ)引擎調(diào)用API獲取數(shù)據(jù)，最后返回給客戶端。怎么存數(shù)據(jù)、怎么取數(shù)據(jù)，都與存儲(chǔ)引擎有關(guān)。
  • 然后，mysql默認(rèn)使用的BTREE索引，并且一個(gè)大方向是，無(wú)論怎么折騰sql，至少在目前來(lái)說，mysql最多只用到表中的一個(gè)索引。

5、Mysql存儲(chǔ)引擎

• 查看命令
  • 查看當(dāng)前的MySQL 提供什么存儲(chǔ)引擎
    • mysql> show engines;
  • 看你的 MySQL 當(dāng)前默認(rèn)的存儲(chǔ)引擎:
    • show variables like ‘%storage_engine%’;

• 默認(rèn)的存儲(chǔ)引擎

• MyISAM和InnoDB

  

  兩種引擎對(duì)比

• 阿里巴巴、淘寶用哪個(gè)
• • Percona 為 MySQL 數(shù)據(jù)庫(kù)服務(wù)器進(jìn)行了改進(jìn)，在功能和性能上較 MySQL 有著很顯著的提升。該版本提升了在高負(fù)載情況下的 InnoDB 的性能、為 DBA 提供一些非常有用的性能診斷工具；另外有更多的參數(shù)和命令來(lái)控制服務(wù)器行為。
  • 該公司新建了一款存儲(chǔ)引擎叫xtradb完全可以替代innodb,并且在性能和并發(fā)上做得更好,
  • 阿里巴巴大部分mysql數(shù)據(jù)庫(kù)其實(shí)使用的percona的原型加以修