series
MySQL > select * from mysql. select * from mysql. select
MySQL series 2: Redo log
MySQL series 3
MySQL MySQL series 4
Those of you who have read the previous articles will notice that there is no discussion of indexes and transactions, which have been covered in previous articles.
Here is a portal for you to click directly to view ha!
Uncover the mystery of MySQL index
Come up to ask MySQL transaction, shivering…
MVCC: I heard people were curious about my low-level implementation
Fantasy: I heard some people think I was killed by MVCC
Next open up the world of normal and unique indexes.
Understand common indexes and unique indexes
Normal index
The basic index type in MySQL, with no restrictions, allows you to insert duplicate and null values in the column where the index is defined, purely for the sake of querying data faster.
The only index
Values in index columns must be unique, but null values are allowed.
A primary key index is a special unique index that does not allow empty values.
Expand the other two indexes so that knowledge points can be remembered better together
The full text indexing
Select * from char, vARCHar, text; select * from char, varchar, text; select * from char, varchar, text; Through the beautiful boy, may be able to find the record.
Spatial index
Spatial indexes are built on fields of spatial data types. There are four spatial data types in MySQL, namely, GEOMETRY, POINT, LINESTRING, and POLYGON. Use the SPATIAL keyword when creating SPATIAL indexes. If the engine is Myisam, the column that creates the spatial index must be declared not NULL.
Index adding mode
Alter table table_name add primary key (column)
Alter table table_name add unique (column)
Alter table table_name add index index_name (column)
Alter table table_name add fulltext (column)
Alter table table_name add index index_name (column1,column2, colum3)
2. Application scenarios
Now that you know the difference between a normal index and a unique index, let’s look at how to select two indexes in some scenarios.
A business scenario mentioned in Mr. Ding’s article is the citizen system, which checks the name by the ID number.
Here kaka also uses this scenario to describe the process through kaka’s thinking.
Select name from user where card = ‘6104301996XXXXXXXX ‘;
The first reaction to this scenario would be to create an index for card, but what index? Primary key indexes are definitely not recommended.
Consider: why not use id numbers as primary key indexes?
3. Why not use a value too large as a primary key
The primary key index structure of Innodb storage engine is shown below
The general index data structure is shown below
The leaf node of the primary key index stores the entire row of data corresponding to the primary key.
The leaf node of a normal index stores the corresponding primary key.
If B+Tree reads data at a depth of three layers, each disk size is 16kb.
How much data can be stored in a non-leaf node of a B+Tree? Typically we have a primary key for each table.
The first and second layers store key values, which are the primary key values.
The first layer node can store 16 * 1000/10 = 1600. The first layer node can store 16 * 1000/10 = 1600.
Similarly, each layer 2 node can store 1600 keys.
The third layer is leaf nodes. The storage size of each disk is the same as that of BTree. Each piece of data occupies 1KB.
In B+Tree, the data that can be stored at three layers is 1600 * 1600 * 16 = 40960000
Conclusion: If the primary key is too large, it will directly affect the amount of data stored in the index. Therefore, it is not recommended to use too large data as the primary key index.
Four, from the perspective of query analysis
Suppose that we now want to look up the record card = 5. The search process is as follows: start from the root of the B+ tree, search to the leaf node by layer, and then locate the record card = 5 by dichotomy.
Normal index
For a normal index, when it finds card = 5, it will continue searching until it hits the first record that does not meet card = 5.
The only index
For unique indexes, it’s very simple, because unique indexes are unique, so if you look up card = 5, you don’t look up the next one.
Does one query for a common index have a significant impact on performance?
The effect is almost negligible, and in previous installments kakha popularized the term “locality principle.”
Data and programs have a tendency to cluster, and once they access one piece of data, they are more likely to access that piece of data and adjacent pieces of data again.
So MySQL’s Innodb storage engine also uses this local principle when reading data, which is 16kb, or one page.
The default page size in the Innodb storage engine is 16KB. This parameter can be adjusted as well. The parameter is innodb_page_size.
But there’s one thing you need to know, albeit with a very low probability.
When the index is a normal index, the data retrieved is the last data on the page, and the data on the next page needs to be read. This operation is a bit complicated, but can be ignored by modern cpus.
5. Learn about change Buffer
First of all, we need to understand a new knowledge point change buffer.
When the record card = 5 needs to be updated, the data page of this data is directly updated in memory. Otherwise, the updated operation needs to be cached in the Change buffer. When the next query needs to access the data page, the data page is read into memory and the operations associated with the page in change Buffer are performed.
Next, learn another new knowledge, merge.
Merge When data from the change Buffer is applied to a data page, the process of obtaining the latest results is called merge. This operation is also performed during a normal database shutdown.
Conclusion: The update operation will record the records in the change buffer first, which can reduce disk I/O and improve the statement execution speed.
Pay attention to
1. Data reading from change Buffer into memory needs to occupy the buffer pool. Using Change buffer can avoid occupying memory.
2. Change Buffer can persist data. Change buffer is copied in memory and written to disk.
6. Under what conditions is change buffer used
Consider: Why isn’t change Buffer used for unique indexes
The only index is definitely not useful, and if you feel a little uncomfortable with this answer, you need to look at it again before going back a few issues.
When a unique index inserts a row of data, it performs a query to determine whether the row already exists in the table and whether it violates the unique constraint. The change buffer must be used to read the data from the page into memory.
Therefore, only plain indexes can be used.
MySQL also provides a parameter to set the size of the change buffer. This may be a bit different from other data units. If set to 30, change buffer takes up only 30% of the buffer pool memory.
Consider: In what circumstances cannot change buffer be used?
The change buffer is used to cache updated actions, so when a data page is merged, the more changes recorded by the change buffer, the greater the benefit.
However, it is not applicable to all scenarios. Kakaka is currently developing an accounts software, and most updates are checked immediately. Does this violate the above statement that when merging a data page, the more records the change buffer has, the greater the benefits will be.
Therefore, the change buffer can be very useful only in scenarios where you write more and read less.
Consider: Why isn’t it useful to query Change Buffer immediately after the update?
After a record initiates an update operation, it is first recorded in the Change Buffer. Then, when the data queried is in the data page, the merge will immediately trigger the merge, so that the number of random ACCESS IO will not be reduced, but increase the maintenance cost of the Change buffer. Therefore, the use of Change Biffer in this business model will be counterproductive.
Consider: How to turn off change Buffer
Just set innodb_change_BUFFer_max_size = 0.
Analysis from the perspective of the impact of update statement performance
In the first case the data page to be updated is in memory.
Unique index: Looks in memory to see if the record exists and inserts the value if it does not.
Normal index: Directly update the value to be updated.
Conclusion: When the data page to be updated is in memory, a unique index makes one more judgment than a normal index.
In the second case, the data page to be updated is not in memory.
Unique index: You need to read the data page of this data into memory to see if this record exists, and then update the data.
Normal indexes: Record the data to be updated in the Change buffer.
Conclusion: Change buffer can significantly improve performance if your index is a normal index when the updated data is not in the data page.
Note: When changing an index from a normal index to a unique index, be aware of the effect of the change buffer, which can directly affect the memory hit ratio.
Eight, summary
Back to the topic of the article how to choose a normal index and a unique index, there is no difference between the two in terms of query, mainly in the impact of the update operation.
If your business needs to query the record immediately after the update, as in the click scenario, you can choose to directly turn off change Buffer.
If this is not the case, try to choose normal indexes. Using Change Buffer can significantly improve update performance.
“
Insist on learning, insist on writing, insist on sharing is the belief that Kaka has been upholding since he started his career. May the article in the big Internet can give you a little help, I am kaka, see you next time.
“