MySQL optimization Jenshau

Optimization: Storage layer: Data table "Storage Engine" selection, field type selection, inverse paradigm (3 paradigm) Design layer: index, partition/table schema layer: Distributed Deployment (master/Slave mode/shared) SQL statement layer: In the same case, choose high efficiency, fast, resource-saving SQL statements perform familiar storage engines: Myisam: There is a compression mechanism for the characteristics of the Mysiam, the concurrency of the set hired Innodb to be slightly inferior, because the data table is a "table lock" Innodb: Support transactions, support row-level locks, support foreign keys, speed than Myisam to be slightly slower,　　High concurrency, multi-person request at the same time, fast, high efficiency.　　Memory: Internal data run very fast, temporary storage of some information, the server if the power outage, the storage engine will empty the entire data storage engine storage data format, different storage engine has its own characteristics Mysiam file: Each data table has three files (structure, index, data) Write stored data order: Storage sequence consistent with write order concurrency: Lock mechanism is "table lock", concurrency is not high compression processing: infrequently changing data is suitable for compressing InnoDB files: structure has separate files, data and indexes are merged into a file to write the stored data order: According to the order of the primary key ID value Data storage concurrency: Good concurrency, "row lock" the atomicity of the three-paradigm field is unique and can no longer be separated by a unique distinction between each row, emphasizing that a table must have a primary key associated with the database redundancy. A table cannot contain non-keyword information for other tables. That means you have the other table's primary key as your foreign key, can not take other people's other fields inverse paradigm The overall design of the data table to abide by the three paradigms, but sometimes for the overall performance of the consideration, do not observe a little, so called "inverse paradigm" Index primary key index (primary key)　　Indexing is one of the most effective techniques for improving performance when designing a database.　　Using index Benefits: You can quickly locate the information we need to find.　　Why the index is fast: From the perspective of the technology itself index, index is a data structure (dormitory building), data structure within the algorithm, you can help group us to quickly and accurately locate the required information.　　Execution plan explain: An SQL statement before execution, the resources needed to plan the use of the index: the cost of resources, query speed is not used to index: expensive resources, slow query speed Note: There are a lot of fields repeating content (such as the Gender field), do not set the index, will not work.　　　Index principle: field independent principle left principle Fuzzy Query composite index use the first field alone as a condition, you can use the second field alone as a condition, you cannot use the　　　Using all fields (regardless of order) as a condition, you can use the or principle to have indexes around, merge indexes together with indexes, and no indexes on one side, resulting in no index usage for the whole   

MySQL optimization Jenshau