mysql的幾種索引

標籤：情況   不能   and   http   justify   文章   data   xpl   efault   

為什麼建立索引後，速度就會變快?

答：使用索引後，查詢是按二叉樹演算法來尋找到記錄

索引使用的注意事項：

 索引的代價:

1. 佔用磁碟空間

2. 對dml（新增、修改、刪除）操作有影響，變慢

在哪些列上適合添加索引：

1. 較頻繁的作為查詢條件欄位應該建立索引
select * from emp where empno = 1
2. 唯一性太差的欄位不適合單獨建立索引，即使頻繁作為查詢條件
select * from emp where sex = ‘男‘
3. 更新非常頻繁的欄位不適合建立索引
select * from emp where logincount = 1
4. 不會出現在WHERE子句中欄位不該建立

總結: 滿足以下條件的欄位，才應該建立索引.

a: 肯定在where條經常使用

b: 該欄位的內容不是唯一的幾個值(sex)

c: 欄位內容不是頻繁變化.

使用索引的注意事項

把dept表中，我增加幾個部門:

alter table dept add index my_ind (dname,loc); // dname 左邊的列,loc就是右邊的列

說明，如果我們的表中有複合索引(索引作用在多列上)， 此時我們注意:

1， 對於建立的多列索引，只要查詢條件使用了最左邊的列，索引一般就會被使用。

explain select * from dept where loc=‘aaa‘\G

就不會使用到索引

2，對於使用like的查詢，查詢如果是 ‘%aaa’ 不會使用到索引

       ‘aaa%’ 會使用到索引。

比如: explain select * from dept where dname like ‘%aaa‘\G

不能使用索引，即，在like查詢時，關鍵的 ‘關鍵字’ , 最前面，不能使用 % 或者 _這樣的字元.， 如果一定要前面有變化的值，則考慮使用 全文索引->sphinx.

1. 如果條件中有or，即使其中有條件帶索引也不會使用。換言之，就是要求使用的所有欄位，都必須建立索引, 我們建議大家盡量避免使用or 關鍵字

select * from dept where dname=’xxx’ or loc=’xx’ or deptno=45

2. 如果列類型是字串，那一定要在條件中將資料使用引號引用起來。否則不使用索引。(添加時,字串必須’’), 也就是，如果列是字串類型，就一定要用 ‘’ 把他包括起來.

3. 如果mysql估計使用全表掃描要比使用索引快，則不使用索引

explain 可以協助我們在不真正執行某個sql語句時，就執行mysql怎樣執行，這樣利用我們去分析sql指令.

如何查看索引使用的情況:

show status like ‘Handler_read%’;

大家可以注意：
handler_read_key:這個值越高越好，越高表示使用索引查詢到的次數。

 handler_read_rnd_next:這個值越高，說明查詢低效。

sql語句的小技巧

1. 在使用group by 分組查詢是，預設分組後，還會排序，可能會降低速度.

比如:

 

在group by 後面增加 order by null 就可以防止排序.

1. 有些情況下，可以使用串連來替代子查詢。因為使用join，MySQL不需要在記憶體中建立暫存資料表。

 select * from dept, emp where dept.deptno=emp.deptno; [簡單處理方式]

 select * from dept left join emp on dept.deptno=emp.deptno; [左外串連，更ok!]

======================================================================

建立索引有四種方式：分別是：主鍵索引、唯一索引、全文索引、普通索引

1、主鍵索引

當一張表，把某個列設為主鍵的時候，則該列就是主鍵索引

create table aaa

(id int unsigned primary key auto_increment ,

name varchar(32) not null defaul ‘’);

這是id 列就是主鍵索引.

如果你建立表時，沒有指定主鍵索引，也可以在建立表後，在添加, 指令:

alter table 表名 add primary key (列名);

舉例:

create table bbb (id int , name varchar(32) not null default ‘’);

alter table bbb add primary key (id);

2、普通索引

一般來說，普通索引的建立，是先建立表，然後在建立普通索引

比如:

create table ccc(

id int unsigned,

name varchar(32)

)

create index 索引名 on 表 (列1,列名2);

3、全文索引

全文索引，主要是針對對檔案，文本的檢索, 比如文章, 全文索引針對MyISAM有用.

建立 ：

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 ...‘);

如何使用全文索引:

錯誤用法:     select * from articles where body like ‘%mysql%’; 【不會使用到全文索引】

證明:   explain select * from articles where body like ‘%mysql%’

正確的用法是:     select * from articles where match(title,body) against(‘database’); 【可以】

知識點說明:

1. 在mysql中fulltext 索引只針對 myisam生效

2. mysql自己提供的fulltext針對英文生效->sphinx (coreseek) 技術處理中文

3. 使用方法是 match(欄位名..) against(‘關鍵字’)

4. 全文索引一個 叫 停止詞, 因為在一個文本中，建立索引是一個無窮大的數，因此，對一些常用詞和字元，就不會建立，這些詞，稱為停止詞.

4、唯一索引

①當表的某列被指定為unique約束時，這列就是一個唯一索引

create table ddd(id int primary key auto_increment , name varchar(32) unique);

這時, name 列就是一個唯一索引.

unique欄位可以為NULL,並可以有多NULL, 但是如果是具體內容，則不能重複.

主鍵欄位，不能為NULL,也不能重複.

②在建立表後，再去建立唯一索引

create table eee(id int primary key auto_increment, name varchar(32));

create unique index 索引名 on 表名 (列表..);

 

查詢索引的方式：

1、desc 表名 【該方法的缺點是：　不能夠顯示索引名.】

2、show index(es) from 表名

3、show keys from 表名

 

刪除索引的方式：

alter table 表名 drop index 索引名;

如果刪除主鍵索引：  alter table 表名 drop primary key

 

修改索引的方式：

先刪除，再重新建立.

 

mysql的幾種索引