索引-MYSQL

索引是一种能提高数据库查询效率的数据结构。他可以比作一本字典的目录，可以帮你快速找到对应的记录。

MYSQL索引的类型：

数据结构维度：

1. B+树索引

2.哈希索引

3.全文索引

4.R-Tree索引

物理存储维度

1.聚集索引：聚集索引就是以主键创建的索引，在叶子节点存储的是表中的数据，（Innodb存储引擎）

2.非聚集索引：非聚集索引就是以非主键创建的索引，在叶子节点存储的是主键和索引列（Innodb存储引擎）

逻辑维度

1.主键索引

2.普通索引

3.联合索引

4.唯一索引

5.空间索引

索引为什么失效？？？？？？？

1.查询条件包含or

2.如果字段类型是字符串，where时一定要用引号括起来，否则失效

3.like通配符可能导致索引失效（LIKE 'John%'可以避免索引失效， 如果是 以%开头的，索引都会失效）

4.联合索引，查询时条件列不是联合索引中的第一个列，索引失效

5.在索引列上使用MySQL内置函数，索引失效、

6.对索引列运算，如： + -* /

7.索引字段上使用  ！= 或者<>，not in  时，导致索引失效

8.左连接查询或者右连接查询查询关联的字段编码格式不一样

9.MySQL估计使用全表扫描要比索引快，则不使用索引

哪些场景不适合建立索引？

1.数据量少的表，不适合加索引

2.更新比较频繁的也不适合加索引

3.区分度低的字段不适合加索引（如性别）

4.where group by order by 等后面没有使用到的字段，不需要建立索引

5.已经有冗余的索引的情况（比如已经有a，b的联合索引，不需要单独建立 a 索引）

索引的最左前缀原则

索引的最左前缀原则，可以是联合索引的最左N个字段，比如你建立一个组合索引

（A,B,C），其实可以相当于建了（a)(a,b)(a,b,c)三个索引，大大提高了索引复用能力。

什么是回表，如何减少回表？

当查询的数据在索引树中，找不到的时候，需要回到主键索引树中获取，这个过程叫做回表。

大表如何添加索引

如果数据量级是千万级以上，给表添加索引的时候，是会锁表的，如果不谨慎操作，可能出现生产事故。

1.先创建一张跟表A数据结构相同的新表

2.在新表B添加需要加上的索引

3.在原表A数据导入到新表B

4.两个表的名字做更改

如何知道语句是否走索引

explain查看SQL的执行计划，这样就知道是否命中索引了。

当explain与SQL一起使用时，MYSQL讲显示来自优化器的有关语句执行计划的信息

一般来说，我们需要重点关注 type,rows,filtered,extra,key.

rows

该列表示MySQL估算要找我们所需的记录，需要读取的行数，对于innodb表，此数字是估计值，并非一定是个准确值

filtered

该列是一个百分比的值，表里符合条件的记录数的百分比。简单来说，这个字段表示存储引擎返回的数据在经过过滤后，剩下满足条件的记录数量的比例

type 
 type 表示连接类型，查看索引执行情况的一个重要指标。以下性能从好到坏依次： system > const > eq _ ref > ref > ref _ or _ null > index _ merge > unique _ subquery > index _ subquery > range > index > ALL system ：这种类型要求数据库表中只有一条数据，是 const 类型的一个特例，一般情况下是不会出现的。
 const ：通过一次索引就能找到数据，一般用于主键或唯一索引作为条件，这类扫描效率极高，，速度非常快。
 eq _ ref ：常用于主键或唯一索引扫描，一般指使用主键的关联查询
 ref ：常用于非主键和唯一索引扫描。
 ref _ or _ null ：这种连接类型类似于 ref ，区别在于 MySQL 会额外搜索包含 NULL 值的行 index _ merge ：使用了索引合并优化方法，查询使用了两个以上的索引。
 unique _ subquery ：类似于 eq _ ref ，条件用了 in 子查询
 indexsubquery ：区别于 unique _ subquery ，用于非唯一索引，可以返回重复值。
 range ：常用于范围查询，比如： between ...
 and 或 In 等操作
 index ：全索引扫描
 ALL ：全表扫描

extra 
该字段包含有关 MySQL 如何解析查询的其他信息，它一般会出现这几个值：
 Using filesort ：表示按文件排序，一般是在指定的排序和索引排序不一致的情况才会出现。一般见于 order by 语句
 Using index ：表示是否用了覆盖索引。 Using temporary :表示是否使用了临时表，性能特别差，需要重点优化。一般多见于 group by 语句，或者 union 语句。
 Using where ：表示使用了 where 条件过滤． Using index condition :MySQL5.6之后新增的索引下推。在存储引擎层进行数据过滤，而不是在服务层过滤，利用索引现有的数据减少回表的数据。

key 
该列表示实际用到的索引。一般配合 possible _ keys 列一起看。

Hash索引和 B ＋树区别是什么？你在设计索引是怎么抉择的？
● B ＋树可以进行范围查询， Hash 索引不能。● B ＋树支持联合索引的最左侧原则， Hash 
索引不支持。
● B ＋树支持 order by 排序， Hash 索引不支持。
● Hash 索引在等值查询上比 B ＋树效率更
高。（但是索引列的重复值很多的话， Hash 冲突，效率降低）。
● B ＋树使用 like 进行模糊查询的时候， like 
后面（比如％开头）的话可以起到优化的作用， Hash 索引根本无法进行模糊查询。
14．索引有哪些优缺点？
优点：
●索引可以加快数据查询速度，减少查询时间
●唯一索引可以保证数据库表中每一行的数据的唯一性
缺点：
●创建索引和维护索引要耗费时间
●索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
●以表中的数据进行增、删、改的时候，索
引也要动态的维护。

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

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。它表示索引结构和数据一起存放的索引。非聚集索引是索引结构和数据分开存放的索引。
接下来，我们分不同存存储引擎去聊哈～在 MySQL 的 InnoDB 存储引擎中，聚簇索引与非聚簇索引最大的区别，在于叶节点是否存放一整行记录。聚簇索引叶子节点存储了一整行记录，而非聚簇索引叶子节点存储的是主键信息，因此，一般非聚簇索引还需要回表查询。
一个表中只能拥有一个聚集索引（因为一般聚簇索引就是主键索引），而非聚集索引一个表则可以存在多个。
一般来说，相对于非聚簇索引，聚簇索引查询效率更高，因为不用回表。
而在 MyISM 存储引擎中，它的主键索引，普通索引都是非聚簇索引，因为数据和索引是分开的，叶子节点都使用一个地址指向真正的表数据。