“This is the second day of my participation in the First Challenge 2022, for more details: First Challenge 2022”.

Hello everyone, I am Melo, a sophomore backstage trainee, and the balance of seven days of Spring Festival is insufficient… Get on the shore.

Column to review

  • Principles of indexing && design principles

Welcome to this column: Advanced MySQL

This quick reference

In our last article, we talked about the principles of indexing && design principles, and learned how to use indexes. Emm? How to analyze SQL statements and optimize them? This article will take you to break ta from the following points:

  • Analyze SQL in detail
  • Several scenarios of index failure
    • .
  • Several scenarios for SQL optimization
    • Mass insertion
    • order by
    • group by
    • Limit the paging
    • Insert operations
    • Nested query
    • Or conditions

Note that the MySQL version of this article is 5.6.43, some of the conclusions may not be applicable to other versions!!

  • The length of this article is long, the full text of nearly 8500 words, can be collected down slowly chew, nothing out to read.

It is suggested that you search the helpful parts through the sidebar column, where the emoji prefix is the key part. Melo will continue to improve this article and MySQL column if it is helpful to you.

Now that we’ve mastered the basics of indexing and how to use it, it’s time to optimize SQL! Wait, what SQL are we going to optimize again, pants are off, the result has no object can…..

Don’t worry, this article is linked to MySQL advanced, of course, MySQL is very advanced, provides us with several methods to find SQL for us, and analyze SQL. This article, we first focus on how to analyze, specific how to find SQL, the subsequent actual combat, we will talk about it in detail.

๐ŸŽใ€ไธ€, explainใ€‘ analyze SQL

Explain contains the following fields (which may vary from version to version) :

field meaning
id The sequence number of a SELECT query, which is a set of numbers that indicate the order in which the select clause or operation table is executed in the query.
select_type Indicates the type of the SELECT. Common values include SIMPLE (a SIMPLE table that does not use table joins or subqueries), PRIMARY (the PRIMARY query that is the outer query), UNION (the second or subsequent query statement in the UNION), and SUBQUERY (the first SELECT in the SUBQUERY)
table Outputs a table of result sets
partitions For non-partitioned tables, the value is NULL. If partitioned tables are queried, partitions that are matched by partitioned tables are displayed.
type Represents the join type of the table, Performance from good to bad connection type (system – > const — — — — — > eq_ref — — — — — – > ref — — — — — — — > ref_or_null — — — — > index_merge – > index_subquery —–> range —–> index ——> all )
possible_keys Represents the query,May be usedThe index of the
key Represents the query,The actual useThe index of the
key_len The length of the index field that can be used to distinguish between long and short indexes
rows Number of scanned rows
filtered The percentage of eligible records in the table
extra Description and implementation

Don’t worry. Let’s deepen our memory through actual cases

id

The ID field is the sequence number of the SELECT query and is a set of numbers representing the order in which the select clause or operation table is executed in the query. There are three id cases:

  1. This is a single table query with only one ID

  1. Same id, top to bottom

  1. If the ID is different, a larger ID indicates a higher priority

Here is the nested subquery, the innermost subquery, which is naturally executed first

In short:

  • The larger the ID value is, the higher the priority is.
  • If the ID value is the same, from top to bottom;

select_type

SELECT_TYPE meaning
SIMPLE A simple SELECT query that does not contain subqueries or unions
PRIMARY If the query contains any complex subqueries, the outermost query is marked with this identifier
SUBQUERY Subqueries are included in SELECT or WHERE lists
DERIVED inSubqueries contained in the FROM list, and labeled as DERIVED, MYSQL recursively executes these subqueries, putting the results in temporary tables
UNION If the second SELECT occurs after the UNION, mark it as UNION; If UNION is included in the subquery of the FROM clause, the outer SELECT will be marked: DERIVED
UNION RESULT SELECT to get the result from the UNION table

PRIMARY,SUBQUERY

DERIVED(Need temporary tables, naturally less efficient than above)

type

