MySQL tuning and bit-by-bit recording

Efficiency Comparison Between select count (*) and select count (COL) (refer to the http://hi.baidu.com/wrjgg/item/f5b823b4e1dca79918469729 ):

1. Select count (*) from tablename is the optimal choice under any circumstances;

2. Minimize select count (*) from tablename where Col = 'value' queries;

3. prevent the appearance of select count (COL) from tablename

1. when multiple columns are combined as the unique key, note that if Columna, columnb, and columnc are combined as the unique key, do not "can be empty ", if Columna can be empty, multiple Columna records with null columns, columnb records, and columnc records are equal (no error is reported in the database)

2. Run -- log-Slow-queries -- long-query-time = 2 to view slow queries. Then use the explain statement to analyze the query and make optimization (log_queries_not_using_indexesRecords queries with no indexes used)

3. Try to avoid complex select queries on frequently updated tables to avoid read/write conflicts related to locked tables.

4. Try not to make the column null, which is not conducive to MySQL optimization.

5. indexes are particularly important for applications that take up most of queries. In many cases, the performance problem is very simple because we forget to add an index, or we have not added a more effective index.

If no index is added, a full table scan is performed to search for any specific data. If the data volume of a table is large and the results meet the requirements are few, if you do not add an index, it will cause fatal performance degradation.

However, it is not always necessary to create an index. For example, there may be only two values for gender. Creating an index not only has no advantages, but also affects the update speed. This is called excessive index.

6. Key points for indexing:

1> Compound Index

For example, there is a statement like this: Select * from users where area = 'beijing' and age = 22;

If we create a single index on the area and age respectively, MySQL queries can only use one index at a time. Therefore, although full table scan improves the efficiency compared with no index, however, creating a composite index on the "area" and "Age" columns will increase the efficiency. If we create a composite index (area, age, salary), it is equivalent to creating (area, age, salary), (area, age), (area) three indexes, which are called the best left prefix feature. Therefore, when creating a composite index, we should place the columns that are most commonly used as restrictions on the leftmost and decrease in turn.

2> the index does not contain columns with null values.

As long as a column contains a null value, it will not be included in the index. If a column in the composite index contains a null value, this column is invalid for this composite index. Therefore, do not set the default value of a field to null during database design.

3> use short Indexes

Index a string or column. If possible, specify a prefix length. For example, if a CHAR (255) Column exists and multiple values are unique within the first 10 or 20 characters, do not index the entire column. Short indexes not only increase query speed, but also save disk space and I/O operations.

4> Sorting index

Mysql queries only use one index. Therefore, if an index is already used in the where clause, columns in order by will not use the index. Therefore, do not use the sorting operation when the database's default sorting can meet the requirements. Try not to include the sorting of multiple columns. It is best to create a composite index for these columns if necessary.

5> like statement operation

Generally, like operations are not encouraged. If they are not usable, how to use them is also a problem. Like "% aaa %" does not use indexes, but like "aaa %" can use indexes.

6> do not perform operations on columns

Select * from users where YEAR (adddate )...

7> do NOT use NOT IN

Not in does NOT use indexes to scan the entire table. Not in can be replaced by not exists.

7. the difference between a primary key and a unique index (django1.4 does not support composite primary key, and some extension packages provide this function, however, the so-called "Composite primary key" they created is actually a unique index at the database level, so they are somewhat confused about these two concepts)

A primary key is a constraint, and a unique index is an index. The two are essentially different.

A primary key must contain a unique index. A unique index is not necessarily a primary key.

The unique index column allows null values, while the primary key column does not.

When a primary key column is created, the default value is non-null + unique index.

A primary key can be referenced as a foreign key by other tables, but a unique index cannot.

A table can only create one primary key, but multiple unique indexes can be created.

Primary keys are more suitable for unique identifiers that are not easy to change, such as auto-incrementing columns and ID card numbers.

In RBO mode, the execution plan priority of the primary key is higher than that of the unique index. The two can increase the query speed.

8. modifying indexes in innodb tables (verified indexes are deleted) will be slow, and myisam tables will be fast (show processlist traces the process: copy to tmp table; repair by sorting; rename table)

9. innodb table data is not in the mysql data directory: mysql data directory only has a very small (about a dozen KB) frm file, no MYD file and MYI file (http://dev.mysql.com/doc/refman/5.0/en/innodb-configuration.html)

InnoDBCreates tablespace files in the MySQL data directory by default. To specify a location explicitly, useinnodb_data_home_dir
Option. For example, to use two files namedibdata1Andibdata2But create them in/ibdataDirectory, configureInnoDBLike this:

[mysqld]innodb_data_home_dir = /ibdatainnodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

10. select * from a left join B on. id = B. ID; left join. The result is. *, B. * display, left side (. *) reserved, right side (B. *) Fill in with null

Select * from a right join B on. id = B. ID; right join. The result is. *, B. * display, left side (. *) Fill in with null, right side (B. *) Reserved

Select * from a inner join B on A. ID = B. ID; Inner join (equal join), equivalent to select * from a, B where a. ID = B. ID;

11. select * from a where. id> 1 and. ID <10; select * from a where. ID <10 and. id> 1; the latter is more efficient, because id <10 reduces the scope, the entire execution will be faster

12. mySQL partitions and table shards (merge storage engine or business implementation) have different focuses. The focus of table shards is on how to improve MySQL concurrency when accessing data. What about partitions, how to break through the read/write capability of the disk to improve MySQL performance. Http://blog.51yip.com/mysql/1029.html

Table sharding: Business-level control table sharding, merge storage engine table sharding http://blog.51yip.com/mysql/949.html

Partition: range partition, list partition, hash partition, key partition (KEYTakes only a list of one or more column names. Beginning with MySQL 5.1.5, the column or
Columns used as the partitioning key must comprise part or all of the table's primary key, if the table has one. http://dev.mysql.com/doc/refman/5.1/en/partitioning-key.html), subpartition
Http://blog.51yip.com/mysql/1013.html

. /Configure -- help | grep-A 3 partitionmysql> show variables like "% Part % "; + ------------------- + ------- + | variable_name | value | + ------------------- + ------- + | have_partitioning | Yes | + ----------------- + ------- + 1 row in SET (0.00 Sec) range partition: create Table if not exists 'user' ('id' int (11) not null auto_increment comment 'user id', 'name' varchar (50) not null default ''comment' name', 'sex 'I NT (1) not null default '0' comment '0 is male, 1 is female, primary key ('id ')) engine = MyISAM default charset = utf8 auto_increment = 1 partition by range (ID) (partition P0 values less than (3), partition P1 values less than (6 ), partition P2 values less than (9), partition P3 values less than (12), partition P4 values less than maxvalue); List partition: (if there is a primary partition, the primary key must be in the partition, or an error will be reported. Generally, a table must have a primary key. This is a partition limitation .) Create Table if not exists 'list _ part' ('id' int (11) not null comment 'user id', 'vince _ id' int (2) not null default 0 comment 'province ', 'name' varchar (50) not null default ''comment' name', 'sex' int (1) not null default '0' comment '0 is male, 1 is female ') engine = InnoDB default charset = utf8 partition by list (province_id) (partition P0 values in (1, 2, 3, 4, ,), partition P1 values in (,), partition P2 values in (,), partition P3 values in (, 22 ));

According to official documents and personal tests, MySQL merge seems to have the following problems)

When data is inserted to a table after merge, data can only be inserted into one table without even distribution. Therefore, data is inserted directly to the child table at the business level. You can optionally specify an insert_method option to control how inserts into the MERGE table take place. Use a value of first or last to cause inserts to be made in the first or last underlying table,
Respectively.

Data in different child tables of merge cannot guarantee uniform unique constraints. Therefore, you must control the data in the business layer.

13. Like Fuzzy search efficiency is very low (traverse all records), you can use full text index Replace: http://db.090.net/2012/03/mysql%E5%85%A8%E6%96%87%E7%B4%A2%E5%BC%95/

14. During the query, we had to accurately provide data that matches the column type for retrieval. We do not want to use MySQL to intelligently check and convert the data we provide.

mysql> show create table wom_account\G*************************** 1. row ***************************       Table: wom_accountCreate Table: CREATE TABLE `wom_account` (  ...  `provider` smallint(6) NOT NULL,  `uid` varchar(64) NOT NULL, UNIQUE KEY `provider` (`provider`,`uid`),) ENGINE=MyISAM AUTO_INCREMENT=2518615 DEFAULT CHARSET=utf81 row in set (0.00 sec)mysql> explain * FROM wom_account WHERE provider=1 AND uid IN ('2840978914');+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+| id | select_type | table       | type  | possible_keys | key      | key_len | ref         | rows | Extra |+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+|  1 | SIMPLE      | wom_account | const | provider,uid  | provider | 196     | const,const |    1 |       |+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+1 row in set (0.00 sec)mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid IN (2840978914);  +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows   | Extra       |+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+|  1 | SIMPLE      | wom_account | ref  | provider,uid  | provider | 2       | const | 489995 | Using where |+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+1 row in set (0.00 sec)mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = 2840978914;   +----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+| id | select_type | table       | type | possible_keys | key      | key_len | ref   | rows   | Extra       |+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+|  1 | SIMPLE      | wom_account | ref  | provider,uid  | provider | 2       | const | 489995 | Using where |+----+-------------+-------------+------+---------------+----------+---------+-------+--------+-------------+1 row in set (0.00 sec)mysql> explain SELECT * FROM wom_account WHERE provider=1 AND uid = '2840978914';+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+| id | select_type | table       | type  | possible_keys | key      | key_len | ref         | rows | Extra |+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+|  1 | SIMPLE      | wom_account | const | provider,uid  | provider | 196     | const,const |    1 |       |+----+-------------+-------------+-------+---------------+----------+---------+-------------+------+-------+1 row in set (0.00 sec)

15. The distinct column duplicates the entire record row consisting of columns from the SELECT statement.

mysql> select a,b from x where a = 1 or b = 1;         +------+------+| a    | b    |+------+------+|    1 |    1 ||    1 |    2 ||    1 |    2 ||    1 |    3 ||    1 |    3 ||    2 |    1 ||    2 |    1 ||    3 |    1 ||    3 |    1 |+------+------+9 rows in set (0.00 sec)mysql> select distinct a,b from x where a = 1 or b = 1;+------+------+| a    | b    |+------+------+|    1 |    1 ||    1 |    2 ||    1 |    3 ||    2 |    1 ||    3 |    1 |+------+------+5 rows in set (0.00 sec)

16. perform union on the query results

Yesterday (2013.3.7), I met a user who interacted with 1 from the interactive Table x: the initial solution is to use the results of "select B from x where a = 1" and "select a from x where B = 1" to calculate the statistics after the memory is duplicated, it can be imagined that data transmission is time consuming.

The improved method is to use union to join the query results. In this way, the big data results produced by the two subqueries must be merged, but the network transmission time is reduced, in addition, the sorting and sorting performed by Mysql must be faster than the business layer.

Union is union distinct by default.

mysql> select b from x where a = 1 ;+------+| b    |+------+|    1 ||    2 ||    2 ||    3 ||    3 |+------+5 rows in set (0.00 sec)mysql> select a from x where b = 1 ;  +------+| a    |+------+|    1 ||    2 ||    3 ||    2 ||    3 |+------+5 rows in set (0.00 sec)mysql> select b from x where a = 1 union select a from x where b = 1;+------+| b    |+------+|    1 ||    2 ||    3 |+------+3 rows in set (0.01 sec)mysql> select b from x where a = 1 union all select a from x where b = 1;+------+| b    |+------+|    1 ||    2 ||    2 ||    3 ||    3 ||    1 ||    2 ||    3 ||    2 ||    3 |+------+10 rows in set (0.00 sec)

The following reference http://www.tyhs.net.cn/article.asp? Articleid = 64

Http://blog.csdn.net/maria57/article/details/2963410 (this article analysis is very detailed, innodb and myisam different engine settings are also described)

Index

1) MySQL only uses the prefix, such as key (a, B )... Where B = 5 will not use the index.

2) Select an index. Using indexes on columns with few changes is not very good, such as gender columns.

3) define the Unique index in the Unique column.

