About MySQL Database optimization

about MySQL database optimization

In my humble opinion, the database is optimized to optimize storage and query speed

At present the main optimization I think is to optimize the query speed, query speed, improve the user's experience

I think that the optimization is mainly from two aspects of consideration,

Optimizes database objects,

Optimizing SQL

Optimizing Database Objects

1. Optimize the data type of the table, for each field with the appropriate data type, can not only solve the disk storage space, but also to avoid wasting the program run time wasted physical memory, improve query speed.

For example, for integer types that do not have negative numbers, unsigned integers are used, and integer ranges are small with tinyint or smallint

2 Improve the efficiency of table access by splitting tables. (For MyISAM)

Vertical split, split horizontally, if some columns in a table are commonly used, some columns are not commonly used and can be split vertically. Disadvantage when querying all data, you need to have a table attached.

3 Inverse Normalization

To add redundant columns, redundant columns are worth the same columns in multiple tables, which is to avoid a table operation when querying

Increasing the columns of the derived columns from data from other tables is calculated from the data of the other tables. This is to reduce the connection operation and avoid the use of aggregate functions

Re-grouping tables The data that users view frequently is data that is connected using two tables, can be re-rented a table, but also reduce the operation of the table

4 using the Intermediate Table Intermediate table to copy part of the data from the source table, and isolated from the source table, querying the statistical operations on the intermediate table does not affect the source table.

The intermediate table can be used flexibly to increase the index, or increase the temporary field, so as to improve the efficiency of statistical query

Second, SQL optimization

· In the query for the table, there are several types of queries,

1 Traversing full table all

2 Scan full index traverse entire index

3 index range Scan, common in < > >= between range

4 prefixes using a non-unique index or a unique index

5 Unique index, only one record in the table matches the key value for each index Eq_ref

6 Const/system A single table with a maximum of one matching row, the query is very fast, for example, depending on the primary key or unique index

7 null for NULL, MySQL does not return directly to get results

Indexing is one of the most common and important means of database optimization.

Storage classifications for indexes:

B-tree indexes most commonly used

Hash index only Memory support

R-tree Index Spatial Index, not commonly used

Full-text Full-Text index

More commonly used is B-tree index and hash index, hash index suitable for key-value query, not suitable for range lookup

B-tree index is not a binary tree, but a balance tree

Note that the column for the query is a string, and the index is not used if you enclose it in quotation marks.

For a condition that is separated by or, the index is not used if there is an index in the column in front of the or, and there is no index in the subsequent column.

For insert operations

Use the INSERT statement of multiple value tables as much as possible, which greatly reduces the client-to-database connection, shutdown, and so on.

Avoid subqueries and use more efficient join,join because MYSQ does not need to create temporary tables in memory

Optimize paging

About MySQL Database optimization