<< High Performance mysql>> Note 1

Reprint Please specify: theviper http://www.cnblogs.com/TheViper

<< High Performance mysql>> This book is really very good, but unfortunately this cock, most of them do not understand, for the moment, write down the MySQL optimization related to their own useful things.

Test metrics

• Throughput

Throughput refers to the number of transactions per unit of time, in TPS (transaction per second). This has been an indicator of classic database application testing.

• Response time or delay

This indicator is used to test the overall time required for a task

• Concurrency of

Note that Web server concurrency is not equivalent to the concurrency of the database. High concurrency of the server can also lead to high concurrency in the database, but the language, framework, and toolset used by the server will have an impact on this. A well-designed application that can open hundreds of thousands of database server connections, but may have only a few connections executing queries at the same time. As a result, you need to focus on concurrent operations that are working, or the number of threads or connections that are working at the same time.

• Scalability

Scalability refers to the increase in the work of the system, ideally, will be twice times the throughput, at this time, see the actual increase in the throughput is how much.

goal of performance optimization-response time

Many people think that performance optimization is about reducing CPU utilization. But it's a trap, and resources are used to consume and work. So, sometimes consuming more resources can speed up the query. Many times, after upgrading to a new version of MySQL, the CPU utilization will rise very much. This is not representative of the problem can be. Instead, it indicates that the new version has increased utilization of resources.

In addition, if the performance optimization is only seen to increase the number of queries per second, this is actually only throughput optimization. Increased throughput can reduce the by-product of response time (reciprocal relationship).

Optimizing data Types

• Smaller is usually better

In general, you should try to use the smallest data type that correctly stores your data. But make sure you don't underestimate the range of worth of storage.

• It's simple.

Operations of simple data types typically require less CPU cycles. For example, an integer is less expensive and more specific than a word Fu Cao.

1. Use MySQL built-in type instead of string to store date and time

2. You should store the IP address with an integer type.

• Try to avoid null

In general, it is best to specify column NOT NULL unless you really need to store a null value. If the query contains nullable columns, it is more difficult for MySQL to optimize because nullable columns make indexes, index calculations, and value comparisons more complex. Additionally, a nullable column uses more storage space.

The following specific data types

1. Integer type

There are two types of numbers, integers and real numbers. If you store integers, you can use these types of integers: Tinyint,smallint,mediumint,int,bigint, which corresponds to 8,16,24,32,64 bit storage space.

Integer types have the Unsinged property, which means that negative numbers are not allowed, which can roughly be one-fold higher than the maximum of integers.

MySQL can specify widths for integer types, such as int (7), but this is meaningless for most applications. It does not limit the range of values. for storage, int (1) and int (10) are the same.

2. Real type

A real number is one with a fractional part. However, they can be used not only to store fractional parts, but also to store integers larger than bigint in decimal.

Floating-point types typically use less space than decimal when storing values of the same range. Float is stored using 4 bytes, and double is stored with 8 bytes. MySQL uses a double as the type of the internal floating-point calculation.

Note that when the amount of data is large, for example, to store financial data, consider using bigint instead of decimal, which will need to be stored in the currency unit, multiplied by the number of decimal places to the corresponding multiples. This avoids the problem of inaccurate floating-point storage and the high cost of decimal precision computation.

3. String type

VARCHAR: Used to store variable-length strings. It is more space-saving than a fixed-length type because it uses only the necessary space.

VARCHAR uses 1 or 2 extra bytes to record the length of a string. If the maximum length of the column is <=255 bytes, it is represented by 1 bytes, otherwise 2 bytes are used.

Because the row is longer, it may make the row longer than it was when the update was made. It is more appropriate to use varchar under the following conditions.

1. The maximum length of a string column is much larger than the average length

2. There are few updates to the column and no fragmentation issues.

3. Use the complex character set that you want to utf-8.

4. Each character is stored using a different number of bytes.

CHAR: fixed length, MySQL always allocates enough space according to the length of the defined string.

When stored, MySQL removes all trailing spaces. Char is suitable for storing very short strings, or all values are close to the same length.

For example, char is suitable for storing the MD5 value of a password because it is fixed-length. For frequently changed data, char is better than varchar because char is not prone to fragmentation.

4.blob and text types

Both are string data types that are designed to store large data and are stored in binary and character mode, respectively.

5. Date, Time type

MySQL can store a minimum time granularity of seconds.

DATETIME: This type can hold a wide range of values, from 1001 to 9999, with a precision of seconds, which encapsulates the date and time in an integer format of YYYYMMDDHHMMSS, regardless of the time zone, using 8 bytes of storage space.

