Innodb_thread_concurrency

如果引數設定大於0，則表示檢查機制開啟，允許進入的執行緒數就是引數的值.

5.5 innodb_thread_concurrency預設為0

設定此值可允許單個sql一次性執行多個請求，從而減少上下文切換，

在新的MySQL執行緒呼叫Innodb介面前，Innodb會檢查已經接受的請求執行緒數，如已經超過innodb_thread_concurrency設定的限制，則該請求執行緒會等待innodb_thread_sleep_delay微秒後嘗試重新請求，如果第二次請求還是無法獲得，則該執行緒會進入執行緒佇列休眠。重試兩次的機制是為了減少CPU的上下文切換的次數，以降低CPU消耗，這和Oracle中latch的spin機制是同樣的道理。如果請求被Innodb接受，則會獲得一個次數為innodb_concurrency_tickets(預設500次)的通行證，在次數用完之前，該執行緒重新請求時無須再進行前面所說innodb_thread_concurrency的檢查。

另外，如果是一個已經持有lock的執行緒，則透過呼叫srv_conc_force_enter_innodb函式可以無視該檢查，這是為了避免執行緒長時間持有鎖影響效能，且可能增加死鎖的機率。除此之外，slave執行緒也是有無視檢查直接通行的許可權。

/* The following controls how many threads we let inside InnoDB concurrently:

threads waiting for locks are not counted into the number because otherwise

we could get a deadlock. MySQL creates a thread for each user session, and

semaphore contention and convoy problems can occur without this restriction.

Value 10 should be good if there are less than 4 processors + 4 disks in the

computer. Bigger computers need bigger values. Value 0 will disable the

concurrency check. */

因為檢查機制需要Mutex保護(Mutex-based Model)，所以開啟檢查本身也有效能消耗，並且擴充套件性也會受到限制，在MySQL5.4版本中引入了一種新的機制（Timer-based Model），這裡就不討論了，有興趣的可以參考這裡(http://mikaelronstrom.blogspot.co.uk/2009/05/mysql-54-patches-innodb-thread.html )

部分情況下 mutex競爭可透過降低此引數減緩

Innodb adaptive flushing

plugin 1.0.4提供，innodb在2種情況下觸發flush dirty page：1 日誌切換，稱為flush_list flush 2 沒有空閒buffer，透過LRU將dirty page寫入資料檔案，稱為LRU_list Flush

對系統IO影響較大的為前者；

原理：

1 計算切換前剩餘時間：日誌剩餘量/產生速度

2 計算重新整理速度：髒頁數/剩餘時間

3 減去LRU_list flush速度

Straight_join的最佳化

http://itblog.cc/409.html

SELECT post.*

FROM post

INNER JOIN post_tag ON post.id = post_tag.post_id

WHERE post.status = 1 AND post_tag.tag_id = 123

ORDER BY post.created DESC

LIMIT 100

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

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

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

最佳化，post_tag作為驅動表但以post.created欄位排序，故採用using filesort，強制post為驅動表，雖然候選行數更多但省去了file sort

SELECT post.*

FROM post

STRAIGHT_JOIN post_tag ON post.id = post_tag.post_id

WHERE post.status = 1 AND post_tag.tag_id = 123

ORDER BY post.created DESC

LIMIT 100 試著用EXPLAIN查詢一下SQL執行計劃(篇幅所限，結果有刪減)：

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

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

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

Mysql如何選擇索引和join順序

MySQL最佳化器只有兩個自由度：順序選擇；單表訪問方式；

explain

select *

from

employee as A,department as B

where

A.LastName = 'zhou'

and B.DepartmentID = A.DepartmentID

and B.DepartmentName = 'TBX';

join模式為A|B或B|A，而每個表均有3種訪問方式：全表掃描或兩個欄位索引

最佳化器工作包括如下部分：

Query Rewrite(包括Outer Join轉換等)、const table detection、range analysis、JOIN optimization(順序和訪問方式選擇)、plan refinement

從range analysis入手

3

該階段包括range和index merge成本評估

本案例中，range analysis會針對A表的條件A.LastName = 'zhou'和B表的B.DepartmentName = 'TBX'分別做分析。其中：

表A A.LastName = 'zhou' found records: 51

表B B.DepartmentName = 'TBX' found records: 1

這兩個條件都不是range，但是這裡計算的值仍然會儲存，在後面的ref訪問方式評估的時候使用。這裡的值是根據records_in_range介面返回，而對於InnoDB每次呼叫這個函式都會進行一次索引頁的取樣，這是一個很消耗效能的操作，對於很多其他的關聯式資料庫是使用"直方圖"的統計資料來避免這次操作

4

JOIN optimization

透過列舉left-deep tree，找到最優執行順序和訪問方式

4.1 決定驅動表，根據found records排序，記錄少的放前面，在此順序為B/A

4.2 當表數量預設63)，採用窮舉搜尋left-deep tree，另外為減少開銷採用prune_level(預設on)，至少3表關聯才可行

(*) 選擇第一個JOIN的表為B

(**) 確定B表的訪問方式

