Oracle的组合索引操作有2种，access和filter,当然access更好。Mysql在组合索引上，只有access，没有filter。因此要注意MYSQL处理where clause包括多个range条件的SQL语句。
详见测试案例和truss中对MYD文件的pread输出。
表结构
mysql> show create table test;
+——-+————–+——-+————+
| Table | Create Table
+——-+————–+——-+————+
| test | CREATE TABLE “test” (
“a” int(11) NOT NULL,
“b” int(11) NOT NULL,
“c” int(11) default NULL,
“d” char(20) default NULL,
KEY “test3_idx” USING BTREE (”a”,”b”,”c”)
) ENGINE=MYISAM DEFAULT CHARSET=utf8 |
+——-+————++——-+————-+
1 row in set (0.00 sec)mysql> show table status;
+——–+——–|————+——-+—————-+
| Name | Engine | Row_format | Rows | Avg_row_length |
+——–+——–|————+——-+—————-+
| test | MyISAM | Fixed | 1598 | 73 |
+——–+——–|————+——-+—————-+
3 rows in set (0.00 sec)
在列a,b,c上存在一个复合索引。每行需73 bytes的固定长度。下面是各种组合查询的explain plan.
mysql> show table status;
+——–+——–|————+——-+—————-+
| Name | Engine | Row_format | Rows | Avg_row_length |
+——–+——–|————+——-+—————-+
| test | MyISAM | Fixed | 1598 | 73 |
+——–+——–|————+——-+—————-+
3 rows in set (0.00 sec)mysql> explain select a,b,c from test where a=1;
+—-+————-+——-+——+—————+———–+———+——-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———–+———+——-+——+——-+
| 1 | SIMPLE | test | ref | test3_idx | test3_idx | 4 | const | 1595 | |
+—-+————-+——-+——+—————+———–+———+——-+——+——-+
1 row in set (0.00 sec)mysql> explain select a,b,c from test where a=1 and b=1555 ;
+—-+————-+——-+——+—————+———–+———+————-+——+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———–+———+————-+——+——-+
| 1 | SIMPLE | test | ref | test3_idx | test3_idx | 8 | const,const | 1 | |
+—-+————-+——-+——+—————+———–+———+————-+——+——-+
1 row in set (0.00 sec)mysql> explain select a,b,c from test where a=1 and b>1555 ;
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| 1 | SIMPLE | test | range | test3_idx | test3_idx | 8 | NULL | 1 | Using where |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
1 row in set (0.00 sec)mysql> explain select a,b,c from test where a=1 and b=1555 and c=3000;
+—-+————-+——-+——+—————+———–+———+——————-+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+———–+———+——————-+——+————-+
| 1 | SIMPLE | test | ref | test3_idx | test3_idx | 13 | const,const,const | 1 | Using where |
+—-+————-+——-+——+—————+———–+———+——————-+——+————-+
1 row in set (0.01 sec)mysql> explain select a,b,c from test where a=1 and b=1555 and c<=1554;
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| 1 | SIMPLE | test | range | test3_idx | test3_idx | 13 | NULL | 1 | Using where |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
1 row in set (0.00 sec)mysql> explain select a,b,c from test where a=1 and b>1500 and c<=1000;
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| 1 | SIMPLE | test | range | test3_idx | test3_idx | 8 | NULL | 6 | Using where |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
1 row in set (0.00 sec)mysql> explain select * from test where a=1 and b>1500 and c=1000;
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
| 1 | SIMPLE | test | range | test3_idx | test3_idx | 8 | NULL | 102 | Using where |
+—-+————-+——-+——-+—————+———–+———+——+——+————-+
1 row in set (0.00 sec)
从explain中的key_len列，可以看到会被access的组合索引的列长度。 key_len=13的，表示a,b,c 3列都在access-list中。key_len=8的表示只有a,b 两列参与access。
那最后2个SQL可有不同？SQL A >select a,b,c from test where a=1 and b>1500 and c=1000; 和SQL B >select * from test where a=1 and b>1500 and c=1000。
通过truss，可以看到SQL A并没有读MYD文件（表段），只读取了MYI文件（索引文件）；SQL B 既读取了MYI，也读取了MYD文件。而且在pread(MYD) 文件的时候，读取了 select couunt(*) from test where a=1 and b>1500次。
表示ＳＱＬ Ｂ，MYSQL在组合索引上通过ａ=1 and b>1500 条件返回Ｎ条记录，然后通过记录在索引中的offset去一一读取表段ＭＹＤ，判断条件Ｃ＝１０００是否满足。 虽然列Ｃ在组合索引中，但仍然需要访问表。
SQL A 没有读取MYD文件，是因为ｓｅｌｅｃｔ的字段全部包括在组合索引中，ｍｙｓｑｌ判断出能够在组合索引中完成过滤filter，也算是智能了。不过这个filtter根Oracle的filter完全不同。
如下是truss SQL B 读取ＭＹＤ文件的输出。
/7: read(45, ” 5\0\0\0″, 4) = 4
/7: read(45, “03 s e l e c t * f”.., 53) = 53
/7: time() = 1252743991
/7: time() = 1252743991
/7: pread(48, “F901\0\0\0DD05\0\0DD05\0″.., 73, 109500) = 73
/7: pread(48, “F901\0\0\0DE05\0\0DE05\0″.., 73, 109573) = 73
/7: pread(48, “F901\0\0\0DF05\0\0DF05\0″.., 73, 109646) = 73
/7: pread(48, “F901\0\0\0E005\0\0E005\0″.., 73, 109719) = 73
/7: pread(48, “F901\0\0\0E105\0\0E105\0″.., 73, 109792) = 73
/7: pread(48, “F901\0\0\0E205\0\0E205\0″.., 73, 109865) = 73
/7: pread(48, “F901\0\0\0E305\0\0E305\0″.., 73, 109938) = 73
/7: pread(48, “F901\0\0\0E405\0\0E405\0″.., 73, 110011) = 73
/7: pread(48, “F901\0\0\0E505\0\0E505\0″.., 73, 110084) = 73
/7: pread(48, “F901\0\0\0E605\0\0E605\0″.., 73, 110157) = 73
/7: pread(48, “F901\0\0\0E705\0\0E705\0″.., 73, 110230) = 73
/7: pread(48, “F901\0\0\0E805\0\0E805\0″.., 73, 110303) = 73
/7: pread(48, “F901\0\0\0E905\0\0E905\0″.., 73, 110376) = 73
/7: pread(48, “F901\0\0\0EA05\0\0EA05\0″.., 73, 110449) = 73
/7: pread(48, “F901\0\0\0EB05\0\0EB05\0″.., 73, 110522) = 73
/7: pread(48, “F901\0\0\0EC05\0\0EC05\0″.., 73, 110595) = 73
/7: pread(48, “F901\0\0\0ED05\0\0ED05\0″.., 73, 110668) = 73
/7: pread(48, “F901\0\0\0EE05\0\0EE05\0″.., 73, 110741) = 73
/7: pread(48, “F901\0\0\0EF05\0\0EF05\0″.., 73, 110814) = 73
/7: pread(48, “F901\0\0\0F005\0\0F005\0″.., 73, 110887) = 73
/7: pread(48, “F901\0\0\0F105\0\0F105\0″.., 73, 110960) = 73
/7: pread(48, “F901\0\0\0F205\0\0F205\0″.., 73, 111033) = 73
/7: pread(48, “F901\0\0\0F305\0\0F305\0″.., 73, 111106) = 73
/7: pread(48, “F901\0\0\0F405\0\0F405\0″.., 73, 111179) = 73
/7: pread(48, “F901\0\0\0F505\0\0F505\0″.., 73, 111252) = 73
/7: pread(48, “F901\0\0\0F605\0\0F605\0″.., 73, 111325) = 73
/7: pread(48, “F901\0\0\0F705\0\0F705\0″.., 73, 111398) = 73
/7: pread(48, “F901\0\0\0F805\0\0F805\0″.., 73, 111471) = 73
/7: pread(48, “F901\0\0\0F905\0\0F905\0″.., 73, 111544) = 73
/7: pread(48, “F901\0\0\0FA05\0\0FA05\0″.., 73, 111617) = 73
/7: pread(48, “F901\0\0\0FB05\0\0FB05\0″.., 73, 111690) = 73
/7: pread(48, “F901\0\0\0FC05\0\0FC05\0″.., 73, 111763) = 73
/7: pread(48, “F901\0\0\0FD05\0\0FD05\0″.., 73, 111836) = 73
/7: pread(48, “F901\0\0\0FE05\0\0FE05\0″.., 73, 111909) = 73
/7: pread(48, “F901\0\0\0FF05\0\0FF05\0″.., 73, 111982) = 73
/7: pread(48, “F901\0\0\0\006\0\0\006\0″.., 73, 112055) = 73
/7: pread(48, “F901\0\0\00106\0\00106\0″.., 73, 112128) = 73
/7: pread(48, “F901\0\0\00206\0\00206\0″.., 73, 112201) = 73
/7: pread(48, “F901\0\0\00306\0\00306\0″.., 73, 112274) = 73
/7: pread(48, “F901\0\0\00406\0\00406\0″.., 73, 112347) = 73
/7: pread(48, “F901\0\0\00506\0\00506\0″.., 73, 112420) = 73
/7: pread(48, “F901\0\0\00606\0\00606\0″.., 73, 112493) = 73
/7: pread(48, “F901\0\0\00706\0\00706\0″.., 73, 112566) = 73
/7: pread(48, “F901\0\0\0\b06\0\0\b06\0″.., 73, 112639) = 73
/7: pread(48, “F901\0\0\0\t06\0\0\t06\0″.., 73, 112712) = 73
/7: pread(48, “F901\0\0\0\n06\0\0\n06\0″.., 73, 112785) = 73
/7: pread(48, “F901\0\0\0\v06\0\0\v06\0″.., 73, 112858) = 73
/7: pread(48, “F901\0\0\0\f06\0\0\f06\0″.., 73, 112931) = 73
/7: pread(48, “F901\0\0\0\r06\0\0\r06\0″.., 73, 113004) = 73
/7: pread(48, “F901\0\0\00E06\0\00E06\0″.., 73, 113077) = 73
/7: pread(48, “F901\0\0\00F06\0\00F06\0″.., 73, 113150) = 73
/7: pread(48, “F901\0\0\01006\0\01006\0″.., 73, 113223) = 73
/7: pread(48, “F901\0\0\01106\0\01106\0″.., 73, 113296) = 73
/7: pread(48, “F901\0\0\01206\0\01206\0″.., 73, 113369) = 73
/7: pread(48, “F901\0\0\01306\0\01306\0″.., 73, 113442) = 73
/7: pread(48, “F901\0\0\01406\0\01406\0″.., 73, 113515) = 73
/7: pread(48, “F901\0\0\01506\0\01506\0″.., 73, 113588) = 73
/7: pread(48, “F901\0\0\01606\0\01606\0″.., 73, 113661) = 73
/7: pread(48, “F901\0\0\01706\0\01706\0″.., 73, 113734) = 73
/7: pread(48, “F901\0\0\01806\0\01806\0″.., 73, 113807) = 73
/7: pread(48, “F901\0\0\01906\0\01906\0″.., 73, 113880) = 73
/7: pread(48, “F901\0\0\01A06\0\01A06\0″.., 73, 113953) = 73
/7: pread(48, “F901\0\0\01B06\0\01B06\0″.., 73, 114026) = 73
/7: pread(48, “F901\0\0\01C06\0\01C06\0″.., 73, 114099) = 73
/7: pread(48, “F901\0\0\01D06\0\01D06\0″.., 73, 114172) = 73
/7: pread(48, “F901\0\0\01E06\0\01E06\0″.., 73, 114245) = 73
/7: pread(48, “F901\0\0\01F06\0\01F06\0″.., 73, 114318) = 73
/7: pread(48, “F901\0\0\0 06\0\0 06\0″.., 73, 114391) = 73
/7: pread(48, “F901\0\0\0 !06\0\0 !06\0″.., 73, 114464) = 73
/7: pread(48, “F901\0\0\0 “06\0\0 “06\0″.., 73, 114537) = 73
/7: pread(48, “F901\0\0\0 #06\0\0 #06\0″.., 73, 114610) = 73
/7: pread(48, “F901\0\0\0 $06\0\0 $06\0″.., 73, 114683) = 73
/7: pread(48, “F901\0\0\0 %06\0\0 %06\0″.., 73, 114756) = 73
/7: pread(48, “F901\0\0\0 &06\0\0 &06\0″.., 73, 114829) = 73
/7: pread(48, “F901\0\0\0 ‘06\0\0 ‘06\0″.., 73, 114902) = 73
/7: pread(48, “F901\0\0\0 (06\0\0 (06\0″.., 73, 114975) = 73
/7: pread(48, “F901\0\0\0 )06\0\0 )06\0″.., 73, 115048) = 73
/7: pread(48, “F901\0\0\0 *06\0\0 *06\0″.., 73, 115121) = 73
/7: pread(48, “F901\0\0\0 +06\0\0 +06\0″.., 73, 115194) = 73
/7: pread(48, “F901\0\0\0 ,06\0\0 ,06\0″.., 73, 115267) = 73
/7: pread(48, “F901\0\0\0 -06\0\0 -06\0″.., 73, 115340) = 73
/7: pread(48, “F901\0\0\0 .06\0\0 .06\0″.., 73, 115413) = 73
/7: pread(48, “F901\0\0\0 /06\0\0 /06\0″.., 73, 115486) = 73
/7: pread(48, “F901\0\0\0 006\0\0 006\0″.., 73, 115559) = 73
/7: pread(48, “F901\0\0\0 106\0\0 106\0″.., 73, 115632) = 73
/7: pread(48, “F901\0\0\0 206\0\0 206\0″.., 73, 115705) = 73
/7: pread(48, “F901\0\0\0 306\0\0 306\0″.., 73, 115778) = 73
/7: pread(48, “F901\0\0\0 406\0\0 406\0″.., 73, 115851) = 73
/7: pread(48, “F901\0\0\0 506\0\0 506\0″.., 73, 115924) = 73
/7: pread(48, “F901\0\0\0 606\0\0 606\0″.., 73, 115997) = 73
/7: pread(48, “F901\0\0\0 706\0\0 706\0″.., 73, 116070) = 73
/7: pread(48, “F901\0\0\0 806\0\0 806\0″.., 73, 116143) = 73
/7: pread(48, “F901\0\0\0 906\0\0 906\0″.., 73, 116216) = 73
/7: pread(48, “F901\0\0\0 :06\0\0 :06\0″.., 73, 116289) = 73
/7: pread(48, “F901\0\0\0 ;06\0\0 ;06\0″.., 73, 116362) = 73
/7: pread(48, “F901\0\0\0 <06\0\0 <06\0″.., 73, 116435) = 73
/7: pread(48, “F901\0\0\0 =06\0\0 =06\0″.., 73, 116508) = 73
/7: pread(48, “F901\0\0\0 >06\0\0 >06\0″.., 73, 116581) = 73
/7: write(45, “01\0\00104 $\0\00203 d e”.., 183) = 183
/7: time() = 1252743991
/7: port_associate(3, 4, 0×0000002D, 0×00000001, 0xFFFFFD7FFDF10150) = 0
/7: port_get(3, 0xFFFFFD7FFD5BBF60, 0×00000000) (sleeping…)正好９８个ｐｒｅａｄ（４８，．．,73,．．),每次读一行（７３ｂｙｔｅｓ）． pfiles可以看到文件48正好是 test.MYD文件.
mysql> select count(*) from test where a=1 and b>1500 ;
+———-+
| count(*) |
+———-+
| 98 |
+———-+
1 row in set (0.00 sec)
下面是对explain的简单解释。
let’s break this down column by column.
* table – This is the name of the table. This will become important when you have large joins, as each table will get a row.
* type – The type of the join. Here’s what the MySQL documentation has to say about the ref type: “All rows with matching index values will be read from this table for each combination of rows from the previous tables. ref is used if the join uses only a leftmost prefix of the key, or if the key is not UNIQUE or a PRIMARY KEY (in other words, if the join cannot select a single row based on the key value). If the key that is used matches only a few rows, this join type is good.” In this case, since our index isn’t UNIQUE, this is the best join type we can get. In summary, if the join type is listed as “ALL” and you aren’t trying to select most of the rows in the table, then MySQL is doing a full table scan which is usually very bad. You can fix this by adding more indexes. If you want more information, the MySQL manual covers this value with much more depth.
* possible_keys – The name of the indexes that could possibly be used. This is where nicknaming your index helps. If you leave the name field blank, the name defaults to the name of the first column in the index (in this case, it would be “firstname”), which isn’t very descriptive.
* key – This shows the name of the index that MySQL actually uses. If this is empty (or NULL), then MySQL isn’t using an index.
* key_len – The length, in bytes, of the parts of the index being used. In this case, it’s 102 because firstname takes 50 bytes, lastname takes 50, and age takes 2. If MySQL were only using the firstname part of the index, this would be 50.
* ref – This shows the name of the columns (or the word “const”) that MySQL will use to select the rows.
Here, MySQL references three constants to find the rows.
* rows – The number of rows MySQL thinks it has to go through before knowing it has the correct rows. Obviously, one is the best you can get.
* Extra – There are many different options here, most of which will have an adverse effect on the query.
In this case, MySQL is simply reminding us that it used the WHERE clause to limit the results.
Choosing the right position for intercourse also is one of
the methods to get pregnant fast. When women are stressed it tends to interfere with their menstrual cycles.
That is especially applicable if you have regular menstruation.