mysql的索引介绍

创件数据库
MariaDB [mydb]> create database mydb;
切入到库里，创建表的命令
MariaDB [mydb]> use mydb;
MariaDB [mydb]> create table students ( stuid INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,name VARCHAR (200),age TINYINT UNSIGNED, gender ENUM ('F','M'),major VARCHAR(200));
生成测试表命令
for i in {1001..2000}; do mysql -e "INSERT INTO mydb.students VALUES ('$i','stu$i','$[$RANDOM%100+1]','F','chenxue$i')";done

MariaDB [mydb]> select * from students;
|   960 | stu960   |   68 | M      | CHENXI960         |
|   961 | stu961   |   22 | M      | CHENXI961         |
|   962 | stu962   |   83 | M      | CHENXI962         |
|   963 | stu963   |   44 | M      | CHENXI963         |
|   964 | stu964   |   29 | M      | CHENXI964         |
|   965 | stu965   |   94 | M      | CHENXI965         |
|   966 | stu966   |   78 | M      | CHENXI966         |
|   967 | stu967   |   44 | M      | CHENXI967         |
|   968 | stu968   |   72 | M      | CHENXI968         |
|   969 | stu969   |   57 | M      | CHENXI969         |
|   970 | stu970   |   69 | M      | CHENXI970         |
|   971 | stu971   |   49 | M      | CHENXI971         |
|   972 | stu972   |   54 | M      | CHENXI972         |
|   973 | stu973   |   97 | M      | CHENXI973         |
|   974 | stu974   |   84 | M      | CHENXI974         |
|   975 | stu975   |   75 | M      | CHENXI975         |
|   976 | stu976   |   64 | M      | CHENXI976         |
|   977 | stu977   |   70 | M      | CHENXI977         |
|   978 | stu978   |   63 | M      | CHENXI978         |
|   979 | stu979   |   97 | M      | CHENXI979         |
|   980 | stu980   |    4 | M      | CHENXI980         |
|   981 | stu981   |   70 | M      | CHENXI981         |
|   982 | stu982   |   64 | M      | CHENXI982         |
|   983 | stu983   |   66 | M      | CHENXI983         |
|   984 | stu984   |   79 | M      | CHENXI984         |
|   985 | stu985   |   33 | M      | CHENXI985         |
|   986 | stu986   |  100 | M      | CHENXI986         |
|   987 | stu987   |   63 | M      | CHENXI987         |
|   988 | stu988   |   26 | M      | CHENXI988         |
|   989 | stu989   |    4 | M      | CHENXI989         |
|   990 | stu990   |   31 | M      | CHENXI990         |
|   991 | stu991   |   52 | M      | CHENXI991         |
|   992 | stu992   |   53 | M      | CHENXI992         |
|   993 | stu993   |   34 | M      | CHENXI993         |
|   994 | stu994   |   73 | M      | CHENXI994         |
|   995 | stu995   |   67 | M      | CHENXI995         |
|   996 | stu996   |   58 | M      | CHENXI996         |
|   997 | stu997   |   93 | M      | CHENXI997         |
|   998 | stu998   |   26 | M      | CHENXI998         |
|   999 | stu999   |   25 | M      | CHENXI999         |
|  1000 | stu1000  |    6 | M      | CHENXI1000        |
|  1001 | stu1001  |   65 | F      | chenxue1001       |
|  1002 | stu1002  |   12 | F      | chenxue1002       |
|  1003 | stu1003  |   92 | F      | chenxue1003       |
|  1004 | stu1004  |   61 | F      | chenxue1004       |
|  1005 | stu1005  |   32 | F      | chenxue1005       |
|  1006 | stu1006  |   41 | F      | chenxue1006       |
|  1007 | stu1007  |   56 | F      | chenxue1007       |
|  1008 | stu1008  |   71 | F      | chenxue1008       |
|  1009 | stu1009  |   30 | F      | chenxue1009       |
|  1010 | stu1010  |    3 | F      | chenxue1010       |
|  1011 | stu1011  |   66 | F      | chenxue1011       |
|  1012 | stu1012  |   39 | F      | chenxue1012       |
|  1013 | stu1013  |    4 | F      | chenxue1013       |
|  1014 | stu1014  |  100 | F      | chenxue1014       |
|  1015 | stu1015  |   60 | F      | chenxue1015       |
|  1016 | stu1016  |   35 | F      | chenxue1016       |
|  1017 | stu1017  |   13 | F      | chenxue1017       |
|  1018 | stu1018  |   92 | F      | chenxue1018       |
|  1019 | stu1019  |   69 | F      | chenxue1019       |
|  1020 | stu1020  |   79 | F      | chenxue1020       |
|  1021 | stu1021  |   20 | F      | chenxue1021       |
|  1022 | stu1022  |   53 | F      | chenxue1022       |
|  1023 | stu1023  |   45 | F      | chenxue1023       |
|  1024 | stu1024  |   86 | F      | chenxue1024       |
|  1025 | stu1025  |   89 | F      | chenxue1025       |
|  1026 | stu1026  |   71 | F      | chenxue1026       |
|  1027 | stu1027  |   97 | F      | chenxue1027       |
|  1028 | stu1028  |   50 | F      | chenxue1028       |
|  1029 | stu1029  |   34 | F      | chenxue1029       |
|  1030 | stu1030  |   58 | F      | chenxue1030       |
|  1031 | stu1031  |   60 | F      | chenxue1031       |
|  1032 | stu1032  |   83 | F      | chenxue1032       |
|  1033 | stu1033  |   28 | F      | chenxue1033       |
|  1034 | stu1034  |   64 | F      | chenxue1034       |
|  1035 | stu1035  |   51 | F      | chenxue1035       |
|  1036 | stu1036  |   26 | F      | chenxue1036       |
|  1037 | stu1037  |   83 | F      | chenxue1037       |
|  1038 | stu1038  |   99 | F      | chenxue1038       |
|  1039 | stu1039  |   14 | F      | chenxue1039       |
|  1040 | stu1040  |   47 | F      | chenxue1040       |
|  1041 | stu1041  |   15 | F      | chenxue1041       |
|  1042 | stu1042  |   31 | F      | chenxue1042       |
|  1043 | stu1043  |   30 | F      | chenxue1043       |
|  1044 | stu1044  |   83 | F      | chenxue1044       |
|  1045 | stu1045  |   64 | F      | chenxue1045       |
|  1046 | stu1046  |   80 | F      | chenxue1046       |
|  1047 | stu1047  |   30 | F      | chenxue1047       |
|  1048 | stu1048  |   57 | F      | chenxue1048       |
|  1049 | stu1049  |   81 | F      | chenxue1049       |
|  1050 | stu1050  |   25 | F      | chenxue1050       |
|  1051 | stu1051  |    1 | F      | chenxue1051       |
|  1052 | stu1052  |    9 | F      | chenxue1052       |
|  1053 | stu1053  |   84 | F      | chenxue1053       |
|  1054 | stu1054  |   20 | F      | chenxue1054       |
|  1055 | stu1055  |   52 | F      | chenxue1055       |
|  1056 | stu1056  |   20 | F      | chenxue1056       |
|  1057 | stu1057  |   74 | F      | chenxue1057       |
|  1058 | stu1058  |   32 | F      | chenxue1058       |
|  1059 | stu1059  |   78 | F      | chenxue1059       |
|  1060 | stu1060  |   69 | F      | chenxue1060       |
|  1061 | stu1061  |   52 | F      | chenxue1061       |
|  1062 | stu1062  |   78 | F      | chenxue1062       |
|  1063 | stu1063  |   87 | F      | chenxue1063       |
|  1064 | stu1064  |   55 | F      | chenxue1064       |
|  1065 | stu1065  |   41 | F      | chenxue1065       |
|  1066 | stu1066  |   29 | F      | chenxue1066       |
|  1067 | stu1067  |   57 | F      | chenxue1067       |
|  1068 | stu1068  |   41 | F      | chenxue1068       |
|  1069 | stu1069  |    8 | F      | chenxue1069       |
|  1070 | stu1070  |   70 | F      | chenxue1070       |
|  1071 | stu1071  |   56 | F      | chenxue1071       |
|  1072 | stu1072  |   58 | F      | chenxue1072       |
|  1073 | stu1073  |   30 | F      | chenxue1073       |
|  1074 | stu1074  |   67 | F      | chenxue1074       |
|  1075 | stu1075  |   48 | F      | chenxue1075       |
|  1076 | stu1076  |   51 | F      | chenxue1076       |
|  1077 | stu1077  |   42 | F      | chenxue1077       |
|  1078 | stu1078  |   73 | F      | chenxue1078       |
|  1079 | stu1079  |   91 | F      | chenxue1079       |
|  1080 | stu1080  |   57 | F      | chenxue1080       |
|  1081 | stu1081  |   87 | F      | chenxue1081       |
|  1082 | stu1082  |   43 | F      | chenxue1082       |
|  1083 | stu1083  |   59 | F      | chenxue1083       |
|  1084 | stu1084  |   69 | F      | chenxue1084       |
|  1085 | stu1085  |   31 | F      | chenxue1085       |
|  1086 | stu1086  |   14 | F      | chenxue1086       |
|  1087 | stu1087  |   33 | F      | chenxue1087       |
|  1088 | stu1088  |    5 | F      | chenxue1088       |
|  1089 | stu1089  |   54 | F      | chenxue1089       |
|  1090 | stu1090  |   25 | F      | chenxue1090       |
|  1091 | stu1091  |   39 | F      | chenxue1091       |
|  1092 | stu1092  |   66 | F      | chenxue1092       |
|  1093 | stu1093  |   62 | F      | chenxue1093       |
|  1094 | stu1094  |   25 | F      | chenxue1094       |
|  1095 | stu1095  |    4 | F      | chenxue1095       |
|  1096 | stu1096  |   20 | F      | chenxue1096       |
|  1097 | stu1097  |   75 | F      | chenxue1097       |
|  1098 | stu1098  |   22 | F      | chenxue1098       |
|  1099 | stu1099  |   78 | F      | chenxue1099       |
|  1100 | stu1100  |    9 | F      | chenxue1100       |
|  1101 | stu1101  |   76 | F      | chenxue1101       |
|  1102 | stu1102  |   35 | F      | chenxue1102       |
|  1103 | stu1103  |   25 | F      | chenxue1103       |
|  1104 | stu1104  |   53 | F      | chenxue1104       |
|  1105 | stu1105  |   98 | F      | chenxue1105       |
|  1106 | stu1106  |   73 | F      | chenxue1106       |
|  1107 | stu1107  |   93 | F      | chenxue1107       |
|  1108 | stu1108  |   63 | F      | chenxue1108       |
|  1109 | stu1109  |   26 | F      | chenxue1109       |
|  1110 | stu1110  |   31 | F      | chenxue1110       |
|  1111 | stu1111  |   90 | F      | chenxue1111       |
|  1112 | stu1112  |   15 | F      | chenxue1112       |
|  1113 | stu1113  |   83 | F      | chenxue1113       |
|  1114 | stu1114  |   36 | F      | chenxue1114       |
|  1115 | stu1115  |   61 | F      | chenxue1115       |
|  1116 | stu1116  |   88 | F      | chenxue1116       |
|  1117 | stu1117  |    7 | F      | chenxue1117       |
|  1118 | stu1118  |   21 | F      | chenxue1118       |
|  1119 | stu1119  |   29 | F      | chenxue1119       |
|  1120 | stu1120  |    9 | F      | chenxue1120       |
|  1121 | stu1121  |    2 | F      | chenxue1121       |
|  1122 | stu1122  |   99 | F      | chenxue1122       |
|  1123 | stu1123  |   32 | F      | chenxue1123       |
|  1124 | stu1124  |   76 | F      | chenxue1124       |
|  1125 | stu1125  |   98 | F      | chenxue1125       |
|  1126 | stu1126  |   13 | F      | chenxue1126       |
|  1127 | stu1127  |    5 | F      | chenxue1127       |
|  1128 | stu1128  |   85 | F      | chenxue1128       |
|  1129 | stu1129  |   80 | F      | chenxue1129       |
|  1130 | stu1130  |   60 | F      | chenxue1130       |
|  1131 | stu1131  |    8 | F      | chenxue1131       |
|  1132 | stu1132  |   19 | F      | chenxue1132       |
|  1133 | stu1133  |    6 | F      | chenxue1133       |
|  1134 | stu1134  |   35 | F      | chenxue1134       |
|  1135 | stu1135  |   35 | F      | chenxue1135       |
|  1136 | stu1136  |   83 | F      | chenxue1136       |
|  1137 | stu1137  |   29 | F      | chenxue1137       |
|  1138 | stu1138  |   42 | F      | chenxue1138       |
|  1139 | stu1139  |   69 | F      | chenxue1139       |
|  1140 | stu1140  |   74 | F      | chenxue1140       |
|  1141 | stu1141  |   57 | F      | chenxue1141       |
|  1142 | stu1142  |    4 | F      | chenxue1142       |
|  1143 | stu1143  |   47 | F      | chenxue1143       |
|  1144 | stu1144  |   20 | F      | chenxue1144       |
|  1145 | stu1145  |   52 | F      | chenxue1145       |
|  1146 | stu1146  |   61 | F      | chenxue1146       |
|  1147 | stu1147  |   38 | F      | chenxue1147       |
|  1148 | stu1148  |    7 | F      | chenxue1148       |
|  1149 | stu1149  |   39 | F      | chenxue1149       |
|  1150 | stu1150  |   45 | F      | chenxue1150       |
|  1151 | stu1151  |   72 | F      | chenxue1151       |
|  1152 | stu1152  |   27 | F      | chenxue1152       |
|  1153 | stu1153  |    3 | F      | chenxue1153       |
|  1154 | stu1154  |   34 | F      | chenxue1154       |
|  1155 | stu1155  |   23 | F      | chenxue1155       |
|  1156 | stu1156  |   61 | F      | chenxue1156       |
|  1157 | stu1157  |   34 | F      | chenxue1157       |
|  1158 | stu1158  |   95 | F      | chenxue1158       |
|  1159 | stu1159  |   94 | F      | chenxue1159       |
|  1160 | stu1160  |   80 | F      | chenxue1160       |
|  1161 | stu1161  |   61 | F      | chenxue1161       |
|  1162 | stu1162  |   69 | F      | chenxue1162       |
|  1163 | stu1163  |   81 | F      | chenxue1163       |
|  1164 | stu1164  |   16 | F      | chenxue1164       |
|  1165 | stu1165  |    9 | F      | chenxue1165       |
|  1166 | stu1166  |    5 | F      | chenxue1166       |
|  1167 | stu1167  |   54 | F      | chenxue1167       |
|  1168 | stu1168  |   88 | F      | chenxue1168       |
|  1169 | stu1169  |   87 | F      | chenxue1169       |
|  1170 | stu1170  |   12 | F      | chenxue1170       |
|  1171 | stu1171  |   98 | F      | chenxue1171       |
|  1172 | stu1172  |   54 | F      | chenxue1172       |
|  1173 | stu1173  |   70 | F      | chenxue1173       |
|  1174 | stu1174  |   28 | F      | chenxue1174       |
|  1175 | stu1175  |    9 | F      | chenxue1175       |
|  1176 | stu1176  |    8 | F      | chenxue1176       |
|  1177 | stu1177  |    3 | F      | chenxue1177       |
|  1178 | stu1178  |   18 | F      | chenxue1178       |
|  1179 | stu1179  |   28 | F      | chenxue1179       |
|  1180 | stu1180  |   92 | F      | chenxue1180       |
|  1181 | stu1181  |   83 | F      | chenxue1181       |
|  1182 | stu1182  |   53 | F      | chenxue1182       |
|  1183 | stu1183  |   54 | F      | chenxue1183       |
|  1184 | stu1184  |   82 | F      | chenxue1184       |
|  1185 | stu1185  |    4 | F      | chenxue1185       |
|  1186 | stu1186  |   85 | F      | chenxue1186       |
|  1187 | stu1187  |   17 | F      | chenxue1187       |
|  1188 | stu1188  |   86 | F      | chenxue1188       |
|  1189 | stu1189  |   45 | F      | chenxue1189       |
|  1190 | stu1190  |   20 | F      | chenxue1190       |
|  1191 | stu1191  |   43 | F      | chenxue1191       |
|  1192 | stu1192  |   29 | F      | chenxue1192       |
|  1193 | stu1193  |   65 | F      | chenxue1193       |
|  1194 | stu1194  |   70 | F      | chenxue1194       |
|  1195 | stu1195  |   99 | F      | chenxue1195       |
|  1196 | stu1196  |   24 | F      | chenxue1196       |
|  1197 | stu1197  |   61 | F      | chenxue1197       |
|  1198 | stu1198  |   85 | F      | chenxue1198       |
|  1199 | stu1199  |   46 | F      | chenxue1199       |
|  1200 | stu1200  |   39 | F      | chenxue1200       |
|  1201 | stu1201  |   89 | F      | chenxue1201       |
|  1202 | stu1202  |   42 | F      | chenxue1202       |
|  1203 | stu1203  |   33 | F      | chenxue1203       |
|  1204 | stu1204  |   21 | F      | chenxue1204       |
|  1205 | stu1205  |   14 | F      | chenxue1205       |
MariaDB [mydb]> select name,age from students where age <= 15;
+---------+------+
| name    | age  |
+---------+------+
| stu6    |   15 |
| stu19   |    4 |
| stu29   |   10 |
| stu37   |    4 |
| stu41   |    2 |
| stu44   |    1 |

