MYSQL索引

索引的优点：

1. 增加查询速度，包括分组和排序

索引的缺点：

 1. 创建和维护索引需要时间，随着数量的增加而增加;
 2. 占用物理空间，与数量成正比
 3. 增加增删改的效率，因为需要动态维护

建立索引时机：

1. 主键自动建立唯一索引;
2. 数据唯一时使用唯一索引；
3. 频繁查询的字段建立索引
4. 查询中排序分组的的字段建立索引
5. 查询中与其它表关联的字段，外键关系建立索引

不需要建立索引：

1. 频繁修改的字段;
2. 查询条件用不到的字段；
3. 经常增删改的字段；
4. 数据重复且分布平均的字段，因此为经常查询的和经常排序的字段建立索引。注意某些数据包	
	含大量重复数据，因此他建立索引就没有太大的效果，例如性别字段，只有男女，不适合建
	立索引

索引种类：

 1. 普通索引:
		CREATE INDEX index_name ON table(column(length))
		ALTER TABLE table_name ADD INDEX index_name ON (column(length))(修改表结构)
		DROP INDEX index_name ON table
 2. 唯一索引：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
		CREATE UNIQUE INDEX indexName ON table(column(length)
		ALTER TABLE table_name ADD UNIQUE indexName ON (column(length))(修改表结构)
		DROP INDEX index_name ON table
 3. 主键索引：是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值
 4. 组合索引：指多个字段上创建的索引，最左前缀集合
		CREATE INDEX name_city_age ON table( name,city,age)
		ALTER TABLE `table` ADD INDEX name_city_age (name,city,age);(修改表结构)
 5. 全文索引：**5.7以后官方支持中文分词**。
		主要用来查找文本中的关键字，而不是直接与索引中的值相比较。fulltext索引跟其它索引大
		不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。fulltext索引配合
		match against操作使用，而不是一般的where语句加like。它可以在create table，alter table 
		，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。值得一提的
		是，在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用CREATE index创
		建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多。
		 全文索引不支持中文需要借sphinx(coreseek)或迅搜<、code>技术处理中文。
		CREATE FULLTEXT INDEX index_content ON article(content)
		ALTER TABLE article ADD FULLTEXT index_content(content)(修改表结构)
		
		SELECT * FROM article WHERE MATCH(title,content) AGAINST (‘查询字符串’);

查看所有索引：

	show indexes from `表名`;
	show keys from `表名`;

explain

字段解释：

1. id 号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。id值越大优先级越高执行越
   早

2. select_type:
   1、SIMPL:简单的 select 查询,查询中不包含子查询或者UNION
   2、PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary
   3、DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生)
   MySQL会递归执行这些子查询, 把结果放在临时表里。
   4、SUBQUERY:在SELECT或WHERE列表中包含了子查询
   5、DEPENDENT SUBQUERY:在SELECT或WHERE列表中包含了子查询,子查询基于外层
   6、UNCATCHABLE SUBQUERY:无法被缓存的子查询
   7、UNION:若第二个SELECT出现在UNION之后，则被标记为UNION；
   若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
   8、UNION RESULT:从UNION表获取结果的SELECT

3. type:type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
   system > const > eq_ref > ref > fulltext > ref_or_null > index_merge >
   unique_subquery > index_subquery > range > index > ALL
   system>const>eq_ref>ref>range>index>ALL
   一般来说，得保证查询至少达到range级别，最好能达到ref。
   1、system ：表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也
   可以忽略不计
   2、const ：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹
   配一行数 据，所以很快如将主键置于where列表中，MySQL就能将该查询转换为
   一个常量
   3、eq_ref ：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯
   一索引扫描
   4、ref:非唯一性索引扫描，返回匹配某个单独值的所有行.本质上也是一种索引访问，它返回所
   有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找
   和扫描的混合体
   5、rang:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引.一般就是
   在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比全
   表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索
   引。
   6、index:Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为
   索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从
   索引中读取的，而all是从硬盘中读的）
   7、all：
   8、index_merge ：在查询过程中需要多个索引组合使用，通常出现在有 or 的关键字的sql中
   9、ref_or_null:对于某个字段既需要关联条件，也需要null值得情况下。查询优化器会选择用
   ref_or_null连接查询。
   10、unique_subquery :该联接类型类似于index_subquery。 子查询中的唯一索引
   11、index_subquery :利用索引来关联子查询，不再全表扫描。

4. key:实际使用的索引。如果为NULL，则没有使用索引,查询中若使用了覆盖索引，则该索引和查询
   的select字段重叠

5. key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。

6. ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上
   的值

7. rows:检查的行数，越小越好

8. extra:包含不方便在其他列显示，但很重要的额外信息
   1、using filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读
   取。MySQL中无法利用索引完成的排序操作称为“文件排序”.查询中排序的字段，
   排序字段若通过索引去访问将大大提高排序速度
   2、using temporary:使用临时表保存了中间结果。常见与order by group by
   3、using index:表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据
   行，效率不错！
   如果同时出现using where，表明索引被用来执行索引键值的查找;
   如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。
   覆盖索引：一个数据库也可以通过索引查找一列的数据，这时不需要读取整行，
   一个索引包含了(或覆盖了)[select子句]与查询条件[Where子句]中所有需要的字段就叫做覆
   盖索引。
   4、using join buffer:使用了连接缓存
   5、impossible where :where 字句的值总是false，不能用来获取任何元组

查询优化：

1. 使用索引

1、索引失效：
	1、全职匹配
	2、最佳左前缀
	3、不在索引列上做任何操作
	4、存储引擎不能使用索引中范围条件右边的列
	5、mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
	6、is not null 也无法使用索引,**但是is null是可以使用索引的**
	7、like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
	8、字符串不加单引号索引失效

关联查询优化

	1、保证被驱动表的join字段已经被索引
	2、left join 时，选择小表作为驱动表，大表作为被驱动表。
	3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
	4、子查询尽量不要放在被驱动表，有可能使用不到索引。

子查询优化

		尽量不要使用not in  或者 not exists。**left outer join  on  xxx is null 替代**

order by 关键字优化

	尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
	ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序
	如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序
		单路排序优化：
			增大sort_buffer_size参数的设置
			大max_length_for_sort_data参数的设置
		why:
			 1、 
				 1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会
						 用改进后的算法——单路排序， 否则用老算法——多路排序。
			 	 1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次
			 			 I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
			2. 尝试提高 sort_buffer_size
				不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每
				个进程的
			3. 尝试提高 max_length_for_sort_data
				提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就			
				增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 

GROUP BY关键字优化

		group by实质是先排序后进行分组，遵照索引建的最佳左前缀
		当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
		where高于having，能写在where限定的条件就不要去having限定了。

重点 分页查询的优化—limit 所以没有使用 ★

	  EXPLAIN    SELECT  SQL_NO_CACHE * FROM emp  ORDER  BY  deptid  LIMIT 10000,40
	  那我们就给deptno这个字段加上索引吧。然并卵。
	  因为优化器认为 ？？？？
	  优化：  先利用覆盖索引把要取的数据行的主键取到，然后再用这个主键列与数据表做关联：
		EXPLAIN  SELECT  SQL_NO_CACHE * FROM emp
		INNER JOIN (SELECT id FROM emp e ORDER BY deptno LIMIT 10000,40) a ON a.id=emp.id

最后使用索引的手段：覆盖索引

；