创建测试表
create table mytab(
id int(10) unsigned not nullauto_increment,
c1 int(11) not null default 0,
c2 int(10) unsigned default null,
c5 int(10) unsigned not null default 0,
c3 timestamp not null defaultcurrent_timestamp on update current_timestamp,
c4 varchar(200) not null default '',
primary key(id),
key idx_c1(c1),
key key_c2(c2)
)engine=innodb auto_increment=2686347;
插入数据
for ((i=1;i<=100000;i++));do `mysql wwb-e "insert into mytab(c1,c2,c5,c4)values($i,floor($i+rand()*$i),$i,'user1'))"`;done
可以直接从索引返回记录避免回表
>desc select c1 from mytabwhere c1=1234 limit 1;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 |SIMPLE | mytab | ref | idx_c1 | idx_c1 | 4 | const | 1 | Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.02 sec)
对无索引的列扫描则需要回表
root@localhost:mysql.sock 04:42:56 [wwb]>desc select c1 from my_tabwhere c5=1234 limit 1;
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
| 1 |SIMPLE | my_tab | ALL | NULL | NULL | NULL | NULL | 56861 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+-------+-------------+
1 row in set (0.00 sec)
提高聚合函数效率
>select min(c2),max(c2) frommytab;
+---------+---------+
| min(c2) | max(c2) |
+---------+---------+
| 1 | 199762 |
+---------+---------+
1 row in set (0.00 sec)
没有索引需要走全表扫描
root@localhost:mysql.sock 04:46:18 [wwb]>desc select min(c5),max(c5)from mytab;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 |SIMPLE | mytab | ALL | NULL | NULL | NULL | NULL | 100090 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
有点慢
>select min(c5),max(c5) from mytab;
+---------+---------+
| min(c5) | max(c5) |
+---------+---------+
| 1 | 100000 |
+---------+---------+
1 row in set (0.03 sec)
求平均值 扫描索引即可 无需全表扫描
>desc select avg(c1) frommytab;
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
| 1 |SIMPLE | mytab | ref | idx_c1 | idx_c1 | 4 | const | 1 | Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+-------------+
1 row in set (0.00 sec)
>select min(c5),max(c5) from mytab;
+---------+---------+
| min(c5) | max(c5) |
+---------+---------+
| 1 | 100000 |
+---------+---------+
1 row in set (0.03 sec)
求平均值无论怎么样总是全表扫描
>desc select avg(c5) frommytab;
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| id | select_type | table | type | possible_keys| key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
| 1 |SIMPLE | mytab | ALL | NULL | NULL | NULL | NULL | 100090 | NULL |
+----+-------------+-------+------+---------------+------+---------+------+--------+-------+
1 row in set (0.00 sec)
>select avg(c5) from mytab;
+------------+
| avg(c5) |
+------------+
| 50000.5000 |
+------------+
1 row inset (0.03 sec)
3.提高排序效率
>desc select c5 from mytabwhere c5>100 order by c5 limit 10;
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
| 1 |SIMPLE | mytab | ALL | NULL | NULL | NULL | NULL | 100090 | Using where; Usingfilesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+-----------------------------+
1 row in set (0.00 sec)
>desc select c5 from mytab where c1>100 order by c1 limit 10;
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+
| 1 |SIMPLE | mytab | range | idx_c1 | idx_c1 | 4 | NULL | 50045 | Using index condition |
+----+-------------+-------+-------+---------------+--------+---------+------+-------+-----------------------+
1 row in set (0.00 sec)
减少多表关联时扫描行数
>desc select c1 from mytabt1 left join mytab t2 using(c1);
+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+
| 1 |SIMPLE | t1 | index | NULL | idx_c1 | 4 | NULL | 100090 | Using index |
| 1 |SIMPLE | t2 | ref | idx_c1 | idx_c1 | 4 | wwb.t1.c1 | 1 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+-----------+--------+-------------+
2 rows in set (0.00 sec)
>desc select c5 from mytabt1 left join mytab t2 using(c5);
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
| 1 |SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 100090 | NULL |
| 1 |SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 100090 | Using where; Using joinbuffer (Block Nested Loop) |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------------+
字段类型定义为not null 索引的结构也会比较小
>desc select count(c1) frommytab;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| 1 |SIMPLE | mytab | index | NULL | idx_c1 | 4 | NULL | 100090 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)
root@localhost:mysql.sock 06:27:04 [wwb]>desc select count(c2) frommytab;
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
| 1 |SIMPLE | mytab | index | NULL | key_c2 | 5 | NULL | 100090 | Using index |
+----+-------------+-------+-------+---------------+--------+---------+------+--------+-------------+
1 row in set (0.00 sec)
直接impossible 非常快
>desc select count(c2) from mytab where c2 is null;
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| id | select_type | table | type |possible_keys | key | key_len |ref | rows | Extra |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
| 1 |SIMPLE | mytab | ref | key_c2 | key_c2 | 5 | const | 1 | Using where; Using index |
+----+-------------+-------+------+---------------+--------+---------+-------+------+--------------------------+
1 row in set (0.01 sec)
>desc select count(c1) frommytab where c1 is null;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| id | select_type | table | type |possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
| 1 |SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------+
1 row in set (0.01 sec)