mysql学习 索引

Wesley13
• 阅读 577

  在平时开发过程中写sql时,我们通常都不太关心sql的性能,只有能给查出来数据,sql的执行速度不是太慢就不会去管它了。但是开发时期的数据量往往都不是太大,很多性能问题只有在生产环境中才会发现,如:数据过多、sql关联了太多的表,使用了太多的join、或者建立了索引,但是索引失效的问题。所以要解决这些性能上的难题,就要去研究mysql最为重要的特性--索引。

一、索引的简介

1.索引的定义

  首先我们来看看官网上,对索引的定义:索引(Index)是帮助MySQL高效获取数据的数据结构。这种数据结构是BTree或B+Tree(我们这里不会去介绍这两种树)。

详细的来讲,数据库维护着两种东西,一个是用户本身存储的数据,另一个则是满足特定查找算法的数据结构,数据结构可以通过一些特定的方式来指向我们使用的数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

2.索引的特点

  索引的优点:

第一点:能够提高数据库检索数据的效率,降低数据库对磁盘的IO读取次数。

第二点:排序过程中是需要消耗CPU资源的,如果加上了索引,那么就会降低CPU的消耗。

索引的缺点:

第一点:索引本身也是一种数据,所以建立了索引会减少磁盘的容量。

第二点:在进行新增、更新、删除过程中,如果添加了索引,mysql还要对索引进行增删改操作,所以索引对增删改操作是负面的。

3.索引的分类

a.单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

b.唯一索引

索引列的值必须唯一,但允许有空值

c.主键索引

设定为主键后数据库会自动建立索引,innodb为聚簇索引

d.复合索引(也叫组合索引)

即一个索引包含多个列,一般来说复合索引的性价比要比单值索引高的多,因此一般建议在创建索引时优先选择复合索引。

4.如何建立索引

首先我们来看看哪些字段要建立索引:

a.主键会自动建立唯一索引,

b.查询中where后面的条件字段应该建立索引,

c.进行连表查询中,与其他表有外键关系的字段要建立索引,

d.在有排序和分组的查询中,需要给排序的字段和分组的字段建立索引,

e.对需要统计的字段,应该加上索引。

在上面我们已经说过了,索引虽然能够大大加快我们的查询速度,但是它也是有缺点的,针对它的缺点我们来看看那些情况不需要加索引:

a.表中的数据太少不需要加索引,这是因为表记录太少的话查询速度本身就会很快,加上索引提升不大,而且还会多占用磁盘容量。

b.在对表的操作中,都太多的增删改情况的字段不加索引。对于这种字段,虽然提供了查询速度,但是会降低更新表的速度,因为在进行增删改操作时,mysql也会对索引进行更新。

c.过滤性太低的字段不要加索引。首先我们来看看什么是过滤性,所谓过滤性指的是这个条件能够过滤多少条记录。举个例子,如user表中的性别(只有男、女性别的情况)字段。

二、查询优化

1.索引失效

在我们实际开发中,是不是往往会遇到即使加上了索引,sql运行的速度有没有提高很多,面对这样的情况,很可能就是索引了,接下来我们就来看看索引失效的规则:

a.最佳左前缀法则

定义:如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。我们来看一个例子

先创建一张user表,有三个字段

mysql学习  索引

给这三个字段添加复合索引

mysql学习  索引

   然后我们来看一个sql:

select * from user where name='小明' and userId='1'

我们来分析下这个sql,在where的后面有两个条件name和userId,首先根据创建的索引,name是能使用的,而userId就不一样了,在创建的索引中name的后面是age,那么根据左前缀法则,那么userId没有用到索引。那么为什么索引的使用是必须遵守左前缀法则呢,这是和索引创建的数据结构有关,如图。

根据图我们可以看到,复合索引的创建是一个层级关系,每一层都是由上面的字段指向下一个字段。所以在name_age_userId这个复合索引中,name字段是指向age字段的,age是指向userId字段的,name和userId没有直接关系,因此回到我们的sql,索引只会命中name字段,这就是为什么遵循左前缀法则的原因。

mysql学习  索引

   b.不在索引列上添加任何操作,如函数、计算、类型转换

看一个例子:还是上面的那张表,有两个sql:

select * from user where userId=1;

select * from user where userId='1'

这两个sql中有很明显的是,只有第二个才会用到索引,这是因为我们的userId是varchar类型,而第一个sql中,where后面的userId是用了一个数字进行比较,这就会让这个索引列进行了一次类型转换,导致了索引失效的问题。

