Excerpt MySQL Related information

1,myisam on disk, store the data as 3 files. The first is a table structure file whose name starts with the name of the table, the. frm file, the second file is a data file with a. MYD extension, and the third is an index file with an extension of. MYI.
The MyISAM storage engine is characterized by table-level lock, non-transactional and full-text indexing, which is suitable for some CMS content management system as a back-end database, but in large concurrent, heavy-duty production systems, the table lock features appear to be inadequate.
2,innodb provides a transaction-safe (acid-compatible) storage engine that commits, rolls back, and crashes resiliency. Row locks. Support for foreign keys (FOREIGN key). Designed for maximum performance when dealing with huge amounts of data. It may be CPU-efficient that other disk-based relational database engines cannot match. InnoDB stores its tables and indexes in a table space. The InnoDB table can be of any size, even on an operating system with a file size limit of 2GB.
3,myisam table-level lock, lock block, no deadlock, locking granularity is high, the probability of lock conflict is highest, the concurrency is the lowest.
InnoDB Row-level lock, locking slow, deadlock, locking granularity is minimal, the probability of the collision of the lock is the lowest, the concurrency is the highest.
4, the read operation on the MyISAM table (read lock tablename Read) does not block other processes from reading requests to the same table, but blocks write requests to the same table. Write operations for other processes are performed only when the read lock is released.
writes to MyISAM (write locks) block other read and write operations on the same table today, and read and write operations are performed by other processes only after the write lock is released.
The 5,innodb Storage Engine room is implemented by locking the index entries on the index, which means that the InnoDB uses row-level locks only if the data is retrieved by index criteria, otherwise, table locks are used.

myisam and InnoDB:
MyISAM non-transactional security,　　The InnoDB is transaction-safe, which is the acid transaction support;
MyISAM is a table-level lock with minimal lock overhead, while InnoDB supports row-level locking, large lock management overhead, and support for better concurrent write operations;
MyISAM supports full-text indexing, and InnoDB does not support full-text indexing, but is supported in the latest version 5.6;
MyISAM is relatively simple and easy to manage, so the efficiency is better than INNODB, small applications can consider using MyISAM;
MyISAM in the form of a file, the use of MyISAM storage in cross-platform data transfer can save a lot of trouble; InnoDB uses tablespaces to manage data;

Myisam the read lock of the storage engine is mutually exclusive to the write lock, and the read-write operation room is serial. Imagine a process requesting a read lock on a MyISAM table, and another process requesting a write lock on the same table, how does mysql handle it? The answer is that the write process obtains the lock first, and not only that, even if the read request goes to the lock waiting queue, and the write request is written, the write lock is inserted before the read lock request, because MySQL considers that writing the request is generally more important than the read request. This is also why the MyISAM table is not suitable for a large number of update operations and query operation applications. Because a large number of update operations can cause query operations to be difficult to obtain read locks. Thus may be blocked forever.
Innodb for transactional applications with many features, including support for acid transactions, Row locks, and so on. If you need to perform a large number of read and write operations in your application, you should use InnoDB, which can improve the performance of multiple user concurrency operations. For the Myiam engine, in the mysql5.5 version, Oracle has very little support, and in the future the memory database is a trend, so it is recommended that the InnoDB engine be selected first.

To turn on slow logging:
In the MY.CNF configuration file, add the following parameters:
Slow_query_log = 1
Slow_query_log_file = Mysql.slow
Long_query_time = 2;//2s above sql is logged
Use the Mysqldumpslow command to remove the top 10 slow SQL for good Things
Mysqldumpslow-s t-t Slow.log

SQL statement Optimization:
1, try to avoid using subqueries, use a linked table instead
2,like "%xxx%" is not available for indexing
a sentence: select COUNT (1) from table where name is like '%xxx% ';
b Sentence: select COUNT (1) from table Table_alias join
(select key from table where name like '%xxx% ') table_alias_2 on a.key = B.key
b sentence is more efficient than sentence a.
3,limit Paging Optimization
a sentence: SELECT * from tablename limit 99999,10;
b Sentence: SELECT * FROM tablename ORDER by key limit 99999,10;
C Sentence: SELECT * from tablename where key > 999999 order by key limit 99999,10;
C > B > A;

