SoFunction
Updated on 2025-05-17

Interpretation of mysql index and transaction usage

Introduction to mysql index

Index Overview

In MySQL, indexing is a special data structure that can significantly improve the query efficiency of the database. Simply put, indexes are like directories of books, through which you can quickly find the location of the content you need without having to flip through the entire book page by page. In the database, indexing can help the database system quickly locate data rows that meet the conditions in the table, thereby reducing the number of data scans and improving query speed.

An index is essentially a well-sorted data structure that stores the values ​​of certain columns in a table and the physical addresses corresponding to these values. When executing a query, the database system first looks for the values ​​that meet the conditions in the index, and then directly access the corresponding data rows based on the physical address recorded in the index. This avoids full table scanning and greatly improves query efficiency.

Indexing function

Indexes play a crucial role in the database, mainly reflected in the following aspects:

  • Improve query efficiency: This is the main function of indexing. Through indexing, the database system can quickly locate data rows that meet the conditions, reducing the amount of data scans, thereby significantly improving query speed. Especially when processing large amounts of data, the advantages of indexing are more obvious.
  • Accelerate sorting and grouping operations: When performing sorting (ORDER BY) and grouping (GROUP BY) operations, if there are indexes on the relevant columns, the database system can directly use the orderliness of the indexes to sort and group without having to perform additional sorting operations, thereby improving the efficiency of these operations.
  • Ensure the uniqueness of the data: Unique index and primary key index can ensure the uniqueness of data in the table. When inserting data into the table, the database system will automatically check whether the value of the index column already exists. If it exists, it will refuse to insert, thus ensuring the uniqueness of the data.
  • Reduce I/O operations: Since indexes are usually much smaller than table data, indexes can be loaded into memory when querying, reducing disk I/O operations and improving query performance.

Index classification

MySQL provides multiple types of indexes, each with its specific application scenario.

Here are several common index types:

Normal index

Normal index is the most basic index type, and it has no special restrictions. Normal indexes can be created on a single column or on multiple columns (combined indexes). The purpose of creating a normal index is mainly to improve query efficiency.

The syntax for creating a normal index is as follows:

CREATE INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... INDEX index_name (column_name) );

Unique index

A unique index is similar to a normal index, but its main feature is that the value of the index column must be unique, that is, there are no duplicate values ​​allowed. A unique index can contain NULL values, but each NULL value is considered unique. Unique indexes are usually used to ensure uniqueness of data, such as fields such as email address, mobile phone number, etc. in user tables.

The syntax for creating a unique index is as follows:

CREATE UNIQUE INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... UNIQUE INDEX index_name (column_name) );

Primary key index

A primary key index is a special unique index that must uniquely identify each row of data in the table and cannot contain a NULL value. Each table can only have one primary key index. Primary key indexes are often used to define the primary key of a table, such as the User ID field in a user table.

The syntax for creating a primary key index is as follows:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);
CREATE TABLE table_name ( column1 datatype PRIMARY KEY, column2 datatype, ... );

Combination index (leftmost prefix)

Combined indexes are indexes created on multiple columns. Combined indexes are more efficient in querying, but their use needs to follow the principle of "leftmost prefix". The principle of leftmost prefix means that when using a combined index for query, MySQL will use the columns in the index from left to right, sequentially until the first column that does not meet the criteria is encountered.

For example, create a composite index on columns (col1, col2, col3) and the query conditions areWHERE col1 = 'value1' AND col2 = 'value2'When using the combined index, the query condition isWHERE col2 = 'value2' AND col3 = 'value3'When , the combined index is not used because there is no starting from the leftmost column.

The syntax for creating a composite index is as follows:

CREATE INDEX index_name ON table_name(column1, column2, column3);
CREATE TABLE table_name ( column1 datatype, column2 datatype, column3 datatype, ... INDEX index_name (column1, column2, column3) );

Full text index

Full-text index is a special index type, which is mainly used for full-text searches in text data. Full-text index can quickly locate text data containing specific keywords, and is suitable for search scenarios with a large amount of text data, such as article content search, product description search, etc.

It should be noted that full-text indexing is only applicable to MyISAM and InnoDB storage engines and can only be used for columns of type CHAR, VARCHAR, and TEXT.

