Mysql performance optimization, mysql

Mysql performance optimization, mysql

1. When using an index to query records, you must pay attention to the index usage, for example:

(1) The string configured with the like keyword cannot start with '%'

(2) When using multi-column indexes, the first field of the index must be used as the query condition.

(3) When the or keyword is used, indexes must be used for all conditions connected by the or keyword.

2. Optimize subqueries

In many queries, subqueries are required, but the execution efficiency of subqueries is not high. In subqueries, mysql needs to create a temporary table for the query results of the inner query statements, then, the outer query statement queries records in the temporary table.

After the query is completed, mysql needs to cancel these temporary tables. Therefore, the speed of the subquery will be affected. If the queried data volume is large, the impact will increase accordingly, you can use connection query in mysql.

Instead of subqueries, you do not need to create a temporary table for connection queries, which is faster than subqueries.

3. Optimize the database structure

(1) split tables with many fields into multiple tables. If there are many fields and some fields are frequently used, they can be divided into multiple tables.

(2) Add an intermediate table. If you frequently perform join table queries, the database query speed will be reduced. You can create an intermediate table to increase the query speed.

(3) add redundant fields. Associate the dept_id in the student table with the department table to add the redundant field dept_name.

Copyright statement: This article is the original author article, reprint please indicate the source: http://blog.csdn.net/lingzhm