各種mysql視頻的學習筆記

標籤：service   二進位   資料庫   mysql   start   

開啟二進位日誌（二進位日誌內容：更改資料庫的操作）

在/etc/mysql/my.cnf中

將log_bin這一行的注釋去掉就開啟了後面的是儲存的路徑

下面的expire_logs_days是過多久，Mysql自動刪除二進位日誌

max_binlog_size為最大二進位日誌大小

注意重啟mysql後剛剛的修改才生效

shell>sudo service mysql stop

shell>sudo service mysql start

然後登入mysql

每次登陸後都會產生二進位日誌

我的二進位日誌具體為

mysql-bin.000001（這裡的mysql為主機名稱字）

可以在MySQL裡用：SHOW BINARY LOGS來查看

查看具體內容需要在shell裡用：例如

mysqlbinlog /var/log/mysql/mysql-bin.0000002

來查看

刪除所有記錄檔（還會產生一個新的二進位記錄檔）：

mysql>RESET MASTER;

刪除指定的記錄檔：

mysql>PURGE MASTER LOGS TO ‘mysql-bin.0000002‘;

to是到的意思所以上面那句為刪除mysql-bin.0000001

還可以根據時間來部分刪除

:mysql>PURGE MASTER LOGS BEFORE ‘20150531‘;

從二進位日誌中恢複資料：

shell>mysqlbinlog --stopdatetime="2015-05-31 10:18:15"/var/log/mysql/mysql-bin.000002|mysql -u root -p

其中時間為那個二進位日誌裡的時間

暫停二進位日誌：

mysql>set sql_log_bin=OFF;

恢複二進位日誌：

mysql>set sql_log_bin=ON;

【執行flush logs的影響：會多產生一個新的記錄檔】

所以上面的指令要在開啟了二進位日誌時候才能用。

開啟錯誤記錄檔（尾碼為.err）

同樣在my.cnf裡讓log_error的注釋取消

查看錯誤記錄檔所在的路徑

mysql>show variables like ‘log_error‘

刪除錯誤記錄檔：

shell>mysqladmin -u root -p flush-logs

或者：

mysql>flush logs

啟動通用查詢日誌（通用查詢日誌：使用者對資料庫的每一步操作）：

同理在[mysqld]

裡加：log

刪除/其實是重新建立：

mysqladmin -u root -p flush-logs

啟動慢查詢日誌（慢查詢日誌內容：執行時間超過long_query_time的查詢或者不使用索引的查詢）

啟動設定慢查詢日誌：與前面的同

在my.cnf裡添加：log-slow-queries(後面不加指定路徑即使用其預設的data路徑)

重建慢查詢記錄檔

shell>mysqladmin -u root -p flush-logs

mysql>flush logs

【慢查詢日誌與效能最佳化相關】

分析查詢語句

1）explain [extended] select select_options

2)describe（可以簡寫為desc） select select_options

查看錶的資訊[show create table 表名]

什麼時候索引沒用

1）用like查詢且%在開頭

2）多列索引直接用第二個欄位來查詢，索引不起作用

例如：create index index_id_price on fruits(f_id,f_price);

explain select * from fruits where f_price=5.2;

這條select 語句中索引不起作用。

3）使用or關鍵字的查詢語句前後不是索引列時

最佳化子查詢：

子查詢的效率不高，用串連（join）查詢+索引來替代子查詢

最佳化資料庫結構

1）將欄位很多的表分解成多個表：將常用的欄位分離出來成為新表

2）增加中間表：適用於經常需要聯集查詢的表

3）合理增加冗餘欄位：

4）最佳化插入記錄的速度：採用批量插入，使用load data infile 大量匯入，插入時禁用索引（set改參數的值就行了），插入前禁用唯一性檢查

分析表：analyze table 表名,分析期間加了唯讀鎖

檢查表：check table 表名，檢查期間加了唯讀鎖

最佳化表：optimize table 表名,最佳化片段，最佳化期間加上唯讀鎖

最佳化伺服器：

最佳化參數：詳見show variables那裡的解釋，記得配置後重啟mysql服務

可用？+命令來查看命令

mysql內建字串函數

concat（）串連

lcase（）轉成小寫

ucase（）轉成大寫

replace(str,search_str,replace_str)替換

substring(str,position,length)取子字串

space(count)產生空格

ltrim（）去左邊的空格

repeat(string,count)重複count次

mysql內建數學函數

BIN（）十進位轉二進位

ceiling（）向上取整

floor()向下取整

max()

min()

sqrt()開平方

rand()0-1內的隨機值

mysql內建日期函數

curdate()

curtime()

time

week(date)返回日期為第幾周

year(date)

datediff(exp1,exp2)兩個日期之間差的天數

mysql預先處理語句舉例(用於最佳化)

mysql> prepare stmt1 from ‘select * from t where id>?‘;

mysql> set @i=1;

mysql> execute stmt1 using @i;

mysql> drop prepare stmt1;

