High Performance MySQL Reading Notes (1), high performance mysql

High Performance MySQL Reading Notes (1), high performance mysql
Slow Query

When a resource becomes inefficient, You should know why it is. Possible reasons are as follows:
1. The resource is used too much, and the remaining amount is insufficient for normal operation.
2. The resource is not correctly configured.
3. The resource has been damaged or has failed.

Because of slow queries, too many queries are too long, resulting in logic accumulation.
Is slow query the cause or result? It is unknown before an in-depth investigation. Remember, this query is also running normally. A query that requires filesort and creating a temporary table does not necessarily mean that there is a problem. Although removing filesort and temporary tables is usually a "best practice ".

MySQL Data Type

In general, try to use the minimum data type that can properly store data. Smaller data types are usually faster, because they consume less disk, memory, and cpu cache, and require less cpu cycles for processing.
Simple data operations usually require less cpu cycles. For example, integer type operations are cheaper than character operations because character sets and collation rules (sorting rules) Make the character comparison more complex than integer type operations, use an integer storage IP address.
Avoid NULL as much as possible. Normally, it is best to specify the column not null unless you really need to store the NULL value. If a query contains a column that can be NULL, it is more difficult for mysql to optimize it, because a column that can be NULL makes the index, index statistics, and Value Comparison more complex.

Alias

MYSQL supports many aliases for compatibility, such as integer, bool, and numeric. They are only aliases. Although they may be confusing, they will not affect performance. If the data type alias is used during table creation and the show create table check is used, mysql reports the basic type instead of the alias.

MySQL can specify the width for the integer type, for example, int (11). This is meaningless for most applications:It does not limit the valid range of values.It only specifies the number of characters used by some mysql interaction tools (such as the mysql command line client.Int (1) and int (20) are the same for storage and computing..

Character Type

The varchar type is used to store variable-length strings. One or two additional bytes are required to record the length of strings. This type saves more space than the fixed-length type because it only uses the necessary space.
The char type is fixed: MySQL always allocates enough space according to the defined String Length.
Use Cases of varchar:
The maximum length of a string column is much larger than the average length. The column is rarely updated, so fragmentation is not a problem. A complex character set like UTF-8 is used, each character is stored in different bytes.
Char is suitable for storing short strings, or all values are close to the same length. For example, char is very suitable for storing the MD5 value of the password because it is a fixed length value. For frequently changed data, char is also better than varchar, because fixed-length char types are not prone to fragmentation.

Big field character type

Blob and text are both string data types designed to store large amounts of data. They are stored in binary and string modes, respectively. MySQL sorts blob and text columns differently from other types: It only sorts the first max_sort_length byte of each column rather than the entire string.
Avoid text and blob types as much as possible. If it is unavoidable, one technique is to use substring (column, length) Wherever blob fields are used) convert the column value to a string (which is also applicable in the order by clause), so that you can use the memory temporary table. However, make sure that the truncated sub-string is short enough so that the temporary table size does not exceed max_heap_table_size or tmp_table_size. After the size exceeds, Mysql converts the temporary memory table to the temporary MyISAM disk table.

Time Type

The timestamp type stores the number of seconds since midnight, January 1, January 1, 1970. timestamp only uses 4 bytes of storage space. Therefore, it ranges Much less than datatime: it can only represent the period from January 1, 1970 to January 1, 2038. The from_unixtime function is provided to convert a unix timestamp to a date, and the unix_timestamp function to convert a date to a timestamp.
Sometimes people store unix timestamps as integers, which does not bring any benefit. It is usually inconvenient to save the timestamp format with integers, so we do not recommend this.

Identifier)

Integers are usually the best choice for identifying columns, because they are fast and can use auto_increment. Do not use enum or set as the identification column. Do not use string as the identification column, because they consume much space and are generally slower than the numeric type. Be especially careful when using strings as the identification columns in the MyISAM table, because MyISAM uses a compressed index on strings by default, which leads to a much slower query.

IP address field

Varchar (15) is often used to store IP addresses. However, they are actually 32-bit unsigned integers, not strings. MySQL provides the INET_ATON () and INET_NTOA () functions to convert IP addresses between integer and four-segment representation.

Paradigm and anti-paradigm in Databases

In a database with a paradigm, each fact data appears only once. On the contrary, in a database with an anti-paradigm, information is redundant and may be stored in multiple places.
The disadvantage of schema in the design of the paradigm is that it usually requires association. Many associations may make some index policies invalid. For example, the paradigm may store columns in different tables, these columns can belong to the same index in a table.
The schema of the anti-paradigm can avoid association because all data is in a table. The disadvantage is that the update operation is costly and multiple tables need to be updated,As for whether this is a problem, we need to consider the update frequency and update duration, and compare it with the frequency of executing the select query..
The reason for redundant data from another parent table to the child table is usually the need for sorting.
Cache derived values are also useful. If you want to display the messages sent by each user (like in many forums), you can execute an expensive subquery each time to calculate and display the messages; you can also create a num_messages column in the user table to update this value whenever a user sends a new message.

Cache tables and Summary Tables

It is very expensive to cache tables and summary tables and calculate statistical values in real time, because either you need to scan most of the data in the table, or the query statement can only run effectively on certain indexes, such specific indexes generally have an impact on the update operation, so you generally do not want to create such indexes.
When Using Cache tables and summary tables, you must decide whether to maintain data in real time or regularly rebuild the data. Which of the following statements depends on the application,Regular rebuild not only saves resources, but also keeps the table from a lot of fragments and fully ordered indexes (this will be more efficient).

Counter application

If the application saves counters in the table, it may encounter concurrency issues when updating the counter. TIPS: Save the counter in multiple rows, update the Count + 1 operation to randomly select a row for update, and make a sum when calculating the Count value.

Table Structure Change

In general, most of the alter table operations will cause MySQL service interruption (lock the table and recreate the table ). MySQL executes most of the operations to modify the table structure by creating an empty table with the new structure, finding all data from the old table into the new table, and then deleting the old table. This operation may take a long time.
Not all alter table operations will cause table reconstruction. In theory, MySQL can skip the process of creating a new table. The default value of a column actually exists in the. frm file of the table. Therefore, you can directly modify this file without modifying the table itself. For example, use alter comlum to change the default value of a column:
Alert table tablename
Alert column col1 set default 5;
This statement will directly modify the. frm file without involving table data, so this operation is very fast.

 

Chapter 4 earlier