在理论上来说，这听起来非常的不错。但是从个人经验（也是大多数人的）上来说，这个功能制造出来的麻烦事更多。因为，你只有有限的链接数，内存问题，文件句柄数，等等。
而且，Apache 运行在极端并行的环境中，会创建很多很多的了进程。这就是为什么这种“永久链接”的机制工作地不好的原因。在你决定要使用“永久链接”之前，你需要好好地考虑一下你的整个系统的架构。
19、当查询较慢的时候，可用Join来改写一下该查询来进行优化
| mysql> select sql_no_cache * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; Empty set (18.87 sec) mysql> select sql_no_cache a.* from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151; Empty set (0.01 sec) |
原因
| mysql> desc select sql_no_cache * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151) ; +----+--------------------+-----------------+-------+---------------+---------+---------+-------+----------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+-------------+ | 1 | PRIMARY | guang_deal_outs | ALL | NULL | NULL | NULL | NULL | 18633779 | Using where | | 2 | DEPENDENT SUBQUERY | guang_deals | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | +----+--------------------+-----------------+-------+---------------+--------- +---------+-------+----------+-------------+ 2 rows in set (0.04 sec) mysql> desc select sql_no_cache a.* from guang_deal_outs a inner join guang_deals b on a.deal_id = b.id where b.id = 100017151; +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ | 1 | SIMPLE | b | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index | | 1 | SIMPLE | a | ref | idx_guang_dlout_dlid | idx_guang_dlout_dlid | 4 | const | 1 | | +----+-------------+-------+-------+---------------------- +----------------------+---------+-------+------+-------------+ 2 rows in set (0.05 sec) |
其实在 guang_deal_outs 在deal_id 上也是有索引的。 其实我想把子查询设置为
| select * from guang_deal_outs where deal_id in (select id from guang_deals where id = 100017151); |
变成下面的样子
| select * from guang_deal_outs where deal_id in (100017151); |
但不幸的是，实际情况正好相反。MySQL试图让它和外面的表产生联系来“帮助”优化查询，它认为下面的exists形式更有效率
