MYSQL索引

什么是索引

官方回答：一种帮助mysql提高查询效率的数据结构

索引的优点：大大加快了数据查询的速度

索引的缺点：

维护索引需要消耗数据库资源
索引需要占用磁盘空间
当对表的数据进行增删改的时候，因为要维护索引，速度会受到限制。

索引的分类

—InnoDB

主键索引：设置为主键后数据库会自动建立索引，innoDB为聚簇索引。
单值索引(单列索引或普通索引)：即一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值，但是只能有一个空值。
复合索引：即一个索引包含多个列。

—MYISAM
5. Full Text 全文索引（MYSQL 5.7以前，只能用于MYISAM引擎）：全文索引类型为FULLTEXT，在定义素引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文素引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文素引。

索引的基本操作

-----------------------------------1.主键索引，自动创建--------------------------------------
--建表，主键自动创建主键索引
mysql> create table t_user( id varchar(20) primary key,name varchar(20));
--查看索引
show index from t_user

mysql> show index from t_user
    -> ;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_user |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

--------------------------------------2.单值索引-----------------------------------------
--建表时创建：key(列名)
mysql> create table t_user( id varchar(20) primary key,name varchar(20),key(name));
----------注意：建表时创建的索引 索引名和列名一致
--建表后创建
create index nameindex on t_user(name);

mysql> show index from t_user
    -> ;
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_user |          0 | PRIMARY   |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_user |          1 | nameindex |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
--删除索引

DROP index 索引名 on 表名
mysql> drop index nameindex on t_user
    -> ;