(**) 從剩餘的表中窮舉選出第二個JOIN的表，這裡剩餘的表為：A

(**) 將A表加入JOIN，並確定其訪問方式

(***) IND_L_D A.LastName = 'zhou'

(***) IND_DID B.DepartmentID = A.DepartmentID

(***) IND_L_D成本為25.2；IND_DID成本為1.2，所以選擇後者為當前表的訪問方式

(**) 確定A使用索引IND_DID，訪問方式為ref

(**) JOIN順序B|A，總成本為：1.2+1.2 = 2.4

(*) 選擇第一個JOIN的表為A

(**) 確定A表的訪問方式

(**) 這裡訪問A表的成本已經是25.2，比之前的最優成本2.4要大，忽略該順序

在開啟了引數prune_level(預設開啟)後，MySQL不再使用窮舉的方式擴充套件執行計劃，而是透過一些規則跳過一些看似消耗更大的執行計劃，而是在剩餘表中直接選取訪問最少紀錄數的表透過這種"啟發式"的方式忽略一些執行計劃，藉此可以大大減少需要窮舉的執行計劃。按照MySQL手冊上的描述是：根據經驗來看，這種”educated guess”基本不會漏掉最優的執行計劃，但是卻可以大大(dramatically )縮小搜尋空間。要是你懷疑漏掉了某個最優的執行計劃，你可以考慮關閉引數試試，當然這會導致搜尋空間增大，最佳化器執行時間偏長

獲取隨機資料

不推薦order by rand() limit n，因為

rand()放在ORDER BY 子句中會被執行多次，自然效率及很低。

You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times.

普遍採用max(id) * rand()然後join原表

SELECT *

FROM `table` AS t1 JOIN (SELECT ROUND(RAND() * (SELECT MAX(id) FROM `table`)) AS id) AS t2

WHERE t1.id >= t2.id

ORDER BY t1.id ASC LIMIT 5;

15萬行資料只需0.01秒

Order by最佳化

http://space.itpub.net/15480802/viewspace-757553

http://space.itpub.net/15480802/viewspace-757562

儘量使用索引排序

1

Order by使用了最左字首的列或者前N列都為const

2

多表連線時order by的列均為outter table的最左字首列

Filesort分兩種：雙路排序和單路排序，後者受max_length_for_sort_data影響

內部臨時表

使用memory引擎存於記憶體，或使用MyISAM引擎存於磁碟

何時生成

使用order by /group by的列並非全來自於join queue的第一個表

Distinct和order by聯合使用

多表連線需要儲存中間結果集

如何儲存

SQL_SMALL_RESULT會使用記憶體臨時表，除非包含必須使用磁碟臨時表的條件：

當表包含blob/text列，或group by/distinct的列大於512位元組時，必須使用磁碟臨時表；

當臨時表> min(tmp_table_size, max_heap_table_size)時，會自動將記憶體臨時錶轉化為磁碟臨時表

可透過狀態變數created_tmp_tables/created_tmp_disk_tables檢視內部臨時表的使用情況

Group by預設排序, order by null可去除

Memory引擎預設hash索引

不支援range，可顯示建立Btree索引

create table t1_memory (

id int unsigned not null auto_increment primary key,

a1 decimal(15,12),

a2 decimal(15,12),

remark varchar(200) not null,

key idx_u1 (a1,a2)

) engine memory;

alter table t1_memory drop key idx_u1, add key idx_u1 using btree (a1,a2);

innodb plugin 1.0新特性

啟動日誌

InnoDB: The InnoDB memory heap is disabled –採用了OS slab allocation，禁用了內建分配器，innodb_use_sys_malloc=on

InnoDB: Mutexes and rw_locks use GCC atomic built-ins –使用GCC atomic builtins執行互斥和讀寫所，比pthread_mutex_t更高效

InnoDB: The first specified data file ./ibdata1 did not exist:

InnoDB: a new database to be created!

100609 17:17:11 InnoDB: Setting file ./ibdata1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:15 InnoDB: Log file xxx/ib_logfile0 did not exist: new to be created

InnoDB: Setting log file xxx/ib_logfile0 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

100609 17:17:21 InnoDB: Log file xxx/ib_logfile1 did not exist: new to be created

InnoDB: Setting log file xxxx/ib_logfile1 size to 512 MB

InnoDB: Database physically writes the file full: wait…

InnoDB: Progress in MB: 100 200 300 400 500

InnoDB: Doublewrite buffer not found: creating new

InnoDB: Doublewrite buffer created

InnoDB: Creating foreign key constraint system tables

InnoDB: Foreign key constraint system tables created

100609 17:17:26 InnoDB Plugin 1.0.6-unknown started; log sequence number 0

100609 17:17:28 [Note] Event Scheduler: Loaded 0 events

100609 17:17:28 [Note] /usr/xtradb/libexec/mysqld: ready for connections.

Version: ’5.1.45-log’ socket: ’xxx/mysqld.sock’ port: 4331 Source distribution

知識點筆記之innodb_thread_concurrency/straight_join/rand

相關文章