数据库索引及优化

数据库索引
一、索引的概念
索引是一种排好序的快速查找的数据结构。
索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引、次要索引、覆盖索引、复合索引、前缀索引、唯一索引 默认都是使用B+树索引，统称为 索引。当然除了 B+树 这种类型的索引之外，还有哈希索引（hash index）等。

二、索引的优缺点
优点：
1）提高数据检索的效率，降低数据库的 IO 成本
2）通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
缺点：
1）索引其实也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用空间的
2）索引虽然提高了查询速度，但是却降低了更新表的速度。因为更新表时，Mysql不仅要保存数据，还要保存索引文件。每次更新添加了索引列的字段。都会调整因为更新所带来的的键值变化后的索引信息。

三、explain
1.概念
用法：
explain + sql 查看执行计划包含的信息

	例如：
	 explain select * from admin；

作用：

	1）查看表的读取顺序
	2）数据读取操作的操作类型
	3）哪些索引可以使用
	4）哪些索引被实际使用
	5）表之间的引用
	6）每张表有多少行被优化器查询

2.各字段解释
1）id
id相同，执行顺序由上至下
id不同，id值越大越早执行

2）select_type
simple -----------------------普通查询，查询中不包含子查询或者union
primary-----------------------查询中包含任何复杂的子部分，最外层查询被标记为primary
subquery---------------------查询中包含任何复杂的子部分，内层查询被标记为subquery
derived-----------------------在from列表中包含的子查询被标记为derived（衍生），mysql会递归执行这些子查询，把结果放在临时表中
union--------------------------若第二个select出现在union之后，则被标记为union；若union包含在from子句的子查询中，外层select将被标记为derived
union result------------------从union表获取结果的select

3）type
从最好到最差的排序为：
system --> const --> eq_ref --> ref --> range --> index --> ALL
一般来说，得保证查询至少达到 range 级别，最好能达到 ref

	1.system ：
	表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计

	2.const：
	表示通过索引一次就找到了，const用于比较 primary key 或者 unique 索引。因为只匹配一行数据，所以很快。

	3.eq_ref
	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
	
	4.ref
	非唯一性索引扫描，返回匹配某个单独值的所有行。
	
	5.range
	只检索给定范围的行，使用一个索引来选择行。一般在 between 、< 、> 、in 等的查询
	
	6.index
	full index scan，index与ALL的区别为  index类型只遍历 索引树。通常比ALL快，因为索引文件通常比数据文件小。

	7.ALL
	全表扫描

4）possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若涉及索引，则该索引将被列出，但不一定被查询实际使用

5. key
   实际使用的索引。如果为null，则没有使用索引

四、索引失效的原因
1）在索引列上做操作（计算、函数、类型转换），会导致索引失效而转向全表扫描
2）使用 != 或者 <> 的时候会失效
3）is null /is not null 会失效
4）like 以通配符 ‘%abc…’ 会失效（若使用右模糊查询（‘abd…%’），则可以使用索引）
注：使用覆盖索引可以使用 %abc…%
5）字符串不加单引号会失效
6）用 or 连接 会失效
7）复合索引，左侧的不能断

注：
索引两大功能：检索（select） 和 排序（order by）
group by 基本上都需要进行排序，会有临时表产生

索引检索（where 之后，order by之前的条件）优化口诀：

索引排序（order by 之后）优化：
1）order by时 ，不要使用 **select ***
2）尝试提高 sort_buffer_size 的大小
每次读取到 sort_buffer 的内存数据容量大小
3）合理设置 max_length_for_sort_data 的大小

日志分析工具 mysqldumpslow ：