The syntax for creating a full-text index is as follows:

CREATE FULLTEXT INDEX index_name ON table_name(column_name);
CREATE TABLE table_name ( column1 datatype, column2 datatype, ... FULLTEXT INDEX index_name (column_name) );

The syntax for searching using full-text index is as follows:

SELECT * FROM table_name WHERE MATCH(column_name) AGAINST('search_keyword');

Principles for creating indexes

When creating indexes in a database, certain principles need to be followed to ensure the validity and rationality of the index. Here are some basic principles for creating indexes:

  • Select the appropriate column to create an index: Indexes should be created on columns that are often used for querying conditions, sorting, and joining. For example, columns that appear frequently in the WHERE clause, columns used in the ORDER BY clause, and columns involved in the JOIN operation.
  • Avoid creating too many indexes on small tables: For tables with smaller data volumes, full table scanning may be more efficient than using indexes, so there is no need to create too many indexes. Too many indexes will increase the overhead of data insertion, update and deletion, and will also take up more storage space.
  • Control the number of indexes: The number of indexes in each table should not be too large, and it is generally recommended not exceeding 5-6. Too many indexes can affect the performance of the database, especially when data insertion, update and delete operations are frequent.
  • Pay attention to the order of combined indexes: When creating a composite index, the order of columns should be reasonably arranged according to the principle of leftmost prefix. The column that is most commonly used as a query condition is usually placed to the leftmost, and so on.
  • Avoid creating indexes on frequently updated columns: Frequently updated columns will cause frequent rebuilding of indexes, which will affect the performance of the database. Therefore, it should be avoided to create indexes on frequently updated columns.
  • Use prefix index for longer string columns: For longer string columns (such as TEXT, VARCHAR types), you can only create indexes for the prefix part, which can reduce the size of the index and improve the efficiency of the index. The syntax for creating a prefix index is as follows:
CREATE INDEX index_name ON table_name(column_name(length));

where length represents the length of the prefix.

Principles for database indexing

Determine whether the operations for this table are large quantities of query operations or large quantities of additions, deletions and modification operations

  • Try to create an index to help with specific queries. Check your own SQL statement questions and index fields that frequently appear in the where clause;
  • Try to create composite indexes to further improve system performance. Modifying the composite index will take longer to go, and the composite index will also occupy disk space;
  • For small tables, indexing may affect performance; indexing fields with fewer values ​​should be avoided; and columns of large data types should be selected as indexes.

Principles of index establishment

Index query is an important record query method in the database. Whether to create an index and establish an index on those fields must be combined with the query requirements of the actual database system to consider the following common principles in the actual production environment:

  • Indexing on fields that are often used as filters;
  • In SQL statements, indexes are often made on fields of GROUPBY and ORDERBY
  • There is no need to index fields with fewer different values, such as gender fields;
  • Avoid indexing for frequently accessed columns;

Create compound indexes on multiple columns that are frequently accessed, but pay attention to the order in which the compound index is established.

  • The frequency used is determined;
  • By default, non-cluster indexes are established, but in the following cases, it is best to consider cluster complex indexes, such as having a limited number of unique columns (not very few) and conducting large-scale queries; making full use of indexes can reduce the number of table scans I/0 times, effectively avoiding searches on the entire table.
  • Of course, reasonable indexes should be based on the analysis and prediction of various queries, and they also depend on the database structure designed by the DBA.

View index

In MySQL, you can use the following methods to view the index in the table:

Use SHOW INDEX statements：

SHOW INDEX FROM table_name;

This statement returns detailed information of all indexes in the table, including the index name, index type, column where the index is located, etc.

Use the SHOW CREATE TABLE statement：

SHOW CREATE TABLE table_name;

This statement returns the SQL statement that created the table, which contains the table's structure and index information.

Query the information_schema.statistics table：

SELECT * FROM information_schema.statistics WHERE table_name = 'table_name';

This statement returns detailed information of all indexes in the table, including the index name, index type, column where the index is located, etc.

Delete the index

In MySQL, you can delete the index using the following syntax:

DROP INDEX index_name ON table_name;
ALTER TABLE table_name DROP PRIMARY KEY;

