索引

大纲

一、什么是索引

索引就是排好序的数据结构

二、索引的优缺点

（1）优点：避免全表扫描，加快查询速度

（2）缺点：维护成本高，增加、删除、修改（索引维护：分裂和合并）

（3）什么样的信息能成为索引：主键、唯一键等能让数据具备一定区分性的字段

三、索引的分类及数据结构

注意：索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持B+树索引，不能够更换。MEMORY/HEAP存储引擎：支持Hash索引和B+树索引。

1、存储结构（实现）（数据结构）

（1）B+树索引

mysql默认B+树一个节点可存储16kb数据

（2）B-树索引

（3）Hash索引

2、应用层次（类型）

（1）单列索引

普通索引:
唯一索引:索引列中的值必须是唯一的，但是允许为空值
主键索引:索引列中的值必须是唯一的，但是不允许为空值

（2）联合索引

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

（3）全文索引

3、存储方式

（1）聚簇索引（主键索引）

既存储索引值,又在叶子中存储行的数据

（2）非聚簇索引（非主键索引）

叶子节点内容是主键的值。

四、为什么用了索引之后，查询就会变快？

索引的元素一开始是存储在磁盘上的，当你找到某个节点元素之后它会被加载到内存上。
磁盘和内存交换的次数=树的高度
每一个索引在InnoDB里面对应一棵B+树

五、索引构建的过程

（1）单个索引B+树
（2）联合索引B+树

六、索引什么时候会失效、为什么 会失效

1、联合索引：最左前缀匹配原则
2、联合索引：范围查询

七、如何建立高效率的索引

1、自增主键的插入数据模式，正符合了我们前面提到的递增插入的场景。每次插入
一条新记录，都是追加操作，都不涉及到挪动其他记录，也不会触发叶子节点的分裂
显然，主键长度越小，普通索引的叶子节点就越小，普通索引占用的空间也就越小。
2、

八、主键索引和非主键索引（聚簇索引和非聚簇索引）

1、什么是主键索引和非主键索引
（1）主键索引的叶子节点存的是整行数据。非主键索引的叶子节点内容是主键的值。
2、基于主键索引和普通索引的查询有什么区别？
（1）主键索引只用扫描主键索引树，普通索引首先在普通索引树上查找，然后根据主键再去主键索引树上查找得到数据。
（2）回到主键索引树搜索的过程，我们称为回表。
（3）基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使
用主键查询。
3、覆盖索引
在查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。
4、主键索引查询只会查一次，而非主键索引需要回表查询多次。是所有情况都是这样的吗？
不是，覆盖索引就只需要查询一次

九、联合索引和最左前缀原则

1、联合索引在B+树中是如何存储的

2、联合索引的查找方式

3、最左前缀匹配原则

（1）mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配。存在范围查询，就不会走联合索引
（2）=和in可以乱序，mysql的查询优化器会帮你优化成索引可以识别的形式
（3）等值查询查询条件中，存在联合索引最左边的索引字段，就会走联合索引。

4、最左匹配原则的成因（为什么会有最左前缀匹配原则）

mysql创建联合索引的条件是首先会对联合索引最左边的字段进行排序。在第一个字段排序的基础上，在对第二个字段进行排序，所以第一个字段绝对是有序的，第二个字段是无序的，所以只用第二个字段当查询条件时是用不到索引的。

十、InnoDB为什么要选择B+树作为数据结构？

（1）B+树能够很好地配合磁盘的读写特性，减少单次查询的磁盘访问次
数。逻辑存储单位块/页，一块/页可以存储多行数据
（2）mysql数据库的最小逻辑存储单位是块/页，一块/页可以存储多行数据
（3）磁盘由一个个盘片组成的，一个盘片上下两面都是可读写的

图中的一圈圈灰色同心圆为一条条磁道，从圆心向外画直线，可以将磁道划分为若干个弧段，每个磁道上一个弧段被称之为一个扇区（图践绿色部分）。扇区是磁盘的最小组成单元，通常是512字节。
show global status LIKE ‘Innodb_page_size’

十一、如何定位并优化慢查询sql

具体场景具体分析只提供大致思路

（1）根据慢日志定位慢查询sql

（2）根据explain等工具分析sql

（3）修改sql尽量让sql走索引:修改sql语句或添加索引

1、什么是慢日志

慢日志就是来记录一些比较慢的sql的日志

2、根据慢日志定位慢查询sql

(1)show variables like ‘%query%’
long_query_time 10 (当查询时间大于等于10s时，该次查询将会被记录到慢查询日志中)
slow_query_log ON（慢查询开启）
slow_query_log_file DESKTOP-G99S6I5-slow.log（慢查询日志文件位置）
（2）show status like ‘%slow_queries%’
Slow_queries 0 （本次会话mysql慢查询条数，客户端重启后会被清零）
（3）
set global long_query_time=0.1
set global slow_query_log=on

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

3、explain帮我们分析查询慢的原因

explain关键字段
（1）type

all表示全表扫描
（2）extra
extra中出现以下两项意味着MYSQL根本不能使用索引，效率会受到重大影响，应尽可能对此进行优化
using filesort：使用一个外部索引进行排序
using temporary：对查询结果排序时使用临时表，常用于排序order by和分组查询group by
（3）key表明我们用的是哪一个键的索引。

4、

DDL数据库定义语言不会进入慢查询
DML数据库操作语言会进入慢查询

5、判断有没有走索引

mysql的查询优化器的目标是尽可能的使用索引，并且使用最严格的索引来消除尽可能多的数据行
最终的目标是select语句查找数据行而不是排除数据行，优化器试图排除数据行的原因在于他排除数据行的速度越快，那么找到与条件匹配的数据行的速度越快，因此查询优化器会根据他的分析和判断的标准决定走哪一个索引
没走主键索引的原因是因为主键索引叶子节点除了存储索引字段，还存储其他字段了，所以计算总数效率会更慢。
explain SELECT count(*) from aoa_user;
explain select * from aoa_user order by dept_id ASC;

强制索引
explain select * from aoa_user force index(FKadtg9xju7q1ijcyxlkl9gwv5t);