后端優(yōu)化那些事兒~ MySQL優(yōu)化第6天

本篇博客再次去復(fù)盤(pán) Mysql 中的索引，從該角度學(xué)習(xí) Mysql 的優(yōu)化。

索引

索引不是越多越好，因?yàn)閿?shù)據(jù)庫(kù)在插入，刪除，更新數(shù)據(jù)時(shí)，都會(huì)更新索引，導(dǎo)致效率降低。

新建索引的時(shí)候優(yōu)先選擇離散度高的索引（列中不同值多的，適合做索引，例如 id 適合，status 狀態(tài)不適合，因?yàn)橐话阒挥?個(gè)值），如果一個(gè)列中 NULL 值特別多，也不適合做索引列。

如果僅依賴一列無(wú)法做到唯一，可以選擇幾列同時(shí)作為索引，即復(fù)合索引，復(fù)合索引要優(yōu)先把離散度高的列放在前面，形式上是左側(cè)。

優(yōu)化細(xì)節(jié)

盡量避免 select * 的出現(xiàn)，在僅有主鍵索引的情況下，下述命令時(shí)間差別（假設(shè)查詢的是一個(gè)超過(guò)百萬(wàn)數(shù)據(jù)的表格）

select * from 百萬(wàn)表名 # 耗時(shí) 1.9s select id,name,age from 百萬(wàn)表名 # 耗時(shí) 1.5s

如果確定一張表中主鍵沒(méi)有間隔，即完整連續(xù)，可以使用下述分頁(yè)辦法提高查詢效率。

select id,name from 百萬(wàn)表名 limit 1000000,10000; # 0.25秒 select id,name from 百萬(wàn)表名 where id > 1000000 limit 10000; # 0.024秒

在僅有主鍵索引的情況下，速度提高了 10 倍。

where 查詢注意事項(xiàng)

優(yōu)先使用帶索引的列，例如下述兩個(gè)查詢，得到的結(jié)果一樣，但耗時(shí)卻相差較大

select id,name from 百萬(wàn)表名 where id =5 # 0.02秒 select id,name from 百萬(wàn)表名 where tid = 88888 # 0.3秒

先使用索引，然后在進(jìn)行正向過(guò)濾（=，in，<，>），最后執(zhí)行復(fù)雜條件（子查詢，like 模糊查詢）

優(yōu)先過(guò)濾數(shù)字型字段，然后在選擇字符串；

條件里面字段類(lèi)型要明確，例如數(shù)字型字段=數(shù)字，字符型字段=字符

不用OR，而用 union 或 union all 進(jìn)行組合

union 操作符用于合并兩個(gè)或多個(gè) select 語(yǔ)句的結(jié)果集，前提是結(jié)果集的列數(shù)相同，每列的數(shù)據(jù)類(lèi)型也相同。

union：合并兩個(gè)查詢結(jié)果中相同的數(shù)據(jù)；

union all：不合并兩個(gè)查詢結(jié)果中的相同數(shù)據(jù)

可以對(duì)比下述語(yǔ)句的執(zhí)行計(jì)劃

select * from `百萬(wàn)表名` where id = 5 or id = 1 order by id desc (select * from `百萬(wàn)表名` where id = 5 order by id desc) union all (select * from `百萬(wàn)表名` where id = 1 order by id desc) order by null

盡量使用 = 替換 in，盡量避免使用非正向過(guò)濾，例如 !，!=，not in，not exists，not like

盡量不使用 %匹配內(nèi)容。

如果在 in 中查詢的數(shù)據(jù)是連續(xù)的，可以使用 between...and... 代替；

實(shí)戰(zhàn)中也可以使用 exists 代替 in

select id from `百萬(wàn)表名` where exists(select 1 from 另一張表 where id=`百萬(wàn)表名`.id)；

影響數(shù)據(jù)庫(kù)性能的參數(shù)

公共參數(shù)及其默認(rèn)值

同時(shí)最大連接數(shù)

max_connections = 151

查詢排序時(shí)緩沖區(qū)大小，僅限制 order by 和 group by

sort_buffer_size = 2M

打開(kāi)文件數(shù)限制

open_files_limit = 1024

InnoDB參數(shù)及其默認(rèn)值

查看所有配置使用如下命令

show variables like 'innodb_%';

索引和數(shù)據(jù)緩沖區(qū)大小

innodb_buffer_pool_size = 128M # 建議設(shè)置內(nèi)存大小的60%~70%

緩沖池實(shí)例個(gè)數(shù)

innodb_buffer_pool_instances = 1 # 建議設(shè)置為 4 或者 8

控制MySQL的磁盤(pán)寫(xiě)入策略

innodb_flush_log_at_trx_commit = 1

這部分找到一篇講解的不錯(cuò)的 博客，可以參考學(xué)習(xí)

是否共享表空間

innodb_file_per_table = OFF

關(guān)閉獨(dú)立表空間將導(dǎo)致共享表空間 idbdata 持續(xù)增大，從而影響 I/O 性能，可以修改為開(kāi)啟獨(dú)立表空間模式。

innodb_file_per_table = 1 # 為使用獨(dú)占表空間 innodb_file_per_table = 0 # 為使用共享表空間

與之對(duì)應(yīng)的幾個(gè)配置如下所示：

修改獨(dú)占空表空間的數(shù)據(jù)存儲(chǔ)位置

innodb_data_home_dir = "C:\mysql\data\" # 數(shù)據(jù)庫(kù)文件所存放的目錄 innodb_log_group_home_dir = "C:\mysql\data\" # 日志存放目錄 innodb_data_file_path=ibdata1:10M:autoextend innodb_file_per_table=1

日志緩沖區(qū)大小

innodb_log_buffer_size = 8M

MySQL

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。

版權(quán)聲明：本文內(nèi)容由網(wǎng)絡(luò)用戶投稿，版權(quán)歸原作者所有，本站不擁有其著作權(quán)，亦不承擔(dān)相應(yīng)法律責(zé)任。如果您發(fā)現(xiàn)本站中有涉嫌抄襲或描述失實(shí)的內(nèi)容，請(qǐng)聯(lián)系我們jiasou666@gmail.com 處理，核實(shí)后本網(wǎng)站將在24小時(shí)內(nèi)刪除侵權(quán)內(nèi)容。