MySQL如何優(yōu)化

表的設(shè)計(jì)合理化(符合3NF)软舌；
添加適當(dāng)索引(index) [四種: 普通索引羽圃、主鍵索引欢搜、唯一索引unique封豪、全文索引]；
SQL語句優(yōu)化炒瘟；
分表技術(shù)(水平分割吹埠、垂直分割)；
讀寫[寫: update/delete/add]分離疮装；
存儲(chǔ)過程 [模塊化編程缘琅，可以提高速度]；
對(duì)mysql配置優(yōu)化 [配置最大并發(fā)數(shù)my.ini, 調(diào)整緩存大小 ]廓推；
mysql服務(wù)器硬件升級(jí)刷袍；
定時(shí)的去清除不需要的數(shù)據(jù),定時(shí)進(jìn)行碎片整理(MyISAM)

數(shù)據(jù)庫設(shè)計(jì)

什么是數(shù)據(jù)庫范式

為了建立冗余較小、結(jié)構(gòu)合理的數(shù)據(jù)庫樊展，設(shè)計(jì)數(shù)據(jù)庫時(shí)必須遵循一定的規(guī)則呻纹。在關(guān)系型數(shù)據(jù)庫中這種規(guī)則就稱為范式。范式是符合某一種設(shè)計(jì)要求的總結(jié)滚局。要想設(shè)計(jì)一個(gè)結(jié)構(gòu)合理的關(guān)系型數(shù)據(jù)庫居暖，必須滿足一定的范式。

數(shù)據(jù)庫三大范式

第一范式：1NF是對(duì)屬性的原子性約束藤肢，要求屬性(列)具有原子性太闺，不可再分解；(只要是關(guān)系型數(shù)據(jù)庫都滿足1NF)
第二范式：2NF是對(duì)記錄的惟一性約束嘁圈，表中的記錄是唯一的, 就滿足2NF, 通常我們?cè)O(shè)計(jì)一個(gè)主鍵來實(shí)現(xiàn)省骂，主鍵不能包含業(yè)務(wù)邏輯。
第三范式：3NF是對(duì)字段冗余性的約束最住，它要求字段沒有冗余钞澳。沒有冗余的數(shù)據(jù)庫設(shè)計(jì)可以做到。

但是涨缚，沒有冗余的數(shù)據(jù)庫未必是最好的數(shù)據(jù)庫轧粟，有時(shí)為了提高運(yùn)行效率，就必須降低范式標(biāo)準(zhǔn)脓魏，適當(dāng)保留冗余數(shù)據(jù)兰吟。具體做法是：在概念數(shù)據(jù)模型設(shè)計(jì)時(shí)遵守第三范式，降低范式標(biāo)準(zhǔn)的工作放到物理數(shù)據(jù)模型設(shè)計(jì)時(shí)考慮茂翔。降低范式就是增加字段混蔼，允許冗余。

分表分庫

垂直拆分

垂直拆分就是要把表按模塊劃分到不同數(shù)據(jù)庫表中（當(dāng)然原則還是不破壞第三范式）珊燎，這種拆分在大型網(wǎng)站的演變過程中是很常見的惭嚣。當(dāng)一個(gè)網(wǎng)站還在很小的時(shí)候遵湖，只有小量的人來開發(fā)和維護(hù)，各模塊和表都在一起晚吞，當(dāng)網(wǎng)站不斷豐富和壯大的時(shí)候延旧，也會(huì)變成多個(gè)子系統(tǒng)來支撐，這時(shí)就有按模塊和功能把表劃分出來的需求槽地。其實(shí)垄潮，相對(duì)于垂直切分更進(jìn)一步的是服務(wù)化改造，說得簡單就是要把原來強(qiáng)耦合的系統(tǒng)拆分成多個(gè)弱耦合的服務(wù)闷盔，通過服務(wù)間的調(diào)用來滿足業(yè)務(wù)需求看，因此表拆出來后要通過服務(wù)的形式暴露出去旅急，而不是直接調(diào)用不同模塊的表逢勾，淘寶在架構(gòu)不斷演變過程，最重要的一環(huán)就是服務(wù)化改造藐吮，把用戶溺拱、交易、店鋪谣辞、寶貝這些核心的概念抽取成獨(dú)立的服務(wù)迫摔，也非常有利于進(jìn)行局部的優(yōu)化和治理，保障核心模塊的穩(wěn)定性泥从。

垂直拆分用于分布式場景句占。

水平拆分

