"MySQL": MySQL performance optimization summary

I. Overview of the MySQL engine

The 1.MyISAM Storage Engine MyISAM table is independent of the operating system, which means that it can be easily ported from a Windows Server to a Linux server, and whenever we build a table for a MyISAM engine, we create three files on the local disk, which is indicated by the file name. For example, if I build a tb_demo table for the MyISAM engine, the following three files will be generated: 1.tb_demo.frm, storage table definition, 2.tb_demo. MYD, storing data; 3.tb_demo.    MYI, stores the index. The MyISAM table cannot handle transactions, which means that there is a table for transaction processing requirements and cannot use the MyISAM storage engine. The MyISAM storage engine is particularly suitable for use in the following situations: 1. Select Intensive tables.    The MyISAM storage engine is very fast in filtering large amounts of data, which is its most prominent advantage. 2. Insert intensive tables. The concurrent Insert feature of MyISAM allows data to be selected and inserted at the same time.    For example, the MyISAM storage engine is ideal for managing mail or Web server log data. 2.Innodb Storage Engine Innodb is a robust transactional storage engine that has been used by many Internet companies to provide a powerful solution for users to operate very large data stores. MySQL version 5.6.13 is installed on my computer, and InnoDB is the default storage engine. InnoDB also introduces row-level locking and foreign key constraints, which are ideal for use with InnoDB in the following situations: 1. Update dense tables.    The InnoDB storage engine is ideal for handling multiple concurrent update requests. 2. Transactions.    The InnoDB storage engine is a standard MySQL storage engine that supports transactions. 3. Automatic disaster recovery.    Unlike other storage engines, the InnoDB table can automatically recover from a disaster. 4. Foreign KEY constraints.    MySQL supports the foreign key storage engine only InnoDB.    5. Support automatic increment of column auto_increment attribute.    In general, InnoDB is a good choice if transaction support is required and there is a high frequency of concurrent reads.    The 3.NDBCluster storage Engine NDB storage engine, also known as the NDB Cluster storage engine, is primarily used in the MySQL Cluster distributed cluster environment, and Cluster is a new feature that MySQL has only begun to provide since version 5.0. The 4.Merge storage engine Merge storage engine is a combination of a set of MyISAM tables whose structures must be identical, even though their use is not as prominent as other engines, butis very useful in some cases. To be blunt, the merge table is just a few aggregators of the same MyISAM table, and there is no data in the merge table, and the merge type table can be queried, updated, deleted, and actually operated on the internal MyISAM table.    The usage scenario for the merge storage engine. For server logs, the most common storage strategy is to divide the data into tables, each associated with a specific time-end. For example, you can use 12 identical tables to store server log data, each named by the name of each month. When it is necessary to generate a report based on data from all 12 log tables, this means that multiple table queries need to be written and updated to reflect the information in those tables.    Instead of writing these queries that may have errors, instead of merging the tables with a single query and then deleting the Mer GE table without affecting the original data, deleting the merge table simply removes the definition of the merge table and has no effect on the internal table. The starting point for the 5.Memory storage engine using the MySQL memory storage engine is speed. To get the fastest response time, the logical storage medium used is system memory. While storing table data in memory does provide high performance, all M Emory data is lost when the mysqld daemon crashes. The speed of the acquisition also brings some drawbacks. It requires that the data stored in the Memory data table use a constant length format, which means that a variable length data type such as BLOB and text cannot be used, and VARCHAR is a variable-length type, but because it is a fixed-length char type within MySQL,    So it can be used. Memory storage engines are typically used in the following situations: 1. The target data is small and is accessed very frequently.    Storing data in memory, so it will cause the use of memory, can be controlled by parameter max_heap_table_size the size of the memory table, set this parameter, you can limit the memory table maximum.    2. If the data is temporary and required to be immediately available, it can be stored in the memory table.    3. Data stored in the memory table, if suddenly lost, will not have a substantial negative impact on the app service.    The 6.BDB storage Engine BDB storage engine is all called the BerkeleyDB storage Engine, and InnoDB, as well as MySQL's own development implementation of a storage engine, but is provided by Sleepycatsoftware, of course, is also open source storage engine, also support transaction security 7.FEDERATED Storage Engine FThe functionality implemented by the Ederated storage engine is basically similar to Oracle's Dblink, which is primarily used to provide access to the data on the remote MySQL server.    If we use source code compilation to install MySQL, then you must manually specify that the Federa Ted storage engine is enabled, because MySQL does not use the storage engine by default. The 8.ARCHIVE Storage Engine Archive storage engine is primarily used to store outdated, infrequently accessed historical data with small storage space. The archive table does not support indexing, a. frm structure definition file, and a. Arz data compression file also has a. ARM meta-information file. Due to the particularity of the data being stored, the archive table does not support the deletion and modification operation, only the insert and query operations are supported.    The locking mechanism is row-level locked. 9.BLACKHOLE Storage Engine Blackhole storage engine is a very interesting storage engine, the function of the name, is a "black hole."    Just like the "/dev/null" device under our UNIX system, no matter what information we write, there is no return. The 10.CSV storage Engine the CSV storage engine actually operates a standard CSV file that does not support indexing. The main purpose is that you may sometimes need to export data from the database into a report file, and the CSV file is a more standard format supported by many software, so we can first create a CVS table in the database, and then insert the generated report information into the table, To get a copy of the CSV report file.