It should be noted that deleting an index will affect the query performance that depends on that index, so its impact should be carefully evaluated before deleting an index.

mysql transaction

Transaction Overview

In MySQL, a transaction is an inseparable sequence of SQL statements, either all executed successfully or all failed. Transactions are an important mechanism for achieving data consistency and integrity in database management systems.

Transactions have four basic characteristics, commonly known as ACID characteristics:

  • Atomicity: A transaction is an inseparable unit of work. All operations in a transaction are either successful or fail. If any operation in the transaction fails, the entire transaction will be rolled back to the initial state.
  • Consistency: Transactions must make the database transition from one consistent state to another. That is to say, before and after the transaction is executed, the database integrity constraints must be met. For example, in a transfer operation, the total amount of the two accounts must remain the same regardless of whether the transaction is successful or failed.
  • Isolation: When multiple transactions are executed concurrently, the execution of one transaction cannot be interfered with by other transactions. Each transaction should not feel the existence of other transactions as if they were executed serially.
  • Durability: Once the transaction commits successfully, the modifications it makes to the database will be permanently saved and will not be lost even if the database fails.

Two methods of mysql transaction processing

MySQL provides two ways to handle transactions: implicit transactions and explicit transactions.

Implicit transactions

Implicit transactions are when MySQL automatically creates a transaction for each SQL statement and automatically commits or rolls back after the statement is executed. In implicit transaction mode, each SQL statement is an independent transaction and there is no correlation between them.

MySQL uses implicit transaction mode by default. In this mode, each SQL statement is automatically submitted after execution, unless an error occurs automatically rollback. For example:

INSERT INTO users (name, age) VALUES ('John', 30);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;

In the example above, each SQL statement is an independent transaction with no correlation between them. If the first INSERT statement is executed successfully and the second UPDATE statement is executed failed, the modification of the first statement will not be rolled back.

Implicit transactions are suitable for scenarios where multiple SQL statements do not need to be guaranteed to be atomic, such as simple queries and insertion and update operations of a single piece of data.

Explicit transactions

Explicit transactions are when the user manually controls the start, commit and rollback of a transaction. In explicit transaction mode, users can combine multiple SQL statements into one transaction, ensuring that these statements are either executed successfully or all fail.

In MySQL, you can use the following statement to manage explicit transactions:

  • START TRANSACTION or BEGIN: Used to start a transaction.
  • COMMIT: Used to commit a transaction and save all modifications in the transaction to the database permanently.
  • ROLLBACK: Used to roll back transactions, revoke all modifications in transactions, and restore the database to the state before the transaction begins.
  • BEGIN or START TRANSACTION:Open a transaction explicitly;
  • ROLLBACK: You can also use ROLLBACK WORK, but the two are equivalent. Rolling back ends the user's transaction and revokes all uncommitted modifications that are in progress;
  • SAVEPOINT identifier:SAVEPOINT allows to create a savepoint in a transaction, and there can be multiple SAVEPOINTs in a transaction;
  • RELEASE SAVEPOINT identifier: Delete a transaction's save point. When there is no specified save point, executing the statement will throw an exception;
  • ROLLBACK TO identifier:Rolle the transaction back to the marking point;
  • SET TRANSACTION:Used to set the isolation level of the transaction. The InnoDB storage engine provides isolation levels of transactions: READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ and SERIALIZABLE.

Here is an example of an explicit transaction:

START TRANSACTION;
INSERT INTO users (name, age) VALUES ('John', 30);
UPDATE accounts SET balance = balance - 100 WHERE user_id = 1;
INSERT INTO transactions (user_id, amount, type) VALUES (1, 100, 'withdrawal'); COMMIT;

Briefly introduce the main methods of mysql transaction processing (two types)

Use BEGIN, ROLLBACK, COMMIT to implement it

  • BEGIN Start a transaction
  • ROLLBACK transaction rollback
  • COMMIT transaction confirmation

Use SET directly to change the automatic submission mode of MySQL

SET AUTOCOMMIT=0Automatic submission is prohibited
SET AUTOCOMMIT=1Turn on automatic submission

Summarize

The above is personal experience. I hope you can give you a reference and I hope you can support me more.