My MYSQL learning experience (1): simple grammar MYSQL my Michael Kors Bedford Logo Medium Brown Satchels learning experience (2): Data Type Width my MYSQL learning experience (3): View field length MYSQL my learning experience (4): The data type of learning my MYSQL Reviews (5): Operators my MYSQL learning experience (6): Functions my MYSQL learning experience (7): check my MYSQL learning experience (8): insert update delete my MYSQL learning experience (9): Index I The MYSQL learning experience (10): custom stored procedures and functions MYSQL my learning experience Michael Kors Bedford Logo Large Gold Crossbody Bags (11): view my MYSQL learning experience (12): Trigger My MYSQL learning experience (13): My MYSQL permissions management study Reviews (14): Backup and restore my MYSQL learning experience (15): The log has written a step by step way of ~ optimization Chapter 16. This article describes the MYSQL, MYSQL database optimization is necessary DBA and developers Preparation skills MYSQL optimization on the one hand is to identify system bottlenecks and improve overall performance MYSQL database; on the other hand need a reasonable structure design and parameters adjusted to improve the speed of response to user operations; as well as to conserve system resources, so that the system can provide greater load service if you read my writing two articles, so learning MYSQL index would not be too difficult, because it is interlinked SQLSERVER clustered index and non-clustered indexes again studies (on) SQLSERVER clustered index and non- Again studies (under) clustered index actually MYSQL also have SQLSERVER heap table concept myisam allow no indexes and primary key table exists, personally feel that there is no primary key myisam table belong to heap table because MYSQL does not support the clustered index of non-primary key. innodb engine If no primary key or unique index is not empty, it will automatically generate a 6-byte primary key (not visible to users) Detail Reference: MyISAM vs InnoDB: MySQL storage engine Detailed but 'MyISAM vs InnoDB: MySQL storage engine Xiangjie' Articles also have a little error, intent shared lock is a table lock, is actually wrong 1, Introduction to optimize mysql optimization is multifaceted principle is to reduce system bottlenecks, reduce footprint, and increase the reaction speed of the system. For example, by optimizing the file system, improving disk I / O read and write speeds; Michael Kors Bedford Gusset Medium Tan Crossbody Bags by optimizing the operating system scheduling policy, improve mysql load capacity under high load conditions; optimized table structure, indexes, queries etc. to make queries faster response in mysql, you can use the show status statement to query some of the Michael Kors Crossbody Bags mysql performance parameters show status like 'value'; where the value is the parameter value to be queried, some commonly used performance parameters are as follows: connections: the number of connections mysql server uptime: on-line time mysql server slow_queries: the number of slow queries com_select: query times com_insert: insert number of operations com_update: updating the operating frequency com_delete: delete the number if the number of connections mysql query server, you can execute the following statement to show status like 'connections'; If the query mysql server slow Michael Kors Clutches queries, you can Michael Kors Bedford Logo Monogram Large Coffee Totes execute the following statement show status like 'slow_queries'; 2, query optimization is a database query most frequent operations, improve Michael Kors Bedford Logo Large Purple Crossbody Bags query speed can effectively improve the performance of mysql database (1) analysis query by the query analysis , you can see the implementation of the query to identify bottlenecks mysql query execution provided EXPLAIN statement and DESCRIBE statement to analyze the basic syntax of the query EXPLAIN EXPLAIN statement [EXTENDED] SELECT SELECT_OPTION use EXTENDED keywords, EXPLAIN statement will produce Additional Information. SELECT_OPTION SELECT statement is Michael Kors Bedford Gusset Medium Tan Crossbody Bags the query options, including FROM WHERE clauses, and so the statement is executed, you can analyze the implementation of EXPLAIN select statement later, and be able to analyze some of the characteristics of the query table using EXPLAIN statement to analyze a query USE TEST ; EXPLAIN EXTENDED SELECT * FROM PERSON; the following interpretation of results · idSELECT identifier. This is Michael Kors Accessories a SELECT query serial number. · Select_typeSELECT type, which can be any of the following: SIMPLE: Simple SELECT (not using UNION or subqueries) PRIMARY: represents the main query, or outermost query (multi-table joins Michael Kors Bedford Gusset Medium Michael Kors Berkley Logo Large Beige Clutches Orange Crossbody Bags when) UNION: indicates that the connection query second or later query DEPENDENT UNION: UNION join query or behind the second SELECT statement, depending on the outside of the query UNION RESULT: UNION join query results SUBQUERY: sub-query first SELECT statement DEPENDENT SUBQUERY: subquery first SELECT statement, depending on the outside of the query DERIVED: Export table SELECT (FROM subquery clause) · table indicates the type of query join table Michael Kors Berkley Logo Large Black Clutches · type table below indicates the various connection type according sorted from best to worst type types: (1) system tables only one row (= system table). This is a special case of the const join type. (2) const table at most one matching row, which will be read at the start of the query. The rest of the query optimization are treated as constants. const table queries quickly, because they are read only once. const relatively constant value for PRIMARY KEY or Michael Kors Bedford Signature Large White Totes UNIQUE index of all parts of the occasion. In the following queries, tbl_name can be used for const table: Michael Kors Bedford Logo Medium Black Satchels SELECT * from tbl_name WHERE primary_key = 1; SELECT * from tbl_nameWHERE primary_key_part1 = 1 and primary_key_part2 = 2; (3) eq_ref for each combination from the front row of the table, reads a line from the Michael Kors Outlet table. This is probably the best connection type, except const type. It is used in all parts of an index and the index is coupled use is UNIQUE or PRIMARY KEY. eq_ref can be used to use the '=' operator to compare indexed columns. Comparison value can be a constant or an expression used in the preceding table that read table columns. In the following examples, MySQL can use eq_ref coupled to handle ref_tables: SELECT * FROM ref_table, other_tableWHERE ref_table.key_column = other_table.column; SELECT * FROM ref_table, other_tableWHERE ref_table.key_column_part1 = other_table.column AND ref_table.key_column_part2 = 1; ( 4) ref for each combination of any line from the front of the table, will be read all matching rows in the table. If the join uses only a leftmost prefix of the index key or if the key is not UNIQUE index or PRIMARY KEY, use the ref. If the key used matches only a few rows, this join type is good. ref can be used use = or \u0026 lt; = \u0026 gt; operator of indexed columns. In the following examples, MySQL can use the ref join to process ref_tables: SELECT * FROM ref_table WHERE key_column = expr; SELECT * FROM ref_table, other_tableWHERE ref_table.key_column = other_table.column; SELECT * FROM ref_table, other_tableWHERE ref_table.key_column_part1 = other_table. columnAND ref_table.key_column_part2 = 1; (5) ref_or_null the coupling type like ref, but adds a MySQL can specifically search for the line containing NULL values, often use this type of join optimization in resolving subqueries. In the following examples, MySQL can use ref_or_null coupled to handle ref_tables: SELECT * FROM ref_tableWHERE key_column = expr OR key_column IS NULL; (6)