MySQL Database Performance Tuning summary accumulation

MySQL database tuning can be divided into four chunks

0 Architecture Tuning

---Sub-table based on business reading and writing

---master-slave read-write separation

1 Tuning of the configuration

Set Cache Size---open cache query

---Maximum number of connections settings

---database engine configuration myisam (read operation, query fast) Innodb (write operation, main library, support transaction, security) engine configuration

---log configuration

2 Table Structure Tuning

----Establish an appropriate index (primary key index, unique index, normal index, full-text index MyISAM used to retrieve text) EXPLAIN Select command there can often get some help knowing what needs to be indexed (Pros: Query fast disadvantage: Occupy space, update SQL Slow)

---Try to add an ID column to each table, except for the associated table

---table, vertical split, column is too long, split into fixed-length tables and fixed-length table-length queries are faster, provided that other tables after the library are not frequently join. Otherwise less efficient.

3 Tuning for SQL

---Avoid full fuzzy query%like%, it will invalidate the index.

---Avoid using SQL functions directly in SQL, SQL functions invalidate cached queries and replace them with variables.

---Large-volume operations are more efficient with limit batching (avoid locking the full table), and with limit 1, it is more efficient to know that only 1 queries are taken out of the data.

---is NOT null <>! =, invalidates the index.

---for some fixed-value columns, it is more efficient to use enums instead of varchar as much as possible.

---Avoid meaningless full-table scans count (*)

MySQL Database Performance Tuning summary accumulation