mysql 子查询设置临时索引 mysql临时表建索引

一、索引创建

1. 普通索引
   这是最基本的索引，它没有任何限制，比如上文中为title字段创建的索引就是一个普通索引，MyIASM中默认的BTREE类型的索引，也是我们大多数情况下用到的索引。
   1）创建方式：

1. 直接创建索引

CREATE INDEX index_name ON table(column(length))

1. 修改表结构的方式添加索引

ALTER TABLE table_name ADD INDEX index_name ON (column(length))

1. 创建表的时候同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	INDEX index_name (title(length))
)

2）删除索引

DROP INDEX index_name ON table

1. 主键索引
   1）创建方式

1. 修改表结构

alter table table_name add primary key (column name);

1. 创建表时同时创建索引

create table a(
	id int primary key auto_increment,
	name varchar(20) not null default ''
);

1. 唯一索引
   与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须是唯一的，创建方法和普通索引类似。
   1）创建方式：

1. 创建唯一索引

CREATE UNIQUE INDEX index_name ON table(column(length))

1. 修改表结构

ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (column(length))

1. 创建表时同时创建索引

CREATE TABLE `table` (
	`id` int(11) NOT NULL AUTO_INCREMENT ,
	`title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
	`content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
	`time` int(10) NULL DEFAULT NULL ,
	PRIMARY KEY (`id`),
	UNIQUE indexName (title(length))
);

1. 组合索引
   平时用的SQL查询语句一般都有比较多的限制条件，所以为了进一步榨取MySQL的效率，就要考虑建立组合索引。例如上表中针对title和time建立一个组合索引：

ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))

建立这样的组合索引，其实是相当于分别建立了下面两组组合索引：title,time、title
为什么没有time这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这两列的查询都会用到该组合索引。

1. 全文索引
   首先，全文索引主要针对文本文件，比如文章，标题，全文索引只有MyISAM有效(mysql5.6之后InnoDB也支持了全文索引)。
   1）创建方式：

1. 修改表结构

alter table table_name add fulltext (`column` )

1. 创建表时同时创建索引

create table c(
	id int primary key auto_increment ,
	title varchar(20),
	content text,
	fulltext(title,content)
)engine=myisam charset utf8;

1. 使用全文索引常见的错误：

select * from c where content like "%mysql%";

1. 这里并不会使用全文索引，可以用explain进行查看。正确用法：

select * from c where match(title,content) against ('MYSQL');

1. 备注:

1. 在mysql中fulltext 索引只针对 myisam生效
2. mysql自己提供的fulltext针对英文生效->sphinx(coreseek)技术处理中文
3. 使用方法是 match(字段名…) against(‘关键字’)

二、查询索引

show indexes from table_name;
show keys from table_name;

三、删除索引

alter table table_name drop index 索引名;

四、MySQL Explain详解
在优化中，常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。

explain select * from emp where name = 'Jefabc';

字段对应的解释：

