精华内容
下载资源
问答
  • MySQL的where条件中,有时会用到很多的条件,通常为了加快速度会把这些字段放到联合索引中,可以更快的提高搜索速度; 但是对联合索引中字段顺序的合理排序,便更能提高速度 例子:select * from table where ...

    在MySQL的where条件中,有时会用到很多的条件,通常为了加快速度会把这些字段放到联合索引中,可以更快的提高搜索速度;

    但是对联合索引中字段顺序的合理排序,便更能提高速度

    例子:select * from table where (groupid=1000) and (userid=500) and (time=140012345)

    建立的索引也通常很随性的就按照where条件中字段的顺序建立

    ALTER TABLE table ADD INDEX g_u_time_index(groupid,userid,time);

    那么MySQL就会先检索到groupid=1000的所有数据,假如搜到了1000条,再在检索到的1000条数据中再检索userid=500的数据,假设还剩余300条,再在300条中搜索time=140012345的数据,假设就剩了100条;这样总共搜索了1000+300+100=1400条数据才找到符合的100条

    但是为了更好的优化,我们现在需要在建立索引前,对这几个字段进行计算,计算他们在表中所占的比例

    可以写一个select语句来查看:计算各个条件在表中所占的比例大小

    select count(*) total,sum(groupid=1000) gtotal,sum(userid=500) utotal,sum(time=1400123456) time from table;

    total 10000

    gtotal 1000

    utotal 300

    time 800

    可见如果先搜索岛userid=500的数据就可以剔除掉一大半,只剩下300条符合的,再在300中搜索符合time=1400123456和groupid=1000的数据,这样就大大缩减了查询次数;

    按照从小到大的顺序排列,可以减少搜索次数

    sql改为:select * from table where (userid=500) and (time=140012345) and (groupid=1000)

    ALTER TABLE table ADD INDEX g_u_time_index ( userid , time , groupid)

    总结:这种情况更加适合某个特定的慢sql。如果没有可以参考全局的基数和选择性。sql如下

    select count(distinct userid)as userid,
    	count(distinct time)as time,
    	count(distinct groupid)as groupid
     from table
    

    选择性大的排在前列。

    展开全文
  • mysql 优化 联合索引

    2019-08-04 10:05:32
    如何建立联合索引 唯一值越多,离散度就越好,可选择性就越高,联合索引时,离散度高的放前面

     

    如何建立联合索引

     

     

     

     

     

    唯一值越多,离散度就越好,可选择性就越高,联合索引时,离散度高的放前面

     

     

     

     

     

     

    展开全文
  • 为经常需要排序、分组和联合操作的字段建立索引 经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。 为常作为查询条件的字段建立...

    使用MySQL索引都有什么原则?

    1. 选择唯一性索引

      唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。例如,学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话,可能存在同名现象,从而降低查询速度。

    2. 为经常需要排序、分组和联合操作的字段建立索引

      经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段,排序操作会浪费很多时间。如果为其建立索引,可以有效地避免排序操作。

    3. 为常作为查询条件的字段建立索引

      如果某个字段经常用来做查询条件,那么该字段的查询速度会影响整个表的查询速度。因此,为这样的字段建立索引,可以提高整个表的查询速度。

    4. 限制索引的数目

      索引的数目不是越多越好。每个索引都需要占用磁盘空间,索引越多,需要的磁盘空间就越大。修改表时,对索引的重构和更新很麻烦。越多的索引,会使更新表变得很浪费时间。

    5. 尽量使用数据量少的索引

      如果索引的值很长,那么查询的速度会受到影响。例如,对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

    6. 尽量使用前缀来索引

      如果索引字段的值很长,最好使用值的前缀来索引。例如,TEXT和BLOG类型的字段,进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符,这样可以提高检索速度。

    7. 删除不再使用或者很少使用的索引

      表中的数据被大量更新,或者数据的使用方式被改变后,原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引,将它们删除,从而减少索引对更新操作的影响。

    8. 最左前缀匹配原则,非常重要的原则。

      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的顺序可以任意调整。

    9. =和in可以乱序。

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

    10. 尽量选择区分度高的列作为索引。

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

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

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

    12. 尽量的扩展索引,不要新建索引。
      比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可。

    MySQL索引如何使用

    1. 在连接查询join语句执行过程中,驱动表是走全表扫描,而被驱动表是走树搜索。所以我们尽量需要让小表做驱动表。(在决定哪个表做驱动表的时候,应该是两个表按照各自的条件过滤,过滤完成之后,计算参与join的各个字段的总数据量,数据量小的那个表,就是“小表”,应该作为驱动表)
    2. 尽量使用连接查询代替子查询,子查询效率太差,执行子查询时,MYSQL 需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,这里多了一个创建和销毁临时表的过程
    3. 索引列务必重复度低,where 条件字段上需要建立索引
    4. 使用索引就不能用OR查询,否则索引不起作用
    5. 使用索引,like模糊查询不能以%开头
    6. 查询条件务必以索引列开头,否则索引失效
    7. 复合索引遵守最左原则
    8. 根据业务场景建立覆盖索引只查询业务需要的字段,如果这些字段被索引覆盖,将极大的提高查询效率
    9. 多表连接的字段上需要建立索引,这样可以极大提高表连接的效率
    10. where 条件字段上需要建立索引,但Where 条件上不要使用运算函数,以免索引失效
    11. 排序字段上,分组字段上需要建立索引
    12. 优化insert语句:批量列插入数据要比单个列插入数据效率高
    展开全文
  • MySQL联合索引原理解析

    千次阅读 2018-12-09 13:36:00
    什么是MySQL联合索引 联合索引又叫复合索引,是MySQL的InnoDB引擎中的一个索引方式,如果一个系统频繁地使用相同的几个字段查询结果,就可以考虑建立这几个字段的联合索引来提高查询效率。 如何建立索引 举个例子: ...

    什么是MySQL联合索引

    联合索引又叫复合索引,是MySQL的InnoDB引擎中的一个索引方式,如果一个系统频繁地使用相同的几个字段查询结果,就可以考虑建立这几个字段的联合索引来提高查询效率。

    如何建立索引

    举个例子:

    create table `table_name`(
     `id` bigint(20) NOT NULL  PRIMARY KEY,
      `a` int(11),
      `b` int(11),
      `c` varchar(22),
       KEY `key_a_b_c` (`a`,`b`,`c`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    如上面的代码其中

    KEY `key_a_b_c` (`a`,`b`,`c`)
    
    

    语句就是建立了a,b,c字段联合索引的语句。

    最左前缀原则

    在使用联合索引时要注意有个最左前缀原则,最左前缀原则就是要考虑查询的字段的顺序,只有遵守这个原则才能最大地提高查询的效率,下面我们举个例子说明最左前缀原则。

    建立 (a,b,c)的联合索引

    #完全按创建的顺序,能走到a,b,c3个字段的索引,评级:优化最高
    SELECT * FROM tz_prod.table_name where a = 1 and b=2 and c = '3';
    
    #换了b和c的顺序,MySQL会进行优化,效率和上面的一样,评级:优化最高
    SELECT * FROM tz_prod.table_name where a = 1 and c = '3' and b=2;
    
     #能走到a和b的索引,评级:优化最高
     SELECT * FROM tz_prod.table_name where    a = 1 and b=2 ;
    
    #能走到a和b的索引,b的范围查询不影响优化,评级:优化最高
     SELECT * FROM tz_prod.table_name where     a = 1 and b<2 ;
    
    #能走到a的索引,评级:优化最高
    SELECT * FROM tz_prod.table_name where    a = 1;
    
    #能走到a的索引,评级:优化最高
    SELECT * FROM tz_prod.table_name order by   a ;
    
    #只能走到a的索引走不到c的索引,如果c的离散度高则查询效率很低,评级:优化差
    SELECT * FROM tz_prod.table_name where     a = 1 and  c = '3';
    
    #能走到a和b的索引走不到c的索引,b的范围查询使后面字段无法走索引,评级:优化差
    SELECT * FROM tz_prod.table_name where     a = 1  and b<2 and  c = '3';
    
    #能走到a的索引,评级:优化最高
    SELECT * FROM tz_prod.table_name where   a > 1  order by a;
    
    #能走到a的索引,走不到b索引 评级:优化差
    SELECT * FROM tz_prod.table_name where   a > 1  order by b;
    
    #同上,评级:优化差
    SELECT * FROM tz_prod.table_name where   a > 1  order by c;
    
    #走不到b和c的索引,最左前缀原则必须以建立索引的第一个字段作为第一个条件,评级:最差
    SELECT * FROM tz_prod.table_name where   b=2 and  c = '3';
    

    联合索引提高查询效率的原理

    MySQL会为InnoDB的每个表建立聚簇索引,如果表有索引会建立二级索引。聚簇索引以主键建立索引,如果没有主键以表中的唯一键建立,唯一键也没会以隐式的创建一个自增的列来建立。聚簇索引和二级索引都是一个b+树,b+树的特点是数据按一定顺序存在叶子节点且每页数据相连。一般情况下使用索引查询时,先查询二级索引的b+树,查到数据并拿数据中保存的主键回查聚簇索引查到所有数据。下面我们举个例子来重现这个过程。

    以下面表举例,假设表中已经存了部分数据:

    create table `user_info`(
      `id` bigint(20) NOT NULL  PRIMARY KEY,
      `name` varchar(11),
      `age` int(11),
      `phone` varchar(20),
       KEY `key_name_age` (`name`,`age`)
    )ENGINE=InnoDB DEFAULT CHARSET=utf8;
    

    InnoDB建立的聚簇索引和二级索引如下图

    聚簇索引

    二级索引

    假如我们想要查找名字为zhaoliu,年龄为30的人的信息。即name='zhaoliu',age=30

    • (1)先查二级索引,先用二分法查找发现在wangwu名字的右边
    • (2)读取右边的这页的数据到内存,二分法查到数据2个name为zhaoliu人。
    • (3)继续二分法比较age查到数据id=31
    • (4)id=31回查聚簇索引先用二分法查找发现在31右边
    • (5)读取31左边这页数据到内存,二分法查到数据并返回数据

    如果你仅仅查找id,name和age数据那么这样就用到了覆盖索引,这样就不用回查聚簇索引,在第(3)步直接返回数据即可。

    转载于:https://my.oschina.net/u/945573/blog/2985834

    展开全文
  • 联合索引 从上图可以看到: B+Tree 会优先比较 name 来确定下一步应该搜索的方向,往左还是往右。 如果 name相同的时候再比较 phone。 失效原因: 但是如果查询条件没有name,就不知道第一步应该查哪个节点,因为...
  • MYSQL-联合索引

    2019-03-18 18:28:00
    理解了 index merge 技术,我们才知道应该如何在表上建立索引。 1. 为什么会有index merge 我们的 where 中可能有多个条件(或者join)涉及到多个字段,它们之间进行 AND 或者 OR,那么此时就有可能会使用到 index ...
  • 使用联合索引需要注意的列顺序比如在使用select * from user where x=1 and y=2;的时候,应该需要建立的索引可能是 add key(x,y)如何确定索引的顺序一般经验而言可以使用select count(distinct x)/count(x) as x_...
  • 联合索引的最左匹配原则 1.最左前缀匹配原则,非常重要的原则,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配,比如 a = 3 and b = 4 and c > 5 and d = 6如何建立(a,b,c,d)...
  • MySQL如何建议优秀的索引

    万次阅读 2020-06-04 08:17:11
    (3) 最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做 (4) 列值长度较长的索引列,我们建议使用前缀索引. (5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit...
  • Mysql在什么情况下索引失效,如何避免索引失效 1、环境准备 mysql的版本 2、数据库表 下面来看各种情况: 为字段name,status,address建立复合索引 1、最左匹配法则: 如果索引了多列,要遵守这个法则。指的是查询...
  • 1. 在where 从句,group by 从句,order by 从句...3. 离散度大的列放到联合索引的前面;比如: select * from payment where staff_id = 2 and customer_id = 236; 针对上面的查询是 index(sftaff_id, custome...
  • 一、如何选择合适的列建立索引 1、在where从句,group by从句,order by从句,on从句中的列添加索引 2、索引字段越小越好(因为数据库数据存储单位是以“页”为单位的,数据存储的越多,IO也会越大) 3、离散度大的...
  • MySQL 索引

    2021-01-30 10:01:41
    那你们在创建联合索引的时候,需要做联合索引多个字段之间顺序你们是如何选择的呢? 你知道在MySQL 5.6中,对索引做了哪些优化吗? 你们创建的那么多索引,到底有没有生效,或者说你们的SQL语句有没有使用索引查询...
  • mysql中的唯一索引

    2021-01-31 16:47:14
    表的主键一般都会使用一个自增的数值(一般从0或1开始),所以更加关心的是如何建立唯一索引以及有没有必要建立唯一索引问题。   注意,别把唯一索引和主键相混淆了。 主键是索引的一种 不同行的主键和唯一索引...
  • mysql建立联合索引有最左前置原则,在建立联合索引时,根据需求,where子句中使用最频繁的一列放在最左边; 如何理解联合索引中遵守的最左前置原则? mysql默认的存储引擎是InnoDB,InnoDB使用B+树,B+树的数据项...
  • MySQL5.7~关于索引

    2021-06-19 13:58:42
    在日常开发中,经常用于计算的(充当where条件时)列中,我们会为其构建索引,根据业务场景的不同可将索引划分为主键索引、唯一索引、非空索引、联合索引(除此之外,有一些较深层次的聚簇索引、覆盖索引)。 3.建立索引...
  • MySQL索引优化 最后, 关于索引失效问题 ...3、离散度大的列放在联合索引的前面,如 如何判断列的离散度? 去重查询看列的唯一值,唯一值越多则离散度越大。 举例: 查看account.user_info表的数据结...
  • mysql索引

    2020-04-25 16:35:54
    文章目录查询结构的进化史...与 把业务逻辑字段作为主键自增主键业务逻辑的字段做主键最左前缀原则 与 模糊匹配索引优化,在建立联合索引的时候,如何安排索引内的字段顺序索引优化,如何避免回表过程何为回表索引优...
  • 八、使用联合索引进行排序和分组的注意事项 九、不可以使用索进行排序或分组的情况 ASC、DESC混用 十、索引失效总结 十一、如何建立索引 11.1 考虑索引选择性 11.2 考虑前缀索引 十二、总结 条件:一个表...
  • MySQL索引知识复习

    2021-04-03 09:58:11
    在你享受工作舒适的同时,你的危机也已经在慢慢靠近 正确的创建合适的索引才是保证数据库性能保证的...一条慢SQL查询where后面有很多的查询字段,我们该如何建立联合索引?遵循的原则:最左匹配原则>离散度高原则&
  • MySQL 数据库 索引优化

    2019-07-30 11:10:00
    现有sql语句: ①select * from tab1 where number = '5' and mode >= 1 and mode ; ②select * from ...对于①,我建立联合索引,key(number,id)但是explain的时候提示没有用到该索引,用的是主键索引。
  • Mysql索引优化

    2018-05-17 15:28:19
    如何选择合适的列建立索引1....离散度大的列放到联合索引的前面select * from payment where staff_id = 2 and customer_id = 584;由于customer_id的离散度更大,所以应该使用index(customer_id,staff_id),而不...
  • mysql给创建的外键自动建立索引吗?

    千次阅读 2014-03-13 17:36:48
    如何查看呢,其实你就查看下这个表中所有的索引列就行了。 下面我们来爱这个的介绍下这几个列是啥意思 table:这个不用说了 No_unique:是否唯一,如果唯一就是0,如果可以重复就是1 key_name:索引名字,可以...
  • MySQL索引优化+事务

    2020-02-25 17:25:47
    4.联合索引底层数据结构又是什么样的 5.MySQL索引优化最佳实践 什么是索引? 索引是帮助MySQL高效获取数据的排好序和数据结构 我们索引以及数据表中的数据存储位置在哪里? 在我们的磁盘上:sql的data文件夹下。 ...
  • mysql 索引 优化 面试

    千次阅读 2017-05-18 17:37:57
    mysql 索引 优化 面试题目: 问如何优化下面的Mysql SQL语句? select * from employee where employee.deptName in ( "departA", "departB", "departC") and tbl....1: 肯定要建立二级联合索引: index( lo
  • MYSQL索引的底层原理

    2021-02-16 15:42:22
    二:InnoDB中联合索引生成的过程1:建立联合索引的一种思路:2:真正的联合索引的建立规则:3:最左前缀原则: 一:B + 树 1: 叶子结点是有指针的。 2:叶子结点是有所有元素的,非叶子结点和叶子结点元素有冗余...
  • MySQL性能优化三索引的优化 1.如何选择合适的列建立索引 1.1.在where从句、group by从句、...1.3.离散大(变量各个取值之间的差异程度)的列放到联合索引的前面,可以通过count()函数查看字段的差异值,返回值越大说
  • 一:如何建立合适的索引列 1:在where 从句,group by 从句,order by 从句,on 从句中出现的列 2:索引字段越小越好,因为字段小,索引节点就小,每个页存放的索引节点就多,然后在遍历时,IO效率据更大 3:离散...
  • 关于MySQL索引,我的整理都在这了

    千次阅读 2020-09-04 13:00:24
    联合索引的最左匹配成因?什么是最左匹配原则?索引是建立的越多越好吗? 索引 什么是索引? 索引类似于书籍的目录,想找到书中某一个内容,我们需要先去找到对应的目录,定位对应的页码,MySQL存储

空空如也

空空如也

1 2 3 4 5
收藏数 93
精华内容 37
关键字:

mysql如何建立联合索引

mysql 订阅