a sentence: SELECT * FROM tablename ORDER BY createdata ASC limit 332344,10;
optimization idea: First take out 332344 lines after the record ID, and then use the method of internal connection to remove 10
b Sentence: Select a.* from TableName a
Join
(SELECT ID from tablename ORDER by createdata ACS limit 332344,1) b
on
a.id >= b.id
limit ten;
4,or Conditional Statement optimization
The TMP table has 2 fields, name,age are indexed
SELECT * FROM tmp where age = or name = ' xxx ';
is not used to any index, optimization (with UNION ALL):
SELECT * FROM tmp where age =
Union All
SELECT * FROM tmp where name = ' xxx ';
5,where After you use SQL functions for fields after a condition, the index cannot be used
6,using Filesort Optimization method, that is, the sorting field, join the joint index, if it is only a single field sorting, then this field with the primary key to establish a federated index, if there are other conditions, then with that condition to establish a federated index. (Order by; GROUP by;)

MY.CNF optimization
Corresponding to the per_thread_buffers, it can be understood as the oracal PGA, for each user connected to MySQL allocated memory, which contains the following parameters:
1,read_buffer_size

This parameter is used for sequential scans of the table, indicating the buffer size allocated for each thread. For example, when the full table is scanned, MySQL will read the data in the order in which the data is stored sequentially, each time the data block will be temporarily read_buffer_size, when the buffer space is full or all the data is read, The data in buffer is then returned to the upper-level caller for increased efficiency. Default 128KB, this parameter is not set too large, generally in 128kb-256kb

2,read_rnd_buffer_size

This parameter is used for random reads of the table, which represents the buffer size allocated for each thread. For example, in a non-indexed field to do the order by sort operation, the buffer will be used to stage the read data, the default 256KB, this parameter is not set too large, generally in the 128-256kb
3,sort_buffer_size

When a table is ordered by, the group by sort operation, because the sorted field does not have an index, a using filesort appears, in order to improve performance, this parameter can be used to increase the buffer size allocated by each thread, the default 2Mb, this parameter does not set too large, generally in 128-2 56kb. In addition, when using Filesort is generally present, it should be resolved by increasing the index.
4,thread_stack
This parameter represents the stack size of each thread, the default 192kb, and if it is a 64-bit operating system, set to 256KB, not too large.
5,join_buffer_size
If the associated field does not have an index when the table is performing a join operation, the Using join buffer appears, in order to improve performance, this parameter can be used to increase the buffer size allocated per thread, default 128kb, not too large, generally between 128-256kb. In general, using join buffer is used to solve the problem by increasing the index.
6,binlog_cache_size
in general, if there are no large transactions in the database, writing is not particularly frequent and setting it to 1-2MB is an appropriate choice. If there is a large transaction, the buffer value can be increased appropriately.
7,max_connectons
the maximum number of connections, default 100, is generally set to 521-1000.

global_buffers Optimization:
The corresponding gblobal_buffers, which can be understood as Oracle's SGA, is used to cache data blocks retrieved from data files in-memory, which can greatly improve the performance of querying and updating data. The main parameters are as follows:
1,innodb_buffer_pool_size
This parameter is the core parameter of InnoDB storage engine, the default 128MB, this parameter is set to the 60%-70% of physical memory;
2,innodb_additional_mem_pool_size
This parameter is used to store data dictionary information and additional internal data structures. The more tables you have, the more memory you need to allocate here. If the InnoDB runs out of memory in this pool. InnoDB begins to allocate memory from the operating system and writes a warning message in the Wang MySQL error log. The default is 8M, which is generally set to 16M
3,innodb_log_buffer_size
the buffer used by the transaction log. InnoDB write the transaction log again, in order to improve performance, the information is written to InnoDB log buffer, and when the corresponding conditions of the Innodb_flush_log_trx_commit parameter lock setting are met, the log is written to the file. The default is 8MB and is generally set to 16-64MB.
4,key_buffer_size
This parameter is used to cache the index parameters of the MyISAM storage engine. mysql5.5, the default is the InnoDB storage engine, so this parameter can be set to lowercase, 64M
5,query_cache_siez
Parameters for caching SELECT statements and result set sizes
Note that the Per_thread_buffers memory setting + global_buffers setting cannot be greater than the actual physical memory, or the concurrency is high, resulting in content overflow and system panic.

Excerpt MySQL Related information