我们专注攀枝花网站设计 攀枝花网站制作 攀枝花网站建设
成都网站建设公司服务热线:400-028-6601

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后,网站问题一站解决

mysql索引失效or怎么优化 mysql索引为什么失效

mysql 避免索引失效

where条件==order by 条件==group by 条件 按顺序遵守 最佳左前缀法则

海州网站制作公司哪家好,找成都创新互联公司!从网页设计、网站建设、微信开发、APP开发、响应式网站等网站项目制作,到程序开发,运营维护。成都创新互联公司2013年至今到现在10年的时间,我们拥有了丰富的建站经验和运维经验,来保证我们的工作的顺利进行。专注于网站建设就选成都创新互联公司

假设创建了复合索引:a,b,c

不在索引列上做任何的操作(计算、函数、显式或隐式的类型转换),否则会导致索引失效而转向全表扫描

1、字符不加单引号会导致索引失效

name字段为varchar类型

这条sql发生了隐式的类型转换:数值==字符串。所以导致了全表扫描,索引失效

应尽量避免在 where 子句中对字段进行表达式操作,这将导致引擎放弃使用索引而进行全表扫描。如:

mysql中的范围条件有:in/not in、 like、 、BETWEEN AND ;

后面的索引失效

in会导致索引全部失效!!!

BETWEEN AND 范围条件不会导致索引失效!!!

尽量让索引列和查询列一致;减少select * 的使用

1、查询表结构

2、查询表的索引结构

联合索引:name,age,post;说明add_time字段没有添加索引

3、查看select * 的执行计划

4、查看 select name,age,pos的执行计划

5、如果select只用一部分索引

like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。

解决:可以使用 覆盖索引 来解决这个问题!

1、先查看表上的索引

id、name、age、pos 四个字段上都有索引; 注意:name是联合索引中的第一个,带头大哥!

2、查看表结构

有个add_time字段没有用到索引

3、查看执行计划

使用UNION ALL

假设创建了联合索引 x(a,b,c)

ps:like虽然也是范围查询但是区别于、,%用在最前面就只用到索引a了;%用在最后面可以用到a+b+c!

下面的sql几乎违背了上面的所有原则,索引依然全部生效。因为select是索引覆盖的,select里不包含没有建立索引的字段。因此总是用到索引的。可以看出来索引覆盖在sql优化中的作用性

Mysql高级(五) 索引失效

1.全值匹配

2.最佳左前缀法则

3.不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描

4.存储引擎不能使用索引中范围条件右边的列

5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一直)),减少select *

6.mysql在使用不等于(!=或者)的时候无法使用索引会导致全表扫描

7.is null, is not null也无法使用索引

8.like以通配符开头(‘%abc...’)mysql索引失效会变成全表扫描的操作

9.字符串不加单引号索引失效

10.少用or,用它来连接时索引会失效

mysql 如何优化无索引查询

