有一个表t_test(userid(int),age(int),register_time(int),sex(smallint)),一条查询语句"select userid from t_test where sex=$sex and age=$age order by register_time limit 10";对于这个查询来说,是在age,register_time,sex三列建一个联合索引效率高,还是,sex和age上建一个联合索引,register_time单独建一个索引效率好?
调试欢乐多
alter table t_test add index(age,sex,register_time) 索引字段顺序很重要你说的其他两个索引基本对这个sql没什么用
create index xxx on t_test (age,register_time )
create index xxx on t_test(age,register_time);
explain select userid from t_test where age=$age order by register_time limit 10;以供分析。
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+---------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| t_test | 0 | PRIMARY | 1 | uid | A | 633858 | NULL | NULL | | BTREE | |
| t_test | 1 | idx_age | 1 | age | A | 56| NULL | NULL | | BTREE | |
| t_test | 1 | idx_register | 1 | registertime| A | 633858 | NULL | NULL | | BTREE | |
| t_test | 1 | idx_sex | 1 | sex | A | 3 | NULL | NULL | YES | BTREE | |
+-------------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----++----+-------------+-------------+-------+---------------+-------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------------+-------+---------------+-------------+---------+------+------+-------------+
| 1 | SIMPLE | t_test | index | idx_age | idx_register| 4 | NULL | 10 | Using where |
+----+-------------+-------------+-------+---------------+-------------+---------+------+------+-------------+