mysql實戰(zhàn)總結(jié)

應(yīng)用層面的操作數(shù)據(jù)庫主要需要了解的內(nèi)容有:

1. 安裝啟動和連接數(shù)據(jù)庫，修改數(shù)據(jù)庫的管理密碼管理權(quán)限
2. 數(shù)據(jù)庫的備份與恢復(fù)，全量備份，增量備份，物理備份，邏輯備份
3. 數(shù)據(jù)庫的高可用部署：集群
4. 數(shù)據(jù)庫的優(yōu)化：數(shù)據(jù)庫操作系統(tǒng)硬件層的優(yōu)化，數(shù)據(jù)庫軟件配置層面的優(yōu)化，sql語句層面的優(yōu)化，數(shù)據(jù)索引層面的優(yōu)化。

-

安裝數(shù)據(jù)庫：

可以下載官網(wǎng)已經(jīng)編譯好的安裝包，環(huán)境centos
yum 安裝：mysql-community
先下載npm包
安裝npm包： rpm -Uvh mysql57-community-release-el7-9.noarch.rpm
安裝mysql：yum install mysql-community-server
啟動mysql：systemctl start mysqld
初始安裝后，修改root用戶密碼
啟動服務(wù)后，root用戶會創(chuàng)建一個臨時密碼存放在/var/log/mysqld.log里面，可以通過
grep 'temporary password' /var/log/mysqld.log 進行查看
然后使用 mysql -uroot -p進行登錄
登錄時如果臨時密碼有特殊符號的，可以用單引號括起來'myPassword'
然后
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
刷新權(quán)限：flush privileges;
-

授權(quán)某個用戶訪問數(shù)據(jù)庫
grant all privileges on 數(shù)據(jù)庫.表 to '用戶名'@'ip' identified by '密碼'
grant all privileges on . to 'root'@'%' identified by '密碼'//%百分號表示任意地址

-

備份與還原

使用mysqldump備份：
使用sql格式備份：mysqldump --all-databases > my.sql
mysqldump --databases db1 db2 > my.sql
通過sql文件重新加載數(shù)據(jù)：mysql < my.sql 或者mysql>source my.sql
使用分隔符形式備份數(shù)據(jù)：
mysqldump --tab=/tmp db1會使用兩種格式保存兩個文件，一種txt一種sql
參數(shù)：--fields-terminated-by=str，--fields-enclosed-by=char，--fields-optionally-enclosed-by=char，--fields-escaped-by=char，--lines-terminated-by=str
恢復(fù)分隔符形式的備份文件：
mysql db1<t1.sql
mysqlimport db1 t1.txt
或者
mysql>use db1;
mysql> load data infile 't1.txt' into table t1;

如何復(fù)制一個數(shù)據(jù)庫
mysqldump db1 > dump.sql
mysql db2 < dump.sql
不要再mysqldump中使用--database，這會導(dǎo)致use db1的語句存在dump文件中，從而覆蓋db2的數(shù)據(jù)庫名。
如何復(fù)制一個服務(wù)器的數(shù)據(jù)庫到另外一個服務(wù)器：
mysqldump --databases db1 > dump.sql
在服務(wù)器二：mysql>dump.sql
使用--databases是因為會包含create database語句
如何備份存儲過程（存儲過程，函數(shù)，觸發(fā)器，事件）
加上參數(shù) --events --routines（存儲過程和函數(shù)） --triggers
如何存儲定義和數(shù)據(jù)分隔：
--no-data不會復(fù)制表格數(shù)據(jù)，--no-create-info：
升級兼容性測試：
mysqldump --all-databases --no-data --routines --events > dump-defs.sql
在升級的服務(wù)器中mysql < dump-defs.sql

