前 言
MOP 不用多说了，我这里指的就是 MySQL、Oracle、PostgreSQL 三种目前最主流的数据库，MOP 系列打算更新 MOP 三种数据库的索引知识、高可用架构及常用 SQL 语句等等，今天打算介绍一下这三种数据库的索引基础知识，但由于文章过长，前面已经分享了 Oracle 篇，今天分享 MySQL 篇。
MySQL 索引类型
索引原理
MySQL默认存储引擎 innodb 只显式支持 B-Tree( 从技术上来说是B+Tree)索引，对于频繁访问的表，innodb 会透明建立自适应 hash 索引，即在B树索引基础上建立hash索引，可以显著提高查找效率，对于客户端是透明的，不可控制的，隐式的。B+树索引是B+树在数据库中的一种实现，是最常见也是数据库中使用最为频繁的一种索引。B+树中的B代表平衡（balance），而不是二叉（binary），因为 B+ 树是从最早的平衡二叉树演化而来的。二叉树具有以下性质：左子树的键值小于根的键值，右子树的键值大于根的键值。
MySQL 默认 innodb 存储引擎就是使用B+树来实现索引结构的。由于内节点(非叶子节点)不存储 data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。也就是说使用B+树单次磁盘IO的信息量相比较B树更大，IO 效率更高。
非唯一索引
索引值可以出现多次（默认索引类型）
唯一索引
索引值必须唯一或为NULL
ALTER TABLE tab_name ADD UNIQUE (col_name);
CREATE UNIQUE INDEX idx_tname_2 ON tablename(col_name);
主键
表的主键表示在最重要的查询中使用的列或列集。它有一个关联索引，用于快速查询性能。值必须唯一，并且不能包含 NULL。
ALTER TABLE tab_name ADD PRIMARY KEY (col1);
全文索引
只有 InnoDB 和 MyISAM 存储引擎支持 FULLTEXT 索引，并且只支持 CHAR、VARCHA R和 TEXT 列。索引总是在整个列上进行，不支持列前缀索引。
CREATE TABLE opening_lines (FTS_DOC_ID BIGINT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,opening_line TEXT(500),author VARCHAR(200), title VARCHAR(200) ) ENGINE=InnoDB;
CREATE FULLTEXT INDEX idx ON opening_lines(opening_line);
空间索引
MySQL允许在NOT NULL的几何值列上创建空间索引。优化器检查索引列的SRID属性，以确定要使用哪个空间参考系统(SRS)进行比较，并使用适合于SRS的计算
创建索引的 SQL 语句
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326, SPATIAL INDEX(g));
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
ALTER TABLE geom ADD SPATIAL INDEX(g);
CREATE TABLE geom (g GEOMETRY NOT NULL SRID 4326);
CREATE SPATIAL INDEX g ON geom (g);
函数索引
对表中的列执行表达式或函数计算后的结果构成索引
ALTER TABLE tab_name ADD INDEX ((func(col)));
CREATE INDEX idx_t_f ON tab_name ((func(col_name)));
降序索引
MySQL支持降序索引:索引定义中的DESC不再被忽略，而是导致键值按降序存储。以前，索引可以按相反的顺序扫描，但会降低性能。降序索引可以按正向顺序扫描，这样效率更高。降序索引还使优化器可以在最有效的扫描顺序混合了某些列的升序和其他列的降序时使用多列索引。
•考虑下面的表定义，它包含两个列和四个两列索引定义，用于列上升序和降序索引的各种组合: CREATE TABLE t ( c1 INT, c2 INT, INDEX idx1 (c1 ASC, c2 ASC), INDEX idx2 (c1 ASC, c2 DESC), INDEX idx3 (c1 DESC, c2 ASC), INDEX idx4 (c1 DESC, c2 DESC) );
•ORDER BY c1 ASC, c2 ASC – optimizer can use idx1 ORDER BY c1 DESC, c2 DESC – optimizer can use idx4 ORDER BY c1 ASC, c2 DESC – optimizer can use idx2 ORDER BY c1 DESC, c2 ASC – optimizer can use idx3
复合索引
MySQL可以创建复合索引(即多个列上的索引)。一个索引最多可以包含16列。注意多列索引的最左边前缀匹配原则。
CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) );
SELECT * FROM tbl_name WHERE col1=val1; SELECT * FROM tbl_name WHERE col1=val1 AND col2=val2; SELECT * FROM tbl_name WHERE col2=val2; SELECT * FROM tbl_name WHERE col2=val2 AND col3=val3; 如果索引存在于 (col1, col2, col3)， 只有前两个查询使用索引。第三和第四 查询确实涉及索引列，但不使用索引 执行查找，因为 (col2) 和 (col2, col3) 不是最左边的前缀 (col1, col2, col3)。
不可见索引
MySQL 支持不可见索引;也就是说，优化器不使用的索引。该特性适用于主键以外的索引(显式或隐式)。
CREATE TABLE t1 (i INT,j INT,k INT,INDEX i_idx (i) INVISIBLE ) ENGINE = InnoDB;
CREATE INDEX j_idx ON t1 (j) INVISIBLE;
ALTER TABLE t1 ADD INDEX k_idx (k) INVISIBLE;
查看索引
show create table tab_nameG
show index from tab_nameG
SELECT INDEX_NAME,IS_VISIBLE FROM INFORMATION_SCHEMA.STATISTICS WHERE TABLE_SCHEMA = 'JiekeXu' AND TABLE_NAME = 't1';
参考链接
https://docs.oracle.com/en/database/oracle/oracle-database/23/cncpt/indexes-and-index-organized-tables.html#GUID-ACA0308E-5F01-4236-81D3-D0CDE5CB6695 https://docs.oracle.com/en/database/oracle/oracle-database/23/admin/managing-indexes.html#GUID-E637BC13-A2CA-454D-B680-07B95F7C4CE4
https://dev.mysql.com/doc/refman/8.0/en/optimization-indexes.html
https://www.postgresql.org/files/documentation/pdf/16/postgresql-16-A4.pdf
全文完，希望可以帮到正在阅读的你，如果觉得有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~
❤️ 欢迎关注我的公众号【JiekeXu DBA之路】，一起学习新知识！
————————————————————————————
公众号：JiekeXu DBA之路
墨天轮：https://www.modb.pro/u/4347
CSDN ：https://blog.csdn.net/JiekeXu
ITPUB：https://blog.itpub.net/69968215
腾讯云：https://cloud.tencent.com/developer/user/5645107
————————————————————————————
