一、MySQL索引规范

在使用索引之前其他的优化查询的动作纯粹是浪费时间,只有合理地使用索引之后,才有必要考虑其他优化方式。

1.1 选择唯一性索引

唯一性索引的值是唯一的,可以更快速的通过该索引来确定某条记录。

1.2 选择操作频繁的列

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

1.3 选择查询频繁的列

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

1.4 限制索引的数量

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

1.5 最左前缀匹配

  1. 对于联合索引,MySQL 会一直向右匹配直到遇到范围查询(> , < ,between,like)就停止匹配。比如 a = 3 and b = 4 and c > 5 and d = 6,如果建立的是(a,b,c,d)这种顺序的索引,那么 d 是用不到索引的。
  2. = 和 in 可以乱序,比如 a = 3 and b = 4 and c = 5 建立 (a,b,c)索引可以任意顺序。
  3. 如果建立的索引顺序是 (a,b)那么直接采用 where b = 5 这种查询条件是无法利用到索引的,这一条最能体现最左匹配的特性。

1.6 选择区分度高的列

  1. 区分度的公式是count(distinct col)/count(*),表示字段不重复的比例
  2. 比例越大我们扫描的记录数越少,唯一键的区分度是1。
  3. 一般需要join的字段我们都要求是0.1以上,即平均1条扫描10条 记录

1.7 索引列不能参与计算

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

1.8 优先扩展索引,而不是新建

比如表中已经有a的索引,现在要加(a,b)的索引,那么只需要修改原来的索引即可

1.9 索引与锁

对于InnoDB来说,索引可以让查询锁住更少的行,从而可以在并发情况下拥有更佳表现。没有索引的列会锁表。

1.10 聚簇索引

仅InnoDB支持

  1. 聚簇索引(Clustered Indexes)保证关键字的值相近的元组存储的物理位置也相同,有更好的访问性能。且一个表只能有一个聚簇索引。
  2. 字符串类型不建议使用聚簇索引,特别是随机字符串,因为它们会使系统进行大量的移动操作。
  3. 如果使用聚簇索引,最好使用AUTO_INCREMENT列作为主键,应该尽量避免使用随机的聚簇主键。

1.11 联合索引

对于复合索引:Mysql从左到右的使用索引中的字段,一个查询可以只使用索引中的一部分,但只能是最左侧部分。例如索引是key index (a,b,c)。可以支持a | a,b| a,b,c 3种组合进行查找,但不支持 b,c | a,c 进行查找 。当最左侧字段是常量引用时,索引就十分有效。

1.12 索引失效

  1. 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
  2. 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *
  3. mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
  4. is null,is not null也无法使用索引
  5. like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作。覆盖索引除外。
  6. 字符串不加单引号索引失效

二、MySQL数据库引擎

2.1 MyISAM

  1. 不支持事务,但是每次查询都是原子的;
  2. 支持表级锁,即每次操作对整个表加锁;
  3. 存储表的总行数;
  4. 一个MYISAM表有三个文件:索引文件、表结构文件、数据文件;
  5. 采用非聚集索引,索引文件的数据域存储指向数据文件的指针。辅索引与主索引基本一致,但是辅索引不用保证唯一性。

2.2 InnoDb

  1. 支持ACID的事务,支持事务的四种隔离级别;
  2. 支持行级锁及外键约束:因此可以支持写并发;
  3. 不存储总行数;
  4. 一个InnoDb引擎可能存储在一个文件空间(共享表空间,表大小不受操作系统控制,一个表可能分布在多个文件里),也有可能为多个(设置为独立表空,表大小受操作系统文件大小限制,一般为2G),受操作系统文件大小的限制;
  5. 主键索引采用聚集索引(索引的数据域存储数据文件本身),辅索引的数据域存储主键的值;因此从辅索引查找数据,需要先通过辅索引找到主键值,再访问辅索引;最好使用自增主键,防止插入数据时,为维持B+树结构,文件的大调整。

2.3 适用场景

  1. MyISAM相对简单,在效率上要优于InnoDB。适合读多写少,对原子性要求低的系统。且MyISAM恢复速度快。可直接用备份覆盖恢复。
  2. 如果系统读少写多,尤其是并发写入高的时候。InnoDB就是首选了。

三、MySQL隐式转换

当字段是INT类型,查询条件为字符串

  1. 如果字符串格式的数值,则自动转换为INT类型。
  2. 如果字符串前缀是数字,将截取前数字用来比较,
  3. 如果字符串前缀非数字,则转换为0。

当字段是字符串类型,查询条件为INT
会将查询字段转换为INT再进行比较,可能会造成全表扫描。

Q.E.D.

知识共享署名-非商业性使用-相同方式共享 4.0 国际许可协议