4) Avoid creating indexes that cannot be used.

5) in the Btree index (InnoDB uses Btree), you can create an index on the columns to be sorted.

6) Avoid duplicate indexes.

7) avoid creating an index on the prefix of an existing index. For example, if index (a, B) exists, remove index ().

8) control the length of a single index. Use key (name (8) to index the first few characters of the data.

9) The shorter the key value, the better. integer is recommended.

10) using indexes in queries (using explain for viewing) can reduce the number of disk reads and accelerate data reading.

11) Similar key values are better than random values. Auto_increment is better than UUID.

12) Optimize table can compress and sort indexes, so do not run them frequently.

13) analyze table can update data.

14) If a string-type column with an index is used for a query (where columnname = 100), the query will not be used. When the data volume is large, the query will be very slow.

Server Settings Optimization

The default MySQL settings have poor performance, so some parameter adjustments are required. This section describes some common parameter adjustments and does not involve specific storage engines (mainly MyISAM and InnoDB)

-- Character-set: for a single language, use a simple character set, such as latin1. Use less Utf-8, UTF-8 occupies more space.

-- Memlock: locking MySQL can only run in the memory to avoid swapping. However, if the memory is insufficient, an error may occur.

-- Max_allowed_packet: It must be large enough to adapt to relatively large SQL queries, which has no significant impact on performance, mainly to avoid packet errors.

