文章目錄
-
-
- 一.最大連接配接數優化
- 二.啟用查詢緩存
- 三.引擎優化
- 四.索引
- 五.SQL語句優化
-
-
常有的優化方式
一.最大連接配接數優化
- 1.查詢最大連接配接數
- 方法一：
SQL指令查詢
- 方法二：
也可通過DBMS檢視（navicat，workbeanch）
- 2.修改最大連接配接數
- 方法一：
SQL指令修改
//此修改方式重新開機資料庫之後會重置
set global max_connections=300
- 方法二：
修改my.ini檔案(永久修改)
- 在自己的盤符找到配置檔案然後修改
二.啟用查詢緩存
特别注意：查詢緩存從MySQL 5.7.20開始已被棄用，并在MySQL 8.0中被删除。
- 簡單描述MySQL5.0中的操作方法
為什麼MySQL8.0直接把查詢緩存的功能删除了呢？
- 一種說法是不建議使用查詢緩存，
因為查詢緩存往往弊大于利。
隻要有對一個表的更新，這個表上的所有的查詢緩存都會被清空。
三.引擎優化
- 執行以下sql即可查詢到mysql中的存儲引擎
SHOW ENGINES
-
InnoDB存儲引擎
- InnoDB是事務型資料庫的首選引擎，InnoDB是目前MYSQL的預設事務型引擎，是目前最重要、使用最廣泛的存儲引擎。支援事務安全表（ACID），支援行鎖定和外鍵。
- InnoDB主要特性有：
- ①InnoDB給MySQL提供了具有送出、復原和崩潰恢複能力的事物安全（ACID相容）存儲引擎。
- ②InnoDB是為處理巨大資料量的最大性能設計。它的CPU效率可能是任何其他基于磁盤的關系型資料庫引擎鎖不能匹敵的
- ③InnoDB存儲引擎完全與MySQL伺服器整合，InnoDB存儲引擎為在主記憶體中緩存資料和索引而維持它自己的緩沖池。InnoDB将它的表和索引在一個邏輯表空間中，表空間可以包含數個檔案（或原始磁盤檔案）。這與MyISAM表不同，比如在MyISAM表中每個表被存放在分離的檔案中。InnoDB表可以是任何尺寸，即使在檔案尺寸被限制為2GB的作業系統上
- ④InnoDB支援外鍵完整性限制，存儲表中的資料時，每張表的存儲都按主鍵順序存放，如果沒有顯示在表定義時指定主鍵，InnoDB會為每一行生成一個6位元組的ROWID，并以此作為主鍵
- ⑤InnoDB被用在衆多需要高性能的大型資料庫站點上
- 應用場景：
由于其支援事務處理，支援外鍵，支援崩潰修複能力和并發控制
-
MyISAM存儲引擎
- MyISAM基于ISAM存儲引擎，并對其進行擴充。它是在Web、資料倉儲和其他應用環境下最常使用的存儲引擎之一。MyISAM擁有較高的插入、查詢速度，但不支援事物和外鍵。
- MyISAM主要特性有：
- 1、支援大檔案（達到63位檔案長度）
- 2、當把删除和更新及插入操作混合使用的時候，動态尺寸的行産生更少碎片。
- 3、每個MyISAM表最大索引數是64，每個索引最大的列數是16
- 4、最大的鍵長度是1000位元組，這也可以通過編譯來改變，對于鍵長度超過250位元組的 情況，一個超過1024位元組的鍵将被用上
- 5、BLOB和TEXT列可以被索引
- 6、NULL被允許在索引的列中，這個值占每個鍵的0~1個位元組
- 7、所有數字鍵值以高位元組優先被存儲以允許一個更高的索引壓縮
- 8、MyISAM類型表的AUTO_INCREMENT列更新比InnoDB類型的AUTO_INCREMENT更快
- 9、可以把資料檔案和索引檔案放在不同目錄
- 10、每個字元列可以有不同的字元集
- 11、有VARCHAR的表可以固定或動态記錄長度
- 12、VARCHAR和CHAR列可以多達64KB
- 場景：如果表主要是用于插入新記錄和讀出記錄，那麼選擇MyISAM能實作處理高效率。
- 引擎的檢視與修改
-
①檢視引擎
show create table tablename；
SHOW TABLE STATUS FROM sites WHERE NAME='site';
SHOW TABLE STATUS FROM db_name WHERE NAME='table_name';
-
②修改引擎表
alter table table_name engine=innodb;
alter table table_name engine=myisam;
補充知識點
- 行鎖
- ①
讀鎖\樂觀鎖
select column from table where conditions lock in share mode；
- ②
寫鎖\悲觀鎖
select math from zje where math >60 for update；
- 小結：
- a. 行鎖隻有InnoDB有
- b. 送出事務就是釋放鎖
- c. 鎖需要在事務内開啟
引擎優化小結
- ①一個資料庫中多個表可以使用不同引擎以滿足各種性能和實際需求，使用合适的存儲引擎，将會提高整個資料庫的性能
- ②InnoDB提供送出、復原、崩潰恢複能力及并發控制能力，适用于對資料更新操作頻率高的資料表
- ③MyISAM引擎能提供較高的查詢效率，适用于對資料進行頻繁查詢操作的資料表
四.索引
- 這裡是便于查詢可以設定索引，讓查詢效率變高
| 索引分類 | 文法 |
|---|---|
| 普通索引 | create index name on s1(name); |
| 唯一索引 | create unique index age on s1(age); |
| 聚合索引（多字段） | create index name on s1(id,name); |
| 全文索引（MyISAM） | ALTER TABLE article ADD FULLTEXT INDEX 索引名稱(title,content) |
- 聚合索引按照從左到右的比對原則。也就是必須先比對ID才能比對name查詢。
- 全文檢索的查詢方式：
-
SELECT * FROM article WHERE MATCH(title, content) AGAINST('查詢字元串')
- 全文索引隻适合MyISAM引擎的資料表。并且隻能對英文進行檢索
五.SQL語句優化
- SQL優化的重心是查詢優化，查詢優化的重心是建立索引。是以
查詢優化主要是避免出現導緻索引失效的查詢
- ①避免在索引列上出現null。
- ②不要在索引列上進行算術運算。：select age+1 from user
- ③避免實作!=或者<>、is null或者is not null、in等可能導緻全表周遊的操作。
- ④模糊查詢隻能使用右邊%。
- ⑤where語句後盡可能少用小括号、或者不要出現小括号嵌套小括号。
-
小結：
① 表不能太多（超過200張）
②單表的列數不能太多（超過40列）
③32位系統單表最大4G，64位沒有限制。
Whatever is worth doing is worth doing well.
2020.03.02