Mysql索引详解

索引类型

1.FULLTEXT：全文索引，只有mysql支持。
2.HASH：作唯一索引，不需要像树形索引那样逐层查找，因此具有极高的效率。但是只在“=”和“in”条件下高效，对于范围查询、排序及组合索引效率不高。
3.BTREE：把索引值存储到二叉树中，在查询的时候遍历，Mysql默认和最常用。
4.RTREE：很少用。

索引种类

普通索引：normal 仅加速查询
唯一索引：unique 加速查询 + 列值唯一（可以有null）
主键索引：primary key 加速查询 + 列值唯一（不可以有null）
全文索引：full text 对文本的内容进行分词，进行搜索
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并（最左前缀）
聚集索引和非聚集索引区别
聚集索引：该索引中键值的逻辑顺和表中相应记录行的物理顺序相同，一个表中只能拥有一个聚集索引。
非聚集索引：数据存储在一个地方，索引存储在另一个地方，索引带有指针指向数据的存储位置（数据MyIsam）

索引创建语句

1.添加 PRIMARY KEY（主键索引）
ALTER TABLE table_name ADD PRIMARY KEY ( column )
2.添加 UNIQUE（唯一索引）
ALTER TABLE table_name ADD UNIQUE ( column )
3.添加 INDEX（普通索引）
ALTER TABLE table_name ADD INDEX index_name ( column )
4.添加 FULLTEXT（全文索引）
ALTER TABLE table_name ADD FULLTEXT ( column)
5.添加联合索引 INDEX
ALTER TABLE table_name ADD INDEX index_name ( column1, column2, column3 )

DDL的索引语句：

