MySQL Optimized design scheme

First, let's talk about the project scenario:

1: This is a web-based Java project, its main function is to some of the audio and video information processing and display, wherein the audio data is crawled by the crawler crawl Tens amount of data

2: The project is using the mysql5.5 version of the database

3: The project has a search function, need to according to the keyword in the thousands data fuzzy matching query out the corresponding data

4: The project may be more user-oriented, there will be a lot of high concurrency

The main issues involved are:

1: This project for the data read operation needs far greater than the write operation, and take into account the write operation may appear the transaction problem needs to choose InnoDB as the search engine. However, the performance of InnoDB reads is much lower than MyISAM

2: Due to the large amount of data, the search for the implementation of the function we need to use the fuzzy query, at this time I need to use like '%keywords% ', then the search for MySQL will not go through the index instead of a full table search, then each search to find the need to consume at least 50 seconds of time, This is absolutely not allowed for performance requirements

Make the following solutions for the above problems:

1: Read-write separation of the database, allocation of 1 master (Master) 1 from (Slave) database, master for read-write database (using InnoDB search engine: In order to support transactions), Slave as a read-only database (using the MYISAM data Engine: read-only can consider not supporting transactions, The use of MyISAM can improve retrieval speed).

1) Read and write the separation need to note: Slave in the data will be delayed, in the master after the data is written slave need a certain amount of time to be synchronized, so we need the project for real-time requirements do not have to be absolutely accurate.

2) Read-write separation is used to read much more than writing scenes, if there is only one server, when the select many, update and delete will be in these select Access data congestion, waiting for select end, concurrency performance is not high, so read and write separation can also improve concurrency performance.

3) Read/write separation reduces the pressure on the server, making the database less prone to collapse

2: Consider full-text retrieval performance issues in the slave database, using Mysql+coreseek

1) for MySQL database, only MyISAM search engine support full-text index (fulltext keyword) before version 5.6, and support InnoDB full-text index after version 5.6. However, they do not support the Chinese index, which is a big problem for the Chinese search we need for this project.

2) At first my idea is to use lucence to do Chinese word segmentation index, but the efficiency and use of methods have not achieved the desired effect, compared to the lucence is not for the database

3) After the selection of Sphinx, however, Sphinx also does not support Chinese, want to support to the Chinese need to do a lot of processing.

4) finally chose Coreseek, it is the great developers of the Republic of China developed support for Chinese word segmentation, a full-text search support for MySQL search engine (it is based on Sphinx), it Tens data search speed can reach fraction level. Using it to combine MySQL can solve the problem 2.

Here is the flowchart:

In the Web, by invoking the API provided by Sphinx, we can retrieve the primary key ID value for MySQL, and then query the database with these ID values (go to the index in the database), which can greatly improve the speed of the search.

Of course, this is only my personal solution, there may be a lot of shortcomings, I hope you help correct the study

MySQL Optimized design scheme