MySQL索引

| 分类 index  | 标签 index  mysql  浏览次数: -

准备环境

  • 查看帮助信息
help create index
  • 创建表
create database test;
use test
create table t (
    id int not null PRIMARY KEY AUTO_INCREMENT,
    username varchar(16) not null,
    index index_name(username)
) AUTO_INCREMENT=1;
  • 创建索引

username的索引名字叫 index_name

create index index_name on t(username);

注意:

1.索引需要占用 磁盘空间,因此在创建索引时需要考虑到磁盘空间是否足够。

2.创建索引时需要 对表加锁,因此实际操作中需要在业务空闲期间进行

  • 查看索引
show index from t;
  • 删除索引
drop index index_name on t;

alter table t drop index index_name;
  • 查看建表语句
show create table t;
  • 插入数据

自增的列可以忽略数据

insert into t (username) values ("test");
  • 查看执行计划
explain select * from t;

explain select * from t where id=1;

索引的目的

索引的目的在于提高查询效率,可以类比新华字典,如果要查询 黄 字的释义,那么我们需要先知道 黄 的拼音 huang ,再开始需要定位到 h 字母,然后往下找到 u 字母,再找剩下的 ang 。如果没有索引,那么我们只能把新华字典的所有字都翻一遍才能找到我们想要的。

索引原理

通过不断缩小想要获得数据的范围来筛选出最终想要的结果,同时把随机的事件变成顺序的事件,也就是我们总是通过同一种查找方式来获取数据。即将无序的数据变成相对有序的数据

磁盘IO

磁盘读取数据靠的是机械运动,每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分。

寻道时间指的是磁臂移动到指定磁道所需要的时间,主流磁盘一般在 5ms 以下

旋转延迟就是我们经常听说的磁盘转速,比如一个磁盘 7200 转,表示每分钟能转 7200 次,也就是说 1 秒钟能转 120 次(7200 / 60 = 120),旋转延迟就是 1 / 120 / 2 = 4.17ms (旋转延迟用磁盘旋转一周所需时间的一半来表示)。

传输时间指的是从磁盘读出或将输入写入磁盘的时间,一般在 零点几毫秒。

访问一次磁盘的时间,即一次磁盘IO的时间约等于 5 + 4.17 ~= 9ms 左右. 普通机器每秒可以执行 5亿 条指令,因为指令依靠的是电的性质,即执行一次IO的时间可以执行 40万 条指令,数据库动辄十万百万乃至千万级数据,每次 9毫秒,显然是个灾难。

预读

考虑到磁盘IO是非常高昂的操作,计算机操作系统做了一些优化,当一次IO时,不光把当前磁盘地址的数据,同时把相邻的数据也读取到内存缓冲区内,因为局部预读性原理告诉我们,当计算机访问一个地址的数据时,与其相邻的数据也会很快被访问到。

每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据和操作系统有关,一般为 4k 或 8k,也就是我们读取一页内的数据时候,实际上才发生一次IO。

b+树

b+树

如上图所示,是一颗b+树。

浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

b+树的查找过程

如图所示,如果要查找数据项29.

1.那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO 2.在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO 3.29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。

真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

b+树性质

1.通过上面的分析,我们知道IO次数取决于b+数的高度h,假设当前数据表的数据为N,每个磁盘块的数据项的数量是m,则有h=㏒(m+1)N,当数据量N一定的情况下,m越大,h越小;而 m = 磁盘块的大小 / 数据项的大小,磁盘块的大小也就是一个数据页的大小,是固定的,如果数据项占的空间越小,数据项的数量越多,树的高度越低。这就是为什么每个数据项,即索引字段要尽量的小,比如int占4字节,要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点,一旦放到内层节点,磁盘块的数据项会大幅度下降,导致树增高。当数据项等于1时将会退化成线性表。

2.当b+树的数据项是复合的数据结构,比如(name,age,sex)的时候,b+数是按照从左到右的顺序来建立搜索树的,比如当(张三,20,F)这样的数据来检索的时候,b+树会优先比较name来确定下一步的所搜方向,如果name相同再依次比较age和sex,最后得到检索的数据;但当(20,F)这样的没有name的数据来的时候,b+树就不知道下一步该查哪个节点,因为建立搜索树的时候name就是第一个比较因子,必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时,b+树可以用name来指定搜索方向,但下一个字段age的缺失,所以只能把名字等于张三的数据都找到,然后再匹配性别是F的数据了, 这个是非常重要的性质,即索引的最左匹配特性。

