一，表設(shè)計
　　1.1. E-R（entity relation）實(shí)體關(guān)系圖
　　　　長方形 實(shí)體 表
　　　　橢圓形 屬性 字段
　　　　菱形 關(guān)系 一對一 多對一 屬于 多對多
　　1.2. 三范式標(biāo)準(zhǔn)
　　　　原子性
　　　　　　個人信息
　　　　　　省市縣鄉(xiāng)鎮(zhèn)
　　　　唯一性
　　　　　　主鍵 id
　　　　無冗余性
　　　　　　訂單表中的商品名稱與價格應(yīng)該關(guān)聯(lián)查詢商品表
　　　　三范式并不絕對。
　　1.3. 選擇合適的存儲引擎
　　　　查詢效率
　　　　　　myisam快
　　　　　　innodb慢
　　　　事務(wù)
　　　　　　myisam不支持
　　　　　　innodb支持
　　　　全文索引
　　　　　　myisam支持
　　　　　　innodb不支持
　　　　　　兄弟連，研究生命，和特朗普通話，別插嘴。
　　　　鎖機(jī)制
　　　　　　myisam表鎖
　　　　　　　　即鎖定一張表，如果進(jìn)行讀操作，則其他進(jìn)程不允許寫，如果進(jìn)行寫操作，則其他進(jìn)程不允許讀，更不允許寫
　　　　　　innodb行鎖
　　　　　　　　即鎖定一條記錄，其他進(jìn)程可以對其他記錄進(jìn)行讀寫操作，
　　　　文件存儲區(qū)別
　　　　　　myisam有三個文件
　　　　　　　　.frm
　　　　　　　　.myd
　　　　　　　　.myi
　　　　　　innodb有兩個文件
　　　　　　　　.frm
　　　　　　　　.idb
　　　　　　總結(jié)
　　　　　　　　查詢頻繁的使用myisam，例如新聞系統(tǒng)
　　　　　　　　安全性要求高的使用innodb，例如商城系統(tǒng)

　　注意： 選擇合適的字段類型
　　　　數(shù)字->時間->枚舉->字符串
　　　　盡量不要使用 null 類型
二，備份
　　2.1. 普通備份
　　　　mysqldump -uroot -p123456 database > /tmp/bak.sql
　　　　mysqldump -uroot -p123456 --all-databases --events > /tmp/bak.sql
　　計劃任務(wù)
　　　　linux
　　　　windows
　　2.2. 增量備份
　　　　增量備份（incremental backup）是備份的一個類型，備份上一次備份后的所有有變化的文件。
　　　　1.?配置
　　　　　　vim /etc/my.cnf
　　　　　　log-bin=bin-log
　　　　　　開啟二進(jìn)制日志
　　　　2.?查看
　　　　　　/usr/local/mysql/data/mysql-bin.000001
　　　　　　二進(jìn)制日志文件位置
　　　　　　mysqlbinlog mysql-bin.000001
　　　　3. 恢復(fù)
　　　　　　（1）通過時間
　　　　　　　　mysqlbinlog --stop-datetime="2017-05-06 06:01:30" mysql-bin.000001 | mysql -uroot -p
　　　　　　（2）通過位置
　　　　　　　　mysqlbinlog --stop-position="3068" mysql-bin.000001 |mysql -uroot -p
　　　　4. 重置
　　　　　　reset master
　　　　5.?案例
　　　　　　CREATE DATABASE lizhaohui;
　　　　　　USE lizhaohui;
　　　　　　CREATE TABLE users(id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50) NOT NULL);
　　　　　　reset master
　　　　　　在創(chuàng)建完庫表之后，再執(zhí)行二進(jìn)制日志文件記錄。
　　　　　　INSERT INTO users(name) VALUE('aaaaa');
　　　　　　INSERT INTO users(name) VALUE('bbbbb');
　　　　　　INSERT INTO users(name) VALUE('ccccc');
　　　　　　INSERT INTO users(name) VALUE('ddddd');
　　　　　　INSERT INTO users(name) VALUE('eeeee');
　　　　　　INSERT INTO users(name) VALUE('fffff');
三，優(yōu)化
　　3.1. 定位慢語句
　　　　vim /etc/my.cnf
　　　　　　[mysqld]
　　　　　　slow-query-log=1 # 開啟記錄慢查詢的操作
　　　　　　slow-query-log-file=slow.log #文件默認(rèn)存放在mysql的data目錄下
　　　　　　long-query-time=1 #不能使用小數(shù)
　　　　重啟 mysql
　　　　　　mysqladmin -uroot -p shutdown
　　　　　　mysqld_safe -u mysql &
　　　　快速插入數(shù)據(jù)
　　　　　　INSERT INTO users(name) SELECT name FROM users;
　　3.2. 處理
　　　　1.?增加，刪除，修改
　　　　　　查看服務(wù)器 cpu 和 內(nèi)存使用率
　　　　　　top命令
　　　　　　一張表記錄不要超過100萬條
　　　　　　　　SELECT COUNT(*) FROM users;
　　　　　　檢查表索引不宜過多
　　　　　　　　SHOW INDEX FROM users;
　　　　　　分庫分表
　　　　2.?查詢
　　　　　　緩存
　　　　　　語句分析
　　　　　　　　explain 指令
　　　　　　　　　　EXPLAIN SELECT * FROM users;
　　　　　　　　type（連接類型）
　　　　　　　　　　好壞順序
　　　　　　　　　　　　system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
　　　　　　　　　　system
　　　　　　　　　　const
　　　　　　　　　　　　表中滿足條件的記錄最多一條，使用主鍵或者 唯一索引查詢
　　　　　　　　　　　　EXPLAIN SELECT * FROM users WHERE id=1;
　　　　　　　　　　eq_ref
　　　　　　　　　　　　某一列等于帶索引的列
　　　　　　　　　　　　CREATE TABLE IF NOT EXISTS t1(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) 　　　　　　　　　　　　ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　　　　　　　　　　　CREATE TABLE IF NOT EXISTS t2(id INT NOT NULL AUTO_INCREMENT,name VARCHAR(50) NOT NULL, PRIMARY KEY(id)) 　　　　　　　　　　　　ENGINE=InnoDB DEFAULT CHARSET=utf8;
　　　　　　　　　　　　EXPALIN SELECT * FROM t1, t2 WHERE t1.id = t2.id;
　　　　　　　　　　　　注意：當(dāng)數(shù)據(jù)量足夠大時，才會出現(xiàn)此連接類型。
　　　　　　　　　　ref
　　　　　　　　　　　　使用普通索引進(jìn)行查詢
　　　　　　　　　　　　SELECT * FROM users WHERE name = 'admin';
　　　　　　　　　　ref_or_null
　　　　　　　　　　　　通過普通索引檢索,并且會檢索null值
　　　　　　　　　　　　EXPLAIN SELECT * FROM users WHERE name = 'amdin' or name = null;
　　　　　　　　　　range
　　　　　　　　　　　　范圍
　　　　　　　　　　　　EXPLAIN SELECT * FROM users WHERE id<3;
　　　　　　　　　　index
　　　　　　　　　　　　跟all一樣,不過只會掃描索引.
　　　　　　　　　　　　EXPLAIN SELECT id FROM users;
　　　　　　　　　　all
　　　　　　　　　　　　全表掃描
　　　　　　　　　　　　EXPLAIN SELECT * FROM users;
　　　　　　索引優(yōu)化
　　　　　　　　創(chuàng)建
　　　　　　　　主鍵
　　　　　　　　　　CREATE TABLE test(id INT PRIMATY KEY AUTO_INCREMENT,name VARCHAR(50));
　　　　　　　　　　ALTER TABLE test ADD PRIMARY KEY(id);
　　　　　　　　普通索引
　　　　　　　　　　CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50), INDEX name(name));
　　　　　　　　　　ALTER TABLE test ADD INDEX name(name);
　　　　　　　　唯一索引
　　　　　　　　　　CREATE TABLE test(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50), UNIQUE name(name));
　　　　　　　　　　ALTER TABLE test ADD UNIQUE name(name);
　　　　　　　　刪除
　　　　　　　　主鍵
　　　　　　　　　　ALTER TABLE test MODIFY id INT(10);
　　　　　　　　　　ALTER TABLE test DROP PRIMARY KEY;
　　　　　　　　非主鍵
　　　　　　　　　　ALTER TABLE test DROP INDEX name;
　　　　　　　　查看
　　　　　　　　　　SHOW INDEX FROM test;
　　　　　　　　　　注意：索引，利弊，索引是一把雙刃劍。
　　　　多機(jī)配置
　　　　　　主從
　　　　　　　　好處
　　　　　　　　　　單向備份
　　　　　　　　　　讀寫分離，提高效率
　　　　　　　　　　原理

