Mysql优化之索引(一)

本文涉及的产品
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS MySQL Serverless 基础系列,0.5-2RCU 50GB
云数据库 RDS PostgreSQL,集群系列 2核4GB
简介: Mysql优化之索引

一、索引


1.1索引的概念

●索引是一个排序的列表,在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址(类似于C语言的链表通过指针指向数据记录的内存地址)。

●使用索引后可以不用扫描全表来定位某行的数据,而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据,因此能加快数据库的查询速度。

●索引就好比是一本书的目录,可以根据目录中的页码快速找到所需的内容。

●索引是表中一列或者若干列值排序的方法。

●建立索引的目的是加快对表中记录的查找或排序。


1.2索引的作用与副作用

索引的作用:

●设置了合适的索引之后,数据库利用各种快速定位技术,能够大大加快查询速度,这是创建索引的最主要的原因。

●当表很大或查询涉及到多个表时,使用索引可以成千上万倍地提高查询速度。

●可以降低数据库的IO成本,并且索引还可以降低数据库的排序成本。

●通过创建唯一性索引,可以保证数据表中每一行数据的唯一性。

●可以加快表与表之间的连接。

●在使用分组和排序时,可大大减少分组和排序的时间。


索引的副作用:

●索引需要占用额外的磁盘空间。

对于 MyISAM 引擎而言,索引文件和数据文件是分离的,索引文件用于保存数据记录的地址。

而 InnoDB 引擎的表数据文件本身就是索引文件。

●在插入和修改数据时要花费更多的时间,因为索引也要随之变动。


1.3创建索引的原则依据

索引随可以提升数据库查询的速度,但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源,在有索引的情况下,数据库会先进行索引查询,然后定位到具体的数据行,如果索引使用不当,反而会增加数据库的负担。


●表的主键、外键必须有索引。因为主键具有唯一性,外键关联的是子表的主键,查询时可以快速定位。

●记录数超过300行的表应该有索引。如果没有索引,需要把表遍历一遍,会严重影响数据库的性能。

●经常与其他表进行连接的表,在连接字段上应该建立索引。

●唯一性太差的字段不适合建立索引。

●更新太频繁地字段不适合创建索引。

●经常出现在 where 子句中的字段,特别是大表的字段,应该建立索引。

●索引应该建在选择性高的字段上。

●索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建索引。


二、索引的分类和创建


准备数据库,数据表及数据


mysql -u root -p #进入
create database ceshi;#创建数据库
use ceshi;#进入库中
create table ky19 (id int(10),name varchar(10),cardid varchar(18),phone varchar(11),address varchar(50),remark text);#创建表
desc member;#查看表结构
insert into member values (1,'zhangsan','123','111111','nanjing','this is vip');
#向表中添加数据
insert into member values (4,'lisi','1234','444444','nanjing','this is normal');
insert into member values (2,'wangwu','12345','222222','benjing','this is normal');
insert into member values (5,'zhaoliu','123456','555555','nanjing','this is vip');
insert into member values (3,'qianqi','1234567','333333','shanghai','this is vip');
select * from member;#查看表中的数据





2.1普通索引

最基本的索引类型,没有唯一性之类的限制。


2.1.1直接创建索引

直接创建索引
create index 索引名 on 表名(列名(列名[length]);
#(列名(length)):length是可选项,下同。如果忽略 length 的值,则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引,这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾


示例:


create index phone_index on member (phone);#创建普通索引
select phone from member;#使用索引
show create table member;#查看表结构


2.1.2以修改表方式的创建

alter table 表名 add index 索引名 (列名);
例:alter table weibo add index id_index (id);
#利用alter命令扩增表结构,给id字段添加普通索引
select id from weibo;
select id,name from weibo;



2.1.3创建表的时候指定索引

create table 表名 ( 字段1 数据类型,字段2 数据类型[,...],index 索引名 (列名));


例:


create table weibo2(id int(4) not null,name varchar(10) not null,cardid varchar(18) not null,index id_index (id));#从创建表的时候直接指定索引
show create table test;#查看


2.2唯一索引

与普通索引类似,但区别是唯一索引列的每个值都唯一。

唯一索引允许有空值(注意和主键不同)。如果是用组合索引创建,则列值的组合必须唯一。添加唯一键将自动创建唯一索引。


2.2.1创建唯一索引

CREATE UNIQUE INDEX 索引名 ON 表名(列名);


例:


select * from member;
create unique index address_index on member (address);
create unique index name_index on member (name); 
show create table member;


2.2.2修改表方式创建

alter table 表名 add unique 索引名 (列名);


alter table member add unique cardid_index (cardid);


2.2.3创建表的时候指定

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));


create table weibo4 (id int,name varchar(20),unique id_index (id));
show creat table weibo4;
create table test2 (id int,name varchar(40),age int(5),primary key (id));


2.3主键索引

是一种特殊的唯一索引,必须指定为“PRIMARY KEY”。

一个表只能有一个主键,不允许有空值。 添加主键将自动创建主键索引。


2.3.1创建表的时候指定

CREATE TABLE 表名 ([...],PRIMARY KEY (列名));
例:
create table weibo6 (id int primary key,name varchar(20));
create table weibo7 (id int,name varchar(20),primary key (id));
show create table weibo6;
show create table weibo7;




2.3.2修改表方式创建

alter table 表名 add primary key (列名);



2.4组合索引(单列索引与多列索引)

可以是单列上创建的索引,也可以是在多列上创建的索引。需要满足最左原则,因为 select 语句的 where 条件是依次从左往右执行的,所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致,否则索引将不会生效。


CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';
例:create table amd1 (id int not null,name varchar(20),cardid varchar(20),index index_amd (id,name));
show create table amd1;
insert into amd1 values(1,'zhangsan','123123');
select * from amd1 where name='zhangsan' and id=1;




组合索引创建的字段顺序是其触发索引的查询顺序
例如:
--+
| test3 | CREATE TABLE "test3" (
  "id" int(11) NOT NULL,
  "name" varchar(50) DEFAULT NULL,
  "age" int(5) DEFAULT NULL,
  KEY "index_idname" ("id","name")
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
对以上的表进行select 
select id,name from test3;      #会触发组合索引
而:
select name,id from test3;      #按照索引从左到右检索的顺序,则不会触发组合索引


相关实践学习
如何在云端创建MySQL数据库
开始实验后,系统会自动创建一台自建MySQL的 源数据库 ECS 实例和一台 目标数据库 RDS。
全面了解阿里云能为你做什么
阿里云在全球各地部署高效节能的绿色数据中心,利用清洁计算为万物互联的新世界提供源源不断的能源动力,目前开服的区域包括中国(华北、华东、华南、香港)、新加坡、美国(美东、美西)、欧洲、中东、澳大利亚、日本。目前阿里云的产品涵盖弹性计算、数据库、存储与CDN、分析与搜索、云通信、网络、管理与监控、应用服务、互联网中间件、移动服务、视频服务等。通过本课程,来了解阿里云能够为你的业务带来哪些帮助     相关的阿里云产品:云服务器ECS 云服务器 ECS(Elastic Compute Service)是一种弹性可伸缩的计算服务,助您降低 IT 成本,提升运维效率,使您更专注于核心业务创新。产品详情: https://www.aliyun.com/product/ecs
相关文章
|
17天前
|
存储 关系型数据库 MySQL
阿里面试:为什么要索引?什么是MySQL索引?底层结构是什么?
尼恩是一位资深架构师,他在自己的读者交流群中分享了关于MySQL索引的重要知识点。索引是帮助MySQL高效获取数据的数据结构,主要作用包括显著提升查询速度、降低磁盘I/O次数、优化排序与分组操作以及提升复杂查询的性能。MySQL支持多种索引类型,如主键索引、唯一索引、普通索引、全文索引和空间数据索引。索引的底层数据结构主要是B+树,它能够有效支持范围查询和顺序遍历,同时保持高效的插入、删除和查找性能。尼恩还强调了索引的优缺点,并提供了多个面试题及其解答,帮助读者在面试中脱颖而出。相关资料可在公众号【技术自由圈】获取。
|
4天前
|
缓存 监控 关系型数据库
如何优化MySQL查询速度?
如何优化MySQL查询速度?【10月更文挑战第31天】
17 3
|
7天前
|
缓存 关系型数据库 MySQL
如何优化 MySQL 数据库的性能?
【10月更文挑战第28天】
25 1
|
14天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:百万级数据统计优化实践
【10月更文挑战第21天】 在处理大规模数据集时,传统的单体数据库解决方案往往力不从心。MySQL和Redis的组合提供了一种高效的解决方案,通过将数据库操作与高速缓存相结合,可以显著提升数据处理的性能。本文将分享一次实际的优化案例,探讨如何利用MySQL和Redis共同实现百万级数据统计的优化。
43 9
|
14天前
|
NoSQL 关系型数据库 MySQL
MySQL与Redis协同作战:优化百万数据查询的实战经验
【10月更文挑战第13天】 在处理大规模数据集时,传统的关系型数据库如MySQL可能会遇到性能瓶颈。为了提升数据处理的效率,我们可以结合使用MySQL和Redis,利用两者的优势来优化数据查询。本文将分享一次实战经验,探讨如何通过MySQL与Redis的协同工作来优化百万级数据统计。
39 5
|
19天前
|
存储 关系型数据库 MySQL
如何在MySQL中进行索引的创建和管理?
【10月更文挑战第16天】如何在MySQL中进行索引的创建和管理?
42 1
|
19天前
|
存储 关系型数据库 MySQL
优化 MySQL 的锁机制以提高并发性能
【10月更文挑战第16天】优化 MySQL 锁机制需要综合考虑多个因素,根据具体的应用场景和需求进行针对性的调整。通过不断地优化和改进,可以提高数据库的并发性能,提升系统的整体效率。
25 1
|
8天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第27天】本文深入探讨了MySQL的索引策略和查询性能调优技巧。通过介绍B-Tree索引、哈希索引和全文索引等不同类型,以及如何创建和维护索引,结合实战案例分析查询执行计划,帮助读者掌握提升查询性能的方法。定期优化索引和调整查询语句是提高数据库性能的关键。
43 0
|
9天前
|
监控 关系型数据库 MySQL
数据库优化:MySQL索引策略与查询性能调优实战
【10月更文挑战第26天】数据库作为现代应用系统的核心组件,其性能优化至关重要。本文主要探讨MySQL的索引策略与查询性能调优。通过合理创建索引(如B-Tree、复合索引)和优化查询语句(如使用EXPLAIN、优化分页查询),可以显著提升数据库的响应速度和稳定性。实践中还需定期审查慢查询日志,持续优化性能。
38 0
|
18天前
|
存储 监控 关系型数据库
MySQL并发控制与管理:优化数据库性能的关键
【10月更文挑战第17天】MySQL并发控制与管理:优化数据库性能的关键
79 0