16. Simple SQL Optimization for mysql partitions 2. SQL Optimization for mysql partitions

16. Simple SQL Optimization for mysql partitions 2. SQL Optimization for mysql partitions

1. index classification B-Tree supports HASH. Only memory supports R-Tree myisam and supports Full-text myisam (Full-text index. only the "=" condition in the Memory engine uses the index ======================== === Data Import Optimization =========================== 3. how to Improve myisam import efficiency alter table emp disable keys; disable INDEX load data infile 'aa. SQL 'into table emp; import data to emp alter table emp enable keys; enable index 4. how to improve the efficiency of innodb import data can be arranged in the order of primary keys. disable the uniqueness check set unique_checks = 0; load data infile .... set unique_checks = 1; enable the check ================================== Insert optimization ==== ================================= 1. insert into emp values (1, 2, 3), (1, 2 ),..... ==================================== page optimization ======== ================================= 1. use inner join inline 2. the general idea of processing on the application end is to record the id of the last record on the previous page. The next page uses this id as the starting value, but this must ensure that the id is unique.

 

Mysql Optimization

1. Optimize the SQL statement and find the slow query SQL statement from the slow query log.
2. Partition large tables
3. For the table engine, increase the corresponding configuration parameters (innodb is innodb_buffer_pool_size, and myisam is key_buffer)
4. read/write splitting
5. database/table sharding
6. Upgrade the hardware, create a disk array, or use ssd instead.

(1 ). in terms of database design, this is the responsibility of DBA and impact ect. A database with a good design structure should be de-normalized when necessary (I don't know what the Chinese translation is ), some data redundancy is allowed to avoid JOIN operations to improve query efficiency.
(2 ). in terms of system architecture design, the table is hashed, and massive data is hashed into several different tables. fast and slow tables: only the latest data is retained. Slow tables are archived in history. cluster, Master server Read & write, slave server read only, or N servers, each machine is a Master
(3). (1) and (2) better than PHP Programmer's requirements. It doesn't matter. check whether there is any less index.
(4 ). write efficient SQL statements to see if there are any inefficient SQL statements, such as generating full connections to cartesian products, a large number of Group By and order by statements, and no limit. when necessary, encapsulate the database logic in the stored procedure of the DBMS. cache query results and explain each SQL statement
(5). All the results are required. Only necessary data is obtained from the database. For example, you can query the number of comments of an article, select count (*)... where article_id =? You can. Do not select *... where article_id =? Then msql_num_rows.
Send only required SQL statements. For example, if you modify only the title when modifying an article, update... set title =? Where article_id =? Do not set content =? (Large text)
(6). Use different storage engines when necessary. For example, InnoDB can reduce deadlocks. HEAP can increase the query speed by an order of magnitude.

Best mysql optimization skills

1. select the most suitable field attribute

MySQL can support access to large data volumes, but generally, the smaller the table in the database, the faster the query will be executed on it. Therefore, when creating a table, we can set the field width in the table as small as possible to achieve better performance. For example, if you set it to CHAR (255) when defining the zip code field, it is obvious that unnecessary space is added to the database, and even the VARCHAR type is redundant, because CHAR (6) can well complete the task. Similarly, if possible, we should use MEDIUMINT instead of BIGIN to define integer fields.

Another way to improve efficiency is to set the field to not null whenever possible, so that the database does NOT need to compare NULL values during future queries.

Some text fields, such as "Province" or "gender", can be defined as ENUM. In MySQL, The ENUM type is processed as the numeric data, and the numeric data is processed much faster than the text type. In this way, we can improve the database performance.

2. Use JOIN instead of Sub-Queries)

MySQL supports SQL subqueries from 4.1. This technique can use the SELECT statement to create a single column query result, and then use this result as a filter condition in another query. For example, if you want to delete a customer who has no orders in the basic customer information table, you can use the subquery to retrieve the customer IDs of all orders from the sales information table, then pass the result to the primary query, as shown below:

Delete from customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

Subqueries can be used to complete SQL operations that require multiple logical steps at a time. At the same time, transactions or tables can be prevented from being locked and can be easily written. However, in some cases, subqueries can be replaced by more efficient JOIN. For example, if we want to retrieve all users without order records, we can use the following query:

SELECT * FROM customerinfo
WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)

If you use JOIN... to complete this query, the speed will be much faster. Especially when the salesinfo table has an index on CustomerID, the performance will be better. The query is as follows:

SELECT * FROM customerinfo
Left join salesinfoON customerinfo. CustomerID = salesinfo.
CustomerID
WHERE salesinfo. CustomerID IS NULL

JOIN... it is more efficient because MySQL does not need to create a temporary table in the memory to perform the query in two steps.

3. Use UNION instead of creating a temporary table manually

MySQL 4.0 and later versions support UNION queries. It can merge two or more SELECT queries in a temporary table. When the query Session on the client ends, the temporary table is automatically deleted to ensure the database is neat and efficient. When using UNION to create a query, we only need to use UNION as the keyword to connect multiple SELECT statements. Note that the number of fields in all SELECT statements must be the same. The following example demonstrates a query using UNION.

SELECT Name, Phone FROM client
UNION
SELECT Name, BirthDate FROM author ...... remaining full text>