一、索引介绍
索引(index)是帮助MySQL 高效获取数据的数据结构(有序)。在数据之外，数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据，这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
二、索引优缺点
优点：
提高数据检索的效率，降低数据库的io成本通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。
缺点：
索引列也是要占用空间的。索引大大提高了查询效率，同时却也降低更新表的速度，如对表进行INSERT、UPDATE、DELETE时，效率降低。
三、索引结构
通常我们所说的索引，没有特别指明，都是指B+树结构组织的索引
B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引
Hash索引：底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引)：空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)：是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES
1. 经典B+树
看结构和B树比较像，B+树与B树的区别在于：
1.所有的元素都会出现在叶子节点，非叶子节点主要起到索引的作用，而叶子节点是用来存放数据的
2.B+树的数据结构中，叶子节点形成了一个单向链表，每一个节点都会通过指针指向下一个元素
2. MySQL中B+树索引
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree,提高区间访问的性能，叶子节点双向链表+首尾相连，便于范围搜索和排序。
3. Hash索引
哈希索引就是采用一定的hash算法，将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值，映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。
特点：
- Hash索引只能用于对等比较(=，in)， 不支持范围查询(between, >，
存储引擎支持：
在MySQL中，支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
4. 为什么InnoDB选择B+树索引？
与二叉树相比，它的层次更少，搜索效率更高。
[En]
Compared with binary tree, it has fewer levels and higher search efficiency.
对于B-tree,无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；
相对Hash索引，Hash索引只支持等值匹配，B+tree支持范围匹配及排序操作。
四、索引分类
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种：
聚簇索引（Clustering Index）：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据；必须有而且只有一个。
二级索引（Secondary Index）：将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键；可以存在多个。
聚簇索引选取规则：
如果有主键，则主键索引是聚集索引。
[En]
If there is a primary key, the primary key index is a clustered index.
如果不存在主键，将使用第一个唯一(UNIQUE) 索引作为聚簇索引。
如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚簇索引。
如果是(非主键)条件查询，则使用回表查询，即先通过二级索引找到主键(聚集索引)，获取主键，再通过聚集索引找到该行数据。
[En]
If it is a (non-primary key) conditional query, use * back table query * , that is, first find the primary key (clustered index) through the secondary index, get the primary key, and then find this row of data through the clustered index.
InnoDB主键索引的B+tree高度为多高呢?
假设:
一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间，主键即使为bigint,占用字节数为8。
高度为2:
n8+(n+ 1)6= 161024 , 算出n约为1170
117116= 18736
高度为3：
1171 * 1171 * 16 = 21939856
五、索引语法
创建索引
CREATE [ UNIQUE | FULLTEXT ] INDEX index_ name ON table_ name ( index_col_ name,.. ) ;
查看索引
SHOW INDEX FROM table_ name ;
删除索引
DROP INDEX index_ name ON table_ name ;
六、SQL性能分析
1. SQL执行频率
MySQL客户端连接成功后,通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、 SELECT的访问频次：
show global status like 'Com_______';
2. 慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_ query_time, 单位:秒,默认10秒)的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(/etc/my.cnf) 中配置如下信息:
#开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为2秒，SQL 语句执行时间超过2秒，就会视为慢查询，记录慢查询日志
long query time=2
配置完毕之后，通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log
当某一操作时间多于2s则会被记录在慢查询日志中。
3. profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_ profiling参数, 能够看到当前MySQL是否支持profile操作:
#查看当前数据库是否支持profile操作
select @@have_profiling
默认profiling是关闭的，可以通过set语句在session/ global级别开启profiling:
#开启profilingset profiling = 1;
#查看每一条SQL 的耗时基本情况show profiles;#查看指定query_ id的SQL语句各个阶段的耗时情况show profile for query query_ id;#查看指定query_ id的SQL语句CPU的使用情况show profile cpu for query query_id;
4. explain执行计划
EXPLAIN或者DESC命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。语法:
#直接在select语句之前加，上关键字explain / desc
EXPLAIN SELECT 字段列表FROM 表名WHERE 条件;
EXPLAIN执行计划各字段含义:
Id：
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下; id不同,值越大,越先执行)。
select_ type：
表示SELECT的类型,常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询)、UNION (UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
type：
表示连接类型，性能由好到差的连接类型为NULL、system、 const、 eq_ref、ref、range、index、all 。
possible_ key：
显示可能应用于此表的一个或多个索引。
[En]
Displays one or more indexes that may be applied to this table.
Key：
实际使用的索引，如果为NULL，则没有使用索引。
Key_ len：
表示索引中使用的字节数，这是索引字段的最大可能长度，而不是实际长度，实际长度尽可能短而不会损失精度。
[En]
Represents the number of bytes used in the index, which is the maximum possible length of the index field, not the actual length, which is as short as possible without losing accuracy.
rows：
MySQL认为必须要执行查询的行数,在innodb引擎的表中，是-一个估计值，可能并不总是准确的。
filtered：
表示返回结果的行数占需读取行数的百分比，filtered 的值越大越好。
七、索引使用
1. 索引效率
当数据量特别大时，在未建立索引之前，执行SQL，查询无索引字段SQL的耗时非常大。
针对字段创建索引后。
再次执行相同的SQL语句，SQL的耗时将大大减小。
2. 联合索引
最左前缀法则
如果索引了多列(联合索引) , 要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，查询必须包含最左边的列（否则全部失败），并且不跳过索引中的列。
如果跳过一列，索引将部分失效(后续的字段索引将失败)。
[En]
If you jump a column, the index will be partially invalidated (the subsequent field index will fail).
范围查询
联合索引中，出现范围查询(>,
Original: https://www.cnblogs.com/yclblogs/p/15983254.html
Author: 阿龙同学
Title: MySQL索引
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/507772/
转载文章受原作者版权保护。转载请注明原作者出处！