SQL代碼開發優化
一般的說，在開發的時候程序員都會設法把代碼寫的更好，所謂優化就是讓MySQL以更好的方式去執行查詢。
通常需要的是
Explain 和 profile。
創建兩個表來說明他們的使用方法
MySQL> create table tt(
-> id int primary key,
-> name char(20))
-> ;
Query OK, 0 rows affected (0.06 sec)
MySQL> create table ttt(
-> id int,
-> name char(20));
Query OK, 0 rows affected (0.01 sec)
MySQL> insert into tt values(1,'aaa'),(2,'bbb'),(3,'ccc');
MySQL> insert into ttt values(1,'aaa'),(2,'bbb'),(3,'ccc');
看下面兩個查詢：
MySQL> explain select * from tt where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| 1 | SIMPLE | tt | range | PRIMARY | PRIMARY | 4 | NULL |
2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
1 row in set (0.00 sec)
MySQL> explain select * from tt where id > 1;
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
| 1 | SIMPLE | tt | range | PRIMARY | PRIMARY | 4 | NULL |
2 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+-
-----+-------------+
1 row in set (0.00 sec)
由於TT有索引，所以掃描完索引後，直接定位滿足條件的兩行數據，所以只掃描了兩行。
但是TTT沒有索引，他進行了全表掃描，設想這是一張幾個GB的表，那麼第二個表由於沒索引就需要消耗很資源，而且還耗費時間。
MySQL> explain select * from tt where name = 'aaa';
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows
| Extra |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
| 1 | SIMPLE | tt | ALL | NULL | NULL | NULL | NULL | 3
| Using where |
+----+-------------+-------+------+---------------+------+---------+------+-----
-+-------------+
1 row in set (0.01 sec)
在TT上使用name字段查詢，他沒索引雖然只返回1行，但是也進行全表掃描。
Profile使用
MySQL> set profiling=on
-> ;
Query OK, 0 rows affected (0.00 sec)
MySQL> select * from tt;
+----+------+
| id | name |
+----+------+
| 1 | aaa |
| 2 | bbb |
| 3 | ccc |
+----+------+
3 rows in set (0.00 sec)
MySQL> show profile;
+--------------------------------+----------+
| Status | Duration |
+--------------------------------+----------+
| starting | 0.000024 |
| checking query cache for query | 0.000049 |
| Opening tables | 0.000013 |
| System lock | 0.000010 |
| Table lock | 0.000029 |
| init | 0.000017 |
| optimizing | 0.000005 |
| statistics | 0.000011 |
| preparing | 0.000009 |
| executing | 0.000005 |
| Sending data | 0.000148 |
| end | 0.000016 |
| query end | 0.000019 |
| freeing items | 0.000043 |
| storing result in query cache | 0.000007 |
| logging slow query | 0.000004 |
| cleaning up | 0.000004 |
+--------------------------------+----------+
17 rows in set (0.01 sec)
MySQL> show profiles;
+----------+------------+-----------------------------------------------------+
| Query_ID | Duration | Query |
+----------+------------+-----------------------------------------------------+
| 2 | 0.00396900 | select * from user |
| 3 | 0.00300300 | SELECT DATABASE() |
| 4 | 0.00045900 | select * from t3 |
| 5 | 0.04893400 | select * from t1 |
| 6 | 0.00307200 | explain select * from t1 |
| 7 | 0.06494800 | create table tt(
id int primary key,
name char(20)) |
| 8 | 0.01028900 | create table ttt(
id int,
name char(20)) |
| 9 | 0.00411100 | insert into tt values(1,'aaa'),(2,'bbb'),(3,'ccc') |
| 10 | 0.00497300 | insert into ttt values(1,'aaa'),(2,'bbb'),(3,'ccc') |
| 11 | 0.00486100 | explain select * from tt |
| 12 | 0.00329900 | explain select * from tt where id > 1 |
| 13 | 0.00039200 | explain select * from ttt where id > 1 |
| 14 | 0.00402100 | explain select * from tt where name = 'aaa' |
| 15 | 0.00015200 | set profiling=on |
| 16 | 0.00041300 | select * from tt |
+----------+------------+-----------------------------------------------------+
15 rows in set (0.01 sec)
他列出了耗時。
