上海建网站费用优帮云,论坛型网站怎么做的,群晖 安装wordpress,公司网站建设招标文件范本前言 程序员避不开和数据库打交道#xff0c;大数据更是如此#xff0c;不管是 MySQL、Oracle、SQL Server 这些 OLTP 数据库#xff0c;还是Greeplum、StarRocks、Hive、Spark SQL、Flink SQL、ClickHouse 等 OLAP 数据库#xff0c;SQL 都是最基础最重要的能力#xff0…前言 程序员避不开和数据库打交道大数据更是如此不管是 MySQL、Oracle、SQL Server 这些 OLTP 数据库还是Greeplum、StarRocks、Hive、Spark SQL、Flink SQL、ClickHouse 等 OLAP 数据库SQL 都是最基础最重要的能力数据库知识也是每一个程序员必备的知识。 而当我们讨论 SQL 优化的时候其实很大程度都是在围绕索引在做优化。
1、索引
1.1、索引概述
索引是帮助 MySQL 高效获取数据的数据结构有序。下面我们以查询 age45 岁的用户为例进行说明
1.1.1、有无索引对比
1无索引
没有索引就相当于每次查找我们都需要遍历全表时间复杂度ON 2有索引 如果对 age 列建立了索引我们假设这个数据结构是一颗二叉树那么我们就不需要遍历全表时间复杂度OlogN 1.1.2、优缺点
1优点
提高数据检索的效率降低数据库 IO 成本磁盘寻址、读写数据都是 IO 成本通过索引对数据进行排序降低数据排序的成本降低 CPU 消耗
2缺点
索引列也需要占用磁盘空间索引大大提高查询效率的同时也降低了更新数据的速度当对表进行 CUD 操作时因为要对树的节点进行操作效率会降低
一般当我们考虑对一张表添加索引时这两个缺点都可以忽略。因为第一磁盘很便宜第二大部分业务系统中查询操作是最多的。
1.2、索引结构 上一节我们了解了索引是在存储引擎层实现的不同的存储引擎有不同的结构主要有以下几种 B Tree(面试重点) 最常见的索引类型大部分引擎都支持 B 树索引Hash 索引 底层数据结构是用哈希表实现的只有精确匹配索引列的查询才有效不支持范围查询R- Tree空间索引 空间索引是 MyISAM 引擎的一个特殊索引类型主要用于地理空间数据类型通常使用较少Full-text全文索引 是一种通过建立倒排索引快速匹配文档的方式。类似于 LucenceSolrES 1.2.1、B Tree
我们先假设使用二叉树来当做索引结构 上面从左到右分别是二叉排序树、顺序插入的二叉排序树和红黑树平衡二叉树可以明显发现
大数据量情况下层级较深检索速度慢顺序插入时会形成一个链表查询性能大大下降链表查询速度ON 要想解决大数据量情况下层级较深的缺点我们可以构造一个节点下有多个子节点的树而非只有两个子节点这就是我们说的 B Tree
B-Tree多路平衡二叉树 以一颗最大度数max-degree为55阶的 b-tree 为例每个节点最多存储 4 个key5个指针 树的度数指的是一个节点的子节点个数。 B-Tree 的演变过程
我们构建一颗 5 阶 b-tree 树每个节点最多存储 4 个 key5 个指针并先插入 4 个数字 当插入第 5 个数字时key 达到最大 4发生裂变中间的 key 上升为父节点 继续插入数字 1.2.2、BTree
以一颗最大度数为 4 的 btree 为例 可以看到 BTree 有这样的特点
所有的元素都会出现在叶子节点上面的非叶子节点只起到索引的作用而叶子节点才是存储数据的地方所有叶子节点生成一个单向链表
BTree 的演变过程
这里演示一颗 4 阶的 BTree 的演变过程首先随意插入 3 个值 此时的 key 为 3 达到最大4-1当插入第 4 个值的时候会发生分裂 可以看到当插入 3 之后此时的排列为 1 3 5 7排序后下标为 4/2 的节点会上升为父节点但同时叶子节点仍会保留它的值。 最后可以看到所有的数据都会出现在叶子节点而且叶子节点形成了一个单向链表。
1.2.3、MySQL 中的 BTree MySQL 对 BTree 进行了优化在原来 BTree 的基础上增加了一个指向相邻叶子节点的链表指针就形成了带有顺序指针的 BTree提高区间访问的性能。 1.2.4、Hash 哈希索引就是采用一定的 hash 算法将键值换算成新的 hash 值映射到对应的槽位上然后存储在哈希表中。 哈希索引的特点 只能用于对等比较in不支持范围查询between...无法利用索引完成排序因为哈希运算结果是无序的查询效率高通常一次检索就可以了效率通常要高于BTree索引毕竟哈希取值时间复杂度为O1
存储引擎支持
在 MySQL 当中只有 Memory 引擎支持 hash 索引而 InnoDB 引擎中具有自适应 hash 功能hash 索引是存储引擎根据 BTree 索引在指定条件下构建的。
1.2.5、面试题
1为什么 InnoDB 存储引擎选择使用 BTree 数据结构
回答为什么不使用二叉树、红黑树、B-Tree 以及哈希索引。
如果二叉树顺序插入会形成链表查找效率很低ON虽然可以通过红黑树来解决但是红黑树的本质也是一颗平衡二叉树大数据量情况下它又会遇到层级深的问题查询效率依然低B-Tree 叶子节点和非叶子节点都会存放数据一个叶子节点也就是一页16 KB相同数据量的情况下BTree 能够存储的 key 和 指针更多层级更少相对 hash 索引它只支持等值匹配不支持范围查询和排序操作
1.3、索引分类 在 InnoDB 存储引擎中根据索引的存储形式又可以分为下面两种 注意这两种索引都是 BTree 的结构聚集索引必须唯一所以一般用主键索引或唯一索引作为聚集索引叶子节点存放的是一行的数据而二级索引的叶子节点存放的是主键 id 聚集索引选取规则
默认主键索引就是聚集索引如果没有主键将使用第一个唯一索引UNIQUE作为聚集索引如果没有主键也没有唯一索引则 InnoDB 会自动生成一个 rowid 作为隐藏的聚集索引
查询数据流程
假设我们现在执行 SELECT * FROM user WHERE name Arm;
首先不能直接走聚集索引因为我们不知道主键信息而 name 列我们之前为它创建了二级索引所以应该走的是二级索引二级索引中我们可以根据姓名的字典序快速定位到姓名所在的叶子节点而二级索引的叶子节点存储的是主键所以我们得到了主键就可以进行回表查询去聚集索引中去查询在聚集索引中我们可以通过主键直接从叶子节点中得到整行数据 BTree 是有序的。在BTree中所有的节点包括非叶子节点和叶子节点都按照键key的字典顺序进行排序这确保了树中的数据保持有序性。 InnoDB 主键索引的 BTree 高度是多少 所以当高度为 3 的时候已经能够存储 2 千万左右的数据了。如果数据量再大我们就需要考虑分库分表了。
1.4、索引语法
语法
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);
注意
这里的索引类型中没有主键索引因为主键索引早在建表的时候就自动生成了。一次创建索引的字段可以是多个这种叫做联合索引或者组合索引否则叫做单列索引。
查看索引
SHOW INDEX FROM table_name;
字段比较多也可以在命令行在 SQL 后面增加 \G 来展示 删除索引
DROP INDEX index_name ON table_name;
示例
-- 创建普通索引
CREATE INDEX idx_student_name ON student(name);
-- 创建唯一索引
CREATE UNIQUE INDEX idx_student_phone ON student(phone);
-- 创建联合索引
CREATE INDEX idx_pro_age_sta ON student(profession,age,status);
注意
在 InnoDB 存储引擎中创建的索引默认都是 BTree 结构创建联合索引时字段的前后顺序都是有讲究的
1.5、SQL 性能分析
1.5.1、查看执行频次 SQL 的性能优化主要针对的是查询语句所以我们可以通过查看数据库各类操作CRUD的访问频率来决定是否需要进行 SQL 优化。
SHOW [SESSION|GLOBAL] STATUS
下面我们通过 show global status like Com_______; 7个下划线代表7个字符来查看当前数据库各操作的频率 当数据库查询频率占据绝大部分时我们就应该考虑进行 SQL 优化了。
1.5.2、慢查询日志 上面我们只是知道了当前数据中 select 的权重比较高具体是哪类 SQL 需要优化呢这就需要使用慢查询日志来确定对哪些 SQL 进行优化。 慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认 10 秒 的所有 SQL 语句的日志。
MySQL 的慢查询日志默认没有开启需要在 MySQL 的配置文件/etc/my.cnf中配置
首先通过下面的语句查看慢查询是否开启
SHOW VARRIABLES LIKE slow_query_log; /etc/my.cnf 修改注意是加到 mysqld 下面否则无法生效
# 开启慢查询日志开关
slow_query_log1
# 设置慢查询日志的时间单位s
long_query_time2
查看慢查询日志文件的存储位置
SHOW VARIABLES LIKE slow_query_log_file;
这样当有 SQL 的查询耗时超过 2s 时就会在这个日志文件中生成对应的记录包括查询时间锁行时间返回记录数数据库名SQL 语句
1.5.3、profile 详情 除了上面设置的慢查询日志之外还有一类 SQL 它的执行时间接近我们设置的慢查询时间我们也需要对它们进行优化。
SHOW PROFILES;
通过 having_profiling 参数可以查看当前数据库是否支持 profile 操作
SELECT have_peofiling; -- YES
默认 profiling 是关闭的我们可以通过 set 语句在 session/global 级别开启 profling
SELECT profiling; -- 查询是否开启 0: 未开启/1: 开启SET profiling 1; -- 开启 profiling
这样我们就可以清楚地看到每一条 SQL 的耗时时间 我们也可以查看特定语句的详细耗时情况
SHOW PROFILE FOR QUERY QUERY_ID; 1.5.4、explain 执行计划 EXPLAIN 或者 DESC 命令可以获取 MySQL 执行查询语句的信息包括在查询语句中表的连接情况、连接顺序、是否使用索引等。
EXPLAIN/DESC SQL; 这些字段分别代表
id 代表查询的序列号表示查询中执行 SQL 子句或者操作表的顺序id 相同则执行顺序从上到下id 不同则值越大越先执行多表查询时就会有多个 id select_type 查询类型常见的取值有 SIMPLE 简单表表示不需要使用表连接和子查询、PRIMARY 主查询也就是外层的查询、UNIONUNION 语句中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE 之后包含了子查询等。type 表示连接类型性能由好到差的连接类型依次为 NULL、system、const、eq_ref、ref、range、index、all。一般我们希望尽可能把 type 优化的更高但是一般不可能优化到 NULL因为只有不访问任何表才 type 会为 NULL访问系统表 type 才会为 system访问主键或者唯一索引时 type 为 const访问非唯一索引 type 就是 ref当使用了索引时type 是 index但是也会扫描整个索引树性能也不会比 all 高多少。possible_key 在这张表上可能用到的索引一个或多个key 实际用到的索引如果为 NULL则没有使用索引key_len 索引使用的字节数索引字段的最大可能长度并非实际使用长度在不损失精度的情况下越短越好rows 执行查询的行数在 InnoDB 表中这是一个估计值filtered 返回结果占读取行数的百分比filtered 的值越大越好extra 额外的信息
1.6、索引使用原则
1.6.1、最左前缀法则 如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是从索引最左列开始并不能跳过索引中的列。如果跳过某一列索引将部分失效跳过的字段索引失效索引列的顺序无所谓也就是说 where 后面的条件顺序无所谓不是非要按着联合索引的列的顺序。
1.6.2、范围查询
联合索引中出现范围查询范围查询右侧的列表索引失效
select * from tb_user where profession 数据科学与大数据技术 and age 30 and status 01; 比如对于上面这条 SQL where 后的三个字段 profession 、age 和 status 三个列组成了联合索引但是上面这条 SQL 中 status 的索引会失效因为前面的 age 出现了 。 这种情况也有避免的办法也就是在业务允许的情况下尽量使用 或者 。
注意对于上面的 SQL 并不是说把 age 30 和 status 01 交换位置使得范围查询右侧没有列就不会使 status 的索引失效了。因为这个顺序指的是联合索引的顺序如果联合索引为 index_profession_age_status 那么即使换了位置也没有意义因为 age 30 中有 所以 age 索引之后的 status 索引就失效了。
1.6.3、索引列运算
不要在索引列上进行运算操作否则索引将失效。
-- 截取手机号后两位
select * from student tb_user where substring(phone,10,2) 15;
1.6.4、字符串不加引号
字符串类型字段使用时不加引号索引将失效。
1.6.5、模糊匹配
如果仅仅是尾部模糊匹配索引不会失效。但如果是头部模糊匹配索引失效。
-- 会失效
SELECT * FROM student WHERE profession like %技术;
-- 不会失效
SELECT * FROM student WHERE profession like 技术%;
1.6.6、or 连接的条件 用 or 分开的条件如果 or 前面的条件中的列有索引而后面的列没有索引那么设计的索引都不会被用到。也就是说只有 or 两侧的列都有索引的时候索引才会生效。
1.6.7、数据分布影响 如果 MySQL 的优化器评估使用索引比扫描全表还慢则不使用索引。 比如我们有 100 行数据age 字段从0到99即使我们给 age 列建立的索引那么当查询过滤条件式是 age 10时MySQL 也是不会走索引的因为大部分条件都满足这个条件走索引效率更慢当过滤条件是 age 48 也是一样的当过滤条件的结果占据全表的 1/2 以下时才有可能会走索引。 所以一条 SQL 语句会不会走索引并不取决于字段有没有索引而是取决于数据的分布情况如果过滤条件后的行数比全表数据行数一般都多的话是不会走索引的包括比如 is null 或者 is not null 这种过滤条件。
1.6.8、SQL 提示 SQL 提示是优化数据库的一个重要手段。其实就是在 SQL 语句中加入一些人为的提示来达到优化操作的目的。
use index 对于 use index 这种语法MySQL 可能会听取意见但也有可能依然我行我素除非使用下面的 force index。
SELECT * FROM table_name USE INDEX(index_name) WHERE XXX;
ignore index
SELECT * FROM table_name IGNORE INDEX(index_name) WHERE XXX;
force index
SELECT * FROM table_name FORCE INDEX(index_name) WHERE XXX; 比如一张表中对于同一个字段它可能有单列索引也有可能被包含在联合索引当中那么当我们的过滤条件中包含该字段时满足最左前缀原则MySQL 就有可能走单列索引也有可能走联合索引这种情况下我们就可以通过上面的语法指定 MySQL 走哪个索引从而起到优化的作用。
1.6.9、覆盖索引 尽量使用覆盖索引查询使用了索引并且需要返回的列在该索引中全部能够找到对应的值减少 select * 。 说白了就是我们希望查询的字段都能在过滤条件中的索引列中找到而 select * 的时候因为查询的是所有字段所以很可能会有部分字段未建立索引并且不在过滤条件当中。 比如我们有一张名为 tb_user 的表主键是 id 字段联合索引index_user_pro_age_sta由三个字段组成profession、age、status那么下面的两个 SQL 的 Extra 信息是不一样的
SELECT id,profession,age FROM tb_user WHERE profession数据科学与大数据技术 AND age18 AND status 01; 这条 SQL 的 Extra 信息为using whereusing index表示查询使用了索引而且需要的数据都在索引列中能找到profession 和 age 是联合索引所以 MySQL 会创建二级索引而二级索引的叶子节点存储的正是主键所以不需要回表查询。
SELECT id,profession,age,status,name FROM tb_user WHERE profession数据科学与大数据技术 AND age18 AND status 01;
这条 SQL 的 Extra 信息为using index condition表示查询使用了索引但是因为 name 字段走二级索引idx_user_pro_age_sta是拿不到的需要去二级索引的叶子节点找到 id 再去聚集索引所以需要回表查询数据。
下面的例子是典型的覆盖索引这条 SQL 返回的字段idname通过索引 index_name 可以直接得到 name 列的数据而 id 又是这个二级索引的叶子节点所以不需要回表查询性能很高 而下面的这个 SQL 因为 gender 列无法获取所以需要回表查询性能要差一些 综上如果我们有一张表 user idusernamepasswordstatus 当 SQL 语句是
SELECT id,username,password FROM user WHERE username lidaxi;
最优的优化方案就是对 username 和 password 建立联合索引。
1.6.10、前缀索引 对于字段类型为 varchar、text 类型的字段有时候需要索引很长的字符串比如医院里的医嘱信息、查房记录等字段类型常常是 varchar(1024) 或者 text 类型这会让索引变得很大查询时浪费大量的磁盘 IO影响查询效率。所以这时候可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。
语法
-- 这里的 n 代表的是截取作为索引的字符串的长度
CREATE INDEX idx_name ON table_name(column_name(n)); 而对于这里的前缀长度 n 的选择可以参考下面 可以根据索引的选择性就相当于唯一性来决定选择性是指不重复的索引值和数据记录总数的比值索引的选择性越高那查询效率当然越高比如唯一索引的选择性为 1。计算最佳的前缀长度可以参考下面的公式
-- 即使这种选择性高但有时候需要考虑减小索引的体积选择使用前缀索引
SELECT COUNT(DISTINCT field_name) / COUNT(*) FROM table_name;SELECT COUNT(DISTINCT SUBSTRING(field_name,1,5)) / COUNT(*) FROM table_name;
1.6.11、单列索引和联合索引 在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引毕竟建立了联合索引就不需要回表查询而如果建立的是单列索引那么 MySQL 在查询多列的时候只能使用一个效率比较高的单列索引去叶子节点查询出 id然后再回表查询得到所有需要的字段的数据。
下面是一个使用 name 和 phone 建立的联合索引因为 phone 是唯一的所以联合索引肯定也是唯一的就可以创建一个唯一索引 这个二级索引的 BTree 会先按照 phone 的字典序进行排序如果相同会按照 name 再进行排序。
1.7、索引设计原则
针对数据量比较大100w且查询比较频繁的表建立索引针对常作为查询条件where、排序order by、分组group by操作的字段建立索引而且尽量建立联合索引尽量选择区分度高的列作为索引尽量建立唯一索引比如身份证号、手机号对于比较长的字符串字段建立前缀索引节省磁盘 IO尽量建立联合索引减少单列索引因为联合索引很多时候可以覆盖索引避免回表要控制索引的数量索引并不是越多越好索引越多维护代价越大影响增删改的效率而且占用磁盘空间如果索引列不能存储 NULL 值请在建表时使用 NOT NULL 进行约束。这样优化器就可以更好地确定哪个索引最有效地用于查询
总结
首先索引是一种高效获取数据的数据结构有序的索引的结构主要包括两种 BTree所有的数据都出现在叶子节点叶子节点是双向链表Hash只有 Memory 存储引擎支持这种索引结构它是一个哈希表检索性能很高只需要计算出字段的哈希值就可以直接定位到数据如果存在哈希碰撞只需要沿着链表寻找即可但是不足之处就是仅支持精确匹配不支持范围查询无法利用索引完成排序因为哈希运算结果是无序的索引的分类 索引可以分为主键索引、唯一索引、常规索引和全文索引在 InnoDB 存储引擎中根据存储格式索引又可以分为聚集索引只能存在一个一般是主键或者第一个唯一索引如果主键和唯一索引都没有MySQL 会自动生成一个隐藏的 row_id 作为聚集索引和二级索引区别在于聚集索引的叶子节点上存储的是一行的数据而二级索引的叶子节点存储的是主键索引的语法 create [unique] index xxx on xxx(xx)show index from xxxdrop index xxx on xxxSQL 性能分析 执行频次查看当前数据库中哪类SQL执行的最多、慢查询日志开启之后查询时间超过设置阈值的 SQL 会被记录、profile查看每条 SQL 语句每一阶段的执行情况、explain查看索引是否命中索引使用原则 联合索引最左前缀法则、尽量使用 或 索引失效函数运算、字符串缺失引号、前缀模糊匹配、or 两侧一侧有索引列一侧没有、MySQL 评估全表比走索引还快数据分布的影响SQL 提示当有多个索引时MySQL 会自动选择索引我们也可以给 MySQL 提示、忽略或者强制指定使用的索引覆盖索引查询返回的列在索引结构当中都已经包含了不需要回表查询前缀索引长字符串单列/联合索引推荐联合索引、很多时候避免回表查询索引设计原则哪些表需要建立索引 表数据量大100w、查询频次较高字段经常出现在 where、order by、group by 之后的索引尽量唯一索引尽量建立联合索引大文本使用前缀索引
