author：skate
time：2012/06/30

mysql优化(2)---表的数量和临时表

1.mysql如何open和close表

mysql如何open表
mysql open表是通过句柄（file descriptor ）来实现的，mysql是多线程的，在并发session open表的时候，mysql为每个session单独打开表的，通过内存cache住这部分信息，为后来open表所用，可以大大的提高open表的速度；对于打开一个myisam表，mysql要同时分配两个文件描述符，一个是数据文件描述符(线程之间不能共享)，一个是索引文件描述符（线程之间可以共享），例如一个两个线程访问同一表或一个线程在一个查询中访问一个表两次，表都会被打开两次；而open其他的表只需要一个文件描述符。

哪些参数影响open表的数量
table_open_cache和max_connections影响打开mysql打开表的数量，那这两个参数如何设置呢？

max_connections：是mysql支持最大并发连接数
table_open_cache：为了更快的open表，这个参数很重要；比如200个并发connection，table_open_cache=200×N（一个查询中参与join的最大表的数量，也可能是分区表），如果是myisam表的话，table_open_cache=200×(N×2),还要考虑突发的临时表的文件描述符；在os可以支持打开文件数时，如果table_open_cache设置的比较高，mysql用光所有的文件描述符，就会拒绝连接，查询出错。

table_open_cache设置多大合适呢？处理从上面的理论推理计算外，还可以根据状态变量来监控“Opened_tables”，这个状态变量表示，从server服务启动为止，open表的数量，可以根据这个状态变量来比较精准的设置table_oprn_cache

mysql如何colse表
mysql open一个表，并放入table cache中，那mysql什么时候close一个不用的表，并从tabl中e cache中删除呢？当满足如下条件时，开始准备删除table cache中的表
    A. 当table cache已经满了，而又有一个线程需要open一个不在table cache中的表。
    B. table cache里存在的条目比table_open_cache多，并且在table cache中的表不再被任何线程使用
    C.当表有flush操作时，例如有人执行flush table语句、 mysqladmin flush-tables或mysqladmin refresh

table cache中表哪些表被删除
    A. 当table cache满了的时候，根据lru算法删除
    B. 当table cache满了，而又有新表需要open时，这时候cache会临时扩展，当cache处于临时扩展状态时，表就从可用变为不可用，表就可以被colse并从table cache中删除了

2.一个database里应有多少表
在同个database里表数量不宜太多（300-500为宜），如果表太多，那么open、close和create operations都会很忙 ，这可能因为mysql的系统表都是myisam，不支持并发的原因.

3.mysql internal临时表的使用
当系统需要internal临时表的时候，首先在内存初始化为memory类型的表，随着数据的增长，mysql自动把内存中memory类型表转变为磁盘上myisam表，内存中的临时表是取变量 tmp_table_size和max_heap_table_size两个中最小的一个。

mysql server使用临时表的情况可以通过状态变量Created_tmp_tables 和 Created_tmp_disk_tables 来监控；只要创建临时表，变量Created_tmp_tables 就增加，创建磁盘临时表，变量值Created_tmp_disk_tables增加。通过这两个状态变量的监控可以方便设置变量tmp_table_size，来减少磁盘排序。

在什么条件下会出现使用磁盘临时表？
   A. 表中出现blob和text列
   B. 超过512bytes的group by或distinct
   C. 当有union和union all被使用时，select list超过512bytes

如何确定一个查询是否使用临时表?
可以通过explain查看“Extra”列，看其是否有“Using temporary”

----end----

参考：http://dev.mysql.com/doc/refman/5.5/en/optimize-multi-tables.html