21.mysql高级 - 索引与数据处理

索引与数据处理

什么是索引？

索引是一种数据结构，在插入一条记录时，它从记录中提取（建立了索引的字段的）字段值作为该数据结构的元素，该数据结构中的元素被有序组织，因此在建立了索引的字段上搜索记录时能够借助二分查找提高搜索效率；此外每个元素还有一个指向它所属记录（数据库表记录一般保存在磁盘上）的指针，因此索引与数据库表的关系可类比于字典中目录与正文的关系，且目录的篇幅（索引所占的存储空间存储空间）很小。
数据库中，常用的索引数据结构是BTree（也称B-Tree，即Balance Tree，多路平衡查找树。Binary Search Tree平衡搜索二叉树是其中的一个特例）。

建立索引之后为什么快？

索引是大文本数据的摘要，数据体积小，且能二分查找。这样我们在根据建立了索引的字段搜索时：其一，由表数据变为了索引数据（要查找的数据量显著减小）；其二，索引数据是有序组织的，搜索时间复杂度由线性的O(N)变成了O(logN)（这是很可观的，意味着线性的2^32次操作被优化成了32次操作）。

MySQL常用索引类型

• 主键索引（primary key），只能作用于一个字段（列），字段值不能为null且不能重复。
• 唯一索引（unique key），只能作用于一个字段，字段值可以为null但不能重复
• 普通索引（key），可以作用于一个或多个字段，对字段值没有限制。为一个字段建立索引时称为单值索引，为多个字段同时建立索引时称为复合索引（提取多个字段值组合而成）。

测试唯一索引的不可重复性和可为null：

索引管理

创建索引

• 创建表（DDL）时创建索引 | 1234567 | mysql> create table student ( -> id int(10) not null auto_increment, -> stuId int(32) default null, -> name varchar(100) default null, -> primary key(id), -> unique key(stuId) -> ) engine=innodb auto_increment=1 default charset=utf8; | | :—- | :—- |

• 创建索引语句：create [unique] index <index_name> on <table_name>(<col1>,<col2>...) | 123 | mysql> create index idx_name on student(name);Query OK, 0 rows affected (0.44 sec)Records: 0 Duplicates: 0 Warnings: 0 | | :—- | :—- | | | |

• 更改表结构语句：alter table <table_name> add [unique] index <index_name> on (<col1>,<col2>....) | 1234567 | mysql> drop index idx_name on student;Query OK, 0 rows affected (0.27 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> alter table student add index idx_name(name);Query OK, 0 rows affected (0.32 sec)Records: 0 Duplicates: 0 Warnings: 0 | | :—- | :—- |

• 删除索引

  drop index <index_name> on <table_name>

  查看索引

  SHOW INDEX FROM <table_name>
  

————————————————

原文链接：https://zhenganwen.top/posts/9982416a/