MySQL 5.7 索引優化
提升查詢性能最好的方法就是創建索引。索引項就像指向表中行的指針，讓查詢通過WHERE條件快速找到所要查詢的行。
MySQL所有的數據類型都可以創建索引。
不必要的索引會消耗系統的空間和MySQL在判斷使用哪個索引時的時間。索引同樣會增加DML操作的成本，在提升查詢速度和系統資源消耗之間需要找到一種平衡。
--前綴索引
對于字符字段，可以只創建一個索引，這個索引只包含此字段的前N個字符。這樣會使索引更加小。當對BLOB或TEXT字段創建索引時，必須指定前綴索引。
前綴長度最多可以達到1000個字節（對于InnoDB表可以達到767個字節，除非開啟innodb_large_prefix參數）。
--例①
mysql> show variables like '%prefix%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| innodb_large_prefix | ON |
+---------------------+-------+
1 row in set (0.39 sec)
CREATE TABLE test5 (blob_col BLOB, INDEX(blob_col(10)));
Prefixes can be up to 1000 bytes long (767 bytes for InnoDB tables, unless you have innodb_large_prefix set).
mysql> CREATE TABLE test5 (blob_col BLOB, INDEX(blob_col(10)));
Query OK, 0 rows affected (0.27 sec)
mysql> desc test5;
+----------+------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+------+------+-----+---------+-------+
| blob_col | blob | YES | MUL | NULL | |
+----------+------+------+-----+---------+-------+
1 row in set (0.06 sec)
mysql> show keys from test5;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test5 | 1 | blob_col | 1 | blob_col | A | 0 | 10 | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
--例②
LIKE條件中以%開頭的的查詢不會使用索引
mysql> create table emp(id int(2),name varchar(30));
Query OK, 0 rows affected (0.22 sec)
mysql> insert into emp values(1000,'JiaJianning');
Query OK, 1 row affected (0.18 sec)
mysql> insert into emp values(2000,'JiaDingyi');
Query OK, 1 row affected (0.07 sec)
mysql> insert into emp values(3000,'JiaLiying');
Query OK, 1 row affected (0.00 sec)
mysql> insert into emp values(4000,'JiaPeiyuan');
Query OK, 1 row affected (0.09 sec)
mysql> create index idx_title on emp(name(5));
Query OK, 0 rows affected (0.09 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show keys from emp;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> explain select * from emp;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where name like 'Jia%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.16 sec)
mysql> explain select * from emp where name like '%Jia%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 25.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where name like 'jia%';
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--例③
數據類型隱式轉換
mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(2) | YES | | NULL | |
| name | varchar(30) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> show keys from emp;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(2) | YES | | NULL | |
| name | varchar(30) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.00 sec)
mysql> create index idx_emp_id on emp(id);
Query OK, 0 rows affected (0.27 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show keys from emp;
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE | | |
| emp | 1 | idx_emp_id | 1 | id | A | 4 | NULL | NULL | YES | BTREE | | |
+-------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> select * from emp;
+------+-------------+
| id | name |
+------+-------------+
| 1000 | JiaJianning |
| 2000 | JiaDingyi |
| 3000 | JiaLiying |
| 4000 | JiaPeiyuan |
+------+-------------+
4 rows in set (0.00 sec)
mysql> explain select * from emp where id=1000;
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | idx_emp_id | idx_emp_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where id='1000';
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
| 1 | SIMPLE | emp | NULL | ref | idx_emp_id | idx_emp_id | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where id='1000' or id=8000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_emp_id | NULL | NULL | NULL | 4 | 50.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
--全文索引
全文索引用于全文搜索。只有InnoDB和MyISAM存儲引擎支持全文索引，且只適用于CHAR, VARCHAR, TEXT字段。
--空間索引
MyISAM和InnoDB存儲引擎支持空間類型上的R樹索引。
--MEMORY存儲引擎上的索引
MEMORY存儲引擎默認使用哈希索引，但是也支持BTREE索引。
--聯合索引
聯合索引最多可以包含16個字段。mysql> CREATE TABLE test (
-> id INT NOT NULL,
-> last_name CHAR(30) NOT NULL,
-> first_name CHAR(30) NOT NULL,
-> PRIMARY KEY (id),
-> INDEX name (last_name,first_name)
-> );
Query OK, 0 rows affected (0.17 sec)
mysql> insert into test values(1,'Terry','John');
Query OK, 1 row affected (0.07 sec)
mysql> insert into test values(2,'Allice','Hanks');
Query OK, 1 row affected (0.02 sec)
mysql> insert into test values(3,'Lily','Weber');
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(4,'Lucy','Willis');
Query OK, 1 row affected (0.07 sec)
mysql> insert into test values(5,'David','Beckham');
Query OK, 1 row affected (0.01 sec)
mysql> select * from test;
+----+-----------+------------+
| id | last_name | first_name |
+----+-----------+------------+
| 2 | Allice | Hanks |
| 5 | David | Beckham |
| 3 | Lily | Weber |
| 4 | Lucy | Willis |
| 1 | Terry | John |
+----+-----------+------------+
5 rows in set (0.00 sec)
mysql> show keys from test;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | |
| test | 1 | name | 1 | last_name | A | 5 | NULL | NULL | | BTREE | | |
| test | 1 | name | 2 | first_name | A | 5 | NULL | NULL | | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
mysql> explain select * from test where last_name like 'All%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | range | name | name | 30 | NULL | 1 | 100.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.09 sec)
mysql> explain select * from test where last_name = 'All%';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | name | name | 30 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.07 sec)
mysql> explain select * from test where last_name = 'Allice';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | test | NULL | ref | name | name | 30 | const | 1 | 100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where last_name like 'All%' and first_name like 'H%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | range | name | name | 60 | NULL | 1 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.09 sec)
mysql> explain select * from test where first_name like 'H%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where first_name = 'H%';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from test where first_name = 'Hanks';
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
| 1 | SIMPLE | test | NULL | index | NULL | name | 60 | NULL | 5 | 20.00 | Using where; Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)
--B樹索引和哈希索引的對比
B樹索引適用于=, >, >=, <, <= 或 BETWEEN操作符，也適合于LIKE操作符。
哈希索引適用于= 或 <=>操作符。MySQL不能使用哈希索引來加速ORDER BY操作的速度。
--列生成索引
mysql> CREATE TABLE t1 (f1 INT, gc INT AS (f1 + 1) STORED, INDEX (gc));
Query OK, 0 rows affected (0.61 sec)
mysql> desc t1;
+-------+---------+------+-----+---------+------------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------+------+-----+---------+------------------+
| f1 | int(11) | YES | | NULL | |
| gc | int(11) | YES | MUL | NULL | STORED GENERATED |
+-------+---------+------+-----+---------+------------------+
2 rows in set (0.07 sec)
mysql> show index from t1;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t1 | 1 | gc | 1 | gc | A | 0 | NULL | NULL | YES | BTREE | | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> insert into t1(f1) values(1);
Query OK, 1 row affected (0.13 sec)
mysql> insert into t1(f1) values(2);
Query OK, 1 row affected (0.08 sec)
mysql> insert into t1(f1) values(3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into t1(f1) values(4);
Query OK, 1 row affected (0.00 sec)
mysql> insert into t1(f1) values(5);
Query OK, 1 row affected (0.00 sec)
mysql> select * from t1;
+------+------+
| f1 | gc |
+------+------+
| 1 | 2 |
| 2 | 3 |
| 3 | 4 |
| 4 | 5 |
| 5 | 6 |
+------+------+
5 rows in set (0.00 sec)
mysql> explain select * from t1 where f1 = 1;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.09 sec)
mysql> explain select * from t1 where gc = 1;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.04 sec)
mysql> explain select * from t1 where gc = 2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where f1+1 = 2;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where f1+1 = 5;
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | t1 | NULL | ref | gc | gc | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from t1 where f1+1 >= 2;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | t1 | NULL | ALL | gc | NULL | NULL | NULL | 5 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.03 sec)
--對空值的掃描會使用索引
mysql> desc emp;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(2) | YES | | NULL | |
| name | varchar(30) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.07 sec)
mysql> show index from emp;
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| emp | 1 | idx_title | 1 | name | A | 4 | 5 | NULL | YES | BTREE | | |
+-------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)
mysql> select * from emp where name is not null;
+------+-------------+
| id | name |
+------+-------------+
| 1000 | JiaJianning |
| 2000 | JiaDingyi |
| 3000 | JiaLiying |
| 4000 | JiaPeiyuan |
+------+-------------+
4 rows in set (0.01 sec)
mysql> select * from emp where name is null;
Empty set (0.08 sec)
mysql> explain select * from emp where name is not null;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ALL | idx_title | NULL | NULL | NULL | 4 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> explain select * from emp where name is null;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
| 1 | SIMPLE | emp | NULL | ref | idx_title | idx_title | 8 | const | 1 | 100.00 | Using where |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)
mysql> show warnings;
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
| Note | 1003 | /* select#1 */ select `fire`.`emp`.`id` AS `id`,`fire`.`emp`.`name` AS `name` from `fire`.`emp` where isnull(`fire`.`emp`.`name`) |
+-------+------+-----------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.11 sec)
--查看索引的使用情況
Handler_read_rnd_next
數據文件中讀取下一行的請求數。如果執行了大量的全表掃描，則這個參數會的值會很高。通常這個參數用于建議表沒有建立恰當的索引或查詢沒有合理利用現有的索引。
mysql> show global status like 'Handler_read_rnd%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 1521 |
+-----------------------+-------+
2 rows in set (0.00 sec)
