For example, id is set to TINYINT(2) UNSIGNED, which means it is unsigned, and can store up to 255 values, of which TINYINT(2) does not work with ZEROFILL and has no meaning in practice. For example, if you insert the number 200, the length of the number is more than two digits, but the number can be inserted successfully, and the result of the query is also 200.

TINYINT(2) with ZEROFILL does not have any meaning, for example, when inserting the number 200, the length is more than two digits, but it can be inserted successfully, and the query result will be the same as 200; when inserting the number 5, the query result will be 5.

TINYINT(2) with ZEROFILL will actually store the number 5 when inserting the number 5, but the query result will be the same as 05.

Optimization:

Optimization:

In general, consider what type and length of field should be used in the table, and then build the table. The table's SQL is taken out and analyzed before the index is built.

Try to build as few single-value indexes as possible (except for unique indexes), and design one or two or three union indexes, so that each union index tries to include the where, order by, and group by fields in the SQL statement, and at the same time make sure that the order of the fields in the union indexes tries to meet the left-most prefix principle of the SQL query. prefix principle of SQL queries.

The index base is the total number of different values of the field in the table, for example, if a table has a total of 1 million rows of records, and there is a gender field with three values for gender I***: male, female, and secret, then the base of the field is 3.

If you build an index on a field with such a small base, you will not be able to quickly bisect the field, because there are only three values in the index tree: male, female, and secret.

If you create an index on such a small base field, because there are only three values in the index tree: male, female, and secret, you can't do a fast binary lookup, and you need to go back to the table.

In general, to build an index, try to use fields that have a large base, so that you can take advantage of the fast binary lookup of the B+ tree.

When there is a conflict between where and order by index design, is it a priority to design an index for where? Or do you prioritize the index design for order by?

Usually it is the priority for where to design the index, because usually it is the first where condition to use the index to quickly filter out the data that meets the conditions, and then to filter out the data for sorting and grouping, and the where condition to quickly filter out the data tends to be not a lot of data.

Specific index optimization, code optimization, and other strategies for slow query SQL found in the actual production runtime, or in the test environment during large-data testing.

Finally, it is the turn of the real world, the second favorite real world.

Writing here have to spit out, this gold three silver four jumping season, the year before leaving, the results of the separation has not been done on the closed village for two whole weeks, oooh, oooh ... ...

The last section of the second mentioned that there will be a very interesting small case, then in the epidemic when the door can not get out of the day, the feeling that this example is even more interesting, let's discuss how to do a variety of social platforms to search for user information.

Social platforms have a small friends like the function of searching for information about friends, such as Xiaoyi skillfully point to the province ... City ... Gender... Age... Height...

Ahem... How could Junior do such a thing, Junior only has code in his heart... hmmm... That's right.

This can be said to be for the user information query filtering, usually this table is a very large amount of data, in the case of not considering the split library table, how to optimize through the index with SQL?

Usually when we write SQL, we will write SQL to execute similar to the following, where, order by, limit and other conditions to query.

Then the next two slowly add fields one by one to analyze how to design indexes based on business scenarios.

In this case, it's easy to design a union index (price, city, sex) and be done with it.

Then this time there are partners will say, it is very simple ah, the range of fields to put the last we still know, the joint index is changed to (price, city, sex, age) is not on it.

Well, yes, there is nothing wrong with doing this, but have you ever thought that some people like both handsome and beautiful women, don't think about it....

These are the first time I've ever seen a woman in the world with a good eye on a woman.

Then this time Miss will not search for gender, then this time the joint index can only be used to the first two fields, then not in line with our professional standards ah, what to do? At this time there is still a way, we just need to move the little head to change the SQL on the line, in the absence of a choice of gender judgment, change to the following so you can.

What to do le, the same to the joint index inside the plug, for example (price, city, sex, hobby, xx, age).

For this kind of multiple range query, in order to better utilize the index, in the case of business allows you to use a fixed range, and then the database field to store the range of identifiers on it, so that it is converted into an equal value match, you can make good use of the index.

For example, the last login time field does not record the last login time, but records the setting field is_login_within_seven_days within 7 days of a login is 1, otherwise 0, and finally the index is designed as (price, city, sex, hobby, xx, is_login_within_seven_days, age). seven_days, age).

Then according to the scenario finally designed out of this index may have been able to cover most of the query traffic, then if there are other parts of the heat of the query how to do it, the way is also very simple ah, and then add one or two indexes can be.

For example, usually query the city is more popular (score: score) of the lady, this time to add a joint index (price, city, sex, score) then it can be.

As you can see, the index must be designed in conjunction with the scenario, the idea is to try to use no more than three complex union index to resist most of the more than 80% of the commonly used query traffic, and then use one or two secondary indexes to resist some of the very used query traffic.

The above is the index design that I want to share with you, if you can move your rich little hand to give a free point to the small two praise is better ~

The next two to talk about MySQL transactions and locking mechanisms.