目錄
六、使用執行計劃，完成sql語(yǔ)句索引執行情況查詢(xún)；
2、對于使用like的查詢(xún)，查詢(xún)如果是”%aaa”,不會(huì )使用到索引，'aaa%’會(huì )使用到索引。
3、如果條件中有or，則要求or的索引字段都必須有索引，否則不能用到索引。
4、如果列類(lèi)型是字符串，一定要在條件中將數據使用引號引用起來(lái)，否則不使用索引。
6、當取出的數據量超過(guò)表中數據的20%，優(yōu)化器就不會(huì )使用索引，而是全表掃描。
頁(yè)面靜態(tài)化的內容，memcache，減少數據庫的訪(fǎng)問(wèn)，提高網(wǎng)站的訪(fǎng)問(wèn)速度，無(wú)論如何優(yōu)化，還是要操作數據庫的，要從數據庫的角度來(lái)優(yōu)化，提高訪(fǎng)問(wèn)速度。
設計角度：存儲引擎的選擇，字段類(lèi)型選擇，范式
利用mysql自身的特性：索引，查詢(xún)緩存，分區分表，存儲過(guò)程，sql語(yǔ)句優(yōu)化配置，
部署大負載架構體系：主從復制，讀寫(xiě)分離。
硬件升級：
要 查找執行速度比較慢的sql語(yǔ)句，
慢查詢(xún)日志，是由mysql提供的，用于記錄sql執行時(shí)間超過(guò)了某個(gè)時(shí)間界限，該時(shí)間界限我們可以自己設定，比如我們設定的時(shí)間界限為0.5秒，開(kāi)啟慢查詢(xún)日志后，會(huì )自動(dòng)記錄執行時(shí)間超過(guò)0.5秒的sql語(yǔ)句。慢查詢(xún)日志默認沒(méi)有開(kāi)啟，默認的時(shí)間界限是10秒。
（1）如何開(kāi)啟慢查詢(xún)日志，
方式一：打開(kāi)mysql的配置文件,my.ini,添加如下語(yǔ)句：修改完成后，要重啟mysql服務(wù)。
|
|
我們設置的時(shí)間界限。如果不設置則默認是10秒 |
|
|
設置慢查詢(xún)日志的保存位置，該保存位置，可以自己隨意設置，此處的slow-log是慢查詢(xún)日志的名稱(chēng)，該名稱(chēng)也是可以自己隨意的設置。 如果log-slow-queries后面沒(méi)有設置值，則會(huì )采用默認的名字保存在默認 的位置。該默認位置和表文件的存儲位置一致。 |
|
|
設置數據庫和表文件的目錄。 |
（2）開(kāi)始測試，是否記錄執行時(shí)間超過(guò)0.5秒 的sql語(yǔ)句。
select benchmark(執行次數,表達式);
在慢查詢(xún)日志文件里面查看是否記錄：
（3）相關(guān)的一些命令，
查看慢查詢(xún)日志的時(shí)間界限
可以在當前會(huì )話(huà)中進(jìn)行修改慢查詢(xún)的時(shí)間界限：
語(yǔ)法：set long_query_time=時(shí)間界限
該機制開(kāi)啟后，會(huì )記錄每個(gè)sql語(yǔ)句的執行時(shí)間，精確到小數點(diǎn)后8位。
如果開(kāi)啟：set profiling=1|0 值為1則是開(kāi)啟，為0則是關(guān)閉。
查看sql語(yǔ)句的執行時(shí)間:show profiles
一般sql語(yǔ)句執行比較慢，原因是沒(méi)有添加索引，
沒(méi)有添加索引之前：
添加索引之后使用的時(shí)間：
索引：利用字段的某些屬性，快速 的定位數據（磁盤(pán)，柱面，磁道，扇區）
唯一索引（unique key）:字段數據是唯一的，數據內容里面能否為null,在一張表里面，是可以添加多個(gè)唯一索引。
主鍵索引(primary key )：數據記錄里面不能有null,數據內容不能重復，在一張表里面不能有多個(gè)主鍵索引。
普通索引(index )：使用字段關(guān)鍵字建立的索引，主要是提高查詢(xún)速度。
全文索引(fulltext index)：在比較老的版本中，只有myisam引擎支持全文索引，在最新的版本中（可能是mysql.5.6）innodb引擎也支持全文索引，在mysql中全文索引不支持中文。
（1）在創(chuàng )建表的時(shí)候，同時(shí)創(chuàng )建索引，
create table stu(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text,
unique key (name),
index (email),
fulltext index (intro)
)engine myisam charset utf8;
（2）在修改表的時(shí)候，添加索引
create table stu1(
id int primary key auto_increment,
name varchar(32) not null,
age tinyint unsigned not null,
email varchar(32) not null,
intro text
)engine myisam charset utf8;
alter table stu1 add unique key (name), add index (email), add fulltext index (intro);
主鍵索引的刪除，在刪除主鍵 索引時(shí)，要刪除到auto_increment屬性，
alter table 表名 drop primary key
普通索引的刪除：alter table 表名 drop index 索引名稱(chēng) （如果索引名稱(chēng)沒(méi)有指定則是索引的字段名稱(chēng)）
刪除唯一索引：alter table 表名 drop index索引名稱(chēng)
show index form 表名
show indexes from 表名
desc 表名，
show create table 表名
（1）較頻繁的作為查詢(xún)條件字段應該創(chuàng )建索引
select * from emp where empno = 1
唯一性太差的字段不適合單獨創(chuàng )建索引，即使頻繁作為查詢(xún)條件
select * from emp where sex = '男'
更新非常頻繁的字段不適合創(chuàng )建索引
select * from emp where logincount = 1
（2）不會(huì )出現在WHERE子句中字段不該創(chuàng )建索
查看索引的類(lèi)型，是BTREE結構。
myisam引擎的索引的結構
該引擎的表是有三個(gè)文件組成的。一個(gè)是frm(存儲結構)myd(數據) myi（索引）
innodb索引的索引結構，innodb的索引叫聚簇索引。
innodb的主索引文件上直接存放該行數據,稱(chēng)為聚簇索引,非主索引指向對主鍵的引用
注意: innodb來(lái)說(shuō),
1: 主鍵索引既存儲索引值,又在葉子中存儲行的數據
2: 如果沒(méi)有主鍵, 則會(huì )Unique key做主鍵
3: 如果沒(méi)有unique,則系統生成一個(gè)內部的rowid做主鍵.
4: 像innodb中,主鍵的索引結構中,既存儲了主鍵值,又存儲了行數據,這種結構稱(chēng)為”聚簇索引”
在插入大量的數據的時(shí)候，造成頻繁的頁(yè)分裂.
主要用于分析sql語(yǔ)句的執行情況（并不執行sql語(yǔ)句）得到sql語(yǔ)句是否使用了索引，使用了哪些索引。
語(yǔ)法：explain sql語(yǔ)句\G 或 desc sql語(yǔ)句\G
在mysql之前的版本中，explain只支持select語(yǔ)句，但是在最新的5.6版本中，它支持 explain update/delete了。
建表完成測試：
create table user(
id int primary key auto_increment,
name varchar(32) not null default '',
age tinyint unsigned not null default 0,
email varchar(32) not null default '',
classid int not null default 1
)engine myisam charset utf8;
insert into user values(null,'xiaogang',12,'gang@sohu.com',4),
(null,'xiaohong',13,'hong@sohu.com',2),
(null,'xiaolong',31,'long@sohu.com',2),
(null,'xiaofeng',22,'feng@sohu.com',3),
(null,'xiaogui',42,'gui@sohu.com',3);
創(chuàng )建一個(gè)班級表：
create table class(
id int not null default 0,
classname varchar(32) not null default ''
)engine myisam charset utf8;
insert into class values(1,'java'),(2,'.net'),(3,'php'),(4,'c++'),(5,'ios');
|
|
掃描的行數 |
|
|
實(shí)際用到的索引 |
|
|
可能用到的索引,當前用到了主鍵索引 |
|
|
檢索的類(lèi)型：常量 |
|
|
查詢(xún)的表 |
|
|
查詢(xún)方式，簡(jiǎn)單查詢(xún) |
復合索引，有多列組合成一個(gè)索引。比如如下，建立一個(gè)name和age的一個(gè)復合索引。
mysql> alter table user add index (name,age);
Query OK, 5 rows affected (0.05 sec)
Records: 5 Duplicates: 0 Warnings: 0
（1）對于創(chuàng )建的多列(復合)索引，只要查詢(xún)條件使用了最左邊的列，索引一般就會(huì )被使用。
因為組合索引是需要按順序執行的，比如c1234組合索引，要想在c2上使用索引，必須先在c1上使用索引，要想在c3上使用索引，必須先在c2上使用索引，依此。
比如：根據電影的劇情查找電影的名稱(chēng)，根據歌詞查找歌名。like '%愛(ài)請%’
可以使用第三方查詢(xún)工具，sphinx
|
|
沒(méi)有對age添加索引，因此該語(yǔ)句沒(méi)有用到索引 |
默認情況下， mysql對所有的group by col1,col2進(jìn)行排序。這與在查詢(xún)中指定order by col1,col2類(lèi)型，如果查詢(xún)中包括group by 但用戶(hù)想要避免排序結果的消耗，則可以使用order by null禁止排序。
索引覆蓋是指：如果查詢(xún)的列恰好是索引的一部分，那么查詢(xún)只需要在索引文件上進(jìn)行，不需要回行到磁盤(pán)再找數據，這種查詢(xún)速度非?？?，稱(chēng)為“索引覆蓋”
案例1，如下對name字段添加了普通索引，要查詢(xún)name字段信息。
案例2：比如對id和name字段建立符合索引，我們取出的數據是復合索引的一部分，因此用到了索引覆蓋。
利用字段數據的前部分作為索引，稱(chēng)為前綴索引。減少索引長(cháng)度，提高索引效率。
比如：統計密碼的前7個(gè)字符，作為不相同匹配條件，幾乎可以做到1:1
此時(shí)，就可以利用前7個(gè)字符做索引關(guān)鍵字即可（離散程度高）
語(yǔ)法：
alter table 表名 add index (passwd(7)) 指定前7位作為索引關(guān)鍵字。
不使用索引前綴，索引的長(cháng)度。
使用索引前綴后，索引的長(cháng)度。
翻頁(yè)的sql語(yǔ)句：
select * from table_name limit offset N
使用如上語(yǔ)句，在翻頁(yè)時(shí)，翻到最后，越來(lái)越慢，
原因：并不是跨過(guò)offset行，取出n條，
是取出offset+N條數據，舍棄前面的offset行，只取出n條數據。
如何解決？
（1）從業(yè)務(wù)上去解決：
辦法：不允許翻過(guò)100頁(yè)，
以百度為例，一般翻頁(yè)到70頁(yè)左右，谷歌40頁(yè)左右
（2）不用offset，用條件查詢(xún)，條件中使用id查詢(xún)，使用到了索引，
select * from user limit 10000,10;
select * from user where id>10000 limit 10;
下一頁(yè)：select * from user where id>10000+10 limit 10
該種方式要注意：如果有數據被刪除，會(huì )導致select * from user limit 10000,10;
和select * from user where id>10000 limit 10;語(yǔ)句取出的結果不一樣，。
（3）假如不能使用限制翻頁(yè)到100頁(yè)，數據有刪除，還要求翻頁(yè)，速度不能受影響。
思路：通過(guò)翻頁(yè)，先取出id（主鍵），在根據id取出數據。
select name,age,email from user inner join (select id from user limit 10000,10) as tmp on tmp.id=user.id
非要物理刪除，還要用offset精確查詢(xún)，還不限制用戶(hù)分頁(yè)，怎么辦
我們現在必須要查，則只查索引，不查數據，得到id
再用id去查具體條目，這種技巧就是延遲索引。
比如建表測試：
數據表文件原來(lái)的容量：
當delete from ceshi where id=1，應該容量減去三分之一，但是并沒(méi)有被刪除。
需要把里面的一些碎片給釋放掉。
使用optimize table 表名；或alter table 表名 engine myisam(innodb)
執行optimize table 表名，命令后，把原來(lái)的碎片空間給釋放掉
注意：修復表的數據及索引碎片，就會(huì )把所有的數據文件重新整理一遍，使之對齊，這個(gè)過(guò)程，如果表的行數比較大，也是比較耗費資源的操作，所以，不能頻繁的修復。
如果表的update操作很頻繁，可以按周月來(lái)修復
場(chǎng)景:
下訂單：
庫存 為100，買(mǎi)一件：
（1）取出庫存的數量 100
（2）庫存減去1 99
（3）把剩余的庫存再寫(xiě)入到表里面。 99
如果是兩個(gè)人同時(shí)操作：
劉備：
100-1=99
99
曹操：
100-1=99
99
鎖機制，
mysql 的鎖有以下幾種形式：
表級鎖：開(kāi)銷(xiāo)小，加鎖快，發(fā)生鎖沖突的概率最高，并發(fā)度最低。myisam引擎屬于這種類(lèi)型。
行級鎖：開(kāi)銷(xiāo)大，加鎖慢，發(fā)生鎖沖突的概率最低，并發(fā)度也最高。innodb屬于這種類(lèi)型。
對myisam表的讀操作（加讀鎖），不會(huì )阻塞其他進(jìn)程對同一表的讀請求，但會(huì )阻塞對同一表的寫(xiě)請求。只有當讀鎖釋放后，才會(huì )執行其他進(jìn)程的操作。
對myisam表的寫(xiě)操作（加寫(xiě)鎖），會(huì )阻塞其他進(jìn)程對同一表的讀和寫(xiě)操作，只有當寫(xiě)鎖釋放后，才會(huì )執行其他進(jìn)程的讀寫(xiě)操作。
read:所有人都只可以讀，只有釋放鎖之后才可以寫(xiě)。
write:只有鎖表的客戶(hù)可以操作這個(gè)表，其他客戶(hù)讀都不能讀。
語(yǔ)法：
lock table 表名 read|write,
解鎖：
unlock table
讀鎖的演示：
|
|
添加讀鎖后，自己和別人只能查詢(xún)，不能執行修改操作。其他人要修改則會(huì )被阻塞，等待讀鎖被釋放。 |
要注意：對表添加鎖定后，只能操作鎖定的表，如果想要操作其他表，則可以在鎖定表時(shí)，一次性鎖定多張表。語(yǔ)法：lock table 表1 read,表2 read;
寫(xiě)鎖的演示
對表寫(xiě)鎖鎖定之后，自己可以進(jìn)行修改和查詢(xún)，另外的進(jìn)程則無(wú)法查詢(xún)，更不能修改。
是innodb支持的一種鎖，在使用時(shí)，要添加條件限制是要操作哪行數據。
語(yǔ)法：
begin;
執行語(yǔ)句；
commit;
|
|
操作鎖定的行，則被阻塞，等待被釋放。 |
|
|
操作沒(méi)有鎖定的行則正常操作。 |
|
|
鎖定id=4的行。 |
文件鎖，flock_file
基本概念，把一個(gè)表，從邏輯上分成多個(gè)區域，便于存儲數據。
采用分區的前提：數據量非常大。
分區的語(yǔ)法，在創(chuàng )建表時(shí)，完成分區
create table 表名(
字段信息
)表選項
partition by 分區的類(lèi)型（分區的條件）(
//分區信息。
)；
list :條件值為一個(gè)數據列表。
通過(guò)預定義的列表的值來(lái)對數據進(jìn)行分割
例子：假如你創(chuàng )建一個(gè)如下的一個(gè)表，該表保存有全國20家分公司的職員記錄，這20家分公司的編號從1到20.而這20家分公司分布在全國5個(gè)區域，如下表所示：
職員表：
id name store_id(分公司的id)
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
東部 3,12,19,20
西部 8,14,15,16
id name store_id(分公司的id)
1 李小龍 3
2 大刀王五 8
北部 1,4,5,6,17,18
南部 2,7,9,10,11,13
東部 3,12,19,20
西部 8,14,15,16
create table emp(
id int,
name varchar(32),
store_id int
)engine myisam charset utf8
partition by list (store_id)(
partition p_north values in (1,4,5,6,17,18),
partition p_east values in(2,7,9,10,11,13),
partition p_south values in(3,12,19,20),
partition p_west values in(8,14,15,16)
);
添加兩天條語(yǔ)句，測試是否使用了分區存儲。
explain partitions select *from emp where store_id=3
測試是否用到了分區：
explain partitions select * from p_list where store_id=20\G
注意：在使用分區時(shí)，where后面的字段必須是分區字段，才能使用到分區。
Range（范圍）
這種模式允許將數據劃分不同范圍。例如可以將一個(gè)表通過(guò)年份劃分成若干個(gè)分區
create table p_range(
id int,
name varchar(32),
birthday date
)partition by range (month(birthday))(
partition p_1 values less than (3),
partition p_2 values less than(6),
partition p_3 values less than(9),
partition p_4 values less than MAXVALUE
);
less than 小于等于；
MAXVALUE可能的最大值
聯(lián)系客服