上面談到垂直切分只是把表按模塊劃分到不同數(shù)據(jù)庫，但沒有解決單表大數(shù)據(jù)量的問題躯嫉，而水平切分就是要把一個(gè)表按照某種規(guī)則把數(shù)據(jù)劃分到不同表或數(shù)據(jù)庫里纱烘。例如像計(jì)費(fèi)系統(tǒng)，通過按時(shí)間來劃分表就比較合適祈餐，因?yàn)橄到y(tǒng)都是處理某一時(shí)間段的數(shù)據(jù)擂啥。而像SaaS應(yīng)用，通過按用戶維度來劃分?jǐn)?shù)據(jù)比較合適帆阳，因?yàn)橛脩襞c用戶之間的隔離的哺壶，一般不存在處理多個(gè)用戶數(shù)據(jù)的情況，簡單的按user_id范圍來水平切分蜒谤。

通俗理解：水平拆分行山宾，行數(shù)據(jù)拆分到不同表中，垂直拆分列芭逝，表數(shù)據(jù)拆分到不同表中塌碌。

水平拆分示例

3張用戶表和1張uuid表，用來提供自增的id旬盯。

@Service
public class UserService {

    @Autowired
    private JdbcTemplate jdbcTemplate;

    public String add(String name, String pwd) {
        // 1.先獲取到 自定增長ID
        String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
        jdbcTemplate.update(idInsertSQL);
        Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        // 2.判斷存儲(chǔ)表名稱
        String tableName = "user" + insertId % 3;
        // 3.注冊(cè)數(shù)據(jù)
        String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
                + "');";
        System.out.println("insertUserSql:" + insertUserSql);
        jdbcTemplate.update(insertUserSql);
        return "success";
    }

    public String get(Long id) {
        String tableName = "user" + id % 3;
        String sql = "select name from " + tableName + "  where id="+id;
        System.out.println("SQL:" + sql);
        String name = jdbcTemplate.queryForObject(sql, String.class);
        return name;
    }

}

索引

什么是索引

索引用來快速地尋找那些具有特定值的記錄台妆，所有MySQL索引都以B-樹的形式保存翎猛。如果沒有索引，執(zhí)行查詢時(shí)MySQL必須從第一個(gè)記錄開始掃描整個(gè)表的所有記錄接剩，直至找到符合要求的記錄切厘。表里面的記錄數(shù)量越多，這個(gè)操作的代價(jià)就越高懊缺。如果作為搜索條件的列上已經(jīng)創(chuàng)建了索引疫稿，MySQL無需掃描任何記錄即可迅速得到目標(biāo)記錄所在的位置。如果表有1000個(gè)記錄鹃两，通過索引查找記錄至少要比順序掃描記錄快100倍遗座。

查詢索引

desc 表名;   --不能顯示索引名稱
show index from 表名;
show keys from 表名;

索引的分類

主鍵索引

主鍵是一種唯一性索引，但它必須指定為“PRIMARY KEY”俊扳。如果你曾經(jīng)用過AUTO_INCREMENT類型的列途蒋，你可能已經(jīng)熟悉主鍵之類的概念了。主鍵一般在創(chuàng)建表的時(shí)候指定馋记，例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”号坡。但是，我們也可以通過修改表的方式加入主鍵梯醒，例如“ALTER TABLE
tablename ADD PRIMARY KEY (列的列表); ”宽堆。每個(gè)表只能有一個(gè)主鍵。

--添加主鍵索引
alter table 表名 add primary key (列名);
--刪除主鍵索引
alter table articles drop primary key;

全文索引

1茸习、創(chuàng)建全文索引表畜隶；

CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

2、查詢?nèi)乃饕?/p>

--錯(cuò)誤用法:
select * from articles where body like '%mysql%'; 錯(cuò)誤用法 索引不會(huì)生效
--正確用法:
select * from articles where match(title,body) against ( 'database')

3逮光、全文索引注意事項(xiàng)代箭；

在mysql中fulltext 索引只針對(duì) myisam生效，mysql自己提供的fulltext針對(duì)英文生效涕刚，使用sphinx (coreseek) 技術(shù)處理中文嗡综，使用方法是 match(字段名..) against(‘關(guān)鍵字’)；
全文索引：停止詞杜漠，因?yàn)樵谝粋€(gè)文本中极景，創(chuàng)建索引是一個(gè)無窮大的數(shù)，因此驾茴，對(duì)一些常用詞和字符盼樟，就不會(huì)創(chuàng)建，這些詞锈至，稱為停止詞.比如（a晨缴，b，mysql峡捡，the）击碗；
```
--輸出的是每行和database的匹配度
select match(title,body) against ('database') from articles;
```

唯一索引

這種索引和前面的“普通索引”基本相同筑悴，但有一個(gè)區(qū)別：索引列的所有值都只能出現(xiàn)一次，即必須唯一稍途。唯一性索引可以用以下幾種方式創(chuàng)建：

--創(chuàng)建索引
CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表)阁吝； 
--修改表
ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表)； 
--創(chuàng)建表的時(shí)候指定索引
CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) )械拍；