使用二進制日志進行時間點恢復(fù)（增量恢復(fù)）：
查看二進制日志列表：show binary logs
查看二進制日志的名字：show master status
恢復(fù)二進制日志：mysqlbinlog mylog | mysql-uroot -p
查看二進制日志信息：mysqlbinlog mylog | more
如果要處理多個二進制日志，每個文件單獨一條語句時一種危險的做法，應(yīng)該在一條語句中執(zhí)行
mysqlbinlog log1 log2 log3 | mysql -uroot -p
或者使用追加的方式：
mysqlbinlog log1 >my.sql
mysqlbinlog log2 >>my.sql
然后使用：mysql -uroot -p -e "source my.sql"
可以使用--skip-gtids忽略全局事務(wù)
恢復(fù)時間點：
使用--start-datetime和--stop-datetime來指定恢復(fù)的時間：
mysqlbinlog --stop-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p
mysqlbinlog --start-datetime="2017-04-20 9:59:59" mylog | mysql -uroot -p
使用--start-position和--stop-position來指定恢復(fù)的位置：
mysqlbinlog --stop-position=154 mylog | mysql -uroot -p
mysqlbinlog --start-position=209 mylog | mysql -uroot -p

主從配置

原理就是使用二進制日志，一主兩從，主服務(wù)器在保存數(shù)據(jù)之前會寫入一個binlog記錄，里面包含了數(shù)據(jù)操作的記錄，這個記錄會被同步到從服務(wù)器，然后從服務(wù)器會啟動sql線程將這些記錄同步進數(shù)據(jù)庫
操作過程：
1：分別配置主從服務(wù)器的my.cnf文件，添加server-id=1,server-id=2,server-id=3....
主節(jié)點上添加log-bin=mylog，開啟二進制文件
2：主節(jié)點上添加復(fù)制用的用戶
grant replication slave on . to 'repl'@'%' identified by 'myPassword';
查看主節(jié)點狀態(tài)：show master status;此時會顯示日志文件名和position
3：給各個服務(wù)器打開3306端口：
firewall-cmd --zone=public --add-port=3306/tcp --permanent
firewall-cmd --reload
4：在各個從節(jié)點上輸入
change master to
master_host='主節(jié)點ip', //逗號要有
master_user='repl',
master_password='myPassword',
master_log_file='mylog.000001', //這里的文件名是從主節(jié)點上查看到的文件名
master_log_pos=154; //這里的位置是從主節(jié)點上查看到的
成功后使用start slave;進行啟動
然后查看從節(jié)點信息：show slave status\G，如果
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
都為是則說明正常，如果是否則可能有不對的配置，查看端口，log_pos位置等。
主從復(fù)制的具體配置my.cnf
server-id=1
log-bin=/var/log/mysql/mysql-bin //二進制日志的存放位置
binlog-do-db=test //需要同步的庫
binlog-ignore-db=mysql //不記錄日志的庫，即不需要同步的庫
log-slave-updates //開啟從節(jié)點上的日志功能
sync_binlog=1 //經(jīng)過1次日志寫操作把日志文件寫入硬盤一次，最安全但效率最低，默認是0
#auto_increment //控制自增列的行為

優(yōu)化

單表優(yōu)化：
字段：
1：盡量使用tinyint，smallint，medium_int作為整數(shù)類型而非INT，非負的加上UNSIGNED
2：varchar長度只分配需要的空間
3：使用枚舉或整數(shù)代替字符串類型
4：使用timestamp，不使用datetime
5：單表字段建議在20以內(nèi)
6：用整型來存ip
索引：
1：考慮在where和order by的列上建立索引
2：值分布很稀少的字段不適合建索引
3：字符字段不要做主鍵
4：不用外鍵，由程序保證約束
5：不用unique，由程序保證約束
優(yōu)化sql語句的執(zhí)行效率的方法：
1：盡量選擇較小的列
2：將where中用的比較頻繁的字段建立索引
3：select子句中避免使用 *
4：避免在索引列上使用計算，not， in 和<>操作
5：當(dāng)只需要一行數(shù)據(jù)的時候使用limit 1
6：保證表單數(shù)據(jù)不超過200W，適時分隔表

