文档章节

MySQL Index

learn_more
 learn_more
发布于 2016/12/30 16:55
字数 2593
阅读 18
收藏 0

 

1、索引创建

1)查看表中的索引, show index from table; show indexes from table;

2)我们一般都不在数据库层面限制外键,因为约束太多数据库压力太大,死锁产生的概率也会变大,但是关联字段肯定是会有的,这些关联字段都必须建立索引,因为外键都是为了关联查询的,连接条件最好有索引可用。

3)经常出现在查询条件里的字段,order by, group by 的字段, 连接条件的字段 都是应该添加索引的,但是对于那些枚举值比较少的字段不要建立索引,因为索引基数太小,没什么作用。

4)字段是否为 null,对于索引的使用本身是没问题的,因为查询条件 is null 也是会使用索引的,但是会影响字段的存储空间以及索引的存储空间,因为 null 比较特殊,存储这个值需要额外开辟空间,同样索引也是一样需要额外空间。如果数据量比较大的话那么索引数据越多,也会是查询变慢,所以尽量不使用 null,也就是在字段定义时限制 not null。

5)字段是否需要 unique,unique 对索引来说是最好的,因为最高效的索引就是最大基数的索引,索引的基数最大也不过记录数,那么如果记录的字段都是unique,索引基数也是最大的,也就是为什么 主键索引会优先使用。

 

 

2、索引使用

1)保持数据完整性,优化数据访问性能

2)改进表的连接操作,对结果进行排序,简化聚合数据操作

3)如果经常使用 or 查询的,那么应该使用单列索引,因为mysql对 or 的查询会使用多个索引的并集,即一个表可能使用多个索引,但是如果经常使用 and 查询,那么建议对这些列使用复合索引,因为mysql对 and 的查询一般一个表只会使用一个索引,可想而知如果索引覆盖越大,效率越高。

 

 

3、索引失效

1)本来是数值类型,结果使用字符串(使用引号引起来)查询,索引会失效

我们系统的ID大多使用 bigInt ,所以都是数值类型,查询的时候直接使用 id = 223 这个时候肯定是会使用 Primary 索引的,但是如果使用带引号的字符串查询则会全表扫描,如 id = '223';

同样,如果本来就是字符串类型,结果用不带引号的数值去查询,也不会使用索引,其实这里都会涉及到一个数据类型隐式转换的问题,还有就是查询条件中如果有函数计算其实也是不会使用索引的。

 

2)如果mysql估计使用全表扫描要比使用索引快,则不使用索引

数据量小的时候千万不要纠结索引的问题,因为你会被困扰的,所以请记住这条,不到优化的时候不要去优化,优化是在有必要的情况才需要做的额外事情。

 

3)mysql 索引讲究“最左原则”,即建立的索引时mysql根据字段顺序依次建立子索引

这个只是在联合索引建立的时候才会出现,比如 index(name,value,sex) ,那么会有如下的三个索引

index1:name-value-sex

index2:name-value

index3:name

所以,如果查询条件中只有 value 和 sex ,那么mysql无法匹配到索引。

 

4)or 连接的字段必须都预先建立索引,否则所有索引失效

查询条件经常会使用 or 连接, 如果使用 or 连接的字段中有一个没有建立索引,那么整个表的查询就不会使用索引,如果所有列都使用了索引,那么会使用这些索引的并集,即一个表不仅仅使用一个索引。Using union(index_1,index_2.....),所以使用 or 连接的时候需要注意索引的覆盖,并且也要反驳那些说使用 or 就不会使用索引的谬论。

 

5)in 也会使用索引,合理对待 in 和 exists

所有一开始学习sql的人都会觉得使用 in 就不会使用索引,所以遇到 in 就会改为 exists , 这个说明学得太肤浅了。难道使用 exists 就会使用索引了,搞笑。首先 IN 有两种情况下使用

in1:使用常量,即 id in(1,2,3) , 这种情况无法使用exists替代,而且如果 id 上有索引的话,这个索引会被使用。