實(shí)驗(yàn)
在主服務(wù)器上操作
1. 啟動binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=101
3. 重啟mysql
service mysqld restart
4. 查看二進(jìn)制日志是否開啟
show global variables like '%log%';
5. 在 主服務(wù)器上 授權(quán)
grant replication slave on *.* to 'zhang'@'192.168.103.102' identified by '123456';
6. 查看主機(jī)信息
show master status;
在從服務(wù)器上操作
1. 啟動binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=102
3. 重啟mysql
service mysqld restart
4. 查看二進(jìn)制日志是否開啟
show global variables like '%log%';
5. 在 從服務(wù)器上 連接
change master to master_host='192.168.103.101',master_user='zhang',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=263;
6. 啟動 sql 線程
show slave status\G;
start slave;
主主
好處
雙向備份
高可用
負(fù)載均衡
原理
兩臺機(jī)器互為主從
實(shí)驗(yàn)
在主服務(wù)器上操作
1. 啟動binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=101
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=1
3. 重啟 mysql
service mysqld restart
4. 以 101 為主，以 102 為從
grant replication slave on *.* to 'zhang'@'192.168.103.102' identified by '123456';
show master status;
change master to master_host='192.168.103.101',master_user='zhang',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;
5. 查看 SQL線程
show slave status\G;
start slave;
在從服務(wù)器上操作
1. 啟動binlog日志
vi /etc/my.cnf
2. 在文件中添加
log-bin=mysql-bin
server-id=102
replicate-do-db=test
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
auto-increment-increment=2
auto-increment-offset=2
3. 重啟 mysql
service mysqld restart
4. 以 102 為主，以 101 為從
grant replication slave on *.* to 'zhang'@'192.168.103.101' identified by '123456';
show master status;
change master to master_host='192.168.103.102',master_user='zhang',master_password='123456',master_log_file='mysql-bin.000001',master_log_pos=106;
5. 查看 SQL線程
show slave status\G;
start slave;
中文分詞。
sphinx。=> coreseek。
scws。

轉(zhuǎn)載于:https://www.cnblogs.com/peilanluo/p/6995176.html

總結(jié)

以上是生活随笔為你收集整理的【mysql】mysql优化的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。