不做列運算，任何對列的操作都導(dǎo)致表掃描，查詢時把操作移到等號右邊。sql語句盡可能簡單，一條sql只能在一個cpu上運行，大語句拆小語句。or改成in，or的效率是n級別，in的效率是log(n)的級別，in的個數(shù)建議在200內(nèi)

-
行子查詢：可以使用多個列作為判斷條件
select * from t1 where (col1,col2) in (select col3,col4 from t2)

exist：如果子查詢有返回，則返回數(shù)據(jù)，如果子查詢沒有內(nèi)容則沒有返回

優(yōu)化子查詢：
1：使用可以影響子查詢行數(shù)或順序的子查詢語句，就是讓子查詢語句過濾多點數(shù)據(jù)
2：使用子查詢來替換join連接
3：把外部語句移入內(nèi)部子查詢
SELECT FROM t1
WHERE s1 IN (SELECT s1 FROM t1 UNION ALL SELECT s1 FROM t2);
替換：
SELECT FROM t1
WHERE s1 IN (SELECT s1 FROM t1) OR s1 IN (SELECT s1 FROM t2);
又如：
SELECT (SELECT column1 + 5 FROM t1) FROM t2;
替換
SELECT (SELECT column1 FROM t1) + 5 FROM t2;
4：使用一個行查詢來替代相關(guān)子查詢：
SELECT FROM t1
WHERE (column1,column2) IN (SELECT column1,column2 FROM t2);
替換：
SELECT FROM t1
WHERE EXISTS (SELECT * FROM t2 WHERE t2.column1=t1.column1
AND t2.column2=t1.column2);
5：使用not (a = any()) 比 a <> all()好
6：使用x = any (table containing (1,2)) 比x=1 or x =2好
7：使用 = any比exists好
8：如果記錄只返回一行，那么使用=比in好

explain查詢分析器

explain的內(nèi)容：
id select_type table type possible_keys key key_len ref rows Extra

id：執(zhí)行編號，如果沒有嵌套或者子查詢，只有唯一的select，那么每行都顯示1
select_type：顯示本行查詢類型：
simple：簡單子查詢，不包含子查詢和union
primary：包含union或子查詢
subquery：子查詢
derived：派生表
union：
union result：匿名臨時表
dependent union：
subquery：
dependent subquery：
table：對應(yīng)行賑災(zāi)訪問的表名或別名
type：訪問類型：
all：全表掃描
index：掃描表的時候按索引此塤掃描，而不是逐行
range：范圍掃描，key列會顯示使用了哪個索引。使用=，<>，>，》=,《,<=， BETWEEN或I時可以使用range
ref：索引訪問，返回所有所有匹配的單個值的行，只有使用非唯一性索引或唯一性索引非唯一性前綴時才會發(fā)生。
eq_ref：使用唯一索引或主鍵查詢時才出現(xiàn)
const：確定最多只有一行匹配的時候，mysql優(yōu)化器會在查詢前讀取且只讀取一次
system：const連接的一種特列，表僅有一行滿足條件
Null：在執(zhí)行節(jié)點甚至用不到訪問表或索引（高效）
possible_keys：顯示使用了哪些索引
key：實際決定使用的鍵（索引），如果沒有選擇索引則為Null
key_len：使用的鍵長度，長度越短越好
ref：賢惠使用哪個列或常熟與key一起從表中選擇行
rows：執(zhí)行查詢必須檢查的行數(shù)，這是一個預(yù)估值
extra：重要補充信息：
using filesort：使用索引排序
using temporary：用臨時表保存中間結(jié)果
not exists：一旦匹配了left join就不再搜索
using index：使用覆蓋索引，不需要讀取數(shù)據(jù)文件，如果同時出現(xiàn)了using where，表明
using index conditon：
using where：
using join buffer：
impossible where：
select tables optimized away：
distinct：