Mysql optimization and usage highlights

MyISAMReadBetter performance than Innodb

MyISAM indexes and data are separated, and the indexes are compressed.

Innodb is closely bound to indexes and data. Without compression, Innodb is much larger than MyISAM.

MyISAM does not support Innodb

MyISAM does not support transaction Innodb support

MyISAM only supports row locks supported by table Innodb

The Storage Method for Data Information is different. MyISAM creates a table corresponding to three files, while Innodb only has one file. frm, and the data is stored in ibdata1.

Copy your own insert into tt select * from tt

--------------------------

Restore

When I accidentally update a table with the wrong where write range, this table cannot be used normally, and the superiority of MyISAM is displayed, take the file of the corresponding table from the compressed package copied on the current day, put it in a database directory, dump it into SQL, export it back to the master database, and add the corresponding binlog. If it is Innodb, I am afraid it cannot be so fast

--------------------------

Lock table

Select count (*) and order by are the most frequent operations, which may account for more than 60% of the total SQL statements. Innodb actually locks the table, many people think that Innodb is a row-Level Lock, that is, where is only valid for its primary key, and non-primary keys will lock the entire table.

-------------------------

Size

Ensure that a single database instance should not exceed 150 GB.

Due to file system operation restrictions, more file handles are required for a large number of files, and large directory operations result in low copy, compression, and backup efficiency.

-Open the table to occupy database resources (table_cache)

√ It is recommended that a database should not exceed-tables

√ It is recommended that tables with char fields should not exceed 5 million rows. Tables based on numeric fields should not exceed 10 million rows.

Split as many small instances as possible. One machine runs 7-8 instances. It is reasonable to load avg no more than 1-2 and peak no more than 6-7.

-------------------------

Master/Slave

By dividing multiple master databases for future scalability

Replicate_do_db (table) is used to solve the problem that the latency of the master database from the slave database is long. Because the slave database of mysql can only be traced by a single process, multiple processes can be created to catch up with different databases to reduce latency. The disadvantage is that the management cost will be high.

---------------------------

Multiple IDCs

Improve database platform stability by 99.999% through multiple IDCs

---------------------------
Table sharding
By Time (Finance)
Hash by ID (Unified pass)
By Business Project (general vote)

Merge Engine

Faster code development
1.
For example, for some projects, users need to regularly store offline messages. You can use a program to access only the corresponding merge table, and then the merge table corresponds to seven sub-tables (such as Monday to Sunday ).
2.
For example, for a statistical item, the table sharding policy may be one table per month, and the statistics for the first and second quarters are as follows:
To facilitate development, you can use a program to access only the corresponding merge table, and then freely combine tables 1234 and N as the child tables of the merge table.

---------------------------

Index

Use indexes correctly to avoid using fixed-length tables for full TABLE search, and regularly run the optimize table command (note that this command will lock the TABLE, please do it when the database access is small) when you add an index to a large table, you must select a small period of access. Otherwise, it may cause serious problems.

Note: Generally, the access to most projects is low at AM.

Index optimization, select lab
Improved steadily
Copy the tables to be optimized to the test environment.
Start a test daemon in the test environment, disable the query cache, or use the select SQL _NO_CACHE method.
When not optimized, test several query times and explain check Scan set.
Select an appropriate index test. Use index (xx) can be used forcibly. Check whether it is valid.
Test the query time change and repeat the test to obtain the optimal result.

Keep an eye on and change the index settings as needed

Use different index modes from the database to improve performance
For example, some projects have many different sorting requirements.
A lot of indexes need to be created, but adding them will inevitably lead
Can be decreased, so use different functions to use the corresponding index
From the database.

-------------------------------------

Sort

Try to use fields with primary keys for order by sorting. Try not to provide more page searches (preferably only within 100 pages) to prevent crawlers from crawling data, resulting in high database load. Because order by field1 limit xxxxxx is executed, 20 is very resource-consuming. ------------------------------------- BatchThe insert batch mode is used to increase the write speed of the master database, and the Write Performance of the master database can be improved through the batch values mode. -------------------------------------- Key-valueA simple key-value database is used to process simple logic services, such as berkeley DB, LightCloud, and Tokyo Tyrant ------------------------------------ NoSQLUse Memcache to buffer databases with frequent updates
For example, you can set the threshold value to 500 times to write data to the database or write data to the database every 30 minutes. ------------------------------------- PatchThe heap patch developed by ebay is used to solve some problems such as session services, such as running some data, but the total size is not very large, but the update is very frequent, such as the user status value, the advantage of the patch is that the memory is saved. ------------------------------------ MonitoringProcess List mysql> show processlist; ProfilingThis method is disabled by default. You can use the following statement to view
Mysql> select @ profiling;
Mysql> set profiling = 1; // open
Execute the SQL statement to be tested: mysql> show profiles;
Query the execution information of a specified SQL statement using the specified Query_ID:
Mysql> show profile for query 1;
After the test is completed, close the parameter: mysql> set profiling = 0