create table mytable(
`ID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增主键',
`A` varchar(32) NOT NULL COMMENT 'AAA',
 ……
PRIMARY KEY (`ID`),
KEY `IDX_A` (`A`),
KEY `IDX_A_B_C` (`A`,`B`,`C`)
)ENGINE=InnoDB AUTO_INCREMENT=483843633 DEFAULT CHARSET=utf8 ROW_FORMAT=COMPRESSED COMMENT='表名';

删除索引：
ALTER TABLE 表名 DROP [INDEX|KEY] 索引名;

联合索引分析使用

https://www.yuque.com/wangrisheng/lrnwr3/zaogl7
最左前缀法则口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。

如何挑选索引

建立索引或编写查询语句时注意事项

只为用于搜索、排序或分组的列创建索引

出现在WHERE子句中的列、连接子句中的连接列，出现在ORDER BY或GROUP BY子句中的列。
SELECT b, d FROM mytable WHERE a = ‘ase’;

查询列表中的b、d两列就不需要建立索引，只需要为出现在WHERE子句中的a列创建索引。

为列的基数大的列创建索引

列的基数指的是某一列中不重复数据的个数，最好为那些列的基数大的列建立索引。

比如2, 5, 8, 2, 5, 8, 2, 5, 8，有9条记录，列的基数是3。

索引列的类型尽量小

在定义表结构时要显式的指定列的类型，以整数类型为例，有TINYINT、MEDIUMINT、INT、BIGINT，它们占用的存储空间依次递增。
如果要对某个整数列建立索引的话，在表示的整数范围允许的情况下，尽量让索引列使用较小的类型，比如我们能使用INT就不要使用BIGINT，能使用MEDIUMINT就不要使用INT

• 数据类型越小，在查询时进行的比较操作越快
• 数据类型越小，索引占用的存储空间就越少，在一个数据页内就可以存更多记录，从而减少磁盘I/O带来的性能损耗，也就意味着可以把更多的数据页缓存在内存中，从而加快读写效率

只有索引列在比较表达式中单独出现才会使用索引

假设表中有一个整数列col建立索引：

1. WHERE col * 2 < 4;
2. WHERE col < 4/2;

结论：如果索引列在比较表达式中不是以单独列的形式出现，而是以某个表达式或者函数调用形式出现，不使用索引。

索引字符串值的前缀(可以只对字符串值的前缀建立索引)

只对字符串的前几个字符进行索引也就是说在二级索引的记录中只保留字符串前几个字符。
比方说我们在建表语句中只对a列的前10个字符进行索引可以这么写：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    b DATE NOT NULL,
    c CHAR(11) NOT NULL,
    d varchar(100) NOT NULL,
    PRIMARY KEY (id),
    KEY idx_a_b_c (a(10), b, c)
);

a(10)就表示在建立的B+树索引中只保留记录的前10个字符的编码，这种只索引字符串值的前缀的策略被鼓励使用。

索引列前缀对排序的影响

在使用排序时：使用索引列前缀的方式无法支持使用索引排序
SELECT * FROM mytable ORDER BY a LIMIT 10; —-因为二级索引中不包含完成的a信息，无法进行索引排序

主键设置AUTO_INCREMENT属性 保证主键插入顺序

建议：为了尽可能少让聚簇索引发生页面分裂和记录移位的情况，建议主键设置AUTO_INCREMENT属性。，让存储引擎自己为表生成主键 ：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    PRIMARY KEY (id),
);

避免冗余和重复索引

冗余索引：

CREATE TABLE mytable(
    id INT NOT NULL auto_increment,
    a VARCHAR(100) NOT NULL,
    b DATE NOT NULL,
    c CHAR(11) NOT NULL,
    d varchar(100) NOT NULL,
    PRIMARY KEY (id),
      KEY idx_a (a(10))
    KEY idx_a_b_c (a(10), b, c)
);

idx_a (a(10)) 和 idx_a_b_c (a(10), b, c) 索引冗余，因为后者就包含前者的功能。

重复索引：

CREATE TABLE repeat_index_demo (
    c1 INT PRIMARY KEY,
    c2 INT,
    UNIQUE uidx_c1 (c1),
    INDEX idx_c1 (c1)
);

c1既是主键、又给它定义了一个唯一索引 和一个普通索引，主键本身就会生成聚簇索引，所以定义的唯一索引和普通索引是重复的。

参考：
https://blog.csdn.net/houmenghu/article/details/109580196
https://juejin.cn/book/6844733769996304392/section/6844733770046636045

索引失效优化建议

• 对于单键索引，尽量选择针对当前query过滤性更好的索引
• 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
• 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
• 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

索引优化(避免索引失效/索引使用注意项)

1.合理使用聚簇索引和非聚簇索引
2.不在包含null的列上使用索引
3.尽量使用短索引
4.尽量不要使用索引排序，在where使用了索引的话order by不再使用索引。
5.尽量不要使用like：like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6.不要在列上进行计算
避免冗余索引
冗余索引指的是索引的功能相同，一个索引可以命中另一个索引的全部，比如组合索引的(id,name)和唯一索引(id)，这两个索引就是冗余索引。

MySQLS.7 版本后，可以通过查询 sys 库的 schemal_r dundant_indexes 表来查看冗余索引。

聚集索引

定义：数据行的物理顺序与列值的逻辑顺序相同，一个表中只能有一个聚集索引。指索引项的排序方式和表中数据记录排序方式一致的索引。
解释：数据库会根据聚集索引的键的顺序来存储表中的数据，即对表的数据按聚集索引键的顺序进行排序，然后重新存储到磁盘上。因为数据在物理存放时只能有一种排序方式，所以一个表只能有一个聚集索引。
人话：先对索引项（列值）进行逻辑排序，按照此索引键的顺序对数据的进行物理存放。该索引为聚集索引。

比如字典中，用‘拼音’查汉字，就是聚集索引。因为正文中字都是按照拼音排序的。而用‘偏旁部首’查汉字，就是非聚集索引，因为正文中的字并不是按照偏旁部首排序的，我们通过检字表得到正文中的字在索引中的映射，然后通过映射找到所需要的字。

非聚集索引

： 索引顺序与物理存储顺序不同

索引：
索引是关系型数据库中给数据库表中一列或多列的值排序后的存储结构。
SQL的主流索引结构有B+树以及Hash结构，聚集索引以及非聚集索引用的是B+树索引。
SQL Sever索引类型有：唯一索引，主键索引，聚集索引，非聚集索引。
MySQL 索引类型有：唯一索引，主键（聚集）索引，非聚集索引，全文索引。
SQL Sever默认主键为聚集索引，也可以指定为非聚集索引，而MySQL里主键就是聚集索引。
2021/03/31 15:22

聚簇索引

：将数据存储与索引放到一块，找到索引也就找到了数据。由于聚簇索引是将数据跟索引结构放到一块，因此一个表仅有一个聚簇索引。

非聚簇索引(二级索引)

二级索引的内节点的目录项记录的内容由三个部分构成的：

• 索引列的值
• 主键值
• 页号

定义：将数据存储与索引分开的结构，索引结构的叶子节点指向了数据对应行。
innoDB中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引，前缀索引、唯一索引、辅助索引叶子节点存储的不再是行的物理位置，而是主键值。
结合图再仔细点看

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISAM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。
稠密索引
在稠密索引中，每个搜索码都对应一个索引项。也就是说，稠密索引为数据记录文件的每一条记录都设一个键-指针对。如下图所示，索引项包括索引值以及指向该搜索码的第一条数据记录的指针，即我们所说的键-指针对。

稀疏索引

在稀疏索引中，只为搜索码的某些值建立索引项。也就是说，稀疏索引为数据记录文件的每个存储块设一个键-指针对，存储块意味着块内存储单元连续。如下图所示。

优缺点：
- 稠密索引比稀疏索引更快的定位一条记录。
- 稀疏索引所占空间小，并且插入和删除时所需维护的开销也小。
Innodb底层存储数据
B+树索引的两种类型

• 1. 聚集索引： 通过每张表的主键顺序进行存放，其叶子节点存放的是这张表的每行完整数据。也正是我们有时称呼的主键索引（对比一下稠密索引）
• 1. 非聚集索引（辅助索引，二级索引）： 其叶子节点并不包含行记录的全部数据，其叶子结点的数据包含书签和键值（用于创建索引的字段值），书签的作用是找与索引相对应的行数据。也就是对应聚集索引的主键值。你是否有想过对应的描述的索引值

关系

1. 看完稀疏索引和稠密索引还有聚集索引和非聚集索引的概念，我们是否能看出他们有什么关系。
2. 聚簇索引（主键索引）是稠密索引，因为主键索引是所有的值都不为空，每一个搜索码都会有对应的行记录。
3. 非聚集索引是稀疏索引，非聚集索引有唯一索引，普通索引，复合索引。他们的特征就是不会为表得每个值创建搜索码，而是为单个或多个字段创建，且行记录的某些值可以为null。当我们的where条件不止单个条件的时候我们也会首先通过索引查找出来一批数据，然后进行顺序查找筛选，所以是完全复合稀疏索引的条件的。

优势

1. 通过上面的了解，稀疏索引占用空间少，但是在查询的精确率上还是相对于稠密索引还是比较慢的，因为不需要顺序查找，还有回表。

2. 稠密索引那就是相对来说比较快，因为他可以精确定位数据，但是占用的空间比较大。

   2021/03/29 15:01
   Mysql目前主要有已下几种索引类型：FULLTEXT，HASH、BTREE，RTREE

FULLTEXT

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATABLE、ALTERTABLE、CREATE INDEX使用，不过目前只有CHAR、VARCHAR，TEXT列上可以创建全文索引。值得一提的是在数据量较大的时候，先将数据放到入一个没有全局索引的表中，然后再用CREATE INDEX创建FULLTEXT索引，要比先为一张表建立FULLTEXT然后再将数据写入的速度快很多。
全文索引并不是和MYISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%”这类针对文本的模糊查询效率低的问题。在没有全文索引之前，这样一个查询语句是要遍历数据表操作的，可见，在数据量比较大时是极其耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间。
全文索引的使用方法：
创建：ALTER TABLE table ADD INDEX ‘FULLTEXT’ USING FULLTEXT（column）
ALTER TABLE table_name ADD FULLTEXT ( column)

HASH

Hash就是一种（key=>value）形式的键值对，如数学中的函数映射，
2021/03/29 18:27
聚集索引：将表内的数据按照一定的规则进行排列的目录。正因为如此，一个表中的聚焦索引只有一个。对此我们要注意“主键就是聚焦索引”这是极端错误的，是对聚焦索引的一种浪费。（虽然SQLServer默认主键就是聚焦索引）使用聚焦索引的最大好处就是按照查询要求，迅速缩小查询范围，避免进行全表扫描。其次让每个数目都不相同的字段作为聚焦索引也不符合“大数目不同情况下不应建立聚集索引的原则”。
1、聚集索引
（1）首先指出一个误区，主键并不一定是聚集索引，只是在SQL SERVER中，未明确指出的情况下，默认将主键定义为聚集，而Oracle中则默认是非聚集，因为SQL SERVER中的ROWID未开放使用。
（2）聚集索引适合用于需要进行范围查找的列，因为聚集索引的叶子节点存放的是有序的数据行，查询引擎可根据WHERE中给出的范围，直接定位到两端的叶子节点，将这部分节点页的数据根据链表顺序取出即可；
（3）聚集索引尽量建立在值不会发生变更的列上，否则会带来非聚集索引的维护；
（4）尽量在建立非聚集索引之前建立聚集索引，否则会导致表上所有非聚集索引的重建；
（5）聚集索引应该避免建立在数值单调的列上，否则可能会造成IO的竞争，以及B树的不平衡，从而导致数据库系统频繁的维护B树的平衡性。聚集索引的列值最好能够在表中均匀分布。
3、唯一索引
（1）再指出一个误区，聚集索引并不一定是唯一索引，由于SQL SERVER将主键默认定义为聚集索引，事实上，索引是否唯一与是否聚集是不相关的，聚集索引可以是唯一索引，也可以是非唯一索引；
（2）将索引设置为唯一，对于等值查找是很有利的，当查到第一条符合条件的纪录时即可停止查找，返回数据，而非唯一索引则要继续查找，同样，由于需要保证唯一性，每一行数据的插入都会去检查重复性；
2021/03/29 18:20
1.Primary Key（聚焦索引）：InnoDB存储引擎的表会存在主键（唯一非NULL），如果建表的时候没有指定主键，则会使用第一非空的唯一索引作为聚焦索引，否则InnoDB会自动帮你创建一个不可见的、长度为6字节的row_id用来作为聚集索引。
2021/04/01 11:31
MyISAM和InnoDB的区别
定义
InnoDB：MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。
事务
InnoDB：支持
MyISAM：不支持
锁
InnoDB：支持行锁、表锁。行锁是实现在索引上的，如果没有索引，就没法使用行锁，将退化为表锁。
MyISAM：支持表锁。
主键
InnoDB：必须有，没有指定会默认生成一个隐藏列作为主键
MyISAM：可以没有
索引
InnoDB：聚集索引，使用 B+ 树作为索引结构，数据文件和索引绑在一起，必须要有主键。主键索引一次查询；辅助索引两次查询，先查询主键，再查询数据；
MyISAM：非聚集索引，使用 B+ 树作为索引结构，索引和数据文件是分离的。主键索引和辅助索引是独立的。
外键
InnoDB：支持
MyISAM：不支持
AUTO_INCREMENT
InnoDB：必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
数据库文件
InnoDB：frm是表定义文件，ibd是数据文件。支持两种存储方式：

• 共享表空间存储：所有表的数据文件和索引都保存在一个表空间里，一个表空间可以有多个文件，通过 innodb_data_file_path 和 innodb_data_home_dir 参数设置共享表空间的位置和名字，一般共享表空间的名字叫 ibdata1-n。
• 多表空间存储：每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开关，以.ibd为扩展名

MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件。支持三种存储格式：静态表（默认，注意数据末尾不能有空格，会被去掉。）、动态表、压缩表。
表的行数
InnoDB：没有保存。select count() from table;会扫描全表。
MyISAM：保存。select count() from table;会直接取出该值。
注：但加了 where 条件后，两者处理方式一样，都是扫描全表。
全文索引
InnoDB：5.7及以后版本支持。
MyISAM：支持。
总结
InnoDB：

• 优点：支持事务，支持外键，并发量较大，适合大量 update。
• 缺点：查询数据相对较快，不适合大量的 select。

MyISAM：

• 优点：查询数据相对较快，适合大量的 select，可以全文索引。
• 缺点：不支持事务，不支持外键，并发量较小，不适合大量 update。

如何选择？

• 你的数据库有外键吗？如有，选择 InnoDB。
• 你需要事务支持吗？如需要，选择 InnoDB。
• 你需要全文索引吗？在5.7及以后版本，都可选，优先考虑 InnoDB + Sphinx。
• 你经常使用什么样的查询模式？如果表中绝大多数都只是读查询，可以考虑 MyISAM，如果既有读写也挺频繁，请使用 InnoDB。
• 你的数据有多大？数据量大，选择 InnoDB，因为其支持事务处理和故障恢复。MyISAM 可能需要几小时或几天来恢复，InnoDB 只需要几分钟。
• 你需要在线热备份吗？如需要，选择 InnoDB。