MySQL索引原理
一、索引类型
索引可以提升查询速度,会影响where查询,以及order by排序。
1.普通索引
基于普通的索引类型,是基于普通字段建立的索引,没有任何限制。
创建索引的方法:
- CREATE INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD INDEX [索引的名字] (字段名)
- CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名) );
2.唯一索引
索引字段的值必须唯一,但允许空值。创建或修改表时追加唯一约束,就会自动创建对应的唯一索引。
创建唯一索引的方法:
- CREATE UNIQUE INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD UNIQUE INDEX [索引的名字] (字段名);
- CREATE TABLE tablename ( […], UNIQUE [索引的名字] (字段名) ;
3.主键索引
它是一种特殊的唯一索引,不允许有空值。在创建或修改表时追加主键约束即可,每个表只能有一个主
键。
创建主键索引的方法如下:
- CREATE TABLE tablename ( […], PRIMARY KEY (字段名) );
- ALTER TABLE tablename ADD PRIMARY KEY (字段名);
4.复合索引
单一索引是指索引为一列的情况,即新建索引的语句只实施在一列上;用户可以在多个列上建立索引,这种索引做复合索引。
创建组合索引的方法如下:
- CREATE INDEX <索引的名字> ON tablename (字段名1,字段名2…);
- ALTER TABLE tablename ADD INDEX [索引的名字] (字段名1,字段名2.
- CREATE TABLE tablename ( […], INDEX [索引的名字] (字段名1,字段名2…) );
不要过多使用会影响where查询,影响更新速度。优点是相比单一索引开销更少。
4.全文索引
数据比较时候会使用like查询,但是当数据比较多的时候一般使用全文索引,速度比like快几倍。mysql5.6后都支持MongoDB个MYISAM引擎。
创建索引的方法:
- CREATE FULLTEXT INDEX <索引的名字> ON tablename (字段名);
- ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
- CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;
二、索引原理
存储引擎快速查找记录的一种数据结构,需要额外开辟和数据维护工作。
- 索引是物理数据页存储,在数据文件中(InnoDB,ibd文件),利用数据页(page)存储
- 索引可以加快检索速度,但是同时也会降低增删改操作速度,索引维护需要代价。
涉及的理论知识:二分查找、hash、B+Tree
1.二分查找法
二分查找也叫折半查找法,在有序数组中查找指定数据的搜索算法。优点:等值查询、范围性能优秀;缺点更新数据、新增数据、删除数据和维护成本。
查找方式:
- 首先定义light和left指针
- 计算(light+left)/2
- 判断除2之后索引位置值和目标值的大小对比
- 索引位置大于目标值-1,right移动,小于目标值+1,left移动
例如:二分查找
第一次查找:
第一次查找比目标值大 所以右指针-1
2.Hash结构
底层是hash表来实现的,是根据键值对<key,value>存储数据的结构。非常适合根据key查找value值,也就是单个key 查询。
3.B-Tree和B+Tree结构
mysql采用的是B+Tree结构,在B-Tree结构上做了优化。
B-Tree结构
索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列
- B树的搜索:从根节点开始,对节点内的索引值序列采用二分法查找,如果命中就结束查找。没有
命中会进入子节点重复查找过程,直到所对应的的节点指针为空,或已经是叶子节点了才结束
B+Tree结构(从左到右有指针)
相比于B-Tree多了指针
- 非叶子节点不存储data数据,只存储索引值,这样便于存储更多的索引值
- 叶子节点包含了所有的索引值和data数据
- 叶子节点用指针连接,提高区间的访问性能
- 相比B树,B+树进行范围查找时,只需要查找定位两个节点的索引值,然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据,显然B+Tree效率高。
4.聚簇索引和辅助索引
1 聚簇索引和非聚簇索引
B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。
2 主键索引和辅助索引
B+Tree的叶子节点存放的是主键字段值就属于主键索引;如果存放的是非主键值就属于辅助索引(二级索引)。
三 索引分析与优化1 EXPLAIN
mysql提供了一个explain命令,用这个,命令可以对select语句进行分析,输出详细信息后可对其进行优化。
EXPLAIN SELECT * from user WHERE id < 3;
EXPLAIN 命令的输出内容大致如下:
2.最左前缀原则
复合索引使用时遵循最左前缀原则,最左前缀顾名思义,就是最左优先,即查询中使用到最左边的列,那么查询就会使用到索引,如果从索引的第二列开始查找,索引将失效。
3.LIKE查询
面试题:MySQL在使用like模糊查询时,索引能不能起作用?
回答:可以使用,只有把%字符放在后面才会使用到索引。
select * from user where name like 'o%'; //起作用
4.NULL查询
面试题:如果MySQL表的某一列含有NULL值,那么包含该列的索引是否有效?
答:对mysql来说,Null是一个特殊的值,用概率上说意味着是一个未知值,他处理方式与其他值有些不同。比如:不能使用=,<,>这样的运算符,对null做算法运算结果都是null,count时不会包括null等。NULL列需要增加额外空间来记录其值是否为NULL。虽然MySQL可以在含有NULL的列上使用索引,但NULL和其他数据还是有区别的,不建议列上允许为NULL。最好设置NOT NULL,并给一个默认值,比如0和 ‘’ 空字符串等,如果是datetime类型,也可以=设置系统当前时间或某个固定的特殊值,例如’1970-01-01 00:00:00’。
代码如下(示例):
5.索引与排序
MySQL查询支持filesort和index两种方式的排序,filesort是先把结果查出,然后在缓存或磁盘进行排序
操作,效率较低。使用index是指利用索引自动实现排序,不需另做排序操作,效率会比较高。
filesort有两种排序算法:双路排序和单路排序
- 双路排序:需要两次磁盘扫描读取,最终得到用户数据。第一次将排序字段读取出来,然后排序;第二次去读取其他字段数据。
- 单路排序:从磁盘查询所需的所有列数据,然后在内存排序将结果返回。
该处使用的url网络请求的数据。
总结
文章简单讲述了索引原理以及索引优化问题。