Mysql optimization notes

Mysql optimization notes
[Mysql optimization part]
General Optimization ideas:
A. Table Design rationalization (in line with 3NF)
B. Add an appropriate index)
Mysql indexes are roughly divided into four types:
Common Index, primary key index, unique index, full-text index
C. Table sharding Technology (horizontal and vertical)
D. read/write splitting (read select write insert/delete/update)
E. Stored Procedures (modular programming can improve the speed)
F. MySQL configuration optimization (for example, max_connections)
G. MySQL server hardware upgrade
H. Clear unnecessary data regularly for fragment (especially the myisam storage engine)


[I. Table Design rationalization] 1. Table Design Principles

1NF: column attributes of a table are inseparable. (Relational databases meet all requirements and do not need to be considered)
2NF: the records in the table are unique. (Implemented by setting the primary key)
Primary keys generally do not include business logic and are self-increasing.
3NF: The table does not contain redundant data (some fields in the table can be derived,
Fields should not be designed separately to store them)


Sometimes the field design table of anti-3NF is used.
For example:
[Category Table]: id view
[Detailed table]: id view name cid
The page views in the [Classification Table] must be calculated by the page views in the [detailed table ].
You can add a view field to the [Category Table,
When updating the [detailed table], the view value in the [Category Table] is also added.
It can avoid computing and improve query efficiency.


In general, do not query fields that need to be calculated.
Calculate them, and calculate them as a field when updating or inserting them.


2. Design Principles of fields (keep short or not long)

Use tinyint instead of int.
If char is used, do not use varchar.
Do not use text if you can use varchar.
...
All in all, we try to use the appropriate field type to design the table fields.


[2. select an appropriate storage engine] 1. Selection Principle myisam: tables do not have high requirements on transactions, mainly query and add and modify tables,
Consider using this engine (such as the evaluation form ).
Innodb: high transaction requirements. It stores important data. We recommend that you use
This engine (such as order table and account table ).
Memory: data changes frequently and does not need to be stored into the database. Frequently query and modify at the same time,
Consider using this engine (such as the user's logon status ).
Note: memory data is stored in the memory and will be lost when mysql is restarted.


2. Differences between myisam and innodb ① Transaction Security (innodb)
② Query and acceleration (myisam)
③ Full-text index (myisam) supported)
④ Locking mechanism (innodb)
⑤ Foreign key (innodb)


3. Writing a large amount of data ① for myisam, disable the index
Alter table table_name disable keys;
Insert and load data
Alter table table_name enable keys;
Because the index is automatically created during table creation, the load will increase.


② For innodb
Sort the data to be inserted by primary key
Set unique_checks = 0; # disable unique indexes (uniqueness check affects efficiency)
Set autocommit = 0; # disable automatic submission


[3. Create an appropriate index]

Use of four indexes (primary key, unique, full-text, and common index)


1. alter table art add primary key (id );
Delete the index alter table art drop primary key;


2. If a column in the unique index table is specified as the unique keyword, it is a unique index.
The unique index can be null or''
However, multiple null values can exist, and no more ''(empty string) can exist.
Create unique index name on table name (column name 1 ,...)


3. Create a full-text index when creating a table
Create table art (
Id int primary key,
Title varchar (20 ),
Body text,
Full text (title, body)
) Engine = myisam charset utf8;


Note:
① Full-text indexing only supports the myisam Engine
② Full-text index provided by mysql system, only English is supported, Chinese is not supported
To support Chinese characters, you need to download the sphinx ins.
③ The full-text index has a stopword. In an article, creating a full-text index is
An infinite number, so only full-text indexes are created for uncommon words.
④ Full-text indexes must follow the use rule match () against ();
Select * from art where match (title, body) against ('day ');




4. create index name of a common index on table name (column name );
Alter table name add index name (column name );


Delete index: alter table Name drop index name


5. Index query ① Table Structure Query
Desc table name;


② Query a single index
Select index (index name) from Table Name \ G


③ Query all indexes of a table
Show keys from Table Name \ G


④ View index usage
Show status like 'handler _ read %'
High handler_read_key indicates high index usage
High handler_read_rnd_next indicates low query efficiency


6. Usage principles of indexes ① multiple-column indexes are created. indexes are used only when the leftmost column is used.
② When the like keyword is used for query, wildcards '%' and '_' cannot start
Otherwise, the index is not used.
③ Indexes are not used if the condition contains the or keyword.


7. indexing principles and advantages and disadvantages ① advantage: Fast query speed using a binary tree log2n Query
② Disadvantage: occupying disk space
Tables that frequently operate on dml statements (non-query statements) may slow down the speed.
③ Add principles
The fields frequently used in the where clause are indexed.
Fields with poor uniqueness are not suitable for independent indexing.






4. Table Segmentation technology]

1. horizontal segmentation means that one table will be copied to multiple tables without changing the structure.
Principles:
① The table structure remains unchanged
② The table sharding standards should be found based on the business needs, and on the retrieval page
Restrict user permissions.


[Example] a qq login table with hundreds of millions of data records qqlogin
Based on the remainder of user id % 3, we determine which table the user is stored in.
Uuid (User ID generated automatically)
Qqlogin0)
Qqlogin1)
Qqlogin2)


2. vertical segmentation separates common and uncommon fields in a table to form two different tables.
Principles:
① Separate uncommon fields in the table
② Separating tables with large data volumes that affect query speed
③ Note the relationship between the detached table and the original table


[5. read/write splitting]

