明辉站/应用软件/内容

MySql中10分重要的索引设置

应用软件2023-07-16 阅读
[摘要]着重基础之—MySql 不能遗忘的索引操作  关于MySql索引的基础知识我就不在这里写了,我不太想当信息的搬运工。  技巧分享:Workbench 作为一款专为MySQL设计的ER/数据库建模工具。除了管理数据库外,其实也是一款Sql语句生成利器。合理利用,将带来事半功倍的效果。当然,前提是我们...

着重基础之—MySql 不能遗忘的索引操作

  关于MySql索引的基础知识我就不在这里写了,我不太想当信息的搬运工。

  技巧分享:Workbench 作为一款专为MySQL设计的ER/数据库建模工具。除了管理数据库外,其实也是一款Sql语句生成利器。合理利用,将带来事半功倍的效果。当然,前提是我们对基础知识的了解。

  我先来整理一些索引操作的Sql语句,之所以整理,起因是我遇到问题时,在百度里查到的回复,基本上都是错的,需要有人来纠正

  1.多主键(PRIMARY)删除

  假设场景:你在表里定义了多个主键(PRIMARY),比如:id是自增主键,userCode也定义成了主键、userAge也定义成了主键。你打算只保留一个id,其他俩个删除,那么对应的Sql语句如下:

  解释:对指定表进行修改,删除 PRIMARY KEY,同时增加 id 做为 PRIMARY KEY。


ALTER TABLE `表名` 
DROP PRIMARY KEY,ADD PRIMARY KEY (`id`);

  2.增加普通索引

  警告:千万别不改就执行,table_name不是我强调的重点,我要说的是index_name,一定要合理规范索引的命名,例如index_字段名称,或者你有更好的,可以给我留言。

  否这你会报错: Duplicate key error in MySQL (Duplicate key name '')。可以肯定的是因为 index_name 没改。沿用了index_name。


ALTER  TABLE  `table_name`  ADD  INDEX index_name (`column`)

  3.关于索引使用规范

  (1).索引的使用

  索引是需要占用物理存储空间的,索引的使用不当,不但会占用存储空间,而且对查询速度还会起到适得其反的作用,所以索引的定义要数据库设计是要有很清晰的认识。建索引的几大原则

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)的索引,那么只需要修改原来的索引即可

  

  (2).索引类型

  1.普通索引
    最基本的索引,没有任何限制

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

  3.主键索引
    主键索引属于一种特殊的唯一索引,不允许有空值

  4.单列索引
    单个多列索引(组合索引)效率高于多个单列索引

  5.最左前缀(Leftmost Prefixing):多列索引

以上就是MySql中非常重要的索引操作的详细内容,更多请关注php中文网其它相关文章!


学习教程快速掌握从入门到精通的SQL知识。

……

相关阅读