MySQL stage seven--mysql optimization

MySQL optimization

-First, hardware optimization (main point of optimization)

01.CPU----preferably 64-bit, example: 8-16 CPU

02. Memory----Example: 96g-128g,3-4 instances

03.disk----The number of better, performance: SSD (suitable for high concurrency) >sas (normal online business) >sata (for offline)

RAID----Deploy the appropriate raid

04. NIC----Multi-Nic bond, and BUFFER,TCP optimization

-Two, software optimization

01. Operating System----x86_64

02. Software: MySQL, compile optimization

-Three, my.cnf inside parameter optimization (general effect is not very obvious)

01. Using the Tuning tool

-Four. Optimization of SQL statements

01. Index Optimization

A. Grab and remove slow query sql

B. You can use the Slow query log analysis tool

(For query-Slow SELECT statements, you can use explain to view queries; The CV index is usually on a column with unique values or fewer repetitions, if the conditions in the multi-condition query are all unique values are few and repeat more, you can use a federated index to index multiple columns together)

C. 0 O'Clock Analysis slow query, send to core development, DBA analysis.

02. Splitting large, complex SQL statements

Sub-query, jion query.

03. The database is where the data is stored, not where the data is calculated

04. The SQL index does not work for queries like "%xxxiii%" that are both before and after (it is generally a problem with some search)

Solution: A. From the business, you can realize the user login and then query or search, reduce the number of searches

B. If a large number of frequent searches, usually crawler crawling, then you can analyze the Web log, the frequent query of IP sealed off

C. Configure master-Slave synchronization, program to achieve read and write separation

D. add memcached cache server to the front of the database

E. Query without a database, using search software

-Five. Optimization on the architecture

01. Business Split, search function, like "%xxx%", generally do not use MySQL database

02. Cache must be added to the front end of the database, for example: memcached

03. Business splitting, some businesses should use NoSQL persistent storage

For example: Fan focus, friend relationship, etc.

04. Dynamic Data Static

05. Database cluster and read/write separation, a master multiple from

06. Too many single-table, for the demolition of the table

-Six, process, system, security optimization

This article from "Qin Bin blog" blog, declined reprint!

MySQL stage seven--mysql optimization