查此命令你执行时用到的索引
MariaDB [mydb]> EXPLAIN select *from students where name = "stu999";
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 2061 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
创建索引
MariaDB [mydb]> create index name on students(name);
Query OK, 0 rows affected (0.25 sec)
Records: 0  Duplicates: 0  Warnings: 0
查看指定表上的索引
MariaDB [mydb]> show indexes from students;
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY  |            1 | stuid       | A         |        1608 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name     |            1 | name        | A         |         160 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
测试索引是否有用
MariaDB [mydb]> EXPLAIN select *from students where name = "stu999";
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref   | rows | Extra                 |
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
|    1 | SIMPLE      | students | ref  | name          | name | 203     | const |    1 | Using index condition |
+------+-------------+----------+------+---------------+------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)
查看满足where条件找找是用到的索引
MariaDB [mydb]> EXPLAIN select *from students where name LIKE  "stu100%"; 右侧统配索引可用
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | name          | name | 203     | NULL |   11 | Using index condition |
+------+-------------+----------+-------+---------------+------+---------+------+------+-----------------------+
1 row in set (0.00 sec)
MariaDB [mydb]> EXPLAIN select *from students where name LIKE  "%stu100%";  左侧就就是去功能了
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 1608 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
创建组合索引；这个组合索引与上面的name索引重复了
MariaDB [mydb]> create index name_and_age on students(name,age);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
像这种情况就可以把name删除了
MariaDB [mydb]> EXPLAIN select *from students where name LIKE  "stu100%";
+------+-------------+----------+-------+-------------------+------+---------+------+------+-----------------------+
| id   | select_type | table    | type  | possible_keys     | key  | key_len | ref  | rows | Extra                 |
+------+-------------+----------+-------+-------------------+------+---------+------+------+-----------------------+
|    1 | SIMPLE      | students | range | name,name_and_age | name | 203     | NULL |   11 | Using index condition |
+------+-------------+----------+-------+-------------------+------+---------+------+------+-----------------------+
1 row in set (0.01 sec)
删除表中name索引
MariaDB [mydb]> drop index name on students;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看表中的索引 
MariaDB [mydb]> show indexes from students;
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table    | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| students |          0 | PRIMARY      |            1 | stuid       | A         |        1565 |     NULL | NULL   |      | BTREE      |         |               |
| students |          1 | name_and_age |            1 | name        | A         |         195 |     NULL | NULL   | YES  | BTREE      |         |               |
| students |          1 | name_and_age |            2 | age         | A         |         195 |     NULL | NULL   | YES  | BTREE      |         |               |
+----------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
这种查询方式组合索引就用不了了，
MariaDB [mydb]> explain select * from students where age >= 50;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | NULL          | NULL | NULL    | NULL | 1565 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)
MariaDB [mydb]> explain select * from students where name LIKE 'stu%'and age >= 50;
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id   | select_type | table    | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
|    1 | SIMPLE      | students | ALL  | name_and_age  | NULL | NULL    | NULL | 1565 | Using where |
+------+-------------+----------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