Query OK, 0 rows affected (0.51 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t_user
    -> ;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_user |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

mysql>
--------------------------------------3.唯一索引------------------------------------------
--建表时创建：(unique (列名))
 create table t_user( id varchar(20) primary key,name varchar(20),unique(name));
--建表后创建：
create unique index nameunipueindex on t_user(name);

mysql> create unique index nameunipueindex on t_user(name);
Query OK, 0 rows affected (0.61 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show index from t_user
    -> ;
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_user |          0 | PRIMARY         |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_user |          0 | nameunipueindex |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
--------------------------------------4.复合索引-------------------------------------------

--建表时创建
create table t_user( id varchar(20) primary key,name varchar(20),age int,key(name,age));

mysql> show index from t_user;  ---注意索引名
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| t_user |          0 | PRIMARY  |            1 | id          | A         |           0 |     NULL | NULL   |      | BTREE      |         |               |
| t_user |          1 | name     |            1 | name        | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
| t_user |          1 | name     |            2 | age         | A         |           0 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
--建表后创建
create index nameageindex on t_user(name,age);



-----对于复合索引有如下原则

----1.最左前缀原则：若要用的符合索引，则要依据创建索引时的顺序,比如如下语句
--create table t_user( id varchar(20) primary key,name varchar(20),age int,bir varchar(20),key(name,age,bir));
--该语句创建了符合索引(name,age,bir),等于建立了(name),(name,age),(name,age,bir)三个索引。所以只有基于name,  name  age,   name age bir的查询操作才能用到索引。
----2.mysql 引擎在查询中为了更好利用索引，在查询过程中会动态调整查询字段顺序以便利用索引
--所以,上述语句中的基于name bir age，age  bir name，bir age name（会自动调整为name age bir）也可以用到索引。

所以的底层原理

1.思考

--建表
create table t_emp(id int primary key,name varchar(20),age int);

--插入数据
insert into t_emp values(5,'d',22);
insert into t_emp values(6,'d',22);
insert into t_emp values(7,'e',21);
insert into t_emp values(1,'a',22);
insert into t_emp values(2,'b',23);
insert into t_emp values(3,'c',20);
insert into t_emp values(4,'a',22);
insert into t_emp values(8,'f',21);
insert into t_emp values(9,'v',25);
-- 查询
select * from t_emp;

mysql> select * from t_emp;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | a    |   22 |
|  2 | b    |   23 |
|  3 | c    |   20 |
|  4 | a    |   22 |
|  5 | d    |   22 |
|  6 | d    |   22 |
|  7 | e    |   21 |
|  8 | f    |   21 |
|  9 | v    |   25 |
+----+------+------+
9 rows in set (0.00 sec)

如上，插入id无序数据，查询结果发现id自动排序了。这是由于主键索引进行的排序，为了提高基于主键查询的速度。

B树结构：

B +树结构（MySQL）：

B+Tree是在B-Tree（B树）基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。
从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/0次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几点不同：
1.非叶子节点只存储键值信息。
2.所有叶子节点之间都有一个链指针。
3.数据记录都存放在叶子节点中。

InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT(占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为【10^3）。也就是说一个深度为3的B+Tree索引可以维护10^3*10^3*10^3=10亿条记录。
实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。mysql的InnoDB存储引擎在设计时是将根节点常驻内存的(无需磁盘I/O操作)，查询是每层需要一次磁盘I/O操作，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/O操作。

聚簇索引和非聚簇索引

1.聚簇索引：将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据（以 InnoDB 作为存储引擎的表，表中的数据都会有一个主键，即使你不创建主键，系统也会帮你创建一个隐式的主键，这种以主键作为 B+ 树索引的键值而构建的 B+ 树索引，我们称之为聚集索引。 innodb的主键索引就是聚簇索引）

2.非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置（如innoDB中以主键以外的列值作为键值构建的 B+ 树索引，我们称之为非聚集索引）。

在innodb中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引。辅助索引叶子节点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找。

InnoDB中的主键索引是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用”where id = 14”这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
InnoDB中的辅助索引，如以Name列为索引键值建立单值索引，对（where name= “ “）进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）
InnoDB中聚族索引默认是主键，如果表中没有定义主键，InnoDB会选择一个唯一且非空的索引代蓄。如果没有这样的索引，InnoDB会隐式定义一个主键（类似oracle中的Rowld)来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可。

MyISAM使用的是非聚簇索引（包括主键索引和其他索引），非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

使用聚簇索引的优势：

每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗？聚簇索引的优势在哪？

1.由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快。

2.辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小。

注：我们知道一次io读写，可以获取到16K大小的资源，我们称之为读取到的数据区域为Page。而我们的B树，B+树的索引结构，叶子节点上存放好多个关键字（索引值）和对应的数据，都会在一次IO操作中被读取到缓存中，所以在访问同一个页中的不同记录时，会在内存里操作，而不用再次进行IO操作了。除非发生了页的分裂，即要查询的行数据不在上次IO操作的换村里，才会触发新的IO操作。

3.因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

4.不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

聚簇索引需要注意什么？
--1.当使用主键为聚簇索引时，主键最好不要使用uuid，因为uuid的值太过离散，不适合排序且可能出线新增加记录的uuid，会插入在索引树中间的位照，导致索引树调验复杂度变大，消耗更多的时间和资源。
--2.建议使用int类型的自增，方便排序并且认会在索引树的末属增加主键值，对索引树的结构影响最小。而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到IO操作读取到的数据量。

为什么主键通常建议使用自增id？
---聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，硅盘碎片少，效率也高。

哪些情况下不能使用索引？
-----1.查询语句中使用LIKE关键字
--在查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用。
-----2.查询语句中使用多列索引（最左前缀原则）
--多列索引是在表的多个字段上创建一个素引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用。
-----3.查询语句中使用0R关键字
--查询语句只有0R关键字时，如果oR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引。

给个饭钱？

Post author: Sovzn
Post link: http://sovzn.github.io/2021/01/11/MYSQL%E7%B4%A2%E5%BC%95/
Copyright Notice: All articles in this blog are licensed under unless otherwise stated.

什么是索引

索引的分类

索引的基本操作

所以的底层原理

聚簇索引和非聚簇索引