专栏首页T客来了MySQL专题- 数据库索引使用场景&注意事项

MySQL专题- 数据库索引使用场景&注意事项

目录

1.何种查询支持索引? 2.注意事项和建议

一 何种查询支持索引?

1 MySQL 目前支持前导列


就目前来说,mysql 暂时只支持最左前缀原则进行筛选。
例子:创建复合索引
create index idx_a_b_c on tb1(a,b,c)
只有使用如下条件才可能应用到这个复合索引
1.where a=?
2.where a = ? and b = ?
3.where a = ? and b = ? and c = ?
但
4.where a = ? and c = ?
只会使用到mysql 索引 a 列的信息

2.索引列上的范围查找


对于某个条件进行范围查找时,如果这个列上有索引,且使用 where ... between 
and ... > ,< 等范围操作,那么可能用到索引范围查找,如果索引范围查找的成本太高,
数据库可能会选择全表扫描的方式
。注意 in  不属于范围查找的范畴。

3.join 列


在联合查询两个表时,比如查询语句为 select a.col1,b.col2 from a join b on a.id = b.id,
其中id 为两个表的主键,如果a是小表,那么a 就被视为驱动表,那么数据库可能全表扫描a 表,
并用 a表的每个id 去探测b表的索引查询匹配的记录。

4.where 子句


形如:
where a = ? and b = ? and c>1000
where a = ? and b = ? and c = ? and d>1000

where 子句的条件列是复合索引前面的索引列+另一个列的范围查找

create  index idx_a_b_c_d on tb1(a,b,c,d);

形如:
where a = ? and b = ? and c>1000
where a = ? and b = ? and c = ? and d>1000
才会用到这个索引

下面两个查询:
where a = ? and b =? and c>10000 and d< 10000
这个例子中d 
d <10000这个操作不会走索引
where a >? and b =? and c>10000 and d< 10000
这个例子中a列上有范围查找,那么b、c、d列上的索引信息都不能被利用
原则,创建索引,考虑把复合索引的范围查找放到最后。

5.mysql 优化器


mysql 优化器会做一些特殊优化,比如对于索引查找max(索引列)可以直接进行定位。
遇到max,min  是可以在列上做索引。

二 注意事项和建议


1.where 条件中的索引列不能是表达式的一部分,mysql 不支持函数索引

2.InnoDB 二级索引底层叶子极点存储的是索引+主键值

InnoDB 的非主键索引存储的不是实际的记录的指针,而是主键的值,所以主键最好是整数型,如自增ID ,基于主键存取数据是最高效的,使用二级索引存取数据则需要进行二次索引查找。

3.索引尽量是高选择性的

而且要留意基数值,基数值指的是一个列中不同值的个数,显然,
最大基数意味着该列中的每个值都是唯一的,最小基数意味着该列中的所有值都是相同的,索引列的基数相对于表的行数较高时,
也就重复值更少,索引的工作效果更好。
有种情况虽然基数很小,但由于数据分布很不均匀因此也会导致某些记录数很小,
那么这种情况也适合建立索引加速查找这部分数据。

4.使用更短的索引

可以考虑前缀索引,但应确保选择的前缀的长度可以保证大部分值是唯一的。
如:alter table test add  key(col(6))
衡量不同前缀索引唯一值比例。
select  count(distinct left(col_name,5))/count(*) As sele5,
select  count(distinct left(col_name,6))/count(*) As sele6,
select  count(distinct left(col_name,7))/count(*) As sele7,
select  count(distinct left(col_name,8))/count(*) As sele8,
select  count(distinct left(col_name,9))/count(*) As sele9
from table_name;

5.避免创建过多的索引

索引过多可能会浪费大量空间
尤其本身字段量较大的字符串,索引过多可能会浪费空间,且降低修改数据的速度,
所以,不要创建过多的索引,也不要创建重复的索引。

6.如果是唯一值得列,创建唯一索引会更佳,也可以确保不会出现重复数据.

7.使用覆盖索引能大大提高性能

