MySQL 索引全面解析：从数据结构到性能优化实战
目录一、索引介绍1.1 概念1.2 优缺点二、索引结构-B-Tree多路平衡查找2.1 二叉树缺点2.2 B-Tree三、索引结构-BTree3.1 BTree特点3.2 MySQL中的情况四、索引结构-hash4.1 概念4.2 特点4.3 存储引擎支持为什么InnoDB存储引擎选择使用BTree索引结构五、索引分类5.1 第一种分类5.2 第二种分类六、索引-操作语法6.1 创建索引6.2 查看索引6.3 删除索引七、索引-性能分析7.1 SQL执行频率7.2 慢查询日志7.3 profile详情7.4 explain执行计划八、索引-使用规则8.1 索引使用8.2 索引失效情况8.3 SQL提示8.4 覆盖索引8.5 前缀索引8.6 单列索引与联合索引的选择情况九、索引-设计原则一.索引介绍1.概念索引是帮助Mysql高效获取数据的数据结构有序。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用数据这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。2.优缺点二.索引结构-B-Tree多路平衡查找1.二叉树缺点顺序插入时会形成一个链表查询性能大大降低。在大数据量的情况下层级越深检索速度越慢2.B-Treen阶就有n个指针n-1个key三.索引结构-BTree1.BTree所有数据都会出现在叶子结点叶子结点构成单向链表非叶子结点起到一个索引的作用2.Mysql中的情况四.索引结构-hash1.概念2.特点1hash索引只能用于对等比较in不支持范围查找2无法利用索引完成排序操作3查询效率高通常只需要一次检索就可以3.存储引擎支持在Mysql中支持hash索引的是Memory引擎而InnoDB中有自适应的hash功能hash索引是存储引擎根据BTree索引在指定条件下自动构建的附 -引擎支持情况为什么InnoDB存储引擎选择使用BTree索引结构五.索引分类1.第一种分类2.第二种分类六.索引-操作语法1.创建索引CREATE [UNIQUE |FULLTEXT]INDEX index_name ON table_name index_col_name,…联合索引2.查看索引SHOW INDEX FROM table_name;3.删除索引DROP INDEX index_name ON table_name;七.索引-性能分析1.SQL执行频率(通过下列语句查询数据库中增删改查的访问频次)SHOW GLOBAL STATUS LIKE Com_______;(7个下划线)2.慢查询日志主要记录所有执行时间超过指定参数的所有SQL语句的日志。MySQL的慢查询日志默认未开启状态需要在MySQL的配置文件/etc/my.cnf中配置信息1查询开启情况以及时间,文件show variables like long_query_time;SHOW VARIABLES LIKE slow_query_log_file;2开启慢日志查询开关SET GLOBAL slow_query_log ON;(3)设置慢日志时间为2秒超过两秒就记录SET GLOBAL long_query_time 2;(4)设置输出方式为文件SET GLOBAL log_output FILE;3.profile详情1.概念show profiles能够在做SQL优化时帮助我们了解时间耗费在哪里。通过have_profiling参数能够看到当前MySQL是否支持profile操作SELECT have_profiling; SELECT profiling;(查看是否开启)2.开启SET profiling 1;4.explain执行计划1概念explain或者desc命令获取MySQL如何执行SELECT语句的信息包括在SELECT语句执行过程中表如何连接和连接的顺序2语法例explain各个字段的含义八.索引-使用规则1.索引使用使用大于等于或小于等于可规避该风险2.索引失效情况1对索引列进行运算2对字符串类型数据进行索引查询时没有加单引号3模糊查询时若为头部模糊匹配则索引失效4or连接时只有两侧都有索引索引才会生效、5数据分布影响若评估走全表更高效则不使用索引3.SQL提示4.覆盖索引思考usename,password两者创建联合索引效率最高5.前缀索引6.单列索引与联合索引的选择情况在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引九.索引-设计原则
)
