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集群
目录导航