unique字段可以為NULL,并可以有多NULL, 但是如果是具體內(nèi)容突勇，則不能重復(fù)，但是不能存有重復(fù)的空字符串

普通索引

普通索引（由關(guān)鍵字KEY或INDEX定義的索引）的唯一任務(wù)是加快對(duì)數(shù)據(jù)的訪問速度坷虑。因此甲馋，應(yīng)該只為那些最經(jīng)常出現(xiàn)在查詢條件（WHEREcolumn=）或排序條件（ORDERBYcolumn）中的數(shù)據(jù)列創(chuàng)建索引。只要有可能迄损，就應(yīng)該選擇一個(gè)數(shù)據(jù)最整齊摔刁、最緊湊的數(shù)據(jù)列（如一個(gè)整數(shù)類型的數(shù)據(jù)列）來創(chuàng)建索引。

create table ccc(
id int unsigned,
name varchar(32)
)
create index 索引名 on 表 (列1,列名2);

索引的代價(jià)

占用磁盤空間
對(duì)DML(update海蔽、delete、insert)語句的效率影響
增刪改會(huì)對(duì)索引影響绑谣，因?yàn)樗饕匦抡怼?br> | 存儲(chǔ)引擎 | 允許的索引類型 |
| :---------: | :------------: |
| myisam | btree |
| innodb | btree |
| memory/yeap | Hash,btree |

添加索引需要注意：

在where條件經(jīng)常使用党窜；

該字段的內(nèi)容不是唯一的幾個(gè)值；

字段內(nèi)容不是頻繁變化借宵。

查詢索引使用率

show status like ‘handler_read%’;

handler_read_key: --這個(gè)值越高越好幌衣，越高表示使用索引查詢到的次數(shù)。
handler_read_rnd_next:  --這個(gè)值越高壤玫，說明查詢低效

總結(jié)

--創(chuàng)建主鍵索引
alter table 表名 add primary key (列名);
--創(chuàng)建一個(gè)聯(lián)合索引
alter table dept add index my_ind (dname,loc); --dname 左邊的列,loc就是右邊的列

對(duì)于創(chuàng)建的多列索引豁护，如果不是使用第一部分，則不會(huì)創(chuàng)建索引欲间；
```
explain select * from dept where loc='aaa';  --就不會(huì)使用到索引
```
模糊查詢?cè)趌ike前面有百分號(hào)開頭會(huì)失效楚里；
如果條件中有or，即使其中有條件帶索引也不會(huì)使用猎贴。換言之班缎，就是要求使用的所有字段，都必須建立索引, 才會(huì)使用索引她渴；
如果列類型是字符串达址，那一定要在條件中將數(shù)據(jù)使用引號(hào)引用起來。否則不使用索引趁耗。(添加時(shí),字符串必須“ ”), 也就是沉唠，如果列是字符串類型，就一定要用“ ”把他包括起來苛败；
如果mysql估計(jì)使用全表掃描要比使用索引快满葛，則不使用索引径簿。

SQL優(yōu)化

查看Mysql服務(wù)器狀態(tài)

使用show status查看MySQL服務(wù)器狀態(tài)信息：

--mysql數(shù)據(jù)庫啟動(dòng)了多少時(shí)間
show status like 'uptime';

--類推 update  delete（顯示數(shù)據(jù)庫的查詢，更新纱扭，添加牍帚，刪除的次數(shù)）
show  stauts like 'com_select'  

--默認(rèn)是session 會(huì)話，指取出當(dāng)前窗口的執(zhí)行乳蛾，如果你想看所有(從mysql 啟動(dòng)到現(xiàn)在暗赶，則應(yīng)該 global)
show [session|global] status like .... 

--顯示到mysql數(shù)據(jù)庫的連接數(shù)
show status like  'connections '; 

--顯示慢查詢次數(shù)
show status like 'slow_queries';

慢查詢

什么是慢查詢

MySQL默認(rèn)10秒內(nèi)沒有響應(yīng)SQL結(jié)果,則為慢查詢，可以修改慢查詢肃叶。

修改慢查詢

--查詢慢查詢時(shí)間
show variables like 'long_query_time';
--修改慢查詢時(shí)間
set long_query_time=1; ---但是重啟mysql之后蹂随，long_query_time依然是my.ini中的值

將慢查詢定位到日志中

在默認(rèn)情況下，我們的mysql不會(huì)記錄慢查詢因惭，需要在啟動(dòng)mysql時(shí)候岳锁，指定記錄慢查詢才可以。

bin\mysqld.exe --safe-mode  --slow-query-log [mysql5.5 可以在my.ini指定]（安全模式啟動(dòng)蹦魔，數(shù)據(jù)庫將操作寫入日志激率，以備恢復(fù)）
bin\mysqld.exe –log-slow-queries=d:/abc.log [低版本mysql5.0可以在my.ini指定]