Second, the impact of the hardware environment on the system performance

1, typical OLTP application system for all kinds of database system environment of the most common OLTP system, characterized by a large amount of concurrency, the overall data volume is more, but each access to less data, and access to the data is more discrete, active data accounted for the proportion of the overall data is not too large. The database for this type of system is actually the most difficult to maintain, the most difficult to optimize, the overall performance requirements of the host is also the highest. Because not only the traffic is very high, the amount of data is not small. For these characteristics and analysis, we can get an approximate direction of the OLTP. Although the overall data volume of the system is large, but the proportion of the system active data in the total data is small, we can enlarge the memory capacity to cache the active data into memory as much as possible, although the IO access is very frequent, but the amount of data accessed is small and discrete. Then our requirement for disk storage is that the IOPS performance is very good, the throughput is a secondary factor, the concurrency is very high, the CPU processing requests per second naturally a lot, so CPU processing power needs to be relatively strong, although the amount of data per interaction with the client is not particularly large, but the network interaction is very frequent, So the network device that the host interacts with the client does not need to be too weak to the traffic capacity. 2, the typical OLAP application system for data analysis of the OLAP system is the main feature of the data is very large, concurrent access is not much, but each access needs to retrieve more data, and data access is relatively concentrated, there is no obvious active data concept. Based on the various features and corresponding analysis of OLAP system, the approximate strategy for the hardware optimization of OLAP system is as follows: The data volume is very large, so the unit capacity of disk storage system needs to be as large as possible, the volume of data is large and the access data is concentrated. The performance requirements for the IO system are as large as possible IO throughput per second, so the disk with the largest throughput per second should be used, although the IO performance requirements are relatively high, but the number of concurrent requests is less, so CPU processing power is more difficult to become a performance bottleneck, so CPU processing power is not too stringent requirements Although the number of accesses per request is large, most of the data in the execution is not returned to the client, and the amount of data returned to the client is small, so the network device requirements for interacting with the client are not too high, and because the OLAP system can parallelize well because of its long operation process, So the general OLAP system is a cluster composed of multiple hosts, and the data interaction between host and host in the cluster is generally very large, so the network equipment between hosts in the cluster is very high. 3, in addition to the above two typical applications, there is a special kind of application system, their data volume is not particularly large, but the access request and its frequent, and most of the read request. Tens or even tens of thousands of requests per second may be required, and each request is very simple, and most likely only one or a few smaller records are returned, such as data-basedThe library's DNS service is such a type of service. Although the amount of data is small, but the access is very frequent, so large memory can be used to cache most of the data, which can guarantee very high hit rate, disk IO is small, so the disk does not need to be particularly high performance, concurrent requests are very frequent, more than the need for strong CPU processing capacity to handle Although the application and database interaction is very large, but each interaction data is small, the overall traffic will be larger, but generally the average gigabit NIC is enough.

