MySQL索引原理
1.索引类型
索引可以提升查询速度，会影响where查询，以及order by排序。
从索引存储结构划分：B Tree索引、Hash索引、FULLTEXT全⽂索引、R Tree索引
从应⽤层次划分：普通索引、唯⼀索引、主键索引、复合索引
从索引键值（字段）类型划分：主键索引、辅助索引（⼆级索引）
从数据存储和索引键值逻辑关系划分：聚集索引（聚簇索引）、⾮聚集索引（⾮聚簇索引）
1.1普通索引
这是最基本的索引类型，基于普通字段建⽴的索引，没有任何限制。
创建索引的⽅法如下：
CREATE INDEX <；索引的名字> ON tablename(字段名); （单独建⽴索引）
ALTER TABLE tablename ADD INDEX [索引的名字] (字段名)； （修改表结构时加索引）
CREATE TABLE tablename（[…],INDEX [索引的名字] (字段名)); （创建表时加索引）
删除索引的⽅法
drop index <；索引的名字> on tablename
查询表中的索引信息
show index from tablename；
1.2唯⼀索引
与普通索引类似，不同的就是：索引字段的值必须唯⼀，但允许有空值。在创建或修改表时追加唯⼀约束，就会⾃动创建对应的唯⼀索引。创建唯⼀索引的⽅法如下：mysql面试题索引
CREATE UNIQUE INDEX <；索引的名字> ON tablename(字段名);
ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
CREATE TABLE tablename（[…],UNIQUE [索引的名字] （字段名））
1.3主键索引
它是⼀种特殊的唯⼀索引，不允许有空值。在创建或修改表时追加主键约束即可，每个表只能有⼀个主键。excel软件教程下载
创建主键索引的⽅法如下
CREATE TABLE tablename ([…],PRIMARY KEY(字段名))
ALTER TABLE tablename ADD PRIMARY KEY(字段名)
1.4复合索引
单⼀索引是指索引列为⼀列的情况，即新建索引的语句只实施在⼀列上；⽤户可以在多个列上建⽴索引，这种索引叫做复合索引（组合索引）。复合索引在数据库操作期间所需的开销更⼩，可以代替多个单⼀索引。
索引同时有两个概念叫做窄索引和宽索引，窄索引是指索引列为1-2列的索引，宽索引也就是索引列超过2列的索引，设计索引的⼀个重要原则就是能⽤窄索引不⽤宽索引，因为窄索引往往⽐组合索引更有效
创建组合索引的⽅法如下：
CREATE INDEX <；索引的名字> ON tablename（字段名1，字段名2…）；
ALTER TABLE tablename ADD INDEX [索引的名字] （字段名1，字段名2…）frontpage软件
CREATE TABLE tablename（[…],INDEX [索引的名字] (字段名1，字段名2…)）
复合索引的使⽤注意事项：
复合索引字段是有顺序的，在查询使⽤时要按照索引字段的顺序使⽤。例如select * from user where name=xx and age=xx ，匹配（name，age）组合索引，不匹配（age,name）。
何时使⽤复合索引，要根据where条件建索引，注意不要过多使⽤索引，过多使⽤会对更新操作效率有很⼤影响。
如果表已经建⽴了（col1，col2），就没有必要再单独建⽴（col1）；如果现在有（col1）索引，如果查询需要col1和col2条件，可以建⽴（col1，col2）复合索引，对于查询有⼀定的提⾼。
联合索引IDX(字段A，字段B，字段C，字段D)，当仅使⽤字段A查询时，索引IDX就会使⽤到；如果仅使⽤字段B或字段C或字段D查询，则索引IDX都不会⽤到。
冒泡排序怎么调用
1.5全⽂索引
查询操作在数据量⽐较少时，可以使⽤like模糊查询，但是对于⼤量的⽂本数据检索，效率低下。如果使⽤全⽂索引，查询速度会⽐like快很多倍。在MySQL5.6以前的版本，只有MyISAM存储引擎⽀持全⽂索引，从MySQL5.6之后开始MyISAM和InnoDB存储引擎均⽀持。
创建全⽂索引的⽅法如下：
CREATE FULLTEXT INDEX<；索引的名字> ON tablename(字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename([...], FULLTEXT KEY [索引的名字] (字段名));
和常⽤的like模糊查询不同，全⽂索引有⾃⼰的语法格式，使⽤match和against 关键字，⽐如
select * from user where mathc（name）against（‘aaa’）；
全⽂索引必须在字符串、⽂本字段上建⽴。
全⽂索引字段值必须在最⼩字符和最⼤字符之间才会有效。（innodb：3-84，myisam：4-84）
全⽂索引字段值要进⾏切词处理，按syntax字符进⾏切割，例如b+aaa，切分成b和aaa
全⽂索引匹配查询，默认使⽤的时等值匹配，例如a匹配a，不会匹配ab，ac。如果想匹配可以在布尔模式下搜索a*
select * from user where mathc（name）against（‘a*’ in boolean mode）；
全⽂索引的最⼩最⼤查询字符数
2.索引原理
MySQL官⽅对索引的定义：是存储引擎⽤于快速查记录的⼀种数据结构。需要额外开辟空间和数据维护⼯作。
索引是物理数据页存储，在数据⽂件中（InnoDB，ibd‘⽂件），利⽤数据页（page）存储。
索引可以加快检索速度，但是同时也会降低增删改操作的速度，索引维护需要代价。
索引涉及的理论知识：⼆分查法、Hash和B+Tree。
2.1⼆分查法
精通ios开发⼆分查法也叫作折半查法，它是在有序数组中查指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据，新增数据、删除数据维护成本⾼。
⾸先定位left和right两个指针
计算（left+right）/2
判断除2后索引位置值与⽬标值的⼤⼩⽐对
索引位置值⼤于⽬标值就-1，right移动；如果⼩于⽬标值就+1，left移动。
2.2Hash结构
Hash底层实现是由Hash表来实现的，是根据键值<key,value>存储数据的结构。⾮常适合根据key查value值，也就是单个key查询，或者说等值查询。其结构如下所⽰：
从上⾯结构可以看出，Hash索引可以⽅便的提供等值查询，但是对于范围查询就需要全表扫描了。Hash索引在MySQL中Hash结构主要应⽤在Memory原⽣的Hash索引、InnoDB⾃适应哈希索引。
InnoDB提供的⾃适应哈希索引功能强⼤，接下来重点描述下InnoDB⾃适应哈希索引。
InnoDB⾃适应哈希索引是为了提升查询效率，InnoDB存储引擎会监控表上各个索引页的查询，当InnoDB注意到某些索引值访问⾮常频繁时，会在内存中基于B+Tree索引再创建⼀个哈希索引，使得内存中的B+Tree索引具备哈希索引的功能，即能快速定值访问频繁访问的索引页。
⾃适应哈希索引的建⽴使得InnoDB存储引擎能⾃动根据索引页访问的频率和模式⾃动地为某些热点页建⽴哈希索引来加速访问。另外InnoDB⾃适应哈希索引的功能，⽤户只能选择开启或关闭功能，⽆法进⾏⼈⼯⼲涉。
2.3B+Tree结构
MySQL数据库索引采⽤的是B+Tree结构，在B-Tree结构上做了优化改造。
什么叫异步传输方式B-Tree结构
索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值以及对应的data数据
树节点中的多个索引值从左到右升序排列
B树的搜索：从根节点开始，对节点内的索引值序列采⽤⼆分法查，如果命中就结束查。没有命中会进⼊⼦节点重复查过程，直到所对应的节点指针为空或者已经是叶⼦节点了才结束。
B+Tree结构
⾮叶⼦节点不存储data数据，只存储索引值，这样便于存储更多的索引值。
叶⼦节点包含了所有的索引值和data数据。
叶⼦节点⽤指针链接，提⾼区间的访问性能
每个⾮叶⼦节点链接的下层都作为⾸个索引值存在
相⽐B树，B+树进⾏范围查时，只需要查定位两个节点的索引值，然后利⽤叶⼦节点的指针进⾏遍历即可。⽽B树需要遍历范围内所有的节点和数据，显然B+Tree效率⾼。
2.4聚簇索引和辅助索引
聚簇索引和⾮聚簇索引：B+Tree的叶⼦节点存放主键索引值和⾏记录就属于聚簇索引；如果索引值和⾏记录分开存放就属于⾮聚簇索引。主键索引和辅助索引 ：B+Tree的叶⼦节点存放的是主键字段值就属于主键索引；如果存放的是⾮主键的值就属于辅助索引（⼆级索引）。
在InnoDB引擎中，主键索引采⽤的就是聚簇索引结构存储。
聚簇索引
聚簇索引是⼀种数据存储⽅式，InnoDB的聚簇索引就是按照主键顺序构建B+Tree结构。B+Tree的叶⼦节点就是⾏记录，⾏记录和主键紧凑的存储在⼀起。这也意味着InnoDB的主键索引就是数据库表本⾝，它按主键顺序存放了整张表的数据，占⽤的空间就是整个表数据量的⼤⼩。通常说的主键索引就是聚簇索引。（个⼈理解：主键的值放在索引值⾥⾯，对应的那⼀⾏数据存放到data⾥⾯（data见上图））
InnoDB的表要求必须要有聚簇索引
1.如果表定义了主键，则主键就是聚簇索引
2.如果表没有定义主键，则第⼀个⾮空unique列作为聚簇索引
3.否则InnoDB会创建⼀个隐藏的row-id作为聚簇索引
辅助索引
InnoDB辅助索引，也叫作⼆级索引，是根据索引列构建B+Tree结构。但在B+Tree的叶⼦节点中只存放了索引列和主键信息。⼆级索引占⽤的空间会⽐聚簇索引⼩很多，通常创建辅助索引就是为了提升查询效率。⼀个表InnoDB只能创建⼀个聚簇索引，但可以创建多个辅助索引
⾮聚簇索引

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。