删除索引：
发现时间又变成了0.57s左右，

建立一个的联合索引：
ALTER TABLE user
ADD INDEX index_sex_last_login_area (sex,last_login,area)
SELECT * FROM user WHERE area='美国ATT用户' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.18s
SELECT * FROM user WHERE area='泰国' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.17s
SELECT * FROM user WHERE area='台湾省台湾大宽频' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.81s
SELECT * FROM user WHERE area='美国弗吉尼亚州' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.01s
SELECT * FROM user WHERE area='德国奔驰汽车' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.02s
SELECT * FROM user WHERE area='台湾省中华电信' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.04s
SELECT * FROM user WHERE area='韩国' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.01s
SELECT * FROM user WHERE area='拉美地区' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.01s
SELECT * FROM user WHERE area='美国纽约(Prudential)' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.03s
SELECT * FROM user WHERE area='印度尼西亚' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.04s
sex怎么总是你在拖后腿啊！把你调整到索引的最后一个吧！
删除索引：
发现时间又变成了0.57s左右，

建立一个的联合索引：
ALTER TABLE user
ADD INDEX index_last_login_area_sex (area,last_login,sex)
SELECT * FROM user WHERE area='美国ATT用户' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.03s
SELECT * FROM user WHERE area='泰国' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.07s
SELECT * FROM user WHERE area='台湾省台湾大宽频' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.50s
SELECT * FROM user WHERE area='美国弗吉尼亚州' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.02s
SELECT * FROM user WHERE area='德国奔驰汽车' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.05s
SELECT * FROM user WHERE area='台湾省中华电信' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.06s
SELECT * FROM user WHERE area='韩国' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.02s
SELECT * FROM user WHERE area='拉美地区' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.02s
SELECT * FROM user WHERE area='美国纽约(Prudential)' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.04s
SELECT * FROM user WHERE area='印度尼西亚' AND sex=0 ORDER BY lastlogin DESC limit 30; 0.06s

综上所述：1.建立索引不一定能够加快查询效率如sex这种给重复次数特别多的列增加索引如sex这种会降低查询效率，具体的原因有待查找
2.给重复次数比较少的列增加u讴吟还是能够大幅度提高效率
3.给where和orderby之后的字段添加索引才会加快查询效率
4.为每一个列单独建立索引，不能将索引的效率最大化，应该使用索引合并策略，即根据查询条件，建立联合索引
5.联合索引的顺序问题：将选择性高的索引放到前面
6.根据资料建立索引意味着索引按照最左列进行排序，然后事第二列，以此类推。如（lastlogin ,area）就会按照lastlogin进行排序，然后才是area
7.根据这次的这个查询条件来说最好的索引是：ALTER TABLE userADD INDEX index_last_login_area (last_login,area)。

在公司能有个机会，查看资料和实践索引真的很不错哈！推荐书籍：高性能mysql（第三版）

PDF版本的：http://pan.baidu.com/s/1sjJIyRV