c.存储引擎不能使用索引中范围条件右边的列

看一个例子:

select * from user where name='小明' and age>17 and userId='1';

这个sql索引失效的问题也比较简单,首先name能给命中索引,然后age字段由于是一个范围查找,所以索引不能命中age;最后根据左前缀法则,age没有命中索引的,那么userId也无法命中。

接下来的几个索引失效规则都比较简单,就直接列出来:

d.在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描。

e.在查询过程中is not null 也无法使用索引,但是is null是可以使用索引的。

f.在进行模糊查询过程中,like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作。

2.如何正确建立索引

在介绍了索引失效问题后,我们在以后建立和使用索引时就会有一个很好的思路,下面我们就来总结一下如何正确的建立索引

a.在创建复合索引中,过滤性越好的字段顺序应该越靠前越好。

b.组合索引的创建中,有范围查找、不等于、is not null条件的字段应该放在最后。

c.组合索引的字段中,最好包含where后面中所有的索引字段。

e.在使用left join 时,小表作为驱动表(主表),大表作为非驱动表,这样做能给使索引最大化的利用。

f.子查询不要用与join的驱动表,因为索引会失效。

点赞
收藏
评论区
推荐文章
blmius blmius
2年前
MySQL:[Err] 1292 - Incorrect datetime value: ‘0000-00-00 00:00:00‘ for column ‘CREATE_TIME‘ at row 1
文章目录问题用navicat导入数据时,报错:原因这是因为当前的MySQL不支持datetime为0的情况。解决修改sql\mode:sql\mode:SQLMode定义了MySQL应支持的SQL语法、数据校验等,这样可以更容易地在不同的环境中使用MySQL。全局s
Jacquelyn38 Jacquelyn38
3年前
2020年前端实用代码段,为你的工作保驾护航
有空的时候,自己总结了几个代码段,在开发中也经常使用,谢谢。1、使用解构获取json数据let jsonData  id: 1,status: "OK",data: 'a', 'b';let  id, status, data: number   jsonData;console.log(id, status, number )
皕杰报表之UUID
​在我们用皕杰报表工具设计填报报表时,如何在新增行里自动增加id呢?能新增整数排序id吗?目前可以在新增行里自动增加id,但只能用uuid函数增加UUID编码,不能新增整数排序id。uuid函数说明:获取一个UUID,可以在填报表中用来创建数据ID语法:uuid()或uuid(sep)参数说明:sep布尔值,生成的uuid中是否包含分隔符'',缺省为
待兔 待兔
1星期前
手写Java HashMap源码
HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程HashMap的使用教程22
Stella981 Stella981
2年前
Python3:sqlalchemy对mysql数据库操作,非sql语句
Python3:sqlalchemy对mysql数据库操作,非sql语句python3authorlizmdatetime2018020110:00:00coding:utf8'''
Wesley13 Wesley13
2年前
mysql设置时区
mysql设置时区mysql\_query("SETtime\_zone'8:00'")ordie('时区设置失败,请联系管理员!');中国在东8区所以加8方法二:selectcount(user\_id)asdevice,CONVERT\_TZ(FROM\_UNIXTIME(reg\_time),'08:00','0
Wesley13 Wesley13
2年前
mysql5.6 分页查询优化
mysql5.6分页查询优化场景:表结构:主键(非自增)contentCode(varchar),过滤条件列为updateTime(timeStamp),已经为timestamp建立索引。搜索sql为:SELECTFROMmy_hello_tableWHEREupdat
Wesley13 Wesley13
2年前
MySQL部分从库上面因为大量的临时表tmp_table造成慢查询
背景描述Time:20190124T00:08:14.70572408:00User@Host:@Id:Schema:sentrymetaLast_errno:0Killed:0Query_time:0.315758Lock_
为什么mysql不推荐使用雪花ID作为主键
作者:毛辰飞背景在mysql中设计表的时候,mysql官方推荐不要使用uuid或者不连续不重复的雪花id(long形且唯一),而是推荐连续自增的主键id,官方的推荐是auto_increment,那么为什么不建议采用uuid,使用uuid究
Python进阶者 Python进阶者
6个月前
Excel中这日期老是出来00:00:00,怎么用Pandas把这个去除
大家好,我是皮皮。一、前言前几天在Python白银交流群【上海新年人】问了一个Pandas数据筛选的问题。问题如下:这日期老是出来00:00:00,怎么把这个去除。二、实现过程后来【论草莓如何成为冻干莓】给了一个思路和代码如下:pd.toexcel之前把这