数据库-索引

---

索引

什么是索引 怎么创建索引/索引的产生

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针

键值+逻辑指针清单

索引的底层实现原理和优化

B+树， 经过优化的 B+树
主要是在所有的叶子结点中增加了指向下一个叶子节点的指针， 因此 InnoDB 建议为大部分表使用默认自增的主键作为主索引

什么情况下设置了索引但无法使用

1、以“%” 开头的 LIKE 语句， 模糊匹配
2、OR 语句前后没有同时使用索引
3、数据类型出现隐式转化（ 如 varchar 不加单引号的话可能会自动转换为 int 型）
可以使用多少列创建索引？
任何标准表最多可以创建 16 个索引列

索引类型

• 普通索引 (由关键字 KEY 或 INDEX 定义的索引)
• 主键索引
  在oracle中，我们创建一个主键，则同时自动创建了一个同名的唯一索引；删除主键，则主键约束和对应的唯一索引都删除了。这是我们经常见到的现象
• 唯一索引
  唯一索引，一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查
• 联合索引 组合索引是将两个或者多个字段组合起来的索引， INDEX(columnA, columnB)索引，而单独的字段允许不是唯一的值

主键和唯一索引的区别

主键是一种约束，唯一索引是一种索引，两者在本质上是不同的。

• 主键创建后一定包含一个唯一性索引，唯一性索引并不一定就是主键。
• 唯一性索引列允许空值，而主键列不允许为空值。
• 主键列在创建时，已经默认为空值 + 唯一索引了。
• 主键可以被其他表引用为外键，而唯一索引不能。
• 一个表最多只能创建一个主键，但可以创建多个唯一索引。
• 主键更适合那些不容易更改的唯一标识，如自动递增列、身份证号等。

简单描述 MySQL 中，索引，主键，唯一索引，联合索引的区别，对数据库的性能有什么影响（从读写两方面）

索引是一种特殊的文件(InnoDB 数据表上的索引是表空间的一个组成部分)， 它们包含着对数据表里所有记录的引用指针。

• 普通索引(由关键字 KEY 或 INDEX 定义的索引)的唯一任务是加快对数据的访问速度。
  普通索引允许被索引的数据列包含重复的值。如果能确定某个数据列将只包含彼此各不相同的值， 在为这个数据列创建索引的时候就应该用关键字 UNIQUE 把它定义为一个唯一索引。也就是说，
• 唯一索引，可以保证数据记录的唯一性。
• 主键， 是一种特殊的唯一索引， 在一张表中只能定义一个主键索引， 主键用于唯一标识一条记录， 使用关键字 PRIMARY KEY 来创建。
• 联合索引索，引可以覆盖多个数据列，如像 INDEX(columnA, columnB)索引，这就是联合索引

索引可以极大的提高数据的查询速度， 但是会降低插入、删除、更新表的速度， 因为在执行这些写操作时， 还要操作索引文件

索引的具体写法操作

1. 创建索引
   –创建普通索引CREATE INDEX index_name ON table_name(col_name);
   –创建唯一索引CREATE UNIQUE INDEX index_name ON table_name(col_name);–
   创建主键索引 ALTER TABLE test ADD CONSTRAINT test_PrimaryKey PRIMARY KEY (PrimaryKey);
   创建普通组合索引CREATE INDEX index_name ON table_name(col_name_1,col_name_2);
   –创建唯一组合索引CREATE UNIQUE INDEX index_name ON table_name(col_name_1,col_name_2);

2. 通过修改表结构创建索引
   ALTER TABLE table_name ADD INDEX index_name(col_name);

3. 创建表时直接指定索引
   CREATE TABLE table_name (
   ID INT NOT NULL,col_name VARCHAR (16) NOT NULL,INDEX index_name (col_name)
   );

4. 删除索引
   –直接删除索引DROP INDEX index_name ON table_name;
   –修改表结构删除索引ALTER TABLE table_name DROP INDEX index_name;

5.查看索引
show index from table_name;