首頁  >  文章  >  資料庫  >  MySQL索引優化之適合建構索引的情況有哪些

MySQL索引優化之適合建構索引的情況有哪些

WBOY
WBOY轉載
2023-06-02 21:08:411341瀏覽

結論

  1. 在where後面的過濾欄位上建立索引(select/update/delete後面的where都是適用的),使用索引加快過濾效率,不用進行全表掃描

  2. 在具有唯一要求的欄位上新增唯一索引,加快查詢效率,查到即可直接回傳

  3. group by或order by後面的欄位添加索引,由於索引是排好序的,所以建立索引就等同於在查詢之前已經是排好序了(這裡需要注意建立的聯合索引建立中字段的順序,可以結合具體案例場景7進行學習)

  4. 在DISTINCT(去重字段)後面的字段添加索引,由於建立了索引,那麼相同的數據就是挨在一起的,所以就可以進行快速的去重操作,否則可能需要將相同的資料找出來在進行去重操作

  5. #在多表連接join的時候在連接的欄位上建立索引(小表驅動大表)

  6. 取字串一定前綴建立索引(不是用整個字串作為索引,否則將會佔用太大的空間)

  7. 在頻繁使用的列上建立索引(可以建立聯合索引,同時最頻繁使用的欄位應該在聯合索引的最左側,最左側原則)

  8. 在區分度高的列上建立索引(主鍵的區分度最高,因為所有的鍵都是唯一的)

#建立索引的場景

場景一:在where欄位後面的欄位建立索引

-- 描述:当where中有多个条件需要进行匹配的时候,那么可以创建联合索引,这样所有的条件都可以使用索引,大大提高了检索的效率
select * from student_info where student_id = 1;
-- 当然数据量比较大的时候给where后面的字段添加索引
create index student_id_index on student_info (student_id)

未新增索引前,耗費0.383秒,基本上遍歷整個表

MySQL索引優化之適合建構索引的情況有哪些

新增索引後,耗費0.001秒,使用了索引(但是建立索引的時候會耗費一定時間)

MySQL索引優化之適合建構索引的情況有哪些

在頻繁的查詢的業務中可以對where篩選的欄位建立索引,如果where篩選的欄位有多個也可以建立聯合索引

場景二:在具有唯一性限制的欄位上建立唯一索引(查找到目標即可返回不用繼續尋找)

select * from student_info where id = 1001;
-- 因为学号是唯一的,所以可以在学号这个字段上添加唯一所用
create index id_unique on student_info(id);

具有唯一性限制的欄位上就可以建立唯一索引,雖然建立了唯一索引對insert操作有一定的影響(需要判斷新增的資料是否已經在表中),但是建立唯一索引對於查詢的效率是顯著提升的,例如上面的例子,因為建立了唯一索引,一旦查找到id為1001的學生資料之後就不需要判斷資料庫中是否還有id等於1001的學生(只有唯一一份),直接回傳資訊即可,如果沒有建立索引,那麼就需要全表掃描

場景三:經常group by和order by的欄位上建立索引(因為索引本身就是排好序的,相當於查詢之前就已經進行了排序)

select * from student_info order by name;
-- 这里就可以给name字段进行索引的添加
select * from student_info group by class_id;
-- 这里就可以给class_id字段添加索引

建立索引前,耗時0.501秒,使用的是所有資料在記憶體中排序

MySQL索引優化之適合建構索引的情況有哪些

建立索引後,耗時0.01秒

MySQL索引優化之適合建構索引的情況有哪些

#場景四:在DISTINCT後面的欄位中新增索引(索引已經將相同的欄位排好序,去重效率更高)

select distinct(student_id) from student_info;
-- 这里就可以根据student_id字段建立索引
create index student_id_index on student_info;

建立了索引,那麼預設就是按照索引字段的升序排列的,那麼相同值的字段也就排列在一起了,那麼去重也就變得簡單、高效

場景五:在join多表連接大表中的連接字段建立索引

SELECT s.course_id,NAME,s.student_id,c.course_name
FROM student_info s JOIN course c
ON s.`course_id` = c.`course_id`
WHERE NAME = 'xiaoyuanhao';
-- 根据大表驱动小表的原则需要在student_info表的course_id字段上建立索引

沒有建立索引之前,耗時0.697s,沒有使索引

MySQL索引優化之適合建構索引的情況有哪些

建立索引後,使用了索引,耗時0.003s

MySQL索引優化之適合建構索引的情況有哪些

小表驅動大表:

透過對小表進行逐一歷,同時在大表中的連接欄位建立索引即可加快查詢,本案例中,每次取出課程表中course_id和學生表中學生的course_id進行連接操作,在學生表中對course_id建立索引即可

場景六:使用字串的前綴建立索引

create table shop(address varchar(120) not null);
alter table shop add index(address(12));
--这里只是对表中的address的前12个字符建立了索引,而不是整个字符串建立索引

前綴建立索引的原因:

  • 由於有些字串很長,如果為整個字串建立索引,那麼索引將佔用很大的空間

  • #由於需要儲存整個字串,那麼資料項目就會很大,那麼索引樹的深度就會加深,檢索速度下降

  • 雖然可能出現在索引中兩個字串相同,但是再根據主鍵進行回表操作效率依然比較高

如何确定前缀索引中前缀的长度呢?(也就是如果前缀的长度太短,那么索引的区分度就很低,从多个字符串截取的前缀数据可能都是一样的,但是如果前缀索引的前缀过长,那么前缀索引的优点就消失了)

引入了区别度的概念,select count(distinct left(索引字段,前缀索引长度) / count(*) from xxx),该值越接近1,那么区分度就越明显,那么该索引长度就是所求的前缀索引长度

场景七:在频繁使用的列上建立索引或联合索引(频繁使用的字段应该在索引的左侧)

select * from xiaoyuanhao where age = 18;
select * from xiaoyuanhao where age = 19 and sex = 'man';
select * from xiaoyuanhao where age = 10 and sex = 'man' and password = '123456';
-- 在这里实际上就可以建立age,sex,password的联合索引,只需要建立一个索引,这三个查询都是可以使用的
create index age_sex_password_index on xiaoyuanhao(age,sex,password);
select * from student_info group by class_id order by name;
-- 在这里可以建立class_id和name的联合索引,但是一定要注意索引的顺序,一定是要class_id在前,name在后,因为在select语句中执行的顺序是先group by 之后才是 order by 索引如果索引的字段顺序是相反的,那么就无法使用索引
create index class_id_name_index on student(class_id,name);

索引建立需要符合顺序的原因:

索引字段的顺序如果是错误的,那么索引就会失效,因为索引实际上是排好序的,如果索引建立的时候是现根据name排好序之后在根据class_id进行排序,那么在面对需要先根据class_id排序再根据name排序的业务就无法进行使用

补充:

在select * from xxx where age = 19 and sex = ‘man’ and password = '123456’这里索引建立的顺序不一定是(age,sex,password)因为在实际执行的过程中,优化器会优化执行步骤会按照索引的顺序进行查询,但是group by 和 order by的执行顺序是无法改变的,索引必须严格的按照顺序建立索引,否则索引失效

以上是MySQL索引優化之適合建構索引的情況有哪些的詳細內容。更多資訊請關注PHP中文網其他相關文章!

陳述:
本文轉載於:yisu.com。如有侵權,請聯絡admin@php.cn刪除