mysql高级篇(二)

本文详细介绍了MySQL的索引,包括索引的优势和劣势,BTree和B+Tree索引结构,以及索引分类如单值索引、唯一索引、主键索引和复合索引。此外,还探讨了何时创建索引,以及如何通过Explain分析查询性能,讲解了type字段的含义。最后,提到了单表索引优化、关联查询优化和子查询优化的策略。
摘要由CSDN通过智能技术生成

二、mysql索引

1、sql性能问题

  1. 数据过多:分库分表
  2. 关联表过多,使用太多join:sql优化
  3. 没有充分利用索引:索引建立
  4. 服务器调优及各个参数设置

2、索引简介

2.1、索引

MySQL官方对索引的定义为:索引(Index)是帮助MySQL高效获取数据的数据结构。可以得到索引的本质:索引是数据结构。也可以简单理解为“排好序的快速查找数据结构”。

数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。

优势:类似大学图书馆建书目索引,提高数据检索的效率,降低数据库的IO成本。通过索引列对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段,都会调整因为更新所带来的键值变化后的索引信息。实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占用空间的。

2.2、索引结构

BTree索引
在这里插入图片描述
【初始化介绍】
一颗b树,浅蓝色的块我们称之为一个磁盘块,可以看到每个磁盘块包含几个数据项(深蓝色所示)和指针(黄色所示),如磁盘块1包含数据项17和35,包含指针P1、P2、P3,P1表示小于17的磁盘块,P2表示在17和35之间的磁盘块,P3表示大于35的磁盘块。
真实的数据存在于叶子节点,即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据,只存储指引搜索方向的数据项,如17、35并不真实存在于数据表中。

【查找过程】
如果要查找数据项29,那么首先会把磁盘块1由磁盘加载到内存,此时发生一次IO,在内存中用二分查找确定29在17和35之间,锁定磁盘块1的P2指针,内存时间因为非常短(相比磁盘的IO)可以忽略不计,通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存,发生第二次IO,29在26和30之间,锁定磁盘块3的P2指针,通过指针加载磁盘块8到内存,发生第三次IO,同时内存中做二分查找找到29,结束查询,总计三次IO。真实的情况是,3层的b+树可以表示上百万的数据,如果上百万的数据查找只需要三次IO,性能提高将是巨大的,如果没有索引,每个数据项都要发生一次IO,那么总共需要百万次的IO,显然成本非常非常高。

B+Tree索引
在这里插入图片描述
B+Tree与BTree 的区别

  • B树的关键字和记录是放在一起的,叶子节点可以看作外部节点,不包含任何信息;B+树的非叶子节点中只有关键字和指向下一个节点的索引,记录只放在叶子节点中。

  • 在B树中,越靠近根节点的记录查找时间越快,只要找到关键字即可确定记录的存在;而B+树中每个记录的查找时间基本是一样的,都需要从根节点走到叶子节点,而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好,而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据,这样每个节点可容纳的元素个数比B-树多,树高比B树小,这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B树多,但是一次磁盘访问的时间相当于成百上千次内存比较的时间,因此实际中B+树的性能可能还会好些,而且B+树的叶子节点使用指针连接在一起,方便顺序遍历(例如查看一个目录下的所有文件,一个表中的所有记录等),这也是很多数据库和文件系统使用B+树的缘故。

因此,mysql数据库选择B+Tree来建立索引。

为什么说B+树比B树更适合实际应用中操作系统的文件索引和数据库索引?

  • B+树的磁盘读写代价更低

B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中,那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。

  • B+树的查询效率更加稳定

由于非终结点并不是最终指向文件内容的结点,而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同,导致每一个数据的查询效率相当。

聚簇索引与非聚簇索引

聚簇索引的好处:按照聚簇索引排列顺序,查询显示一定范围数据的时候,由于数据都是紧密相连,数据库不不用从多个数据块中提取数据,所以节省了大量的io操作。

