"MySQL optimization section"
Optimize the general idea:
A. Rationalization of the design of tables (3NF compliant)
B. Adding an appropriate index
The MySQL index is broadly divided into four categories:
Normal index, primary key index, unique index, full-text index
C. sub-table technology (horizontal split, vertical division)
D. Read/write separation (read select Write insert/delete/update)
E. Stored procedures (modular programming, can improve speed)
F. Configuration optimizations for MySQL (such as maximum concurrency max_connections, etc.)
G. MySQL server hardware upgrade
H. Timing out unnecessary data timing defragmentation (especially MyISAM storage engine)
"One, the design rationalization of the table"1, the design principle of the table
1NF: The column properties of the table are indivisible. (relational databases are met, not considered)
2NF: The records in the table are unique. (implemented by setting the primary key)
Primary keys generally do not contain business logic, self-growth
3NF: The table does not contain redundant data (some fields of the table can be deduced,
You should not design fields separately to store them)
Sometimes the anti-3NF field design table is used.
For example:
[Classification Table]:id View
[Detail Table]:id view name CID
Where the number of views in the [table] is calculated by calculating the number of views in [detail table]
So we can also add a view field to the [table],
When updating [detail table], the view value in [table] is also increased, then the extracted
Time to avoid calculations and improve query efficiency.
In general, you need to calculate the fields, try not to query the time
calculate, and change them to be calculated as a field when updating or inserting.
2, the design principle of the field (short Yasunaga)
If you can use tinyint, don't use int.
If you can use char, don't use varchar.
If you can use varchar, don't use text.
...
To summarize, use the appropriate field type to design the table's fields.
"Second, choose the right storage Engine"1. Selection principleMyISAM: Table on the transaction requirements are not high, mainly to query and add the main changes,
Consider using this engine (such as a rating table, etc.).
InnoDB: High demands on transactions, preserving important data, and recommending the use of
This engine (such as order form, Account table).
Memory: Data changes frequently, do not need storage. At the same time, frequent queries and modifications,
Consider using this engine (such as the user's login status, and so on).
Note: Memory data is present in RAM and restarting MySQL will be lost.
2. The difference between MyISAM and InnoDB① Transaction Security (INNODB)
② Query and add speed (MyISAM)
③ support full-text indexing (MyISAM)
④ lock Mechanism (INNODB)
⑤ foreign KEY (INNODB)
3, a large number of data write① for MyISAM, close index
ALTER TABLE table_name disable keys;
Insert Load Data
ALTER TABLE table_name enable keys;
Because indexes are created automatically when tables are created, so the load increases
② for InnoDB
Sort the data you want to insert by primary key
Set unique_checks=0; #关闭唯一索引 (uniqueness check affects efficiency)
Set autocommit=0; #关闭自动提交
"Iii. establishing a suitable index"
Use of four indexes (primary key, unique, full-text, normal index)
1. Primary KEY indexadd an index ALTER TABLE art ADD primary key (ID);
Delete index ALTER TABLE art drop PRIMARY key;
2. Unique indexA unique index when a column of a table is specified as the unique keyword
The unique index allows for null and '
However, you can allow multiple null values to exist and cannot have multiple ' (empty strings) present
Create unique index index name on table name (column name 1,...)
3. Full-Text IndexingCreate a table when creating a
CREATE TABLE Art (
ID int PRIMARY KEY,
Title varchar (20),
Body text,
Full TEXT (Title,body)
) Engine=myisam CharSet UTF8;
Attention:
① Full-Text indexing supports only MyISAM engines
Full-text index provided by ②mysql system, English only, not supported in Chinese
If you want to support Chinese, you need to download the Sphinx plugin.
③ Full-Text indexing has a stop word, in an article, creating a full-text index is a
Infinite number, so only non-common words are created for the full-text index.
④ using full-text indexing must follow the rules match () against ();
SELECT * from Art where match (title,body) against (' Daye ');
4. General IndexCREATE index index name on table name (column name);
ALTER TABLE name add index index name (column name);
Delete index: ALTER TABLE name DROP INDEX name
5, the query of the index① Table Structure Query
DESC table name;
② querying a single index
Select index (index name) from table name \g
③ all indexes of the query table
Show keys from table name \g
④ viewing the usage of indexes
Show status like ' handler_read% '
Handler_read_key High Index usage rate
Handler_read_rnd_next high specification for low query efficiency
6, the use of the index principle① creates multiple column indexes that are used only when the leftmost column is used
② when you query with the LIKE keyword, you cannot start with a wildcard '% ', ' _ ', etc.
Otherwise, the index will not be used
Index is not used in ③ condition with or keyword
7. Index adding principle and advantages and disadvantages① Advantages Query speed use two fork tree log2n query
② disadvantage takes up disk space
Tables that are frequently manipulated against DML statements (non-query statements) can cause slow speed
③ Add principle
Add fields that are frequently used in the WHERE clause to index
Fields with poor uniqueness are not suitable for individual indexing
"Four, table of segmentation technology"
1. Horizontal splitCopy a table into multiple table structure unchanged
Principle:
① table structure unchanged
② should find the standard of the sub-table according to the needs of the business, and search the page
Constrain user permissions.
[Example] a QQ login form, hundreds of millions of data qqlogin
Based on the remainder of the user's id%3, we decide which table to deposit the user
UUID (auto-generated user ID)
Qqlogin0 (the user who deposited the ID to find the remainder result of 0)
Qqlogin1 (the user who deposited the ID to find the remainder result of 1)
Qqlogin2 (the user who deposited the ID to find the remainder result of 2)
2, Vertical Divisionseparating common and infrequently used fields from a table, forming two different tables
Principle:
① separate fields that are not commonly used in a table
② a table with a large amount of data that can affect the query speed.
③ attention to the relationship between the separated table and the original table
"v. Read and write separation"
1. Master and slave copy of tableINSERT INTO TAB1
Select Col1 col2 ... from tab2;
"Six, master-slave Replication"(slightly) Detailed later chapters to explain
"Seven, positioning slow query SQL"(note: Here the slow query does not necessarily refer only to the SELECT statement, other statement execution speed
Slower is also called slow query)
General ideas for SQL optimization:
1. Understand the efficiency of various SQL executions through the show status command
2. Locating SQL statements with less efficient execution
3. Analyze the execution of inefficient SQL statements by explain
4, determine the problem to take appropriate measures
1. Understand the efficiency of various SQL executions through the show status commandShow [Session|global] status like '% ';
Where: Session is counted for the current conversation window. Default item
Global is counted for all session windows.
①mysql Run Time:
Show status like ' uptime ';
② total number of executions:
Select:show status like ' Com_select ';
Update:show status like ' Com_update ';
Insert:show status like ' Com_insert ';
Delete:show status like ' Com_delete ';
③ Current number of connections
Show status like ' connections ';
④ Show Slow query times
Show status like ' Slow_queries ';
2. Locating SQL statements with less efficient executionwe want to locate the slow query SQL statement in the following steps:
① shutting down the MySQL service
Under Windows, open "services", locate MySQL, shut down the service
Under Linux, directly find the mysqld process, kill
② command line into MySQL's installation directory input
Version 5.5 and later
Bin\mysqld.exe--safe-mode--slow-query-log
Version 5.0 and Previous
Bin\mysqld.exe-log-slow-queries=d:/ab.log
Enter
③ again into MySQL command line mode change slow query setting time limit to 1s
Set long_query_time = 1;
④ now the slow query log is turned on
Record address in: My.ini in the directory referred to in DataDir
3. Analyze the execution of inefficient SQL statements by explain
Mysql> explain select * from emp where empno = 345680\g
1. Row ***************************
Id:1 #查询序列号
Select_type:simple #查询类型
primary/
Table:emp #查询的表名
Type:all #扫描方式 All (full table scan, try to avoid)
SYSTEM table has only one row
The CONST table matches only one row
Possible_keys:null #表中可能使用到的索引
Key:null #实际使用的索引
Key_len:null
Ref:null
rows:4000000 #该sql语句扫描了多少行, may get the number of records
Extra:using where #额外信息 such as sorting such as Filesort, etc.
1 row in Set (0.00 sec)
4, determine the problem to take appropriate measuresOptimizing SQL statements
① Optimizing GROUP BY statements
The system will sort by default after using the GROUP BY clause
If sorting is not required, it is recommended to add an order by null
② using join joins instead of subqueries
"Eight, defragmentation"
Defragmenting the MyISAM engine
Defragment the specified table
Mysql> optimize table table_name;
"Nine, backup/restore"
1, PHP scheduled to complete the database backup① Manual Backup command
Backing Up the database
# MYSQLDUMP-UROOT-PSA Database name >/file path
Backup table
#mysqldump-UROOT-PSA database name. Table 1 database name. Table 2 ... > File path
② Manual Data Recovery
Mysql> Source Backup File path
2. Scheduled Tasks①windows Batch processing (Task Manager)
(1) Create a. bat batch file (such as My.bat)
Locate the installation bin directory of the MySQL file to copy the file path
Add the following statement to the My.bat
D:\PHPSERVICE\MYSQL\BIN\MYSQLDUMP-UROOT-PSA demp > D:demp.bak.sql
(2) Adding my.bat files to a scheduled task
Create tasks, Operations, Task Scheduler, management tools, control Panel
Import tasks in action
New set trigger time in trigger
Set task name in general
Setting task time in a condition
Setting related conditions for tasks in Settings
(3) When set up, it will trigger on time
②linux Crontab-e Scheduled Tasks
This is much simpler.
Simply change the script path and the program path written above to OK
3, PHP implementation of scheduled database backup
<?php
Setting the time zone
Date_default_timezone_set ("PRC");
Set file name
$bakfile _path = Date (' y-m-d h:i:s ', Time ());
Assemble command
$commond = "D:\phpservice\mysql\bin\mysqldump-uroot-psa demp > d:{$bakfile _path}.bak.sql";
Execute command
EXEC ($commond);
?>
4. mysql Incremental backup
MySQL database will be in binary form, the MySQL operation of the database, log to the file
This file can be used for backup recovery when the user wants to recover.
Incremental backup principle
① record DML statements (without query statements)
② record A. Action statement itself
B. Operating time
C. Operation position
How to make incremental backup/recovery
(1) Configure Mysql.ini to enable binary backup
Add a statement under [Mysqld]
Log-bin = D:\binlog\mylog
(2) Restart MySQL service (this step is critical)
Find MySQL and restart in the service under Windows
Linux under restart MySQL process
(3) View MySQL log
Find the location of the log file
Use the command:
# mysqlbinlog log file path
Log analysis
A. Where to operate in the End_log_pos log file
B. The point in time at which the TIMESTAMP operates
C. Database recovery based on the above two points
(4) Recovery by timestamp/position
Restore by Time
# mysqlbinlog--stop-datetime= "2015-01-14 18:23:43" d:\binlog\mylog000001 | Mysql-uroot-psa
Restore by Location
# mysqlbinlog--start-position= "d:\binlog\mylog000001" | Mysql-uroot-psa
"Ten, configuration Optimization"
1. Port number ChangeIf you want to set multiple MySQL to use on the same server, you need to change the port number
If you do not use 3306, you need to take the mysql_connect when the connection function is used
Port number
2. Change the maximum number of connections (mysql.ini/mysql.conf)max_connections = 100 (best range 100-1000)
3. Open Query Cachequery_cache_size = 15M
4. Set different cache sizes for different enginesMyISAM---> key_buffer_size
InnoDB---> innodb_additonal_new_pool_size = 64M
Innodb_buffer_pool_size = 1G
5. If the server memory is more than 4G, consider using 64-bit operating system and
64-bit MySQL server
MySQL optimization notes
