搞定MySQL索引

内容导读

互联网集市收集整理的这篇技术教程文章主要介绍了搞定MySQL索引，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6444字，纯文字阅读大概需要10分钟。

内容图文

1. 索引（index）

1.1 索引

官方定义: 一种帮助MySQL提高查询效率的数据结构；

这里就得到，索引其实就是一种数据结构，选用的为B+树

1.2 优缺点

优点：加快数据查询速度；

缺点：
1、维护索引需要耗费数据库资源；
2、索引需要占用磁盘空间；
3、当对表的数据进行增删改的时候，因为要维护索引，速度会受到影响；

1.3 索引分类

因为现行的MySQL数据库下，InnoDB存储类型最为常用，所以还是主要以InnoDB来说：

1. 主键索引：设定为主键后数据库会自动建立索引，InnoDB为聚簇索引；主键索引索引列的值不能为空

2. 单值索引：即一个索引只包含单个列，一个表可以有多个单值索引，也可称为单列索引，普通索引 ；

3. 唯一索引：索引列的值必须唯一，但允许有空值，但只能存在一个null；

4. 复合索引：即一个索引包含多个列；基本上是把基于多个列来进行查询的生成符合索引；

5. Full Text 全文索引： (MySQL 5.7版本之前，只能用于MYISAM引擎)

   全文索引类型为FULL TEXT，有点类似ES，在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR、TEXT类型列上创建。MYSQL只有MYISAM存储引擎支持全文索引

2. 索引操作

2.1 主键索引 自动创建

建表时主键自动创建主键索引

create table t_user(id varchar(20) primary key,name varchar(20));

查看索引

show index from t_user;

Table	对应表
Key_name	索引的名字
Seq_in_index	索引编号
Column_name		对应列
Index_type	索引类型

2.2 单值索引(普通索引|单列索引)

随表一起建立的索引，索引名同列名一致

create table t_user(id varchar(20) primary key,name varchar(20),key(name));  

建表后创建

-- create index 索引并 on 表名(字段);
create index name_index on t_user(name);

删除索引

-- drop index 索引名 on 表名
drop index name on t_user;

2.3 唯一索引（unique）

建表时创建，索引名同列名一致**

 create table t_user(id varchar(20) primary key,name varchar(20),unique(name));

建表后创建

create unique index nameindex on t_user(name);

2.4 复合索引

建表时创建

create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));

建表后创建

create index name_age_index on t_user(name,age);

注意

1. 最左前缀原则：必须符合我左前缀（即左包含）的顺序才能够利用符合索引；举个例子

   建立一个复合索引 （A ， B ， C），然后下列可以使用索引查询的：
   1）A			2）B			3）C
   4）A	，B 		5）A ， C		6）B ， C
   7）A ， B ， C		8)A ,C, B	9) B , A, C		
   10)B , C, A		11) C , A , B	12)C, B, A等等排列
   # 能够使用的仅有 1，4，7， 其他的都不可以；
   

2. MySQL引擎在查询时为了更好的利用索引，在查询过程中会动态调整查询字段的顺序，以便更好地利用索引

   这句话的意思就是，MySQL会把顺序帮你自动矫正过来，比如bca就会成abc进而可以使用索引
   再假设一个索引（name，age，bir）
   1）name,age,bir		可以利用索引
   2）name,bir,age		可以利用索引
   3）age,bir    		不可利用索引 
   4）bir,age,name		可以利用索引
   5）bir,age     		不可利用索引
   

3. 索引底层原理

-- 建立一个新表
drop table if exists t_emp;
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',23);
insert into t_emp values(2,'b',26);
insert into t_emp values(3,'c',27);
insert into t_emp values(4,'a',32);
insert into t_emp values(8,'f',53);
insert into t_emp values(9,'v',13);

--查询
show index from t_emp;
select * from t_emp;

3.1 上面数据明明没有按顺序插入,查询时却是有顺序

1. 原因是：MySQL底层为主键自动创建索引,一定创建索引会进行排序；也就是MySQL底层真正存储是这样的；

2. 为什么要排序呢?

   因为排序之后在查询就相对比较快了，如查询 id=3的我只需要按照顺序找到3就行啦

   (如果没有排序大海捞针,全靠运气!)

3.2 为了进一步提高效率MySQL索引又进行了优化

当上面的那条链足够大足够长的时候，就会导致查询的时间过长，时间复杂度过高，效率极低，针对此，MySQL基于页的形式进行管理索引，如查询id=4的直接先比较页，先去页目录中找，再去数据目录中找；

3.3 B+树数据结构

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构，这里不多讲解，只提一句，如果按照我们上面的建表结构，三层B+树大概能够存储近10亿条数据；

4. 聚簇索引和非聚簇索引

聚簇索引： 将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据

非聚簇索引：将数据与索引分开存储，索引结构的叶子节点指向了数据对应的位置

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

聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一且非空的索引代替。如果没有这样的索引，InnoDB 会**隐式定义一个主键（类似Oracle中的RowId）**来作为聚簇索引。如果已经设置了主键为聚簇索引又希望再单独设置聚簇索引，必须先删除主键，然后添加我们想要的聚簇索引，最后恢复设置主键即可

常见问题

使用聚簇索引的优势

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

1. 由于行数据和聚簇索引的叶子节点存储在一起，同一页中会有多条行数据，访问同一数据页不同行记录时，已经把页加载到了Buffer中（缓存器），再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Id来组织数据，获得数据更快
2. 辅助索引的叶子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化；或者是我们需要查找的数据，在上一次IO读写的缓存中没有，需要发生一次新的IO操作时，可以避免对辅助索引的维护工作，只需要维护聚簇索引树就好了。另一个好处是，因为辅助索引存放的是主键值，减少了辅助索引占用的存储空间大小

聚簇索引需要注意什么

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

为什么主键通常建议使用自增id

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

什么情况下无法利用索引

1. 查询语句中使用LIKE关键字：在查询语句中使用 LIKE 关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引不会被使用。如果“%”不是在第一个位置，索引就会被使用；
2. 查询语句中使用多列索引：多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才会被使用；
3. 查询语句中使用OR关键字：查询语句只有OR关键字时，如果OR前后的两个条件的列都是索引，那么查询中将使用索引。如果OR前后有一个条件的列不是索引，那么查询中将不使用索引；

内容总结

以上是互联网集市为您收集整理的搞定MySQL索引全部内容，希望文章能够帮你解决搞定MySQL索引所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。

内容备注

版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。

内容手机端