TIMESTAMP: This type holds the number of seconds since January 1, 1970 0 o'clock. It is the same as the Unix timestamp. It is stored in 4 bytes, so the range is much smaller than datetime and can only represent 1970 to 2038. The value displayed by timestamp is related to the time zone.

Create high-performance indexes

An index can contain values for one or more columns. If the index contains more than one column, the order of the columns is important because MySQL can only use the leftmost prefix column of the index efficiently.

The most common B-tree index stores data sequentially, so MySQL can do order by and group by operations. Because the data is ordered, B-tree also stores the related column values together. Finally, because the actual values are stored in the index, some queries use only the index to complete the query.

Strategy:

1. Stand-alone columns: an indexed column cannot be part of an expression, nor can it be an argument to a function. Like what

Select ID from a where id+1=5

Select .... where To_days (current_date)-to_days (date_col) <=10

You should develop the habit of simplifying where conditions, and always place the index columns on one side of the comparison symbol alone.

2. Prefix index: Part of the character at which the index begins.

For long columns like Blob,text, you must use a prefix index. Because MySQL does not allow the full length of these columns to be indexed.

Therefore, the key to establishing a prefix index is to choose a prefix that is long enough to ensure high selectivity and not too long.

The prefix should be long enough to make the selectivity of the prefix index close to the index of the entire column. In order to find this sufficient length, you need to find a list of the most common values, and then compare them with the most common list of prefixes. For example

You can see that each value is between 45 and 65, and the sensitivity is not good. Take 3 prefix characters below

This time the distinction will be better, the following continue to increase the prefix length, and finally found that the prefix length of 7 is more appropriate

Another way to calculate the appropriate prefix length is to calculate the selectivity of the complete column and to make the selectivity of the prefix close to the selectivity of the complete column, specifically

3. Multi-column index

A common mistake is to create a separate index for each column, or create a multicolumn index in the wrong order.

In terms of the order of indexed columns, the correct order depends on the query that uses the index, and also on whether the sorting and grouping requirements are met.

One lesson: Place the highest-selectivity columns at the forefront of the index. This experience works well without having to consider sorting and grouping. This time the index is only useful in optimizing where condition lookups.

In fact, performance depends not only on the selectivity of all indexed columns, but also on the specific value of the query condition, which is related to the value distribution, as previously said to choose the best prefix length to consider. In other words, you might want to adjust the order of indexed columns based on those queries that run most frequently.

Sorting using index Scans

MySQL has two ways of generating ordered results, either by sorting or by index order. If the value of the type column explain out is index, the index scan is used to do the sorting.

Scanning the index itself is fast because only one index record needs to be moved to the next record immediately. However, if the index cannot overwrite all of the columns required by the query, it is necessary to query the corresponding row at a time with each index record scanned. This is basically random IO. Therefore, reading data in indexed order is often slower than sequential full-table scans, especially in IO-intensive workloads.

MySQL can use the same index to satisfy both sorting and finding rows. Therefore, if possible, the design of the index should be as good as possible to meet both tasks.

MySQL can use an index to sort the results only if the order of the indexes is exactly the same as the ORDER BY clause, and if all the columns are in the same direction.

If the query requires more than one table to be associated, the index can be used for sorting only if the fields referenced by the ORDER BY clause are all of the first table.

The restriction of the ORDER BY clause and the lookup query is the same and requires that the leftmost prefix of the index be met, otherwise MySQL will need to perform a sort operation.

There is a situation where the index's leftmost prefix requirement is not met, and index ordering can still be used. That is when the current flux is constant. For example

On a table, build the index (A,B,C).

Select ... where a= "2014-12-21" ORDER by B,c.

At this point the first column of the index is specified as a constant and the index can be used. The following can also use the index

Select ... where a> "2014-12-21" ORDER by B

Select ... where a> "2014-12-21" ORDER by A, b

These two just use the index prefix, so it's also possible.

Here are some queries that cannot be sorted using an index

Select ... where a= "2014-12-21" ORDER by B desc,c ESC

The query uses two different sort orientations, but the indexed columns are ordered in a positive order.

Select ... where a= "2014-12-21" ORDER by B,d

A column that is not in the index is referenced

Select ... where a= "2014-12-21" ORDER by C

The column in where and order by cannot be the leftmost prefix of the index, because the column of B is skipped

Select ... where a> "2014-12-21" ORDER by B,c

The first column is the range query

Select ... where a> "2014-12-21" and B in (on) Order by C

There are multiple equals conditions on column B

<< High Performance mysql>> Note 1