MySQL table and index design guide

Welcome to pay attention to the rich way Web development team, PHP, front-end lack. Lack of people crowd

First of all, I want to say I’m sorry. Haven’t updated for a long time.

After a trip to Shanghai last month to attend the FDC2018 Front Thousand Summit. I haven’t had time to update it.

Experience sharing click here

A lot of feelings. I will attend more when I have the chance.

Think about learning something fun yourself these days.

The original link

Part I overview of MySQL

What is the MySQL

In 1970, Edgar Frank “Ted” Codd published A paper titled “A Relational Model of Data for Large Shared Data Banks,” This paper presents and proves for the first time that relational models can be used to describe data.

Relational models use the form of two-dimensional tables to represent entities and their relationships. MySQL is one of many relational databases implemented based on this theory.

Relational model diagram

The history and current status of MySQL

• 1990 Michael Widenius wrote the first version of MySQL.
• MySQL AB was founded by Michael Widenius in 1995.
• 2000 Michael Widenius publishes MySQL source code under the GPL license. MySQL entered the open source era.
• In 2008 Sun acquired MySQL AB. The MySQL database entered the Sun era.
• Oracle acquires Sun. The MySQL database entered the Oracle era. Oracle maintains both the community edition of MySQL and an enterprise version of MySQL.
• MariaDB was founded by Michael Widenius in 2009 by rebranching from the open source MySQL branch.

MySQL version

The community edition of MySQL is free and open source, while the enterprise edition is closed source and charged.

Below is a comparison of the major release features.

Tencent cloud CDB 5.6 version is: 5.6.28-CDB20160902-log. When online DDL, note that adding columns to a table will still cause congestion.

Mysql 5.7 has seen significant improvements in performance and concurrent connections.

MySQL and MariaDB compatibility and differences

With Oracle’s purchase of Sun, MySQL fell into the hands of the relational database king. In 2009, Michael, the father of MySQL, went ahead and started MariaDB, another MySQL spin-off, under the name of his daughter Maria, in consideration of Oracle’s reputation and the possibility of closed source.

As MariaDB is a full replacement of MySQL, the MySQL manual at dev.mysql.com/doc is generally applicable.– Source

We compile the community edition of MySQL base code into MariaDB every month to ensure that MariaDB is compatible with any patches and updates to MySQL that Oracle adds.

The MariaDB version matches the Mysql version — MariaDB 5.1, for example, uses the same code as Mysql 5.1. Since the updates and fixes are for the MySQL source tree, MariaDB can adopt these patches, meaning patches of the original code, not new features of each (in theory, MariaDB merges with MySQL source code every month). Source –

Upgrading from MySQL to MariaDB

Compatibility and difference

Logical architecture of MySQL

Mysql is made up of SQL interfaces, parsers, optimizers, caches, and storage engines.

• ConnectorsThe caller
• Management Serveices & UtilitiesSystem management and control tools
• Connection PoolThe connection pool
• SQL Interface
• ParserThe parser
• OptimizerThe optimizer
• Cache & BufferAll kinds of cache
• EngineThe storage engine

Thread pool is a core function of Mysql5.6. For server applications, whether web application services or DB services, high concurrent requests are always an unavoidable topic. When a large number of concurrent requests are received, resources are constantly created and released, resulting in low resource utilization and degraded service quality. Thread pooling is a common technique in which a number of threads are created in advance. When a request arrives, the pool allocates a thread to serve it, and when the request ends, the thread goes to serve other requests. In this way, frequent creation and release of threads and memory objects are avoided, concurrency on the server is reduced, context switching and resource competition are reduced, and resource utilization efficiency is improved. Thread pools for all services are essentially designed to improve resource efficiency and are implemented in much the same way. This article mainly explains the implementation principle of Mysql thread pool.

Physical file of MySQL

The log file records the changes that occur during mysql running. When mysql is damaged unexpectedly, you can use log files to recover data.

Logs record a lot of information. For example, mysql connection status, EXECUTION of SQL statements, and error messages are recorded.

Mysql log files contain the following types:

• The error log
• Query log
• Slow Query logs
• The transaction log
• Binary log

Error logs record data related to the mysql server.

Slow query logs record some queries that take a long time to execute.

Transaction logs are unique to InnoDB storage engine;

Binary logs record mysql statements that modify data or may cause data changes.

Mysql stores data files using different storage files depending on the storage engine.

SQL Execution Process

Everything we want to query or modify or delete data is done by executing SQL statements. Mysql does this by parsing the SQL statements we pass in.

After the SQL statement is passed into mysql, the system checks whether there is any matching data in the cache. If there is any matching data, the system returns the data directly to end the SQL. If not, the current SQL statement needs to be passed to the parser for parsing. Then preprocessing to check whether the syntax is consistent with the semantics, and finally the optimizer will convert it into an execution plan to get the most appropriate query statements for mysql, and finally to the query execution engine. Get the data we want at the end.

Cache pooling, sequential reads and random reads

The cache pool is implemented by the storage engine (which is a different level of cache than the query cache). In MySQL InnoDB, the size of the cache pool can be defined with the innodb_buffer_pool_size parameter.

The cache pool is maintained through the LRU policy. The database is considered to be performing best when the data in the database can be stored entirely in the cache pool. Except for synchronous or asynchronous disk writes, all operations can be done in memory.

Here is the 18GB data, and how TPS changes as the cache pool grows. 18 gigabytes of data, it’s a little bit bigger to store in memory, because there’s other overhead.

Because of the cache pool, some hot data can automatically lie in the cache pool.

Random and sequential read and write between disks: Sequential read refers to the sequential reading of pages from disks. Random reads are accesses to pages that are not contiguous and require the disk’s head to move constantly.

