MySQL索引优化案例分析

MySQL中的索引分类

• 按算法来分类
  包括B+Tree、Hash两种，大多数情况下会采用B+Tree。

  
  B+Tree与HASH两种索引对比分析

• 按数据存储形式分类
  包括聚集索引、非聚集索引。聚集索引即主键，非叶子节点仅仅存储索引数据，叶子节点存储索引数据和数据块。非聚集索引的非叶子节点也是存储索引数据，叶子节点存储索引数据和数据块的指针。索引数据都是按顺序存储。

  
  对比分析聚集索引和非聚集索引

• 其他分类方式
  包括主键索引、普通索引、唯一索引、组合索引、全文索引。

MySQL中索引创建方式

CREATE TABLE table_name[col_name data type]
[unique|fulltext][index|key][index_name](col_name[length])[asc|desc]

1. unique|fulltext为可选参数，分别表示唯一索引、全文索引
2. index和key为同义词，两者作用相同，用来指定创建索引
3. col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
4. index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
5. length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度
6. asc或desc指定升序或降序的索引值存储

索引的优缺点

1. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
2. 建立索引会占用磁盘空间。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优的索引，或优化查询语句。

索引使用注意事项

使用索引时，有以下一些技巧和注意事项：

1. 索引不会包含有null值的列
   只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。
2. 使用短索引
   对字符串进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
3. 索引列排序
   查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
4. like语句操作
   一般情况下不推荐使用like操作。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
5. 不要在列上进行运算
   这将导致索引失效而进行全表扫描，例如
   SELECT * FROM table_name WHERE YEAR(column_name)<2017;
   6.不使用not in和<>操作

索引优化案例分享

在一个CMS系统中，有文章和栏目两个重要的领域模型。一个文章有一个主栏目，同时又可以有多个引用栏目。有些栏目是引用栏目，可以通过配置SQL规则，自动引用多篇文章。

列名	列类型	列注释	备注
id	int	自动ID
article_id	int	文章ID
column_id	int	栏目ID
type	tinyint	引用类型	0 -- 主栏目，1 -- 手动引用栏目， 2--规则引用栏目
status	tinyint	数据状态	0 -- 无效，1 -- 有效
in_date	datetime	插入时间
in_user	varchar(50)	插入操作人帐号
last_edit_date	datetime	最后编辑时间
last_edit_user	varchar(50)	最后编辑人帐号

这是一个多对多的关联关系表，为了保证业务的正确性，我们需要在关联键上增加唯一索引，保证数据的唯一性。同时为了数据的顺序存储，我们增加自增列id做为主键。

在业务上主要有以下两种需求：

1. 分页或不分页查询某一栏目关联的文章列表
2. 查询某一文章关联的栏目列表（不需要分页）

上述两个需求都需要连接文章表、栏目表。
以下是为此表设计的索引，一共有三个唯一索引column_article_ui(article_id, column_id, status)、联合索引article_id_column_id_type_index(article_id, column_id, type)、单列索引IDX_column_id(column_id)。

文章栏目关联关系表

查看了某一实际查询的执行计划，总体上感觉还是不错的。

image.png

但是从规范上来说，这里的索引命名不规范，同时唯一索引column_article_ui和联合索引article_id_column_id_type_index有些重叠，经过和同事确认，可以优化成两个索引, 分别是唯一索引UK_column_article(column_id, article_id)和单列索引IX_article_id(article_id)。

参考资料

• MySQL索引类型

本文由博客一文多发平台 OpenWrite 发布！