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