====================== Part one end =====================

The second part is database design

Model definition

• First normal form: Attributes are indivisible. Each column (each field) in the data table must be the smallest unit that cannot be split, that is, to ensure atomicity of each column;
• Second normal form: To have a primary key, require that all other fields depend on the primary key. If 1NF is met, all columns in a table must depend on the primary key, and no column can be independent of the primary key.
• Third normal Form: Eliminate transitive dependencies (eliminate redundancy). You must first satisfy the second normal Form (2NF), which requires that each column in a table is only directly related to the primary key, not indirectly.
• Bass-coad paradigm (BCNF) : there is only one candidate key in each table
• Fourth normal form: Eliminate multi-value dependencies in tables. (When non-primary attributes in a table are independent of each other (3NF), these non-primary attributes should not have multiple values)

Note: we usually get to the third normal form, which is too strict for practical use. The third normal form is generally well satisfied when the database is built according to the domain model.

Start by designing the table according to the specification of the paradigm. Then, according to the actual query requirements, anti-paradigm is used to accelerate the query.

Field type selection

Basic guidelines:

• Smaller, simple, adequate, usually better
• In general, you should try to use the smallest data type that can store data correctly.
• Smaller data types are generally faster because they take up less disk, memory, and CPU cache, and require fewer CPU cycles for processing.
• Of course, changing field types later can be time-consuming and painful. Therefore, at the beginning of the design, it is best to balance the “smaller rule” against “late maintenance”, depending on the size of the business. If enough is available, choose the smallest.
• Select a simple data type based on the field properties. For example, age should be stored as an integer, not a string. Time should be stored with built-in time types. IP should be stored in int. (Inet_aton (' 127.0.0.1) inet_ntoa(4294967295)MySQL already has built-in conversion functions.
• Try to avoid NULL
• Avoid using set and enum types

The integer

• TINYINTEight:
• SMALLINT: 16
• MEDIUMINT: 24
• INT: a 32-bit
• BIGINT: 64

Range: -2^(n-1) to (2^(n-1))-1

With UNSIGNED, the range of positive numbers can be formally doubled by +1, such as -128 to 127 -> 0 to 255

• UNSIGNED INT4 billion
• UNSIGNED BIGINT18.446744 quintillion

INT(11), where the 11 doesn’t do anything. It simply specifies the format in which the MYSQL client displays data.

Note: The above five integer types only specify how MYSQL stores data in memory and on disk. MYSQL converts all integers into 64-bit BIGINT.

Real type

The decimal type is recommended for decimal fields; float and double are not precise enough, and decimal must be used especially for business involving money. — Tencent Cloud CDB for MySQL Usage Specification Guide

In our actual business, we prefer to use integer type to store (expand 1000, 10000, etc.).

String type

After 5.7, utF8MB4 is used by default

• CHAR fixed length. The number of characters in parentheses sets the number of characters that can be stored. If not, the system will add something by default.
• VARCHAR. The maximum number of characters is in parentheses. When you actually store data, you don’t open a fixed length of space. It is based on the data being written, but cannot exceed the maximum number of characters.

Note that the parentheses define the number of characters, not bytes

CHAR removes the right-most space (if any). VARCHAR is retained.

VARCHAR and CHAR contain the maximum number of characters in parentheses.

For ‘hello’, it is better to use VARCHAR(5) than VARCHAR(200). Although they take up the same amount of space, VARCHAR(5) is still better, and some operators are more efficient later on. (Less principles)

Storing large amounts of data:

• BLOB series (binary) : TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB
• TEXT series (string) : TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT

As a general practice, integer fields are the most efficient to find. Therefore, if certain fields are to be found, sorted, associated, and so on, using integers is most efficient.

Date and time types

• DATETIME

Use 64-bit storage, with a time span of 1001 to 9999 years. This type does not have the concept of time zone. For example, IF I save “2018-01-01 00:00:00” in east district 8 and take it out in west district 8, I still get “2018-01-01 00:00:00”. That’s not true.

Therefore, it is a good practice to use DATETIME in the database, and then save the time in the program generated UTC time (not the local time zone time), and retrieve the server time or the user time whenever you want.

• TIMESTAMP uses 32 bits to store TIMESTAMP (so range 1970~2038)

MYSQL provides FROM_UNIXTIME to format the output to show the time (and also to help you add the time zone). Also, MySQL provides CURRENT_TIMESTAMP to automatically maintain CREATED_AT

Int TIMESTAMP, DATETIME, TIMESTAMP, TIMESTAMP, DATETIME

www.jianshu.com/p/edfdaacc3…

Blog.csdn.net/ppvqq/artic…

Identifies the data type selection for the column

An identity column is a key value used to identify a row of data, such as an increment ID. Because these identity columns are often used for associative operations, comparisons with other data, and so on, special care must be taken to select the data type of the identity column for optimal query efficiency.

Here are some tips

• Once a type is selected, it is desirable that the data type of the associated column to which it is compared or associated is exactly the same as that of the identity column, includingunsignedThese properties had better be the same.
• chooseintPlastic surgery is always the best choice.
• Avoid usingEnum and set types.
• Try not to use string types.
• Try to avoid NULL

Some skills

Use cache tables and summary tables

In order to read faster, you have to write more slowly. Sometimes the best way to improve performance is to keep redundant derived data in the same table. However, sometimes it is necessary to create a completely separate summary table or cache table (especially for retrieval purposes)

• Cache table: Generally used for “redundant data”, can be generated from a table, take a certain amount of time.
• Summary table: Usually the result of a group operation (for example, a collection table is a summary table).

Summary tables, count tables, may encounter write bottlenecks. In this case, you can use “slot” to randomly assign each new “+1” to a row. In this way, a row is locked for each write, and one of N slots is selected for each write. Write lock wait is avoided as much as possible.

Some other tips

Constraint and concurrency control

The only index

Using the database mechanism to help us achieve uniqueness. You can combine the uniqueness of fields to achieve uniqueness and so on.

Optimistic locking

When data is updated, update conditions include the version number (or the value of an important field) of the previously read record.

Optimistic lock support for YII

Happens to decrease

If count=4, add 1 and save to database. Count = count + 1 and count = 5

Use slots to reduce lock resource waits

If you want to make frequent changes to a row of data, there may be a write lock wait on that row. At this point, consider whether you can change one line from business logic to multiple lines. Allocate write operations to multiple lines to reduce lock waits for a single line.

Store raw data instead of results

For example, to get the remaining number of sweepstakes a user has. At this point, in the database, saving the number of draws that have been used and the total number of draws is better than saving only one remaining draw.

=================== Part 2 end =======================

The third part index design

MySQL 5.6 InnoDB BTREE index As long as the MySQL InnoDB BTREE tree structure is clear, you can derive a lot of index design criteria.

The index profile

An index is just a data structure. (Hash tables, trees, etc.) Different types of indexes have different data structures and functions.

For example, in Chinese textbooks, each article corresponds to a starting page number, and all articles are arranged and typeset in accordance with the page number order. In this case, “articles are arranged in page number order” is the data structure of the index, and the table of contents of the book is the index file.

The index type

Mysql 5.6 InnoDB provides two index types (Index_type)

• BTREE
• FULLTEXT

(Mysql 5.6 InnoDB does not support manual use of hash index (InnoDB internal support for adaptive hash index), nor Geospatial index (support after 5.7))

Clustered indexes refers to Clustered indexes

This can be done using the BTREE index

• The primary key indexPRIMARY KEY
• The only indexUNIQUE KEY
• Normal indexKEY

(FOREIGN KEY implementation, also partly depends on the BTREE index, when the specified FOREIGN KEY must be at least a normal index, otherwise, the system will automatically help you to create a FOREIGN KEY.)

You can do this using the FULLTEXT index

• The full text indexingFULLTEXT KEY

Single column index and union index

• Single-column index, using only one column to build the index
• A federated index that uses multiple columns to build an index

CREATE DATABASE `test_db` DEFAULT CHARSET utf8 COLLATE utf8_general_ci; use test_db; CREATE TABLE `table_b`( `id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `title` VARCHAR(100) NOT NULL, `name` VARCHAR(100) NOT NULL, `f_id` INT UNSIGNED NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY uk_name (`name`), KEY title (`title`), KEY title_name_fid (`title`,`name`,`f_id`), FULLTEXT KEY (`name`) )ENGINE=InnoDB DEFAULT CHARSET=utf8; SHOW INDEX FROM table_b; +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+ | table_b | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | | table_b | 0 | uk_name | 1 | name | A | 0 | NULL | NULL | | BTREE | | | | table_b | 1 | title | 1 | title | A | 0 | NULL | NULL | | BTREE | | | | table_b | 1 | title_name_fid | 1 | title | A | 0 | NULL | NULL | | BTREE | | | | table_b | 1 | title_name_fid | 2 | name | A | 0 | NULL | NULL | | BTREE | | | | table_b | 1 | title_name_fid | 3 | f_id | A | 0 | NULL  | NULL | | BTREE | | | | table_b | 1 | name | 1 | name | NULL | 0 | NULL | NULL | | FULLTEXT | | | +---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+---- --+------------+---------+---------------+Copy the code

The role of indexes

• Speed up queries
• Maintain data constraints (integrity, consistency)

For faster queries, using indexes is not necessarily the best choice. Small table on the direct full table scan, in the large table to build index, large table on the partition table. It’s a trade-off between the benefits of an index and the cost of maintaining it.

BTREE index

In MySQL 5.6 InnoDB, we usually create index, only BTREE option. Full-text indexes, which are not typically used in our business scenarios.

Introduction of B + tree

B+ tree is a classical data structure, like binary tree and balanced binary tree. B+ trees are accessed sequentially by B trees and indexes (ISAM, familiar? Yes, this is the same data structure MyISAM engine originally referenced) evolved, but the actual use of b-trees is almost non-existent.

A B+ tree is a balanced search tree designed for disks or other direct access auxiliary devices. In a B+ tree, all the record nodes are stored in the same layer of leaf nodes in order of key values. Pointers to leaf nodes are connected.

Features:

• The tree
• Search trees
• balance
• The data is all in the leaf node
• A node can contain multiple pieces of data
• There are Pointers connecting leaf nodes
• Find, insert, delete approximately O(LGN)

Related:

• Locality principle
• I/O is slow

Why use a B+ tree data structure as an index?

• Search tree, search efficiency is close to O(LGN)
• Balanced, where the number of lookups per query is roughly equal
• Index data on non-leaf nodes can be loaded into memory to speed up the query
• Leaf node points are connected with Pointers to facilitate traversal

Online B+ tree generator

Mysql InnoDB logical storage structure

The logical structure is designed to be manageable, just as a school divides grades, classes, and groups.

• Tablespace (tablespace)
• Segment (segment)
• Area (among)
• Page (page)
• Row (row)

1. A page is a node (leaf or non-leaf) in a B+ tree.

2. Pages have sub-index pages and data pages. Index page, which stores the data of non-leaf nodes, data page stores the data of leaf nodes. Each page contains more than one row connected sequentially by a pointer. When we search for data, we locate a page and then look for the desired row within the page. Each page defaults to 16KB.

3. An area contains 64 pages. An area is applied for as a block during disk application. Therefore, data in an area is physically contiguous. (64 pages x 16KB for a page = 1M for a zone) General memory has the ability to load all non-leaf nodes in a B+ tree index into memory for management. Therefore, maintaining the non-leaf nodes of the B+ tree in memory is generally relatively inexpensive. However, the maintenance of leaf nodes usually requires disk IO, so the entire leaf data cannot be loaded into memory.

4. A table space is like a folder, and segments are files within the folder. When an index is created, two segments of data are created, one for index only (index segment) and one for data only (data segment).

5. A table space is the data of a table.

The logical storage structure of Mysql InnoDB has three interesting aspects:

• Segment is to distinguish non-leaf data from leaf data. Easy to load the entire non-leaf data into memory.
• An area is a contiguous disk space.
• Page, page is also a continuous space.

Analogy:

• The center, the division of the center, is to better manage employees with the same functional skills.
• A line of business, a line of business contains a number of small groups, each group sitting nearby, can facilitate communication.
• Small groups, small groups of employees, the same group of employees sitting together, easy to communicate.

From the logical structure, we can know:

• continuity
• Separate management of leaf nodes and non-leaf nodes

MySQL InnoDB BTREE

From the picture above, we can draw the following conclusions

• All values match
• Matches the leftmost prefix
• Matching column prefix
• Matching range value
• Matches exactly one column and ranges exactly the other
• The segment index words following the first range query field can only be used for filtering.

A field in an index can serve two purposes:

• Determines the start and end positions of the index slice
• Filter, compare filter

Clustered indexes and secondary indexes

Clustered index

In MySQL InnoDB, every row has a primary key. The official recommendation is to use the business-independent integer unsigned increment non-null type as the primary key. If you do not define a primary key, the system selects a primary key based on the following rules

• See if there is a single non-empty unique index, and if so, use this as the primary key. If there are more than one, select the first field that matches the condition in the construction statement.
• If not, maintain a 6-byte space as the primary key. For details, please see

Extension: why do we define a self-increment non-null int

• 6 bytes. You probably won’t need that much on your own
• The order. Reduce page splitting.

Secondary index

Other indexes are secondary indexes (unique indexes, plain indexes, and so on). In the secondary index, the leaf node holds the primary key value of the record. The primary key value is then aggregated to find the actual value.

Is it recommended to add primary key fields after secondary index fields required by the business?

There is no experimental difference between adding and not adding here. The optimizer should take care of it for us (the optimizer is very powerful, the code of the optimizer updates quickly and has many features, trust the optimizer).

Why don’t the leaf node of the secondary index store the actual physical pointer (page number, etc.) instead of going to the clustered index again?

If so, if pages in the clustered index are split, the physical structure of the data will change. If the secondary index still stores physical information, then the data in the secondary index needs to be updated, which can cause a lot of additional operations. Now, with this structure, we have to look it up twice, but that’s not a big burden because the non-leaf nodes are in memory, so it’s fast to look it up.

Samsung index design specification

How to design an index for an SQL statement.

Cover index

Without accessing the clustered index, the requirements can be fulfilled through secondary indexes.

• The fields to select are in the index.
• Select count(*) because we only need one number, so we only need access to secondary index, so it is also overridden index.

Secondary indexes are usually smaller than clustered indexes (a leaf page of a secondary index can contain more column records on one page).

How to optimize count(*)

Especially with conditional counting, must scan, no system statistics can be taken directly. So, either use the overwrite index, or if it’s still slow, use the summary table that you designed earlier, and set up multiple slots to prevent locking the table.

When we talk about a full table scan, we mean walking through the leaf nodes of the clustered index

Definition of three-star index:

• First star: minimize the range of leaf nodes to be scanned (the interval between the start and stop bits should be as small as possible).
• Second star: Avoid sort operations that load data from leaf nodes into memory. Use the B+ tree index to help us sort ahead.
• Third star: Avoid reducing back to clustered index queries and solve battles with secondary indexes.

The design steps

In general, we can achieve the third star, namely, using overwrite indexes to avoid the process of returning to clustered index lookups can save a lot of I/O back to the table.

If the where criteria of the query are all equivalent queries (or if there is no sorting), then we can accomplish the 3-star requirement.

If there is a range query in the WHERE condition of the query and there is a need to sort, then we simply choose between (first star + third star) and (second star + third star).

Candidate A,

(first star + third star) pursue narrow, scan the least number of index slices

1. Take the columns for equivalent conditions and use them as the leading columns of the index, in any order. (High selectivity near the front will be better, easy to reuse other queries this is the index)
2. Make the most selective range condition the next column in the index.
3. Add them in the right orderorder byThe column. Ignore the columns that were added in the previous two steps.
4. Adding additional columns from the SELECT statement to the index in any order makes it difficult to change the column start.

Candidate B

(Second star + third star) Pursuit is not sorted

1. Take the columns for equivalent conditions and use them as the leading columns of the index, in any order. (High selectivity near the front will be better, easy to reuse other queries this is the index)
2. Add them in the right orderorder byThe column. Ignore the columns that were added in the previous two steps.
3. Add additional columns from the SELECT statement to the index in any order, starting with immutable columns.

If there is A sort and range query, candidate A or candidate B is considered. Otherwise, A three-star index can be satisfied.

A. in order by B. in order by

Choosing between candidate A and candidate B is A judgment between the cost of loading into memory and the cost of iterating through the filter one by one. This kind of non-qualitative answer depends on the characteristics of the data and what kind of data to take.

select A,B from user where A > a order by B;

Assume that there are n records in total, and M records meet the condition A > A. Each record receives an I/O

• Candidate A:n->mAnd theni/o mData entering the memory for sorting takes timeO(mlgm), namely, the time is:mtimei/o+ mlgmTime to compare
• Candidate B:i/o nIs entered into memory for comparison, and the comparison times are1~n, namely, the time is:1~nLess [than +i/oTime]. (Because you can exit early if you only need 1 data, so1~n)

If m is large, large enough to approach n, then candidate B is good. If m is small, close to one, then candidate A is good.

================== Part 3 end ====================

The fourth part is practice test

The hardware configuration

• Tencent cloud CDB
• 5.6.28 – cdb2016 – log 20180122
• High IO version, 1000MB ram, 25GB hard disk, 1000 cycles/SEC

Main configuration information about MySQL

• Disabling query caching

show variables like '%query_cache%';
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | OFF     |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+
6 rows in set (0.01 sec)
Copy the code

• Each page size 16KB

show variables like '%innodb_page%';
+------------------+-------+
| Variable_name    | Value |
+------------------+-------+
| innodb_page_size | 16384 |
+------------------+-------+
Copy the code

• InnoDB cache size 893M

show variables like '%innodb_buffer_pool%';
+-------------------------------------+----------------+
| Variable_name                       | Value          |
+-------------------------------------+----------------+
| innodb_buffer_pool_dump_at_shutdown | OFF            |
| innodb_buffer_pool_dump_now         | OFF            |
| innodb_buffer_pool_filename         | ib_buffer_pool |
| innodb_buffer_pool_instances        | 8              |
| innodb_buffer_pool_load_abort       | OFF            |
| innodb_buffer_pool_load_at_startup  | OFF            |
| innodb_buffer_pool_load_now         | OFF            |
| innodb_buffer_pool_size             | 936378368      |
+-------------------------------------+----------------+
Copy the code

Test the initial state of the database

• Quantity: 500 w
• Primary key: int autoincrement primary key

CREATE TABLE `user` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `sex` tinyint(3) unsigned NOT NULL,
  `age` tinyint(3) unsigned NOT NULL,
  `email` varchar(255) NOT NULL,
  `address` varchar(350) NOT NULL,
  `company` varchar(255) NOT NULL,
  `city` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8


select count(*) from user;
+----------+
| count(*) |
+----------+
|  5037343 |
+----------+
1 row in set (1.81 sec)


select * from user limit 1 \G
*************************** 1. row ***************************
     id: 1
   name: Prof. Osborne Waelchi I
    sex: 0
    age: 60
  email: [email protected]
address: 35712 Quigley Mountains North Alysonville, CO 53682-2718
company: McGlynn Ltd
   city: Port Maziebury
1 row in set (0.01 sec)
Copy the code

The experiment case

Indexes related to

The evaluated column cannot be indexed

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> explain select * from user whereid+1=2; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.04 sec)

MySQL [test_db_for_index]> select * from user whereid+1=2; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (3.04 sec)

MySQL [test_db_for_index]> select * from user whereid=1; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (0.01 sec)

Copy the code

Column field prefixes and IN bypass techniques

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 3 rowsin set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (3.11 sec)

MySQL [test_db_for_index]> explain select * from user where name='Prof. Osborne Waelchi I' and sex in(0, 1); +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user | range | sex_name | sex_name | 768 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' and sex in(0, 1); +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (0.01 sec)
Copy the code

Partial use of multi-column indexes

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 1 rowin set (0.01 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I'and age=60; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.03 sec)

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I'and age=60;; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (3.03 sec)

ERROR: No query specified

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set(3.03 SEC) MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age |  A | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE  | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ 4 rowsin set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I'; +----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+ |  id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+----------------+---------+-------+------+-----------------------+ |  1 | SIMPLE | user | ref | name_age_email | name_age_email | 767 | const | 1 | Using index condition | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1  rowin set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I'; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> select *  from user where name='Prof. Osborne Waelchi I'and age=60;; +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | +----+-------------------------+-----+-----+-----------------------+---------------------------------------------------- -------+-------------+----------------+ 1 rowin set (0.01 sec)

ERROR: No query specified

MySQL [test_db_for_index]> desc select *  from user where name='Prof. Osborne Waelchi I'and age=60; +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-------------------- ---+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-------------------- ---+ | 1 | SIMPLE | user | ref | name_age_email | name_age_email | 768 | const,const | 1 | Using index condition | +----+-------------+-------+------+----------------+----------------+---------+-------------+------+-------------------- ---+ 1 rowin set (0.04 sec)

Copy the code

Partial use of single index

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 1 rowin set (0.02 sec)


MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.02 sec)


MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%'; +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ | id | name | sex | age | email | address | company | city | +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 798465 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1167101 | Prof. Osborne Weissnat V | 0 | 74 | [email protected] | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 1660173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2160173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2660173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3160173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3528809 | Prof. Osborne Weissnat V | 0 | 74 | [email protected] | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 4021968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4521968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 5021968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ 11 rowsin set(3.30 SEC) MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 2 rowsin set (0.02 sec)

MySQL [test_db_for_index]> desc select *  from user where name like 'Prof. Osborne W%';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE  | user | range | name | name | 767 | NULL | 11 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 rowin set (0.02 sec)

MySQL [test_db_for_index]> select *  from user where name like 'Prof. Osborne W%'; +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ | id | name | sex | age | email | address | company | city | +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 798465 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1660173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2160173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 2660173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 3160173 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4021968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 4521968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 5021968 | Prof. Osborne Weimann I | 0 | 79 | [email protected] | 64416 Lia Mills Apt. 784 Kleinburgh, MI 09030-1298 | Green PLC | Hesselhaven | | 1167101 | Prof. Osborne Weissnat V | 0 | 74 | [email protected] | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | | 3528809 | Prof. Osborne Weissnat V | 0 | 74 | [email protected] | 77818 Rohan Throughway Koelpinmouth, VA 66568-0775 | Stark-Anderson | Oberbrunnershire | +---------+--------------------------+-----+-----+--------------------------+------------------------------------------- ----------------+----------------+------------------+ 11 rowsin set (0.04 sec)
Copy the code

The equivalent or of the same field, the new optimizer will optimize to in, can use the index

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 2 rowsin set (0.01 sec)


MySQL [test_db_for_index]> desc select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley';
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE  | user | range | name | name | 767 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 rowin set (0.02 sec)

MySQL [test_db_for_index]> desc select * from user where name in('Prof. Osborne Waelchi I'.'Zaria Quigley');
+----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ | 1 | SIMPLE  | user | range | name | name | 767 | NULL | 2 | Using index condition | +----+-------------+-------+-------+---------------+------+---------+------+------+-----------------------+ 1 rowin set (0.01 sec)


MySQL [test_db_for_index]> select * from user where name in('Prof. Osborne Waelchi I'.'Zaria Quigley'); +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 4 | Zaria Quigley | 0 | 41 | [email protected] | 799 Barney Cove Princessland, VA 34382 | Farrell-Hartmann | DuBuqueport | +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ 2 rowsin set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='Prof. Osborne Waelchi I' or name='Zaria Quigley'; +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ | id | name | sex | age | email | address | company | city | +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ | 1 | Prof. Osborne Waelchi I | 0 | 60 | [email protected] | 35712 Quigley Mountains North Alysonville, CO 53682-2718 | McGlynn Ltd | Port Maziebury | | 4 | Zaria Quigley | 0 | 41 | [email protected] | 799 Barney Cove Princessland, VA 34382 | Farrell-Hartmann | DuBuqueport | +----+-------------------------+-----+-----+------------------------+--------------------------------------------------- --------+------------------+----------------+ 2 rowsin set (0.01 sec)

Copy the code

The optimization of OR for different fields, using two single-column index combinations, is the same with union and does not optimize.

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | name | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | email | 1 | email | A | 1623524 | NULL | NULL  | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 3 rowsin set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' or email='d'; +----+-------------+-------+-------------+---------------+------------+---------+------+------+------------------------- -------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------------+---------------+------------+---------+------+------+------------------------- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user | index_merge | name, email | name, email | 767767 | NULL | 2 | Using union(name,email); Usingwhere| +----+-------------+-------+-------------+---------------+------------+---------+------+------+------------------------- -------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> select * from user where name='1' or email='d';
Empty set (0.02 sec)


MySQL [test_db_for_index]> select * from user where name='1' union select * from user where email='d';
Empty set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where name='1' union select * from user where email='d';
+----+--------------+------------+------+---------------+-------+---------+-------+------+-----------------------+
| id | select_type  | table      | type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1  PRIMARY | user | ref | name | name | 767 | const | 1 | Using index condition | | 2 | UNION | user | ref | email | email | 767 | const | 1 | Using index condition | | NULL | UNION RESULT | < 2 > union1, | | NULL ALL | NULL | NULL | NULL | NULL  | Using temporary | + - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 3 rowsin set (0.01 sec)
Copy the code

Minimize scope conditions. (>, <! =, not in, not between, not between, not between

The ## sex field has only two values: 0 and 1MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | | user | 1 | sex_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 3 rowsin set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user wheresex! =1 and name='payton'; +----+-------------+-------+------+---------------+------+---------+------+---------+-------------+ | id | select_type |  table |type | possible_keys | key  | key_len | ref  | rows    | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | ALL  | sex_name      | NULL | NULL    | NULL | 4870574 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+---------+-------------+
1 row in set (0.01 sec)

MySQL [test_db_for_index]> select * from user wheresex! =1 and name='payton';
Empty set (3.18 sec)

MySQL [test_db_for_index]> desc select * from user where sex=0 and name='payton';
+----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+----------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | user | ref | sex_name | sex_name | 768 | const,const | 1 | Using index condition | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + 1 rowin set (0.02 sec)

MySQL [test_db_for_index]> select * from user where sex=0 and name='payton';
Empty set (0.02 sec)

Copy the code

In a federated index, the field following the range search field can no longer determine the slice width, but can only be used for filtering. Optimization with IN

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name | 1 | age | A | 136 | NULL | NULL | | BTREE | | | | user | 1 | age_name | 2 | name | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 3 rowsin set (0.01 sec)

MySQL [test_db_for_index]> desc select * from user where age >= 10 and age <= 15 and name='payton'; +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ | id |  select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user | range | age_name | age_name | 768 | NULL | 626654 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+--------+-----------------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age >= 10 and age <= 15 and name='payton';
Empty set (0.09 sec)

MySQL [test_db_for_index]> desc select * from user where age inAnd name =,11,12,13,14,15 (10)'payton';
+----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+
| id | select_type | table | type| possible_keys | key | key_len | ref | rows | Extra | + - + -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | user | range | age_name | age_name | 768 | NULL | 6 | Using index condition | +----+-------------+-------+-------+---------------+----------+---------+------+------+-----------------------+ 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> select * from user where age inAnd name =,11,12,13,14,15 (10)'payton';
Empty set (0.02 sec)

Copy the code

Overwrite indexes are better

Because the InnoDB cache pool here is too large, with nearly 900 MEgabytes of memory. Fully capable of loading all leaf and non-leaf nodes of secondary indexes into memory. The same is true for clustered indexes. Since there is no IO gap, the difference is how many rows are on a page. So the difference here is not that great. However, the gap would be larger if not all of the clustered index non-leaf nodes were in memory.

MySQL [test_db_for_index]> desc select count(*) from user;
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set(0.02sec) MySQL [test_db_for_index]> desc SELECT count(*) from user; +----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+ | id | select_type | table |type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 4870574 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
1 row in set(0.01sec) MySQL [test_db_for_index]> select count(*) from user; +----------+ | count(*) | +----------+ | 5037343 | +----------+ 1 rowin set(1.69 SEC) MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 2 rowsin set(0.01sec) MySQL [test_db_for_index]> desc SELECT count(*) from user; +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ | 1 | SIMPLE | user  | index | NULL | sex | 1 | NULL | 4870574 | Using index | +----+-------------+-------+-------+---------------+------+---------+------+---------+-------------+ 1 rowin set(0.01sec) MySQL [test_db_for_index]> select count(*) from user; +----------+ | count(*) | +----------+ | 5037343 | +----------+ 1 rowin set (0.67 sec)

Copy the code

InnoDB cache pool size, impact on queries

The environment of this experiment is different from the others.

Cache pooling is implemented by the storage engine. In MySQL InnoDB, the size of the cache pool can be defined with the innodb_buffer_pool_size parameter.

The cache pool is maintained through the LRU policy. The database is considered to be performing best when the data in the database can be stored entirely in the cache pool. Except for synchronous or asynchronous disk writes, all operations can be done in memory.

Here is the 18GB data, and how TPS changes as the cache pool grows. 18 gigabytes of data, it’s a little bit bigger to store in memory, because there’s other overhead.

Because of the cache pool, some hot data can automatically lie in the cache pool, so, fast.

Random and sequential reads and writes between disks

Sequential reading means that pages on disk are read sequentially. Random reads are accesses to pages that are not contiguous and require the disk’s head to move constantly.

Notice that by order, we mean that the inside of the block is sequential, and the blocks can be discontinuous from one to the other. Because it is difficult to guarantee the application of a continuous space of tens of gigabytes.

In MySQL InnoDB, pages are managed by extents. Each time storage is requested, a contiguous extents, including 64 pages, are requested. Therefore, the 64 pages are guaranteed to be contiguous, but extents and ranges are not.

Although the physical structure of a SOLID-state drive is different from that of a disk, it complies with the above principles. Sequential reading is still faster than random reading.

The system caches all the leaf nodes indexed by Random_digit into memory

MariaDB [big_tables]> show index from custom; +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | custom | 0 | PRIMARY | 1 | id | A | 1240315 | NULL | NULL | | BTREE | | | | custom | 1 | email | 1 | email | A | 1240315 | 255 | NULL | YES | BTREE | | | | custom | 1 | name | 1 | name | A | 1240315 | 255 | NULL | YES | BTREE | | | | custom | 1 | random_digit | 1 | random_digit | A | 20 | NULL | NULL | YES | BTREE | | | +--------+------------+--------------+--------------+--------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ 4 rowsin set (0.00 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in setMariaDB [big_tables]> select count(*) from custom; +----------+ | count(*) | +----------+ | 1158255 | +----------+ 1 rowin set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set (0.23 sec)

MariaDB [big_tables]> select count(*) from custom;
+----------+
| count(*) |
+----------+
|  1158255 |
+----------+
1 row in set(0.25sec) MariaDB [big_tables]> select count(*) from custom; +----------+ | count(*) | +----------+ | 1158255 | +----------+ 1 rowin set (0.22 sec)

MariaDB [big_tables]> desc select count(*) from custom;
+------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+
| id   | select_type | table  | type| possible_keys | key | key_len | ref | rows | Extra | + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + + -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 1 SIMPLE | custom | index | NULL | random_digit | 7 | NULL | 1240315 | Using index | +------+-------------+--------+-------+---------------+--------------+---------+------+---------+-------------+ 1 rowin set (0.00 sec)


Copy the code

Optimizers are awesome

MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A  | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE |  | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ 7 rowsin set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + 1 rowin set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 3 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + MySQL [test_db_for_index]> desc select age,name,email from userwhere age>80 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ | 1 | SIMPLE | user | range | age_name_email | age_name_email | 1 | NULL | 1 | Usingwhere; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ 1 rowin set (0.02 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>580 order by name limit1; +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------- ---------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------- ---------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables | +----+-------------+-------+------+---------------+------+---------+------+------+-------------------------------------- ---------------+ 1 rowin set (0.02 sec)
Copy the code

Cardinality and field selectivity

MySQL [test_db_for_index]> show index from user; +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | sex | 1 | sex | A | 2 | NULL | NULL | | BTREE | | | +-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+----- -------+---------+---------------+ 2 rowsin set(0.01sec) MySQL [test_db_for_index]> select count(distinct sex)/count(*) from user; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | count (distinct sex)/count (*) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 0.0000# is actually not 0, not close enough
+------------------------------+
1 row in set(1.88 SEC) MySQL [test_db_for_index]> select count(distinct name)/count(*) from user; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | count (distinct name)/count (*) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 0.1592 +-------------------------------+ 1 rowin set(6.13 SEC) MySQL [test_db_for_index]> select count(distinct left(email,5))/count(*) from user; +----------------------------------------+ | count(distinct left(email,5))/count(*) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 0.0049 + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - + 1 rowin set(4.12 SEC) MySQL [test_db_for_index]> select count(distinct left(email,15))/count(*) from user; +-----------------------------------------+ | count(distinct left(email,15))/count(*) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 0.1545# This saves the most space. Note, however, that after truncation, the overwrite index cannot be used and must be returned to the clustered index to get the full contents of the current column
+-----------------------------------------+
1 row in set(5.74 SEC) MySQL [test_db_for_index]> select count(distinct email)/count(*) from user; + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | count (distinct email)/count (*) | + -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- + | | 0.1586 +--------------------------------+ 1 rowin set (5.66 sec)

Copy the code

Candidate A or candidate B

MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A  | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE |  | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ 7 rowsin set (0.01 sec)

MySQL [test_db_for_index]> select age,name,email from user where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | [email protected] |
+-----+--------------------+-----------------+
1 row in set(0.01sec) MySQL [test_db_for_index]> select age,name,email from user force indexwhere age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | [email protected] |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

MySQL [test_db_for_index]> select age,name,email from user force index(name_age_email) where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | [email protected] |
+-----+--------------------+-----------------+
1 row in setMySQL [test_db_for_index]> select age,name,email from user force index(name_age_email)where age > 18 order by name limit100000, 10; +-----+--------------------+-------------------------------+ | age | name | email | +-----+--------------------+-------------------------------+ | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20  | Alexandrea Deckow | [email protected] | | 47 | Alexandrea Denesik | [email protected] | | 47 | Alexandrea Denesik | [email protected] | | 28 | Alexandrea Dibbert | [email protected] | | 28 | Alexandrea Dibbert | [email protected] | +-----+--------------------+-------------------------------+ 10 rowsin setMySQL [test_db_for_index]> select age,name,email from user force index(age_name_email)where age > 18 order by name limit100000, 10; +-----+--------------------+-------------------------------+ | age | name | email | +-----+--------------------+-------------------------------+ | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20 | Alexandrea Deckow | [email protected] | | 20  | Alexandrea Deckow | [email protected] | | 47 | Alexandrea Denesik | [email protected] | | 47 | Alexandrea Denesik | [email protected] | | 28 | Alexandrea Dibbert | [email protected] | | 28 | Alexandrea Dibbert | [email protected] | +-----+--------------------+-------------------------------+ 10 rowsin set(18.65 SEC) -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- - MySQL/test_db_for_index > select the age, name, email from the user force index(name_age_email)where age > 89 order by name limit 1;
Empty setMySQL [test_db_for_index]> select age,name,email from user force index(age_name_email)where age > 89 order by name limit 1;
Empty setMySQL [test_db_for_index]> select age,name,email from user force index(name_age_email)where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | [email protected] |
+-----+--------------------+-----------------+
1 row in setMySQL [test_db_for_index]> select age,name,email from user force index(age_name_email)where age > 18 order by name limit 1;
+-----+--------------------+-----------------+
| age | name               | email           |
+-----+--------------------+-----------------+
|  60 | Aaliyah Altenwerth | [email protected] |
+-----+--------------------+-----------------+
1 row in set (3.11 sec)

## To choose candidate A or candidate B, we think of the process, secondly, the optimizer has already decided for us. (If there is both candidate A and candidate B)MySQL [test_db_for_index]> show index from user; +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 4870574 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 1 | age | A | 29698 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 2 | name | A  | 2435287 | NULL | NULL | | BTREE | | | | user | 1 | age_name_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE |  | | | user | 1 | name_age_email | 1 | name | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 2 | age | A | 1623524 | NULL | NULL | | BTREE | | | | user | 1 | name_age_email | 3 | email | A | 2435287 | NULL | NULL | | BTREE | | | +-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------ +------------+---------+---------------+ 7 rowsin set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>1 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + 1 rowin set (0.01 sec)

MySQL [test_db_for_index]> desc select age,name,email from user where age>50 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 2 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + 1 rowin set (0.01 sec)


MySQL [test_db_for_index]> desc select age,name,email from user where age>70 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + | 1 | SIMPLE | user | index | age_name_email | name_age_email | 1535 | NULL | 3 | Usingwhere; Using index | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- + MySQL [test_db_for_index]> desc select age,name,email from userwhere age>80 order by name limit1; +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ | id | select_type | table |type| possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ | 1 | SIMPLE | user | range | age_name_email | age_name_email | 1 | NULL | 1 | Usingwhere; Using index; Using filesort | +----+-------------+-------+-------+----------------+----------------+---------+------+------+-------------------------- ----------------+ 1 rowin set (0.02 sec)

Copy the code

The resources

• dev.mysql.com/doc/
• MySQL 5.6 Reference Manual
• MySQL internal implementation mechanism documentation

book

• High Performance MySQL
• Database Index Design and Optimization
• MySQL Tech Insider: InnoDB Storage Engine
• MySQL Technology Insider: SQL Programming

blog

• Tencent CDB use suggestions
• Mysql > create table and index
• Mysql overview
• MySQL InnoDB index principle
• “Shallow in shallow out” MySQL and InnoDB
• Mysql InnoDB index
• blog.jcole.us/innodb/
• Github.com/xingshaoche…
• searchdatabase.techtarget.com.cn/7-19995/
• Stackoverflow.com/questions/3…
• www.cnblogs.com/cchust/p/45…
• blog.jobbole.com/109695/
• Cloud.tencent.com/info/39f3e3…
• www.zhihu.com/question/24…
• Blog.csdn.net/dove_knowle…
• www.cnblogs.com/gulibao/p/5…
• Blog.codinglabs.org/articles/th…
• www.cnblogs.com/vincently/p…
• www.cnblogs.com/vincently/p…
• www.cnblogs.com/lwhkdash/p/…
• Dev.mysql.com/doc/refman/…
• My.oschina.net/u/1859679/b…
• www.admin10000.com/document/53…
• Blog.csdn.net/monkey_d_fe…

Test data set

• Official test data: Employees Sample Database
• Other test databases
• Generate your own test data
  • faker