mysql索引学习

mysql索引的基础和类型

• 索引的原理

     要理解MySQL中索引的工作原理，最简单的方法就是去看一看一本书的索引部分：比如你想在一本书中寻找某个主题，一般会先看书的索引目录，找到对应的章节、对应的页码后就可以快速找到你想看的内容。

在MySQL中，存储引擎用类似的方法使用索引，其先在索引中查找对应的值，然后根据匹配的索引记录找到对应的数据行，最后将数据结果集返回给客户端。

• 数据库索引的位置

默认是这里 （数据库安装目录\data\某个数据库 ）中的数据库文件不像sqlserver，数据库文件都有后缀

例如：

数据文件：. myd

索引文件：. MYI

表定义文件：. fr

所以说数据库的索引和字典原理是一致的

• 索引对性能的影响

优点：

1.大大减少服务器需要扫描的数据量

2.帮助服务器避免排序和临时表

3.将随机 I/O 变顺序 I/O

4.大大提高查询速度

缺点：

5.降低插入和更新的速度（在执行写操作时，会额外操作一遍索引）

   解释：当进行插入时，先要对索引列进行一遍索引查看，判断新增的索引列值是否重复。因此造成速度变慢。

6.占用磁盘

  解释：因为添加索引后，也会生成对应的索引文件。

• 索引的使用场景

1.对于非常小的表，大部分情况下全表扫描效率会更高

2.对于中大型表来说，索引非常有效

3.特大型的表（上千万，上亿条数据），建立和使用索引的代价将会随之增长，可以使用分区技术来解决

• 索引的类型

1.普通索引（Normal）：也叫普通索引（index或key），它可以常规地提高查询效率。一张数据表中可以有多个常规索引。常规索引是使用最普遍的索引类型，如果没有明确指明索引的类型，我们所说的索引都是指常规索引。

2.唯一索引（Unique）：与普通索引类似，但是具有唯一性约束

3.主键索引（Primary Key）：特殊的唯一索引，不允许有空值

4.组合索引：将多个列组合在一起创建索引，可以覆盖多个列

5.外键索引：只有InnoDB的表才可以使用外键索引，保证数据的一致性，完整性，和实现级联操作

6.全文索引：Mysql自带的全文索引只能用于MYISAM，并且只能对英文进行全文检索

• 主键索引和唯一索引的区别：

1.主键索引一定是唯一索引，但唯一索引不一定是主键索引

2.一个表只能有一个主键索引，但可能有多个唯一索引

3.主键可以与外键构成参照完整性约束，防止数据不一致

mysql索引的创建原则

1.表的主键、外键必须有索引

2.主键尽可能选择较短的数据类型，可以有效减少磁盘的占用，提高查询效率（比如int）

3.经常与其他表进行连接的表，在连接字段上应该建立索引

4.经常出现在Where子句中的字段，特别是大表的字段，应该建立索引

5.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引

6.频繁进行数据操作的表，不要建立太多的索引

7.避免创建过多索引，索引会额外占用磁盘空间，降低写操作效率

8.对字符串进行索引，应该制定一个前缀长度，可以节省大量的索引空间

9.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替

10 在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间

11 为经常出现在关键字order by、group by、distinct后面的字段，建立索引

----复合索引建立原则：

1）复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引

2）如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引

3）如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段

mysql索引的注意事项

1. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作

2. 删除不再使用，或者很少被使用的索引。表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再被需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响

3.对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用。因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

4.like查询，%不能在前，否则索引会失效

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引

5.索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
6 索引可以使用: column is null 查询

引用：https://blog.youkuaiyun.com/m_nanle_xiaobudiu/article/details/79230125