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]
- unique|fulltext为可选参数，分别表示唯一索引、全文索引
- index和key为同义词，两者作用相同，用来指定创建索引
- col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择
- index_name指定索引的名称，为可选参数，如果不指定，默认col_name为索引值
- length为可选参数，表示索引的长度，只有
字符串类型的字段才能指定索引长度 - asc或desc指定升序或降序的索引值存储
索引的优缺点
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。
- 建立索引会占用磁盘空间。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。
索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优的索引，或优化查询语句。
索引使用注意事项
使用索引时，有以下一些技巧和注意事项：
- 索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。 - 使用短索引
对字符串进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。 - 索引列排序
查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。 - like语句操作
一般情况下不推荐使用like操作。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。 - 不要在列上进行运算
这将导致索引失效而进行全表扫描，例如
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做为主键。
在业务上主要有以下两种需求：
- 分页或不分页查询某一栏目关联的文章列表
- 查询某一文章关联的栏目列表（不需要分页）
上述两个需求都需要连接文章表、栏目表。
以下是为此表设计的索引，一共有三个唯一索引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)。
参考资料
本文由博客一文多发平台 OpenWrite 发布！
网友评论