MySQL中的索引

系列文章目录

MySQL常见的几种约束

MySQL中的函数

MySQL中的事务

MySQL中的视图

---

---

前言

对于任何一个数据库管理系统，索引都是进行优化的最主要因素。对于少量的数据，即便没有合适的索引，对数据库性能的影响并不是很大，但是随着数据量的增加，数据库性能会急剧下降。所以索引目的在于提高检索数据的效率。

---

一、索引是什么？

一部字典6000多个汉字，如何查找？
可以采用检字法（拼音，部首），加快查找速度；
一本书 400页，如何查找？
可以使用章节目录或附录，加快了查找速度；
一张数据库表13亿条记录，如何查找？
使用索引，查询内容先查询索引，根据索引可以定位到要查询的内容，可以提高查询速度。

二、索引的作用和常见问题

1、索引的作用

提高了查询的速度

2、常见问题

• 索引占用空间吗？
  会占用空间，但是空间小；能够带来速度的明显提升。

• 索引是不是越多越好？
  不是
  索引也占用空间，多个索引就会占用更多的空间；所以要给经常需要使用到的内容建立索引，否则占用了空间，但是很少使用；索引会提高查询的速度，但是会降低添加、更新、删除的速度（不仅操作数据库表，也要操作索引）。

• 一般给哪些列建立索引？

Emp (empno,ename,job ,mgr,hiredate,sal,comm,deptno，email)

数据库会自动给主键empno和唯一键email、外键deptno建立索引；
要给经常出现在where子句中的或者order by子句中的列建立索引sal deptno ename

• 索引类型
  1.单列索引和多列索引
  sal | sal hiredate
  2.唯一索引和非唯一索引
  empno | sal deptno
  3.存储结构：
  B-Tree、R-Tree和Hash。其中B-Tree是最流行的存储结构，一般也是默认的存储结构

三、索引的使用示例

-- 显示了所有的数据  没有索引也行  
select * from emp;    

-- 会自动给主键列建立索引，此时查询条件是主键列，自动使用了索引  
select * from emp where empno = 7839;   
  
-- 查看表的索引  
show index from emp;  

-- 没有给ename列建立索引，所以此时是逐个比较，依次查询，效率低下  
select * from emp where ename = 'BLAKE'  
  
-- 针对ename列创建索引  
create index index_emp_name on emp(ename);  

-- 自动使用索引，SQL语句无变化，效率提高  
select * from emp where ename = 'BLAKE'   

-- 修改索引  先删除再创建    
-- 删除索引  
alter table emp drop index index_emp_name  
drop index index_emp_name on emp  

-- 删除了给ename列建立的索引，所以此时是逐个比较，依次查询，效率低下  
select * from emp order by ename;   

-- 没有对应的索引，此时的排序，效率低下  
select * from emp order by sal,hiredate   

-- 创建索引  
create index index_emp_sal_desc_hiredate on emp(sal desc,hiredate)  

-- 使用索引即可（索引肯定是有序），效率提高  
select * from emp order by sal,hiredate   

---

总结

索引的优点：

• 提高查询效率：通过使用索引，MySQL可以在常数时间内定位到需要的数据行，而不需要扫描整个表。这大大提高了查询效率，特别是在大型数据库中。
• 加速排序和分组操作：由于索引可以快速定位到需要的数据行，因此它们也可以加速排序和分组操作。这对于需要对大量数据进行排序和分组的应用非常重要。
• 支持全文搜索：MySQL的全文搜索引擎(如MyISAM和InnoDB)使用索引来支持全文搜索功能。这使得用户可以使用自然语言来搜索数据，而不必担心性能问题。

索引的缺点：

• 占用存储空间：由于索引需要存储额外的元数据信息，因此它们会占用额外的存储空间。在大型数据库中，这可能会成为一个问题。
• 降低写入性能：由于索引需要维护额外的元数据信息，因此它们可能会降低写入性能。特别是在高负载情况下，这可能会成为一个瓶颈。
• 无法保证唯一性：虽然索引可以提高查询效率，但它们并不能保证数据的唯一性。如果插入了重复的数据行，那么这些数据行将不会被索引到。