This is the 15th day of my participation in the August More Text Challenge. For details, see:August is more challenging

preface

There are many advantages of indexes, but we still need to be careful when using them. Here are some points to pay attention to when using indexes.

Index of the failure

When are indexes not used?

  • Poor index selectivity
  • <>/not inUnable to use index
  • is nullI’m going to use indexes,is not nullIndex will not be used (whenwhereappearnotIndexes will not be used)
  • whereClause skip the left index column and directly query the right index field
  • Evaluate the index column or use a function

What is selectivity?

For example, the gender field in the user table is generally male or female. In other words, we need to accurately query. Even if the index is hit, we still need to scan half of the data and select the target in half of the data, so the selectivity is very low. However, compared with the ID number, once the index is matched, the query target can be directly locked, which can be regarded as selective.

Use indexes to optimize sorting

whenOrder ByThe index can optimize sorting speed when the fields are in the same order/sort direction as the index fields

Description:

  • Index sort is allowed when there is a column to the left of the index.
  • If the left field is sorted by a single field, the index can be sorted in ascending order.
  • In the case of multiple fields, the left field must be in ascending order and the order cannot be scrambled.

Delete redundant indexes

  • pt-duplicate-key-caecherispercona-toolkitUtility components in the toolkit (note that there is no Windows version of this tool)
  • It helps to check for duplicate indexes or primary keys in a table

Download tool

View the index usage

SELECT
	OBJECT_TYPE,
	OBJECT_SCHEMA,
	OBJECT_NAME,
	INDEX_NAME,
	COUNT_READ,
	COUNT_FETCH,
	COUNT_INSERT,
	COUNT_UPDATE,
	COUNT_DELETE 
FROM
`performance_schema`.table_io_waits_summary_by_index_usage
	ORDER BY SUM_TIMER_WAIT DESC
Copy the code

  • Here is the statistics on the use of indexes in the database level table. If the statistics of some indexes are all0, it proves that the index has never been used, and you can consider deleting the index
  • index_name 为Null“Indicates that no index is taken and a full table scan is performed
  • ORDER BY SUM_TIMER_WAIT DESCQuery the index usage that takes the most time

Reduce table and index fragmentation

-- Recalculate the statistics
analyze tableThe name of the tableRearrange data in tables, reorganize index structures, optimize tablespaces, and free tablespaces
-- Note: The table is locked during execution, and is executed during maintenance.
optimize tableThe name of the tableCopy the code

expand

A Hash index

  • HashThe index is implemented based on a hash table
  • Only queries that exactly match all columns of the index are valid
  • HashAn index is generated for each piece of dataHasCode

Hash index features

  • HashThe index contains only a row pointer to the hash value
  • Only accurate matching is supported. Range query, fuzzy query and sorting are not supported
  • HashThe value is fast but is not recommended when the index selectivity is low
  • MySQLAt present, onlyMemoryAccording to supportHashThe index

Hash index in InnoDB

  • InnoDBThe storage engine supports only display creationBTreeThe index
  • When the data are accurately matchedMySQLWill automatically generateHashCode, stored in the cache

conclusion

Advantages of indexing

  • Index greatly improves the efficiency of data retrieval
  • Index randomIO, becomes the orderIO

Disadvantages of indexes

  • The efficiency of data writing is reduced
  • Too many indexes increase the query optimizer’s selection time, increasingCPUThe cost of
  • Improper use of indexes will occupy a large amount of disk space