久久精品国产精品亚洲人人,久久久久久久久久久久久久,欧美日韩中文字幕久久久不卡

mysql教程欄目介紹關系數據庫的sql的生命周期。

關系數據庫之mysql三：從一條sql的生命周期說起

mysql Query Processing

sql的執行過程和mysql體系架構基本一致

執行過程：

? ? ? ? ? ?? 關系數據庫之mysql三：從一條sql的生命周期說起

連接器：

? ? ? ?建立與 MySQL 的連接，用于查詢SQL語句，判斷權限。

查詢緩存： ?

如果語句不在查詢緩存中，就會繼續后面的執行階段。執行完成后，執行結果會被存入查詢緩存中
如果查詢命中緩存，MySQL不需要執行后面的復雜操作，就可以直接返回結果，提升效率

分析器：

? ? ? ?對 SQL 語句進行硬解析，分析器先會做詞法分析。分析SQL 語句的組成成分。判斷輸入的 SQL 語句是否滿足語法規則。?

優化器：

? ? ? ?優化器是在表里面有多個索引的時候，決定使用哪個索引；或者在一個語句有多表關聯（join）的時候，決定各個表的連接順序。不同的執行方法的邏輯結果是一樣的，但是執行的效率會有不同，而優化器的作用就是決定選擇使用哪一個方案。

執行器：

有索引：第一次調用的是取滿足條件的第一行這個接口，之后循環取滿足條件的下一行這個接口，最終把查詢結果返回客戶端
無索引：調用 InnoDB 引擎接口取這個表的第一行，判斷sql查詢條件，如果不是則跳過，如果是則將這行存在結果集中；調用引擎接口取下一行，重復相同的判斷邏輯，直到取到這個表的最后一行。執行器將上述遍歷過程中所有滿足條件的行組成的記錄集作為結果集返回給客戶端

理解執行計劃

EXPLAIN命令輸出MySQL將如何執行你的SQL語句，但不會返回數據

如何使用

[root@localhost][(none)]> explain select * from 表名 where project_id = 36; +----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+ | id | select_type | table                    | partitions | type | possible_keys | key        | key_len | ref   | rows   | filtered | Extra | +----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+ |  1 | SIMPLE      | 表名                     | NULL       | ref  | project_id    | project_id | 4       | const | 797964 |   100.00 | NULL  | +----+-------------+--------------------------+------------+------+---------------+------------+---------+-------+--------+----------+-------+復制代碼

id

id相同執行順序由上至下
id不同，id值越大優先級越高，越先被執行

select_type

SIMPLE：簡單的 select 查詢，查詢中不包含子查詢或者 union?
PRIMARY：查詢中包含子部分，最外層查詢則被標記為 primary?
DERIVED：是子查詢from的一部分
DEPENDENT SUBQUERY：子查詢中的第一個SELECT，子查詢依賴于外層查詢的結果
SUBQUERY 表示在 select 或 where 列表中包含了子查詢，
MATERIALIZED：表示 where 后面 in 條件的子查詢?
UNION：表示 union 中的第二個或后面的 select 語句?
UNION RESULT：union 的結果?

table

表對象

type

system > const > eq_ref > ref > range > index > ALL（查詢效率）

system：表中只有一條數據，這個類型是特殊的const類型
const：針對于主鍵或唯一索引的等值查詢掃描，最多只返回一個行數據。速度非?？?，因為只讀取一次即可。
eq_ref：此類型通常出現在多表的join查詢，表示對于前表的每一個結果，都只能匹配到后表的一行結果，并且查詢的比較操作通常是=，查詢效率較高
ref：此類型通常出現在多表的join查詢，針對于非唯一或非主鍵索引，或者是使用了最左前綴規則索引的查詢
range：范圍掃描?這個類型通常出現在 , >, >=, , BETWEEN, IN() 操作中
index：索引樹掃描?
ALL：全表掃描（full table scan）?

possible_keys

可能使用的索引，注意不一定會使用
查詢涉及到的字段上若存在索引，則該索引將被列出來
當該列為NULL時就要考慮當前的SQL是否需要優化了

key

顯示MySQL在查詢中實際使用的索引，若沒有使用索引，顯示NULL。?
查詢中若使用了覆蓋索引(覆蓋索引：索引的數據覆蓋了需要查詢的所有數據)，則該索引僅出現在key列表中?

key_length

索引長度?

ref

表示上述表的連接匹配條件，即哪些列或常量被用于查找索引列上的值?

rows

返回估算的結果集數目，并不是準確的值

filtered

示返回結果的行數占需讀取行數的百分比， filtered 的值越大越好

extra

Using where：表示優化器需要通過索引回表，之后到server層進行過濾查詢數據?
Using index：表示直接訪問索引就足夠獲取到所需要的數據，不需要回表?
Using index condition：在5.6版本后加入的新特性（Index Condition Pushdown）?
Using index for group-by：使用了索引來進行GROUP BY或者DISTINCT的查詢
Using filesort：當 Extra 中有 Using filesort 時, 表示 MySQL 需額外的排序操作, 不不能通過索引順序達到排序效果. 一般有 Using filesort, 都建議優化去掉, 因為這樣的查詢 CPU 資源消耗大
Using temporary 臨時表被使用，時常出現在GROUP BY和ORDER BY子句情況下。（sort buffer或者磁盤被使用）

