如何理解并正确使用 MySQL 索引(上)

本文涉及的产品
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS MySQL,高可用系列 2核4GB
RDS MySQL Serverless 高可用系列,价值2615元额度,1个月
简介: SQL数据库开发


1.概述

索引是存储引擎用于快速查找记录的一种数据结构,通过合理的使用数据库索引可以大大提高系统的访问性能,接下来主要介绍在MySql数据库中索引类型,以及如何创建出更加合理且高效的索引技巧。


注:这里主要针对的是InnoDB存储引擎的B+Tree索引数据结构


2.索引的优点

1、大大减轻了服务器需要扫描的数据量,从而提高了数据的检索速度


2、帮助服务器避免排序和临时表


3、可以将随机I/O变为顺序I/O



3.索引的创建

3.1、主键索引


ALTER TABLE 'table_name'
ADD PRIMARY KEY 'index_name' ('column');


3.2、唯一索引


ALTER TABLE 'table_name'
ADD UNIQUE 'index_name' ('column');


3.3、普通索引


ALTER TABLE 'table_name'
ADD INDEX 'index_name' ('column');


3.4、全文索引


ALTER TABLE 'table_name'
ADD FULLTEXT 'index_name' ('column');


3.5、组合索引


ALTER TABLE 'table_name'
ADD INDEX 'index_name' ('column1', 'column2', ...);



4.B+树的索引规则

创建一个测试的用户表


