Preface
In a database, indexing is one of the core tools to improve query performance. MySQL provides a wealth of indexing options, allowing us to design and optimize indexes based on different query requirements and data volumes. This article will explore in-depth some common problems, best practices for adding indexes in MySQL, and how to efficiently add indexes on large-scale tables to ensure that query performance is optimized without affecting the business.
1. Why do you need indexing?
In MySQL, indexes are a data structure of database tables that can speed up data retrieval. When the query conditions involve a large amount of data, if there is no index, the database will search for records that meet the conditions through a full table scan. This operation is very inefficient when the data volume is large. By creating appropriate indexes, MySQL can reduce query time by quickly locate to the index tree and significantly improve query performance.
Common index types include:
- Single column index: Only one column in the table is involved, the most common index type.
- Multi-column index (composite index): Indexes involving multiple columns. For queries involving multiple query conditions, composite indexes can significantly improve query performance.
- Unique index: Ensure that the value of the index column is unique.
- Full text index: Used to support full text search, suitable for large text data.
Although indexes improve performance when querying, they also bring some overhead, especially for insert, update, and delete operations. Therefore, index design needs to be balanced based on query requirements, data volume and update frequency.
2. How to add an index in MySQL?
2.1 Basic index addition syntax
In MySQL, the most common SQL statements that add indexes are as follows:
CREATE INDEX index_name ON table_name (column1, column2);
This statement will betable_nameThe table iscolumn1andcolumn2Create a composite index. You can also useALTER TABLEStatement to add index:
ALTER TABLE table_name ADD INDEX index_name (column1, column2);
2.2 Index selection and design
In order to effectively improve query performance, the selection and design of indexes are very critical. When creating an index, you first need to analyze the columns used in the query. Indexes are best for those inWHEREclause,JOINOperation andORDER BYColumns that appear frequently in clauses.
Common Index Design Principles：
- Select the column that is frequently queried: Select columns that often appear in query conditions for indexing.
- Avoid too much index: Although indexes can improve query efficiency, each index increases the cost of data modification (such as insertion, update, and deletion). Therefore, indexes should be created only for the most commonly used queries.
- Prioritize the creation of composite indexes: If a query involves multiple columns, composite indexes are usually more efficient than multiple single column indexes. MySQL uses composite indexes as much as possible when executing queries.
3. Index creation on big data scale
For tables with large data volumes, you need to be careful when adding indexes, as adding indexes will have an impact on the performance of the table. Here are some recommended methods to minimize the impact on database performance.
3.1 Creation using Online Index (Online DDL)
MySQL provides the ALGORITHM=INPLACE option to allow indexing to be added without locking tables. This means that even when the index is added, the application can still access other data for the table. The INPLACE algorithm can effectively reduce the impact on the business.
For example, the following SQL statements use ALGORITHM=INPLACE and LOCK=NONE to create indexes online:
ALTER TABLE your_table ADD INDEX index_name (column1, column2) ALGORITHM=INPLACE, LOCK=NONE;
- ALGORITHM=INPLACE: Specify the use of in-place algorithm to modify tables. MySQL does not copy table data, but directly modify the data structure of the original table.
- LOCK=NONE: The table is not locked during the index creation process, and other operations can be continued to minimize the impact on the business.
This method is most suitable forInnoDBStorage engine, but you need to make sure that your MySQL version supports this feature (supported by MySQL 5.6 and above).
3.2 Use the pt-online-schema-change tool
If the table is very large, or does not support online index creation, another commonly used solution is to usePercona ToolkitInpt-online-schema-changetool. The working principle of this tool is to create a new table, then gradually migrate the data from the original table to the new table, and switch the tables over after completion. The entire process will not have a big impact on the business.
The following is usedpt-online-schema-changeExample of tool indexing:
pt-online-schema-change --alter "ADD INDEX index_name (column1, column2)" D=your_database,t=your_table --execute
This tool ensures that tables are always available during index creation and will not lock the table. However, its disadvantage is that it relies on the stability of the tool and requires additional installation and configuration.
3.3 Add indexes in batches
If the data volume of the table is very large, performing a one-time index addition operation can cause significant performance problems. You can consider doing it in batches, that is, adding a portion of the index each time. By splitting large operations into small operations, the impact on the database can be reduced every time the modification is made.
3.4 Execute during low peak hours
For tables with large data volumes, if online tools or methods cannot be used, the easiest way is to perform index addition operations during the low peak period of business. While this can cause a brief downtime or performance degradation, this approach is feasible for most business systems.
4. Error diagnosis and FAQ
When adding indexes, you may encounter some common errors or problems. Here are some common situations and solutions:
4.1 Error: 1064 - Syntax Error
This is usually becauseALTER TABLEIncorrectly used in the statementALGORITHM=INPLACE, LOCK=NONEgrammar. In MySQL,ALGORITHMandLOCKMust be placedALTER TABLEthe subject syntax, not in the index part. The correct syntax should be:
ALTER TABLE your_table ADD INDEX index_name (column1, column2) ALGORITHM=INPLACE, LOCK=NONE;
4.2 Error: Error Code: 121 - Duplicate Key Name
If you encounter this error while adding an index, it means that the index you are trying to add already exists. Can be passedSHOW INDEXThe command checks the index that already exists in the current table to avoid repeated additions.
SHOW INDEX FROM your_table;
4.3 The index is added for too long
If the index addition operation takes too long, it may be due to the large amount of data in the table, or the unreasonable memory configuration of MySQL. Performance can be improved by adding memory buffers, optimizing table design, etc.
5. Summary
Adding indexes to MySQL tables is an important part of database optimization, which can significantly improve query performance, especially when the data volume is huge. However, you need to be careful when adding indexes, especially without affecting normal business. By adding indexes online, using tools such as pt-online-schema-change, and selecting the appropriate time window, you can efficiently add indexes on big data scales to optimize database performance.
In actual operations, choosing the right index type, reasonably planning the use and management of indexes, and using online operation methods are the key to improving MySQL performance and maintaining business stability.
The above is the detailed content of the optimization and practice of MySQL indexing. For more information about MySQL indexing, please pay attention to my other related articles!