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)
不必要的索引會消耗系統的空間和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)
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26506993/viewspace-2121854/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- MySQL索引優化MySql索引優化
- mysql優化索引MySql優化索引
- MySQL 5.7 ORDER BY排序的優化MySql排序優化
- MySQL 5.7 跟蹤優化器MySql優化
- MySQL 效能優化之索引優化MySql優化索引
- Mysql索引優化（一）MySql索引優化
- MySQL調優之索引優化MySql索引優化
- MySQL 字串索引優化方案MySql字串索引優化
- MySQL 索引原理以及優化MySql索引優化
- mysql索引原理及優化MySql索引優化
- MySQL——索引優化實戰MySql索引優化
- MySQL 筆記 - 索引優化MySql筆記索引優化
- MySQL優化之索引解析MySql優化索引
- Mysql索引優化之索引的分類MySql索引優化
- MySQL 5.7資料庫引數優化MySql資料庫優化
- MySQL函式索引及優化MySql函式索引優化
- MySQL 索引使用策略及優化MySql索引優化
- MySQL索引與查詢優化MySql索引優化
- mysql索引的使用和優化MySql索引優化
- mysql優化篇（基於索引）MySql優化索引
- 理解 MySQL（2）：索引與優化MySql索引優化
- MySQL 索引優化全攻略MySql索引優化
- MySQL-效能優化-索引和查詢優化MySql優化索引
- 淺談MySQL的B樹索引與索引優化MySql索引優化
- 「MySQL」高效能索引優化策略MySql索引優化
- MySQL 效能優化——B+Tree 索引MySql優化索引
- MySQL-索引優化全攻略MySql索引優化
- mysql索引優化和TCP協議MySql索引優化TCP協議
- MySQL效能優化之索引設計MySql優化索引
- mysql 語句的索引和優化MySql索引優化
- mysql索引覆蓋掃描優化MySql索引優化
- MySQL SQL 優化之覆蓋索引MySql優化索引
- MYSQL索引優化思維導圖MySql索引優化
- 【MySQL】效能優化之 覆蓋索引MySql優化索引
- 瞭解MySQL-索引與優化MySql索引優化
- MySQL 5.7 優化不能只看執行計劃MySql優化
- mysql效能優化-慢查詢分析、優化索引和配置MySql優化索引
- 效能調優-Mysql索引資料結構詳解與索引優化MySql索引資料結構優化