The author likes to talk
Busy week passed again, this week’s biggest fun is to buy a small speaker, and can go off work when the happy began to practice the piano, programmer, or have to cultivate some art bacteria. Ha ha
This week was not ready to write something, just on Friday, the company for internal technology sharing, content for the MySQL index on + best practices, pick out a few important, and is one of the more common and share, there is still a lot of course involves knowledge about index, don’t elaborate on, originally want to reprint an article to share with you, But HUMBLE I have not added the original author wechat, if you are interested in the public number can reply “unique index and ordinary index”. I recommend the original address to you.
Now you can wash your hands, open your Naviact and practice with Manager Wang, knowledge absorption + 100%
Warm reminder: attached at the end of the article is a predicate sentence.
The index foreplay
Manager wang 🤓 : the sky is green, wild boundless, index inquiry helps a lot.
Small Lin small voice BB: manager like a silly batch.
Manager Wang: Lin Bubo, what are you talking about?
Xiao Lin: No, no, no, I said manager, good talent! This poem is written, TSK TSK, really 👍 cow glass
Manager Wang: You are a boy. You are perceptive today. Let me test you. Can you summarize the advantages and disadvantages of indexes?
Kobayashi: manager, the advantages of MySQL summarized in the book High Performance MySQL are
- Reduces the amount of data that a query needs to scan (speeds up the query)
- Reduces server sorting and temporary table creation (speeds up operations like groupby and OrDerby)
- Change server random IO to sequential IO(speed up queries)
As for the disadvantages, indexes are also data and need to be stored, so they take up extra storage space. Second, the index needs to be maintained at the same time as the insert, update, and delete operations, thus incurs additional time overhead
Mei: Bubo, you look so handsome today
Xiao Lin: 😎 😎 😎 😎
Manager Wang: today, bubu is right. Let me summarize the shortcomings of the index
- Indexes occupy disk or memory space
- Slow down the insert update operation
In practice, however, the cost of creating an index is far less than the benefit within a certain data range (without too many indexes), but we still need to prevent index abuse.
Manager Wang: Lin Bu move, what are you proud of? Get down from the table! Instead of focusing on that, we’ll focus on MySQL index best practices in production. Pick up the notebook, open Naviact, and move.
Best practices
Preexisting knowledge: EXPLAIN
1. Left-most prefix rule
EXPLAIN SELECT * FROM `employee_information` WHERE age = 23 AND position = "dev"
Copy the codeIf a column in the WHERE condition column starts at the left-most front of the index, the index is invalidated if the column does not start at the left-most front of the index. It means brother, cut me down. No. It should beThe first brother cannot die, the middle brother cannot be broken
Index (a,b) creates two indexes: index(a) and index(a,b). Index (a,b,c) creates three indexes: index(a), index(a,b), and index(a,b,c).
So, might as well ask yourself a why? 🎯 What are the problems with index redundancy? How many indexes is the best for a table? (See Ali Java Development Manual, or high Performance MySQL)
2, not doing any operations on the index column (calculation, function, (automatic or manual) type conversion) will cause the index to fail and turn to full table scan
EXPLAIN SELECT * FROM `employee_information` WHERE LEFT(name,3) = 'bud'
Copy the codeTake this SQL as an example, I need to fetch the name one by one, perform the function operation, and then compare the name with the qualified condition (BUD) one by one, so there is no way to use the index, only the full table scan.
So, is there a situation where. MySQL is doing something on your index column without you noticing? It’s causing our index to fail.
Like, implicit conversion, right?
3. The storage engine cannot use the column to the right of the range condition in the index (if the middle index column uses the range (>, <, like, etc.), all the following columns are invalid).
EXPLAIN SELECT * FROM `employee_information` WHERE name = 'budongli' AND age > 23 AND position = "dev"
Copy the codeType = range (key_len); age (key_len)
4, try to use overwrite indexes (index-only queries (index columns contain query columns)) and reduce SELECT * statements
EXPLAIN SELECT name FROM `employee_information` WHERE name = 'budongli'
Copy the codeWe use overwrite index in the corresponding SELECT operation, which means that the column of the query is overwritten by the index. Using overwrite index can be very fast, which is the ideal state in SQl optimization.
Note that when Using WHERE and Using index are used together, the Using WHERE is only used to look up data from the index. The data is then used to read, if not to filter, to avoid reading rows back into the table.
If we just like to use SELECT *, we’re screwed.
Extra tells you that the columns in the query are not covered by the index, and filters the data you need through the WHERE condition.
5, MySQL does not equal (! If = or <>), the index cannot be used, causing a full table scan
EXPLAIN SELECT * FROM `employee_information` WHERE name ! ='budongli'
Copy the codeThis and Shit the same SQL 🤐, when I was an intern also wrote.
As much as possible, this kind of judgment falls on the business layer, the database is tired, do not pressure him. Protect the database, starting with you and me.
6, like start with wildcard (‘ $ABC ‘) MySQL index failure will become a full table scan operation
Just like looking up the dictionary, for example, to look up the alphabetic word “CH”, turn the page starting with “CH” in order to have the result, in this SQL statement we want to look up the name starting with “budon”, use the index order to look up ok, soon can find out.
EXPLAIN SELECT * FROM `employee_information` WHERE name like 'budon%'
Copy the code
But if you wanted to look up the word “AN” in pinyin, you would have to go page by page through the entire dictionary to find out what was needed, which would be equivalent to scanning the entire table instead of the index.
EXPLAIN SELECT * FROM `employee_information` WHERE name like '%budon%'
Copy the code
7. Use less or or in
EXPLAIN SELECT * FROM `employee_information` WHERE name = "budongli" or name = "xiaomei"
Copy the codeMySQL’s query optimizer is a very complex part that uses a number of optimization strategies to generate an optimal execution plan: MySQL does not necessarily use an index when using or or in. The MySQL internal optimizer evaluates whether to use an index based on the number of pages per table or index, the cardinality of the index, the length of the index and rows, the distribution of the index, etc. There are many reasons why MySQL can choose the wrong execution plan, such as inaccurate statistics and not taking into account operation costs (user-defined functions, stored procedures) that are not under its control.
At the end of the day, MySQL doesn’t think it’s optimal the way we think it is (we want the execution time to be as short as possible, but MySQL values choose what it thinks costs less, but less cost doesn’t mean less execution time), etc.
So the MySQL internal optimizer is sometimes a fool, and it can be wrong. Of course, if you feel that the SQL statement will fly faster if you go to a fixed INDEX, feel free to use FORCE INDEX to Battle with the optimizer
Time for questions
If a field in a table can have a unique index or a normal index, how would you choose from a performance perspective if you were a DBA in your company? What is the basis of selection?
(It is suggested that you can take the initiative to think and practice. Of course, the official account has prepared more detailed sharing for you. You can reply “unique index and common index” in the official account, and I will share with you.)
Build table statements
-- ----------------------------
-- Table structure for employee_information
-- ----------------------------
DROP TABLE IF EXISTS `employee_information`;
CREATE TABLE `employee_information` ( `id` int(11) NOT NULL. `name` varchar(24) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL. `age` int(11) NOT NULL. `position` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL. `hire_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP. PRIMARY KEY (`id`) USING BTREE, INDEX `hybrid_index`(`name`.`age`.`position`) USING BTREE, INDEX `age`(`age`) USING BTREE ) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; -- ---------------------------- -- Records of employee_information -- ---------------------------- INSERT INTO `employee_information` VALUES (1.'budongli'.23.'dev'.'the 2020-06-12 22:21:24'); INSERT INTO `employee_information` VALUES (2.'xiaomei'.22.'dev'.'the 2020-05-06 22:22:10'); INSERT INTO `employee_information` VALUES (3.'jingliwang'.30.'manager'.'the 2018-06-01 22:22:37'); SET FOREIGN_KEY_CHECKS = 1; Copy the codeThis article is formatted using MDNICE