1. insert into tab1
Select col1 col2... from tab2;




[6. Master-slave replication] (Omitted) Detailed descriptions in later sections




[7. Locate slow query SQL] (Note: here slow query does not necessarily refer only to select statements, and other statements are executed at a speed.
Slow query)
General SQL optimization ideas:
1. Use the show status command to understand the efficiency of various SQL statements.
2. Locate SQL statements with low execution efficiency
3. Analyze the execution of low-efficiency SQL statements through explain
4. Determine the problem and take appropriate measures


1. Use the show status command to learn the efficiency of various SQL statements. show [session | global] status like '% ';
Where: session is the current session window statistics. Default item
Global is used for statistics on all session windows.


① Mysql running 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 connections
Show status like 'connections ';


④ Display the number of slow queries
Show status like 'slow _ queries ';


2. Locate SQL statements with low execution efficiency. We need to use the following steps to locate slow query SQL statements:
① Close the mysql Service
In windows, open "service", find mysql, and disable the service.
In Linux, find the mysqld process and kill it.


② Enter the installation directory of mysql through the command line
Version 5.5 and later
Bin \ mysqld.exe -- safe-mode -- slow-query-log
Versions earlier than 5.0
Bin \ mysqld.exe-log-slow-queries = d:/AB. log


Enter


③ Enter mysql command line mode again to change the time limit for slow queries to 1 s
Set long_query_time = 1;


④ The Slow query log is enabled.
The record address is in the directory specified by datadir in my. ini.


3. Analyze the execution of low-efficiency SQL statements through explain

Mysql> explain select * from emp where empno = 345680 \ G
* *************************** 1. row ***************************
Id: 1 # query the serial number
Select_type: SIMPLE # Query type
PRIMARY/
Table: emp # name of the queried table
Type: ALL # scan method ALL (full table scan, try to avoid)
The SYSTEM table has only one row.
The CONST table matches only one row.


Possible_keys: NULL # possible indexes used in the table
Key: NULL # actually used Index
Key_len: NULL
Ref: NULL
Rows: 4000000 # the number of rows scanned by the SQL statement. The number of records may be obtained.
Extra: Using where # additional information such as sorting methods such as filesort
1 row in set (0.00 sec)




4. Determine the problem and take appropriate measures to optimize the SQL statement


① Optimize the group by statement
After the group by clause is used, the system performs sorting by default.
If you do not need to sort data, we recommend that you add order by null.


② Use join instead of subquery


(8) fragment sorting]

Fragment for the myisam Engine


// Fragment the specified table


Mysql> optimize table table_name;




IX. Backup/restoration]

1. PHP regular database backup ① manual BACKUP command
Back up 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 task ① windows batch processing (Task Manager)
(1) create a. bat batch file (for example, my. bat)
Find the directory where the mysql file is installed.
Add the following statement to my. bat:
D: \ phpservice \ mysql \ bin \ mysqldump-uroot-psa demp> d: demp. bak. SQL


(2) Add the my. bat file to the scheduled task
Control Panel-> Administrative Tools-> task scheduler-> operations-> Create task->
Import Task in Operation
Create a trigger to set the trigger time
Set Task Name in general
Set the task time in the condition
Set related conditions for the task


(3) triggered on time after the settings are completed


② Linux crontab-e Schedule Tasks
This is simpler
Change the script path and program path written above.


3. implement regular database backup in PHP

<? Php
// Set the time zone
Date_default_timezone_set ("PRC ");
// Set the file name
$ Bakfile_path = date ('Y-m-d H: I: s', time ());
// Assemble the command
$ Commond = "D: \ phpservice \ mysql \ bin \ mysqldump-uroot-psa demp> d: {$ bakfile_path}. bak. SQL ";
// Execute the command
Exec ($ commond );


?>


4. mysql Incremental Backup

Mysql database records mysql database operations in binary format to files
You can use this file for backup and recovery when you want to recover it.


Incremental Backup Principle
① Record dml statements (excluding query statements)
② Record a. Operation statement itself
B. Operation Time
C. Operation position


How to perform Incremental backup/recovery
(1) Configure mysql. ini to enable binary backup
Add a statement under [mysqld]
Log-bin = d: \ binlog \ mylog


(2) restart the mysql Service (this step is critical)
Find mysql in the service in windows and restart
Restart mysql process in linux


(3) view mysql logs
Locate the location of the log file
Run the following command:
# Mysqlbinlog Log File Path


Log Analysis
A. the operation location in the end_log_pos Log File
B. TIMESTAMP operation time point
C. Restore the database based on the above two points


(4) Restore by timestamp/location
Restore by Time
# Mysqlbinlog -- stop-datetime = "18:23:43" d: \ binlog \ mylog000001 | mysql-uroot-psa
Restore by location
# Mysqlbinlog -- start-position = "112" d: \ binlog \ mylog000001 | mysql-uroot-psa




[10. Configuration Optimization]

1. If you want to set multiple mysql instances to be used on the same server, you need to change the port number.
If 3306 is not used, you need to attach it when using the mysql_connect connection function.
Port Number


2. Change the maximum number of connections (mysql. ini/mysql. conf) max_connections = 100 (optimal range: 100-1000)


3. Enable query_cache_size = 15 M


4. Set different cache sizes for different engines. myisam ---> key_buffer_size
Innodb ---> innodb_additonal_new_pool_size = 64 M
Innodb_buffer_pool_size = 1G


5. If the server memory exceeds 4 GB, consider using a 64-bit operating system and
64-bit mysql Server