search
HomeDatabaseMysql TutorialDoes mysql have indexes?

Does mysql have indexes?

Nov 08, 2022 pm 07:28 PM
mysqlindex

mysql has an index. There are usually two ways to access the row data of a database table in MySQL: 1. Sequential access, that is, performing a full table scan in the table and traversing it row by row from beginning to end until the target data that meets the conditions is found in the unordered row data; 2. Index access is to directly access the record rows in the table by traversing the index. The data structure of MySQL index can be divided into two types: BTree and Hash, and BTree can be divided into BTree and B Tree.

Does mysql have indexes?

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

What is a MySQL index?

Index is a special database structure, which is composed of one or more columns in the data table. It can be used to quickly query records with a specific value in the data table. This section will explain in detail the meaning, function, advantages and disadvantages of indexes.

Through the index, when querying data, you do not need to read all the information in the record, but only query the index column. Otherwise, the database system will read all information of each record for matching.

The index can be compared to the phonetic sequence of the Xinhua Dictionary. For example, if you want to look up the word "ku", if you don't use phonetic sequence, you need to find it page by page in the 400 pages of the dictionary. However, if you extract the pinyin to form a phonetic sequence, you only need to look it up directly from the phonetic table of more than 10 pages. This can save a lot of time.

Therefore, using indexes can greatly improve the query speed of the database and effectively improve the performance of the database system.

Why use indexes

The index is the relationship between the column values ​​and the record rows established in a certain order based on one or several columns in the table. The correspondence table is essentially an ordered table describing the one-to-one correspondence between the column values ​​of the index columns and the record rows in the original table.

Index is a very important database object in MySQL and is the basis of database performance tuning technology. It is often used to achieve fast retrieval of data.

In MySQL, there are usually two ways to access row data of a database table:

1) Sequential access

Sequential access is in the table Perform a full table scan, traversing row by row from beginning to end until you find target data that meets the conditions in the unordered row data.

Sequential access is relatively simple to implement, but when there is a large amount of data in the table, the efficiency is very low. For example, when searching for a small amount of data among tens of millions of data, using sequential access will traverse all the data, which will take a lot of time and will obviously affect the processing performance of the database.

2) Index access

Index access is a way to directly access record rows in the table by traversing the index.

The premise of using this method is to create an index on the table. After creating the index on the column, when searching for data, you can directly find the location of the corresponding record row based on the index on the column, so as to quickly find the data. The index stores pointers to the data values ​​of the specified columns, sorting these pointers according to the specified sort order.

For example, in the student basic information table tb_students, if an index is established based on student_id, the system will create a mapping table from the index column to the actual record. When the user needs to find the data with student_id 12022, the system first finds the record on the student_id index, then directly finds the data row through the mapping table, and returns the row of data. Because the speed of scanning indexes is generally much greater than the speed of scanning actual data rows, using indexes can greatly improve the efficiency of the database.

In short, without using an index, MySQL must read the entire table starting from the first record until the relevant rows are found. The larger the table, the more time it takes to query the data. If the queried column in the table has an index, MySQL can quickly get to a location to search the data file without having to look at all the data, which will save a lot of time.

Classification of MYSQL indexes

1. Ordinary index and unique index

  • Ordinary index: The basic index type in MySQL, allowing duplicate values ​​and null values ​​to be inserted into the column that defines the index

  • Unique index: The value of the index column must be unique, but null values ​​are allowed

    • If it is a combined index, the combination of column values ​​must be unique
    • The primary key index is a special unique index and no null values ​​are allowed

2. Single-column index and combined index

  • Single-column index: an index only contains a single column, and a table can have multiple single-column indexes
  • Combined index: in the table Indexes created on multiple field combinations
    • The index will only be used when the left fields of these fields are used in the query conditions (leftmost prefix principle)

3. Full-text index

  • The type of full-text index is fulltext
  • supports full-text search of values ​​on the columns that define the index, allowing duplicates to be inserted in these index columns Values ​​and null values
  • Full-text indexes can be created on columns of char, varchar and text types

4, spatial indexes

  • Spatial index is an index established for fields of spatial data type

  • There are 4 types of spatial data types in MySQL, namely Geometry, Point, and Linestring. And Polygon

  • MySQL is extended using the Spatial keyword, allowing you to create a spatial index using a syntax similar to creating a regular index

  • Create a spatial index Column does not allow null values ​​and can only be created in MyISAM tables.

5. Prefix index

  • When creating an index on char, varchar and text type columns, you can specify the length of the index column

MySQL index data structure

The data structure of MySQL index can be divided into two types: BTree and Hash, and BTree can be divided into BTree and B-Tree.

Hash: Use Hash table to store data, Key stores index columns, and Value stores row records or row disk addresses.

Hash only supports equal value queries ("=", "IN", "") and does not support any range queries (the reason is that there is no connection between each key of Hash) , Hash query efficiency is very high, and the time complexity is O(1).

BTree: Belongs to a multi-tree, also known as a multi-way balanced search tree.

Properties:

  • The nodes of BTree store multiple elements (key value - address of data/child node)
  • The key values ​​of BTree nodes are arranged in non-descending order
  • BTree All leaf nodes are located in the same layer (with the same depth)

Does mysql have indexes?

##Query process, for example: Select * from table where id = 6;

Does mysql have indexes?

