2014阿里实习生面试题MySQL如何实现索引的
【相关专题推荐：mysql面试题（2020）】
在MySQL中，索引属于存储引擎级别的概念，不同存储引擎对索引的实现方式是不同的，比如MyISAM和InnoDB存储引擎。
MyISAM索引实现：
MyISAM存储引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。
InnoDB索引实现：
虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是：InnoDB的数据文件本身就是索引文件。
第二个与MyISAM索引的不同是：InnoDB的辅助索引data域存储相应记录主键的值而不是地址。聚集索引这种实现方式使得按主键的搜索十分高效，，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。
其实，数据库索引的实现可以采用红黑树，B-Tree树数据结构。
但是为什么实际上采用的B+Tree呢？
这要从计算机存储原理和操作系统相关知识说起。因为数据表的索引比较大，不能常驻内存，所以以文件形式存储在磁盘中。所以当查询数据的时候就需要I/O操作。高效率查询的目标是较少I/O次数。一次I/O一般读取一页（一般为4k）大小的数据(局部性原理)。如此，在B-树中，每当申请一个新结点时，就以页的大小来申请。也就是说一次I/o可以读取一个一个结点（包含很多key）的数据；而在红黑树结构结构中，逻辑相邻的结点物理上不一定相邻，就是说，读取同等的数据需要多次I/O。所以选择B-树效率更好。
那为何最终选了B+树呢？
因为B+树内节点去掉了data域，因此可以拥有更大的出度，就是说一个结点可以存储更多的内结点，那么I/O效率更高。
了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。
聚集索引与非聚集索引之分：
InnoDB是聚集索引，因为它的B+树的叶结点包含了完整的数据记录。而MyISAM方式B+树的叶结点只是存储了数据的地址，故称为非聚集索引。
索引使用策略及优化
MySQL的优化主要分为结构优化（Scheme optimization）和查询优化（Query optimization）。
相关学习推荐：mysql教程
Hot AI Tools
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Undress AI Tool
Undress images for free
Clothoff.io
AI clothes remover
AI Hentai Generator
Generate AI Hentai for free.
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Hot Topics
1382
52
Several situations of mysql index failure
Feb 21, 2024 pm 04:23 PM
Common situations: 1. Use functions or operations; 2. Implicit type conversion; 3. Use not equal to (!= or <>); 4. Use the LIKE operator and start with a wildcard; 5. OR conditions; 6. NULL Value; 7. Low index selectivity; 8. Leftmost prefix principle of composite index; 9. Optimizer decision; 10. FORCE INDEX and IGNORE INDEX.
Under what circumstances will mysql index fail?
Aug 09, 2023 pm 03:38 PM
MySQL indexes will fail when querying without using index columns, mismatching data types, improper use of prefix indexes, using functions or expressions for querying, incorrect order of index columns, frequent data updates, and too many or too few indexes. . 1. Do not use index columns for queries. In order to avoid this situation, you should use appropriate index columns in the query; 2. Data types do not match. When designing the table structure, you should ensure that the index columns match the data types of the query; 3. , Improper use of prefix index, you can use prefix index.
When might a full table scan be faster than using an index in MySQL?
Apr 09, 2025 am 12:05 AM
Full table scanning may be faster in MySQL than using indexes. Specific cases include: 1) the data volume is small; 2) when the query returns a large amount of data; 3) when the index column is not highly selective; 4) when the complex query. By analyzing query plans, optimizing indexes, avoiding over-index and regularly maintaining tables, you can make the best choices in practical applications.
What are the classifications of mysql indexes?
Apr 22, 2024 pm 07:12 PM
MySQL indexes are divided into the following types: 1. Ordinary index: matches value, range or prefix; 2. Unique index: ensures that the value is unique; 3. Primary key index: unique index of the primary key column; 4. Foreign key index: points to the primary key of another table ; 5. Full-text index: full-text search; 6. Hash index: equal match search; 7. Spatial index: geospatial search; 8. Composite index: search based on multiple columns.
MySQL index left prefix matching rules
Feb 24, 2024 am 10:42 AM
MySQL index leftmost principle principle and code examples In MySQL, indexing is one of the important means to improve query efficiency. Among them, the index leftmost principle is an important principle that we need to follow when using indexes to optimize queries. This article will introduce the principle of the leftmost principle of MySQL index and give some specific code examples. 1. The principle of index leftmost principle The index leftmost principle means that in an index, if the query condition is composed of multiple columns, then only the leftmost column in the index can be queried to fully satisfy the query conditions.
Explain different types of MySQL indexes (B-Tree, Hash, Full-text, Spatial).
Apr 02, 2025 pm 07:05 PM
MySQL supports four index types: B-Tree, Hash, Full-text, and Spatial. 1.B-Tree index is suitable for equal value search, range query and sorting. 2. Hash index is suitable for equal value searches, but does not support range query and sorting. 3. Full-text index is used for full-text search and is suitable for processing large amounts of text data. 4. Spatial index is used for geospatial data query and is suitable for GIS applications.
How to use MySQL indexes rationally and optimize database performance? Design protocols that technical students need to know!
Sep 10, 2023 pm 03:16 PM
How to use MySQL indexes rationally and optimize database performance? Design protocols that technical students need to know! Introduction: In today's Internet era, the amount of data continues to grow, and database performance optimization has become a very important topic. As one of the most popular relational databases, MySQL’s rational use of indexes is crucial to improving database performance. This article will introduce how to use MySQL indexes rationally, optimize database performance, and provide some design rules for technical students. 1. Why use indexes? An index is a data structure that uses
Performance optimization strategies for data update and index maintenance of PHP and MySQL indexes and their impact on performance
Oct 15, 2023 pm 12:15 PM
Performance optimization strategies for data update and index maintenance of PHP and MySQL indexes and their impact on performance Summary: In the development of PHP and MySQL, indexes are an important tool for optimizing database query performance. This article will introduce the basic principles and usage of indexes, and explore the performance impact of indexes on data update and maintenance. At the same time, this article also provides some performance optimization strategies and specific code examples to help developers better understand and apply indexes. Basic principles and usage of indexes In MySQL, an index is a special number
