MySQL BASICS (optimization) and mysql Basics

MySQL BASICS (optimization) and mysql Basics

1. When creating a table, we can set the field width in the table as small as possible to achieve better performance.

2. When possible, set the field to not null as much as possible, so that the database does NOT need to compare NULL values during future query execution.

3. For some text fields, such as "Province" or "gender", we can define them as the ENUM type.

4. Try to use connections instead of subqueries. subqueries can perform SQL operations that require multiple logical steps at a time, and avoid locking transactions or tables, it is easy to write. However, in some cases, subqueries can be replaced by more efficient joins. JOIN... it is more efficient because MySQL does not need to create a temporary table in memory to perform the query in two steps.

5. Use inner join whenever possible.

6. Use UNION instead of manually creating a temporary table. Note that the number of fields in all SELECT statements must be the same, 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. Example (UNION): SELECT Name, Phone FROM client union select Name, BirthDate FROM author UNIONSELECT Name, Supplier FROM product

7. To maintain data consistency and integrity in the database, you need to use a series of statements to complete some work (for example, to insert a data into two associated tables at the same time, we need transactions. The transaction starts with the BEGIN keyword and ends with the COMMIT keyword. If an SQL operation fails, the ROLLBACK command can restore the database to the State before the start of in.

BEGIN;
Insert into salesinfo SET CustomerID = 14;
UPDATE inventory SET Quantity = 11
WHERE item = 'book ';
COMMIT;

Another important role of transactions is that when multiple users use the same data source at the same time, they can use the database locking method to provide users with a safe access method, this ensures that user operations are not affected by other users.

8. Although transactions are a good way to maintain database integrity, they sometimes affect the database performance, especially in large application systems. Because the database will be locked during transaction execution, other user requests can only wait until the transaction ends. In some cases, we can lock the table to achieve better performance. The following example uses the table locking method to complete the transaction function in the previous example.

Lock table inventory WRITE
SELECT Quantity FROM inventory WHEREItem = 'book ';
...
UPDATE inventory SET Quantity = 11 WHEREItem = 'book ';
UNLOCK TABLES9. For details about the index, refer to [http://bbs.landingbj.com/t-0-245601-1.html#. Thank you for sharing your feedback!