MySQL中间件（二）：索引原理以及SQL优化

一、索引与约束

1. 什么是索引和约束？

索引是一种有序的存储结构，按照单个或者多个列的值进行排序，目的是提升搜索效率。
在MySQL中主要使用 B+ 树(InnoDB引擎)来组织索引；可理解为数据库中的“目录”或“书的页码”.

约束：为了实现数据的正确性完整性，innodb本身提供以下几种约束：primary key, unique key, foregin key, default, not null;

2. 约束与索引的区别？

创建主键索引 或者 唯一索引的时候 同时创建了相应的约束；
约束是逻辑上的概念，索引是一个数据结构既包含逻辑的概念也包含物理的存储方式。

索引常常用在筛选条件的场景适合使用，eg: where|group by |order by

以下场景不要使用：

1.没有 where/group by/ order by 的场景
2.区分度不高的列
3.经常修改的列
4.表数据量少

3. 索引分类 (案例理解)

• 主键索引
  表的唯一标识，每张表只能有一个主键
  不能为空 (NOT NULL) 且唯一 (UNIQUE)
  创建主键索引时，MySQL自动建立索引（底层通常是B+树）
• 唯一索引
  保证列值唯一，但允许为空（特殊场景除外）
  不作为主键，可以有多个唯一索引
  通常用于：手机号、邮箱、身份证号等
• 普通索引
  仅加速查询速度，没有唯一性要求，key可以重复
  可以为经常用作条件查询（WHERE）的列加普通索引
• 组合索引
  一个索引包含多个列，适合多列联合查询的场景
  遵循最左前缀原则（查询时条件必须从索引的最左列开始）
• 全文索引
  用于全文搜索，适合大文本内容的搜索
  通常用于：文章、博客、产品描述等

-创建表时指定主键
CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

--或者先创建表，再加主键
ALTER TABLE users ADD PRIMARY KEY (id);
----------------------------------------

-创建唯一索引
CREATE TABLE employees (
    emp_id INT,
    email VARCHAR(100) UNIQUE,
    phone VARCHAR(20),
    PRIMARY KEY (emp_id)
);

-- 或者后期添加唯一索引
ALTER TABLE employees ADD UNIQUE (email);
----------------------------------------

-创建普通索引
CREATE TABLE articles (
    id INT PRIMARY KEY,
    title VARCHAR(200),
    content TEXT
);

-- 给 title 添加普通索引
CREATE INDEX idx_title ON articl