A poorly designed or did not do the index schma optimized performance can be significantly improved.
If you want to get good performance, we need you to run specific queries to design schema and indexes,
You need to estimate demand for different types of query performance, and frequency of use, and the need to search and retrieval of field conditions. Optimization is a trade-off, the index can increase the retrieval speed, but will reduce the update rate, a counter-paradigm query schema might speed up some speed, but in other cases may reduce the speed. Add redundant fields, counters, summary tables to optimize query, but it was very difficult to maintain.
Choose the best type of data:
Mysql supports a large variety of samples of data types, how to choose the right type to store data is to produce high-key.
1 large enough the smaller the better type of small types are generally more efficient because they use less disk space, memory and cpu cache, requires less CPU cycles. But do not underestimate the value of the range of storage, increasing the number of local schema data type is a painful and time-consuming operation.
2 simple like:
Simple types require fewer clock cycles, integer comparison is faster than string comparisons. , For example, you should store
mysql built-in string type, rather than to store the date and time, use an integer to store the ip.
3. As far as possible to avoid the null fields:
As much as possible in the definition of the field to add NOT NULL, MySQL can be null for the column is difficult to optimize,
Because it allows the index, statistics, and complicate the comparison value, nullable columns will use more storage space, and requires some special handling, build indexes on nullable columns, each entry requires additional data, even in MyISAM will become a longer length of the index. No value for a column, you can use some special value to represent, such as 0, an empty string. Will become null not null may enhance the performance is not great, but if you intend to see the index on a column, then to avoid it is nullable's.
Select a column's data type, first decide what type of general: integer, string, and so on. Then again these types of offers to choose the right type of MySQL. Such as DATETIME and TIMESTAMPE can store the same data: the date and time, an accuracy of 1 second. But Timestamp using half the space, and is sensitive to time zones and some special automatic update feature. But with a small range of values, to 2038 on over the.
MYSQL for compatibility provides a lot of aliases, such as INTEGER, BOOL, NUMERIC, they are just aliases, does not affect performance.
Numeric types, including integer and floating point, integer type tinyint, smallint, mediumint, int,
bigint, they accounted 8,16,24,32,64 bytes. You can use the unsigned integer type properties
tinyint unsigned 0 to 255 can be stored. MySQL integer usually calculated using a 64-bit bigint.
MySQL can specify the width of integer type, but this does not limit the scope of data width, only a tool for interactive display to retain the specified number of characters in the storage INT (1) and INT (20) are equivalent.
Decimal floating-point types are part of the book, but they are not just for floating-point numbers, you can use BIGINT DECIMAL to store no less than that integer, MySQL supports two categories: exact and inexact type.
FLOAT and DOUBLE floating-point support of the approximate calculation of the standard, if you need to know the exact calculation of the results, you need to achieve floating-point platform. DECIMAL can store the exact score, MySQL5.0 more support precise mathematical calculations, but the floating-point types of mathematical calculations will be more efficient, less storage space. DECIMAL floating point type and can specify precision, such as DECIMAL (18,9). Due to space and computing efficiency, only the need for accurate results, we use DECIMAL.
MySQL supports many types of string, starting from version 4.1, support for each column in one character set, if you do, it will greatly affect performance.
VARCHAR and CHAR types:
There are two main types of string VARCHAR and CHAR:
VARCHAR main store variable-length character type, than fixed-length type to save space, because it requires and the actual need to use the space as large, VARCHAR specify the length of the application is not so much space, but the maximum possible amount of space . 2 extra bytes used to store the length, such as VARCHAR (10) will account for maximum 11 bytes, VARCHAR (1000) takes up 1002 bytes maximum.
As VARCHAR can save space, so it helps to improve performance, but because the line is longer, if the line too big to be stored in its original location, then will produce the fragments in MyISAM, InnoDB will be paged.
MySQL 5.0 or more does not automatically delete trailing whitespace.
Usually the longest length of data much larger than the average, this field is not updated frequently the case with VARCHAR.
CHAR is fixed length type, it will apply for a specified length of the space. Storage time will be removed at the end of the blank by default,
If memory is short, and almost the length of the string, use the CHAR is very good, such as the user MD5 password storage.
For frequent with the new field, use the CHAR is a better choice, you can use CHAR (1) to represent the Y and N values than VARCHAR (1) less one byte.
BINARY and VARBINARY types used to store binary strings, and traditional string similar to, but storing the bytes instead of letters. BINARY string comparison is faster than ordinary strings.
BLOB and TEXT Types:
BLOB and TEXT is used to store large data, one for binary and string types. There are other types of the same family: TINYTEXT, SMALLTEXT, TEXT, MEDIUMTEXT, LONGTEXT, and binary types TINYBLOB, SMALLBLOB, BLOB, MEDIUMBLOB, LONGBLOB.
Is the difference between BLOB and TEXT to store a binary, a text he had used to store the character set and encoding.
BLOB and TEXT types and other types of sorting, he will not sort the entire length of the string, he would sort the top
max_sort_length bytes. If you need to sort with fewer characters you can reduce the max_sort_length, or use the order by substring (column, length). MySQL is not the full length of these types of construction of the index, it is not able to use the index to sort.
Memory storage engine does not support BLOB and TEXT types, so use the BLOB and TEXT columns the query, the implied use of On-disk temporary table. Even if you configure MySQL temporary tables in memory, still have a very expensive system calls. Best practice is to not use BLOB and TEXT types unless you really need, if unavoidable, you can make substring (column, length) class to convert a string type, so you can use in-memory table. Need to make sure to use enough to have a short substring, the temporary table size does not exceed max_head_table_size or tmp_table_size, otherwise it will use the on-disk MyISAM table.
EXPLAIN the Extra column contains "Using temporary" indicates that this query uses an implicit temporary table.
Instead of using ENUM string sometimes we can use the ENUM type column instead of the traditional string type, ENUM column can store 65535 different string value. MySQL store their very compact, compressed to 1-2 bytes. Each value is an integer of internal storage, the. Frm file to the string of a digital map.
mysql> CREATE TABLE enum_test (
e ENUM ('fish', 'apple', 'dog') NOT NULL
mysql> INSERT INTO enum_test (e) VALUES ('fish'), ('dog'), ('apple');
mysql> SELECT e + 0 FROM enum_test;
| E + 0 |
ENUM field sorted according to the internal storage of integer row, you can display using the FIELD to specify the sort of way.
mysql> SELECT e FROM enum_test ORDER BY FIELD (e, 'apple', 'dog', 'fish');
| Apple |
| Fish |
ENUM is a string list of the biggest drawbacks is the fixed size, add and delete a string need to use ALTER TABLE, so if you said that the string may change in the future, then the use of ENUM is not a good idea.
If the ENUM needs and char, varchar to join operations, it will be slower, this time preferably the same type,
But the use of ENUM will be better than using char, varchar save a lot of space.
Date and time types:
MySQL has a variety of date and time types, such as YEAR, DATE, MySQL can store the minimum time granularity is seconds, but can do temporary operations subtle level.
This type can hold a wider range of values, from 1001-9999, an accuracy of 1 second, he will be the date and time is stored as an integer format YYYYMMDDHHMMSS, and is independent of time, using 8 bytes.
TIMESTAMP literally, loss of memory from 1970.1.1 number of seconds, and the same as the Unix timestamp.
TIMESTAMP only four of their own, so much smaller than DATETIME: From 1970 to 2038. MySQL provides
FROM_UNIXTIME () and UNIX_TIMESTAMP () function to convert a Unix timestamp and date.
TIMESTAMP is the time zone related.
There is no timestamp DATETIME function, he did not insert the data in time, if the column is blank, then automatically insert the current time. When updating a row, this row of data with the new TIMESTAMP column will default to the current time, unless explicitly specified value, TIMESTAMP column is NOT NULL default.
Generally you should use TIMESTAMP, because he takes up less space than DATETIME. Some people like to store
Unix timestamp as an integer, but often do not give you any good, because this format is not easily handled, we do not recommend such use.
Bit compression type (Bit-packed Data Types)
MySQL provides a number of storage type, he stored a value for each bit to a more compact way to store data. All of these types are string types, regardless of the underlying storage format and operation:
Before the MySQL5.0 BIT, BIT is a synonym for TINYINT. But in 5.0, it is a completely different type, has a special feature: You can use the BIT column to store true / false value. BIT (1) contains a single bit is defined, BIT (2) stores two bit, BIT field maximum is 64bit.
BIT different types of behavior in different storage engines, MyISAM storage compression with those columns, so the 17 BIT column only 17bit memory space (no column is NULL). Other storage engines such as Memory and InnoDB, using the integer type large enough to store, so use the BIT does not save much space.
MySQL BIT type as the string instead of numeric type. If you are using BIT (1), check out a string, the content is binary 0 or 1, but not ASCCII code '0 ', '1' value.
However, the use of the digital scene, its value is a string of binary values, such as b'00111001 '57.
This may be more confusing, so we recommend careful use of BIT, in most applications, avoid using.
If you want to store true / false use of a single bit of storage space, then another option is to use varchar (0),
This column can store a NULL or empty string.
If you want to store a lot of true / false value, then consider a lot of columns combined into one, use the SET data type, internal representation of the compressed bit set. MySQL has a function FIND_IN_SET () and FIELD (), so that in the query easier to use. The main drawback is that changes need to ALTER TABLE.
Bit operations using an integer column in another for the SET option is to use an integer as a compressed collection of bits, for example, you can use TINYINT type
8 bit, then bit operation. In this way the maximum relative to the SET bit is to modify the field that does not ALTER TABLE. The disadvantage is more to take to write and understand, bit manipulation, some people like some people do not like, so totally your taste of.
Select the primary key:
Choose the correct type of a primary key is very important. You often use these columns to compare with other values, or use it to find, in other tables as foreign keys. Select the primary key is not only the type of drugs to consider storage, but also consider the performance comparison and calculation. Such as MySQL ENUM and SET types stored within the form of an integer, but in the context of the string will be converted to a string.
Once you select a type of good, to ensure that all related tables with foreign key corresponding to the same type. Type of match is better to be precise, in addition to UNSIGNED. Mix of different types may lead to performance problems, and implicit type conversion may lead to errors.
Select the smallest interval that contains the type of data, such as you would like to use state_id field to store the state of the United States, then TINYINT enough.
Usually the primary key type is the best choice, because it is fast and can self-growth.
ENUM and SET:
Is often a bad choice, they are more suitable as a state, type value.
Avoid using a string primary key, because the take up more space and slower than integer. Use MyISAM tables should be especially careful to use the primary key of type string, because he will by default compress string index, this will cause the query is very slow.
Using the packed index about six times slower.
Use completely random string, such as MD5 (), SHA1 (), UUID (), to be especially careful, the value of each new generation will be in a lot of room, a random value, which will reduce the insert and part of the query statement.
INSERT slow because of the value inserted at random locations in the index, which cause a page split or a random disk access, for clustered storage engine will cause the clustered index fragmentation.
SELECT slow because the adjacent line of logic and memory will be distributed on disk.
Random values can lead to all types of query cache performance is poor.
Special types of data:
For example, many people use VARCHAR (15) to represent the IP, in fact, IP is an unsigned 32 integer, so the stored IP address of an integer is a better way. MySQL provides INET_ATON () and INET_NTOA function to convert the two representations.
Reference to "high performance MySQL"