-- Max_connections: The maximum connection allowed by the server. If it is too large, the out of memory will appear.

-- Table_cache: the number of tables that MySQL keeps open at the same time. The overhead of opening a table is relatively large. Generally, the value is 512.

If you find that open_tables is equal to table_cache and opened_tables is growing, you need to increase the value of table_cache (you can use show
Status like 'open % tables)

-- Query_cache_size: memory size used for cache query.

Using the Query Buffer, MySQL stores the SELECT statement and query result in the buffer. In the future, the same SELECT statement (case sensitive) will be read directly from the buffer.

According to the MySQL user manual, query buffering can achieve a maximum efficiency of 238%.
Check the STATUS value Qcache _ * to check whether the query_cache_size setting is reasonable (the preceding STATUS value can be obtained using show status like 'qcache % ).

If the Qcache_lowmem_prunes value is very large, it indicates that the buffer is often insufficient. If the Qcache_hits value is also very large, it indicates that the query buffer is frequently used. In this case, you need to increase the buffer size;

If the Qcache_hits value is small, it indicates that your query repetition rate is very low. In this case, the Query Buffer will affect the efficiency, so you can consider not to use the query buffer.

In addition, adding SQL _NO_CACHE to the SELECT statement explicitly indicates that no Query Buffer is used.
Parameters related to query buffering include query_cache_type, query_cache_limit, and query_cache_min_res_unit.

Query_cache_type specifies whether to use the Query Buffer. It can be set to 0, 1, and 2. This variable is a SESSION-level variable.

Query_cache_limit specifies the buffer size that can be used by a single query. The default value is 1 MB.

Query_cache_min_res_unit is introduced after version 4.1. It specifies the minimum unit for allocating the buffer space. The default value is 4 K.

Check the status value Qcache_free_blocks. If the value is very large, it indicates that there are many fragments in the buffer. This indicates that the query results are relatively small. In this case, reduce query_cache_min_res_unit.

-- Datadir: the root directory for mysql to store data, which is separated from the installation file on a different disk to improve performance.

-- Key_buffer_size: Specifies the size of the index buffer, which determines the index processing speed, especially the index read speed. Check the status values Key_read_requests and Key_reads to check whether the key_buffer_size setting is reasonable.

The ratio of key_reads/key_read_requests should be as low as possible, at least and (the above STATUS values can be obtained using show status like 'key _ read % ).

Key_buffer_size only applies to the MyISAM table. This value is used even if you do not use the MyISAM table, but the internal temporary disk table is a MyISAM table. You can use the check status value created_tmp_disk_tables to learn the details.

For machines with 1 GB memory, if the MyISAM table is not used, the recommended value is 16 M (8-64 M ).

Key Points of MyISAM Optimization

1) The declared column is not null, which can reduce disk storage.

2) use optimize table for fragment and free space recovery. Note that it only runs after a very large data change.

3) When Deleting/updating/adding has a large amount of data, index is forbidden. Use alter table t disable keys.

