Mysql performance tip (1)

Several principles for data type selection:

 

1. Smaller is usually better. Select the minimum type that correctly represents the data.

 

2. Easy to use. Simple types are better than complex types.

 

3. Avoid NULL. Try to define the field as not null. Performance improvement is small.

 

 

 

String selection:

 

When the maximum length is much greater than the average length and few updates occur, select VARCHAR.

 

CHAR is suitable for saving the MD5 hash value of a user's password.

 

The minimum unit of time for MySql storage is seconds, but temporary computation can be performed in milliseconds.

 

 

 

InnoDB has a special feature called adaptive hash index. When InnoDB notices that some index values are frequently accessed, it will create a memory index for these values at the top of the BTree.

 

 

 

The index contains values from one or more columns in the table. If you index multiple columns of data, the column sequence is very important, because Mysql can only search efficiently, so your leftmost prefix.

 

 

 

Clustered index: not a separate index type, but a way to store data. When a table has a clustered index, its data rows are actually stored in the leaf page of the index. Each table can have only one clustered index.

 

Set the prefix index when using it. Pay attention to the non-repeated index values when selecting indexes.

 

 

 

Index policy summary:

 

Check the most common queries. do not create any index without investigation. Test and analysis should be performed to find the balance between index creation. The first check is the response time. You need to add indexes for long-time queries. Then, you need to check the queries that cause the maximum load and add indexes; it is best to consider the cpu, memory, and disk bottlenecks of the system.

 

Try to extend the index wherever possible, instead of adding a new index. Generally, it is easier to maintain a multi-column index than to maintain multiple single-column indexes. If you do not know the query distribution, you should make the index more selective as much as possible, because the highly selective index is usually more advantageous.

 

 

 

In terms of redundancy:

 

Additional indexes, redundant fields, or even cache tables and summary tables are usually required to accelerate reading. This increases the workload for writing queries and maintenance, but it is still a common technique for designing high-performance queries: making up for the cost of writing slowly with a large speed reading. (But it also increases the development complexity for reading and writing)

 

 

 

Tips for the InnoDB Storage engine:

 

"

 

Transaction: supports transactions and four transaction isolation levels.

 

Foreign key: The only storage engine supporting foreign keys in Mysql 5.0.

 

Row-Level Lock: the lock is set at the row level and will not be passed up and will not block the selection-the standard selection will not set any lock at all, with good concurrency features.

 

Multi-version: InnoDB uses multi-version concurrency control. By default, it does not select to read old data.

 

Clustered by primary key: All InnoDB tables are clustered by the primary key.

 

All indexes contain primary key columns: The index references rows based on the primary key. Therefore, if the primary key is not maintained very short, the index will be very large.

 

Optimized cache: InnoDB caches data and memory in the buffer pool. A hash index is automatically created to accelerate row reading.

 

Unzipped index: the index is not compressed with a prefix, so it may be much larger than the index in the MyISAM table.

 

Slow data loading: In MySql5.0, InnoDB does not particularly optimize data loading. It builds a row of indexes at a time, rather than by sorting. Slow loading.

 

Blocking AUTO_INCREMENT: In versions earlier than MySql5.1, InnoDB uses table-level locks to generate each new auto_increment value.

 

No cached count (*) value: Unlike MyISAM or Memory, InnoDB does not store the number of rows in the table, which means that no where clause is available for count (*) the query will not be optimized, and full table or index scanning is required.

 

"

 

Query Optimization:

 

In the following application scenarios, the connection efficiency on the application side is higher: a large amount of data can be cached for early queries; multiple MyISAM tables are used; data is distributed on different servers; replace a join with in () for a large table. A join references the same table many times.

 

 

 

Query cache: Mysql uses the query text as the key and the result set as a map of the value. Therefore, any change in the query statement will result in a cache miss. Writing consistent query statements becomes the key point of mysql operations. In addition, the query cache does not cache any queries that contain uncertain functions, such as NOW (). In other words, user-defined functions, variables, and so on will not be cached. (The principle is that the server will perform a case-insensitive check to verify whether the query is prefixed with SEL ). In addition, the results are too large to be cached. Creating a cache also has overhead, such as memory allocation. After the cache is enabled, the query will be checked. This is also the performance overhead. Therefore, you must carefully consider whether to enable the cache based on your needs.

 

From Change Dir