MySQL Introduction and optimization (1)

MySQL database 1, is a relational database, 2, small size, multi-processor support, open source, free 3, in the Internet, small and medium-sized web site usage is particularly high MySQL database development FAQs and optimization one, library table Design 1.1 engine selection in MySQL 5.1, Introduces a new plug-in storage engine architecture that allows the storage engine to be loaded into a new MySQL server that is being shipped. Using the MySQL plug-in storage engine architecture, it allows database professionals or software developers who design library tables to choose a dedicated storage engine for specific application requirements, without the need to manage any special application coding requirements or to consider all the underlying implementation details. Therefore, although different storage engines have different capabilities, the application is decoupled from it. In addition, users can store engines in server, database, and table three tiers, providing great flexibility. MySQL common storage engine includes MYISAM, Innodb and memory, each of which features as follows: MYISAM: Full table lock, with high execution speed, one write request block all read and write requests in the same table, concurrency performance is poor, occupy space is relatively small, MySQL 5.5 and below only MYISAM supports full-text indexing and does not support transactions. Innodb: Row-level locks (SQL takes index queries), concurrency is relatively strong, occupying space is 2.5 times times the MYISAM, does not support full-text indexing (5.6 start support), support transactions. Memory: Full table lock, stored in the RAM, fast, but will occupy the amount of data proportional to the memory space and data will be lost when MySQL restarts. Based on the above features, most of the recommendations are set to the InnoDB engine, and the special business considers the use of MYISAM or Memory, such as full-text indexing support or very high execution efficiency. 1.2 Table method in the use of database tables, in order to reduce the burden of the database server, shorten the query time, often consider doing a table design. The Sub-table is divided into two types, one is the vertical sub-table (will be able to be in the same table of content, artificially divided into a number of different structures of the table) and the transverse table (the large table structure, transverse cutting for the same structure of the different tables). Among them, the longitudinal sub-table common way has according to the activity degree table, according to the importance table and so on. The main problems are as follows: resource contention between table and table, low probability of lock contention, implementation of core and non-core tiered storage, such as UDB Landing library split into a level two level three library, solve the database synchronization pressure problem. Horizontal sub-table refers to the classification of large data scale according to certain rules, such as the time table. The main problems are as follows: The performance problem caused by large single-table, single-server space problem caused by large single-table. 1.3 Index problem index is the structure that sorts the values of one or more columns in a database table.Indexing helps to get information faster. MySQL has four different index types: primary key cable This (PRIMARY) unique index (unique) General index (index) Full-text index (fulltext, MYISAM, and MySQL over 5.6 Innodb) is indexed to speed up the The search or sort of a record is not as good as the index, because the index is created at a cost: one is to increase the storage space for the database, and the other is to spend more time maintaining the index when inserting and modifying the data. When you design a table or index, you often have the following issues: fewer indexes or no indexes. This is the most prominent issue, and it is recommended that DBAs work together to help check when building a table. Index abuse. Abusing the index will slow down the write request and slow down the overall database response (only one index is used for MySQL under 5.5). The federated index is never considered. In fact, the efficiency of a federated index is often more efficient than a single-column index. Non-optimal column selection. Low-selectivity fields do not fit into a single-column index, such as a field of status type. Second, slow SQL 2.1 causes slow SQL in the case of slow SQL, it is not easy to boil down to the cause of SQL writing problem (although this is the most common factor), actually lead to slow SQL has many factors, even including the hardware and MySQL itself bug. According to the probability of occurrence from large to small, listed as follows: SQL writing problem lock business instance to each other dry around the IO/CPU resource contention server hardware MySQL BUG2.2 SQL writing caused by slow SQL optimization for SQL writing caused by slow SQL, optimization is relatively convenient. As the correct use of indexes in the previous section speeds up queries, we need to pay attention to index-related rules when writing SQL: field type conversions result in no indexes, such as String type without quotation marks, quotation marks for numeric types, which may result in a full table scan without indexing; MySQL function conversion is not supported, so the field cannot be preceded by a function, otherwise this will not be indexed; Do not add subtraction to the field; The string is longer, consider the index to reduce the size of the index file, improve the write efficiency; like% is not used in the previous index ; The second and subsequent fields of the federated index are not indexed by the singleton query; do not use SELECT *; use ascending order as possible, or query with union instead (INNODB); compound index high-selectivity field is in front; the order By/group by field is included in the cable Reduce the sequencing, and the efficiency will be higher. In addition to the above-mentioned index usage rules, SQL requires special attention when writing: to avoid large transactions of SQL, large-transaction SQLcan affect the concurrency performance of the database and the master-slave synchronization; Delete all records of the table please use TRUNCATE, do not use Delete, do not let MySQL do superfluous things, such as calculation, write SQL with fields to prevent subsequent table changes caused by the problem, performance is also relatively excellent ( Refer to Data dictionary parsing, please check the data yourself); Use SELECT COUNT (*) on Innodb because Innodb stores statistics; use Oder by Rand () with caution. Third, the analysis of diagnostic tools in the daily development work, we can do some work to prevent slow SQL problems, such as pre-launch the use of diagnostic tools to analyze SQL. Commonly used tools are: Mysqldumpslowmysql profilemysql explain four, error operation, program bug how to do a mature enterprise often has perfect data management norms and a richer data recovery program (except for startups), Data backup and data disaster recovery. When you find that the wrong operation or program bug causes the online data to be mistakenly deleted or mistakenly changed, must not panic, should promptly contact the DBA, the first time for data recovery (serious when directly stop service), minimize the impact and loss. For important data, such as funds, you must test repeatedly during development to ensure that there are no problems before you go online.

　　

MySQL Introduction and optimization (1)