解释
id  表示第几条语句
select_type  查询的类型有哪些简单查询：SIMPLE
简单子查询：SUBQUERY
用于FROM中的子查询：DERIVED
联合查询中的第一个查询：PRIMARY
联合查询中的第一个查询之后的其它查询：UNION
联合查询生成的临时表：UNION RESULT
table：查询针对的表
type：关联类型，或称为访问类型，即MySQL如何去查询表中的行
ALL：全表扫描；
index：根据索引的顺序进行的全表扫描；但同时如果Extra列出现了"Using index”表示使用了覆盖索引；
range：有范围限制地根据索引实现范围扫描；扫描位置始于索引中的某一项，结束于另一项；
ref：根据索引返回的表中匹配到某单个值的所有行（匹配给定值的行不止一个）；
eq_ref：根据索引返回的表中匹配到某单个值的单一行，仅返回一个行，但需要与某个额外的参考值比较，而不是常数；
const，system：与某个常数比较，且只返回一行；
possiable_keys：查询中可能会用到的索引；
key：查询中使用的索引；
key_len：查询中用到的索引长度；
ref：在利用key字段所显示的索引完成查询操作时所引用的列或常量值；
rows：MySQL估计出的为找到所有的目标项而需要读取的行数；

联合查询两个条件都满Extra：额外信息
                    Using index：使用了覆盖索引进行的查询；
                    Using where：拿到数据后还要再次进行过滤； 
                    Using temporary：使用了临时表以完成查询；
                    Using filesort：对结果使用了一个外部索引排序；足