in2:使用子查询,即 id in(select id2 from ....) , 这种情况可以使用 exists 替代,但是并不是使用 exists替代in就会效率高一些,如果这个很有必要,那in岂不是没有存在的道理,那为什么网上这么多人说使用exists替代in呢,这其实和表的运行先后有关系,如下:

示例1:

示例2:

 

说明,示例1使用的是in,结果mysql是先执行子查询里面,所以第一行是表 t,但是由于 t 的student_name 没有预先建立索引,所以整个查询无法使用索引,所以对表 t 使用全表扫描;子查询执行得到结果之后,再执行主查询,很明显表 teri 使用了索引 student_id,所以说虽然是使用in但还是使用到了索引查询。

然后再说示例2,示例2使用 exists ,然而mysql是先执行主查询,即扫描表 teri,这里使用了全表扫描,然后再把扫描的结果作为子查询的条件,这个时候子查询使用了索引。

总结,in 和 exists 的区别是执行主查询、子查询的顺序,in 是先执行子查询,再执行主查询;exists是先执行主查询再执行子查询,关于索引的使用 in 是主表的使用,exists 则是字表使用,至于说 exists 的效率高于 in ,那么这个是以偏概全了,从上面的示例可以看出 in 的效率明显高于 exists。总结起来就是:

A:如果主表和子表的数量级差不多,那么 in 和 exists 相差无几

B:如果主表大于子表,那么建议使用 in,因为这样主表可以使用索引

C:如果主表小于子表,那么建议使用 exists,因为这样子表可以使用索引

D:如果可以使用连接替代以上任何情况,那么就是用连接查询,不要使用子查询。

E:Not Exists 要比 Not IN 好,是因为 Not IN 在任何情况下都不会使用索引。

 

6)like 查询如果以 '%' 开头,必然不会使用该列的索引

其实这个很好理解,无需解释。对于那些需要使用 '%'开头的查询其实建议使用在表中多建一个字段,即该字段的 reverse 值,也就是把源值reverse之后再存储到这个字段中,然后使用模糊匹配时就可以使用 % 结尾查询,同样可以使用索引。最后还建议使用搜索引擎做,比如 Lucene.

 

7)这些运算符不会使用外键索引

A:!= 否定语句最好少使用,其实可以通过 < 符号来变通使用,因为 < 是会使用索引的。

B:<> 同上,使用 < 符号来变通

C:> 同上,< 符号来变通

D:not in 可以使用 not exists 替代,对于常量同上, < 符号来变通

E:is not null 很多时候如果要建索引的话最好就是这个字段不为空,因为null需要额外一些空间存储,增大了索引的空间,但是如果你说使用 is null 就不会使用索引的话,那你就错了,他会使用索引的。

 

8)连接语句,连接顺序,条件顺序

写SQL时有些人使用 inner join,left join,using 或者是默认内连接的使用方式等,网上有很多说哪种比哪种好,可是当我使用 explain 去查看时,发现他们使用的索引都是一样的,那么应该不存在有什么快慢的,可能也有其他因素,总之使用索引与连接方式没有关系,与连接的顺序也没有关系。还有人也说把常量放在前面,条件连接放在后面,实际上也没有效果,因为mysql优化引擎会自动优化这些,所以不存在条件顺序问题,如果说顺序会影响索引的使用,那么mysql也就没有必要去寻找最好的索引了。对于另一个疑惑是,某些条件是写在 join 上呢还是写在 where上呢?个人觉得除了全连接意义是一样的外,其他连接使用这两种方式意义完全不同,所以还是根据使用的意义出发,尽量把逻辑主键的连接写在join后面用and连接。

 

9)覆盖索引

索引查询的语句中如果只包含了那个索引列那么这个索引就会被体现最大化,也就是覆盖索引。比如表主键ID都会有索引,如果这样查询

select id from t where id > num ;

比下面这个查询要快很多

