Join Vs select 结果集作列
有时候我们需要关联很多表统计：
select f1, f2, count(*) from a join b join c join d join e ……
如果结果集很小，只统计出几条数据来，那么可以换成这样写
Select f1, f2, (select count(*) from a join b ) from c……
比如下面的例子：
1selectt1.assignment_id assignmentId,t5.person_id,2t2.subject_name subjectName,3t3.dict_name assignType,4t1.assign_date assignDate,5t4.totalcount totalCount,6(selectdict_name from bas_diction where dict_id=t5.assign_marks)asassignMarks,7(caset5.if_submit when1then'是' else '否' end) as ifSubmit,8t6.errorcount errorCount,9t6.nocheckCount,10caset1.online_assignment when1then'是' else '否' end as ifOnline,11(selectdict_name from bas_diction where dict_id=t5.write_appraise)aswriteAppraise12from hom_assignmentinfo t1,bas_subject t2,bas_diction t3,13(selectt1.assignment_id,count(*)astotalcount from hom_assignmentinfo t1,hom_assignment t214where t1.assignment_id=t2.hom_assignment_id15group by t1.assignment_id) t4,hom_assignment_appraise t5leftjoin16(selectt1.assignment_id,t3.appraise_id,17sum(caset3.check_result when3003001then0else1end)aserrorcount,18sum(casewhen t3.check_result=3003001ort3.recheck_result=3003001then0else1end)asnocheckCount19from hom_assignmentinfo t1,hom_check_assignment t320where t1.assignment_id=t3.assignment_id21andt1.person_id='13042'22group by t1.assignment_id,t3.appraise_id) t6on(t5.appraise_id=t6.appraise_id)23where t1.subject_id=t2.subject_id24andt1.assign_type=t3.dict_id25andt1.assignment_id=t4.assignment_id26andt1.assignment_id=t5.assignment_id27andnotexists (select1from hom_assignmentinfo t11,hom_assignment_appraise t1228where t11.assignment_id=t12.assignment_idand29t11.online_assignment=0and30t12.person_id='13042' and31t11.subject_id=t1.subject_idandt11.assign_date>t1.assign_date)32andt1.assign_dateisnotnull33andt5.person_id='13042'34order by t1.assign_date desc;35
改写成下面的，速度由0.625秒优化到: 0.032
1selectt1.assignment_id assignmentId,t5.person_id,2t2.subject_name subjectName,3t3.dict_name assignType,4t1.assign_date assignDate,5(selectcount(*)astotalcount from hom_assignmentinfo where assignment_id=t1.assignment_id) totalcnt,6(7selectsum(caset3.check_result when3003001then0else1end)8from hom_check_assignment t39where t1.assignment_id=t3.assignment_id10) errorcount,11(12selectsum(casewhen t3.check_result=3003001ort3.recheck_result=3003001then0else1end)asnocheckCount13from hom_check_assignment t314where t1.assignment_id=t3.assignment_id15) nocheckCount,16(selectdict_name from bas_diction where dict_id=t5.assign_marks)asassignMarks,17(caset5.if_submit when1then'是' else '否' end) as ifSubmit,18caset1.online_assignment when1then'是' else '否' end as ifOnline,19(selectdict_name from bas_diction where dict_id=t5.write_appraise)aswriteAppraise20from hom_assignmentinfo t1,bas_subject t2,bas_diction t3, hom_assignment_appraise t521where t1.subject_id=t2.subject_id22andt1.assign_type=t3.dict_id23andt1.assignment_id=t5.assignment_id24andnotexists (select1from hom_assignmentinfo t11,hom_assignment_appraise t1225where t11.assignment_id=t12.assignment_idand26t11.online_assignment=0and27t12.person_id='13042' and28t11.subject_id=t1.subject_idandt11.assign_date>t1.assign_date)29andt1.assign_dateisnotnull30andt5.person_id='13042'31order by t1.assign_date desc;
如果什么都做不了，试试全索引扫描
如果一个语句实在不能优化了，那么还有一个方法可以试试：索引覆盖。
如果一个语句可以从索引上获取全部数据，就不需要通过索引再去读表，省了很多I/O。比如这样一个表
如果我要统计每个学生每道题的得分情况，我们除了要给每个表的主键外键建立索引，还要对【得分情况】的实际得分字段索引，这样，整个查询就可以从索引得到数据了。
Join、In、not in、exist、not exist并不是绝对的
网上很多教程讨论了join、in和exist 的性能差异，其实这不是绝对的，对于效率不理想的语句，还是应该换换写法试试看。
Like
Like毕竟效率太低，必要的话可以试试全文检索。对于中文全文检索，可以结合程序分词来实现。
什么情况下查询用不到索引
参见手册 7.4.3列索引, 7.4.4所列索引, 7.4.5 mysql如何使用索引
去掉不必要的排序，如果必要，尽量用主键排序代替
显而易见却容易被忽视的问题。
数据库参数配置
最重要的参数就是内存，我们主要用的innodb引擎，所以下面两个参数调的很大
# Additional memory pool that is used by InnoDB to store metadata
# information. If InnoDB requires more memory for this purpose it will
# start to allocate it from the OS. As this is fast enough on most
# recent operating systems, you normally do not need to change this
# value. SHOW INNODB STATUS will display the current amount used.
innodb_additional_mem_pool_size = 64M
# InnoDB, unlike MyISAM, uses a buffer pool to cache both indexes and
# row data. The bigger you set this the less disk I/O is needed to
# access data in tables. On a dedicated database server you may set this
# parameter up to 80% of the machine physical memory size. Do not set it
# too large, though, because competition of the physical memory may
# cause paging in the operating system. Note that on 32bit systems you
# might be limited to 2-3.5G of user level memory per process, so do not
# set it too high.
innodb_buffer_pool_size = 5G
对于myisam，需要调整key_buffer_size
当然调整参数还是要看状态，用show status语句可以看到当前状态，以决定改调整哪些参数
Cretated_tmp_disk_tables 增加tmp_table_size
Handler_read_key 高表示索引正确 Handler_read_rnd高表示索引不正确
Key_reads/Key_read_requests 应小于0.01 计算缓存损失率，增加Key_buffer_size
Opentables/Open_tables 增加table_cache
select_full_join 没有实用索引的链接的数量。如果不为0，应该检查索引。
select_range_check 如果不为0，该检查表索引。
sort_merge_passes 排序算法已经执行的合并的数量。如果该值较大，应增加sort_buffer_size
table_locks_waited 不能立即获得的表的锁的次数，如果该值较高，应优化查询
Threads_created 创建用来处理连接的线程数。如果Threads_created较大，要增加 thread_cache_size值。
缓存访问率的计算方法Threads_created/Connections。
合理的硬件资源和操作系统
如果你的机器内存超过4G，那么毋庸置疑应当采用64位操作系统和64位mysql
读写分离
如果数据库压力很大，一台机器支撑不了，那么可以用mysql复制实现多台机器同步，将数据库的压力分散。
Master
Slave1
Slave2
Slave3
主库master用来写入，slave1—slave3都用来做select，每个数据库分担的压力小了很多。
要实现这种方式，需要程序特别设计，写都操作master，读都操作slave，给程序开发带来了额外负担。当然目前已经有中间件来实现这个代理，对程序来读写哪些数据库是透明的。官方有个mysql-proxy，但是还是alpha版本的。新浪有个amobe for mysql，也可达到这个目的，结构如下
使用方法可以看amobe的手册。
分享到：
2009-05-31 14:52
浏览 619
评论
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
