MySql索引和索引创建策略

MySQL索引是提高查询效率的重要手段之一。建立正确的索引可以大大优化查询性能，而错误的索引设计则可能会导致查询性能下降。

一、什么是索引？

索引是对数据库表中一个或多个列的值进行排序的数据结构，通过索引可以快速定位到表中满足条件的行。可以将索引比喻成是一本书的目录，可以快速地找到需要的内容。

有两种主要的索引类型：B-Tree索引和哈希索引。B-Tree索引适合于等值查询和范围查询，而哈希索引适合于快速索引访问，但只支持等值查询。

二、索引创建策略

1.根据查询频率建立索引

索引会提高查询速度，但同时会增加写入数据的时间，因此要根据查询的频率来选择建立索引的列，必须避免在不需要的列上创建索引，这将极大程度地影响性能。

根据查询频率来设计索引的原则是：对于经常执行的查询语句，应该尽量在WHERE子句中包含所有必要的列，以便利用多个列的索引来提高查询速度。

例如，假设有一个用户表，其中用户名是经常被查询的列，那么应该为这个列建立索引。

CREATE INDEX idx_username ON user(username);

2.根据列的基数（基数表示列中唯一值的数量）建立索引

如果某个列的基数很小，则用于搜索的索引的效率可能反而比不用索引更慢。相反，如果某个列的基数很高，则用于搜索的索引的效率可能会更快。

因此，根据列的基数来建立索引的策略是：基数低的列不适合建立索引，基数高的列适合建立索引。

例如，假设用户表中有一个列代表用户的关注领域，这个列中可能只有很少的几个不同值，那么为这个列建立索引可能会影响查询效率。

3.根据LIKE查询的位置建立索引

对于LIKE %text%，如：%123%查询语句要使用全文索引。对于LIKE text%或LIKE %text，在text前面建立索引可以加快该查询的速度。

例如，假设有一个文章表，其中有一个列代表文章的标题，那么如果需要进行模糊查询，可以为这个列建立前缀索引。

CREATE INDEX idx_title ON article(title(20));

四、索引的优缺点

1.优点

（1）可以提高查询速度，对于有大量数据的表，查询性能的提升非常明显。

（2）可以加速表与表之间的关联，例如在联表查询时，如果建立了正确的索引，可以大大加快查询速度。

（3）可以加速排序，利用排序时创建的索引可以很快的查询到需要的数据，避免类似于 SELECT * FROM table ORDER BY column这样的查询，提高查询速度和效率。

2.缺点

（1）会占用磁盘空间，因为索引是一个数据结构，索引文件会占用一定的磁盘空间。

（2）会影响写入性能，因为在写入数据的时候，需要维护索引结构，所以写入性能会下降。

（3）会增加查询优化器的选择时间，MySQL的查询优化器会根据查询条件以及索引的分布情况来选择最优的查询计划，而索引的增加往往会带来更多的选择，所以会增加查询优化器的选择时间。

总之，正确的索引设计能够大大提高查询性能，但是错误的索引设计会使查询性能下降甚至崩溃，在设计索引时，需要根据不同的场景、查询特征来选择合适的建索引策略。

下面是示例说明：

1. 为用户表中的 username 列创建索引

CREATE INDEX idx_username ON user(username);

1. 为文章表中的 title 列创建前缀索引

CREATE INDEX idx_title ON article(title(20));