Third, query optimization

First, the query optimization has a few good work can help us to understand the performance of SQL.

1, Explain

　　

2. SHOW TABLE STATUS like ' Tb_test1 '

　　　　

1. Name: Table Name 2, Engine: Table Storage Engine 3, Row_mormat: Row format 4, Rows: Rows in table 5, Avg_row_lenght: Average number of bytes per row 6, Data_length: Table data size 7, Max_data _length: Maximum capacity of table data 8, Index_length: Index size 9, Data_free: Space not used 10, Auto_incerement: Value of the next auto_increment 11, Create_ Time: Create 12, Chent_time: Use the Check Table command or the Myisamchk tool last check table time 13, Collation: Table default character set and character column collation 14, checksum: if enabled, Saved is the entire table of real-time checksum 15, Create_opions: Create table when the other options specified 16, Comment: Additional information

3. Show profile, show profile for query num;

Second, SQL optimization

1. If a SQL occupies less than 5% of the total SQL, do not consider optimization, even in the optimization, can get how much return 2. Always drive large result sets with small result sets; 3. Complete the sorting in the index as much as possible; 4. Just take out the columns;5 you need. Use only the most effective filter conditions; 6. Avoid complex joins and subqueries as much as possible; 7. Use LIMIT 18 when only one row of data is used. Avoid SELECT *
9. If the data has a large backup, such as every month to clear the table without information, do not go to delete one time, a delete will make the database a huge amount of things, a moment to the database produced a lot of pressure, you can cycle every time you delete a point.
10, the Optimization union query, if not the system mandatory to repeat, do not use Union, with UNION ALL

Iv. Optimization of indexes

　　

1. If you do not start the search by the leftmost column of the index, you cannot use index 2, you cannot skip column 3 in the index, and if there is a query scope for a column in the index, the index to the right of it is invalidated
4. Simply cannot make a part of an expression, nor can it be a function parameter
5. You cannot skip columns in an index

7. The index cannot be used if the index is not started by the leftmost column
8. The best way to make a row is not to repeat.
9. Suitable for the length of the column is not too long
10. For the Long character column cannot do the full column index, should do the prefix straight-tempered
11. Try not to use multiple-column federated indexes

V. appropriate data types

