mysql日誌+引擎筆記

標籤：mysql   引擎   

錯誤記錄檔 error log       服務啟動關閉

查詢日誌 query log

•      普通查詢日誌  general    query log         用戶端連結和SQL

•      慢查詢日誌     slow  query  log               執行時間超出指定值

二進位日誌 binary log   記錄資料被修改的相關資訊

錯誤記錄檔

vim /etc/my.cnf

[mysqld_safe]

log-error=/data/3306/mysql_oldboy3306.err

查詢日誌

mysql> show variables like  ‘general_log%‘;         //普通查詢日誌參數

+------------------+----------------------------------+

| Variable_name    | Value                            |

+------------------+----------------------------------+

| general_log      | OFF                              |

| general_log_file | /application/mysql/data/lnmp.log |

+------------------+----------------------------------+

mysql> set global  general_log = ON;

慢查詢：

long_query_time = 1   //超過(大於)1秒的記錄，，，，預設10秒

log-slow-queries = /data/3306/slow.log

log-queries-not-using-indexes    // 沒有使用索引的查詢也將被記錄

log-long-format  // 記錄啟用的更新日誌、二進位更新日誌、和慢查詢日誌的大量資訊

log-short-format  //少量資訊

mysql> show global status like ‘%slow%‘; 

+---------------------+-------+

| Variable_name       | Value |

+---------------------+-------+

| Slow_launch_threads | 0     |   //值較大 說明有些東西正在延遲連結的新線程

| long_query_time     | 1.000000 |  

| Slow_queries        | 0     |    //慢查詢的記錄數

| log_slow_queries    | ON    |     //是否開啟

+---------------------+-------+

2 rows in set (0.00 sec)

二進位日誌

log-bin=mysql-bin

mysql> show variables like ‘%log_bin%‘;

+---------------------------------+-------+

| Variable_name                   | Value |

+---------------------------------+-------+

| log_bin                         | ON    |   

| sql_log_bin                     | ON    |  //臨時不記錄binlog   用mysql恢複時，可以臨時開啟

+---------------------------------+-------+

binlog 日誌的三種模式

statement level   每修改一條SQL的語句都會記錄  不需要記錄每一行的 變化

Row  level         記錄每一行資料修改的模式，然後在slave端再對相同的資料進行修改   bin-log日誌大

mixed           上面 2種模式的混合

mysql> show variables like ‘%binlog_format%‘;

+---------------+-----------+

| Variable_name | Value     |

+---------------+-----------+

| binlog_format | STATEMENT |

+---------------+-----------+

mysql> set global binlog_format = ‘ROW‘;        //臨時 全域改動  退出再進

mysqlbinlog --no-defaults   --base64-output=decode-rows   -v   mysql-bin.000004//查看ROW型 記錄檔

binlog_format=mixed     //修改my.cnf   官方推薦的模式

MyISAM引擎                重要參數:key_buffer_size  = 2048M

• 不支援事務

• 表級鎖定

• 讀寫互相堵塞

• 只會緩衝索引

• 讀寫速度快，佔用資源相對少

• 不支援外鍵約束，但支援全文索引

• 是5.5.5前預設的儲存引擎

調優精要

• 設定合適的索引

• 調整讀寫優先順序，根據實際需求確保重要操作更優先執行

• 啟用延遲插入改善大批量寫入效能

• 盡量順序操作讓insert資料都寫入到尾部，減少阻塞

• 分解大的時間長的操作，降低單個操作的阻塞時間

• 降低並發數，某些高並發情境通過應用進行 排隊隊列機制

• 對於相對靜態資料庫資料，充分利用query_cache_size 或 memcached快取服務可以極大的提高訪問效率

• count只有在去全表掃描的時候特別搞笑 。    select   count(*)  from  he.quan;

• 可以把主從同步的主庫使用innodb, 從庫使用MyISAM引擎（不推薦）。

data/mysql

-rw-rw---- 1 mysql mysql  10630 3月  28 01:04 user.frm      //檔案儲存表的定義

-rw-rw---- 1 mysql mysql   1212 4月   6 07:20 user.MYD      //資料檔案

-rw-rw---- 1 mysql mysql   2048 4月   6 07:27 user.MYI      //索引檔案

mysql> show engines \G;

-rw-rw---- 1 mysql mysql 134217728 4月   7 02:15 ibdata1      //InnoDB的共用資料表空間

InnoDB引擎特點

• 支援事務  支援4個交易隔離等級  ACID

• 行級鎖定

• 讀寫阻塞和交易隔離等級相關

• 具有 非常高效的緩衝特性：能緩衝索引，也能快取資料。

• 整個 表和主鍵已cluster方式 儲存，組成一顆平衡樹

• 支援分區，資料表空間，類似oracle資料庫

• 支援外鍵約束，不支援全文索引5.5以前，以後支援了。

• 對MyISAM比，   硬體資源要求比較高

調優精要

• 主鍵儘可能小

• 避免全表掃描，因為會使用表鎖

• 儘可能緩衝所有的索引和資料，提高響應速度，減少磁碟IO消耗

• 在大批量小插入的時候，盡量自己控制事務而不要使用autocommit自動認可

• 合理設定innodb_flush_log_at_trx_commit參數值，不要過度追求安全性，如果=0，log buffer 每秒就會被刷寫記錄檔到磁碟，提交事務的時候不做任何操作。

• 避免主鍵更新，因為這會帶來大量的資料移動

650) this.width=650;" src="/e/u261/themes/default/images/spacer.gif" style="background:url("/e/u261/lang/zh-cn/images/localimage.png") no-repeat center;border:1px solid #ddd;" alt="spacer.gif" />

mysql> show engines;  //引擎

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         || CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         || MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         || BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         || MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         || FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       || ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         || InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        || PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

/application/mysql/support-files/my-innodb-heavy-4G.cnf  

innodb_buffer_pool_size = 2048M       //緩衝innodb表的索引，資料，插入資料時的緩衝  作業系統記憶體的70%-80%最佳。

修改引擎

mysql> alter table  test engine = MyISAM;  //修改test表的引擎。

/application/mysql/bin/mysql_convert_table_format   --user=root  --password=123456    -engine=MyISAM    hequan    test

yum install perl-ExtUtils-MakeMaker

本文出自 “何全” 部落格，請務必保留此出處http://hequan.blog.51cto.com/5701886/1782212

mysql日誌+引擎筆記