什么是索引？

索引用來快速地尋找那些具有特定值的記錄。 索引是加速查詢主要手段，索引是快速定位數據的技術。 索引是一種特殊的文件（innoDB(事務性數據庫的首選引擎)數據表上的索引是表空間的一個組成部分），它們包含著對數據表里所有記錄的引用指針。 索引：一種特殊的目錄，聚集索引和非聚集索引 聚集索引：如同字典中按照字母查詢，我們把這種正文內容本身就是一種按照一定規則排列的目錄稱為聚集引。 非聚集索引：如同字典中按照偏旁來查詢某個字，我們把這種目錄純粹是目錄，正文純粹是正文的排序。 主鍵就是聚集索引，sql server默認是在主鍵上建立聚集索引的。索引有助于提高檢索性能，但過多或不當的索引也會導致系統低效，過多的索引甚至會導致索引碎片。95%的數據庫性能問題都可以采用索引技術得到解決。 通常情況下，我們會在每個表中建立一個id列，以區分每條數據，并且這個id列是自動增長的，步長一般都是 1.此時，我們將這個列設為主鍵，sql server會將此列默認為聚集索引。這樣做有好處，就是可以讓您的 數據在數據庫中按照id進行物理排序，但是意義不是很大。 顯而易見，聚集索引的優勢是很明顯的，而每個表中只能有一個聚集索引的規則，這使得聚集索引變得更加貴。 聚集索引的優點：迅速縮小查詢范圍，避免全表掃描。（日期，用戶名） 如果你的數據庫真的有1000萬容量的話，把主鍵建立在id列上，在網頁上的表現就是超時，根本就無法顯示 這也是我們摒棄id列作為聚集索引一個重要的因素。 什么時候用？ 應該只為最經常查詢和最經常排序的數據列建立索引。 只有表或視圖的所有者才能為表創建索引。表或視圖的所有者可以隨時創建索引，無論表中是否有數據。 可以通過指定限定的數據庫名稱，為另一個數據庫中的表或視圖創建索引。 并非是在任何字段上簡單地建立索引就能提高查詢速度 并非把所有需要提高查詢速度的字段都加進聚集索引，以提高查詢速度。 如果數據很小的話，用聚集索引作為排序要比使用非聚集索引速度快。 日期列不會因為有分秒的輸入而減慢查詢速度。 不要把SSN或者身份證號碼，永遠不要使用收工輸入的鍵作為主鍵，因為一旦你輸入錯誤，你唯一能做的就是 刪除整個記錄然后從頭開始。 通常情況下不要選擇用戶可編輯的字段作為鍵， 不要索引 memo/notes 字段和不要索引大型文本字段(許多字符) 索引列不會包含有null值 只有列中包含有null值都講不會被包含有索引中，復合索引中只有一個列含有null值，那么這一列對于此 復合索引就無效了，所以我們在數據設計時不要讓字段默認值為null。 索引大大提高了查詢速度，同時卻會降低更新表的速度，如對表進行insert，update，delete，因為更新表 時，mysql不僅要保存數據，還要保存一下索引文件。 一般來說，在where和join中出現的列需要建立索引，但也不完全如此， 但MySQL把同一個數據表里的索引總數限制為16個。 許多sql命令都有一個delay_key_write項，這個選項的作用是暫時限止mysql在該命令每插入一條記錄和每 修改一條現有記錄之后立刻對索引進行刷新，對索引的刷新將等到全部記錄插入、修改完畢之后再進行， 在需要把許多新記錄插入某個數據表的場合，delay_key_write選項的作用將非常明顯。 建立索引，只針對查詢和一些更新和刪除的速度，像性別一列，如果表里面有10000行，如果只有一行是男， 這樣用索引的話肯定高，如果有9999是男，那么它不如直接掃描，這是選擇性。 兩種情況下不建議建索引： 第一種是表記錄比較少，例如一兩千甚至幾百記錄的表，沒必要建索引，讓查詢做全表掃描就好了。 記錄不超過2000可以考慮不建索引，超過2000條可以考慮索引。 第二種索引選擇性較低。選擇性是指不重復的索引值與表記錄數的比例。 如： SELECT count(DISTINCT(concat(first_name, left(last_name, 3))))/count(*) AS Selectivity FROM employees.employees; 怎么建立？ 在執行create table語句時可以創建索引，也可以單獨用create index或alter table來為表添加索引。 1，alter table alter table用來創建普通索引，unique索引或primary key索引。 alter table table_name add index indexName(column_list) alter table table_name add unique(column_list) alter table table_name add primary key(column_list) 其中table_name是要添加索引的表名，column_list指對哪些列進行索引，多列時各列之間用逗號分開， 2，create index create index可對表增加普通索引或unique 索引。 create index indexName on table_name(column_list) create unique index indeName on table_name(column_list) 不能用create index語句創建primary key索引。 一個表只能包含一個primary key。因為一個表中不可能具有兩個同名的索引。 create index indexName on mytable(username(length)); 如果是char，varchar類型，length可以小于 字段實際長度；如果是blob和text類型，必須指定length。 組合索引 alter table mytable add index name_city_age(name(10),city,age) 這樣的索引相對于下面三組索引： username,city,age; ?username,city; ??username 為什么沒有city，age這樣的組合索引呢？這是因為mysql組合索引最左前綴的結果。 唯一索引 create unique index indexName on mytable(username(length)) ?修改表結構 alter mytable add unique indexName on (userName(length)) 創建表的時候直接指定 create table mytable(id int not null,username varchar(16) not null,unique [indexName](username(length))); 在設計數據表的時候采用系統生成的鍵作為主鍵，那么你實際控制了數據庫的索引完整性，這樣就更有效的控制 了對存儲數據中的每一行的訪問。當你擁有一致的鍵結構時，很容易找到邏輯缺陷。 刪除索引的語法 drop index indexName on mytable; alter table table_name drop index index_name alter table table_name drop primary key ?因為一個表只可能有有一個primary key索引，因此不需要 指定索引名。如果沒有創建primary key索引，但表具有一個或多個unique索引，則mysql將刪除第一個unique索引。 查看索引 show index from tabname show keys from tablename; 使用索引注意： 1，order by中的列是不會使用索引的。 2，通配符%在字符串的開端使得索引無法使用如 name like '%zhang' 而name like 'zhang%'就可以。 3，or會引起全表掃描。 4，in的作用相對于or 如： select * from table where id in(2,3) 等于 select * from table where id = 2 or id = 3;如果id上有索引，則索引會失效。 5，盡量少用not。 6，exists 和 in 的執行效率是一樣的。 7，union并不絕對比or的執行效率高。 如： select id,name from table where time='2012-01-01' or age = 22 等于 select id,name from table where time ='2012-01-01' union select id,name from table where age = 22 但是如果or兩邊的查詢條件是一樣的話，那么用union則反倒速度差很多。雖然這里union掃描的是索引，而 or掃描的是全表。 如：select * from table where id = 2 or id = 3; 8，字段提取要按照“需多少、提多少”的原則，避免“select *” 9，count(*)不比count(字段)慢 10，order by按聚集索引列排序效率最高,如果按照某個字段進行排序的時候，無論是倒序還是順序，速度是相當的。 11，charindex 查詢a字段中包含bi的所有行 select * from table where a like '%bi%' 等于 select * from table where charindex('bi',a)>0?這種方法比like的形式速度上要快很多。 12，請選擇也業務無關的自增字段作為主鍵。 這樣就會形成一個緊湊的索引結構，近似順序填滿，由于每次插入時也不需要移動已有數據，因此效率很高， 也不會增加很多開銷在維護索引上。 如果使用非自增主鍵（如果身份證或學號），由于每次插入主鍵的值近似于隨機，因此每次新記錄都要被 查到現有索引頁的中間某個位置。此時mysql不得不為了將新記錄插到合適位置而移動數據，甚至目標頁面 可能已經被寫到磁盤上而從緩存中清掉，此時也要從磁盤上讀回來，這增加了很多開銷，同時頻繁的移動， 分頁操作造成了大量的碎片，得到了不夠緊湊的索引結構，后續不得不通過optimize table來重建表 并優化填充頁面。 數據庫三范式： 第一范式（1NF）數據庫表的每一列都是不可分割的基本數據項，無重復的列。 第二范式（2NF）數據庫表中必須有有一列為唯一標識（主鍵）。 第三范式（3NF）屬性不依賴于其他非主屬性，如：存在一個部門信息表，其中每個部門有部門編號（dept_id）、部門名稱、部門簡介等信息。那么在圖3-2的員工信息表中列出部門編號后就不能再將部門名稱、部門簡介等與部門有關的信息再加入員工信息表中。 oracle創建序列 create sequence seq_atable minvalue 1 maxvalue 9999999 start with 1 increment by 1 nocache? 創建觸發器 create or replace trigger trg_atable before insert on atable for each row begin select seq_atable.nextval into :new.id from dual; end;? 插入數據：insert into atable(a) values('test'); 觸發器方式 這種也可以。 insert into atable(id,a) values(seq_atable.nextval,'test');? mysql的優化主要分為結構優化和查詢優化 1，理論上面索引對順序是敏感的，但是由于mysql查詢優化器會自動調試where子句中條件的順序。 select * from table1 where name='zhangsan' and tID > 10000 和執行: select * from table1 where tID > 10000 and name='zhangsan' 第一條語句是從去全表中查找有幾個name為zhangsan的，而后一句僅僅從表的10000條語句中查找。 2，索引文件本身要消耗存儲空間的，同時索引會加重插入，刪除，修改記錄的負擔，另外mysql運行時也要消耗資源維護索引，因此索引并不是越多越好。 3，最適合索引的列是出現在where子句中的列，或者鏈接子句漲指定的列，而不是出現在select關鍵字后的選擇項列表中的列。 4，使用唯一索引，考慮某列中值的分布，對于唯一值的列，索引的效果最好，而具有多個重復值的列，其索引效果最差。 5，使用短索引，如：有一個char(200)列，如果在前10個或者20個字符內，多數值是唯一的，那么就不要對整個列進行索引。這樣能夠節省大量索引空間，也可能會使查詢更快，較小的索引涉及的磁盤I/O較少，較短的值比較起來更快。 6，利用最左前綴 若表總的某字段出現在select，過濾，排序條件中，為該字段建立索引是值得的。 前綴索引兼顧索引大小和查詢速度，但是其缺點是不能用于order by 和group by操作。也不能用于當索引本身包含查詢所需全部數據時，不再訪問數據文件本身。

轉載于:https://www.cnblogs.com/wayne-ivan/p/3851428.html

總結

以上是生活随笔為你收集整理的数据库索引以及优化的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。