4) set myisam_max _ [Extra] _ sort_file_size to be large enough to significantly increase the repair table speed.

Meeting records during SQL optimization in the project: ---> Start

Avoid subqueries as much as possible, because the results of subqueries are saved as temporary tables, while temporary tables cannot be indexed.

Temporary tables can only be filtered row by row

The following two statements have the same effect ("inner join" does not have the syntax of "Left" or "right", which is "inner join ")

Select * from a inner join B on A. Y = B. Y;

Select * from a, B where A. Y = B. Y;

"Left join" default is "left Outer Join" (outer, not out)

Left Outer Join. If a left out join B, if the record existing in a does not exist in B, the value of the corresponding column in B is null, select the null column in column B to select the result.

Multiple connection conditions: http://dev.mysql.com/doc/refman/5.0/en/join.html

Select * from T1 left join (T2, T3, T4) on (t2.a = t1.a and t3. B = t1. B and t4.c = t1.c)

View the database version:

Select version ();

Select @ version;

High. performance. MySQL (high-performance MySQL): 1, 4, 5, 6, 7

1. client-server structure

One client connects to the server and is processed by one thread (5.5 optimized: thread pool, one thread can serve multiple clients)

Query cache (SQL statement cache), Parser

Read/write lock (write lock hunger: write operations cannot get the lock due to successive read operations. The solution is to serialize the request lock)

