Innodb_thread_concurrency
http://ourmysql.com/archives/811
如果参数设置大于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竞争可通过降低此参数减缓http://forums.mysql.com/read.php?24,411949,412273#msg-412273
Innodb adaptive flushing
http://ourmysql.com/archives/852
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的优化
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
+----------+---------+-------+-----------------------------+
| table | key | rows | Extra |
+----------+---------+-------+-----------------------------+
| post_tag | tag_id | 71220 | Using where; Using filesort |
| post | PRIMARY | 1 | Using where |
+----------+---------+-------+-----------------------------+
优化，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执行计划(篇幅所限，结果有删减)：
+----------+----------------+--------+-------------+
| table | key | rows | Extra |
+----------+----------------+--------+-------------+
| post | status_created | 119340 | Using where |
| post_tag | post_id | 1 | Using where |
+----------+----------------+--------+-------------+
Mysql如何选择索引和join顺序
http://ourmysql.com/archives/1236
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 )缩小搜索空间。要是你怀疑漏掉了某个最优的执行计划，你可以考虑关闭参数试试，当然这会导致搜索空间增大，优化器执行时间偏长
获取随机数据
http://ourmysql.com/archives/524
不推荐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新特性
http://ourmysql.com/archives/1059
启动日志
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 http://www.orczhou.com/index.php/2010/02/innodb-double-write/
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
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15480802/viewspace-766300/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/15480802/viewspace-766300/
684
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
