mysql的索引怎么实现,mysql索引是如何实现的

MySQL 索引是怎么实现的？

索引是满足某种特定查找算法的数据结构，而这些数据结构会以某种方式指向数据，从而实现高效查找数据。

创新互联公司是一家专注于网站设计制作、成都做网站与策划设计,洪湖网站建设哪家好?创新互联公司做网站,专注于网站建设10年,网设计领域的专业建站公司;建站业务涵盖:洪湖等地区。洪湖做网站价格咨询:18982081108

具体来说 MySQL 中的索引，不同的数据引擎实现有所不同，但目前主流的数据库引擎的索引都是 B+ 树实现的，B+ 树的搜索效率，可以到达二分法的性能，找到数据区域之后就找到了完整的数据结构了，所有索引的性能也是更好的。

Mysql空间索引

在涉及LBS的服务开发过程中，经常需要存储地理空间的位置并进行一定计算（附近商家等需求），本文主要介绍mysql对于LBS的支持。

Mysql的空间扩展主要提供一下几个方面的功能：

其中前两点对InnoDB，MyISAM，NDB，ARCHIVE等mysql存储引擎都支持，第三点只有对InnoDB和MyISAM的支持，由于InnoDB的支持行锁以及事务的特性，现在基本上已经是默认存储引擎了，所以本文以下内容都默认使用InnoDB。

创建空间列以及空间索引的语句如下：

Mysql的空间数据类型与OpenGIS的数据类型相对应。

Mysql的空间数据有不同表示格式，其中咱能看懂的也就第一种

因为上文提到了SRID，这里说下什么是SRID，SR是指Spatial Reference，也就是我们常说的空间参考系，mysql支持卡迪尔坐标系和地理坐标系，其中地理坐标系又有好多种，下面说几种常用的空间参考系

Mysql的所有空间坐标系都存在表 mysql.st_spatial_reference_system 中，这个表是隐藏的，看不见的，但是你可以通过 infomation_shcema.st_spatial_reference_system 中查看参考系的信息，这个表就是 mysql.st_spatial_reference_system 的一个视图的实现。

mysql的空间索引的数据结构是R树，R树实际上就是多维的B树，B树的数据结构在我的另一篇博客中有介绍，这里就不展开了，说几点在应用的时候需要注意的。

最后转一篇博文

mysql之普通索引和唯一索引

常见的索引类型：哈希表、有序数组、搜索树。

mysql之普通索引和唯一索引。

执行查询的语句是 select id from T where k=5

这个查询语句在索引树上查找的过程，先是通过 B+ 树从树根开始，按层搜索到叶子节点，也就是图中右下角的这个数据页，然后可以认为数据页内部通过二分法来定位记录。

InnoDB的索引组织结构：

change buffer:持久化的数据。InnoDB将更新操作缓存在 change buffer中,也就是说,change buffer 在内存中有拷贝，也会被写入到磁盘，主要节省的则是随机读磁盘的IO消耗。

change buffer 只限于用在普通索引的场景下，而不适用于唯一索引.

merge:将 change buffer 中的操作应用到原数据页，得到最新结果的过程。

merge执行流程：

1、从磁盘读入数据页到内存

2、从change buffer里找出这个数据页的change buffer记录，依次应用，得到新版数据页

3、写redo log,这个redo log包含了数据的变更和change buffer的变更。

change buffer 用的是 buffer pool 里的内存，因此不能无限增大。change buffer 的大小，可以通过参数 innodb_change_buffer_max_size=50 表示 change buffer 的大小最多只能占用 buffer pool 的 50%。

如果要在这张表中插入一个新记录 (4,400) 的话，InnoDB 的处理流程是怎样的。

第一种情况是，这个记录要更新的目标页在内存中

这时，InnoDB 的处理流程如下：

第二种情况是，这个记录要更新的目标页不在内存中

这时，InnoDB 的处理流程如下：