Disadvantages of BTree:

    does not support fast search for range queries (each query must be traversed from the root node again)
  • Storing data on all nodes will result in scattered disk data storage and reduced query efficiency

B Tree: On the basis of BTree, perform BTree Optimized: only leaf nodes will store key value-data, non-leaf nodes only store key values ​​and addresses of child nodes; leaf nodes are connected using bidirectional pointers to form a bidirectional ordered linked list.

Does mysql have indexes?

Equal value query, for example: Select * from table where id = 8;

Does mysql have indexes?

Range query, for example: Select * from table where id between 8 and 22;

Does mysql have indexes?

Advantages of B Tree:

    ensures fast equality query and range query Search
  • A single node stores more elements, reducing the number of IO queries
[Related recommendations:

mysql video tutorial]

The above is the detailed content of Does mysql have indexes?. For more information, please follow other related articles on the PHP Chinese website!

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
How do you handle database upgrades in MySQL?How do you handle database upgrades in MySQL?Apr 30, 2025 am 12:28 AM

The steps for upgrading MySQL database include: 1. Backup the database, 2. Stop the current MySQL service, 3. Install the new version of MySQL, 4. Start the new version of MySQL service, 5. Recover the database. Compatibility issues are required during the upgrade process, and advanced tools such as PerconaToolkit can be used for testing and optimization.

What are the different backup strategies you can use for MySQL?What are the different backup strategies you can use for MySQL?Apr 30, 2025 am 12:28 AM

MySQL backup policies include logical backup, physical backup, incremental backup, replication-based backup, and cloud backup. 1. Logical backup uses mysqldump to export database structure and data, which is suitable for small databases and version migrations. 2. Physical backups are fast and comprehensive by copying data files, but require database consistency. 3. Incremental backup uses binary logging to record changes, which is suitable for large databases. 4. Replication-based backup reduces the impact on the production system by backing up from the server. 5. Cloud backups such as AmazonRDS provide automation solutions, but costs and control need to be considered. When selecting a policy, database size, downtime tolerance, recovery time, and recovery point goals should be considered.

What is MySQL clustering?What is MySQL clustering?Apr 30, 2025 am 12:28 AM

MySQLclusteringenhancesdatabaserobustnessandscalabilitybydistributingdataacrossmultiplenodes.ItusestheNDBenginefordatareplicationandfaulttolerance,ensuringhighavailability.Setupinvolvesconfiguringmanagement,data,andSQLnodes,withcarefulmonitoringandpe

How do you optimize database schema design for performance in MySQL?How do you optimize database schema design for performance in MySQL?Apr 30, 2025 am 12:27 AM

Optimizing database schema design in MySQL can improve performance through the following steps: 1. Index optimization: Create indexes on common query columns, balancing the overhead of query and inserting updates. 2. Table structure optimization: Reduce data redundancy through normalization or anti-normalization and improve access efficiency. 3. Data type selection: Use appropriate data types, such as INT instead of VARCHAR, to reduce storage space. 4. Partitioning and sub-table: For large data volumes, use partitioning and sub-table to disperse data to improve query and maintenance efficiency.

How can you optimize MySQL performance?How can you optimize MySQL performance?Apr 30, 2025 am 12:26 AM

TooptimizeMySQLperformance,followthesesteps:1)Implementproperindexingtospeedupqueries,2)UseEXPLAINtoanalyzeandoptimizequeryperformance,3)Adjustserverconfigurationsettingslikeinnodb_buffer_pool_sizeandmax_connections,4)Usepartitioningforlargetablestoi

How to use MySQL functions for data processing and calculationHow to use MySQL functions for data processing and calculationApr 29, 2025 pm 04:21 PM

MySQL functions can be used for data processing and calculation. 1. Basic usage includes string processing, date calculation and mathematical operations. 2. Advanced usage involves combining multiple functions to implement complex operations. 3. Performance optimization requires avoiding the use of functions in the WHERE clause and using GROUPBY and temporary tables.

An efficient way to batch insert data in MySQLAn efficient way to batch insert data in MySQLApr 29, 2025 pm 04:18 PM

Efficient methods for batch inserting data in MySQL include: 1. Using INSERTINTO...VALUES syntax, 2. Using LOADDATAINFILE command, 3. Using transaction processing, 4. Adjust batch size, 5. Disable indexing, 6. Using INSERTIGNORE or INSERT...ONDUPLICATEKEYUPDATE, these methods can significantly improve database operation efficiency.

Steps to add and delete fields to MySQL tablesSteps to add and delete fields to MySQL tablesApr 29, 2025 pm 04:15 PM

In MySQL, add fields using ALTERTABLEtable_nameADDCOLUMNnew_columnVARCHAR(255)AFTERexisting_column, delete fields using ALTERTABLEtable_nameDROPCOLUMNcolumn_to_drop. When adding fields, you need to specify a location to optimize query performance and data structure; before deleting fields, you need to confirm that the operation is irreversible; modifying table structure using online DDL, backup data, test environment, and low-load time periods is performance optimization and best practice.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

DVWA

DVWA

Damn Vulnerable Web App (DVWA) is a PHP/MySQL web application that is very vulnerable. Its main goals are to be an aid for security professionals to test their skills and tools in a legal environment, to help web developers better understand the process of securing web applications, and to help teachers/students teach/learn in a classroom environment Web application security. The goal of DVWA is to practice some of the most common web vulnerabilities through a simple and straightforward interface, with varying degrees of difficulty. Please note that this software