linux學(xué)習(xí)第九周總結(jié)

本周的內(nèi)容重中之重，數(shù)據(jù)庫(kù)MySQL，

一、簡(jiǎn)介

MySQL是一個(gè)關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)，由瑞典MySQL AB 公司開(kāi)發(fā)，目前屬于 Oracle 旗下產(chǎn)品。MySQL 是最流行的關(guān)系型數(shù)據(jù)庫(kù)管理系統(tǒng)之一，在 WEB 應(yīng)用方面，MySQL是最好的 RDBMS (Relational Database Management System，關(guān)系數(shù)據(jù)庫(kù)管理系統(tǒng)) 應(yīng)用軟件。

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

MySQL所使用的 SQL 語(yǔ)言是用于訪問(wèn)數(shù)據(jù)庫(kù)的最常用標(biāo)準(zhǔn)化語(yǔ)言。MySQL 軟件采用了雙授權(quán)政策，分為社區(qū)版和商業(yè)版，由于其體積小、速度快、總體擁有成本低，尤其是開(kāi)放源碼這一特點(diǎn)，一般中小型網(wǎng)站的開(kāi)發(fā)都選擇 MySQL 作為網(wǎng)站數(shù)據(jù)庫(kù)。

由于其社區(qū)版的性能卓越，搭配 PHP 和 Apache 可組成良好的開(kāi)發(fā)環(huán)境。

應(yīng)用環(huán)境

與其他的大型數(shù)據(jù)庫(kù)例如 Oracle、DB2、SQL Server等相比，MySQL [1] 自有它的不足之處，但是這絲毫也沒(méi)有減少它受歡迎的程度。對(duì)于一般的個(gè)人使用者和中小型企業(yè)來(lái)說(shuō)，MySQL提供的功能已經(jīng)綽綽有余，而且由于 MySQL是開(kāi)放源碼軟件，因此可以大大降低總體擁有成本

系統(tǒng)特性編輯 編程語(yǔ)言提供了 API。這些編程語(yǔ)言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby,.NET和 Tcl 等。

二、安裝方式

1.RPM安裝     RPM包安裝     CentOS 7：安裝光盤(pán)直接提供         mariadb-server   服務(wù)器包         mariadb          客戶端工具包 CentOS 6 提高安全性     mysql_secure_installation         設(shè)置數(shù)據(jù)庫(kù)管理員root口令         禁止root遠(yuǎn)程登錄         刪除anonymous用戶帳號(hào)         刪除test數(shù)據(jù)庫(kù) 客戶端程序：     mysql: 交互式的CLI工具     mysqldump：備份工具，基于mysql協(xié)議向mysqld發(fā)起查詢請(qǐng)求，并將查得的所有數(shù)據(jù)轉(zhuǎn)換成insert等寫(xiě)操作語(yǔ)句保存文本文件中     mysqladmin：基于mysql協(xié)議管理mysqld     mysqlimport：數(shù)據(jù)導(dǎo)入工具 MyISAM存儲(chǔ)引擎的管理工具：     myisamchk：檢查MyISAM庫(kù)     myisampack：打包MyISAM表，只讀 服務(wù)器端程序     mysqld_safe     mysqld     mysqld_multi 多實(shí)例 ，示例：mysqld_multi --example mysql用戶賬號(hào)由兩部分組成：     'USERNAME'@'HOST‘ 說(shuō)明：     HOST限制此用戶可通過(guò)哪些遠(yuǎn)程主機(jī)連接mysql服務(wù)器         支持使用通配符：             % 匹配任意長(zhǎng)度的任意字符             172.16.0.0/255.255.0.0 或 172.16.%.%             _ 匹配任意單個(gè)字符 服務(wù)器監(jiān)聽(tīng)的兩種socket地址：     ip socket: 監(jiān)聽(tīng)在tcp的3306端口，支持遠(yuǎn)程通信     unix sock: 監(jiān)聽(tīng)在sock文件上，僅支持本機(jī)通信         如：/var/lib/mysql/mysql.sock)     說(shuō)明：host為localhost,127.0.0.1時(shí)自動(dòng)使用unix sock 偵聽(tīng)3306/tcp端口可以在綁定有一個(gè)或全部接口IP上 vim /etc/my.cnf     [mysqld]     skip-networking=1 關(guān)閉網(wǎng)絡(luò)連接，只偵聽(tīng)本地客戶端， 所有和服務(wù)器的交互都通過(guò)一個(gè)socket實(shí)現(xiàn)，socket的配置存放在/var/lib/mysql/mysql.sock） 可在/etc/my.cnf修改

