MySQL性能优化(二)索引

这篇具有很好参考价值的文章主要介绍了MySQL性能优化(二)索引。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

优化手段

  1. 表的索引越全越好么?
  2. 为什么不要在性别子弹常见索引?
  3. 为什么不建议使用身份证做主键?
  4. 模糊匹配like xx%,like %xx% , like %xx都不用到索引么?
  5. 为什么不建议使用select * ?

准备

create table user_innodb
(
    id int not null primary key,
    username varchar(255) null,
    gender char(1) null,
		phone char(11) null
) ENGINE=INNODB;

create table user_myisam
(
    id int not null primary key,
    username varchar(255) null,
    gender char(1) null,
		phone char(11) null
) ENGINE=myisam;

create table user_memory
(
    id int not null primary key,
    username varchar(255) null,
    gender char(1) null,
		phone char(11) null
) ENGINE=memory;

SET @i = 1;
INSERT INTO user_innodb (id, username,gender, phone)
SELECT @i := @i + 1 AS id,
       CONCAT('user', LPAD(@i, 5, '0')) AS username,
			 IF(FLOOR(RAND() * 2) = 0, '1', '0') AS gender,
       CONCAT('1', LPAD(FLOOR(RAND() * 10000000000), 10, '0')) AS phone
FROM   INFORMATION_SCHEMA.TABLES,
       INFORMATION_SCHEMA.TABLES AS t2
WHERE  @i < 5000000;

select max(id) from user_innodb

案例

-- 没有索引的查询时间
select * from user_innodb where username = 'huathy'
> OK
> 时间: 5.872s
-- 为username字段加上索引
alter table user_innodb add index idx_user_innodb_name(username);
-- 走索引的name查询时间开销
select * from user_innodb where username = 'huathy'
> OK
> 时间: 0.017s

索引的本质

数据库索引:数据库管理系统中一个排序的数据结构,加快查询效率。

  • 索引按列分类:单列索引、联合索引
  • 索引类型:normal正常、spatial、unique唯一索引(空)、主键索引(非空)、fulltext全文索引(大文本字段、对于中文需要分词效果不佳、替代ES)
  • 索引方法:B+树,hash索引
    MySQL性能优化(二)索引

索引的数据结构

  1. 二分查找的链表结构:二叉查找树。
    左子树的节点小于父节点,右子树的节点大于父节点。

    二叉树存在极端情况,当所有的节点都大于父节点的时候,二叉树会退化成为链表结构。

  2. 平衡二叉树(AVL Three)
    左右子树的深度差绝对值不能超过1。
    左左形->右旋,右右形->左旋。

MySQL性能优化(二)索引

  1. 多路平衡搜索树(B树)
    通过分裂与合并来保持平衡,这个分裂合并就是innodb页的分裂合并。
    如果键是无序的,那么存储磁盘的时候可能导致碎片。所以身份证
    MySQL性能优化(二)索引

4. B+树 加强版多路平衡查找树
所有数据存放到叶子节点,叶子节点与叶子节点之间有双向指针形成链表结构。

MySQL性能优化(二)索引
优势:

  • B树解决了AVL树一个节点没有存满数据导致深度过深的问题。
  • 扫库、扫表性能更强
  • IO次数更少。磁盘读写能力更强
  • 排序能力更强
  • 效率更加稳定

MySQL性能优化(二)索引

为什么MySQL不用红黑树来作为索引数据结构?红黑树的目的是最大深度不超过最小深度的2倍。红黑树不够平衡。不适用于磁盘数据结构。可以防止内存。

  • 节点分为红色或黑色。
  • 根节点必须是黑色。
  • 叶子节点都是黑色的NULL节点。
  • 红色节点的两个子节点都是黑色(不允许两个相邻的红色节点)。
  • 从任意节点出发,到达每个叶子节点的路径中包含相同数量的黑色节点。

