How to solve the problem of slow query of large tables in mysql

Editor to share with you how to solve the problem of slow query of mysql big table. I hope you will gain a lot after reading this article. Let's discuss it together.

Mysql large table query slow optimization methods: 1, reasonable establishment of the index, usually the use of the index is faster than no index; 2, the key fields to establish a horizontal partition, such as time field, if the query conditions are often queried through the time range, can improve a lot of performance; 3, the establishment of coarse-grained data tables; 4, the use of cache.

Mysql database table is too large to query slow optimization

1. Reasonable establishment of index

Usually the query uses the index faster than no index. You can check whether the index is used through explain. Specific ways to use explain, such as

Http://www.cnitblog.com/aliyiyi08/archive/2008/09/09/48878.html

When the query contains group by and the group by field belongs to the index field, if the query result can not be determined by the group by loose or compact index, the group by operation will build a temporary table to get the results according to the file sorting (Using temporary; Using filesort). The performance is often lower, but it is not absolute, even if the index is used, it is not necessarily faster than not using the query.

2. Establish a partition

Establish a horizontal partition of key fields, such as time field, if the query conditions are often queried through the time range, it can improve a lot of performance.

3. Establish a coarse-grained data table

The corresponding table is established according to the query, and the repeated records are compressed and transferred to the new table regularly, the granularity becomes larger and the data records become less.

4. Make use of cache

Use cache to cache the hard-won data of a query for a period of time, so as to improve efficiency.

After reading this article, I believe you have a certain understanding of how to solve the problem of slow query in mysql. If you want to know more about it, welcome to follow the industry information channel. Thank you for your reading!