先關(guān)閉mysql,再啟動(dòng), 如果啟用了慢查詢?nèi)罩荆J(rèn)把這個(gè)文件放在my.ini 文件中記錄的位置：

#Path to the database root
datadir=" C:/ProgramData/MySQL/MySQL Server 5.5/Data/"

MySQL數(shù)據(jù)引擎

myisam存儲(chǔ)：如果表對(duì)事務(wù)要求不高勿决，同時(shí)是以查詢和添加為主的乒躺，我們考慮使用myisam存儲(chǔ)引擎，比如 bbs 中的發(fā)帖表低缩，回復(fù)表嘉冒。
INNODB 存儲(chǔ)：對(duì)事務(wù)要求高，保存的數(shù)據(jù)都是重要數(shù)據(jù)咆繁，我們建議使用INNODB讳推，比如訂單表，賬號(hào)表玩般。
Memory存儲(chǔ)：比如我們數(shù)據(jù)變化頻繁银觅，不需要入庫，同時(shí)又頻繁的查詢和修改坏为，我們考慮使用memory, 速度極快. （如果mysql重啟的話设拟，數(shù)據(jù)就不存在了）。

MyISAM 和 INNODB的區(qū)別

事務(wù)安全（MyISAM不支持事務(wù)久脯，INNODB支持事務(wù)）纳胧；
查詢和添加速度（MyISAM批量插入速度快）；
支持全文索引（MyISAM支持全文索引帘撰，INNODB不支持全文索引）跑慕；
鎖機(jī)制（MyISAM時(shí)表鎖，innodb是行鎖）；
外鍵 MyISAM 不支持外鍵核行， INNODB支持外鍵. (在PHP開發(fā)中牢硅，通常不設(shè)置外鍵，通常是在程序中保證數(shù)據(jù)的一致)芝雪。
如果你的數(shù)據(jù)庫的存儲(chǔ)引擎是myisam减余，請(qǐng)一定記住要定時(shí)進(jìn)行碎片整理，optimize table test100;

image

總結(jié)

使用group by 分組查詢是惩系，默認(rèn)分組后位岔，還會(huì)排序，可能會(huì)降低速度堡牡，在group by 后面增加 order by null 就可以防止排序抒抬；
```
explain select * from emp  group by deptno order by null;
```

有些情況下，可以使用連接來替代子查詢晤柄。因?yàn)槭褂胘oin擦剑，MySQL不需要在內(nèi)存中創(chuàng)建臨時(shí)表。

select * from dept, emp where dept.deptno=emp.deptno; --[簡單處理方式]
select * from dept left join emp on dept.deptno=emp.deptno; --[左外連接芥颈，更ok!]

對(duì)查詢進(jìn)行優(yōu)化惠勒，要盡量避免全表掃描，首先應(yīng)考慮在 where 及 order by 涉及的列上建立索引爬坑，盡量避免在 where 子句中對(duì)字段進(jìn)行 null 值判斷捉撮，否則將導(dǎo)致引擎放棄使用索引而進(jìn)行全表掃描，如：select id from t where num is null妇垢；最好不要給數(shù)據(jù)庫留 NULL，盡可能的使用 NOT NULL 填充數(shù)據(jù)庫肉康。
備注闯估、描述、評(píng)論之類的可以設(shè)置為 NULL吼和，其他的涨薪，最好不要使用 NULL。不要以為 NULL 不需要空間炫乓，比如：char(100) 型刚夺，在字段建立時(shí)，空間就固定了末捣，不管是否插入值（NULL 也包含在內(nèi)）侠姑，都是占用 100 個(gè)字符的空間的，如果是 varchar 這樣的變長字段箩做， null 不占用空間莽红。可以在 num 上設(shè)置默認(rèn)值 0，確保表中 num 列沒有 null 值安吁，然后這樣查詢：select id from t where num = 0醉蚁。

mysql優(yōu)化（第十四天）

mysql優(yōu)化（第十四天）

MySQL如何優(yōu)化

數(shù)據(jù)庫設(shè)計(jì)

什么是數(shù)據(jù)庫范式

數(shù)據(jù)庫三大范式

分表分庫

垂直拆分

水平拆分

水平拆分示例

索引

什么是索引

查詢索引

索引的分類

主鍵索引

全文索引

唯一索引

普通索引

索引的代價(jià)

查詢索引使用率

總結(jié)

SQL優(yōu)化

查看Mysql服務(wù)器狀態(tài)

慢查詢

什么是慢查詢

修改慢查詢

將慢查詢定位到日志中

MySQL數(shù)據(jù)引擎

總結(jié)