1. Two ways for MySQL to force the use of indexes
1. Use the FORCE INDEX statement:
explain select * from tbl_test force index (index_item_code) where (item_code between 1 and 1000) and (random between 50000 and 1000000) order by random limit 1;
Use FORCE INDEX (index name) to index:
2. Use the USE INDEX statement:
explain select * from tbl_test USE index (index_item_code) where (item_code between 1 and 1000) and (random between 50000 and 1000000) order by random limit 1;
Use USE INDEX (index name) to index:
What is the difference betweenFORCE INDEX or USE INDEX?
- FORCE INDEX: This statement instructs MySQL to force the query to use a specific index. It ignores the optimizer’s choice and uses the specified index regardless of index selectivity. This means that even if a less suitable index is used, MySQL will force its use. This can cause performance degradation, as unsuitable indexes can cause queries to slow down.
- USE INDEX: This statement also allows you to specify the index to use, but it is different from “FORCE INDEX” in that it just implies that MySQL uses the specified index when possible. MySQL can still choose another index if it thinks it is better suited for the query. This can retain a certain degree of flexibility and allow MySQL to choose the best index according to the actual situation.
In general, “FORCE INDEX” forces the use of the specified index, while “USE INDEX” implies the use of the specified index, but MySQL can still choose other indexes based on the optimizer’s judgment. In actual use, evaluation and selection should be made based on specific circumstances.
2. The specific implementation data is as follows:
1. Create a data table and index:
CREATE TABLE `tbl_test` ( `id` bigint NOT NULL AUTO_INCREMENT COMMENT 'Primary key ID', `name` varchar(100) NOT NULL COMMENT 'name', `item_code` bigint NOT NULL COMMENT 'Subitem number', `order_code` varchar(100) NOT NULL COMMENT 'order number', `id_card` varchar(30) NOT NULL COMMENT 'ID card', `goods_number` bigint NOT NULL COMMENT 'goods quantity', `amount` decimal(6,2) NOT NULL COMMENT 'amount', `create_time` datetime NOT NULL COMMENT 'Creation time', `random` bigint NOT NULL COMMENT 'number of data', PRIMARY KEY (`id`), KEY `index_item_code` (`item_code`), KEY `index_id_card` (`id_card`), KEY `index_random` (`random`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Note: After the table is created, use the following command to add a new index:
-- View all index information in the tbl_test table show index from tbl_test;
Add index:
-- Create an index on the goods_number column in the tbl_test table CREATE INDEX index_goods_number ON tbl_test (goods_number);
Delete index:
-- In the tbl_test table, delete the index named index_goods_number ALTER TABLE tbl_test DROP INDEX index_goods_number;
2. Create a stored procedure:
--Create stored procedure
create procedure insert_data() begin declare i INT default 1;
while i <= 100000 DO
insert into test.tbl_test (
name,
item_code,
order_code,
id_card,
goods_number,
amount,
create_time,
random)
values (
CONCAT("test", i),
i,
CONCAT("order", i),
FLOOR(RAND() * 10000000000000),
i,
ROUND(RAND() * 100, 2),
NOW(),
FLOOR(RAND() * 1000000)
);
set
i = i + 1;
end while;
end
-- End
After the execution is completed, it can be viewed here:
Then, call the stored procedure:
-- Call stored procedure CALL insert_data();
After execution, the data information is as follows:
Here, you can use the execution time of the stored procedure to see how slow SQL is positioned.
3. Discovery of slow SQL
1. Execute the show variables like ‘%general%’; command to check whether the log function is enabled
The main commands used are as follows:
- set global general_log=on; This statement sets the value of the global variable general_log to “on”, indicating that the global query log is enabled. After enabling, the MySQL server will record all query statements to the query log file, including SELECT, INSERT, UPDATE, DELETE and other operations.
- set global general_log=off; This statement sets the value of the global variable general_log to “off”, indicating that the global query log is disabled. When disabled, the MySQL server will stop recording query logs and will no longer write query statements to the query log file.
By modifying the value of the global variable general_log, you can control the opening and closing of the global query log.
2. Check the current opening status of slow query log
-- Check the current opening status of slow query log show variables like '%quer%';
The execution information is as follows:
Setting information analysis:
- binlog_rows_query_log_events: This attribute is set to “OFF”, indicating that query events in the binary log are not recorded.
- ft_query_expansion_limit: This attribute is set to 20, which means that in full-text search queries, the expansion query is limited to a maximum of 20 words.
- have_query_cache: This attribute is set to “NO”, indicating that the query cache function is not enabled on the current MySQL server.
- log_queries_not_using_indexes: This attribute is set to “OFF”, indicating that query statements that do not use indexes are not logged.
- log_throttle_queries_not_using_indexes: This attribute is set to 0, indicating that query statements that do not use indexes will not be restricted.
- long_query_time: This attribute is set to 10.000000, which means that queries that take more than 10 seconds to execute will be considered slow queries.
- query_alloc_block_size: This attribute is set to 8192, indicating that the size of the memory block allocated to the query is 8KB.
- query_prealloc_size: This attribute is set to 8192, indicating that the memory size preallocated for the query is 8KB.
- slow_query_log: This attribute is set to “ON”, indicating that the slow query log function is enabled.
- slow_query_log_file: This attribute is set to “DESKTOP-0R9IERO-slow.log”, indicating that the name of the slow query log file is “DESKTOP-0R9IERO-slow.log”.
Through parsing, we still set the slow log threshold to 10 seconds by default (setting command: set global long_query_time = 10)
Through the value of slow_query_log_file, we find the slow SQL file DESKTOP-0R9IERO-slow.log, which is in the local C drive: C:\ProgramData\MySQL\MySQL Server 8.0\Data directory:
Slow SQL log information to view the execution of stored procedures:
4. Index optimization
1. EXPLAIN is a command used to query execution plans in MySQL. It helps you understand query execution, optimization, and performance.
EXPLAIN SELECT * FROM table_name WHERE column = 'value';
Here are some key information about the EXPLAIN command:
id: Represents the identifier of the query. If the query contains subqueries, each subquery has a unique identifier.select_type: Indicates the type of query. Common types include SIMPLE (simple query), PRIMARY (main query), SUBQUERY (subquery), etc.table: Indicates the name of the table involved in the query.type: Indicates the way to access the table. Common types include ALL (full table scan), INDEX (index scan), RANGE (range scan), etc.possible_keys: Indicates indexes that may be applied to the query.key: Indicates the actual index used.key_len: Indicates the length of the index field.ref: Represents a column or constant compared with the index.rows: Indicates the estimated number of rows that need to be scanned.Extra: Provide other additional information, such as whether temporary tables are used, sorting methods, etc.
By analyzing the output of EXPLAIN, you can obtain the following information:
- Check whether the query makes effective use of the index.
- The order and manner in which queries are executed.
- Which tables are accessed and how.
- Estimate the number of rows scanned and the cost of data access.
This information can help you optimize query statements, tune indexes, and improve performance.
2. Common index optimization 1:Conditional field function operations
An index has been created in the current table:
The function acts on the conditional column and the index becomes invalid:
After modification:
2. Common index optimization 2: Implicit type conversion
The current id_card field in the database is of varchar type, and it is directly queried as a numerical type, resulting in index failure:
After modification, it is as follows:
-- Use the following writing method: explain select * from tbl_test where id_card = '2674841548013' -- or: -- CAST(267484154801 AS CHAR) Converts the numeric type 2674841548013 to a character type that matches the data type of the id_card column (varchar). -- By doing this, it ensures that comparisons between id_card and values use matching data types, allowing the index to be used efficiently. explain select * from tbl_test where id_card = CAST(2674841548013 AS CHAR)
The reason for the failure is the same as the case type, and the data type is implicitly converted. For the optimizer, this statement is equivalent to:
select * from tbl_test where CAST(id_card AS signed int) = 66778899;
In this way, when functions, expressions or arithmetic are used in the WHERE clause, the index columns are used incorrectly, resulting in index failure.
3. Common index optimization 3: Implicit character encoding conversion
When using different character sets for implicit encoding conversion, it may cause index failure. This is because MySQL uses the collation rules of the character set for comparison when performing index searches. If the character sets are different, the collation will be different, causing the index to not be used correctly.
For example, in the following example, index failure occurs due to implicit character encoding conversion between character sets utf8mb4 and utf8:
Suppose there is a table my_table with a column named column that uses the utf8mb4 character set and an index is created.
CREATE TABLE my_table ( id INT PRIMARY KEY, column VARCHAR(255) CHARACTER SET utf8mb4 ) ENGINE=InnoDB; CREATE INDEX idx_column ON my_table (column);
Then, we insert some data into the table:
INSERT INTO my_table (id, column) VALUES (1, 'abc'); INSERT INTO my_table (id, column) VALUES (2, 'def');
Now, if we use query statements with different character sets for implicit encoding conversion, it may cause index failure. For example, the following query uses a string in the utf8 character set, which is different from the utf8mb4 character set in the table:
SELECT * FROM my_table WHERE column = 'ghi';
In this case, due to the different character sets, MySQL cannot use the index correctly and perform a full table scan. This results in poor query performance because full table scans are more time consuming than using indexes. To avoid this situation, you can ensure that the character set in the query statement is consistent with the character set in the table, or perform character encoding conversion explicitly.
5. A rough summary of the causes of MySQL index failure
1. Leading fuzzy queries cannot use indexes. For example, the query statement is LIKE ‘%XX’ or LIKE ‘%XX%’, but ‘A%’ can use the index normally.
2. If MySQL estimates that using a full table scan is faster than using an index, then the index will not be used.
3. There are non-indexed columns before and after OR, and the index becomes invalid. If you want to use OR and want the index to take effect, you can only add an index to each column in the OR condition.
4. Ordinary indexes do not mean that the index will not be used. If it is a primary key, the index will still be used; if it is a primary key or the index is an integer type, the index will still be used.
5. Index can be used for is null, but index cannot be used for is not null.
6. It is best to set NOT NULL constraints when designing the table, such as setting the default value of the INT type to 0 and setting the default value of the string to ”.
7. If any operation (calculation, function) is used on the index column in the query condition, or type conversion is performed, the index may become invalid.
8. If the query conditions in the composite index do not follow the leftmost matching principle, the index may not take effect.
9. If the MySQL optimizer thinks that a full table scan is faster than using an index, it may choose a full table scan without using an index.