mysql insert into t(id,k) values(id1,k1),(id2,k2); 当前 k 索引树的状态，查找到位置后，k1 所在的数据页在内存 (InnoDB buffer pool) 中，k2 所在的数据页不在内存中。

分析这条更新语句，你会发现它涉及了四个部分：内存、redo log（ib_log_fileX）、 数据表空间（t.ibd）、系统表空间（ibdata1）。这条更新语句做了如下的操作（按照图中的数字顺序）：

带change buffer的更新过程：

select * from t where k in (k1, k2) ,如果读语句发生在更新语句后不久，内存中的数据都还在，那么此时的这两个读操作就与系统表空间（ibdata1）和 redo log（ib_log_fileX）无关了.

Mysql InnoDB索引原理

理解Mysql索引的原理和数据结构有助于我们更好的使用索引以及进行SQL优化，索引是在存储引擎层面实现的，所以不同的引擎实现的索引也有一定的区别，但是在生产环境中，我们最常用的就是InnoDB引擎和B树索引，OK，那本文要讨论的重点也同样是 InnoDB引擎下的B树索引 。

我们建立一个表来进行测试，表的DDL如下所示，我们要关注的是表t_book上的主键索引id和name author publish_date三列组成的索引test_index。

Mysql中的B树索引是使用B+树实现的，关于B+树的数据结构个人认为美团点评技术博客中Mysql索引原理及慢查询优化一文中介绍的非常详实，B+树的数据结构如下图所示。

图中浅蓝色块即磁盘块，根节点磁盘块中存储17和35两个数据，其中指针P1指向小于17的数据，指针P2指向大于17小于35的数据，指针P3指向大于35的数据。显然通过B+树索引查询数据与B+树的高度有关，如上图的B+树索引查找一个叶子节点的数据只需要三次磁盘IO，对于Mysql来说三层的B+树可以索引上百万的数据，这对于查询效率的提升是巨大的。

总结起来Mysql中B树索引有以下关键特点：

Mysql中的B树索引有两种数据存储形式，一种为聚簇索引，一种为二级索引。

InnoDB一般会使用表的主键来作为聚簇索引，如果一个表没有主键（不建议这么玩）InnoDB会选用一个唯一非空索引来代替，如果没有这样的索引，InnoDB会隐式建立一个聚簇索引。聚簇的含义即是数据行和相邻的键值紧凑的存储在一起，占据一块连续的磁盘空间，因此通过聚簇索引访问数据可以有效减少随机IO，通常使用聚簇索引查找比非聚簇索引查找速度更快。以我们建立的表t_book为例，聚簇索引即为自增主键id，其B树索引数据结构可以用下图来表示。

聚簇索引有以下关键特点：

InnoDB的B树索引中除了聚簇索引，就都是二级索引了，二级索引的含义是索引的叶子节点除了存储了索引值，还存储了主键id，在使用二级索引进行查询时，查找到二级索引B树上的叶子节点后还需要去聚簇索引上去查询真实数据，但是这里有一种特殊情况，即查询所需的所有字段在二级索引中都可以获取，此时就不需要再去回表查数据了，这种情况就是索引覆盖（EXPLAIN中EXTRA列中会出现USING INDEX，本文只关注索引结构，不详细讨论索引覆盖等技术的使用，如果深入理解索引的数据结构，索引覆盖等技术也没有那么神秘）。

在我们的测试表t_book中，test_index即为二级索引，由于我们把除了主键id所有的列都作为一个联合索引，所以在这个表上的查询都可以使用索引覆盖技术，但是具体生产环境中也不建议总是采用这种做法，索引列的增加也会增大插入更新数据时的索引更新成本，具体的优化要视具体情况决策。t_book上的二级索引test_index的索引结构由下图表示。

通过以上结构，我们可以推断出二级索引的以下关键特点：

索引覆盖：

最左前缀匹配：