TYPE meaning
NULL MySQL does not access any tables, indexes, and returns results directly
system The table has only one row of records (equal to the system table), which is a special case of const type and generally does not occur
const Said byThe indexFound once, const is often usedPrimary key or unique index (both are essentially unique indexes). Because it only matches one row, it’s fast. By placing the primary key in the WHERE list, MySQL can convert the query to a constant. Const compares all parts of a “primary key” or “unique” index with a constant value
eq_ref It’s similar to ref, except that it usesThe only index, using the primary keyAssociated query, only one record is displayed in associated query. Common for primary key or unique index scans
ref Non-unique indexScan, returning all rows that match a single value. Essentially an index access that returns all rows (multiple rows) that match a single value
range Retrieves only the rows that are given a return, using oneThe indexTo select rows. Where between, <, >, in, etc.
index The difference between index and ALL is that the index type is onlyTraverses the index tree“Is usually faster than ALL, which iterates through the data file.
all The entire table is traversed to find matching rows

The resulting value, from best to worst, is:

NULL > system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL


system > const > eq_ref > ref > range > index > ALL
Copy the code

โ€‹

Generally, it must be at least range level and preferably ref level.

const

Unique index, non-associated query

Eq_ref, ref

Eq_ref = const eq_ref = const eq_ref = const Eq_ref is a non-unique index

The index, all

Index is read from the index tree, while ALL is read from the hard disk.If you do not walk the index, the entire table will be traversed

Possible_keys, key

Possible_keys: Displays one or more possible indexes that can be applied to the table.

Key: actual used index. If NULL, no index is used. (There may be no index, need to analyze)

Key_len: Indicates the number of bytes used in the index. The shorter the length, the better, without sacrificing accuracy.

  • Single-column index, then the entire index length needs to be taken into account;
  • Multi-column indexes, not all columns available, need to calculate the columns actually used in the query.

ref

Shows which column of the index is used, if possible, as a constant.

  • Display const when using constant equivalents
  • In an associated query, the associated fields of the corresponding associated table are displayed
  • If the query condition uses expressions, functions, or if the condition column is implicitly converted, it may appear as FUNc
  • In other cases, null
  1. Id is an index, and id=1, a constant, so ref = const
  2. User_id is not an index and ref is null

T1. id is an index, and the = sign is not a constant. Therefore, display T1. id, that is, display the associated fields of the corresponding associated table

rows

The number of lines scanned is generally as small as possible

  • With the index rows is 1, whether unique or non-unique
  • Other cases are typically full table scans where rows are equal to the number of rows in the table.

filtered

The percentage of eligible records in the table.

extra

Additional execution plan information, shown in this column, needs to optimize the first two to using index.

EXTRA meaning
using filesort Note mysql uses one for dataexternalIndex sort, instead of reading the table in index order, representsUnable to use indexThe completed sort operation, called “file sort”, is inefficient.
using temporary Using theA temporary tableSave the intermediate result, MySQL inSort the query resultsUse temporary tables when. Order by and group by; Low efficiency
using index Indicates that the corresponding SELECT operation is usedCover index, filter out unwanted results directly from the index, no need to return to the table, efficiency is good.
using index condition Index push down!!The lookup uses an index, but is requiredBack to the table queryData, in this case because the index column does not fully contain the query column

What is the meaning of index push-down in using index condition

using where

Different versions seem to be different

5.7: indicates that MySQL reads the records from the data table (storage engine) and returns them to the server layer of MySQL. Then, the server layer filters out the records that do not meet the requirements. That is, the storage engine cannot directly filter out the records. To put it simply, when queryingWhere is not an index.

Now, we know how to use explain to analyze SQL statements, naturally can analyze the performance of our SQL statements, not too early to have someone summed up a few need to optimize the scenario -> index failure

๐ŸŽ several scenarios of index failure

0. SQL