? ? ? ?光看 filesort 字面意思，可能以為是要利用磁盤文件進行排序，實則不全然。當MySQL不能使用索引進行排序時，就會利用自己的排序算法(快速排序算法)在內存(sort buffer)中對數據進行排序，如果內存裝載不下，它會將磁盤上的數據進行分塊，再對各個數據塊進行排序，然后將各個塊合并成有序的結果集（實際上就是外排序）。

? ? ? ?當對連接操作進行排序時，如果ORDER BY僅僅引用第一個表的列，MySQL對該表進行filesort操作，然后進行連接處理，此時，EXPLAIN輸出“Using filesort”；否則，MySQL必須將查詢的結果集生成一個臨時表，在連接完成之后行行filesort操作，此時，EXPLAIN輸出“Using temporary;Using filesort”。

提高查詢效率

正確使用索引

為解釋方便，來一個demo：

DROP TABLE IF EXISTS user;  CREATE TABLE user(  id int AUTO_INCREMENT PRIMARY KEY,  user_name varchar(30) NOT NULL,  gender bit(1) NOT NULL DEFAULT b’1’,  city varchar(50) NOT NULL,  age int NOT NULL  )ENGINE=InnoDB DEFAULT CHARSET=utf8; ALTER TABLE user ADD INDEX idx_user(user_name , city , age);  復制代碼

什么樣的索引可以被使用？

**全匹配：**SELECT * FROM user WHERE user_name=’JueJin’AND age=’5′ AND city=’上海’;（與where后查詢條件的順序無關） ?
匹配最左前綴：(user_name )、(user_name, city)、(user_name , city , age)（滿足最左前綴查詢條件的順序與索引列的順序無關，如：(city, user_name)、(age, city, user_name)）?
**匹配列前綴：**SELECT * FROM user WHERE user_name LIKE ‘W%’?
**匹配范圍值：**SELECT * FROM user WHERE user_name BETWEEN ‘W%’ AND ‘Z%’

什么樣的索引無法被使用？

**where查詢條件中不包含索引列中的最左索引列，則無法使用到索引：?**

? ? ? ?SELECT * FROM user WHERE city=’上海’;?

? ? ? ?SELECT * FROM user WHERE age=’26’;?

? ? ? ?SELECT * FROM user WHERE age=’26’ AND city=‘上海’;?

**即使where的查詢條件是最左索引列，也無法使用索引查詢用戶名以N結尾的用戶：?**

? ? ? ?SELECT * FROM user WHERE user_name LIKE ‘%N’;?

**如果where查詢條件中有某個列的范圍查詢，其右邊的所有列都無法使用索引優化查詢：?**

? ? ? ?SELECT * FROM user WHERE user_name=’JueJin’ AND city LIKE ‘上%’ AND age=31;?

**索引列不能是表達式的一部分，也不能作為函數的參數，否則無法使用索引查詢：?**

? ? ? ?SELECT * FROM user WHERE user_name=concat(user_name,’PLUS’);

選擇合適的索引列順序?

在組合索引的創建中索引列的順序非常重要，正確的索引順序依賴于使用該索引的查詢的查詢方式
對于組合索引的索引順序可以將選擇性最高的列放到索引最前列，該法則與前綴索引的選擇性方法一致
并不是說所有的組合索引的順序都使用該法則就能確定，還需要根據具體的查詢場景來確定具體的索引順序

覆蓋索引條件?

如果一個索引中包含所有要查詢的字段的值，那么就稱之為覆蓋索引

? ? ? ?SELECT user_name, city, age FROM user WHERE user_name=’Tony’ AND age=’28’ AND city=’上海’;

因為要查詢的字段(user_name, city, age)都包含在組合索引的索引列中，所以就使用了覆蓋索引查詢，查看是否使用了覆蓋索引可以通過執行計劃中的Extra中的值為Using index則證明使用了覆蓋索引，覆蓋索引可以極大的提高訪問性能。

使用索引進行排序

? ? ? ?在排序操作中如果能使用到索引來排序，那么可以極大地提高排序的速度，要使用索引來排序需要滿足以下兩點即可：?

ORDER BY子句后的列順序要與組合索引的列順序一致，且所有排序列的排序方向(正序/倒序)需一致?
所查詢的字段值需要包含在索引列中，及滿足覆蓋索引

排序可用demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name;
SELECT user_name, city, age FROM user_test ORDER BY user_name,city;?
SELECT user_name, city, age FROM user_test ORDER BY user_name DESC,city DESC;?
SELECT user_name, city, age FROM user_test WHERE user_name=’Tony’ ORDER BY city;

排序不可用demo：

SELECT user_name, city, age FROM user_test ORDER BY user_name gender;?
SELECT user_name, city, age, gender FROM user_test ORDER BY user_name;?
SELECT user_name, city, age FROM user_test ORDER BY user_name ASC,city DESC;?
SELECT user_name, city, age FROM user_test WHERE user_name LIKE ‘W%’ ORDER BY city;