mysql索引优化

mysql索引优化

1.索引介绍

索引是一种用来实现 MySQL 高效获取数据的数据结构。

我们通常所说的在某个字段上建索引，意思就是让 MySQL 对该字段以索引这种数据结构来存储，然后查找的时候就有对应的查找算法。

建索引的根本目的是为了查找的优化，特别是当数据很庞大的时候，采用的是二分法查找，折半查找通过二叉查找树或红黑树实现二分搜索。因此在数据之外，数据库系统还维护着满足特定查找算法的数据结构。一个表不超过两个索引。这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

2.索引的类型

(1) 普通索引
普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的访问速度。
普通索引允许在定义索引的列中插入重复值和空值。

创建普通索引时，通常使用的关键字是 INDEX 或 KEY。
例 1
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引。
CREATE INDEX index_id ON tb_student(id);
alter table user add index index_name(name);

(2) 唯一索引
唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避免数据出现重复。
唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯一。

创建唯一索引通常使用 UNIQUE 关键字。
例 2
下面在 tb_student 表中的 id 字段上建立名为 index_id 的索引，SQL 语句如下：
CREATE UNIQUE INDEX index_id ON tb_student(id);

(3) 主键索引
顾名思义，主键索引就是专门为主键字段创建的索引，也属于索引的一种。
主键索引是一种特殊的唯一索引，不允许值重复或者值为空。
创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

(4)联合索引
由两个及两个以上字段组合成的索引叫联合索引，联合索引最左原则
普通联合索引和唯一联合索引的区别
语法不同，普通联合索引 以create index ……开头，
		而 唯一联合索引 以create unique index ……开头
(5) 全文索引
全文索引主要用来查找文本中的关键字，只能在 CHAR、VARCHAR 或 TEXT 类型的列上创建。在 MySQL 中只有 MyISAM 存储引擎支持全文索引。
全文索引允许在索引列中插入重复值和空值。
不过对于大容量的数据表，生成全文索引非常消耗时间和硬盘空间。

创建全文索引使用 FULLTEXT 关键字。
例 4
在 tb_student 表中的 info 字段上建立名为 index_info 的全文索引，SQL 语句如下：
CREATE FULLTEXT INDEX index_info ON tb_student(info);

其中，index_info 的存储引擎必须是 MyISAM，info 字段必须是 CHAR、VARCHAR 和 TEXT 等类型。

查询索引

show index from 表名;

删除索引

drop index index_name on table_name; 

3.explain

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra

id:选择标识符
select_type:表示查询的类型。
table:输出结果集的表
partitions:匹配的分区
type:表示表的连接类型
possible_keys:表示查询时，可能使用的索引
key:表示实际使用的索引
key_len:索引字段的长度
ref:列与索引的比较
rows:扫描出的行数(估算的行数)
filtered:按表条件过滤的行百分比
Extra:执行情况的描述和说明

type:对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行

index: Full Index Scan，index与ALL区别为index类型只遍历索引树

range:只检索给定范围的行，使用一个索引来选择行

ref: 表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

const、system: 当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

NULL: MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

4.索引的底层实现

一棵B+Tree树，浅蓝色的块我们称之为一个磁盘快。可以看到每个磁盘快包含几个数据项（深蓝色所示）和指针（黄色所示）
如磁盘块1包含数据项17和35，包含p1、p2、p3
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子结点即3、5、9、10、13、15、28、29、60、75、79、90、99.
非叶子结点不存储真实的数据，只存储只因搜索方向的数据项，如17、35并不真实存在于数据表中。
3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常的高。

5.Btree和B+Tree的区别

1、B-树的关键词和记录放在一起，叶节点可以看作是外部节点，不包含任何信息；B+树的非叶节点只有关键词和指向下一个节点的索引，记录只放在叶节点上。

2、在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字就可以确定记录的存在；而B+树中每一个记录的查找时间基本相同，都需要从根节点到叶节点，并且在叶节点中再比较一下关键字。在这一点上，B-树的性能似乎比B+树好，而在实际应用中，B+树的性能则更好。由于B+树的非叶节点不存放实际数据，因此每一节点所能容纳的元素数量比B-树多，树高比B-树小，其优点是减少了磁盘的访问次数。虽然B+树找到记录所需的比较次数比B-树多，但一次磁盘访问的时间相当于数百次内存比较的时间，所以实际上B+树的性能可能会更好，而B+树的叶节点也可以用指针连接在一起，方便顺序遍历(例如查看一个目录下的所有文件，一次磁盘访问的所有数据库等)，这也是B+树的所有记录系统使用的数据库和文件。

3、B+树的磁盘读写代价更低
B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B-树更小。
B+树的查询效率更加稳定。

6. 什么情况下索引失效

1.有or必全有索引;
2.复合索引未用左列字段;
3.like以%开头;
4.需要类型转换;
5.where中索引列有运算;
6.where中索引列使用了函数;
7.如果mysql觉得全表扫描更快时（数据少）;

7. 什么情况下没必要用索引

1.唯一性差;
2.频繁更新的字段不用（更新索引消耗）;
3.where中不用的字段;
4.索引使用<>时，效果一般;