I. Numeric type mysql supports numeric types in all standard SQL, including strict data types (integer,smallint,decimal,numberic), and approximate numeric data types (float,real,double presision ), and expand on this basis. The extension adds tinyint,mediumint,bigint to these 3 different lengths of shaping and adds a bit type for storing bit data. Integer type byte range (signed) range (unsigned) Purpose TINYINT 1 bytes (-128,127) (0,255) Small integer  Value SMALLINT 2 bytes (-32 768,32 767) (0,65 535) Large integer value mediumint 3 bytes (-8 388 608,8 388 607) (0,16  777 215) Large integer value int or integer 4 byte (-2 147 483 648,2 147 483 647) (0,4 294 967 295) Large integer value BIGINT 8 bytes (-9 233 372 036 854 775 808,9 223 372 036 854 775 807) (0,18 446 744 073 709 551 615) Maximum integer value FLOAT 4 bytes (-3.402 823 466 e+38,1.175 494 351 E-38), 0, (1.175 494 351 e-38,3.402 823 466 351 e+38) 0, (1.175 494 351 e-38,3.402 823 466 e+38) Single precision floating point value D  Ouble 8 bytes (1.797 693 134 862 315 7 e+308,2.225 073 858 507 201 4 E-308), 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) 0, (2.225 073 858 507 201 4 e-308,1.797 693 134 862 315 7 e+308) Double-precision floating-point numeric value decimal to decimal (m,d), if m>d, is m+2 otherwise the value of d+2-dependent m and D is dependent on the values of the M and d decimal int types: 5 major integer types supported in MySQL are tinyint,smallint,m Ediumint,int and BIGINT. These types are largely the same, except that the size of the values they store is not the same. MySQL extends the SQL standard in the form of an optional display width indicator, so that when retrieving a value from the database, it can be extended to a specified length. For example, specifying that a field is of type INT (6) guarantees that values with fewer than 6 contained numbers can be automatically populated with spaces when they are retrieved from a database. It is important to note that using a width indicator does not affect the size of the field and the range of values that it can store. In case we need to store a number that exceeds the permitted range for a field, MySQL is truncated and then stored according to the one end of the allowable range closest to it. One of the more special areas is that MySQL will automatically change to 0 before the non-compliant value is inserted into the table. The UNSIGNED modifier specifies that the field only holds positive values. Because you do not need to save the positive and negative symbols of the numbers, you can save a "bit" space at the time of storage. thereby increasing the range of values that this field can store. The Zerofill modifier specifies a value of 0 (not a space) that can be used to really complement the output. Use this modifier to prevent the MySQL database from storing negative values. The three floating-point types supported by the float, double, and decimal types are float, double, and decimal types. Float numeric types are used to represent single-precision floating-point numbers, whereas double numeric types are used to represent double-precision floating-point numbers. As with integers, these types also have additional parameters: a display width indicator and a decimal point indicator. For example, the statement FLOAT (7,3) specifies that the value displayed will not exceed 7 digits, followed by a 3-digit number after the decimal point. For the number of digits after the decimal point beyond the allowable range, MySQL automatically rounds it to the nearest value, and then inserts it. The DECIMAL data type is used in calculations that require very high precision, which allows you to specify the precision and count method of a numeric value as a selection parameter. The precision here refers to the total number of valid digits saved for this value, while the Count method represents the number of digits after the decimal point. For example, the statement decimal (7,3) specifies that the stored value will not exceed 7 digits and no more than 3 digits after the decimal point. Ignoring the precision and count method modifiers of the DECIMAL data type will cause the MySQL database to have all the words identified as this data typeThe segment precision is set to 10 and the calculation method is set to 0. The UNSIGNED and Zerofill modifiers can also be used by the FLOAT, DOUBLE, and DECIMAL data types. And the effect is the same as the INT data type. Two. String type MySQL provides 8 basic string types, namely CHAR, VARCHAR, BINARY, VARBINARY, BLOB, TEXT, ENUM set, and more.  can store ranges from simple one character to huge blocks of text or binary string data.      String type byte size description and storage requirements CHAR 0-255-byte fixed-length string VARCHAR 0-255-byte variable-length string Tinyblob     0-255 bytes of binary string not exceeding 255 characters tinytext 0-255 bytes Short text string BLOB 0-65535-byte binary form of long literal data Text 0-65535 bytes Long text data mediumblob 0-16 777 215 byte binary form medium length text data mediumtext 0-16 777 215 bytes Medium length paper                   This data Logngblob 0-4 294 967 295 byte binary form of maximum text data longtext 0-4 294 967 295 bytes Maximum text data VARBINARY (M) A fixed-length byte string that allows a length of 0-m bytes, the length of the value + 1 bytes BINARY (m) M allows a fixed length byte character string of 0-m bytes char and VARCHAR type char type for fixed length string, and must be in the Parentheses are defined in a size modifier. This size modifier ranges from 0-255. A value larger than the specified length will be truncated, and a value smaller than the specified length will be filled with a space. The CHAR type can use the BINARY modifier.   When used for comparison operations, this modifier causes CHAR to participate in the operation in binary mode, rather than in a traditional case-sensitive manner. A variant of the CHAR type is a VARCHAR type. It is a variable-lengthThe string type, and must also have an indicator with a range of 0-255. The difference between char and Varchgar is the way the MYSQL database handles this indicator: Char treats the size as a value, and a space is used to make up the case without the length. The VARCHAR type treats it as the maximum and stores the value using only the length that the string actually needs to be stored (adding an extra byte to store the length of the string itself). So a VARCHAR type that is shorter than the indicator length is not padded with spaces, but the value longer than the indicator will still be truncated. Because varchar types can dynamically change the length of stored values based on the actual content, the use of varchar type can greatly save disk space and improve storage efficiency when it is not possible to determine how many characters a field requires. The VARCHAR type is exactly the same as the CHAR type when using the BINARY modifier. The text and BLOB types require more than 255 field lengths, and MySQL provides text and blob two types. Depending on the size of the stored data, they all have different subtypes. These large data are used to store binary data types such as text blocks or images, sound files, and so on. The TEXT and BLOB types differ in classification and comparison. BLOB types are case-sensitive, while TEXT is not case-sensitive. The size modifier is not used for various blobs and TEXT subtypes. A value that is larger than the maximum range supported by the specified type is automatically truncated. Three. Date and time types when working with values for date and time types, MySQL has 5 different data types to choose from. They can be divided into simple dates, time types, and mixed date and time types. Depending on the accuracy required, subtypes can be used in each category, and MySQL has built-in functionality to turn a variety of input formats into a standard format.       Type size (byte) range format Purpose Date 4 1000-01-01/9999-12-31 yyyy-mm-dd Date value time 3 ' -838:59:59 '/' 838:59:59 ' HH:MM:SS time value or Duration 1 1901/2155 YYYY year value Date Time 8 1000-01-01 00:00:00/9999-12-31 23:59:59 yyyy-mm-dd hh:mm: SS mixed date and time value TIMESTAMP 4 1970-01-01 00:00:00/2037 YYYYMMDD HHMMSS Mixed date and time value, timestamp date, time, and TEAR type MySQL with date and TEAR types store simple date values, which are stored using the time type. These types can be described as strings or sequence of integers without delimiters. If the description is a string, the value of the DATE type should be separated by a hyphen, and the value of the time type should be separated by a colon as a delimiter. Note that the time type value without the colon delimiter will be understood by MySQL as the duration, not the timestamp. MySQL also maximizes the interpreter of the two numbers in the year of the date, or the two numbers entered in the SQL statement for the TEAR type. Because the values of all TEAR types must be stored in 4 digits. MySQL attempted to convert a 2-digit year to a value of 4 digits. Converts values within the range of 00-69 to 2000-2069. Converts the value within the 70-99 range to 1970-1979. If the MySQL auto-converted value does not meet our needs, please enter a 4-digit year. Dateyime and TIMESTAMP types in addition to the date and time data types, MySQL also supports both the Dateyime and TIMESTAMP hybrid types. They can store the date and time as a single value. These two types are typically used to automatically store timestamps that contain the current date and time, and can play a role in applications that need to perform a large number of database transactions and audit trails that require a debug and review purpose. If we have no definite assignment to a field of type TIMESTAMP, or we are assigned a null value. MySQL will automatically populate it with the current date and time of the system. Composite type MySQL also supports two composite data types, ENUM and SET, which extend the SQL specification. Although these types are technically string types, they can be treated as different data types. An ENUM type allows only one value to be obtained from a collection, whereas a set type allows any number of values to be taken from a collection. The enum type enum type is somewhat similar to a single option because it allows only one value to be obtained in the collection. It's easy to understand when dealing with data from each other, such as human sexuality. The ENUM Type field can take a value from the collection or use a null value, otherwise the input will cause MySQL to insert an empty string in this field. In addition, ifThe casing of the inserted value does not match the case of the value in the collection, and MySQL automatically converts the casing of the inserted value to a value that is consistent with the case in the collection. The ENUM type can be stored as a number inside the system, and it is indexed with numbers starting from 1. An ENUM type can contain up to 65,536 elements, one of which is retained by MySQL to store the error message, which is represented by index 0 or an empty string. MySQL considers that the value that appears in the ENUM type collection is a valid input, except that any other input will fail. This means that it is easy to find the location of the error record by searching for a row that contains an empty string or a corresponding numeric index of 0. The set type set type is similar to, but not identical to, the ENUM type. A set type can take any number of values from a predefined collection. And the same as the ENUM type, any attempt to insert a non-predefined value in the SET Type field causes MySQL to insert an empty string. If you insert a record that has a valid element and an illegal element, MySQL retains the legitimate element, removing the illegal element. A SET type can contain up to 64 elements. The value in the SET element is stored as a separate "bit" sequence, which represents the element corresponding to it. A bit is a simple and efficient way to create a collection of ordered elements. And it also removes duplicate elements, so it is not possible to include two identical elements in a SET type. To find illegal records from the SET type field, simply look for rows that contain an empty string or binary value of 0. 1. Using ENUM instead of the Varchar,enum type is very fast and compact. In fact, it holds the TINYINT, but it appears as a string on its appearance. In this way, using this field to make a list of options becomes quite perfect. If you have a field such as "gender", "Country", "nation", "state" datetime,date and timestamp these three kinds. In terms of storage space, timestamp has a minimum of four bytes, while the other two data types are eight bytes, one more times. The disadvantage of timestamp is that he can only store time since 1970, while the other two types of time can be stored as early as 1001. If there is a need to store the time before 1970, we must discard the timestamp type, but as long as we do not need to use the time 1970 years ago, it is best to use timestamp to reduce storage space consumption.