2.二進(jìn)制安裝

二進(jìn)制格式安裝過(guò)程 (1) 準(zhǔn)備用戶     groupadd -r -g 306 mysql     useradd -r -g 306 -u 306 Cd /data/mysql mysql (2) 準(zhǔn)備數(shù)據(jù)目錄，建議使用邏輯卷     mkdir /data/mysql     chown mysql:mysql /data/mysql (3) 準(zhǔn)備二進(jìn)制程序     tar xf mariadb-VERSION-linux-x86_64.tar.gz -C /usr/local     cd /usr/local     ln -sv mariadb-VERSION mysql     chown -R root:mysql /usr/local/mysql/ (5)創(chuàng)建數(shù)據(jù)庫(kù)文件     cd /usr/local/mysql/     ./scripts/mysql_install_db --datadir=/data/mysql --user=mysql (6)準(zhǔn)備服務(wù)腳本，并啟動(dòng)服務(wù)     cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld     chkconfig --add mysqld     service mysqld start (7)PATH路徑     echo ‘PATH=/user/local/mysql/bin:$PATH’ > /etc/profile.d/mysql (8)安全初始化     /user/local/mysql/bin/mysql_secure_installation

3.編譯安裝

安裝包 yum install bison bison-devel zlib-devel libcurl-devel libarchive-devel boost-devel gcc gcc-c++ cmake ncurses-devel gnutls-devel libxml2-devel openssl-devel libevent-devel libaio-devel 做準(zhǔn)備用戶和數(shù)據(jù)目錄     useradd -r -s /sbin/nologin -d /data/mysql/ mysql     mkdir /data/mysql     chown mysql.mysql /data/mysql     tar xvf mariadb-10.2.18.tar.gz cmake 編譯安裝 cmake的重要特性之一是其獨(dú)立于源碼(out-of-source)的編譯功能，即編譯工作可以在另一個(gè)指定的目錄中而非源碼目錄中進(jìn)行，這可以保證源碼目錄不受任何一次編譯的影響，因此在同一個(gè)源碼樹(shù)上可以進(jìn)行多次不同的編譯，如針對(duì)于不同平臺(tái)編譯 編譯選項(xiàng):https://dev.mysql.com/doc/refman/5.7/en/source-configuration-options.html  cd mariadb-10.2.18/ cmake .  -DCMAKE_INSTALL_PREFIX=/app/mysql  -DMYSQL_DATADIR=/data/mysql/  -DSYSCONFDIR=/etc/mysql  -DMYSQL_USER=mysql  -DWITH_INNOBASE_STORAGE_ENGINE=1  -DWITH_ARCHIVE_STORAGE_ENGINE=1  -DWITH_BLACKHOLE_STORAGE_ENGINE=1  -DWITH_PARTITION_STORAGE_ENGINE=1  -DWITHOUT_MROONGA_STORAGE_ENGINE=1  -DWITH_DEBUG=0  -DWITH_READLINE=1  -DWITH_SSL=system  -DWITH_ZLIB=system  -DWITH_LIBWRAP=0  -DENABLED_LOCAL_INFILE=1  -DMYSQL_UNIX_ADDR=/data/mysql/mysql.sock  -DDEFAULT_CHARSET=utf8  -DDEFAULT_COLLATION=utf8_general_ci make && make install 提示：如果出錯(cuò)，執(zhí)行rm -f CMakeCache.txt 準(zhǔn)備環(huán)境變量     echo 'PATH=/app/mysql/bin:$PATH' > /etc/profile.d/mysql.sh     . /etc/profile.d/mysql.sh 生成數(shù)據(jù)庫(kù)文件     cd /app/mysql/     scripts/mysql_install_db --datadir=/data/mysql/ --user=mysql 準(zhǔn)備配置文件     cp /app/mysql/support-files/my-huge.cnf /etc/my.cnf 準(zhǔn)備啟動(dòng)腳本     cp /app/mysql/support-files/mysql.server /etc/init.d/mysqld 啟動(dòng)服務(wù)     chkconfig --add mysqld ;service mysqld start

三、管理數(shù)據(jù)庫(kù)和表

關(guān)系型數(shù)據(jù)庫(kù)的常見(jiàn)組件

數(shù)據(jù)庫(kù)操作

表