MariaDB [mydb]> select * from students where age >= 90 union select * from student where name like 'stu100';
ERROR 1146 (42S02): Table 'mydb.student' doesn't exist
MariaDB [mydb]> select * from students where age >= 90 union select * from students where name like 'stu100';
+-------+---------+------+--------+-------------------+
| stuid | name    | age  | gender | major             |
+-------+---------+------+--------+-------------------+
|     1 | baiqian |  255 | M      | yuqingqiankunshan |
|    13 | stu13   |  100 | M      | CHENXI13          |
|    15 | stu15   |   91 | M      | CHENXI15          |
|    31 | stu31   |   97 | M      | CHENXI31          |
|    34 | stu34   |   98 | M      | CHENXI34          |
|    36 | stu36   |   90 | M      | CHENXI36          |
|    39 | stu39   |   90 | M      | CHENXI39          |
|    55 | stu55   |   95 | M      | CHENXI55          |
|    62 | stu62   |   96 | M      | CHENXI62          |
|    63 | stu63   |   98 | M      | CHENXI63          |
|    68 | stu68   |   96 | M      | CHENXI68          |
|    84 | stu84   |  100 | M      | CHENXI84          |
|   100 | stu100  |   97 | M      | CHENXI100         |
|   103 | stu103  |   94 | M      | CHENXI103         |
|   110 | stu110  |   95 | M      | CHENXI110         |
|   123 | stu123  |  100 | M      | CHENXI123         |
|   124 | stu124  |   98 | M      | CHENXI124         |
|   143 | stu143  |   93 | M      | CHENXI143         |
|   149 | stu149  |   97 | M      | CHENXI149         |
|   155 | stu155  |   99 | M      | CHENXI155         |
|   156 | stu156  |   99 | M      | CHENXI156         |
|   157 | stu157  |   91 | M      | CHENXI157         |
|   189 | stu189  |  100 | M      | CHENXI189         |
|   224 | stu224  |   97 | M      | CHENXI224         |
|   235 | stu235  |  100 | M      | CHENXI235         |
|   236 | stu236  |   91 | M      | CHENXI236         |
|   237 | stu237  |   93 | M      | CHENXI237         |
|   244 | stu244  |   97 | M      | CHENXI244         |
|   263 | stu263  |   92 | M      | CHENXI263         |
|   264 | stu264  |   94 | M      | CHENXI264         |
|   266 | stu266  |   99 | M      | CHENXI266         |
|   267 | stu267  |   99 | M      | CHENXI267         |
|   269 | stu269  |   97 | M      | CHENXI269         |
|   278 | stu278  |   93 | M      | CHENXI278         |
|   280 | stu280  |   93 | M      | CHENXI280         |
|   290 | stu290  |   96 | M      | CHENXI290         |
|   298 | stu298  |   96 | M      | CHENXI298         |
|   309 | stu309  |   94 | M      | CHENXI309         |
|   317 | stu317  |   98 | M      | CHENXI317         |
|   330 | stu330  |   95 | M      | CHENXI330         |
|   332 | stu332  |   98 | M      | CHENXI332         |
|   337 | stu337  |   92 | M      | CHENXI337         |
|   345 | stu345  |   99 | M      | CHENXI345         |
|   364 | stu364  |   99 | M      | CHENXI364         |
|   368 | stu368  |   93 | M      | CHENXI368         |
|   386 | stu386  |   93 | M      | CHENXI386         |
|   394 | stu394  |   95 | M      | CHENXI394         |
|   399 | stu399  |   92 | M      | CHENXI399         |
|   411 | stu411  |   90 | M      | CHENXI411         |
|   414 | stu414  |   92 | M      | CHENXI414         |
|   417 | stu417  |   94 | M      | CHENXI417         |
|   429 | stu429  |   94 | M      | CHENXI429         |
|   434 | stu434  |   96 | M      | CHENXI434         |
|   454 | stu454  |  100 | M      | CHENXI454         |
|   460 | stu460  |   99 | M      | CHENXI460         |
|   474 | stu474  |   90 | M      | CHENXI474         |
|   487 | stu487  |   99 | M      | CHENXI487         |
|   488 | stu488  |   93 | M      | CHENXI488         |
|   491 | stu491  |   93 | M      | CHENXI491         |
|   516 | stu516  |   94 | M      | CHENXI516         |
|   517 | stu517  |   91 | M      | CHENXI517         |
|   518 | stu518  |   97 | M      | CHENXI518         |
|   524 | stu524  |   99 | M      | CHENXI524         |
|   534 | stu534  |  100 | M      | CHENXI534         |
|   553 | stu553  |   98 | M      | CHENXI553         |
|   561 | stu561  |   99 | M      | CHENXI561         |
|   583 | stu583  |   92 | M      | CHENXI583         |
|   608 | stu608  |   96 | M      | CHENXI608         |
|   610 | stu610  |   90 | M      | CHENXI610         |
|   619 | stu619  |   93 | M      | CHENXI619         |
|   625 | stu625  |   93 | M      | CHENXI625         |
|   635 | stu635  |   93 | M      | CHENXI635         |
|   647 | stu647  |   97 | M      | CHENXI647         |
|   660 | stu660  |   99 | M      | CHENXI660         |
|   669 | stu669  |   90 | M      | CHENXI669         |
|   681 | stu681  |   95 | M      | CHENXI681         |
|   682 | stu682  |   92 | M      | CHENXI682         |
|   687 | stu687  |   94 | M      | CHENXI687         |
|   724 | stu724  |   93 | M      | CHENXI724         |
|   725 | stu725  |   94 | M      | CHENXI725         |
|   730 | stu730  |   95 | M      | CHENXI730         |
|   749 | stu749  |   90 | M      | CHENXI749         |
|   753 | stu753  |   90 | M      | CHENXI753         |
|   770 | stu770  |   91 | M      | CHENXI770         |
|   771 | stu771  |   96 | M      | CHENXI771         |
|   774 | stu774  |   92 | M      | CHENXI774         |
|   791 | stu791  |  100 | M      | CHENXI791         |
|   813 | stu813  |  100 | M      | CHENXI813         |
|   823 | stu823  |   98 | M      | CHENXI823         |
|   828 | stu828  |   99 | M      | CHENXI828         |
|   836 | stu836  |   96 | M      | CHENXI836         |
|   840 | stu840  |   96 | M      | CHENXI840         |
|   855 | stu855  |   90 | M      | CHENXI855         |
|   872 | stu872  |  100 | M      | CHENXI872         |
|   906 | stu906  |   99 | M      | CHENXI906         |
|   908 | stu908  |   99 | M      | CHENXI908         |
|   915 | stu915  |   98 | M      | CHENXI915         |
|   920 | stu920  |   97 | M      | CHENXI920         |
|   923 | stu923  |   92 | M      | CHENXI923         |
|   925 | stu925  |   94 | M      | CHENXI925         |
|   955 | stu955  |   95 | M      | CHENXI955         |
|   965 | stu965  |   94 | M      | CHENXI965         |
|   973 | stu973  |   97 | M      | CHENXI973         |
|   979 | stu979  |   97 | M      | CHENXI979         |
|   986 | stu986  |  100 | M      | CHENXI986         |
|   997 | stu997  |   93 | M      | CHENXI997         |
|  1003 | stu1003 |   92 | F      | chenxue1003       |
|  1014 | stu1014 |  100 | F      | chenxue1014       |
|  1018 | stu1018 |   92 | F      | chenxue1018       |
|  1027 | stu1027 |   97 | F      | chenxue1027       |
|  1038 | stu1038 |   99 | F      | chenxue1038       |
|  1079 | stu1079 |   91 | F      | chenxue1079       |
|  1105 | stu1105 |   98 | F      | chenxue1105       |
|  1107 | stu1107 |   93 | F      | chenxue1107       |
|  1111 | stu1111 |   90 | F      | chenxue1111       |
|  1122 | stu1122 |   99 | F      | chenxue1122       |
|  1125 | stu1125 |   98 | F      | chenxue1125       |
|  1158 | stu1158 |   95 | F      | chenxue1158       |
|  1159 | stu1159 |   94 | F      | chenxue1159       |
|  1171 | stu1171 |   98 | F      | chenxue1171       |
|  1180 | stu1180 |   92 | F      | chenxue1180       |
|  1195 | stu1195 |   99 | F      | chenxue1195       |
|  1219 | stu1219 |   92 | F      | chenxue1219       |
|  1259 | stu1259 |   96 | F      | chenxue1259       |
|  1262 | stu1262 |   95 | F      | chenxue1262       |
|  1265 | stu1265 |   99 | F      | chenxue1265       |
|  1267 | stu1267 |   94 | F      | chenxue1267       |
|  1278 | stu1278 |   91 | F      | chenxue1278       |
|  1298 | stu1298 |   96 | F      | chenxue1298       |
|  1299 | stu1299 |   90 | F      | chenxue1299       |
|  1301 | stu1301 |   91 | F      | chenxue1301       |
|  1324 | stu1324 |   94 | F      | chenxue1324       |
|  1326 | stu1326 |   90 | F      | chenxue1326       |
|  1345 | stu1345 |   91 | F      | chenxue1345       |
|  1349 | stu1349 |   92 | F      | chenxue1349       |
|  1364 | stu1364 |   98 | F      | chenxue1364       |
|  1369 | stu1369 |   91 | F      | chenxue1369       |
|  1372 | stu1372 |   94 | F      | chenxue1372       |
|  1384 | stu1384 |   99 | F      | chenxue1384       |
|  1386 | stu1386 |   97 | F      | chenxue1386       |
|  1393 | stu1393 |  100 | F      | chenxue1393       |
|  1416 | stu1416 |   91 | F      | chenxue1416       |
|  1420 | stu1420 |   97 | F      | chenxue1420       |
|  1430 | stu1430 |   92 | F      | chenxue1430       |
|  1455 | stu1455 |   91 | F      | chenxue1455       |
|  1479 | stu1479 |   92 | F      | chenxue1479       |
|  1483 | stu1483 |   91 | F      | chenxue1483       |
|  1485 | stu1485 |   95 | F      | chenxue1485       |
|  1487 | stu1487 |   93 | F      | chenxue1487       |
|  1493 | stu1493 |   90 | F      | chenxue1493       |
|  1501 | stu1501 |   93 | F      | chenxue1501       |
|  1507 | stu1507 |   91 | F      | chenxue1507       |
|  1518 | stu1518 |   92 | F      | chenxue1518       |
|  1523 | stu1523 |   98 | F      | chenxue1523       |
|  1528 | stu1528 |  100 | F      | chenxue1528       |
|  1532 | stu1532 |   96 | F      | chenxue1532       |
|  1543 | stu1543 |  100 | F      | chenxue1543       |
|  1546 | stu1546 |   98 | F      | chenxue1546       |
|  1554 | stu1554 |   96 | F      | chenxue1554       |
|  1572 | stu1572 |   99 | F      | chenxue1572       |
|  1583 | stu1583 |   95 | F      | chenxue1583       |
|  1595 | stu1595 |   98 | F      | chenxue1595       |
|  1599 | stu1599 |   97 | F      | chenxue1599       |
|  1603 | stu1603 |   96 | F      | chenxue1603       |
|  1606 | stu1606 |   90 | F      | chenxue1606       |
|  1626 | stu1626 |   93 | F      | chenxue1626       |
|  1629 | stu1629 |  100 | F      | chenxue1629       |
|  1631 | stu1631 |   99 | F      | chenxue1631       |
|  1636 | stu1636 |   92 | F      | chenxue1636       |
|  1637 | stu1637 |  100 | F      | chenxue1637       |
|  1639 | stu1639 |   98 | F      | chenxue1639       |
|  1657 | stu1657 |   97 | F      | chenxue1657       |
|  1659 | stu1659 |   92 | F      | chenxue1659       |
|  1661 | stu1661 |   93 | F      | chenxue1661       |
|  1671 | stu1671 |   99 | F      | chenxue1671       |
|  1681 | stu1681 |   99 | F      | chenxue1681       |
|  1690 | stu1690 |   91 | F      | chenxue1690       |
|  1713 | stu1713 |   96 | F      | chenxue1713       |
|  1714 | stu1714 |   94 | F      | chenxue1714       |
|  1715 | stu1715 |   99 | F      | chenxue1715       |
|  1717 | stu1717 |   92 | F      | chenxue1717       |
|  1720 | stu1720 |   98 | F      | chenxue1720       |
|  1737 | stu1737 |  100 | F      | chenxue1737       |
|  1739 | stu1739 |   94 | F      | chenxue1739       |
|  1755 | stu1755 |   95 | F      | chenxue1755       |
|  1758 | stu1758 |  100 | F      | chenxue1758       |
|  1762 | stu1762 |   92 | F      | chenxue1762       |
|  1772 | stu1772 |   94 | F      | chenxue1772       |
|  1782 | stu1782 |   96 | F      | chenxue1782       |
|  1786 | stu1786 |   96 | F      | chenxue1786       |
|  1798 | stu1798 |   98 | F      | chenxue1798       |
|  1809 | stu1809 |   92 | F      | chenxue1809       |
|  1815 | stu1815 |  100 | F      | chenxue1815       |
|  1821 | stu1821 |   94 | F      | chenxue1821       |
|  1825 | stu1825 |   96 | F      | chenxue1825       |
|  1827 | stu1827 |  100 | F      | chenxue1827       |
|  1857 | stu1857 |   99 | F      | chenxue1857       |
|  1868 | stu1868 |   96 | F      | chenxue1868       |
|  1893 | stu1893 |  100 | F      | chenxue1893       |
|  1900 | stu1900 |   97 | F      | chenxue1900       |
|  1901 | stu1901 |   99 | F      | chenxue1901       |
|  1904 | stu1904 |   95 | F      | chenxue1904       |
|  1908 | stu1908 |   98 | F      | chenxue1908       |
|  1931 | stu1931 |   94 | F      | chenxue1931       |
|  1935 | stu1935 |   96 | F      | chenxue1935       |
|  1937 | stu1937 |   97 | F      | chenxue1937       |
|  1940 | stu1940 |   99 | F      | chenxue1940       |
|  1966 | stu1966 |   93 | F      | chenxue1966       |
|  1969 | stu1969 |   96 | F      | chenxue1969       |
|  1972 | stu1972 |   96 | F      | chenxue1972       |
|  1995 | stu1995 |   96 | F      | chenxue1995       |
|  1998 | stu1998 |   95 | F      | chenxue1998       |
+-------+---------+------+--------+-------------------+
212 rows in set (0.02 sec)
查看该命令的运行结果流程