Six , scalability design of the sub-table/read/write separation

　　1. Sub-table

Vertical slicing can clear the module division, distinguish governance, horizontal segmentation can solve the large data volume performance bottleneck problem, so often use the combination of the two, which is a common strategy case in the large-scale website: In the case of MySQL, The simple shopping system is set up in the following table: 1. Product table (data volume 10w, Stable) 2. Order table (data volume 200w, and growth trend) 3. User table (data volume 100w, and growth trend) for MySQL as an example of the horizontal split and vertical split, MySQL can tolerate an order of magnitude in millions of static data can go to tens of thousands of vertical splits: Solve the problem: the IO competition between table and table does not solve the problem: the pressure scheme of increasing the amount of data in a single table: placing the Product table and the user table on a server The Orders table is placed on a single server horizontally split: Solve the problem: The pressure does not solve the problem: the IO contention between table and table: User table through gender split into male user table and female user form order form completed and completed by split into completed orders and unfinished orders product table Unfinished order placed on a server completed order Form box male user table put a server on a female user table put on a server (women's love Shopping)

　　
　　
2. Read/write separation

Can Baidu reference

Vii. Scalability Design Cache with the Search the Use

Viii. scalable design using memory disks

The reliability of the infrastructure is now very high, such as EC2 almost no need to worry about server hardware. And the memory is really cheap, it is easy to buy dozens of g of memory of the server, you can use the memory disk, regular backup to disk. Migrating the MYSQL directory to a 4G memory disk mkdir-p/mnt/ramdisksudo mount-t tmpfs-o size=4000m Tmpfs/mnt/ramdisk/mv/var/lib/mysql/mnt/ram Disk/mysqlln-s/tmp/ramdisk/mysql/var/lib/mysqlchown mysql:mysql MySQL

Reference article:
Http://www.cnblogs.com/luxiaoxun/p/4694144.html
Http://www.jb51.net/article/82254.htm
Http://www.jb51.net/article/55849.htm

"MySQL": MySQL performance optimization summary