(jlive)[isfdb]>SET optimizer_switch="mrr=on";
Query OK, 0 rows affected (0.00 sec)
(jlive)[isfdb]>SET optimizer_switch="mrr_cost_based=on";
Query OK, 0 rows affected (0.00 sec)
(jlive)[isfdb]>SET optimizer_switch="mrr_sort_keys=on";
Query OK, 0 rows affected (0.00 sec)
(jlive)[isfdb]>SELECT @@optimizer_switch \G
*************************** 1. row ***************************
@@optimizer_switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=on,mrr_cost_based=on,mrr_sort_keys=on,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on
1 row in set (0.01 sec)
对于optimizer_switch中的值,SET语句只会修改指定的值没有指定的保持原样
注意:上面的SET只是当前会话有效,会话断开的重连是则双恢复原样
加上GLOBAL参数后,可以一直有效直到数据库重启
SET GLOBAL optimizer_switch="mrr=on";
写入配置文件会永久生效
[mysqld]
optimizer_switch = "mrr=on, mrr_cost_based=on,mrr_sort_keys=on"
启用extended keys(InnoDB,XtraDB)
The extended key's optimization improves the index lookups for InnoDB and XtraDB tables
SET GLOBAL optimizer_switch='extended_keys=on';
[mysqld]
optimizer_switch = "extended_keys=on"
two-step deadlock detection(Aria)
https://mariadb.com/kb/en/aria-two-step-deadlock-detection/
http://en.wikipedia.org/wiki/Deadlock
http://en.wikipedia.org/wiki/Wait-for_graph
当两个actions互相争抢同一个资源并且在等待对方结束,结果两个都没有结束而造成死锁
SET GLOBAL deadlock_search_depth_short = 3;
SET GLOBAL deadlock_search_depth_long = 10;
SET GLOBAL deadlock_timeout_long = 10000000;
SET GLOBAL deadlock_timeout_short = 5000;
[mysqld]
deadlock_search_depth_short = 3
deadlock_search_depth_long = 10
deadlock_timeout_long = 10000000
deadlock_timeout_short = 5000
注意:
deadlock_timeout_{long,short}的单位是microseconds,10000000microseconds=10S
(jlive)[isfdb]>SHOW VARIABLES LIKE 'deadlock%';
+-----------------------------+----------+
| Variable_name | Value |
+-----------------------------+----------+
| deadlock_search_depth_long | 10 |
| deadlock_search_depth_short | 3 |
| deadlock_timeout_long | 10000000 |
| deadlock_timeout_short | 5000 |
+-----------------------------+----------+
4 rows in set (0.00 sec)
segment key cache(MyISAM)
https://mariadb.com/kb/en/segmented-key-cache/
SET GLOBAL key_cache_segments = 64;
[mysqld]
key_cache_segments = 64
key_cache_segments等于0是表示关闭该功能,也是默认值,当等于非零时表示开启并将值赋值给该key_cache_segments
,相关的变量还有key_buffer_size, key_cache_age_ threshold, key_cache_block_size, and key_cache_division_limit
配置thread pool
https://mariadb.com/kb/en/thread-pool/
[mysqld]
thread_handling = pool-of-threads
需要重启mariadb,相关参数还有
thread_pool_stall_limit #判定激活的线程处于stalled状态的时间,默认是500milliseconds
thread_pool_max_threads #如果判定某个线程stalled,则会生成一个新的线程,直到达到最大的线程数为止,所以一旦达到了线程池上线,管理员也无法接入mariad进行应急管理,解决办法是通过extra_port单独指定一个管理端口
Aria pagecache
[mysqld]
aria_pagecache_buffer_size = 536870912 #单位byte
aria_pagecache_age_threshold = 400
aria_pagecache_division_limit = 90
需要重启mariadb,下面两个值可以动态加载
SET GLOBAL aria_pagecache_age_threshold = 400; #The value is a ratio of the number of times the pagecache is accessed to the number of blocks in the pagecache.
SET GLOBAL aria_pagecache_division_limit = 90; #the minimum percentage of the pagecache that must be warm
(jlive)[isfdb]>SHOW VARIABLES LIKE 'aria_pagecache%';
+-------------------------------+-----------+
| Variable_name | Value |
+-------------------------------+-----------+
| aria_pagecache_age_threshold | 300 |
| aria_pagecache_buffer_size | 134217728 |
| aria_pagecache_division_limit | 100 |
| aria_pagecache_file_hash_size | 512 |
+-------------------------------+-----------+
4 rows in set (0.00 sec)
(jlive)[isfdb]>SHOW STATUS LIKE 'aria_pagecache%';
+-----------------------------------+--------+
| Variable_name | Value |
+-----------------------------------+--------+
| Aria_pagecache_blocks_not_flushed | 0 |
| Aria_pagecache_blocks_unused | 15706 |
| Aria_pagecache_blocks_used | 1394 |
| Aria_pagecache_read_requests | 409947 |
| Aria_pagecache_reads | 6 |
| Aria_pagecache_write_requests | 138592 |
| Aria_pagecache_writes | 1394 |
+-----------------------------------+--------+
7 rows in set (0.00 sec)
subquery cache
这是mariadb独有特性相对于其它mysql产品而言,默认是开启的
(jlive)[isfdb]>SHOW STATUS LIKE 'subquery%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Subquery_cache_hit | 0 |
| Subquery_cache_miss | 237 |
+---------------------+-------+
2 rows in set (0.00 sec)
semijoin subqueries
SET GLOBAL optimizer_switch='exists_to_in=on';
[mysqld]
optimizer_switch = 'exists_to_in=on';
默认情况下,semijoin=on
创建index
(jlive)[isfdb]>CREATE INDEX email ON emails(email_address(50));
Query OK, 1600 rows affected (0.01 sec)
Records: 1600 Duplicates: 0 Warnings: 0
如果已经确认某列一定唯一,则可以创建UNIQUE INDEX
CREATE UNIQUE INDEX index_name ON table_name(column_name (length));
(jlive)[isfdb]>SHOW INDEX FROM emails\G
*************************** 1. row ***************************
Table: emails
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: email_id
Collation: A
Cardinality: 1600
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: emails
Non_unique: 1
Key_name: email
Seq_in_index: 1
Column_name: email_address
Collation: A
Cardinality: 1600
Sub_part: 50
Packed: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
2 rows in set (0.00 sec)
full-text index
(jlive)[isfdb]>CREATE FULLTEXT INDEX note ON notes(note_note);
Query OK, 417609 rows affected (13.17 sec)
Records: 417609 Duplicates: 0 Warnings: 0
(jlive)[isfdb]>SHOW INDEX FROM notes\G
*************************** 1. row ***************************
Table: notes
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: note_id
Collation: A
Cardinality: 417609
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
*************************** 2. row ***************************
Table: notes
Non_unique: 1
Key_name: note
Seq_in_index: 1
Column_name: note_note
Collation: NULL
Cardinality: 1
Sub_part: NULL
Packed: NULL
Null: YES
Index_type: FULLTEXT
Comment:
Index_comment:
2 rows in set (0.00 sec)
SELECT * FROM notes WHERE MATCH(note_note) AGAINST('+artificial,+intelligence' IN BOOLEAN MODE);
删除index
(jlive)[isfdb]>DROP INDEX note ON notes;
Query OK, 417609 rows affected (0.80 sec)
Records: 417609 Duplicates: 0 Warnings: 0
说明:DROP INDEX实际上是调用ALTER TABLE来删除index的
(jlive)[isfdb]>SHOW INDEX FROM notes\G
*************************** 1. row ***************************
Table: notes
Non_unique: 0
Key_name: PRIMARY
Seq_in_index: 1
Column_name: note_id
Collation: A
Cardinality: 417609
Sub_part: NULL
Packed: NULL
Null:
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.00 sec)
启用user statistics
SET GLOBAL userstat = 1;
[mysqld]
userstat = 1
(jlive)[isfdb]>SET GLOBAL userstat = 1;
Query OK, 0 rows affected (0.00 sec)
(jlive)[isfdb]>SHOW VARIABLES LIKE 'userstat';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| userstat | ON |
+---------------+-------+
1 row in set (0.00 sec)
(jlive)[isfdb]>SHOW INDEX_STATISTICS;
+--------------+--------------+-------------+-----------+
| Table_schema | Table_name | Index_name | Rows_read |
+--------------+--------------+-------------+-----------+
| zabbix | autoreg_host | PRIMARY | 1 |
| zabbix | hosts | PRIMARY | 6 |
| zabbix | drules | drules_1 | 2 |
| zabbix | hosts | hosts_2 | 48 |
| zabbix | triggers | PRIMARY | 448 |
| zabbix | functions | functions_2 | 448 |
| zabbix | items | items_1 | 1956 |
+--------------+--------------+-------------+-----------+
7 rows in set (0.00 sec)
JOIN
(jlive)[isfdb]>SELECT author_canonical, email_address FROM authors INNER JOIN emails ON authors.author_id = emails.author_id LIMIT 3;
+----------------------+------------------------+
| author_canonical | email_address |
+----------------------+------------------------+
| Lois McMaster Bujold | lmbujold@mn.uswest.net |
| Orson Scott Card | orsoncard@aol.com |
| Joe Haldeman | haldeman@mit.edu |
+----------------------+------------------------+
3 rows in set (0.01 sec)
(jlive)[isfdb]>SELECT author_canonical, email_address FROM emails LEFT JOIN authors ON authors.author_id = emails.author_id LIMIT 3;
+----------------------+------------------------+
| author_canonical | email_address |
+----------------------+------------------------+
| Lois McMaster Bujold | lmbujold@mn.uswest.net |
| Orson Scott Card | orsoncard@aol.com |
| Joe Haldeman | haldeman@mit.edu |
+----------------------+------------------------+
3 rows in set (0.00 sec)
SELECT * FROM awards CROSS JOIN award_types LIMIT 10;
日期使用微秒提高精度
(jlive)[isfdb]>CREATE DATABASE IF NOT EXISTS test;
Query OK, 1 row affected (0.00 sec)
(jlive)[isfdb]>USE test;
Database changed
(jlive)[test]>CREATE TABLE times (id int NOT NULL AUTO_INCREMENT,dt datetime(6), PRIMARY KEY (id));
Query OK, 0 rows affected (0.01 sec)
(jlive)[test]>INSERT INTO times (dt) VALUES (NOW()), (NOW(6));
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
(jlive)[test]>SELECT * FROM times;
+----+----------------------------+
| id | dt |
+----+----------------------------+
| 1 | 2016-04-04 22:40:09.000000 |
| 2 | 2016-04-04 22:40:09.730399 |
+----+----------------------------+
2 rows in set (0.00 sec)
自动更新日期和时间戳
CREATE TABLE dtts (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(25),
dt datetime(6) NOT NULL DEFAULT CURRENT_TIMESTAMP(6),
ts timestamp(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
PRIMARY KEY (id)
);
INSERT INTO dtts (name) VALUES
('Thomass'),('Gordon'),('Howard'),('Ezra');
UPDATE dtts SET name = 'Thomas'
WHERE name = 'Thomass';
SELECT * FROM dtts;
(jlive)[test]>SELECT * FROM dtts;
+----+--------+----------------------------+-------------------------+
| id | name | dt | ts |
+----+--------+----------------------------+-------------------------+
| 1 | Thomas | 2016-04-04 22:45:46.711699 | 2016-04-04 22:46:07.471 |
| 2 | Gordon | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |
| 3 | Howard | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |
| 4 | Ezra | 2016-04-04 22:45:46.711699 | 2016-04-04 22:45:46.711 |
+----+--------+----------------------------+-------------------------+
4 rows in set (0.00 sec)