编程知识 cdmana.com

MySQL high performance index strategy

Prefix index and index selectivity

Sometimes you need to index long characters , This will make the index bigger and slower . One strategy is the one mentioned earlier Simulate hash index .
Usually you can Part of the character at the beginning of the index , This can greatly reduce the index space , Improve indexing efficiency . But this will reduce the selectivity of the index .

Index selectivity : Non duplicate index values ( Also becomes the cardinality ) And the total number of records in the data sheet . The more selective the index, the more efficient the query , Because highly selective indexes can filter more rows during lookup . The selectivity of the unique index is 1, Is the most selective .

Prefix indexing is a way to make indexes smaller and faster , But there are also shortcomings :
MySQL Can't use ORDER BY and GROUP BY, You can't use overlay scanning .

Cluster index

Cluster index is not a separate index type , It's a way of storing data .

When a table has a clustered index , Its data rows are actually stored in the leaf page of the index .

Clustering : Data rows and adjacent key values are compactly stored together .

If there is no primary key defined ,InnoDB Will choose a unique non empty index instead of .

If there is no such index , A primary key is implicitly defined as the cluster index .

Disadvantages of clustering index

  • The insertion speed depends heavily on the insertion order . Insert in the order of primary key is to load data into InnoDB The fastest way in the table . But if you don't load the data in primary key order , Best use OPYIMIZE TABLE Reorganize table .

  • Tables based on clustered indexes insert new rows , Or when the primary key is migrated , May be “ Page splitting ”. When the primary key value of a row requires that the row be inserted into a full page , The storage engine will split the page into two pages to hold the row , This is a page splitting operation . Page splitting causes tables to take up more disk space .

Overlay index

Usually, people will query according to WHERE Create an appropriate index based on the criteria , Well designed indexes can also Use the index to get the data of the column directly .

If the leaf node of the index already contains the data to be queried , Then why do you need to query the table again ? If an index contains the values of all the fields to be queried , We call it “ Overlay index ”.

Delayed correlation

Use inner join Make sub query . In the first stage of the query, you can use the overlay index . Although you can't use the index to cover the entire query , But it's better than completely unable to use index coverage .

Redundant and duplicate indexes

The larger the index, the more , The slower you insert data .

have access to Percona Toolkit Medium pt-duplicate-key-checker Analyze the table structure to find redundant indexes .

How many indexes are appropriate for a single table ?

The big table , The primary key has a unique index . There are one or two composite indexes , Up to three indexes are enough .

The number of indexes cannot exceed 4 individual / surface .

Everything follows the needs of the application . How many indexes are created on a table , What kind of index to create , There is no set rule . It can't be said that there is 7 An index , You can't create a second 8 An index .

The number of indexes depends on the specific business scenario .
For example oltp in , Tables often need insert etc. , Then you can't have too many indexes , Generally more than 3 One will have an impact on performance .
stay olap If the table is only used for query , So it doesn't hurt to build multiple indexes .
but olap You need to manually invalidate the index when loading data , When the data is loaded, rebuild the index . This can improve the loading speed .

Index and lock

Indexes allow queries to lock fewer rows . however , If the index cannot filter out invalid rows , So in InnoDB After retrieving the data and returning it to the server layer ,MySQL To use that WHERE Clause , At this time, it is impossible to avoid locking the line :InnoDB These lines have been locked .

mysql> select actor_id from sakila.actor where actor_id < 5 and actor_id <> 1 for update;

Although this query returns 2,3,4, But actually got 1-4 Exclusive lock .

Words and sentences , The operation of the storage engine is “ Find less than 5 The record of ”, The server didn't tell InnoDB You can filter the second 1 Yes WHERE Conditions . be aware EXPLAIN Of Extra The column appears “Using where”, This means MySQL Return the storage engine to the row and apply it later WHERE Filter conditions .

using where representative MYSQL The server tier applies the... After the storage engine tier returns the row WHERE Filter conditions

Re

版权声明
本文为[BLLBL]所创,转载请带上原文链接,感谢
https://cdmana.com/2021/10/20211002145410475h.html

Scroll to Top