創(chuàng)建表：CREATE TABLE

字段信息

查看所有的引擎：SHOW ENGINES

四、數(shù)據(jù)類型：

數(shù)據(jù)長(zhǎng)什么樣

數(shù)值類型 日期/時(shí)間類型 字符串(字符)類型 https://dev.mysql.com/doc/refman/5.5/en/data-types.html 1、整型

2、浮點(diǎn)型(float和double)，近似值

4、字符串(char,varchar,_text)

5.二進(jìn)制數(shù)據(jù)：BLOB

6.日期時(shí)間類型

示例

CREATE TABLE students (id int UNSIGNED NOT NULL PRIMARY KEY,name VARCHAR（20）NOT NULL,age tinyint UNSIGNED); DESC students; CREATE TABLE students2 (id int UNSIGNED NOT NULL ,name VARCHAR(20) NOT NULL,age tinyint UNSIGNED,PRIMARY KEY(id,name));

表操作

DROP TABLE [IF EXISTS] 'tbl_name'; ALTER TABLE 'tbl_name' 字段：     添加字段：add         ADD col1 data_type [FIRST|AFTER col_name]     刪除字段：drop     修改字段：         alter（默認(rèn)值）, change（字段名）, modify（字段屬性） 索引:     添加索引：add index     刪除索引：drop index 表選項(xiàng) 修改: 查看表上的索引：SHOW INDEXES FROM [db_name.]tbl_name; 查看幫助：Help ALTER TABLE

修改表示例

ALTER TABLE students RENAME s1; ALTER TABLE s1 ADD phone varchar(11) AFTER name; ALTER TABLE s1 MODIFY phone int; ALTER TABLE s1 CHANGE COLUMN phone mobile char(11); ALTER TABLE s1 DROP COLUMN mobile; ALTER TABLE s1 character set utf8; ALTER TABLE s1 change name name varchar(20) character set utf8; Help ALTER TABLE 查看

多表查詢

交叉連接：笛卡爾乘積

用于FROM子句中的子查詢

五、函數(shù)，存儲(chǔ)過(guò)程和觸發(fā)器

1.函數(shù)：系統(tǒng)函數(shù)和自定義函數(shù)

DELIMITER // CREATE FUNCTION deleteById(uid SMALLINT UNSIGNED) RETURNS VARCHAR(20) BEGIN DELETE FROM students WHERE stuid = uid; RETURN (SELECT COUNT(stuid) FROM students); END// DELIMITER ;

2.存儲(chǔ)過(guò)程

delimiter // CREATE PROCEDURE showTime() BEGIN SELECT now(); END// delimiter ; CALL showTime

創(chuàng)建含參存儲(chǔ)過(guò)程：只有一個(gè)IN參數(shù)

delimiter // CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED) BEGIN SELECT * FROM students WHERE stuid = uid; END// delimiter ; call selectById(2);

示例

delimiter // CREATE PROCEDURE dorepeat(n INT) BEGIN SET @i = 0; SET @sum = 0; REPEAT SET @sum = @sum+@i; SET @i = @i + 1; UNTIL @i > n END REPEAT; END// delimiter ; CALL dorepeat(100); SELECT @sum;

創(chuàng)建含參存儲(chǔ)過(guò)程:包含IN參數(shù)和OUT參數(shù)

delimiter // CREATE PROCEDURE deleteById(IN uid SMALLINT UNSIGNED, OUT num SMALLINT UNSIGNED) BEGIN DELETE FROM students WHERE stuid >= uid; SELECT row_count() into num; END// delimiter ; call deleteById(2,@Line); SELECT @Line;

說(shuō)明:創(chuàng)建存儲(chǔ)過(guò)程deleteById,包含一個(gè)IN參數(shù)和一個(gè)OUT參數(shù).調(diào)用時(shí),傳入刪除的ID和保存被修改的行數(shù)值的用戶變量@Line,select @Line;輸出被影響行數(shù)

3.流程控制

CREATE [DEFINER = { user | CURRENT_USER }] TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_body

說(shuō)明：

觸發(fā)器示例

CREATE TABLE student_info ( stu_id INT(11) NOT NULL AUTO_INCREMENT, stu_name VARCHAR(255) DEFAULT NULL, PRIMARY KEY (stu_id) ); CREATE TABLE student_count ( student_count INT(11) DEFAULT 0 ); INSERT INTO student_count VALUES(0);

