mysql创建索引实操

MySQL 创建索引完整实操这份笔记主要整理 MySQL 里最常用的 4 类索引普通索引、唯一索引、主键索引、联合索引。每种索引都分成两种写法建表时直接创建索引表已经存在后面再新增索引说明下面 SQL 默认使用 MySQL 8.x字符集统一使用utf8mb4避免中文乱码。0. 准备测试表如果只是练习新增索引可以先创建一张没有索引的测试表。-- 如果 user 表已经存在先删除方便重复执行练习 SQLDROPTABLEIFEXISTSuser;-- 创建用户测试表-- 这里先不加任何索引后面用于演示“已有表新增索引”的写法CREATETABLEuser(-- 用户 ID普通整数字段idINT,-- 用户名最多 50 个字符usernameVARCHAR(50),-- 手机号国内手机号一般 11 位phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱最多 100 个字符emailVARCHAR(100))ENGINEInnoDBDEFAULTCHARSETutf8mb4;1. 普通索引 INDEX普通索引是最常见的索引只用来加速查询不限制字段值是否重复。适合场景经常用某个字段做查询条件比如经常根据手机号查询用户。1.1 建表时创建普通索引-- 如果 user 表已经存在先删除避免建表失败DROPTABLEIFEXISTSuser;-- 建表时直接给 phone 字段创建普通索引CREATETABLEuser(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 phone 字段建立普通索引索引名叫 idx_phoneINDEXidx_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4;1.2 已有表新增普通索引语法CREATEINDEX索引名ON表名(字段名);示例-- 给 phone 字段建立普通索引-- idx_phone 是索引名可以自己起名一般建议用 idx_字段名CREATEINDEXidx_phoneONuser(phone);查询示例-- 因为 phone 字段有索引所以这种按手机号精确查询的 SQL 可以利用索引加速SELECT*FROMuserWHEREphone13900001111;2. 唯一索引 UNIQUE唯一索引既可以加速查询也可以限制字段值不能重复。适合场景手机号、邮箱、身份证号、业务唯一编号等。注意MySQL 的唯一索引允许多个NULL值因为 MySQL 认为NULL表示未知多个未知值不算重复。2.1 建表时创建唯一索引-- 如果 user 表已经存在先删除方便重复执行DROPTABLEIFEXISTSuser;-- 建表时给 phone 字段创建唯一索引CREATETABLEuser(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号后面会给这个字段加唯一索引phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 phone 字段建立唯一索引索引名叫 uk_phone-- 加了这个索引后phone 不能出现重复的非 NULL 值UNIQUEuk_phone(phone))ENGINEInnoDBDEFAULTCHARSETutf8mb4;2.2 已有表新增唯一索引-- 给 phone 字段建立唯一索引-- 注意如果表里已经有重复手机号这条 SQL 会执行失败CREATEUNIQUEINDEXuk_phoneONuser(phone);唯一索引效果示例-- 插入第一条手机号为 13900001111 的数据可以成功INSERTINTOuser(id,username,phone,age,email)VALUES(1,张三,13900001111,20,zhangsanexample.com);-- 再插入一条相同手机号的数据会因为 uk_phone 唯一索引报错INSERTINTOuser(id,username,phone,age,email)VALUES(2,李四,13900001111,22,lisiexample.com);3. 主键索引 PRIMARY KEY主键索引是一种特殊的唯一索引要求字段值必须非空并且唯一。在 InnoDB 里主键索引还是聚集索引也就是说表数据会按照主键组织存储。注意点每张表只能有一个主键主键字段不能为NULL主键值不能重复MySQL 里主键约束名固定显示为PRIMARY3.1 建表时指定主键-- 如果 user 表已经存在先删除方便重复执行DROPTABLEIFEXISTSuser;-- 建表时指定 id 为主键CREATETABLEuser(-- 用户 ID设置为 NOT NULL后面作为主键idINTNOTNULL,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 指定 id 字段为主键-- 主键索引名在 MySQL 中固定显示为 PRIMARYPRIMARYKEY(id))ENGINEInnoDBDEFAULTCHARSETutf8mb4;3.2 已有表添加主键-- 给已有 user 表的 id 字段添加主键-- 注意执行前必须保证 id 字段没有 NULL并且没有重复值ALTERTABLEuserADDPRIMARYKEY(id);如果已有表的id字段可能有空值可以先改成非空-- 把 id 字段改成 NOT NULL-- 只有当表里 id 没有 NULL 时这条 SQL 才能执行成功ALTERTABLEuserMODIFYidINTNOTNULL;-- 再给 id 字段添加主键ALTERTABLEuserADDPRIMARYKEY(id);4. 联合索引联合索引也叫复合索引就是把多个字段一起建成一个索引。适合场景经常按多个字段组合查询比如经常根据username age查询用户。联合索引要重点记住最左匹配原则如果索引是(username, age)查询条件最好从username开始使用。4.1 建表时创建联合索引-- 如果 user 表已经存在先删除方便重复执行DROPTABLEIFEXISTSuser;-- 建表时创建 username 和 age 的联合索引CREATETABLEuser(-- 用户 IDidINT,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100),-- 给 username 和 age 建立联合索引-- 索引顺序是 username 在前age 在后INDEXidx_username_age(username,age))ENGINEInnoDBDEFAULTCHARSETutf8mb4;4.2 已有表新增联合索引-- 给 username 和 age 字段建立联合索引-- 字段顺序很重要这里是 username 在前age 在后CREATEINDEXidx_username_ageONuser(username,age);4.3 最左匹配原则示例假设已经有联合索引-- 创建 username age 联合索引CREATEINDEXidx_username_ageONuser(username,age);可以利用索引的查询-- 可以走索引因为用到了最左边的 username 字段SELECT*FROMuserWHEREusername张三;-- 可以走索引因为 username 和 age 都用到了而且没有跳过最左字段SELECT*FROMuserWHEREusername张三ANDage20;不适合这个联合索引的查询-- 一般不能完整利用 idx_username_age 联合索引-- 因为查询条件跳过了最左边的 username 字段只用了 ageSELECT*FROMuserWHEREage20;5. 删除索引普通索引、唯一索引、联合索引都可以用DROP INDEX删除。语法DROPINDEX索引名ON表名;示例-- 删除普通索引 idx_phoneDROPINDEXidx_phoneONuser;-- 删除唯一索引 uk_phoneDROPINDEXuk_phoneONuser;-- 删除联合索引 idx_username_ageDROPINDEXidx_username_ageONuser;主键索引的删除写法比较特殊-- 删除主键索引-- 注意如果有其他表的外键依赖这个主键可能会删除失败ALTERTABLEuserDROPPRIMARYKEY;6. 查看表中所有索引创建索引之后可以用下面的 SQL 验证是否创建成功。-- 查看 user 表里的所有索引SHOWINDEXFROMuser;-- SHOW KEYS 和 SHOW INDEX 基本等价SHOWKEYSFROMuser;常看字段说明字段名含义Key_name索引名比如PRIMARY、idx_phone、uk_phoneColumn_name索引包含的字段名Non_unique是否允许重复0表示唯一1表示可以重复Seq_in_index字段在联合索引里的顺序Index_type索引类型InnoDB 常见是BTREE7. ALTER TABLE 统一新增索引写法除了CREATE INDEX也可以统一用ALTER TABLE给已有表添加索引。-- 给 age 字段添加普通索引ALTERTABLEuserADDINDEXidx_age(age);-- 给 email 字段添加唯一索引-- 注意如果 email 已经有重复的非 NULL 值这条 SQL 会执行失败ALTERTABLEuserADDUNIQUEuk_email(email);-- 给 phone 和 age 添加联合索引-- phone 是联合索引的最左字段查询时最好优先用 phone 作为条件ALTERTABLEuserADDINDEXidx_phone_age(phone,age);8. 常用命名规范工作中建议索引名写清楚后期排查问题会省很多事。索引类型推荐前缀示例普通索引idx_idx_phone唯一索引uk_uk_phone联合索引idx_字段1_字段2idx_username_age主键索引固定为PRIMARYPRIMARY建议索引名尽量能看出对应字段比如idx_phone、idx_username_age不要给区分度太低的字段乱建索引比如性别gender经常出现在WHERE、JOIN ON、ORDER BY、GROUP BY里的字段更适合考虑建索引索引不是越多越好索引会占空间也会影响新增、修改、删除数据的速度9. 一份可直接复制练习的完整 SQL下面这段 SQL 可以直接复制到 MySQL 里执行用来完整练习“建表、插入数据、创建索引、查看索引、删除索引”。-- 删除旧表保证这份练习 SQL 可以重复执行DROPTABLEIFEXISTSuser;-- 创建用户测试表CREATETABLEuser(-- 用户 ID后面会添加主键idINTNOTNULL,-- 用户名usernameVARCHAR(50),-- 手机号phoneVARCHAR(11),-- 年龄ageINT,-- 邮箱emailVARCHAR(100))ENGINEInnoDBDEFAULTCHARSETutf8mb4;-- 插入测试数据INSERTINTOuser(id,username,phone,age,email)VALUES(1,张三,13900001111,20,zhangsanexample.com),(2,李四,13900002222,22,lisiexample.com),(3,王五,13900003333,20,wangwuexample.com);-- 给 id 添加主键索引ALTERTABLEuserADDPRIMARYKEY(id);-- 给 phone 添加普通索引CREATEINDEXidx_phoneONuser(phone);-- 给 email 添加唯一索引CREATEUNIQUEINDEXuk_emailONuser(email);-- 给 username 和 age 添加联合索引CREATEINDEXidx_username_ageONuser(username,age);-- 查看 user 表的所有索引SHOWINDEXFROMuser;-- 使用 phone 查询可以利用 idx_phone 索引SELECT*FROMuserWHEREphone13900001111;-- 使用 username age 查询可以利用 idx_username_age 联合索引SELECT*FROMuserWHEREusername张三ANDage20;-- 删除普通索引DROPINDEXidx_phoneONuser;-- 删除唯一索引DROPINDEXuk_emailONuser;-- 删除联合索引DROPINDEXidx_username_ageONuser;-- 删除主键索引ALTERTABLEuserDROPPRIMARYKEY;-- 再次查看索引确认索引已经删除SHOWINDEXFROMuser;