覆盖索引:所有数据都可以从索引中得到,而不需要去读物理记录。例如某个复合索引idx_a_b_c 建立在表tb1 的 a、b、c 列上,
那么对于如下的sql 语句
select a,b from tb1 where a = ? and b = ? and c =?
mysql可以直接从索引idx_a_b_c  中获取数据。使用覆盖索引也可以避免二次索引查找。
使用explain 命令输出查询计划,如果extra列是“using index ” 那就表示使用的是覆盖索引。

8.利用索引来排序

mysql 有两种方式可以产生有序结果,一种是使用文件排序,另一种是扫描有序的索引,我们尽量使用索引来排序
 注意事项:
   1. 尽量保证索引列和order by 的列相同,且各列按照相同的顺序排序。
    比如在表table1 的复合索引idx_a_b_c(创建在a,b,c上);
    如:select * from table1 order by  a,b,c;
           select * from table1 where a=? and b =? order by c
    以上查询都可以利用有序索引来加速检索顺序。
   2.如果连接多张表,那么order by 引用的列需要再表连接顺序的首张表内。

9 添加冗余索引需要权衡:

如果一个索引column A 那么一个新的索引(columnA,columnB)就是冗余索引
  一般情况下不论是新增冗余索引,还是扩展原索引为冗余索引,都会导致索引文件的增大,并且增加了维护索引的开销。
  比如更改了列值,并且在此列上建立了索引,那么这个列值更改之后,索引是要进行重新排序的。

本文分享自微信公众号 - T客来了(ltdo11),作者:ltdo111

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2018-10-29

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • MySQL专题- 数据库索引原理与分类

    那么怎样在I/O 块大小 的限制下快速利用二分查找找到目标值呢?我们得引入新的数据结构,B+树正好可以解决上述I/O块大小的限制,解决限制不是说增大了限制范围,...

    用户1081422
  • MySQL 数据库规范--设计篇 (内附福利哦)

    直接参考[MySQL 数据库索引使用场景&注意事项](http://www.jianshu.com/p/aedf4c0972e8)

    用户1081422
  • MySQL 数据库规范--开发篇

    table name = test、column1 = id、column2 = name.

    用户1081422
  • 这句简单的sql,如何加索引?颠覆了我多年的认知

    不啰嗦,直接入正题。问题是这样的。请问下面的sql语句,要想加快查询速度,该怎么创建索引?以下,以mysql数据库为准。

    xjjdog
  • 两千字揭密 MySQL 8.0.19 三大索引新功能:隐藏索引,降序索引,函数索引

    导读:本文详细介绍 MySQL 8.0.19 三大索引新功能,隐藏索引,降序索引,函数索引,结合其他同仁的技术应用案例,进一步进行验证改编,最后总结心得,希望对...

    数据和云
  • Mysql - 组合索引的B+树存储结构(最左前缀原理)

    那组合索引的B+树存储结构是什么样的呢,为什么会有最左前缀原理,看了很多帖子找到了答案

    夹胡碰
  • MySQL数据库,索引的数据结构及基本原理

    索引的数据结构和具体存储引擎的实现有关,在 MySQL 中使用较多的索引有 Hash 索引,B+树索引等,而我们经常使用的 InnoDB 存储引擎的默认索引实现...

    用户1289394
  • MySQL面试题

    BTree索引,主流有两种,一种是B树,每一个叶子节点和中间节点中都存在有数据和指针;另一个是B+树,所有的数据都存储在叶子节点,中间节点也是一个索引。

    石的三次方
  • Mysql索引:图文并茂,深入探究索引的原理和使用

    相信每个IT界大佬,简历上少不了Mysql索引这个关键字,但如果被问起来,你能说出多少干货呢?先看下面几个问题测试一下吧:

    IT大咖说
  • 我叫Mongo,干了「索引探索篇」提升我的效率,值得您拥有

      Mongodb的索引和其它关系型数据库索引很类似,索引是一个存储结构,其存储的内容是数据文档持久化的位置信息。一个数据集合和一本书来对比,那么索引就是书对应...

    小小许

扫码关注云+社区

领取腾讯云代金券