聚簇索引的限制:对于mysql数据库目前只有innodb数据引擎支持聚簇索引,而Myisam并不支持聚簇索引。由于数据物理存储排序方式只能有一种,所以每个Mysql的表只能有一个聚簇索引。一般情况下就是该表的主键。为了充分利用聚簇索引的聚簇的特性,所以innodb表的主键列尽量选用有序的顺序id,而不建议用无序的id,比如uuid这种。

2.3、索引分类

基本语法

创建
CREATE  [UNIQUE ]  INDEX [indexName] ON table_name(column)) ;

删除
DROP INDEX [indexName] ON table_name; 

查看
SHOW INDEX FROM table_name;
有四种方式来添加数据表的索引:
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的(除了NULL外,NULL可能会出现多次)。

ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引,索引值可出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ,用于全文索引。

2.3.1、单值索引

即一个索引只包含单个列,一个表可以有多个单列索引

随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),  PRIMARY KEY(id),  KEY (customer_name));

单独建单值索引:
CREATE  INDEX idx_customer_name ON customer(customer_name); 

删除索引:
DROP INDEX idx_customer_name  on customer;

2.3.2、唯一索引

索引列的值必须唯一,但允许有空值

随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),  PRIMARY KEY(id), KEY (customer_name)), UNIQUE (customer_no);

单独建单值索引:
CREATE UNIQUE idx_customer_no ON customer(customer_name); 

删除索引:
DROP INDEX idx_customer_name  on customer;

2.3.3、主键索引

设定为主键后数据库会自动建立索引,innodb为聚簇索引

随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),  PRIMARY KEY(id) );

CREATE TABLE customer2 (id INT(10) UNSIGNED, customer_no VARCHAR(200), customer_name VARCHAR(200), PRIMARY KEY(id) );

单独建主键索引:
ALTER TABLE customer add PRIMARY KEY customer(customer_no); 

删除建主键索引:
ALTER TABLE customer drop PRIMARY KEY;

建主键索引:
必须先删除掉(drop)原索引,再新建(add)索引

2.3.4、复合索引

即一个索引包含多个列

随表一起建索引:
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200)
  • 1
    点赞
  • 3
    收藏
    觉得还不错? 一键收藏
  • 2
    评论
宋红康MySQL高级课件是一套以MySQL数据库为主题的高级教学材料。本课件由宋红康编写,旨在提供深入理解和学习MySQL高级特性的知识。下面是对该课件的简介: 该课件分为多个章节,内容涵盖了MySQL高级应用和技巧。首先,课程将回顾MySQL基础知识,包括数据库设计、表的创建和管理以及基本的SQL查询语句。然后,课程将深入剖析MySQL高级特性,包括存储过程、触发器、事务和并发控制等方面的知识。 对于存储过程,课程将介绍如何创建和使用存储过程,以及存储过程的参数和返回值的使用。同时,课程还会详细讲解存储过程的语法和常见应用场景,帮助学习者更好地掌握和应用存储过程。 在触发器的部分,课程将解释触发器的概念和工作原理,并且演示如何创建和使用触发器。同时,课程还将分享触发器在实际项目中的常见应用,帮助学习者理解并运用触发器。 针对事务和并发控制,课程将讲解事务的定义、特性和隔离级别,并演示如何使用事务保证数据的完整性。课程还将介绍并发控制的原理和技术,包括锁和多版本并发控制(MVCC)等,以提高数据库的并发性和性能。 此外,课程还将介绍MySQL高级优化技巧,包括索引优化、查询优化存储引擎选择等。学习者将在课程中学会如何通过合理设计和优化数据库结构来提升系统的性能和可扩展性。 总而言之,宋红康MySQL高级课件是一套系统、全面的MySQL高级教学材料,内容丰富、深入,将帮助学习者深入理解和应用MySQL高级特性,提升数据库应用开发和管理的能力。
评论 2
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值