2. Transactions and ACID (atomicity, consistency, isolation, and durability)

Isolation level:

Read uncommited: reads uncommitted data (dirty data) with the lowest isolation level

Read commited: READ-only data committed by another user (the data can be updated between two times of the same READ in the same transaction)

Repeatable read, which can be re-READ (the data READ twice is the same, but no one can update the data row between the two identical reads in the same transaction, but new rows may be added) (phantom read, phantom read, range select, find new records)

T1:

Read R1

... TN: Update r1 (n) insert R2 (y)

Read r1

Serializable, serialization

T1:

Read r1

... TN: Update r1 (n) insert R2 (N)

Read r1

3. MVCC

R1: Data (insert version; Delete Version)

R1

R2

R3

...

Rn

Insert version <= current_version

Delete version = undifined OR delete version> current_version

4. show table status like 'user'; view the TABLE user STATUS

5. select count (*) from a; when there is no where condition and the engine is MYISAM, the speed is very fast, and the data table has a field to track the number of rows.

Select count (id) from a; count the number of rows whose id is not null

6. We recommend that you use Oracle InnoDB to replace InnoDB In the open-source community (the former is used by default after 5.5)

7. varchar (5) is five characters, not five bytes (5 characters may be 15 bytes ...)

8. MYISAM is slow to fix because no logs need to be scanned for the entire file; MYISAM indexes are compressed and smaller; Table-level locks

INNODB supports hot standby, transactions, fast recovery, and row-level locks.

9. program performance bottleneck: CPU disk I/O memory network I/O

10. create table B like a; create a new table B with the same structure as

11. Using B + tree storage index instead of Binary Tree: optimized disk read/write

12. Clustered index (Clustered Indexes): the data and index exist together, and the data is found after the index is found.

Inno DB supports clustered indexes, but only supports clustered indexes on primary keys.

InnoDB: (if the primary key is large, the index file will be large, occupying the memory space and disk space. The primary key should be generated in ascending order instead of randomly)

Primary Key: index + Data

Second index: index + primary key

MyISAM:

Primary Key: index + pointer to data

Second index: index + pointer to data

13. If an index is set for the order by column, the data read from the disk will become ordered and no sort is required in the memory.

14. Replace

Insert into T values (...) on duplicate key update...

15. Union and Union all: it is best to use the latter when there is no repetition (no sorting or deduplication, fast)

Meeting records during SQL optimization in the project: End <---

Mysl tips:

1. MySQL supports regular query. For example, the following statement counts the number of records of wom_account_data_sina.uid non-pure numbers.

Select count (*) from wom_account_data_sina where uid not regexp '^ [0-9] + $ ';

2. See another article on master-slave replication.

Http://blog.csdn.net/xiarendeniao/article/details/10714283