5. Hash索引 时间复杂度永远是O(1)
查询快。经过hash的数据本质上是无序的。所以比较数值比较耗时。Hash碰撞不可避免。
这种索引类型是不可以在InnoDB中使用的。但是可以在其他引擎使用。比如memory引擎。

不同存储引擎中索引的实践

MyIsam (索引没有主次之分、都存放在MYI文件)

主键索引

MySQL性能优化(二)索引

其他索引

MySQL性能优化(二)索引

InnoDB(数据即索引、索引即数据)

索引和数据存放在一个文件中。其B+树的叶子节点直接存放数据。

主键索引——聚集索引

叶子节点存储数据
MySQL性能优化(二)索引

聚集索引

如果索引键值的顺序,与数据行的物理存储顺序一致,则成为聚集索引。

其他索引

叶子节点存储主键。

MySQL性能优化(二)索引
问题:为什么在二级索引上面存储的是数据的主键,而不是地址?
由于增删数据,B+树的分裂合并,地址是会改变的。
回表:查询到二级索引后,还要根据主键去表里面查询数据。图中最长的红线就是表示回表操作。

没有主键的情况?

官方回答:MySQL :: MySQL 5.7 Reference Manual :: 14.6.2.1 Clustered and Secondary Indexes

如果有主键索引,就使用主键索引。如果没有主键索引,就使用非空的唯一索引。如果没有合适的主键和唯一索引,就使用隐藏的rowID来当作索引。

// 但是我在这里查询的时候,好像提示以下错误信息:
// 1054 - Unknown column '_rowid' in 'field list'
select _rowid from test ;

这里找到了解释:https://blog.csdn.net/u011196295/article/details/88030451

当创建表时没有显示定义主键时.

  1. 首先判断表中是否有非空的整形唯一索引,如果有,则该列为主键(这时候可以使用 select _rowid from table 查询到主键列).
  2. 如果没有符合条件的则会自动创建一个6字节的主键(该主键是查不到的).

索引的创建和使用原则

索引越多越好么?

不是的。索引是会占用磁盘空间,以空间换时间。

列的离散度:count(distinct(column_name)):count(*)

gender和phone哪个离散度越高?phone离散度高。
所以不需要在离散度很低的键上面去建立索引。因为走索引会有回表操作,反而降低了性能。

联合索引的最左匹配原则

联合索引必须从第一个字段开始,不能中断。建议把查询最多的放到左侧。

alter table user_innodb add index comidx_name_phone(username,phone);

EXPLAIN select * from user_innodb t where t.phone = '13603108202' and t.username='huathy';	-- 使用索引
EXPLAIN select * from user_innodb t where t.username='huathy' and t.phone = '13603108202';	-- 使用索引
EXPLAIN select * from user_innodb t where t.username='huathy';			-- 使用索引
EXPLAIN select * from user_innodb t where t.phone = '13603108202';	-- 不使用索引

MySQL性能优化(二)索引

使用场景:
对于身份证号和考号这种必须要两个同时来检索的数据,可以使用联合索引。

冗余索引

有了上面的索引,我们是否有必要再为上面的查询建立一个这样的索引。不必要,索引冗余。

select * from user_innodb t where t.username='huathy';	
alter table user_innodb add index idx_user_innodb_name(username);

覆盖索引

如果查询的列已经包含在了用到的索引中,那么就无需回表操作。这就称为覆盖索引。覆盖索引是使用索引的一种情况。
如何判断是否使用覆盖索引:在Extra中如果是Using Index表示使用了覆盖索引。

EXPLAIN select username,phone from user_innodb t where  t.username='huathy';	-- 使用覆盖索引
EXPLAIN select username from user_innodb t where t.username='huathy' and t.phone = '13603108202';	-- 使用覆盖索引
EXPLAIN select username from user_innodb t where t.phone = '13603108202';			-- 使用覆盖索引
EXPLAIN select * from user_innodb t where t.username='huathy';	-- 不使用覆盖索引,不得不回表操作

索引条件下推(ICP)

