索引：
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
组合索引，即一个索包含多个列。
创建索引时，你需要确保该索引是应用在 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.01sec)
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 TABLEifEXISTS 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.00sec)#删除索引
drop index 索引名称 on 表名;
mysql>drop index ix_name on People;
Query OK, 0 rows affected (0.01sec)
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.00sec)
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.00sec)#其中的 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.00sec)
mysql>insert into People(name,email)values('alex','alex@123.com');
Query OK,1 row affected (0.00sec)
mysql> insert into People(name,email)values('alex','alex@123.com');
ERROR1062 (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.00sec)
mysql> insert into People(name,email)values(NULL,'alex@123.com');
Query OK,1 row affected (0.00sec)
mysql> select *fromPeople;+----+------+--------------+
| id | name | email |
+----+------+--------------+
| 1 | alex | alex@123.com |
| 3 | NULL | alex@123.com |
| 4 | NULL | alex@123.com |
+----+------+--------------+
3 rows in set (0.00sec)#查看搜索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.00sec)
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.00sec)
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.00sec)
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.00sec)#当设置为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 ifEXISTS 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.00sec)
mysql> select *from Person where id = 1;+----+------+------+------------+
| id | name | age | part_2_nid |
+----+------+------+------------+
| 1 | tom | 11 | 1 |
+----+------+------+------------+
1 row in set (0.00sec)
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.00sec)
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.00sec)
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.01sec)
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.00sec)
# 正确使用：
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.00sec)#正确使用：
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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.00sec)
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
表的字段顺序固定长度的字段优先
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 | | 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* fromtb1;
特别的：如果有limit限制，则找到之后就不在继续向下扫描
select* from tb1 where email = 'seven@live.com'select* from tb1 where email = 'seven@live.com' limit 1;
虽然上述两个语句都会进行全表扫描，第二句使用了limit，则找到一个后就不再继续扫描。
INDEX 全索引扫描，对索引从头到尾找一遍
select nidfromtb1;
RANGE 对索引列进行范围查找
select* from tb1 where name < 'alex';
PS:
betweenand
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.namefrom tb2 left join tb1 on tb2.nid =tb1.nid;
CONST 常量
表最多有一个匹配行,因为仅有一行,在这行的列值可被优化器剩余部分认为是常数,const表很快,因为它们只读取一次。
select nidfrom 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 checkedforeach record(index map: N)”
这个意味着没有好用的索引，新的索引将在联接的每一行上重新估算，N是显示在possible_keys列中索引的位图，并且是冗余的。
更多：
956
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
