Ways to optimize MySQL database

Ways to optimize MySQL databaseedit Delete Reprint 2016-09-12 14:17:15 Tags: it

1, choose the most applicable field properties, as far as possible to reduce the definition of field length, as far as possible to set the field not NULL, such as ' Province, gender ', preferably set to enum

2. Use a connection (join) instead of a subquery: A. Delete no orders customer: Delete from CustomerInfo WHERE customerid Not IN (SELECT CustomerID from OrderInfo) B. Extract the There are no orders for customers: Select from CustomerInfo WHERE CustomerID Not IN (SELECT CustomerID from OrderInfo) c. Improve speed optimization for B: Select from customer Info left JOIN OrderID customerinfo.customerid=orderinfo.customerid WHERE Orderinfo.customerid is NULL

3. Use Union (Union) instead of manually created temporary table A. Create temporary table: Select name from ' nametest ' UNION select username from ' Nametest2 '

4, transaction processing: A. Ensure data integrity, such as add and modify simultaneously, both are executed, accesses than either failure mysql_query ("BEGIN"); mysql_query ("INSERT into CustomerInfo (name) VALUES (' $name 1 ')", mysql_query ("select * from ' OrderInfo ' where customerid= ". $id"); mysql_query ("COMMIT");

5, lock the table, optimize transaction processing: A. We use a SELECT statement to remove the initial data and, with some calculations, update the new value to the table with the UPDATE statement. The lock table statement with the WRITE keyword guarantees that there will be no additional access to the inventory to insert, update, or delete operations mysql_query ("Lock table Custo" until the UNLOCK TABLES command is executed). Merinfo READ, OrderInfo WRITE "); mysql_query ("Select CustomerID from ' CustomerInfo ' where id=". $id); mysql_query ("UPDATE ' orderinfo ' SET ordertitle= ' $title ' where customerid=". $id); mysql_query ("UNLOCK TABLES");

6, using the foreign key, optimization lock Table A. Map the CustomerID in the CustomerInfo to the CustomerID in OrderInfo, and any record of no legal CustomerID will not be written in OrderInfo. CREATE TABLE CustomerInfo (CustomerID INT not NULL, PRIMARY KEY (customerid)) TYPE = INNODB; CREATE TABLE orderinfo (orderid int NOT NULL, CustomerID int NOT NULL, PRIMARY key (Customerid,orderid), FOREIGN key (Cust Omerid) REFERENCES CustomerInfo (CustomerID) on DELETE CASCADE) TYPE = INNODB; Note: ' On delete CASCADE ', this parameter guarantees that when a record in the CustomerInfo table is deleted, all records of that user in the order table are also deleted, noting that the foreign key is used to define the transaction security type of InnoDB;

7. Indexing: A. Format: (normal index)--CREATE INDEX < index name > on tablename (indexed field) Modify: ALTER TABLE tablename ADD Index [index name] (index field) Genesis specified index: CREATE TABLE tablename ([...],index[index name] (index field)) (Unique index

Creation: Create UNIQUE < index name > on tablename (indexed field)

Modify: ALTER TABLE tablename ADD UNIQUE [index name] (index field) CREATE TABLE Specify index: CREATE TABLE tablename ([...],unique[index name] (index field))

(primary key), which is

Unique index

, generally in the creation table is established, in the format: Creata table tablename ([...],primary key[index field])

8. Optimize query statement A. It is best to compare operations in the same field, minimizing function operations on established index fields

Example 1:select * FROM Order WHERE year (orderDate) <2008; (slow)

SELECT * FROM order WHERE orderdate< "2008-01-01";(Fast) Example 2:

SELECT * from order where addtime/7<24; (slow) SELECT * from order where addtime<24*7; (FAST)

Example 3:select * from the order WHERE title like "%good%";

SELECT * FROM order WHERE title>= "good" and name< "good";

Ways to optimize MySQL database