Shulou(Shulou.com)06/01 Report--

Optimization of table

1. Separation of fixed length and variable length

Such as int,char (4), time core and commonly used fields, built with fixed length, put on a table

The variable-length field of varchar,text,blob is suitable for placing a single table and associating it with the core table with a primary key.

two。 Common fields and infrequent fields should be separated.

3. Add redundant fields on fields where 1-to-many requires associated statistics.

Principle of column type selection

1. Field type priority integer > date,time > enum,char > varchar > blob,text

two。 As long as it's enough, don't be generous. Large fields waste memory and affect speed.

3. Try to avoid using NULL,NULL is not good for indexing, need special bytes to mark.

Description of the Enum column

1.enum columns are stored internally with integers

The association between 2.enum column and enum column is the fastest.

3.enum var char's weakness-it takes time to transform when it comes to being associated with char

4. Advantage-when the char is very long, the enum is still an integer fixed length, when the amount of data queried is larger. The advantage of enum is more obvious.

Index optimization

B-tree index: B-tree index is used by default in myisam,innodb.

Hash index: in memory tables, the hash index is used by default.

Advantages and disadvantages of hash: 1. The query time complexity is O (1).

The results of 2.hash calculations are random and placed randomly on disk.

3. Unable to optimize range query

4. Unable to take advantage of prefix index

5. Sorting cannot be optimized.

6. You must return to the row, that is, to get the data location through the index, you must go back to the table to retrieve the data.

Add an index to the columns commonly used in the where condition, and if it is a separate index, only one can be used at the same time.

If you set up a multi-column index, index (ameme bjorc), note that it is related to order, and the index works and needs to meet the requirements of the left prefix.

The index is used in where,order by,group by (meets the left prefix requirement).

Multi-column index is more practical in practical application. When establishing multi-column index, we should combine with reality and create a reasonable index.

Index improves the speed of query, the speed of sorting and the speed of grouping query.

Clustered and non-clustered indexes

Clustered index: btree

Innodb engine, index and data are in one piece.

Innodb stores row data directly in the tree of the primary key index (storing both primary key values and row data).

The innodb secondary index points to a reference to the primary key.

Advantages: when there are few entries queried according to the primary key, there is no need to return rows (the data is under the primary key node).

Disadvantages: if you encounter irregular data insertion, resulting in frequent page splits.

Non-clustered index: btree

Myisam engine, indexing and data are separate

The myisam index points to the location of the row on disk

In myisam, both the primary and secondary indexes point to the physical row (disk location).

Index override:

If the column of the query happens to be part of the index, the query only needs to be done on the index file and does not need to go back to disk to find the data.

Indexing and sorting

For an overlay index, look it up directly on the index, which is orderly.

On the innodb engine, sorting along the fields of the index is also natural and orderly. For the myisam engine, sort by an index field, but the fields taken out contain fields that are not index fields, then all rows will be fetched out first, and then sorted.

First take out the data, form a temporary table, and sort the filesort files (avoid it as much as possible).

Strive for the goal: the extracted data itself is orderly, using the index to sort.

SQL statement optimization

SQL statement time is spent on waiting time, execution time, only reducing the execution time, then the lock time of other statements is also reduced.

The execution time of the SQL statement is spent on finding and fetching

How to inquire quickly? A) the order, differentiation and length of the joint index b) get faster, and the index covers c) fewer rows and columns are transmitted

Split query, split the data into multiple times, for example: insert 10000 pieces of data, every 1000 pieces per unit

Decompose the query and logically divide the multi-table join query into multiple simple SQL

Summary: check less, be as accurate as possible, and fetch fewer rows; must check, query rows on the index as far as possible; when fetching, take as few columns as possible

In group, group with indexed columns can avoid temporary table and file sorting, which is slightly faster, and int grouping is faster than char grouping.

In group, we assume that only the contents of table An are taken, the columns of group by, and try to use the columns of table A, which is faster than the columns of table B.

The columns of order by should be the same as those of group by, otherwise it will also cause temporary tables, because both of them need to be sorted, and if the columns of the two are inconsistent, they must be sorted at least once.

How to determine whether an index is used in a query?

Query method: explain SQL\ G

Extra field:

Using index: refers to the use of index coverage, which is very efficient

Using where: it means that you can't locate it by index alone, so you have to where to judge it.

Using temporary: when a temporary table is used, when the columns of group by and order by are different, or when the columns of other tables of group by and order by are used

Using filesort: files are sorted, either on disk or in memory.

Subquery

1.from type subquery

Note: the temporary table found by the inner from statement has no index, so the return content of from should be as small as possible, need to be sorted, and sort first.

2.in type subquery

Mysql's query optimizer, for in-type optimization, is changed to the execution effect of subqueries, single-row execution filtering. The larger the outer table, the slower the query. You can replace subqueries with join queries.

Limit and page flip optimization

Limit offset,N

When the offset is very large, it is inefficient because mysql fetches the offset+N row first, returns the offset row before it is abandoned, and returns N rows.

Optimization method:

1) solve the problem in terms of business, it is not allowed to turn over 100 pages. Baidu, for example, can generally turn to more than 70 pages.

2) use conditional query instead of offset (there is an index on ID)

Select * from tablename where id > 1000000 limit 2

3) check only the index, not the data, get ID, and then use ID to look up specific entries (there is an index on ID). This technique is delayed association.

Select id,xxx,xxx from tablename inner join (

Select id from tablename limit 1000000 dint 2) as tmp using (id)

Faster than the following method of directly checking the data

Select id,xxx,xxx from tablename limit 1000000,2

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.