DROP TABLE IF EXISTS user_test;
CREATE TABLE user_test(
id int AUTO_INCREMENT PRIMARY KEY,
user_name varchar(30) NOT NULL,
sex bit(1) NOT NULL DEFAULT b'1',
city varchar(50) NOT NULL,
age int NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


创建一个组合索引:

ALTER TABLE user_test
ADD INDEX idx_user(user_name , city , age);


4.1、索引有效的查询


4.1.1、全值匹配


全值匹配指的是和索引中的所有列进行匹配,如:以上面创建的索引为例,在where条件后可同时查询(user_name,city,age)为条件的数据。


注:与where后查询条件的顺序无关,这里是很多同学容易误解的一个地方


SELECT * FROM user_test
WHERE user_name = 'feinik'
AND age = 26
AND city = '广州';


4.1.2、匹配最左前缀


匹配最左前缀是指优先匹配最左索引列,如:上面创建的索引可用于查询条件为:(user_name )、(user_name, city)、(user_name , city , age)


注:满足最左前缀查询条件的顺序与索引列的顺序无关,如:(city, user_name)、(age, city, user_name)


4.1.3、匹配列前缀


指匹配列值的开头部分,如:查询用户名以feinik开头的所有用户


SELECT * FROM user_test
WHERE user_name LIKE 'feinik%';


4.1.4、匹配范围值


如:查询用户名以feinik开头的所有用户,这里使用了索引的第一列


SELECT * FROM user_test
WHERE user_name LIKE 'feinik%';


4.2、索引的限制


1、where查询条件中不包含索引列中的最左索引列,则无法使用到索引查询,如:


SELECT * FROM user_test
WHERE city = '广州';

SELECT * FROM user_test
WHERE age= 26;

SELECT * FROM user_test
WHERE city = '广州'
AND age = '26';


2、即使where的查询条件是最左索引列,也无法使用索引查询用户名以feinik结尾的用户


SELECT * FROM user_test
WHERE user_name like '%feinik';


3、如果where查询条件中有某个列的范围查询,则其右边的所有列都无法使用索引优化查询,如:


SELECT * FROM user_test
WHERE user_name = 'feinik'
AND city LIKE '广州%'
AND age = 26;

相关实践学习
每个IT人都想学的“Web应用上云经典架构”实战
本实验从Web应用上云这个最基本的、最普遍的需求出发,帮助IT从业者们通过“阿里云Web应用上云解决方案”,了解一个企业级Web应用上云的常见架构,了解如何构建一个高可用、可扩展的企业级应用架构。
MySQL数据库入门学习
本课程通过最流行的开源数据库MySQL带你了解数据库的世界。   相关的阿里云产品:云数据库RDS MySQL 版 阿里云关系型数据库RDS(Relational Database Service)是一种稳定可靠、可弹性伸缩的在线数据库服务,提供容灾、备份、恢复、迁移等方面的全套解决方案,彻底解决数据库运维的烦恼。 了解产品详情: https://www.aliyun.com/product/rds/mysql 
相关文章
|
3月前
|
存储 SQL 关系型数据库
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
mysql底层原理:索引、慢查询、 sql优化、事务、隔离级别、MVCC、redolog、undolog(图解+秒懂+史上最全)
|
3月前
|
存储 关系型数据库 MySQL
MySQL数据库索引的数据结构?
MySQL中默认使用B+tree索引,它是一种多路平衡搜索树,具有树高较低、检索速度快的特点。所有数据存储在叶子节点,非叶子节点仅作索引,且叶子节点形成双向链表,便于区间查询。
102 4
|
5月前
|
存储 关系型数据库 MySQL
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
阿里面试:MySQL 一个表最多 加几个索引? 6个?64个?还是多少?
|
7月前
|
关系型数据库 MySQL 数据库
Mysql的索引
MYSQL索引主要有 : 单列索引 , 组合索引和空间索引 , 用的比较多的就是单列索引和组合索引 , 空间索引我这边没有用到过 单列索引 : 在MYSQL数据库表的某一列上面创建的索引叫单列索引 , 单列索引又分为 ● 普通索引:MySQL中基本索引类型,没有什么限制,允许在定义索引的列中插入重复值和空值,纯粹为了查询数据更快一点。 ● 唯一索引:索引列中的值必须是唯一的,但是允许为空值 ● 主键索引:是一种特殊的唯一索引,不允许有空值 ● 全文索引: 只有在MyISAM引擎、InnoDB(5.6以后)上才能使⽤用,而且只能在CHAR,VARCHAR,TEXT类型字段上使⽤用全⽂文索引。
|
3月前
|
存储 SQL 关系型数据库
MySQL 核心知识与索引优化全解析
本文系统梳理了 MySQL 的核心知识与索引优化策略。在基础概念部分,阐述了 char 与 varchar 在存储方式和性能上的差异,以及事务的 ACID 特性、并发事务问题及对应的隔离级别(MySQL 默认 REPEATABLE READ)。 索引基础部分,详解了 InnoDB 默认的 B+tree 索引结构(多路平衡树、叶子节点存数据、双向链表支持区间查询),区分了聚簇索引(数据与索引共存,唯一)和二级索引(数据与索引分离,多个),解释了回表查询的概念及优化方法,并分析了 B+tree 作为索引结构的优势(树高低、效率稳、支持区间查询)。 索引优化部分,列出了索引创建的六大原则
|
4月前
|
存储 关系型数据库 MySQL
MySQL覆盖索引解释
总之,覆盖索引就像是图书馆中那些使得搜索变得极为迅速和简单的工具,一旦正确使用,就会让你的数据库查询飞快而轻便。让数据检索就像是读者在图书目录中以最快速度找到所需信息一样简便。这样的效率和速度,让覆盖索引成为数据库优化师傅们手中的尚方宝剑,既能够提升性能,又能够保持系统的整洁高效。
125 9
|
5月前
|
机器学习/深度学习 关系型数据库 MySQL
对比MySQL全文索引与常规索引的互异性
现在,你或许明白了这两种索引的差异,但任何技术决策都不应仅仅基于理论之上。你可以创建你的数据库实验环境,尝试不同类型的索引,看看它们如何影响性能,感受它们真实的力量。只有这样,你才能熟悉它们,掌握什么时候使用全文索引,什么时候使用常规索引,以适应复杂多变的业务需求。
113 12
|
9月前
|
存储 关系型数据库 MySQL
MySQL索引学习笔记
本文深入探讨了MySQL数据库中慢查询分析的关键概念和技术手段。
611 81
|
6月前
|
SQL 存储 关系型数据库
MySQL选错索引了怎么办?
本文探讨了MySQL中因索引选择不当导致查询性能下降的问题。通过创建包含10万行数据的表并插入数据,分析了一条简单SQL语句在不同场景下的执行情况。实验表明,当数据频繁更新时,MySQL可能因统计信息不准确而选错索引,导致全表扫描。文章深入解析了优化器判断扫描行数的机制,指出基数统计误差是主要原因,并提供了通过`analyze table`重新统计索引信息的解决方法。
139 3
|
7月前
|
自然语言处理 关系型数据库 MySQL
MySQL索引有哪些类型?
● 普通索引:最基本的索引,没有任何限制。 ● 唯一索引:索引列的值必须唯一,但可以有空值。可以创建组合索引,则列值的组合必须唯一。 ● 主键索引:是特殊的唯一索引,不可以有空值,且表中只存在一个该值。 ● 组合索引:多列值组成一个索引,用于组合搜索,效率高于索引合并。 ● 全文索引:对文本的内容进行分词,进行搜索。

热门文章

最新文章

推荐镜像

更多