JZTXT

009 学习笔记--索引

发布时间 2023-07-17 16:55:12作者: sunwugang
  1. 索引概述
  2. 索引结构
  3. 索引分类
  4. 索引语法
  5. SQL性能分析
  6. 索引使用
  7. 索引设计原则

索引概述：

索引(index)是帮助MySQL高效获取数据的数据结构(有序)。

在数据库之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向)数据，

这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。

无索引：效率慢，查询--全表扫描

有索引：高效(例如，二叉树)

索引优劣点：

优势　 劣势
提高数据检索的效率，降低数据库的IO成本　 索引列需要占用资源空间
通过索引列对数据进行排序，降低数据的排序成本，降低CPU的消耗 索引大大提高了查询效率，同时也降低更新表的数据，例如，对表进行INSERT、UPDATE、DELETE时，效率降低。

 

 

 

索引结构：

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的结构，主要分为：

索引结构 描述
B+Tree索引 最常见的索引类型，大部分引擎都支持B+树索引
Hash索引 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree(空间索引) 空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-txt(全文索引) 是一种通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ES

 

 

 

 

 

 索引分类：

 

注：二级索引，也被称为非聚集索引。

聚集索引选取规则：

  • 如果存在主键，主键索引就是聚集索引
  • 如果不存在主键，将使用第一个唯一索引，作为聚集索引
  • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚焦索引。

索引语法：

 

-- 查询指定表的索引信息
show INDEX from tb_users;
-- 新增索引
create index id_user_name on tb_users(name);
-- 查询指定表的索引信息
show INDEX from tb_users;

-- 添加唯一索引
-- create UNION index idx_indexname on tb_users(name);

-- 删除索引
drop index id_user_name on tb_users;

 

 注：在创建联合索引时，需注意字段的顺序

SQL性能分析：

  • SQL执行频率
    • Mysql客户端连接成功后，通过show [session|global]status命令可提供服务器状态信息。
    • 通过如下指令，可查看当前数据库的Insert、Update、Delete、Select的访问频次：show global status like 'Com_______'（下划线 7 个）

  • 慢查询日志
    • 慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位：秒，默认10秒)的所有SQL语句的日志。
    • MySQl的慢查询日志默认没有开启，需要在MySql的配置文件(/etc/my/.cnf)中配置如下信息：

    • 慢查询日志状态查询——show VARIABLES like 'slow_query_log'; 如下所示：

    •  

      配置完之后，通过以下指令重新启动MySQL服务器进行测试，查看慢日志文件中记录的信息/var/lib/mysql/localhost-slow.log 

  • profile详情
    • show profile(查看sql执行时长)能够在优化时帮助我们了解时间都耗费到哪里了。通过have_profiling参数，能够看到当前Mysql是否支持profile操作

    • -- 查看是否支持 PROFILE：select @@have_profiling; -- YES支持，如下图：

    • 注：默认profiling是关闭的，可通过set语句在session/global级别开启profiling：

    • -- 0,表示关闭，1表示打开  select @@profiling;       设置方式：  set @@profiling =1;

    •  

         

    •