达州做网站
大橙子网站建设，新征程启航
为企业提供网站建设、域名注册、服务器等服务
MySQL5.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)
网站题目：MySQL5.7索引优化
网页地址：http://dzwzjz.com/article/gjdpes.html
不必要的索引会消耗系统的空间和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)
网站题目：MySQL5.7索引优化
网页地址：http://dzwzjz.com/article/gjdpes.html