innoDB自动开启,自动优化。
索引是在存储引擎实现的,存储引擎负责存储数据,数据的过滤、计算是在服务层实现的。如果可以根据索引查询,那么效率更高。将在本存储引擎中无法过滤的条件,先在存储引擎过滤一遍。这个动作就是索引条件下推。

如何判断是否使用了索引条件下推:在执行计划的Extra中存在Using index condition表示使用了索引条件下推。index condition全称:Index condition pushing down。

-- 创建员工表
CREATE TABLE `employees` (
 `emp_no` int(11) NOT NULL,
 `birth_date` date  NULL,
 `first_name` varchar(14) NOT NULL,
 `last_name` varchar(16) NOT NULL,
 `gender` enum('M','F') NOT NULL,
 `hire_date` date  NULL,
 PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB ;
-- 在姓、名列上加上索引
alter table employees add index idx_lastname_firstname(last_name,first_name);

-- 进行查询
EXPLAIN SELECT * FROM employees t WHERE t.last_name = 'Wu'  AND t.first_name like '%x'
-- 可以看到Extra中Using index condition表示用到了索引条件下推。

-- 查看操作开关是否开启索引条件下推
show global variables like '%optimizer_switch%';
-- index_condition_pushdown=on
-- 关闭索引条件下推
set optimizer_switch = 'index_condition_pushdown=off' 
-- 再次查看是否使用了索引条件下推
EXPLAIN SELECT * FROM employees t WHERE t.last_name = 'Wu'  AND t.first_name like '%x'
-- 可以看到返回 Using Where 表示在server层过滤

以上的查询方式,查询流程如下

  • 如果不进行索引下推的流程:
    二级索引检索数据 --回表–> 在主键索引叶子节点拿到完整记录 --> Server层过滤数据(不符合like条件的N条记录,需要server层自己过滤)
  • 进行索引下推的查询流程:二级索引检索数据 --> 过滤二级索引 Wu,x --回表–> 在主键索引叶子节点获取到完整记录 --> 返回给Server层(符合like条件的N条记录,不需要server层过滤)

建立索引的原则:

  1. 在用于where判断、order排序、join链接、group by分组字段上创建索引。
  2. 索引个数不宜过多。
  3. 区分度底的字段(列的离散度底),不需要建索引。
  4. 频繁更新的值,不要作为主键或索引。
  5. 不建议用无序的值(身份证、UUID)作为索引。会引起B+树大量结构调整,消耗计算性能。
  6. 复合索引,将离散度高的列放在前面。
  7. 常见符合索引,而不是修改单列索引。
  8. 过长的字段,创建前缀索引。

前缀索引

一些文本过长,我们只需要通过前缀来匹配,可以截取字串使用前缀索引。文本过长,占用存储空间,太短则没有区分度。这里就需要计算合适的长度。文章来源地址https://www.toymoban.com/news/detail-406924.html

-- 前缀索引:
CREATE TABLE `pre_test` (
  `content` varchar(20) DEFAULT NULL,
  KEY `pre_idx` (`content`(6))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

使用索引的原则

什么时候用不到索引

  1. 索引列上使用函数(replace、substr、concat、sum、count、avg)、表达式
  2. 字符串不加引号,出现隐式转换。
  3. like条件前面加了%。违反了最左匹配原则。当然索引条件下推的情况除外。
  4. 负向查询的情况无法确定:与优化器版本、数据库版本等相关<>、!=、not in、not exists

优化器

  1. 基于成本的优化器(MySQL采用):IO、CPU
  2. 基于规则的优化器(Oracle早期版本):

到了这里,关于MySQL性能优化(二)索引的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • RK3568平台开发系列讲解(调试篇)常见的性能优化手段

    🚀返回专栏总目录 沉淀、分享、成长#

    2023年04月11日
    浏览(80)
  • Unity 性能优化的手段(对象池、静/动态批处理、GPU实例化、垃圾回收、LOD、LightMap)【更新中】

    目录 对象池 扩容策略 收缩策略 DrawCall DrawCall的过程 为什么减少DrawCall可以实现性能优化? 减少Draw Call的方法 静态批处理(Static Batching) 动态批处理(Dynamic Batching) GPU Instancing(GPU实例化)​​​​​​​ 贴图集 垃圾回收的优化 垃圾回收的性能影响 延迟垃圾回收 避免

    2024年02月04日
    浏览(40)
  • SQL性能优化-索引

    1)索引失效 索引分为单索、复合索引。 四种创建索引方式 create index index_name on user (name); create index index_name_2 on user(id,name,email); 2)查询语句较烂 3)关联查询太多join,sql设计不合理 4)服务器问题。 explain可以模拟优化器执行 SQL 查询语句,从而知道 MySQL 是如何处理

    2024年01月21日
    浏览(46)
  • 优化索引粒度参数提升ClickHouse查询性能

    当对高基数列进行过滤查询时,总是希望尽可能跳过更多的行。否则需要处理更多数据、需要更多资源。ClickHouse缺省在MergeTree表读取8192行数据块,但我们可以在创建表时调整该 index_granularity 参数。本文通过示例说明如何调整该参数优化查询性能。 下面示例,创建表并插入

    2024年02月11日
    浏览(42)
  • PostgreSQL性能调优:优化查询和索引设计

    随着数据量的增长和业务需求的变化,数据库性能成为了许多企业关注的焦点之一。在众多的数据库管理系统中,PostgreSQL因其稳定性和可靠性而备受青睐。然而,即使是最强大的系统也需要合适的调优,以确保其能够高效地处理大规模数据和复杂查询。 本文将介绍如何在P

    2024年02月07日
    浏览(53)
  • “更新查询超时时间“——优化ES索引更新性能的方法

    “更新查询超时时间”——优化ES索引更新性能的方法 在实际运用中,Elasticsearch (ES) 索引上的数据不可避免的需要进行更新操作。而update_by_query API 是一个十分强大的ES 更新功能工具,可以应对各种复杂的更新需求。然而,在进行高负载的大数据量操作时,update_by_query 会产生

    2024年02月03日
    浏览(47)
  • 删除主表 子表外键没有索引的性能优化

    整个表147M,执行时一个CPU耗尽, buffer gets 超过1个G, 启用并行也没有用   今天开发的同事问有个表上的数据为什么删不掉?我看了一下,也就不到100000条数据,表上有外键,等了5分钟hang在那里,时间原因,我对表上的外键禁用后,瞬间删除。 现在来还原这个问题。 sys@A

    2024年02月15日
    浏览(36)
  • mysql织梦索引优化之MySQL Order By索引优化

    在一些情况下,MySQL可以直接使用索引来满足一个ORDER BY 或GROUP BY 子句而无需做额外的排序。尽管ORDER BY 不是和索引的顺序准确匹配,索引还是可以被用到,只要不用的索引部分和所有的额外的ORDER BY 字段在WHERE 子句中都被包括了。 使用索引的MySQL Order By 下列的几个查询都会

    2024年02月04日
    浏览(47)
  • MYSQL高性能索引

    正确的选择和创建索引是实现高性能查询的基础,以下是高效使用索引的方法 演示的sql 独立的列 独立的列指的是索引既不是表达式的一部分也不是函数的参数。 前缀索引 如果索引是很长的列,那么索引会变得很大,并且导致索引数层数变高。通常可以索引的部分字符,这

    2024年01月20日
    浏览(43)
  • ⑩② 【MySQL索引】详解MySQL`索引`:结构、分类、性能分析、设计及使用规则。

    个人简介:Java领域新星创作者;阿里云技术博主、星级博主、专家博主;正在Java学习的路上摸爬滚打,记录学习的过程~ 个人主页:.29.的博客 学习社区:进去逛一逛~ 索引 : 什么是索引(index) ? 索引(index)是帮助MySQL 高效获取数据的数据结构 (有序):在数据之外,数据库系统

    2024年02月05日
    浏览(50)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包