MySQL 5.7 索引优化

水深无声 2024-04-18 02:02 47阅读 0赞

提升查询性能最好的方法就是创建索引。索引项就像指向表中行的指针，让查询通过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)

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-2121854/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-2121854/

发表评论取消回复

表情：

评论列表（有 0 条评论，47人围观）

还没有评论，来说两句吧...

相关阅读

相关 mysql索引优化

mysql常见的瓶颈 cpu：CPU饱和一般发生在数据装入内存或从磁盘上读取数据的时候 IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候数据库查询慢该怎么办

以你之姓@/ 2022年12月13日 14:20/ 0 赞/ 195 阅读

相关 mysql innodb 索引优化_MySQL索引优化

一、Btree索引和Hash索引索引是在存储引擎层实现的，因此不同存储引擎的索引其底层实现不同。 1.1 B-tree索引特点：以B+树的结构存储数据更适合进

红太狼/ 2022年10月25日 01:18/ 0 赞/ 319 阅读

相关 MySQL索引优化

在MySQL中，我们经常会为表中的某些字段建立索引，那么怎么通过索引优化我们的查询呢？索引的分类与基本概念索引可以分为以下五种：普通索引：仅加速查询。

一时失言乱红尘/ 2022年10月23日 08:09/ 0 赞/ 205 阅读

相关 MySql 索引优化

在了解EXPLAIN 关键字之后呢，我们就可以根据一些属性查看我们的SQL 语句是否是高效的，从而使我们编写出高效的SQL 语句，接下来就是一些实际应用啦。我在这里建立了一张表

待我称王封你为后i/ 2022年06月05日 11:08/ 0 赞/ 368 阅读

相关 mysql索引优化

索引有什么用处呢？可以提高查询效率可以提高排序效率也可以提高分组效率我们什么时候使用索引呢？索引是不能随便使用的，单独的索引是没有意义的。比

我就是我/ 2022年06月04日 02:56/ 0 赞/ 354 阅读

相关【MySQL】——索引优化

温故-索引类型聚集索引和非聚集索引 MyIASM引擎，B+树的数据结构中存储的内容实际上是实际数据的地址值。也就是说它的索引和实际数据是分开的，只不过使用索引指向了

偏执的太偏执、/ 2022年05月15日 22:06/ 0 赞/ 311 阅读

相关 MySQL索引优化

MySQL索引优化一、MySQL索引基础首先，我们将从索引基础开始介绍一下什么是索引，分析索引的几种类型，并探讨一下如何创建索引以及索引设计的基本原则。此部

电玩女神/ 2022年03月22日 14:15/ 0 赞/ 377 阅读

相关 MySQL 索引优化

MySQL 索引优化 \------------------------------------------------------------------------

爱被打了一巴掌/ 2022年02月17日 02:07/ 0 赞/ 408 阅读

相关 MySQL--MySQL索引优化

[2019独角兽企业重金招聘Python工程师标准>>> ][2019_Python_] ![hot3.png][] 本博客是用来记录自己学习的过程 1.独立的列进

傷城~/ 2022年01月14日 03:43/ 0 赞/ 397 阅读

相关 mysql 索引优化

背景首先是因为一个慢sql带来的思考与建议。随着2017双11即将来临，大家也都进入了备战状态。稳定性保障是我们双11核心目标的第一大目标，是其

今天药忘吃喽~/ 2021年09月22日 00:18/ 0 赞/ 616 阅读