mysql實戰總結

應用層面的操作數據庫主要需要了解的內容有:

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

-

安裝數據庫：

可以下載官網已經編譯好的安裝包，環境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用戶密碼
啟動服務后，root用戶會創建一個臨時密碼存放在/var/log/mysqld.log里面，可以通過
grep 'temporary password' /var/log/mysqld.log 進行查看
然后使用 mysql -uroot -p進行登錄
登錄時如果臨時密碼有特殊符號的，可以用單引號括起來'myPassword'
然后
ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!';
刷新權限：flush privileges;
-

授權某個用戶訪問數據庫
grant all privileges on 數據庫.表 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文件重新加載數據：mysql < my.sql 或者mysql>source my.sql
使用分隔符形式備份數據：
mysqldump --tab=/tmp db1會使用兩種格式保存兩個文件，一種txt一種sql
參數：--fields-terminated-by=str，--fields-enclosed-by=char，--fields-optionally-enclosed-by=char，--fields-escaped-by=char，--lines-terminated-by=str
恢復分隔符形式的備份文件：
mysql db1<t1.sql
mysqlimport db1 t1.txt
或者
mysql>use db1;
mysql> load data infile 't1.txt' into table t1;

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

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

主從配置

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

優化

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

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

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

exist：如果子查詢有返回，則返回數據，如果子查詢沒有內容則沒有返回

優化子查詢：
1：使用可以影響子查詢行數或順序的子查詢語句，就是讓子查詢語句過濾多點數據
2：使用子查詢來替換join連接
3：把外部語句移入內部子查詢
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：使用一個行查詢來替代相關子查詢：
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的內容：
id select_type table type possible_keys key key_len ref rows Extra

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