索引的几大基本原则

  • 1.最左前缀匹配原则

非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引,d是用不到索引的,如果建立(a,b,d,c)的索引则都可以用到,a,b,d的顺序可以任意调整。

  • 2.=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序,mysql的查询优化器会帮你优化成索引可以识别的形式。

  • 3.尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少,唯一键的区分度是1,而一些状态、性别字段可能在大数据面前区分度就是0,那可能有人会问,这个比例有什么经验值吗?使用场景不同,这个值也很难确定,一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条记录。

  • 4.索引列不能参与计算,保持列”干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引,原因很简单,b+树中存的都是数据表中的字段值,但进行检索时,需要把所有元素都应用函数才能比较,显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。

  • 5.尽量的扩展索引,不要新建索引

比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

查询优化神器 - explain命令

关于explain命令相信大家并不陌生,具体用法和字段含义可以参考官网explain-output,这里需要强调rows是核心指标,绝大部分rows小的语句执行一定很快(有例外,下面会讲到)。所以优化语句基本上都是在优化rows。

慢查询优化基本步骤

  • 0.先运行看看是否真的很慢,注意设置SQL_NO_CACHE
  • 1.where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
  • 2.explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
  • 3.order by limit 形式的sql语句让排序的表优先查
  • 4.了解业务方使用场景
  • 5.加索引时参照建索引的几大原则
  • 6.观察结果,不符合预期继续从0分析

索引的优缺点

优点

  • 1.可以快速检索,减少I/O次数,加速检索速度
  • 2.根据索引分组和排序,可以加快分组和排序
  • 3.通过创建唯一性索引,可以保证数据表中每一行数据的唯一性
  • 4.帮助服务器避免排序和临时表
  • 5.将随机IO变成顺序IO

缺点

  • 1.索引本身也是表,因此会占用存储空间,一般来说,索引表占用空间是数据表的1.5倍
  • 2.索引表的维护和创建需要时间成本,这个成本随着数量增大而增大
  • 3.构建索引会降低标的修改(删除、添加、修改)的效率,因为在修改数据表的同时还需要修改索引表

什么时候要使用索引

  • 1.主键自动建立唯一索引;
  • 2.经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引;
  • 3.作为排序的列要建立索引;
  • 4.查询中与其他表关联的字段,外键关系建立索引
  • 5.高并发条件下倾向组合索引;
  • 6.用于聚合函数的列可以建立索引,例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不要使用索引

  • 1.经常增删改的列不要建立索引;
  • 2.有大量重复的列不建立索引;
  • 3.表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时,数据库的性能测试结果才有意义。

索引失效的情况

  • 1、在组合索引中不能有列的值为NULL,如果有,那么这一列对组合索引就是无效的。
  • 2.在一个SELECT语句中,索引只能使用一次,如果在WHERE中使用了,那么在ORDER BY中就不要用了。
  • 3.LIKE操作中,’%aaa%’不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引。
  • 3.在索引的列上使用表达式或者函数会使索引失效,例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users where adddate<'2007-01-01'。其它通配符同样,也就是说,在查询条件中使用正则表达式时,只有在搜索模板的第一个字符不是通配符的情况下才能使用索引。
  • 4.在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效。特别的是如果对主键索引使用!=则不会使索引失效,如果对主键索引或者整数类型的索引使用<符号或者>符号不会使索引失效。(经erwkjrfhjwkdb同学提醒,不等于,包括<符号、>符号和!,如果占总记录的比例很小的话,也不会失效)
  • 5.在查询条件中使用IS NULL或者IS NOT NULL会导致索引失效。
  • 6.字符串不加单引号会导致索引失效。更准确的说是类型不一致会导致失效,比如字段email是字符串类型的,使用WHERE email=99999 则会导致失败,应该改为WHERE email=’99999’。
  • 7.在查询条件中使用OR连接多个条件会导致索引失效,除非OR链接的每个条件都加上索引,这时应该改为两次查询,然后用UNION ALL连接起来。
  • 8.如果排序的字段使用了索引,那么select的字段也要是索引字段,否则索引失效。特别的是如果排序的是主键索引则select * 也不会导致索引失效。
  • 9.尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引;

参考


上一篇 MongoDB索引     下一篇 Docker Swarm集群
目录导航