mysql优化无索引查询:SQL CREATE TABLE test_tab (id INT,name VARCHAR(10),age INT,val VARCHAR(10)。

1、对查询进行优化,应尽量避免全表扫描,首先应考虑在where及order by涉及的列上建立索引。

2、应尽量避免在 where子句中使用!=或操作符,否则将引擎放弃使用索引而进行全表扫描。

3、应尽量避免在 where子句中对字段进行null值判断,否则将导致引擎放弃使用索引而进行全表扫描。

运行mysql安装文件:

按 Next,然后选择安装方式,有 "Typical(默认)"、"Complete(完全)"、"Custom(用户自定义)",选择第二个选项 "Custom",下一步, MySQL Server (mysql服务器), Developer Components (开发者部分), Debug Symbols (调试符号), Server data files (服务器数据文件) 默认。

改变安装路径;原路径是"C:\Program Files\MySQL\MySQL Server 5.5\",也可以修改为:"E:\Program Files\MySQL Server 5.5\"。

MySQL中使用OR连接查询条件不会让索引失效

今天听到一种说法,说OR会使索引失效,最好使用UNION代替。查了一下发现的确有些文章这么说。但总觉得没什么道理。直觉上觉得虽然or连接的查询条件不能使用联合索引,但感觉两个单独的索引是可以用的。看了一些文章,感觉一直很疑惑。

于是试了一下,发现果然如此。

这里的type项是index_merge。搜索后发现是MySQL5.0后的新技术,索引合并。index merge 技术简单说就是在用OR,AND连接的多个查询条件时,可以分别使用前后查询中的索引,然后将它们各自的结果合并交集或并集。当然具体是否使用index merge,优化器会自己选择,比如and连接时有联合索引,或干脆全表查询就很快,就没必要使用它了。

具体可以查阅文档:

还是很有意思的。

可能因为mysql这个东西太复杂,感觉sql优化都快成玄学了。网上流传了很多“奇技淫巧”,让人真假难辨。我觉得可能是过去mysql优化不是很好时,大家有一些优化的技巧,但随着mysql更新优化,现在已经成为过时甚至错误的做法。但这些说法还在流传,造成了我这样的新手的困惑。

所以看来小白要学技术,还是要踏实点多看官方文档,多亲自实践,少看博客,少道听途说。

mysql---索引优化

索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引,没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引,不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构,

第二类是innodb存储引擎使用的叫做聚簇结构(也是一种 b-tree)。 如下图:

注意:

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

有索引列参与计算的查询条件对索引不友好(甚至无法使用索引),如from_unixtime(create_time) = '2014-05-29'。

原因很简单,如何在节点中查找到对应key?如果线性扫描,则每次都需要重新计算,成本太高;如果二分查找,则需要针对from_unixtime方法确定大小关系。

因此,索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c 3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应当尽量避免这种索引,发现后立即删除。比如有一个索引(A,B),再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B),但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

    select user.* from user where login_time '2017-04-01' and age between 18 and 30;

比如想查询某个时间段内登录过的用户:它有两个范围条件,login_time列和age列,MySQL可以使用login_time列的索引或者age列的索引,但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

如果一个索引包含或者说覆盖所有需要查询的字段的值,那么就没有必要再回表查询,这就称为覆盖索引。覆盖索引是非常有用的工具,可以极大的提高性能,因为查询只需要扫描索引会带来许多好处:

1.索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量2.索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如,用性别作索引,那么索引仅能将1000w行数据划分为两部分(如500w男,500w女),索引几乎无效。

区分度的公式是count(distinct ) / count(*),表示字段不重复的比例,比例越大区分度越好。唯一键的区分度是1,而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数),选择性越高索引的效果越好、价值越大,唯一索引的选择性最高;

组合索引中字段的顺序,选择性越高的字段排在最前面;

where条件中包含两个选择性高的字段时,可以考虑分别创建索引,引擎会同时使用两个索引(在OR条件下,应该说必须分开建索引);

不要重复创建彼此有包含关系的索引,如index1(a,b,c) 、index2(a,b)、index3(a);

组合索引的字段不要过多,如果超过4个字段,一般需要考虑拆分成多个单列索引或更为简单的组合索引;

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销,对于插入、删除、更新操作也会增加处理上的开销,而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。

mysql 索引失效的原因有哪些

mysql 索引失效的原因有哪些?Mysql索引失效的原因

1、最佳左前缀原则——如果索引了多列,要遵守最左前缀原则。指的是查询要从索引的最左前列开始并且不跳过索引中的列。

2、不在索引列上做任何操作(计算,函数,(自动或者手动)类型装换),会导致索引失效而导致全表扫描。

3、存储引擎不能使用索引中范围条件右边的列,范围之后索引失效。( , between and)。这写条件判断最后放到后面,先定位到小的范围再开始。

4、mysql使用不等于(!= 或者)的时候,无法使用索引,会导致索引失效

5、mysql中使用is not null 或者 is null会导致无法使用索引。

6、mysql中like查询是以%开头,索引会失效变成全表扫描,覆盖索引。

7、mysql中,如果条件中有or,即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)。要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引。

8、如果mysql使用全表扫描要比使用索引快,则不会使用到索引。

相关mysql调优链接:Mysql调优


分享题目:mysql索引失效or怎么优化 mysql索引为什么失效
文章源于:http://shouzuofang.com/article/hpdech.html

其他资讯