专栏首页程序员备忘录MySql学习笔记(二)- 索引的设计和使用

MySql学习笔记(二)- 索引的设计和使用

作为开发人员,数据库的索引是我们再熟悉不过的了。那么实话真的会了吗,在项目开发中随便定义一个int、varchar后边跟个primary key或者加个index就好了么?考虑到这些咋还真的需要看看专业的人都是怎么做的。

在mysql中索引是提升性能的常用工具,一般来说我们就是通过让where的条件字段变成索引,或者如果where条件比较多的话一般将前边的条件做成索引。这样就可以通过索引快速筛选大量数据,然后在小范围的结果集中通过全表扫描的方式进行目标检索。

在mysql中每个列都可以被索引,但是对于每种表类型来说,索引的数量和索引的本身字段长度都有限制。比如myisam、innodb、memory等每个表至少支持16个索引。索引长度至少256字节。mysisam和innodb表的默认索引为Btree索引,支持前缀索引,也就是对一个字段的前多少个字符建立的索引,对于myisam表来说索引的前缀可达1000字节,而对innoDb来说其前缀索引最长为767字节。

mysql中还支持全文索引,这种索引可以做全文检索使用,但是全文索引只限于char、varchar和text字段列。

默认情况下,memory使用的是hash索引,但也支持btree索引

如何对一个字段创建索引?

-- 对test表的name字段的前5个字段检索索引
create index name_index on test(name(5))
-- 当然,我们也可以看看我们的索引在检索的时候是否被使用了
explain select * from test where name!=''
-- 如果您要删除索引
drop index name_index on test;

索引创建的原则

1.不一定要选择所有选择的列,最适合索引的列是出现在where字句中的列,而不是select中的列

2.使用唯一索引,考虑某个列值得分散。索引的列的基数越大索引效果越好。例如存放出生日期的列具有不同值,很容易区分,而使用性别的话区分度不大。

3.使用短索引,如果对字符串进行索引,应该指定一个前缀长度,比如一个字段varchar(300),但是前10到20字段就能区分,那么在前10到20个字符上建立索引就可以区分很多数据,而且还可以节省索引空间。磁盘io也会变少,而且mysql在内存中可以存储更多的缓存,所以检索会变快。

4.利用最左前缀,在创建一个n列索引的时候,实际上创建了mysql可利用的n个索引,多列索引可起多个索引的作用,但是匹配的时候都是通过最左进行的。

5.不要过度索引,每个索引都要占据磁盘空间,并降低写操作的性能,在修改表的时候索引也要同步更新甚至重构,所以索引越多会带来一定的损耗。所以说如果一个索引经常不用那么这个索引就是负担。所以的创建索引要以优化检索为目的,但不能损耗插入的性能。

6.innodb表默认是按照一定顺序存储的,也就是说如果指定了主键则按照主键顺序进行保存,如果没有主键但是有唯一索引,那么innodb会按照唯一索引的顺序进行存储,如果都没有的话,innodb会生成一个内部列并按这个列的顺序进行存储。检索的时候按主键或者内部列是最快的。所以innodb表最好指定主键,另外innodb表的普通索引也会存储主键的键值,所以在设置主键的时候尽量选用短数据类型,从而减轻索引的磁盘空间,提高索引的缓存效果。

Btree和Hash索引

memory可以选择btree和hash索引,但是hash索引的使用要注意哦!因为hash索引是通过计算得出的具体数据地址,索引hash索引肯定不能使用= 或者》= 和《=等运算符,当然也不能使用order by进行排序。hash索引只能通过关键字来检索一行数据。对于in查询来说,btree和hash索引并没有什么差别。在对范围进行查询的时候,只能使用btree索引。如果对hash索引采用范围查询,那么将是全表扫描式的检索。要注意的是memory表的默认索引模式为hash索引。

大多数的mysql索引,比如primary key、unique、index、fulltext等都是在btree中存储,除此之外memory还支持hash索引。

本文分享自微信公众号 - 程序员备忘录(gh_a84f9a607848),作者:tianjl

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

原始发表时间:2020-10-27

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

我来说两句

0 条评论
登录 后参与评论

相关文章

  • ApplicationListener接口实践

    通过之前的学习,我们了解到ApplicationListener接口在spring事件广播中能够将消息广播到需要的监听器中。那么就让我们手撸一个监听器来试试。看...

    程序员_备忘录
  • Jenkins学习笔记(二)- 跨机器部署项目

    在上期文章中,我们说自动构建和跨机器部署项目是重点,作者通过实践发现Jenkins通过pipline发送项目到远程服务器的比较麻烦,尤其是ssh的方式对linu...

    程序员_备忘录
  • Mybatis学习笔记(三)- Mybatis插件原理

    通过之前的分析和代码跟踪,我们基本上了解了mybatis的一些大概情况。但在文中结尾的时候,我们说对于mybatis的插件的原理还不足够的清晰。当时我们通过分析...

    程序员_备忘录
  • Oracle初级索引学习总结

       索引是常见的数据库对象,建立索引的目的是为了提高记录的检索速度。它的设置好坏,使用是否得当,极大地影响数据库应用程序和Database的性能。虽然有许多资...

    aehyok
  • 你真的了解索引了吗(一)

    给大家介绍下,我是集美貌与才华于一身的程序媛钱六六,人在美国,刚下飞机,就收到了男神无双的短信轰炸

    黑洞代码
  • Mysql - 组合索引的B+树存储结构(最左前缀原理)

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

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

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

    用户1289394
  • MySQL面试题

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

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

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

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

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

    小小许

扫码关注云+社区

领取腾讯云代金券