mysql的交易處理舉例（innodb專屬，用於最佳化）

mysql> set autocommit=0;

mysql> delete from t where id=11;

mysql> savepoint p1;

mysql> delete from t where id=10;

mysql> savepoint p2;

mysql> rollback to p1;(p2後面的還原點全部自動失效)

{

mysql> rollback;復原到最原始的還原點

mysql> commit;提交

}

重排auto_increment

將auto_increment 恢複成1

1）truncate table 表名；

2）alter table 表名 auto_increment=1;

Mysql最佳化技巧

1）Regex（匹配到了就返回1否則返回0）

例如：

mysql> select "linux is very good!" regexp "^very";

會返回0

mysql> select name ,email from t where email REGEXP "@163[.,]com$"; 

但是Regex消耗資源

2）用rand()提取隨機行

例如：隨機取三條

mysql> select * from stu order by rand() limit 3;

3）group by 字句中的with rollup可以檢索出更多的分組彙總資訊，但是它不能喝order by 同時使用

其實好處不是很明顯，個人覺得

4）group by 語句裡加bit_and（位與） ,bit_or（位或）函數

例如：

mysql> select id, bit_and(kind) from t group by id;

5）mysql help的使用

? % 可以獲得所以mysql>裡的命令

? reg%

? contents 查看所以協助資訊

最佳化sql語句的一般步驟

1）看慢查詢日誌或者

mysql> show [session|global] status;

session（預設）表示當前串連

global表示自資料庫啟動至今

2）定位執行效率較低的sql語句

看慢查詢日誌或者

explain或者desc

查看索引的使用方式：show status like ‘Handler_read%‘;

如果索引正在工作，Handler_read_key的值將很高，這個值代表了一行被索引值讀的次數

Handler_read_rnd_next的值高則意味著查詢運行低效，而且應該建立索引補救。

load infile和outfile比mysqldump匯入/匯出資料的效率要高一些

對於myisam表來說匯入前關閉非唯一索引匯入會更快

alter table t1 disable keys;

匯入結束後記得開啟

alter table t1 enable keys；

最好不要關閉唯一性校正

{關閉唯一性檢查的文法

  set unique_checks=0;

  開啟唯一性檢查的文法

  set unique_checks=1;

}

對於innodb表的最佳化

1）匯入前資料就按主鍵升序儲存那麼匯入就很快了。

2）關閉事務機制

不要在網站訪問高峰期使用optimize table，該語句會消耗大量CPU

最佳化insert語句：一次insert多個值

最佳化group by 語句：後面加 order by null

少用巢狀查詢因為外層查詢不能用索引

多用enum來固定欄位，尋找快

設定字元集

在my.cnf裡設定

查看字元集：show character set;

不要密碼登陸mysql（這個實驗我沒做成功）

首先關閉mysql服務

mysqld_safe --skip-grant-table --user=mysql&

mysql -uroot

就可以登入了

沒有socket的時候怎麼登入mysql（未做實驗）

用 mysql -u root -p test --protocol tcp -hlocalhost

也能登入成功

備份資料時要加-l（加讀鎖，保證資料是一個完整的快照）

mysql的主從複製（未做實驗）

1）先登入到mysql資料庫

2）給從伺服器設定授權使用者

例如：

mysql> grant all slave on  *.* to [email protected] identified by "pass"；

修改主伺服器的參數

主從伺服器的server-id不能相同

在主伺服器上獲得資料一致性的快照

mysql> flush tables with read lock;

備份後記得解鎖

mysql> unlock tables；

主從錯誤的解決（未做實驗）

從資料庫無法同步（show slave status）

解決方案

：mysql>slave stop;

mysql> set GLOBAL_SLAVE_SKIP_COUNTER=1;----2

mysql> slave start;

或者在從伺服器上執行手動同步（改上面的第二步）

mysql> change master to

\

\

\配置和（show master status的參數同）

mysql分區

RANGE分區

範例程式碼：

PARTITION BY RANGE(store_id)(

  PARTITION p0 VALUESLESS THAN(6),

  PARTITION p1 VALUESLESS THAN(11),

  PARTITION p2 VALUESLESS THAN(16),

  PARTITION p3 VALUESLESS THAN(21),

);

LIST分區

例如：

PARTITION BY LIST(store_id)

(

PARTITION pnorth VALUES IN(3，5，6,7),

PARTITION peast VALUES IN(1，2，4,8),

);

HASH分區（隨機的平分資料）

例如：

PARTITION BY HASH (YEAR(hired))

PARTITIONS 4;

KEY分區

分區

例如

create table t2(id int ) engine=myisam partition by hash(id) partitions 4;

可以用show create table t2來查看錶的資訊

INNODB表做分區：首先要設為獨佔資料表空間

要在配置裡加一句：

innodb_file_per_table=1

然後做分區的文法和myisam同，建立表的時候做分區

然後插入資料。

各種mysql視頻的學習筆記