MariaDB [mydb]> EXPLAIN select * from students where age >= 90 union select * from students where name like 'stu100%';
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-----------------------+
| id   | select_type  | table      | type  | possible_keys | key          | key_len | ref  | rows | Extra                 |
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-----------------------+
|    1 | PRIMARY      | students   | ALL   | NULL          | NULL         | NULL    | NULL | 1565 | Using where           |
|    2 | UNION        | students   | range | name_and_age  | name_and_age | 203     | NULL |   11 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL   | NULL          | NULL         | NULL    | NULL | NULL |                       |
+------+--------------+------------+-------+---------------+--------------+---------+------+------+-----------------------+
3 rows in set (0.00 sec)

最后编辑于：2017.12.10 07:48:20

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 204,445评论 6赞 478
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 85,889评论 2赞 381
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 151,047评论 0赞 337
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 54,760评论 1赞 276
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 63,745评论 5赞 367
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 48,638评论 1赞 281
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 38,011评论 3赞 398
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 36,669评论 0赞 258
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 40,923评论 1赞 299
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 35,655评论 2赞 321
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 37,740评论 1赞 330
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 33,406评论 4赞 320
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 38,995评论 3赞 307
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 29,961评论 0赞 19
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 31,197评论 1赞 260
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 45,023评论 2赞 350
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 42,483评论 2赞 342

mysql的索引介绍

推荐阅读更多精彩内容