MySQL(索引)
索引：
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。上面都在说使用索引的好处，但过多的使用索引将会造成滥用。
缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。
MySQL中常见索引有：
- 普通索引：仅加速查询。
- 唯一索引：加速查询 + 列值唯一（可以有null）。
- 主键索引：加速查询 + 列值唯一 + 表中只有一个（不可以有null）。
- 组合索引：多列值组成一个索引，专门针对多条件查询。
- 全文索引：对文本的内容进行分词，进行搜索 。
- 索引合并：使用多个单列索引组合搜索。
- 覆盖索引，select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖。
普通索引：
# 先创建一个新的表格 CREATE TABLE People( id INT NOT NULL auto_increment , # 自增 NAME VARCHAR(32) NOT NULL , email VARCHAR(126) , PRIMARY KEY(id) # 设置id为主键 ) ENGINE = INNODB DEFAULT CHARSET = utf8; # 查看索引 show index from People; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ # 我们现在也没有创建索引啊，怎么People中的id就成了索引呢？ 注意：表中的主键默认就是索引。
创建索引：
创建表格后添加：
mysql> create index ix_name on People(name); Query OK, 0 rows affected (0.01 sec) mysql> show index from People; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | people | 1 | ix_name | 1 | NAME | A | 0 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
创建表格时添加索引：
index 索引名称(列(大小)) DROP TABLE if EXISTS People; CREATE TABLE People( id INT NOT NULL auto_increment , name VARCHAR(32) NOT NULL , email VARCHAR(126) , PRIMARY KEY(id), INDEX ix_name(name(32)) ) ENGINE = INNODB DEFAULT CHARSET = utf8; mysql> show index from People; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | people | 1 | ix_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 2 rows in set (0.00 sec) # 删除索引 drop index 索引名称 on 表名; mysql> drop index ix_name on People; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show index from People; +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)
查看索引type：
explain select *from People where email ='tom@123.com' +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | People | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ mysql> explain select *from People where id = 1; +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | People | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from People where name = 'alex'; +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ | 1 | SIMPLE | People | NULL | ref | index_name | index_name | 98 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) # 其中的 type 为 ALL 这就是MySQL默认的搜索type 也是最慢的，而id的type 为const ，name 为 ref 都是快速的索引。
唯一索引：
唯一索引和普通索引差不多，他的索引值必须是具有唯一性,值可以为NULL。如果是组合索引，则列值的组合必须唯一。
作用：加速查询和唯一约束（可含null）
创建表格是添加唯一索引：
unique 索引名称(列名(大小)) CREATE TABLE People( id INT NOT NULL auto_increment , name VARCHAR(32), email VARCHAR(126) not null, PRIMARY KEY(id), UNIQUE ix_name(name(32)) #设置name为唯一索引 ) ENGINE = INNODB DEFAULT CHARSET = utf8;
唯一性：通过往表中插入数据来验证,因为name列是唯一性的，所以，如果重复插入相同的名字就会报错
mysql> desc People; +-------+--------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +-------+--------------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | name | varchar(32) | YES | UNI | NULL | | | email | varchar(126) | NO | | NULL | | +-------+--------------+------+-----+---------+----------------+ 3 rows in set (0.00 sec) mysql> insert into People(name,email)values('alex','alex@123.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into People(name,email)values('alex','alex@123.com'); ERROR 1062 (23000): Duplicate entry 'alex' for key 'ix_name'
可为NULL：
可以为NULL的意思就是可以插入NULL值 mysql> insert into People(name,email)values(NULL,'alex@123.com'); Query OK, 1 row affected (0.00 sec) mysql> insert into People(name,email)values(NULL,'alex@123.com'); Query OK, 1 row affected (0.00 sec) mysql> select *from People; +----+------+--------------+ | id | name | email | +----+------+--------------+ | 1 | alex | alex@123.com | | 3 | NULL | alex@123.com | | 4 | NULL | alex@123.com | +----+------+--------------+ 3 rows in set (0.00 sec) #查看搜索type mysql> explain select *from People where name = 'alex'; +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | People | NULL | const | ix_name | ix_name | 99 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from People where name = NULL; 查找不到 +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from People where name is NULL; +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | People | NULL | ref | ix_name | ix_name | 99 | const | 2 | 100.00 | Using index condition | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> select *from People where name is NULL; +----+------+--------------+ | id | name | email | +----+------+--------------+ | 3 | NULL | alex@123.com | | 4 | NULL | alex@123.com | +----+------+--------------+ 2 rows in set (0.00 sec) # 当设置为NULL的时候，能够查找到结果的写法是 where name is NULL，相应的搜索type 也变成了普通索引的 ref
主键索引：
他的索引值必须是具有唯一性,且索引值不可为NULL。如果是组合索引，则列值的组合必须唯一。
作用：加速查询和唯一约束（不可含null）
创建主键的时候默认的就会指定索引。
mysql> explain select *from People where id = 3; +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | People | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
组合索引：
组合索引是将多个列组合成一个索引。
其应用场景为：频繁的同时使用多个列来进行查询，如：where n1 = 'alex' and n2 = 666。
创建表：
DROP TABLE if EXISTS People; CREATE TABLE People( id INT NOT NULL auto_increment , name VARCHAR(32), email VARCHAR(126) NOT NULL, PRIMARY KEY(id), ) ENGINE = INNODB DEFAULT CHARSET = utf8;
创建索引：
create index ix_name_email on People(name,email);
如上创建组合索引之后，查询：
- name and email -- 使用索引
- name -- 使用索引
- email -- 不使用索引，最左索引
注意：对于同时搜索多个条件时，组合索引的性能好于多个单一索引合并。
索引相关应用：
相关命令：
- 查看表结构：desc 表名;
mysql> desc People;
+-------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+----------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| name | varchar(32) | YES | MUL | NULL | |
| email | varchar(126) | NO | | NULL | |
+-------+--------------+------+-----+---------+----------------+
- 查看索引：show index on 表名;
mysql> show index in People;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| people | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | |
| people | 0 | ix_name | 1 | name | A | 0 | NULL | NULL | YES | BTREE | | |
| people | 0 | ix_name | 2 | email | A | 0 | NULL | NULL | | BTREE | | |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
- 查看执行时间：
set profiling = 1；
SQL语句
show profiles;
mysql> set profiling = 1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> select *from Person where id = 1; +----+------+------+------------+ | id | name | age | part_2_nid | +----+------+------+------------+ | 1 | tom | 11 | 1 | +----+------+------+------------+ 1 row in set (0.00 sec) mysql> show profiles; +----------+------------+----------------------------------+ | Query_ID | Duration | Query | +----------+------------+----------------------------------+ | 1 | 0.00025400 | select *from Person where id = 1 | +----------+------------+----------------------------------+ 1 row in set, 1 warning (0.00 sec)
正确使用索引:
数据库表中添加索引后确实会让查询速度起飞，但前提必须是正确的使用索引来查询，如果以错误的方式使用，则即使建立索引也会不奏效。
即使建立索引，索引也不会生效:
验证时使用的表格：
mysql> select *from Person; +----+-------+------+------------+--------+ | id | name | age | part_2_nid | color | +----+-------+------+------------+--------+ | 1 | tom | 11 | 1 | red | | 2 | jerry | 12 | 2 | yellow | | 3 | jack | 123 | 3 | black | | 4 | rose | 14 | 3 | green | | 5 | coco | NULL | 2 | white | | 6 | kk | NULL | 3 | orange | +----+-------+------+------------+--------+ 6 rows in set (0.00 sec) mysql> show index from Person; +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | person | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | person | 0 | age_index_1 | 1 | age | A | 5 | NULL | NULL | YES | BTREE | | | | person | 1 | part_1_nid | 1 | part_2_nid | A | 3 | NULL | NULL | | BTREE | | | | person | 1 | part_2_nid | 1 | part_2_nid | A | 3 | NULL | NULL | | BTREE | | | | person | 1 | ix_name | 1 | name | A | 6 | NULL | NULL | | BTREE | | | +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> desc Person; +------------+-------------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------+-------------+------+-----+---------+----------------+ | id | int(32) | NO | PRI | NULL | auto_increment | | name | varchar(32) | NO | MUL | NULL | | | age | int(32) | YES | UNI | NULL | | | part_2_nid | int(11) | NO | MUL | NULL | | | color | char(32) | NO | | NULL | | +------------+-------------+------+-----+---------+----------------+ 5 rows in set (0.00 sec)
- like '%xxx'：
mysql> create index ix_name on Person(name(32)); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select *from Person where name like '%om'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 16.67 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
# 正确使用： mysql> explain select *from Person where name like 'T%'; +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | Person | NULL | range | ix_name | ix_name | 34 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
给Person 中的name 建立索引后，用模糊查询的字段开始用%占位符查询用的是全局查询，是不会用索引的，而后边用占位符的话是可以使用查询的。
- 使用函数:
mysql> explain select *from Person where reverse(name) = 'moT'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) # 正确使用： mysql> explain select *from Person where name = reverse('moT'); +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | Person | NULL | ref | ix_name | ix_name | 34 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
- or
# or 条件不会使用索引（两列都建立索引也不会走索引） mysql> show index from Person; +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | person | 0 | PRIMARY | 1 | id | A | 6 | NULL | NULL | | BTREE | | | | person | 0 | age_index_1 | 1 | age | A | 5 | NULL | NULL | YES | BTREE | | | | person | 1 | part_1_nid | 1 | part_2_nid | A | 3 | NULL | NULL | | BTREE | | | | person | 1 | part_2_nid | 1 | part_2_nid | A | 3 | NULL | NULL | | BTREE | | | | person | 1 | ix_name | 1 | name | A | 6 | NULL | NULL | | BTREE | | | +--------+------------+-------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 5 rows in set (0.00 sec) mysql> explain select *from Person where name = 'tom' or color = 'red' and age = 11; +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | age_index_1,ix_name | NULL | NULL | NULL | 6 | 19.44 | Using where | +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from Person where name = 'tom' or age = 11; +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | age_index_1,ix_name | NULL | NULL | NULL | 6 | 33.33 | Using where | +----+-------------+--------+------------+------+---------------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from Person where name = 'tom' or color = 'red'; +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | ix_name | NULL | NULL | NULL | 6 | 30.56 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
- 类型不一致：
指的是如果输入值与搜索类型的值类型不一致，也是不会走索引
mysql> explain select *from Person where name = 888; 搜索类型为int +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | ix_name | NULL | NULL | NULL | 6 | 16.67 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) mysql> explain select *from Person where name = '888'; +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | Person | NULL | ref | ix_name | ix_name | 34 | const | 1 | 100.00 | NULL | +----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
- !=
在搜索的列不为主键的情况下，不走索引。
mysql> explain select *from Person where name != 'tom'; 搜索条件不是主键时 +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | ix_name | NULL | NULL | NULL | 6 | 100.00 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from Person where id != 2; 搜索条件为主键时是走索引的 +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
- >
当搜索条件为主键或者搜索条件为整形时(小数类型不走索引)，可以走索引。注：< 都是走索引的。
mysql> explain select *from Person where name > 'dfds'; 搜索条件不为整形切不是主键 +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | ALL | ix_name | NULL | NULL | NULL | 6 | 83.33 | Using where | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+ mysql> explain select *from Person where id > 1; 搜索条件为主键 +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 5 | 100.00 | Using where | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select *from Person where age > 21; 值为 int 类型 +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ | 1 | SIMPLE | Person | NULL | range | age_index_1 | age_index_1 | 5 | NULL | 1 | 100.00 | Using index condition | +----+-------------+--------+------------+-------+---------------+-------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec)
- order by
当根据索引排序时候，选择的映射如果不是索引，则不走索引
mysql> explain select *from Person order by name desc; 其中的 color 没有索引属性 +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | Person | NULL | ALL | NULL | NULL | NULL | NULL | 6 | 100.00 | Using filesort | +----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select id,name from Person order by name desc; 其中返回的 id 和 name 都是具有索引属性。 +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ | 1 | SIMPLE | Person | NULL | index | NULL | ix_name | 34 | NULL | 6 | 100.00 | Using index | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec)
当索引排序的列为主键时，不管映射是否为索引，会走索引的。
mysql> explain select id,name from Person order by id desc; +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+ | 1 | SIMPLE | Person | NULL | index | NULL | PRIMARY | 4 | NULL | 6 | 100.00 | NULL | +----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
注意事项：
避免使用select*count(1)或count(列) 代替count(*)创建表时尽量时char代替varchar表的字段顺序固定长度的字段优先组合索引代替多个单列索引（经常使用多个条件查询时）尽量使用短索引 -使用连接（JOIN）来代替子查询(Sub-Queries)连表时注意条件类型需一致索引散列值（重复少）不适合建索引，例：性别不适合
Limit 分页：无论是否有索引，limit分页是一个很棒的功能.
假如说我们有1000万条数据，类似论坛里的回贴，那么 我们如果想查询第200万条数据之后的10条数据，该怎样查询呢？
常用的查询方法是 ：
select *from tb limit 2000000, 10;
但是 这种查询方法是属于全局搜索，也就是说搜索的type = ALL 非常浪费时间.
优化方案1（可取，但优化效果不大):
select* from tb where id > (select id from tb limit(20000000-1),1) limit10;
虽然也是优化，但是也就提高了0.X秒左右，效率也没有提高多少。
优化方案2：
可以分页成每页显示10条数据
第一页：1，2，3，4，5，6，7，8，9，10； - select * from tb where id > 0 order asc limit 10;
第二页：11，12，13，14，15，16，17，18，19，20； - select * from tb where id > 10 order asc limit 10;
第N页：.... -select * from tb where id >(n-1)*10 order asc limit 10;
继续优化：
如果说我现在在第一页，但是我想看第4页的数据：那么数据就是从中间就是间隔了（4-1）*10 = 30，从起始值开始算的话，就是0+30 = 30 ，第4页就是从30以后的10条数据。
这样我们就可以先获取从第4页的 id 起始索引:
第四页的起始索引 = select id from (select id from tb where id > 0 order by asc limit 30) as A order by desc limit 1;
然后就开始从这个索引的起始开始查找数据：
select *from tb where id > (select id from (select id from tb where id > 0 order by asc limit 30) as A order by desc limit 1; ) limit 10;
这种方式唯一的缺点就是说如果跳转的数据太多的话 ，是需要 将中间的数据都查找的过程是比较耗时。
执行计划：
prolain MySQL 查询语句：
id
查询顺序标识
如：mysql> explain select * from (select nid,name from tb1 where nid < 10) as B;
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 9 | NULL |
| 2 | DERIVED | tb1 | range | PRIMARY | PRIMARY | 8 | NULL | 9 | Using where |
+----+-------------+------------+-------+---------------+---------+---------+------+------+-------------+
特别的：如果使用union连接气值可能为null
select_type
查询类型
SIMPLE 简单查询
PRIMARY 最外层查询
SUBQUERY 映射为子查询
DERIVED 子查询
UNION 联合
UNION RESULT 使用联合的结果
...
table
正在访问的表名
type
查询时的访问方式，性能：all < index < range < index_merge < ref_or_null < ref < eq_ref < system/const
ALL 全表扫描，对于数据表从头到尾找一遍
select * from tb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select * from tb1 where email = 'seven@live.com'
select * from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nid from tb1;
RANGE 对索引列进行范围查找
select * from tb1 where name < 'alex';
PS:
between and
in
> >= < <= 操作
注意：!= 和 > 符号
INDEX_MERGE 合并索引，使用多个单列索引搜索
select * from tb1 where name = 'alex' or nid in (11,22,33);
REF 根据索引查找一个或多个值
select * from tb1 where name = 'seven';
EQ_REF 连接时使用primary key 或 unique类型
select tb2.nid,tb1.name from tb2 left join tb1 on tb2.nid = tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nid from tb1 where nid = 2 ;
SYSTEM 系统
表仅有一行(=系统表)。这是const联接类型的一个特例。
select * from (select nid from tb1 where nid = 1) as A;
possible_keys
可能使用的索引
key
真实使用的
key_len
MySQL中使用索引字节长度
rows
mysql估计为了找到所需的行而要读取的行数 ------ 只是预估值
extra
该列包含MySQL解决查询的详细信息
“Using index”
此值表示mysql将使用覆盖索引，以避免访问表。不要把覆盖索引和index访问类型弄混了。
“Using where”
这意味着mysql服务器将在存储引擎检索行后再进行过滤，许多where条件里涉及索引中的列，当（并且如果）它读取索引时，就能被存储引擎检验，因此不是所有带where子句的查询都会显示“Using where”。有时“Using where”的出现就是一个暗示：查询可受益于不同的索引。
“Using temporary”
这意味着mysql在对查询结果排序时会使用一个临时表。
“Using filesort”
这意味着mysql会对结果使用一个外部索引排序，而不是按索引次序从表里读取行。mysql有两种文件排序算法，这两种排序方式都可以在内存或者磁盘上完成，explain不会告诉你mysql将使用哪一种文件排序，也不会告诉你排序会在内存里还是磁盘上完成。
“Range checked for each record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
更多：
