MySQL索引

1.MySQL索引

1.什么是mysql索引？

MySQL官方对索引的定义为:索引(Index）是帮助MySQL高效获取数据的数据结构.可以得到索引的本质:索引是数据结构。
你可以简单理解为"排好序的快速查找数据结构”。

2.索引有几种特性类型？

一个索引包含一个列，一个表可以有多个单值索引

2.2 唯一索引

索引的值必须唯一，但是允许有控制

2.3 复合索引

一个索引包含多个列

😊没用的举例

CREATE INDEX indexName ON table_name (column_name)
ALTER table tableName ADD INDEX indexName(columnName)

3.常见的索引的底层是什么？

常见的索引是B+树。

B+实际上是B树的改进，B树是平衡二叉树（AVL树）的改进
B树相对于平衡二叉树每个节点可以存储更多的数据。

更符合数据存储的要求

B+树：B+树实际上是B树的改进，与B树不同，B+数仅仅在叶子节点存储数据，由于磁盘块大小是一定的，单个磁盘块可以存储更多的指针，降低树的高度，叶子节点连续数据的分段读取也更优雅。

注意：B+树的叶子节点（页）之间是双向链表

总结：1. 数据需要大量查找，且区分度较高的情况下需要建立索引

2.经常需要删除的数据不建议建立索引

😊举个例子： 数据库维护用户表的注销操作的时候为了不影响数据的查找效率，维护索引的完整，经常不真正删除数据，而是采用标记的方式，实现逻辑上的删除

聚簇索引和非聚簇索引的区别

• MyISAM是非聚簇索引，B+树的叶子节点储存的不是数据，而是数据地址；

• 主索引和辅助索引没有区别，主索引的key一定要唯一；

• 主索引的B+树的叶子节点存储了主键，辅助索引的B+树的叶子节点存储了辅助键

• 表数据存储在单独的位子，两种索引的树叶子节点节点都使用一个地址指向真正的数据

• InnoDB是聚簇索引，行数据就存在叶子节点

• 主键查询，直接找到叶子节点，返回数据

• 非主键查询，先找到主键，然后再根据主键二次查询数据

• 聚簇索引数据的物理存放顺序和索引顺序是一致的，即，索引是相临的，数据也是相临的，聚簇索引查询效率要比非聚簇索引高效。

• 聚簇索引 主索引+辅助索引的好处是，数据行变动，主索引会更新，但是辅助索引不需要更新

• 非聚簇索引，类似于书的目录，

• 每个表只能有一个聚簇索引，因为一个表的物理顺序是唯一的。

2.MySQL性能分析

1.explain关键字:用于分析sql的运行计划

2.1.explain关键字：

1. id：id越大越优先被执行，同样大从上到下执行。总体趋势是被依赖的先执行

2. select_type:查询的类型

SIMPLE :简单的select查询,查询中不包含子查询或者UNION。
PRIMARY:查询中若包含任何复杂的子部分，最外层查询则被标记为Primary.
DERIVED:在FROM列表中包含的子查询被标记为DERIVED(衍生),MysQL会递归执行这些子查询，把结果放在临时表里。
SUBQUERY:在SELECT或wHERE列表中包含了子查询。

3. table:查询所涉及的表

4. type：访问类型排序

system:表只有一行记录(等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计。

Const:表示通过索引一次就找到了, const.用于比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为
一个常量。

eg_ref:唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

Range:只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索5
一般就是在你的where语句中出现了between、<、>、in等的查询这种范围扫描索引扫描比
全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。

Index: Full Index Scan, index与ALL区别为index类型只遍历索引树。这通常比
ALL快，因为索引文件通常比数据文件小。也就是说虽然all和Index都是读全表，但index
是从索引中读取的，而all是从硬盘中读的。

All: Full Table Scan，将遍历全表以找到匹配的行。

从最好到最差依次是:system>const>eg_ref>ref>range>index>All 一般来说最
好保证查询能达到range级别，最好能达到ref。

5.passible_key:mysql猜测可能使用的索引

里面的索引未必一定会被使用

6.key：实际使用的表中的索引

7.row：文件排序

rows列显示MysQL认为它执行查询时必须检查的行数。一般越少越好。

8.key_len:显示的值为索引字段的最大可能长度，并非实际使用长度

一般越大越好

8:extra

Using filesort: MysQL无法利用索引完成的排序操作称为“文件排序”。
Using temporary: Mysql在对查询结果排序时使用临时表，常见于排序order by和分组查询group by。
Using index:表示索引被用来执行索引键值的查找，避免访问了表的数据行，效率不错。
Using where:表示使用了where 过滤。

2.2. 索引失效举例：

创建复合索引

alter table students add index idx_sname_age_score(sname,age,score

1. 最佳左前缀法则:如果索引了多列，要遵循最左前缀法则，指的是查询从索引的最左
   前列开始并且不跳过索引中的列。

   explain select * from students where sname="小明" and age = 22 and
   score = 100;
   explain select * from students where sname="小明" and age = 22;
   explain select * from students where sname="小明";
   explain select * from students where sname="小明" and score = 80;
   

2. 不在索引列上做任何计算、函数操作，会导致索引失效而转向全表扫描。

   explain select * from students where left(sname,2)= "小明";
   

3. 存储引擎不能使用索引中范围条件右边的列。

    explain select * from students where sname="小明" and age > 22 and
   score = 100;
   

4. MySQL在使用不等于时无法使用索引会导致全表扫描。

   explain select * from students where sname ! ="小明";explain select * from students where sname is null;I
   

5. like 以通配符开头会使索引失效导致全表扫描。

   explain select * from students where sname like "%"明;

6. 字符串不加单引号索引会失效。

   explain select* from students where sname = 123;
   

select * from students where sname like "%"明;

6. 字符串不加单引号索引会失效。

   explain select* from students where sname = 123;