第10章MySQL数据库索引技术详解

MySQL数据库索引技术详解课程导览01索引认知理解索引概念、原理与使用价值02索引分类掌握六种索引类型及其特点03索引创建掌握创建与查看索引的SQL语句04索引管理掌握删除索引及使用注意事项索引认知01索引的概念与查询原理索引是数据库高效查询的基石生活类比字典目录查汉字、书籍目录定位章节索引帮助快速定位目标数据定义将表中字段值按特定结构排序后独立存储与表分开存放无索引·全表扫描从第一条记录逐条遍历数据量越大，耗时越长有索引·指针定位先搜索引值，再通过指针直接定位大幅提升检索效率示例验证对学生表姓名字段建立索引，查询"李明"时先在索引中定位，再通过指针直接获取完整记录从生活类比到数据库原理——索引让查询从逐条翻找变为精准定位VS使用索引的注意事项索引不是越多越好，合适的索引才是最好的索引成本意识占用大量存储空间不能无限制创建降低写入性能影响数据插入、修改和删除筛选原则唯一性差的数据获益不多并非所有数据都适合索引优先备选字段经常用于数据过滤和排序技术要点限制索引数目索引越多维护工作量越大最左前缀原则复合索引如(省+城市)仅按城市排序时索引无效索引分类02基础索引：普通、唯一与主键三种索引约束严格程度：主键索引>唯一索引>普通索引普通索引可为空字段值允许NULL可重复无唯一性限制适用任意数据类型无关键字修饰最基本索引类型无任何限制唯一索引保证唯一性字段值不可重复允许为空可含NULL自动拒绝重复数据插入时检查核心目的避免数据重复主键索引不可为空字段值必须非NULL不可重复严格唯一特殊唯一索引使用PRIMARYKEY核心区别非空约束普通<唯一<主键——约束强度层层递进，精准匹配不同业务场景特殊索引：全文、空间与复合全文索引适用场景大段文本检索字段约束CHAR·VARCHAR·TEXT版本差异5.7前仅英文；5.7起内置ngram插件支持中文分词空间索引适用场景地理空间数据检索字段约束GEOMETRY·POINT·LINESTRING·POLYGON强制规则字段必须设置为NOTNULL，否则创建失败复合索引适用场景多字段联合查询优化核心规则遵循最左前缀原则示例索引(id,name)，仅用id可触发索引，仅用name不生效索引创建03自动创建索引与建表时创建基础索引索引创建分为两种方式：自动创建与手动创建自动创建PRIMARYKEY约束自动生成主键索引UNIQUE约束自动生成唯一索引查看方式SHOWINDEXFROM查看表索引信息建表时手动创建普通索引CREATETABLE...INDEX，默认类型BTREE唯一索引CREATETABLE...UNIQUEINDEX，Non_unique为0建表时创建主键、全文与空间索引主键索引语法CREATETABLE...PRIMARYKEY(column_name[ASC|DESC])关键约束Collation

列

A

表示升序，D

表示降序全文索引语法CREATETABLE...FULLTEXTINDEX[index_name](column_name)关键约束字段类型仅限

CHAR、VARCHAR、TEXTIndex_type

列显示为

FULLTEXT空间索引语法CREATETABLE...SPATIALINDEX[index_name](column_name)关键约束字段须为空间数据类型且

NOTNULLIndex_type

列显示为

SPATIAL建表时创建复合索引语法与定义CREATETABLEtable_name(...INDEX[index_name](column1,column2,...));多字段组合为一个索引，支持普通、唯一等任意类型示例验证stu_id+stu_name复合索引SHOWINDEX显示两条记录Seq_in_index序列号分别为

1

和

2复合索引严格遵循

最左前缀

原则——查询时只有使用第一个字段才会触发索引使用CREATEINDEX为已存在表创建索引（上）CREATE[UNIQUE|FULLTEXT|SPATIAL]INDEXindex_nameONtable_name(column_name...)普通索引CREATEINDEXindex_idONstudent8(stu_id)索引类型为

BTREE唯一索引CREATEUNIQUEINDEXindex_idONstudent9(stu_id)Non_unique

列值为

0，确保字段值不重复

注意：主键索引

不能

使用

CREATEINDEX

语句创建，需使用

ALTERTABLEVS使用CREATEINDEX为已存在表创建索引（下）三种特殊索引，一种统一验证方式全文索引CREATEFULLTEXTINDEXindex_infoONstudent10(stu_info)适用字段类型：CHAR/VARCHAR/TEXTIndex_type

标记为

FULLTEXT空间索引CREATESPATIALINDEXindex_locONstudent11(stu_loc)字段须为空间类型且

NOTNULLIndex_type

标记为

SPATIAL复合索引CREATEINDEXindex_idONstudent12(stu_id,stu_name)多字段联合索引Seq_in_index

依次为

1、2验证方式：SHOWINDEXFROMtable_name使用ALTERTABLE创建索引（上）ALTERTABLE语句创建索引的三种类型及核心区别普通索引ALTERTABLEstudent13ADDINDEXindex_id(stu_id)Non_unique

为

1无唯一约束唯一索引ALTERTABLEstudent14ADDUNIQUEINDEXindex_id(stu_id)Non_unique

为

0值不可重复主键索引ALTERTABLEstudent15ADDPRIMARYKEY(stu_id)索引名默认为

PRIMARY字段值

不允许为空主键索引即使指定其他名称也不生效，系统自动命名为

PRIMARY使用ALTERTABLE创建索引（下）创建方式主键索引支持建表时创建✓CREATEINDEX✗ALTERTABLE✓

唯一替代方式主键索引仅支持ALTERTABLE创建全文索引ALTERTABLEstudent16ADDFULLTEXTINDEXindex_info(stu_info)Index_type

为

FULLTEXT空间索引ALTERTABLEstudent17ADDSPATIALINDEXindex_loc(stu_loc)字段须为空间类型且

NOTNULL，Index_type

为

SPATIAL复合索引ALTERTABLEstudent18ADDINDEXindex_id(stu_id,stu_name)Seq_in_index

分别为

1

和

2索引管理04删除索引的两种方式无用索引会占用存储空间并降低数据修改性能，需及时删除ALTERTABLE方式ALTERTABLEtable_nameDROPINDEXindex_name需指定索引名，可通过

SHOWINDEXFROM

查询不能删除主键索引删除主键需用

ALTERTABLEtable_nameDROPPRIMARYKEYDROPINDEX方式DROPINDEXindex_nameONtable_name需指定索引名和表名——验证：删除后使用

SHOWINDEXFROM

验证，结果显示

Emptyset

表示索引已清空VS本章核心要点回顾六大核心要点，构建完整索引知识体系概念本质/类型体系/创建方式索引概念类似书籍目录，字段值排序存储，通过指针快速定位数据六种类型普通、唯一、主键、全文、空间、复合索