create table `tb_seller` (
	`sellerid` varchar (100),
	`name` varchar (100),
	`nickname` varchar (50),
	`password` varchar (60),
	`status` varchar (1),
	`address` varchar (100),
	`createtime` datetime,
    primary key(`sellerid`)
)engine=innodb default charset=utf8mb4; 

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('alibaba'.Alibaba.Ali Store.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('baidu'.Baidu Technology Co., LTD..'Baidu Store'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('huawei'.Huawei Technologies Co., LTD..Huawei Store.'e10adc3949ba59abbe56e057f20f883e'.'0'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itcast'.'Transwise Podcast Education Technology Limited'.'Pass the Wisdom Podcast'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('itheima'.'Dark Horse Programmer'.'Dark Horse Programmer'.'e10adc3949ba59abbe56e057f20f883e'.'0'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('luoji'.'Logitech Technologies Limited'.Logitech Shop.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('oppo'.'OPPO Technology Limited '.'OPPO official Flagship Store '.'e10adc3949ba59abbe56e057f20f883e'.'0'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('ourpalm'.'Zhangqu Technology Co., LTD.'.'Palm Fun Shop'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('qiandu'.'Thousand Degree Technology'.'Thousand Degree Store'.'e10adc3949ba59abbe56e057f20f883e'.'2'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('sina'.'Sina Technology Limited'.'Sina official Flagship Store'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('xiaomi'.'Xiaomi Technology'.'Xiaomi Official Flagship Store'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Xi 'an'.'the 2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`) values('yijia'.'ๅฎœๅฎถๅฎถๅฑ…'.'Ikea Flagship Store'.'e10adc3949ba59abbe56e057f20f883e'.'1'.'Beijing'.'the 2088-01-01 12:00:00');

Create a federated index
create index idx_seller_name_sta_addr on tb_seller(name,status,address);
Copy the code

1. The left-most prefix is not met

The left-most prefix can be thought of as a process of climbing a staircase. If we have a compound index: name, status, address, then the staircase is in descending order: Name, status, address, the left-most prefix, which requires that we do not jump stairs, otherwise we will invalidate our index:

  1. Press the stairs from low to high, no jump – at this time in line with the left prefix principle, the index will not fail

  1. There is a jump
  • Direct the first layer name does not walk, of course, all invalid

  • Go to the first layer, but then go to the third layer, only the name before the jump will not fail (here only the name succeeds).

  • Also, this order is not determined by the order we order in where, for example:
    • Where id = 1 and status=’1′ and address=’ 1′
    • Where status=’1′ and name=’ 1′ and address=’ 1′

Although the two where fields are in different order, the second one looks like a step over the top, but the effect is the same

MySQL has a query Optimizer. The query Optimizer optimizes SQL to select the optimal query plan to execute.

2. After the range query

Index field after range query will be invalid!! But the index field itself used for the range query is still valid, as shown in Status.

  • In the figure, address fails, as can be seen by comparing the length.

3. Perform the operation on the index field

Performing operations on index fields, using functions, and so on will invalidate the index.

4. String without ‘ ‘

The index field is a string. Because the string is not quoted in the query, the MySQL query optimizer will automatically convert the string and invalidate the index.

5. Avoid select *

harm

  • Consumes more CPU and IO to network bandwidth resources
  • Reduces the impact of table structure changes
  • Unable to use overwrite index

๐ŸŽˆ overwrite index

Use overwrite indexes (index columns that contain query columns entirely) and minimize select *

When the query column contains non-index items, although we can still use the index, in order to obtain non-index item fields, we need to query the data back to the table, which will be relatively inefficient.

6. Or conditions for splitting

A condition split with or. If the column in the condition before or has an index and the column behind it has no index, the index involved will not be used.

Select * from createtime; select * from createtime;

  • Select * from (select * from (select * from (select * from (select * from (select * from (select * from))));
 explain select * from tb_seller where name='Dark Horse Programmer' or createtime = 'the 2088-01-01 12:00:00'\G; 
Copy the code

7. Like fuzzy query starting with %

You can contact the dictionary tree Trie for matching.

  • For example, if you want to find ‘ABC’, if it is % BC, you cannot find the root of the index tree

  • And if it’s ab%, you can still use the first two.

  • % ends with index. % ends with index. % ends with index.

Workaround: Use overwrite indexes

When you really want to use % on both sides of a fuzzy query, you can only really use an index if both the condition field (name in the example) and the data field you want to query (Name & Status & Address in the example) are in the index column.

For more information on overwriting indexes, see indexing Principles, Design Principles

MySQL thinks full tables are faster

Here, due to the particularity of the data, the proportion of ‘Beijing’ is very high, which is not as good as full table scan

8.1 Is NULL and is not NULL

It’s essentially the same thing

If a full table scan is fast, the MySQL database automatically checks whether a full table scan is performed. If most of the index column data in the table is non-null, the index is moved when is not NULL is used, and the index is not moved when IS NULL is used (faster than full table scan). And vice versa.

If there are a large number of IS null’s in the table, the whole table is scanned. If there are few IS null’s in the table, the index is scanned.

8.2 In and not in

To facilitate testing, we built a separate status index and observed the table data. Status has few 2’s and many 1’s.

So in(‘1’) would go through the entire table, and in(‘2’) would go through the index without using it

conclusion

When we build indexes, we generally do not consider adding indexes to fields with uniform and repetitive data distribution, because MySQL will think that the full table is faster, and will scan the full table instead of the index, resulting in the failure of our indexes.

9.! = or < >

Using inequalities can also invalidate indexes

The problem sets

After a few index failure scenarios, here are our specific application scenarios. In the following specific situations, we need to adopt different SQL optimization methods, or use indexes, or use external conditions

๐ŸŽŽ [3. Scenario Optimization] 1. Insert a large number of data

Environment to prepare

CREATE TABLE `tb_user_2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(45) NOT NULL,
  `password` varchar(96) NOT NULL,
  `name` varchar(45) NOT NULL,
  `birthday` datetime DEFAULT NULL,
  `sex` char(1) DEFAULT NULL,
  `email` varchar(45) DEFAULT NULL,
  `phone` varchar(45) DEFAULT NULL,
  `qq` varchar(32) DEFAULT NULL,
  `status` varchar(32) NOT NULL COMMENT 'User status',
  `create_time` datetime NOT NULL,
  `update_time` datetime DEFAULT NULL.PRIMARY KEY (`id`),
  UNIQUE KEY `unique_user_username` (`username`)  -- Uniqueness constraint
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ;
Copy the code

The load command

Proper Settings can improve the efficiency of imports.For innoDB-type tables, there are several ways to improve import efficiency:

1) Sequential insertion of primary keys

Since innoDB-type tables are stored in the order of primary keys, sorting imported data in the order of primary keys can effectively improve the efficiency of data import. If an InnoDB table does not have a primary key, the system automatically creates an internal column as the primary key by default, so if you can create a primary key for the table, you can use this to improve the efficiency of data import.

Script file introduction:

Sql1. log —-> Primary key order sql2.log —-> Primary key order

Order data by inserting primary keys:Primary key out of order:

A permission issue has occurred

Set global local_infile=on;

But there was another problem:

In fact, once we open it, we need toExit and reconnect, it can operate normally when connected again

  • If that still doesn’t work, you can connect to it like this:
mysql --local_infile=1 -u root -ppassword
Copy the code

2) Turn off the uniqueness check

inBefore importing dataRun SET UNIQUE_CHECKS=0 to disable the uniqueness checkAfter importRun SET UNIQUE_CHECKS=1 to restore the uniqueness check to improve import efficiency.

๐ŸŽช2

Environment to prepare

CREATE TABLE `emp` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `age` int(3) NOT NULL,
  `salary` int(11) DEFAULT NULL.PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4;

insert into `emp` (`id`, `name`, `age`, `salary`) values('1'.'Tom'.'25'.'2300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('2'.'Jerry'.'30'.'3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('3'.'Luci'.'25'.'2800');
insert into `emp` (`id`, `name`, `age`, `salary`) values('4'.'Jay'.'36'.'3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('5'.'Tom2'.'21'.'2200');
insert into `emp` (`id`, `name`, `age`, `salary`) values('6'.'Jerry2'.'and'.'3300');
insert into `emp` (`id`, `name`, `age`, `salary`) values('7'.'Luci2'.'26'.'2700');
insert into `emp` (`id`, `name`, `age`, `salary`) values('8'.'Jay2'.'33'.'3500');
insert into `emp` (`id`, `name`, `age`, `salary`) values('9'.'Tom3'.'23'.'2400');
insert into `emp` (`id`, `name`, `age`, `salary`) values('10'.'Jerry3'.'32'.'3100');
insert into `emp` (`id`, `name`, `age`, `salary`) values('11'.'Luci3'.'26'.'2900');
insert into `emp` (`id`, `name`, `age`, `salary`) values('12'.'Jay3'.'37'.'4500');

create index idx_emp_age_salary on emp(age,salary);
Copy the code

Two kinds of sorting

using index

Can directly complete the query in the index column, without back table, about back table query, you can refer toThis articleIn this case, guarantees are neededField to be queriedAre allThe index field, is using index

In many cases, we do not have a way to optimize using filesort to using index. Instead, we try to optimize using filesort from the perspective of external conditions.

๐ŸŽ‘ using filesort

When will it appear:

  1. The field of order by is not an index
  2. The ORDER BY field is an index field, but an overwrite index is not used in SELECT
  3. Order by contains both ASC ascending sort and DESC descending sort

  1. Order by uses a composite index, but does not maintain the order of the fields in the composite index (i.e. violates the left-most prefix principle).

Select ID,age,salary from EMP order by salary,age;

โ€‹

Why is that? For example, if we create a compound index (name, status, address), the index will be stored by this field, similar to the table in the figure:

Compound index tree (only index columns and primary keys are used to back the table), and is sorted by name, then status, and so on

name status address Id (primary key)
Millet 1 0 1 1
Millet 2 1 1 2

So if we do not order by the order of the index, we will not be able to use the order of the index tree.

๐ŸŽ Filesort optimization

Two scanning algorithms

MySQL > select * from Filesort; MySQL > select * from Filesort;

select * from emp where age=1 order bySalary;Copy the code

1) Double scan algorithm: before MySQL4.1, use this way to sort.

Firstly, filter corresponding SALARY that meets age=1 according to where condition, extract the sort field SALARY and the corresponding row pointer information (used to return the table), then sort in sort area sort buffer, if the sort buffer is not enough, The sort results are stored in the temporary table.

(2) After sorting, read all columns from the table according to the row pointer. This operation may cause a large number of random I/O operations, which is what we need to improve.

This is called a double scan. In the first scan, we just get the sorted fields and sort them in sort Buffer. The second scan, then go back to the table to read all the fields, and finally return.

How do you optimize? Why split it up twice? Is there a possibility that there isn’t enough room? So if we had enough space, if we could trade space for time, could we open up a new way, one scan

2) Single-scan algorithm: take out all fields that meet the conditions at one time, and then output the result set directly after sorting in sort area sort buffer. The memory cost of sorting is large, but the sorting efficiency is higher than that of the two-scan algorithm, which is typical of the idea of space for time.

Which algorithm do you use? Select * from max_length_FOR_sort_data; select * from max_length_FOR_sort_data; select * from max_length_sort_data; Otherwise, use the two-scan algorithm.

Optimization scheme

โ‘  Increase the formermax_length_for_sort_data

May be appropriatelymax_length_for_sort_dataSystem variable, to increase the size of the sorting area, improve sorting efficiency, this is typicalSpace for timeThoughts.โ‘ก Reduce the latterThe total size of the fields retrieved by the Query statement

If memory is not enough, we can reduce the number of fields in the query and avoid select *

(3) Increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size: increase sort_buffer_size;

Unfortunately, MySQL cannot see which algorithm it uses. If you increase the value of the max_Length_for_sort_data variable, disk usage goes up, CPU usage goes down, and the Sort_merge_passes status variable starts to go up much faster than before the change, perhaps because MySQL forces many sorts to use a scan algorithm.

The specific actual practice modification process needs to be combined with another tool in MySQL –trace analysis optimizer to analyze the execution plan. We will talk about it in detail when we have a chance later.

I’m going to group by group

Since GROUP BY actually does the same sort operation, and compared with ORDER BY, GROUP BY mainly only has more grouping operations after sorting. Of course, if other aggregate functions are used in grouping, then some calculation of aggregate functions is required. Therefore, indexes can be used in the implementation of GROUP BY as well as ORDER BY.

1. Use indexes

Let’s look at the case without indexes: using temporary; using filesort

Create indexes

create index idx_emp_age_salary onEmp (age, salary);Copy the code

2. Add order by null to forbid sorting

If the query containsgroup byHowever, if the user wants to avoid the consumption of sorting results, he can disable sorting by executing order by NULL. As follows:

3. Need to sort (same as order by optimization)

4. Optimize subqueries

Support for SQL subqueries began after Mysql4.1. The technique uses a SELECT statement to create a single column of query results, which can then be used as a filter in another query. Using subqueries allows you to do many SQL operations at once that would logically require multiple steps, avoid transactions or table locks, and make it easy to write.

However, in some cases, subqueries can be replaced by more efficient joins!

Example of finding all user information with roles:

 explain select * from t_user where id in (select user_id from user_role );
Copy the code

The implementation plan is:After the optimization:

explain select * from t_user u , user_role ur where u.id = ur.user_id;
Copy the code

Join queries are more efficient because MySQL does not need to create temporary tables in memory to perform a logical two-step query.

5. Optimize OR conditions

For a query clause that contains OR, if you want to use an index, each condition column between OR must use the index, and no compound index can be used. If there is no index, you should consider adding an index.

We have an ID primary key index and an age, salary compound index:

One of the columns plus the composition

explain select * from emp where id = 1 or age = 30;
Copy the code

Single column + single column

It’s actually equivalent to range, but this is just an example

Solution: Use union optimization!!

Before optimization

  • Type: index_merge

The optimized

  • Type: one is const and one is ref, both faster than index

6. Use SQL prompts

SQL hints, is an important means to optimize the database, in simple terms, is to add some artificial hints in THE SQL statement to achieve the purpose of optimizing the operation.

use index

Add the use index after the table name in the query statement to provide the MySQL databasereferenceIndex list, MySQL can no longer consider other available indexes.

ingore index

If you simply want MySQL to ignore one or more indexes, you can use ignore Index as a hint.

force index

Force-drop an index, even if MySQL thinks a full table is faster, we can use force to force an index drop.

Use

  • Use is just a reference, it depends on what the MySQL optimizer thinks

โœจ7. Optimize limit paging

MySQL > select * from ‘limit 200000010’; select * from ‘limit 200000010’; select * from ‘limit 200000010’;

For example, we have a statement like select * from tb_item limit 200000010;

The default is to sort by ID.

Optimization idea 1

Sort paging is performed on the index, and then the primary key is associated back to the original table to query the other column contents.

Optimization Idea 2

This scheme applies to tables with self-increasing primary keys and can convert Limit queries into queries at a certain location. (Limitation: primary bonds cannot be faulted)

  • If you want to sort by other fields, this method will not work.

8. Optimize insert operations

Connect once, insert many times

For example, we need to insert three pieces of data:

insert into tb_test values(1.'Tom');
insert into tb_test values(2.'Cat');
insert into tb_test values(3.'Jerry');
Copy the code

At this time, three connections need to be established, and each connection consumes resources. In order to improve the execution efficiency of a single connection, we will take the following steps:

insert into tb_test values(1.'Tom'), (2.'Cat'), (3.'Jerry');
Copy the code

At the same time, it is best to keep the data in order during inserts

๐ŸŽŠ summary

  1. In explain analysis SQL, the more important ones are mainly type, key, ref and extra. We do not need to memorize by rote, and take more statements to explain for comparison, which is more conducive to our auxiliary memory.

  2. Index failure several scenarios, borrow b station hot comment:

Full value match my favorite, most left prefix to obey; The first brother cannot die, the middle brother cannot be broken; Less calculation on index column, all invalid after range; Like 100 write most right, cover index does not write star; Unequal null values and OR, index failure should be used less; VAR quotes are not lost, SQL advanced is not difficult!

  1. Basic principles of optimization: skillfully use indexes to reduce the number of joins.

Finally, it is really not easy to see here. In fact, compared with the previous article, this article is more organized with the idea of dark horse course. I only supplemented and improved some small cases and corrected small mistakes, and the audience may be more inclined to b station video users.

Of course, this is only the initial stage, learn from the video and sort out notes. After the initial stage, it is necessary to optimize SQL in the actual project on this basis. After optimization, it is also necessary to deeply understand the principle of such optimization. Sort_buffer and the corresponding two scanning algorithms are briefly mentioned. How to implement the underlying order BY and how to optimize statements such as the following through the method in this paper:

select * from table order by xxx;
Copy the code

The XXX, regardless of whether the index, according to this query, high probability is using filesort and won’t use index, said unless we go to the limit of xx, the xx also is very small, just can use to the index.

These, just be our further bottom beam pillar, note everybody has, just who arranges a bit better, more some own thinking just.

๐Ÿ’  Next post

In these two articles, we learned about the theory of indexing, how to avoid index failure, analyze and optimize SQL statements, and the optimization of SQL statements. Of course, SQL optimization can also be done through other aspects of MySQL, such as tools, logging, and concurrency parameters. We will learn more about these later.

In the next article, we will first take a look at MySQL ontology and understand the logical architecture and storage engine of MySQL

๐Ÿ–จ Reference Tutorial

  • Dark Horse MySQL Advanced section

Collection = white whoring, praise + attention is true love!! If there is anything wrong with this article, please also point out in the comment area, welcome to add my wechat together communication: Melo__Jun

๐Ÿงฟ friend chain

  • ๐ŸŽ‰ my one year backstage practice career

  • Talk about Java

  • Distributed development practice

  • Redis introduction and actual combat

  • Data structures and algorithms

  • MySQL senior post