SQL performance optimization techniques, Mrs. Mrs. Mrs. Too useful!

SQL performance optimization techniques, Mrs. Mrs. Mrs. Too useful! First, briefly review the basic concepts of Mysql, and then divided into the creation and query of these two stages of optimization. 1 Basic Concepts 1.1 Logical Architecture

 Union query is deduplicated by default. You are advised to use Union All , which is more efficient, if services do not require it

1. If the field type in the condition is inconsistent with the table structure type, the mysql system automatically adds a conversion function, which invalidates the index as a parameter in the function. 2. If the preceding part of the like query is not entered, the index cannot be matched if the value starts with %. 3. Added two new features in version 5.7: Generated column, CREATE TABLE triangle (sidea DOUBLE, sideb DOUBLE, area DOUBLE AS (sidea * sideb / 2)); insert into triangle(sidea, sideb) values(3, 4); select * from triangle;

+——-+——-+——+ | sidea | sideb | area | +——-+——-+——+ | 3 | 4 | 6 | +——-+——-+——+ Supports DATA in JSON format and provides built-in functions CREATE TABLE jSON_test (name JSON). INSERT INTO json_test VALUES(‘{“name1”: “value1”, “name2”: “value2”}’); SELECT * FROM json_test WHERE JSON_CONTAINS(name, ‘$.name1’); Explain SELECT settleId FROM Settle WHERE settleId = “3679”

 select_type, with several values: Simple (represents a simple select, without union and subquery), primary (has a subquery, the outermost select query is primary), union (the second or subsequent select query in the union, does not depend on external query results), Dependent union (the second or subsequent select query in union, which depends on external query results)   type, which has several values: System (table has only one row (= system table), which is a special case of the const join type), const (constant query), ref(non-unique index access, normal index only), eq_ref (use unique index or component query), all (full table query), index (full table query by index),   possible_keys:   key, select the index to use   key_len, select the index length   rows, scan the number of rows,   extra, select   key, select   key_len, select   rows, scan the number of rows,   extra, select Only index, where used, Using filesort, Using temporary Every book we read, every language we learn, may give us an unexpected return in the future. In fact, as a developer, it is particularly important to have a learning atmosphere and a communication circle. Here I recommend a Java learning exchange group 342016322. No matter you are small white or big bull, welcome to enter.