索引
- 1>什么是索引?
- 2>MySql的查询方式
- 3>索引的实现原理
- 4>索引应用在什么情况下
- 5>索引的创建、删除
- 6>索引的常见失败情况
- 第一种情况
-
- 第二种情况
- 第三种情况
-
- 第四种情况
- 第五种情况
- 7>索引的分类
1>什么是索引?
索引是在数据库表的字段上添加的,是为了提高查询效率存在的一种机制。
一张表的一个字段可以添加一个索引,当然,多个字段联合起来也可以添加索引。
索引相当于一本书的目录,是为了缩小扫描范围而存在的一种机制。
对于一本字典来说,查找某个汉字有两种方式:
第一种方式:一页一页挨着找,直到找到为止,这种查找方式属于全字典扫描。
效率比较低。
第二种方式:先通过目录(索引)去定位一个大概的位置,然后直接定位到这个
位置,做局域性扫描,缩小扫描的范围,快速的查找。这种查找方式属于通过
索引检索,效率较高。
2>MySql的查询方式
3>索引的实现原理
假设有一张用户表t_user
id(PK) | name | 每一行记录在硬盘上的物理存储编号 |
---|
100 | zhangsan | 0x1111 |
120 | lisi | 0x222 |
99 | wangwu | 0x888 |
88 | zhaoliu | 0x999 |
101 | jack | 0x666 |
55 | lucky | 0x5555 |
130 | tom | 0x7777 |
![在这里插入图片描述](https://img-blog.csdnimg.cn/2021010319383812.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L29MZW5nTnVhblppWmhpMTI=,size_16,color_FFFFFF,t_70)
- 在任何数据库中主键都会自动添加索引对象,在mysql中,具有唯一性的字段也会自动添加索引。
- 在任何数据库中,任何一张表的任何一条记录在硬盘存储上都有一个硬盘的物理存储编号。
- 在mysql当中,索引是一个单独的对象,不同的存储引擎以不同的形式存在,在MyISAM存储引擎中,索引存储在一个.MYI文件中。在InnoDB存储引擎中索引存储在一个逻辑名称叫做tablespace的当中。在MEMORY存储引擎当中索引被存储在内存当中。不管索引存储在哪里,索引在mysql当中都是一个树的形式存在。(自平衡二叉树:B-Tree)
4>索引应用在什么情况下
-
条件1:数据量庞大(到底有多么庞大算庞大,这个需要测试,因为每一个硬件环境不同)
-
条件2:该字段经常出现在where的后面,以条件的形式存在,也就是说这个字段总是被扫描。
-
条件3:该字段很少的DML(insert delete update)操作。(因为DML之后,索引需要重新排序。)
建议不要随意添加索引,因为索引也是需要维护的,太多的话反而会降低系统的性能。
建议通过主键查询,建议通过unique约束的字段进行查询,效率是比较高的。
5>索引的创建、删除
创建索引:
mysql> create index emp_ename_index on emp(ename);
删除索引:
mysql> drop index emp_ename_index on emp;
、、将emp表上的emp_ename_index索引对象删除。
怎么查看一个SQL语句是否使用了索引进行检索?
mysql> explain select * from emp where ename = 'KING';
6>索引的常见失败情况
第一种情况
select * from emp where ename like '%T';
即使添加了索引也不走索引的原因
原因是因为模糊匹配当中以“%”开头了!
尽量避免模糊查询的时候以“%”开始。
这是一种优化的手段/策略
第二种情况
使用or的时候会失效,如果使用or那么要求or两边的条件字段都要有索引,才会走索引。
所以这就是为什么不建议使用or的原因。
第三种情况
使用复合索引的时候,没有使用左侧的列查找,索引失效。
create index emp_job_sal_index on emp(job,sal);
mysql> explain select * from emp where job = 'MANAGER';
mysql> explain select * from emp where sal = 800;
什么是复合索引
两个字段,或者更多的字段联合起来添加一个索引,叫做复合索引。
第四种情况
在where当中索引列参加了运算,索引失效。
mysql> create index emp_sal_index on emp(sal);
mysql> explain select * from emp where sal+1 = 800;
第五种情况
在where当中索引列使用了函数
explain select * from emp where lower(ename) = 'smith';
7>索引的分类
索引是各种数据库进行优化的重要手段。优化的时候优先考虑的因素就是索引。
- 单一索引:一个字段上添加索引。
- 复合索引:两个字段或者更多的字段上添加索引。
- 主键索引:主键上添加索引。
- 唯一性索引:具有unique约束的字段上添加索引。
- …
注意:唯一性比较弱的字段上添加索引用处不大。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)