1. id:选择标识符
   select 识别符。这是select查询序列号。
   (1）id 相同时，执行顺序由上到下；
   (2）如果时子查询，id的序号会递增，id值越大优先级越高，越先被执行；
   (3）id如果相同，可以认为是一组，从上到下顺序执行；在所有组中，id值越大，优先级越高，越先执行；
2. select_type:表示查询的类型。
   示查询中每个select子句的类型
   (1) SIMPLE(简单SELECT，不使用UNION或子查询等)
   (2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
   (3) UNION(UNION中的第二个或后面的SELECT语句)
   (4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
   (5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
   (6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
   (7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
   (8) DERIVED(派生表的SELECT, FROM子句的子查询)
   (9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)
3. table:输出结果集的表
   显示这一步所访问数据库中表名称（显示这一行的数据是关于哪张表的），有时不是真实的表名字，可能是简称，例如上面的e，d，也可能是第几步执行的结果的简称
4. partitions:匹配的分区
   指出MySQL能使用哪个索引在表中找到记录，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用（该查询可以利用的索引，如果没有任何索引显示 null）
   该列完全独立于EXPLAIN输出所示的表的次序。这意味着在possible_keys中的某些键实际上不能按生成的表次序使用。
   如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查WHERE子句看是否它引用某些列或适合索引的列来提高你的查询性能。如果是这样，创造一个适当的索引并且再次用EXPLAIN检查查询
5. type:表示表的连接类型
   对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
   1）system：
   该表只有一行数据。这种类型是下面const类型的一种特例。
   2）const：
   该表最多只有一行数据被匹配，在查询开始时就会被读取。因为只有一行，所以这一行中列的值可以被优化器的其他部分视为常量。const表非常快，因为它们只需要读取一次。
   当把一个表的主键或唯一索引和一个常量值作比较时，const就会被使用。下面的语句中，t1就可以被作为一个const表。

SELECT * FROM  t1 WHERE id=1;
SELECT * FROM  t2 WHERE unique_index_part1=1 and unique_index_part1=2;

1. 3）eq_ref：
   在与前一个表的每一行做联合时，只需要从该表中读取一行。除了system和const类型，这个是最好的join类型。当一个join语句中使用了主键或一个非空唯一索引的全部部分时，join类型就为eq_ref。
   eq_ref可用于使用=运算符进行比较的索引列。比较值可以是一个常量，也可以是这个表之前读取的表中的列。

SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;
SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

1. 4）ref：
   在与前表的每个行组合中，从该表中读取具有匹配索引值的所有行。当join仅仅使用一个索引的左前缀，或者使用的索引不是一个主键，又或者不是唯一索引，ref就会被使用。如果使用的索引匹配的行数很少，这也不失为一个很好的join类型。
   eq_ref可用于使用=或<=>运算符进行比较的索引列。

SELECT * FROM ref_table WHERE key_column=expr;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column=other_table.column;SELECT * FROM ref_table,other_table  WHERE ref_table.key_column_part1=other_table.column  AND ref_table.key_column_part2=1;

1. 5）fulltext：
   使用全文索引执行join
   6）ref_or_null：
   这个join类型和ref有点像，但是对于包含null值行，MySQL会做一个额外的查询。这种join类型优化最常用于解析子查询。

SELECT * FROM ref_table  WHERE key_column=expr OR key_column IS NULL;

1. 7）index_merge：
   这个join类型表示使用了索引合并优化。这个时候，key列显示了一系列使用到的索引，key_len显示了使用到的索引的最长键长。
   8）unique_subquery：
   在以下形式的某些in子查询中，这个join类型会替代eq_ref。

value IN (SELECT primary_key FROM single_table WHERE some_expr)

1. unique_subquery只是一个索引查找函数，它完全替代了子查询以提高效率。
   9）index_subquery：
   这个join类型和unique_subquery有些类似。它在子查询中替换，但在以下形式的子查询中适用于非唯一索引:

value IN (SELECT key_column FROM single_table WHERE some_expr)

1. 10）range：
   当使用一个索引去选择行时只检索了给定范围的行。key列显示了使用了哪个索引，key_len显示了使用到的索引的最长键长。当join类型为range时，ref列值为null。
   range可用于使用 =, <>, >, >=, <, <=, IS NULL, <=>,BETWEEN, LIKE, IN() 运算符和一个常量进行比较的索引列。

SELECT * FROM tbl_name  WHERE key_column = 10;SELECT * FROM tbl_name  WHERE key_column BETWEEN 10 and 20;
SELECT * FROM tbl_name  WHERE key_column IN (10,20,30);SELECT * FROM tbl_name  WHERE key_part1 = 10 AND key_part2 IN (10,20,30)

1. 11）index：
   这个join类型和ALL相似，区别是扫描了索引数。
   12）ALL：
   为了和前表的每一行做组合而全表扫描。这种类型通常不太好，应当尽量避免。我们可以通过给表添加索引来避免这种情况。
2. possible_keys:表示查询时，可能使用的索引
   这一列显示了可供MySQL选择用来查询使用的所有索引。需要注意的是其中的一些索引在实际运行时可能不会被使用。
   如果这一列为null，那就表示没有想关的索引。这个时候，可以检查where子句引用的列是否适合建立索引，通过建立合适的索引增强执行性能。索引建立完成后，可以再次执行EXPLAIN查看效果。
   想要知道表已经建立了哪些索引，可以通过SHOW INDEX FROM table_name查看。
3. key:表示实际使用的索引
   key列显示MySQL实际决定使用的键（索引），必然包含在possible_keys中
   如果没有选择索引，键是NULL。要想强制MySQL使用或忽视possible_keys列中的索引，在查询中使用FORCE INDEX、USE INDEX或者IGNORE INDEX。
4. key_len:索引字段的长度
   表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度（key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的）
   不损失精确性的情况下，长度越短越好
5. ref:列与索引的比较
   列与索引的比较，表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值
6. rows:扫描出的行数(估算的行数)
   估算出结果集行数，表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数
7. filtered:按表条件过滤的行百分比
   本列表示将被表条件过滤的表行的估计百分比。最大值为100，这意味着没有对行进行筛选。
8. Extra:执行情况的描述和说明
   该列包含MySQL解决查询的详细信息,有以下几种情况：
   Using where:不用读取表中所有信息，仅通过索引就可以获取所需数据，这发生在对表的全部的请求列都是同一个索引的部分的时候，表示mysql服务器将在存储引擎检索行后再进行过滤
   Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询，常见 group by ; order by
   Using filesort：当Query中包含 order by 操作，而且无法利用索引完成的排序操作称为“文件排序”
   Using join buffer：改值强调了在获取连接条件时没有使用索引，并且需要连接缓冲区来存储中间结果。如果出现了这个值，那应该注意，根据查询的具体情况可能需要添加索引来改进能。
   Impossible where：这个值强调了where语句会导致没有符合条件的行（通过收集统计信息不可能存在结果）。
   Select tables optimized away：这个值意味着仅通过使用索引，优化器可能仅从聚合函数结果中返回一行
   No tables used：Query语句中使用from dual 或不含任何from子句