mysql中索引的实现逻辑

本文主要是介绍mysql中索引的实现逻辑，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

mysql中的索引给sql语句的执行提高了很大的效率，具体在什么场景下才适合使用索引？什么是索引？

1、在数据量非常庞大的数据   --索引可以大大的缩小检索的范围，从而快速的定位到数据

2、该字段很少执行DMl语句时  --因为在数据库中索引会自动的维护，当有DML语句对索引字段执行的时候，数据库会

3、该字段经常出现在where子句后面  -经常根据哪个字段查询。如给该字段添加索引将会大大提高检索效率

索引：索引就好像是一本练习册，通过练习册上的目录，我们可以准确的找到某一章节存在的页面数

一、索引的类型

1、单一索引：给单个字段添加索引

2、复合索引：给多个字段同时添加一个索引

3、主键索引：主键字段上默认会自动添加索引

4、唯一索引：字段具有unique约束的会自动添加索引

二、怎么创建索引

创建索引create index 索引名称 on 表名(字段名称)
mysql> create index t_emp_index_sal on t_emp(sal);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0
成功给t_emp表中的sal字段添加索引

删除索引drop index 索引名称 on 表名
mysql> drop index t_emp_index_sal on t_emp;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0
成功给t_emp表中的sal删除索引

三、索引和常见的DML语句有什么区别？

索引底层采用的数据结构是：B + Tree 索引的实现原理：通过B Tree缩小扫描范围，底层索引进行了排序，分区，索引会携带出数据在表中的物理地址，最终通过索引检索到数据之后，获取到关联的物理地址，通过物理地址定位表中的数据，效率是最高的 演示：使用 explain 查看sql语句的执行计划  

mysql> explain select * from t_emp where sal=5000;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | t_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    7 |    14.29 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> select * from t_emp;
+-------+----------+------+-------+--------+
| empno | ename    | job  | sal   | dempno |
+-------+----------+------+-------+--------+
|     1 | xds      | a    |  5000 |      2 |
|     2 | xds      | a    |  3000 |      2 |
|     3 | shenzhen | a    |  3000 |      1 |
|     4 | zs       | a    |  5000 |      1 |
|     9 | ps       | b    |  8000 |      4 |
|    10 | ps       | b    |  6000 |      1 |
|    11 | xw       | b    | 10000 |      8 |
+-------+----------+------+-------+--------+
7 rows in set (0.00 sec)

从执行计划的type和rows字段可以看出，type是all是进行的全表扫描，rows次数是7次。我们查看下表内整体的数据，表中总共7条数据，而常规的sql语句，是在表中1v1的去比较，符合where条件的取出

演示：创建索引后使用explain查看sql语句的执行计划

mysql> create index t_emp_index_sal on t_emp(sal);
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> select * from t_emp where sal=5000;
+-------+-------+------+------+--------+
| empno | ename | job  | sal  | dempno |
+-------+-------+------+------+--------+
|     1 | xds   | a    | 5000 |      2 |
|     4 | zs    | a    | 5000 |      1 |
+-------+-------+------+------+--------+
2 rows in set (0.00 sec)

mysql> explain  select * from t_emp where sal=5000;
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys   | key             | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | t_emp | NULL       | ref  | t_emp_index_sal | t_emp_index_sal | 5       | const |    2 |   100.00 | NULL  |
+----+-------------+-------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
创建索引后我们可以看到，sql执行计划中的type是ref，rows是2条，并不是常用的全表检索，而是进行索引扫描，快速来定位

四、索引底层的实现逻辑到底是怎么实现的？

1、字段添加索引后，是一个索引对象，他会默认的进行排序

2、排序之后会按照字段值进行分区

3、索引会携带出数据在表中的物理地址

4、最终通过索引检索后，获取都物理地址，通过物理地址来定位到表中的数据

演示：

未添加索引时sql的执行机制，全表检索，一一匹配，当数据量达到亿级的时候，搜索效率是非常慢的
mysql> select ename from t_emp;
+----------+
| ename    |
+----------+
| xds      |
| xds      |
| shenzhen |
| zs       |
| ps       |
| ps       |
| xw       |
+----------+
7 rows in set (0.00 sec)

当添加索引后，底层实现会默认对该字段进行排序操作

mysql> select ename from t_emp order by ename;
+----------+
| ename    |
+----------+
| ps       |   -- 011a
| ps       |　  -- 022a
| shenzhen |
| xds      |
| xds      |
| xw       |
| zs       |
+----------+
7 rows in set (0.01 sec)
因为数据在硬盘文件中是有对应的物理地址的，通过检索后会带出表中数据所在硬盘上的物理地址
从而最终在运行时的sql是这样的；
select ename from t_emp where 物理地址='011a';
通过直接搜索物理地址从而来达到快速查询的目的，大大的提升搜索效率

五、什么场景下不适合使用索引？

1、当需要添加索引的字段存在经常执行DML语句  --因为索引需要维护，如果字段值经常改变，数据库本省对索引的维护成本交大

2、当使用模糊查询时，通配符写了value值的前面，"%a%" --这样也是不适合使用索引的，这个时候索引是失效的

 

 --个人理解，如有误解，望评论区指出，谢谢！ 

 

这篇关于mysql中索引的实现逻辑的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！