1.1 索引的介绍
索引是对数据库表中一列或多列的值进行排序的一种构造，应用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜寻所有的行相比，索引有助于更快地获取信息。
索引的一个次要目标就是放慢检索表中数据的办法，亦即能帮助信息搜寻者尽快的找到合乎限度条件的记录ID的辅助数据结构。
1.1.1 惟一索引
惟一索引是不容许其中任何两行具备雷同索引值的索引。当现有数据中存在反复的键值时，大多数数据库不容许将新创建的惟一索引与表一起保留。数据库还可能避免增加将在表中创立反复键值的新数据。
例如，如果在employee表中职员的姓(lname)上创立了惟一索引，则任何两个员工都不能同姓。
1.1.2 主键索引
数据库表常常有一列或多列组合，其值惟一标识表中的每一行。该列称为表的主键。在数据库关系图中为表定义主键将主动创立主键索引，主键索引是惟一索引的特定类型。
该索引要求主键中的每个值都惟一。当在查问中应用主键索引时，它还容许对数据的快速访问。
1.1.3 汇集索引
在汇集索引中，表中行的物理程序与键值的逻辑（索引）程序雷同。一个表只能蕴含一个汇集索引。如果某索引不是汇集索引，则表中行的物理程序与键值的逻辑程序不匹配。与非汇集索引相比，汇集索引通常提供更快的数据访问速度。
汇集索引和非汇集索引的区别，如字典默认按字母程序排序，读者如晓得某个字的读音可依据字母程序疾速定位。因而汇集索引和表的内容是在一起的。如读者需查问某个生僻字，则需按字典后面的索引，举例按偏旁进行定位，找到该字对应的页数，再关上对应页数找到该字。
这种通过两个中央而查问到某个字的形式就如非汇集索引。
1.1.4 索引列
能够基于数据库表中的单列或多列创立索引。多列索引能够辨别其中一列可能有雷同值的行。如果常常同时搜寻两列或多列或按两列或多列排序时，索引也很有帮忙。
例如，如果常常在同一查问中为姓和名两列设置判据，那么在这两列上创立多列索引将很有意义。
查看查问的WHERE和JOIN子句。在任一子句中包含的每一列都是索引能够抉择的对象。对新索引进行试验以查看它对运行查问性能的影响。思考已在表上创立的索引数量。最好防止在单个表上有很多索引。
查看已在表上创立的索引的定义。最好防止蕴含共享列的重叠索引。
查看某列中惟一数据值的数量，并将该数量与表中的行数进行比拟。比拟的后果就是该列的可选择性，这有助于确定该列是否适宜建设索引，如果适宜，确定索引的类型。
1.1.5 B树算法
B树的搜寻，从根结点开始，如果查问的关键字与结点的关键字相等，那么就命中；否则，如果查问关键字比结点关键字小，就进入右边；如果比结点关键字大，就进入左边；如果右边或左边的指针为空，则报告找不到相应的要害。
如果B树的所有非叶子结点的左右子树的结点数目均放弃差不多（均衡），那么B树的搜寻性能迫近二分查找；但它比间断内存空间的二分查找的长处是，扭转B树结构（插入与删除结点）不须要挪动大段的内存数据，甚至通常是常数开销。
1.1.6 B+树算法
B+树是B-树的变体，也是一种多路搜寻树：
1.其定义根本与B-树同，除了： 2.非叶子结点的子树指针与关键字个数雷同； 3.非叶子结点的子树指针P[i]，指向关键字值属于[K[i], K[i+1])的子树（B-树是开区间）； 5.为所有叶子结点减少一个链指针； 6.所有关键字都在叶子结点呈现； 如：（M=3）
B+的搜寻与B-树也基本相同，区别是B+树只有达到叶子结点才命中（B-树能够在非叶子结点命中），其性能也等价于在关键字选集做一次二分查找；
B+的个性：
1.所有关键字都呈现在叶子结点的链表中（浓密索引），且链表中的关键字恰好是有序的； 2.不可能在非叶子结点命中； 3.非叶子结点相当于是叶子结点的索引（稠密索引），叶子结点相当于是存储（关键字）数据的数据层； 4.更适宜文件索引零碎；1.1.7 HASH：HASH算法
哈希索引只有Memory, NDB两种引擎反对，Memory引擎默认反对哈希索引，如果多个hash值雷同，呈现哈希碰撞，那么索引以链表形式存储。
然而，Memory引擎表只对可能适宜机器的内存切实无限的数据集。
要使InnoDB或MyISAM反对哈希索引，能够通过伪哈希索引来实现，叫自适应哈希索引。
次要通过减少一个字段，存储hash值，将hash值建设索引，在插入和更新的时候，建设触发器，主动增加计算后的hash到表里。
1.1.8 其余的索引
FULLTEXT：全文索引RTREE：R树索引1.2 MySQL索引治理
索引建设在表的列上(字段)的。
在where前面的列建设索引才会放慢查问速度。
pages<---索引（属性）<----查数据。
增加索引的办法：
alter table test add index index_name(name);create index index_name on test(name);语法格局:
alter table 表 add index 索引名称(name);1.2.1 创立一般索引
创立一般索引办法一：
mysql> ALTER TABLE PLAYERS ADD INDEX name_idx(NAME);mysql> desc PLAYERS;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| NAME | char(15) | NO | MUL | NULL | |创立一般索引办法二：
mysql> ALTER TABLE PLAYERS ADD INDEX name_idx(NAME);mysql> desc PLAYERS;+------------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+------------+-------------+------+-----+---------+-------+| NAME | char(15) | NO | MUL | NULL | |1.2.2 删除索引
alter table PLAYERS delete INDEX name_idx;mysql> show index from PLAYERS\G*************************** 1. row *************************** Table: PLAYERS Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: PLAYERNO Collation: A Cardinality: 14 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment:1.3 MySQL中的束缚索引
主键索引
只能有一个主键。
主键索引:列的内容是惟一值,例如学号.
表创立的时候至多要有一个主键索引，最好和业务无关。
一般索引
放慢查问速度，工作中优化数据库的要害。
在适合的列上建设索引，让数据查问更高效。
create index index_name on test(name);alter table test add index index_name(name);用了索引，查一堆内容。
在where条件关键字前面的列建设索引才会放慢查问速度.
select id,name from test where state=1 order by id group by name;惟一索引
内容惟一，但不是主键。
create unique index index_name on test(name);1.3.1 创立主键索引
建设表时
CREATE TABLE `test` (`id` int(4) NOT NULL AUTO_INCREMENT,`name` char(20) NOT NULL,PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=UTF8;建设表后减少
CREATE TABLE `test` (`id` int(4) NOT NULL,`name` char(20) NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=UTF8;减少自增主键
alter table test change id id int(4) primary key not null auto_increment;1.3.2 应用字段前缀创立索引及联结索引
前缀索引：依据字段的前N个字符建设索引
create index index_name on test(name(8));联结索引：多个字段建设一个索引。
where a女生 and b身高165 and c身材好index(a,b,c)特点：前缀失效个性。
a,ab,abc 能够走索引。b ac bc c 不走索引(5.6之后 ac 能够走主键索引)。准则：把最罕用来作为条件查问的列放在后面。
*示例:*
创立表
create table people (id int not null auto_increment ,name char(20),sr(20),sex int ,age int, primary key (id));创立联结索引
mysql> alter table people add key name_sex_idx(name,sex) -> ;Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0查看索引的类型
mysql> desc people;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(20) | YES | MUL | NULL | || sex | int(11) | YES | | NULL | || age | int(11) | YES | | NULL | |+-------+----------+------+-----+---------+----------------+建设惟一键索引
mysql> alter table people add unique key age_uidx(age);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0查看数据表
mysql> desc people;+-------+----------+------+-----+---------+----------------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+----------------+| id | int(11) | NO | PRI | NULL | auto_increment || name | char(20) | YES | MUL | NULL | || sex | int(11) | YES | | NULL | || age | int(11) | YES | UNI | NULL | |+-------+----------+------+-----+---------+----------------+4 rows in set (0.00 sec)联结主键是联结索引的非凡模式
PRIMARY KEY (`Host`,`User`)alter table test add sex char(4) not null;create index ind_name_sex on test(name,sex);前缀加联结索引
create index index_name on test(name(8),sex(2));1.4 SQL语句优化
1.4.1 企业SQL优化思路
1、把一个大的不应用索引的SQL语句依照性能进行拆分
2、长的SQL语句无奈应用索引，能不能变成2条短的SQL语句让它别离应用上索引。
3、对SQL语句性能的拆分和批改
4、缩小“烂”SQL由运维（DBA）和开发交换（确认），独特确定如何改，最终由DBA执行
5、制订开发流程
1.4.2 不适宜走索引的场景
1、惟一值少的列上不适宜建设索引或者建设索引效率低。例如：性别列
2、小表能够不建设索引，100条记录。
3、对于数据仓库，大量全表扫描的状况，建索引反而会慢
1.4.3 查看表的惟一值数量
select count(distinct user) from mysql.user;select count(distinct user,host) from mysql.user;1.4.4 建设索引流程
1、找到慢SQL。
show processlist;记录慢查问日志。
2、explain select句,条件列多。
3、查看表的惟一值数量：
select count(distinct user) from mysql.user;select count(distinct user,host) from mysql.user;条件列多。能够思考建设联结索引。
4、建设索引(流量低谷)
force index5、拆开语句（和开发）。
6、like '%%'不必mysql
7、进行判断反复的行数
查看行数:
mysql> select count(*) from city;+----------+| count(*) |+----------+| 4079 |+----------+1 row in set (0.00 sec)查看去重后的行数：
mysql> select count(distinct countrycode) from city;+-----------------------------+| count(distinct countrycode) |+-----------------------------+| 232 |+-----------------------------+1 row in set (0.00 sec)1.5 用explain查看SQL的执行打算
在工作中，咱们用于捕获性能问题最罕用的就是关上慢查问，定位执行效率差的SQL，那么当咱们定位到一个SQL当前还不算完事，咱们还须要晓得该SQL的执行打算，比方是全表扫描，还是索引扫描，这些都须要通过EXPLAIN去实现。
EXPLAIN命令是查看优化器如何决定执行查问的次要办法。能够帮忙咱们深刻理解MySQL的基于开销的优化器，还能够取得很多可能被优化器思考到的拜访策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采纳。
须要留神的是，生成的QEP并不确定，它可能会依据很多因素产生扭转。MySQL不会将一个QEP和某个给定查问绑定，QEP将由SQL语句每次执行时的理论状况确定，即使应用存储过程也是如此。只管在存储过程中SQL语句都是事后解析过的，但QEP依然会在每次调用存储过程的时候才被确定。
1.5.1 查看 select 语句的执行过程
mysql> explain select id,name from test where name='clsn';+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+| 1 | SIMPLE | test | ref | name_idx | name_idx | 24 | const | 1 | Using where |+----+-------------+-------+------+---------------+----------+---------+-------+------+-------------+1 row in set (0.00 sec)SQL_NO_CACHE的作用是禁止缓存查问后果。
应用where条件查找
mysql> explain select user,host from mysql.user where user='root' and host='127.0.0.1';+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+| 1 | SIMPLE | user | const | PRIMARY | PRIMARY | 228 | const,const | 1 | Using index |+----+-------------+-------+-------+---------------+---------+---------+-------------+------+-------------+1 row in set (0.00 sec)1.5.2 通过执行打算能够晓得什么？
mysql> explain select d1.age, t2.id from (select age,name from t1 where id in (1,2))d1, t2 where d1.age=t2.age group by d1.age, t2.id order by t2.id;+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+| 1 | PRIMARY | <derived2> | ALL | NULL | NULL | NULL | NULL | 2 | Using temporary; Using filesort || 1 | PRIMARY | t2 | ref | age | age | 5 | d1.age | 1 | Using where; Using index || 2 | DERIVED | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |+----+-------------+------------+-------+---------------+---------+---------+--------+------+---------------------------------+3 rows in set (0.00 sec)1.5.3 MySQL执行打算调用形式
1.EXPLAIN SELECT ……2.EXPLAIN EXTENDED SELECT …… 将执行打算"反编译"成SELECT语句，运行SHOW WARNINGS 可失去被MySQL优化器优化后的查问语句3.EXPLAIN PARTITIONS SELECT …… 用于分区表的EXPLAIN生成QEP的信息1.5.4 执行打算蕴含的信息
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1.5.5 id
蕴含一组数字，示意查问中执行select子句或操作表的程序
【示例一】id雷同，执行程序由上至下
mysql> explain select t2.* from t1, t2, t3 where t1.id=t2.id and t1.id=t3.id and t1.name='';+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+| 1 | SIMPLE | t1 | ref | PRIMARY,name | name | 63 | const | 1 | Using where; Using index || 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | || 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | Using index |+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+3 rows in set (0.00 sec)【示例二】如果是子查问，id的序号会递增，id值越大优先级越高，越先被执行
mysql> explain select t2.* from t2 where id = (select id from t1 where id = (select t3.id from t3 where t3.name=''));+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables || 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table || 3 | SUBQUERY | t3 | ref | name | name | 63 | | 1 | Using where; Using index |+----+-------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+3 rows in set (0.00 sec)【示例三】id如果雷同，能够认为是一组，从上往下程序执行；在所有组中，id值越大，优先级越高，越先执行
mysql> explain select t2.* from (select t3.id from t3 where t3.name='')s1, t2 where s1.id=t2.id;+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | || 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | || 2 | DERIVED | t3 | ref | name | name | 63 | | 1 | Using where; Using index |+----+-------------+------------+--------+---------------+---------+---------+-------+------+--------------------------+3 rows in set (0.00 sec)1.5.6 select_type
示查问中每个select子句的类型（简略OR简单） a. SIMPLE：查问中不蕴含子查问或者UNION b. 查问中若蕴含任何简单的子局部，最外层查问则被标记为：PRIMARY c. 在SELECT或WHERE列表中蕴含了子查问，该子查问被标记为：SUBQUERY d. 在FROM列表中蕴含的子查问被标记为：DERIVED（衍生）用来示意蕴含在from子句中的子查问的select，mysql会递归执行并将后果放到一个长期表中。服务器外部称为"派生表"，因为该长期表是从子查问中派生进去的 e. 若第二个SELECT呈现在UNION之后，则被标记为UNION；若UNION蕴含在FROM子句的子查问中，外层SELECT将被标记为：DERIVED f. 从UNION表获取后果的SELECT被标记为：UNION RESULT阐明：
SUBQUERY和UNION还能够被标记为DEPENDENT和UNCACHEABLE。DEPENDENT意味着select依赖于外层查问中发现的数据。UNCACHEABLE意味着select中的某些 个性阻止后果被缓存于一个item_cache中。【示例】
mysql> explain select d1.name, ( select id from t3) d2 from (select id,name from t1 where name='')d1 union (select name,id from t2);+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+| 1 | PRIMARY | <derived3> | system | NULL | NULL | NULL | NULL | 0 | const row not found || 3 | DERIVED | t1 | ref | name | name | 63 | | 1 | Using where; Using index || 2 | SUBQUERY | t3 | index | NULL | age | 5 | NULL | 6 | Using index || 4 | UNION | t2 | index | NULL | name | 63 | NULL | 4 | Using index || NULL | UNION RESULT | <union1,4> | ALL | NULL | NULL | NULL | NULL | NULL | |+----+--------------+------------+--------+---------------+------+---------+------+------+--------------------------+5 rows in set (0.00 sec)内容阐明：
第一行：id列为1，示意第一个select，select_type列的primary表 示该查问为外层查问，table列被标记为<derived3>，示意查问后果来自一个衍生表，其中3代表该查问衍生自第三个select查问，即id为3的select。第二行：id为3，示意该查问的执行秩序为2（ 4 => 3），是整个查问中第三个select的一部分。因查问蕴含在from中，所以为derived。第三行：select列表中的子查问，select_type为subquery，为整个查问中的第二个select。第四行：select_type为union，阐明第四个select是union里的第二个select，最先执行。第五行：代表从union的长期表中读取行的阶段，table列的<union1,4>示意用第一个和第四个select的后果进行union操作。1.5.7 type
示意MySQL在表中找到所需行的形式，又称“拜访类型”，常见类型如下:
ALL, index, range, ref, eq_ref, const, system, NULL从左到右，性能从最差到最好
【示例一】ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
mysql> explain select * from t1 where email='';+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using where |+----+-------------+-------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)【示例二】index：Full Index Scan，index与ALL区别为index类型只遍历索引树
mysql> explain select id from t1;+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | t1 | index | NULL | age | 5 | NULL | 4 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)【示例三】range:索引范畴扫描，对索引的扫描开始于某一点，返回匹配值域的行。
不言而喻的索引范畴扫描是带有between或者where子句里带有<, >查问。当mysql应用索引去查找一系列值时，例如IN()和OR列表，也会显示range（范畴扫描）,当然性能下面是有差别的。
mysql> explain select * from t1 where id in (1,4);+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from t1 where id between 1 and 4;+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> explain select * from t1 where id=1 or id=4; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.01 sec)mysql> explain select * from t1 where id > 1; +----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 3 | Using where |+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+1 row in set (0.00 sec)【示例四】ref：应用非惟一索引扫描或者惟一索引的前缀扫描，返回匹配某个独自值的记录行
mysql> explain select * from t1 where name='guo';+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+| 1 | SIMPLE | t1 | ref | name | name | 63 | const | 1 | Using where |+----+-------------+-------+------+---------------+------+---------+-------+------+-------------+1 row in set (0.00 sec)【示例五】eq_ref：相似ref，区别就在应用的索引是惟一索引，对于每个索引键值，表中只有一条记录匹配，简略来说，就是多表连贯中应用primary key或者 unique key作为关联条件。
mysql> explain select t1.name from t1, t2 where t1.id=t2.id;+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+| 1 | SIMPLE | t1 | index | PRIMARY | name | 63 | NULL | 4 | Using index || 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | test.t1.id | 1 | Using index |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+2 rows in set (0.00 sec)【示例六】const、system：当MySQL对查问某局部进行优化，并转换为一个常量时，应用这些类型拜访。
如将主键置于where列表中，MySQL就能将该查问转换为一个常量
mysql> explain select * from ( select * from t1 where id=1)b1;+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | || 2 | DERIVED | t1 | const | PRIMARY | PRIMARY | 4 | | 1 | |+----+-------------+------------+--------+---------------+---------+---------+------+------+-------+2 rows in set (0.00 sec)*注：*system是const类型的特例，当查问的表只有一行的状况下，应用system
【示例七】NULL：MySQL在优化过程中合成语句，执行时甚至不必拜访表或索引， 例如从一个索引列里选取最小值能够通过独自索引查找实现。
mysql> explain select * from t1 where id = (select min(id) from t2);+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+| 1 | PRIMARY | t1 | const | PRIMARY | PRIMARY | 4 | const | 1 | || 2 | SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+-------+---------------+---------+---------+-------+------+------------------------------+2 rows in set (0.00 sec)1.5.8 possible_keys
指出MySQL能应用哪个索引在表中找到记录，查问波及到的字段上若存在索引，则该索引将被列出，但不肯定被查问应用
1.5.9 key
显示MySQL在查问中理论应用的索引，若没有应用索引，显示为NULL
【示例】
mysql> explain select id,age from t1;+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | t1 | index | NULL | age | 5 | NULL | 4 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)1.5.10 key_len
示意索引中应用的字节数，可通过该列计算查问中应用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非理论应用长度，即key_len是依据表定义计算而得，不是通过表内检索出的）。
1.5.11 ref
示意上述表的连贯匹配条件，即哪些列或常量被用于查找索引列上的值。
1.5.12 rows
示意MySQL依据表统计信息及索引选用状况，估算的找到所需的记录所须要读取的行数。
【示例】
mysql> explain select * from t1 , t2 where t1.id=t2.id and t2.name='atlas';+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+| 1 | SIMPLE | t2 | ref | PRIMARY,name | name | 63 | const | 1 | Using where || 1 | SIMPLE | t1 | eq_ref | PRIMARY | PRIMARY | 4 | test.t2.id | 1 | |+----+-------------+-------+--------+---------------+---------+---------+------------+------+-------------+2 rows in set (0.00 sec)1.5.13 Extra
蕴含不适宜在其余列中显示但非常重要的额定信息
【示例一】Using index
该值示意相应的select操作中应用了笼罩索引（Covering Index）
mysql> explain select id from t1;+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | t1 | index | NULL | age | 5 | NULL | 4 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)笼罩索引（Covering Index）
MySQL能够利用索引返回select列表中的字段，而不用依据索引再次读取数据文件
蕴含所有满足查问须要的数据的索引称为笼罩索引（Covering Index）
留神：如果要应用笼罩索引，肯定要留神select列表中只取出须要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查问性能降落
【示例二】Using where
示意mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里波及索引中的列，当（并且如果）它读取索引时，就能被存储引擎测验，因而不是所有带where字句的查问都会显示"Using where"。
有时"Using where"的呈现就是一个暗示：查问可受害与不同的索引。
mysql> explain select id,name from t1 where id<4;+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+| 1 | SIMPLE | t1 | index | PRIMARY | name | 63 | NULL | 4 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+--------------------------+1 row in set (0.00 sec)【示例三】Using temporary
示意MySQL须要应用长期表来存储后果集，常见于排序和分组查问
这个值示意应用了外部长期(基于内存的)表。一个查问可能用到多个长期表。有很多起因都会导致MySQL在执行查问期间创立长期表。两个常见的起因是在来自不同表的上应用了DISTINCT,或者应用了不同的ORDER BY和GROUP BY列。能够强制指定一个长期表应用基于磁盘的MyISAM存储引擎。这样做的起因次要有两个：
1)外部长期表占用的空间超过min(tmp_table_size，max_heap_table_size)零碎变量的限度
2)应用了TEXT/BLOB 列
mysql> explain select id from t1 where id in (1,2) group by age,name;+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+| 1 | SIMPLE | t1 | range | PRIMARY | PRIMARY | 4 | NULL | 2 | Using where; Using temporary; Using filesort |+----+-------------+-------+-------+---------------+---------+---------+------+------+----------------------------------------------+1 row in set (0.00 sec)【示例四】Using filesort
MySQL中无奈利用索引实现的排序操作称为“文件排序”
mysql> explain select id,age from t1 order by name; +----+-------------+-------+------+---------------+------+---------+------+------+----------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | Using filesort |+----+-------------+-------+------+---------------+------+---------+------+------+----------------+1 row in set (0.00 sec)mysql> explain select id,age from t1 order by age; +----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | t1 | index | NULL | age | 5 | NULL | 4 | Using index |+----+-------------+-------+-------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)【示例五】Using join buffer
该值强调了在获取连贯条件时没有应用索引，并且须要连贯缓冲区来存储两头后果。
如果呈现了这个值，那应该留神，依据查问的具体情况可能须要增加索引来改良能。
mysql> explain select t1.name from t1 inner join t2 on t1.name=t2.name;+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+| 1 | SIMPLE | t1 | index | name | name | 63 | NULL | 4 | Using index || 1 | SIMPLE | t2 | ref | name | name | 63 | test.t1.name | 2 | Using where; Using index |+----+-------------+-------+-------+---------------+------+---------+--------------+------+--------------------------+2 rows in set (0.00 sec)删除t1索引
mysql> alter table t1 drop key name; Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0删除t2索引
mysql> alter table t2 drop key name; Query OK, 0 rows affected (0.02 sec)Records: 0 Duplicates: 0 Warnings: 0常常查找
mysql> explain select t1.name from t1 inner join t2 on t1.name=t2.name;+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 4 | || 1 | SIMPLE | t2 | ALL | NULL | NULL | NULL | NULL | 4 | Using where; Using join buffer |+----+-------------+-------+------+---------------+------+---------+------+------+--------------------------------+2 rows in set (0.00 sec)【示例六】Impossible where
这个值强调了where语句会导致没有符合条件的行。
mysql> EXPLAIN SELECT * FROM t1 WHERE 1=2;+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+1 row in set (0.00 sec)【示例七】Select tables optimized away
这个值意味着仅通过应用索引，优化器可能仅从聚合函数后果中返回一行.
mysql> explain select max(id) from t1;+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Select tables optimized away |+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+1 row in set (0.00 sec)【示例八】Index merges
当MySQL 决定要在一个给定的表上应用超过一个索引的时候，就会呈现以下格局中的一个，具体阐明应用的索引以及合并的类型。
Using sort_union(...)Using union(...)Using intersect(...)1.5.14 小结
EXPLAIN不会通知你对于触发器、存储过程的信息或用户自定义函数对查问的影响状况。
EXPLAIN不思考各种Cache。
EXPLAIN不能显示MySQL在执行查问时所作的优化工作。
局部统计信息是估算的，并非准确值。
EXPALIN只能解释SELECT操作，其余操作要重写为SELECT后查看执行打算。
1.6 mysql不走索引的起因
1.6.1 一些常见的起因
1) 没有查问条件，或者查问条件没有建设索引
2) 在查问条件上没有应用疏导列
3) 查问的数量是大表的大部分，应该是30％以上。
4) 索引自身生效
5) 查问条件应用函数在索引列上，或者对索引列进行运算，运算包含(+，-，*，/，! 等)
谬误的例子：select from test where id-1=9; 正确的例子：select from test where id=10;
6) 对小表查问
7) 提醒不应用索引
8) 统计数据不实在
9) CBO计算走索引破费过大的状况。其实也蕴含了下面的状况，这里指的是表占有的block要比索引小。
10)隐式转换导致索引生效.这一点该当引起器重.也是开发中常常会犯的谬误.
因为表的字段tel_num定义为varchar2(20),但在查问时把该字段作为number类型以where条件传给数据库,这样会导致索引生效.
谬误的例子：select * from test where tel_nume=13333333333;
正确的例子：select * from test where tel_nume='13333333333';
11) 留神应用的特殊符号
1,<> ,!=
2,独自的>,<,(有时会用到，有时不会)
12)like "%_" 百分号在前.
select * from t1 where name like 'linux培训%';
13) not in ,not exist.
14) in 尽量改成 union 。
15)当变量采纳的是times变量，而表的字段采纳的是date变量时.或相同状况。
16)B-tree索引is null不会走,is not null会走,位图索引 is null,is not null 都会走 。
17)联结索引 is not null 只有在建设的索引列（不分先后）都会走,
in null时 必须要和建设索引第一列一起应用,当建设索引第一地位条件是is null 时,其余建设索引的列能够是is null（但必须在所有列 都满足is null的时候）,或者=一个值；
当建设索引的第一地位是=一个值时,其余索引列能够是任何状况（包含is null =一个值）,以上两种状况索引都会走。其余状况不会走。
1.6.2 须要留神的一些
1) MyISAM 存储引擎索引键长度总和不能超过1000 字节；2) BLOB 和TEXT 类型的列只能创立前缀索引；3) MySQL 目前不反对函数索引；4) 应用不等于（!= 或者<>）的时候MySQL 无奈应用索引；5) 过滤字段应用了函数运算后（如abs(column)），MySQL 无奈应用索引；6) Join 语句中Join 条件字段类型不统一的时候MySQL 无奈应用索引；7) 应用LIKE 操作的时候如果条件以通配符开始（ '%abc...'）MySQL 无奈应用索引；8) 应用非等值查问的时候MySQL 无奈应用Hash 索引；9) 在咱们应用索引的时候，须要留神下面的这些限度，尤其是要留神无奈应用索引的状况，因为这很容易让咱们因为忽略而造成极大的性能隐患。1.7 数据库索引的设计准则
为了使索引的应用效率更高，在创立索引时，必须思考在哪些字段上创立索引和创立什么类型的索引。
1.7.1 那么索引设计准则又是怎么的
1．抉择唯一性索引
唯一性索引的值是惟一的，能够更疾速的通过该索引来确定某条记录。
例如，学生表中学号是具备唯一性的字段。为该字段建设唯一性索引能够很快的确定某个学生的信息。如果应用姓名的话，可能存在同名景象，从而升高查问速度。
2．为常常须要排序、分组和联合操作的字段建设索引
常常须要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会节约很多工夫。
如果为其建设索引，能够无效地防止排序操作。
3．为常作为查问条件的字段建设索引
如果某个字段常常用来做查问条件，那么该字段的查问速度会影响整个表的查问速度。因而，
为这样的字段建设索引，能够进步整个表的查问速度。
4．限度索引的数目
索引的数目不是越多越好。每个索引都须要占用磁盘空间，索引越多，须要的磁盘空间就越大。批改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。
5．尽量应用数据量少的索引
如果索引的值很长，那么查问的速度会受到影响。例如，对一个CHAR（100）类型的字段进行全文检索须要的工夫必定要比对CHAR（10）类型的字段须要的工夫要多。
6．尽量应用前缀来索引
如果索引字段的值很长，最好应用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的后面的若干个字符，这样能够进步检索速度。
7．删除不再应用或者很少应用的索引
表中的数据被大量更新，或者数据的应用形式被扭转后，原有的一些索引可能不再须要。数据库管理员该当定期找出这些索引，将它们删除，从而缩小索引对更新操作的影响。
8.小表不应建设索引
蕴含大量的列并且不须要搜寻非空值的时候能够思考不建索引
1.8 参考文献
https://baike.baidu.com/item/数据库索引/8751686?fr=aladdinhttps://www.cnblogs.com/oldhorse/archive/2009/11/16/1604009.htmlhttp://blog.csdn.net/manesking/archive/2007/02/09/1505979.aspxhttp://blog.csdn.net/woshiqjs/article/details/24135495欢送关注公众号 【码农开花】一起学习成长
我会始终分享Java干货，也会分享收费的学习材料课程和面试宝典
回复：【计算机】【设计模式】【面试】有惊喜哦