二级索引可以说是我们在Mysql中最常用的索引，通过理解二级索引的索引结构可以更容易理解二级索引的特性和使用。

最后聊点轻松的索引结构，哈希索引就是通过哈希表实现的索引，即通过被索引的列计算出哈希值，并指向被索引的记录。

哈希索引有如下特性：

Mysql索引原理及慢查询优化

高性能Mysql 第三版

mysql索引是如何实现的

1.添加PRIMARY KEY（主键索引）

mysqlALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加UNIQUE(唯一索引)

mysqlALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加INDEX(普通索引)

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加FULLTEXT(全文索引)

mysqlALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

mysqlALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

「Mysql索引原理（七）」覆盖索引

   通常大家都会根据查询的WHERE条件来创建合适的索引，不过这只是索引优化的一个方面。设计优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查找数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果索引的叶子节点中已经包含要查询的数据，那么还有什么必要再回到表中查询呢？ 如果一个索引覆盖所有需要查询的字段的值，我们就称之为“覆盖索引”。

覆盖索引是非常有用的工具，能够极大地提高性能：

   在所有这些场景中，在索引中满足查询的成本一般比查询行要小得多。

   不是所有类型的索引都可以成为覆盖索引。覆盖索引必须要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B+Tree索引所覆盖索引。另外，不同的存储引擎实现覆盖索引的方式也不同，而且不是所有的引擎都支持覆盖索引。

   当发起一个呗索引覆盖的查询是，在EXPLAIN的Extra列可以看到“Using index”的信息。

如： explain select col1 from layout_test where col2=99

   索引覆盖查询还有很多陷阱可能会导致无法实现优化。MySQL查询优化器会在执行查询前判断是否有一个索引能进行覆盖。假设索引覆盖了wehre条件中的字段，但不是整个查询涉及的字段。mysql5.5和更早的版本也总是会回表获取数据行，尽管并不需要这一行且最终会被过滤掉。

如： EXPLAIN select * from people where last_name='Allen' and first_name like '%Kim%'

这里索引无法覆盖该查询，有两个原因：

这条语句只检索1行，而之前的 like '%Kim%'要检索3行。

也有办法解决上面所说的两个问题，需要重写查询并巧妙设计索引。

   这种方式叫做延迟关联，因为延迟了对列的访问。在查询第一个阶段MySQL可以使用覆盖索引，因为索引包含了主键id的值，不需要做二次查找。

   在FROM子句的子查询中找到匹配的id，然后根据这些id值在外层查询匹配获取需要的所有列值。虽然无法使用索引覆盖整个查询，但总算比完全无法利用索引覆盖的好吧。

数据量大了怎么办？

   这样优化的效果取决于WHERE条件匹配返回的行数。假设这个people表有100万行，我们看一下上面两个查询在三个不同的数据集上的表现，每个数据集都包含100万行。

实例1中 ，查询返回了一个很大的结果集，因此看不到优化的效果。大部分时间都花在读取和发送数据上了。

实例2中 ，经过索引过滤，尤其是第二个条件过滤后只返回了很少的结果集，优化的效果非常明显：在这个数据及上性能提高了很多，优化后的查询效率主要得益于只需读取40行完整数据行，而不是原查询中需要的30000行。

实例3中 ，子查询效率反而下降。因为索引过滤时符合第一个条件的结果集已经很小了，所以子查询带来的成本反而比从表中直接提取完整行更高。

   在大多数存储引擎中，覆盖索引只能覆盖那些只访问索引中部分列的查询。不过，可以更进一步优化InnoDB。回想一下，InnoDB的二级索引的叶子节点都包含了主键的值，这意味着InnoDB的二级索引可以有效地利用这些额外的主键列来覆盖查询。

   例如，people表中last_name字段有一个二级索引，虽然该索引的列不包括主键id，但也能够用于对id做覆盖查询：

select id,last_name from people where last_name='hua'