示例：創(chuàng)建觸發(fā)器，在向?qū)W生表INSERT數(shù)據(jù)時(shí)，學(xué)生數(shù)增加，DELETE學(xué)生時(shí)，學(xué)生數(shù)減少

CREATE TRIGGER trigger_student_count_insert AFTER INSERT ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count+1; CREATE TRIGGER trigger_student_count_delete AFTER DELETE ON student_info FOR EACH ROW UPDATE student_count SET student_count=student_count-1;

查看觸發(fā)器

六、mysql用戶和權(quán)限管理

創(chuàng)建用戶：CREATE USER

七、存儲(chǔ)引擎

MyISAM引擎特點(diǎn)

InnoDB數(shù)據(jù)庫(kù)文件

所有InnoDB表的數(shù)據(jù)和索引放置于同一個(gè)表空間中 表空間文件：datadir定義的目錄下 數(shù)據(jù)文件：ibddata1, ibddata2, ... 每個(gè)表單獨(dú)使用一個(gè)表空間存儲(chǔ)表的數(shù)據(jù)和索引 啟用：innodb_file_per_table=ON 參看：https://mariadb.com/kb/en/library/xtradbinnodb-server-system-variables/#innodb_file_per_table ON (>= MariaDB 5.5) 兩類文件放在數(shù)據(jù)庫(kù)獨(dú)立目錄中 數(shù)據(jù)文件(存儲(chǔ)數(shù)據(jù)和索引)：tb_name.ibd 表格式定義：tb_name.frm

其它存儲(chǔ)引擎

MySQL中的系統(tǒng)數(shù)據(jù)庫(kù) sql數(shù)據(jù)庫(kù)

mysqld選項(xiàng)，服務(wù)器系統(tǒng)變量和服務(wù)器狀態(tài)變量

服務(wù)器配置

八、mysql架構(gòu)

查詢緩存

哪些查詢可能不會(huì)被緩存

查詢緩存相關(guān)的服務(wù)器變量

SELECT語(yǔ)句的緩存控制

命中率和內(nèi)存使用率估算 100% 100%

InnoDB存儲(chǔ)引擎

索引

索引類型：

B+TREE索引

B+Tree索引的限制：

索引優(yōu)化策略：

索引優(yōu)化建議

SQL語(yǔ)句性能優(yōu)化 就不用，盡量寫(xiě)全字段名

管理索引

CREATE INDEX [UNIQUE] index_name ON tbl_name (index_col_name[(length)],...); ALTER TABLE tbl_name ADD INDEX index_name(index_col_name); help CREATE INDEX;

刪除索引：

DROP INDEX index_name ON tbl_name; ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

優(yōu)化表空間：

OPTIMIZE TABLE tb_name;

查看索引的使用

并發(fā)控制

鎖策略：在鎖粒度及數(shù)據(jù)安全性尋求的平衡機(jī)制 顯式使用鎖     LOCK TABLES 加鎖         tbl_name [[AS] alias] lock_type         [, tbl_name [[AS] alias] lock_type] ...         lock_type: READ ， WRITE         UNLOCK TABLES 解鎖 FLUSH TABLES [tb_name[,...]] [WITH READ LOCK]     關(guān)閉正在打開(kāi)的表（清除查詢緩存），通常在備份前加全局讀鎖 SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]     查詢時(shí)加寫(xiě)或讀鎖

事務(wù)

事務(wù)Transactions：一組原子性的SQL語(yǔ)句，或一個(gè)獨(dú)立工作單元 事務(wù)日志：記錄事務(wù)信息，實(shí)現(xiàn)undo,redo等故障恢復(fù)功能 ACID特性：     A：atomicity原子性；整個(gè)事務(wù)中的所有操作要么全部成功執(zhí)行，要么全部失敗后回滾     C：consistency一致性；數(shù)據(jù)庫(kù)總是從一個(gè)一致性狀態(tài)轉(zhuǎn)換為另一個(gè)一致性狀態(tài)     I：Isolation隔離性；一個(gè)事務(wù)所做出的操作在提交之前，是不能為其它事務(wù)所見(jiàn)；隔離有多種隔離級(jí)別，實(shí)現(xiàn)并發(fā)     D：durability持久性；一旦事務(wù)提交，其所做的修改會(huì)永久保存于數(shù)據(jù)庫(kù)中

啟動(dòng)事務(wù)：

事務(wù)隔離級(jí)別

并發(fā)控制

還有日志管理 ，備份還原，mysql集群在下周總結(jié)