select * from t where id > num ;

这也就是为什么不建议使用 * 的原因之一,其他原因比如网络流量等。从覆盖索引我们可以得到启发那就是在大数据量情况下想要数据分页,那么可以先查询出满足条件的ID,然后通过ID作为过滤条件去查询列表信息。如下分页代码:

1)select * from product limit 866613, 20 37.44秒

2)select * from product where id > =(select id from product limit 866613, 1) limit 20 0.2秒

3)select * from product a join (select id from product limit 866613, 20) b on a.ID = b.id 0.2秒

 

 

© 著作权归作者所有

learn_more
粉丝 93
博文 240
码字总数 210196
作品 0
深圳
程序员
私信 提问
在标准MySQL 5.6上查询没有使用过的索引的SQL

select distinct mysql.innodbindexstats.tablename, mysql.innodbindexstats.indexname from mysql.innodbindexstats where concat(mysql.innodbindexstats.indexname, mysql.innodbindexst......

吕兵阳
2016/02/13
113
0
多实例 mysql 数据库的巡检脚本

[root@mysql-back ~]# cat mysql_check.sh #!/bin/bash TXT=systemcheck$(date +%F-%H:%M).txt echo ' memory ' >> $TXT free -m >> $TXT echo ' disks information ' >> $TXT df -h >> $TXT......

habby
2013/11/19
1.5K
0
MySQL hints

我们可以对MySQL的对象(表、索引、触发器、自建函数、存储过程等)做注释(comment),这样做的目的是标识该对象的作用等以增强代码的可读性、方便其他同事快速读懂我们写的代码或某个数据库...

大王叫我来巡山Zd
2016/04/11
17
0
mysql强制索引和禁止某个索引

mysql强制索引和禁止某个索引 mysql强制索引和禁止某个索引 1、mysql强制使用索引:force index(索引名或者主键PRI) 例如: select * from table force index(PRI) limit 2;(强制使用主键) sel...

xiaocao13140
2018/06/15
0
0
MySQL 使用SQL提示

SQL提示(SQL HINT)是优化数据库的一种重要手段,简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的。 下面是一个使用SQL提示的例子: SELECT SQLBUFFERRESULTS * FROM …… ...

nao
2015/12/29
222
0

没有更多内容

加载失败,请刷新页面

加载更多

MBTI助你成功,让你更了解你自己

MBTI助你成功,让你更了解你自己 生活总是一个七日接着又一个七日,相信看过第七日的小伙伴,很熟悉这段开场白,人生是一个测试接着又一个测试,上学的时候测试,是为了证明你的智力,可谓从...

蛤蟆丸子
今天
55
0
Android实现App版本自动更新

现在很多的App中都会有一个检查版本的功能。例如斗鱼TV App的设置界面下: 当我们点击检查更新的时候,就会向服务器发起版本检测的请求。一般的处理方式是:服务器返回的App版本与当前手机安...

shzwork
昨天
63
0
npm 发布webpack插件 webpack-html-cdn-plugin

初始化一个项目 npm init 切换到npm源 淘宝 npm config set registry https://registry.npm.taobao.org npm npm config set registry http://registry.npmjs.org 登录 npm login 登录状态......

阿豪boy
昨天
87
0
java基础(16)递归

一.说明 递归:方法内调用自己 public static void run1(){ //递归 run1(); } 二.入门: 三.执行流程: 四.无限循环:经常用 无限递归不要轻易使用,无限递归的终点是:栈内存溢出错误 五.递...

煌sir
昨天
63
0
REST接口设计规范总结

URI格式规范 URI中尽量使用连字符”-“代替下划线”_”的使用 URI中统一使用小写字母 URI中不要包含文件(脚本)的扩展名 URI命名规范 文档(Document)类型的资源用名词(短语)单数命名 集合(Co...

Treize
昨天
69
0

没有更多内容

加载失败,请刷新页面

加载更多

返回顶部
顶部