mysql索引优化的前篇(一)

前言:

由一个索引降维引发的思考炸弹。关于MySQL索引的好处,如果正确合理设计并且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。没有对比就没有伤害。对于没有索引的表,单表查询可能几十万数据就是瓶颈,而通常大型网站单日就可能会产生几十万甚至几百万的数据,没有索引查询会变的非常缓慢。
个人理解:
MySQL索引是一种数据结构,可以是BTREE,RTREE,或者HASH结构.
BTREE适合用于查找某范围内的数据,可以很快的从当前数据找到下条数据.
RTREE常用于查询比较接近的数据.
HASH结构则适用于随机访问的场合,查找每条数据的时间几乎相同.
显然,若要查找某个时间段的数据,用BTREE结构要比HASH结构快得多.
另外还有FULLTEXT(全文)和SPATIAL(空间)这两个索引类型

一、简单说下大概逻辑下几个常有的索引:

  • 唯一索引
与普通索引类似,不同的就是:索引列的值必须唯一,但允许有空值(注意和主键不同)。如果是组合索引,则列值的组合必须唯一,创建方法和普通索引类似。
  • 普通索引
 这是最基本的索引,它没有任何限制,比如上文中为title字段创建的索引就是一个普通索引,MyIASM中默认的BTREE类型的索引,也是我们大多数情况下用到的索引。
  • 组合索引(最左原则)
平时用的SQL查询语句一般都有比较多的限制条件,所以为了进一步榨取MySQL的效率,就要考虑建立组合索引。建立这样的组合索引,其实是相当于分别建立了下面两组组合索引

插语:

虽然索引有利于查询搜索,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件的会膨胀很快。索引只是提高效率的一个因素,如果你的MySQL有大数据量的表,就需要花时间研究建立最优秀的索引,或优化查询语句。

二、下面是一些总结以及收藏的MySQL索引的注意事项和优化方法:

下面引用一张图解释下:

1. 何时使用聚集索引或非聚集索引?

2. 索引不会包含有NULL值的列
 只要列中包含有NULL值都将不会被包含在索引中,复合索引中只要有一列含有NULL值,
 那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
3. 使用短索引
 对串列进行索引,如果可能应该指定一个前缀长度。例如,如果有一个CHAR(255)的列,
 如果在前10个或20个字符内,多数值是惟一的,那么就不要对整个列进行索引。短索引不
 仅可以提高查询速度而且可以节省磁盘空间和I/O操作
4. 索引列排序
MySQL查询只使用一个索引,因此如果where子句中已经使用了索引的话,那么order by
中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作;
尽量不要包含多个列的排序,如果需要最好给这些列创建复合索引
5. like语句操作---最左原则可使用得到
一般情况下不鼓励使用like操作,如果非使用不可,如何使用也是一个问题。like “%xxx%” 
不会使用索引而like “xxx%”可以使用索引。
6. 不要在列上进行运算--因为会使得索引失效
例如:select * from users where YEAR(adddate)<2007,将在每个行上进行运算,
这将导致索引失效而进行全表扫描,因此我们可以改成:select * from users 
where adddate<’2007-01-01′。关于这一点可以围观:一个单引号引发的MYSQL性能损失。
7、索引降维思想
这个需要在不同场景进行考虑,当使用多个单列索引的时候,因为mysql查询的时候
只能用一个索引,索引这个时候就要考虑下条件,如果不需要组合索引,那么使用单列
索引,在这些单列索引中,你要考每个条件对查询的影响,例如:一个月内,有一万个账户
,每天打出三万通话记录,可知,单用户的通话频度不高,因此,先定位phone索引集再
排除时间的搜索方式,肯定比先定时间再定账户的效率高。
==注:这是特定场景!!!具体请以explain与profiling去分析,MYSQL的执行解释器,没有这么简单。
8、组合索引最左原则
 使用组合索引的时候也需要考虑没个索引字段的排序,在查询的时候最左的字段需要是
 第一个字段,若不是,则索引不生效,索引匹配的时候会单遇到>= /<= 就会停止匹配,
 但这里并不代表>=不能使用索引

最后后总结一下:

MySQL只对一下操作符才使用索引:<,<=,=,>,>=,between,in,以及某些时候的
like(不以通配符%或_开头的情形)。而理论上每张表里面最多可创建16个索引,
但索引不是越多越好,要在合适的地方合适的场景用到,切记切记。
我的个人博客:blog.taobaoroom.cn
我的CSDN地址:https://blog.csdn.net/Fa_Ker
备注:后续内容会不断更新

Alan博客
请先登录后发表评论
  • 最新评论
  • 总共0条评论
  • 本博客使用laravel5.5 搭建 © 2016-2018 blog.taobaoroom.cn 版权所有 ICP证:粤ICP备16047280号
  • 联系邮箱:1030283554@qq.com