索引-事务-引擎-优化（MySQL高级）

z小郑敲哥们儿

于 2024-08-10 11:46:36 发布

阅读量905

点赞数 15

分类专栏： MySQL 文章标签： mysql 数据库

本文链接：https://blog.csdn.net/m0_67931795/article/details/140917266

版权

MySQL 专栏收录该内容

9 篇文章 0 订阅

订阅专栏

1.阐述

一、索引

1. 定义与作用

索引是数据库中用于提高数据检索效率的数据结构，通常基于表中的一个或多个列创建。它可以大大加快数据查询的速度，特别是在处理大量数据时效果更为显著。索引还可以减少查询时分组和排序的时间，加速表和表之间的连接操作，从而优化数据库的整体性能。

2. 优缺点

优点：
- 加快数据检索速度。
- 减少查询时分组和排序的时间。
- 加速表和表之间的连接。
缺点：
- 创建和维护索引需要消耗时间，且随着数据量的增加，时间也会增加。
- 索引需要占据磁盘空间。
- 对数据表中的数据进行增加、修改、删除时，索引也要动态地维护，可能会降低这些操作的速度。

3. 索引优化

选择合适的列创建索引：通常选择经常用于查询条件的列、经常用于排序和分组的列创建索引。
使用联合索引：当查询条件涉及多个列时，可以创建联合索引。注意将区分度高的列放在前面。
避免在索引列上使用函数或计算：这会导致索引失效。
利用覆盖索引：如果查询只需要索引中的列，则可以使用覆盖索引，避免回表查询。

二、事务

1. 定义

事务是数据库管理系统执行过程中的一个逻辑单位，由一个或多个SQL语句组成，这些语句作为一个整体一起向系统提交，要么全部执行，要么全部不执行。

2. ACID特性

原子性（Atomicity）：事务开始后所有操作，要么全部完成，要么全部不做，不可能停止在中间环节。
一致性（Consistency）：事务开始前和结束后，数据库的完整性约束没有被破坏。
隔离性（Isolation）：同一时间，只允许一个事务请求同一数据，不同的事务之间彼此没有任何干扰。
持久性（Durability）：事务完成后，事务对数据的所有更新将被保存到数据库，不能回滚。

3. 事务优化

选择合适的隔离级别：根据业务需求选择合适的隔离级别，以平衡并发性和数据一致性。
减少事务的大小和持续时间：尽量将事务分解为多个小事务，并尽快提交，以减少锁的竞争和持有时间。
使用索引：在事务中涉及的表上创建合适的索引，以提高查询和更新操作的效率。

三、引擎

1. 定义

存储引擎是数据库管理系统中用于存储数据、处理数据更新和查询的技术实现方式。不同的存储引擎具有不同的特点和适用场景。

2. 常见存储引擎

MyISAM：MySQL 5.5之前的默认数据库引擎，拥有较高的插入和查询速度，但不支持事务和外键。
InnoDB：MySQL 5.5及以后版本的默认数据库引擎，支持事务、行级锁定和外键约束，是事务型应用的首选。
Memory：所有数据置于内存的存储引擎，拥有极高的插入、更新和查询效率，但数据会在MySQL重启后丢失。

3. 引擎选择

根据应用需求选择：如果是简单的应用，可以选择MyISAM；如果需要支持事务、并发性能较高或者有复杂的数据关系，建议选择InnoDB。

四、优化

1. 数据库优化

优化查询语句：避免使用SELECT *，尽量指定需要查询的列；合理使用索引；避免在WHERE子句中对索引列使用函数或计算。
合理设计数据库表结构：避免过多的冗余字段；使用合适的数据类型；合理设计索引。
定期维护数据库：定期更新统计信息；清理碎片；优化表结构。

2. 系统优化

调整系统配置：根据服务器的硬件配置和数据库负载情况，调整内存、CPU等系统资源的使用。
使用缓存技术：利用Redis、Memcached等缓存技术减少数据库的访问压力。
负载均衡：在数据库访问量较大的情况下，使用负载均衡技术将请求分散到多个数据库服务器上。

综上所述，索引、事务、引擎和优化是数据库管理中非常重要的几个方面。通过合理地使用索引、事务和存储引擎，以及不断优化数据库和系统的性能，可以显著提高数据库的整体性能和稳定性。

2.索引的类型

1. 普通索引

定义：用表中的普通列构建的索引，没有任何限制，主要用于加速查询。
特点：允许出现相同的索引内容，且允许空（null）值。
用途：提高数据的查询速度，应只为那些经常出现在查询条件（where）或排序条件（order by）中的数据列创建索引。

2. 唯一索引

定义：用来建立索引的列的值必须是唯一的，但允许空值。
特点：列值不能重复，但是允许null；当给某个列添加唯一约束时，会自动添加唯一索引。
用途：确保数据的唯一性，更快速地确定某条记录。

3. 主键索引

定义：即主索引，根据主键建立的索引，不允许重复，不允许空值。
特点：一种特殊的唯一索引，一个表只能有一个主键。
用途：唯一标识数据库表中的每条记录，确保数据的完整性和一致性。

4. 组合索引（联合索引）

定义：用多个列组合构建的索引。
特点：这多个列中的值不允许有空值（但在某些数据库系统中，如MySQL，空值可以被包含在组合索引中，但这取决于具体的索引创建方式和数据库配置）。
用途：通过多个列进行查询时，可以提高查询效率，但使用时需遵循最左前缀原则。

5. 全文索引

定义：用大文本对象的列构建的索引，主要用来查找文本中的关键字。
特点：更像是一个搜索引擎，而不是简单的where语句的参数匹配。
用途：非常适合大型数据集，能够利用分词技术等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出搜索结果。

6. 空间索引

定义：用于提高系统获取空间数据（如地理位置信息）的效率。
特点：只能建立在空间数据类型上，如GEOMETRY、POINT、LINESTRING和POLYGON等。
用途：在处理与地理位置相关的数据时，可以显著提高查询效率。

2.1索引的操作

2.1.1普通索引

# 创建表的时候加索引
create  table student(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date,
    phone_num varchar(20),
    score double,
    index index_name(name) -- 给name列创建索引
);

# 创建表之后单独加
create index index_name on tablename(columnname); #名字加索引
create index index_gender on student(gender); #性别加索引


# 修改表结构加

-- 方式3-修改表结构(添加索引)
alter table tablename add index indexname(columnname);#名字添加索引
alter table student add index index_age(age);#年龄添加索引
-- 3、查看表中所有索引 
-- show index from table_name; 
#删除索引
drop index index_gender on student 
-- 或 
alter table student drop index index_name

2.1.2唯一索引

-- 方式1-创建表的时候直接指定
create  table student2(
    sid int primary key,
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    phone_num varchar(20),
    score double,
    unique index_card_id(card_id) -- 给card_id列创建索引
);
-- 方式2-直接创建
-- create unique index 索引名 on 表名(列名) 
create unique index index_card_id on student2(card_id);

-- 方式3-修改表结构(添加索引)
-- alter table 表名 add unique [索引名] (列名)
alter table student2 add unique index_phone_num(phone_num)

2.1.3主键索引

同样是建表时添加索引。


create  table student(
    id int primary key,  # 主键索引
    card_id varchar(20),
    name varchar(20),
    gender varchar(20),
    age int,
    birth date, 
    phone_num varchar(20),
    score double
);

2.1.4组合索引

-- 组合索引
-- 创建普通组合索引的基本语法-- 普通索引 列值可以重复
-- create index indexname on table_name(column1(length),column2(length)); 
create index index_phone_name on student(phone_num,name);
-- 操作-删除索引
 drop index index_phone_name on student; 
 
-- 创建唯一组合索引的基本语法-- 唯一索引  列值不能重复
create  unique index index_phone_name on student(phone_num,name); 



select * from student where name = '张三'; 
select * from student where phone_num = '15100046637'; 
select * from student where phone_num = '15100046637' and name = '张三'; 
select * from student where name = '张三' and phone_num = '15100046637'; 
/* 
  三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段  才能够进行匹配。
  而3和4相比where条件的顺序不一样，为什么4可以用到索引呢？是因为mysql本身就有一层sql优化，他会根据sql来识别出来该用哪个索引，我们可以理解为3和4在mysql眼中是等价的。 

*/

2.1.5全文索引

全文索引的关键字是fulltext全文索引主要用来查找文本中的关键字，而不是直接与索引中的值相比较，它更像是一个搜索引擎，基于相似度的查询，而不是简单的where语句的参数匹配。

2.1.6空间索引

1. 支持的空间数据类型

MySQL 支持多种空间数据类型，包括但不限于：

GEOMETRY：这是一个通用的几何类型，可以存储点、线、多边形等。
POINT：用于存储二维或三维空间中的点。
LINESTRING：用于存储一系列的点，这些点连接成线。
POLYGON：用于存储一系列的点，这些点连接成一个闭合的多边形。

2. 创建空间索引

可以使用 SPATIAL INDEX 关键字为空间列创建索引。以下是一个示例，展示如何为包含 POINT 类型的列创建空间索引：

CREATE TABLE Places ( 
id INT AUTO_INCREMENT PRIMARY KEY, 
name VARCHAR(50), 
location POINT NOT NULL, 
SPATIAL INDEX(location) 
);

在这个例子中，Places 表有一个名为 location 的列，该列的类型为 POINT，并为其创建了一个空间索引。

3. 使用空间索引

空间索引主要用于优化与地理空间数据相关的查询，特别是那些使用空间函数（如 ST_Distance_Sphere、ST_Within、ST_Contains 等）的查询。

例如，如果你想找到距离某个点一定范围内的所有地点，可以使用 ST_Distance_Sphere 函数结合空间索引来优化查询性能：

SELECT name, 
ST_Distance_Sphere( 
point(lon, lat), 
(SELECT location FROM Places WHERE id = 1) 
) AS distance 
FROM Places 
HAVING distance < 1000; -- 假设单位为米

注意：虽然这个查询示例没有直接展示空间索引的使用（因为 ST_Distance_Sphere 可能不直接使用空间索引），但是 MySQL 的查询优化器会根据情况选择性地使用空间索引来加速查询。

4. 注意事项索引的维护：像其他索引一样，空间索引也需要维护。频繁的插入、删除和更新操作可能会降低索引的效率。

性能：虽然空间索引可以显著提高查询性能，但它们也会增加写操作的开销（如插入、更新和删除）。
兼容性：确保你的 MySQL 版本支持空间索引和地理空间数据类型。

3.MySQL的引擎

MySQL的引擎是MySQL数据库中负责处理数据存储和检索的组件，不同的存储引擎具有不同的特点和适用场景。MySQL支持多种存储引擎，以下是一些常见的MySQL引擎及其特点：

1. InnoDB

特点：
- 事务支持：InnoDB提供了事务支持，包括ACID（原子性、一致性、隔离性、持久性）事务处理功能，确保数据的安全性和一致性。
- 行级锁定：InnoDB采用行级锁定，可以最大限度地支持并发处理，提高系统性能。
- 外键约束：支持外键约束，可以维护数据表之间的参照完整性。
- 崩溃恢复：具有崩溃恢复能力，通过redo log（重做日志）保证数据在异常情况下也能恢复。
- 自动增长列：支持自动增长列，方便管理数据增长。
- 缓存机制：InnoDB拥有自己的缓冲池，用于缓存数据和索引，提高访问速度。
适用场景：适用于需要事务支持、高并发读写、数据一致性要求高的应用场景，如电子商务、在线支付等。

2. MyISAM

特点：
- 非事务型：MyISAM不支持事务处理。
- 表级锁定：采用表级锁定，对于读操作具有较好的性能，但在并发写操作较多的情况下性能较差。
- 全文索引：虽然InnoDB也支持全文索引，但MyISAM对全文索引的支持更为传统和常用。
- 存储效率：MyISAM的存储格式较为简单，索引和数据文件分开存储，有利于快速读取数据。
- 不支持外键：MyISAM不支持外键约束。
适用场景：适用于读操作较多、写操作较少、不需要事务支持的应用场景，如博客、新闻网站等。

3. Memory（或Heap）

特点：
- 内存存储：所有数据都存储在内存中，访问速度极快。
- 不支持事务：Memory引擎不支持事务和持久化存储，重启MySQL服务后数据会丢失。
- 哈希索引：默认使用哈希索引，但也可以指定使用B树索引。
适用场景：适用于临时表、缓存和高速缓存等需要快速读写的场景。

4. Archive

特点：
- 高度压缩：采用高度压缩的数据格式，可以大幅减小存储空间。
- 不支持索引和事务：Archive引擎不支持索引和事务，但具有较快的插入和压缩速度。
适用场景：适用于只需要偶尔查询的数据存储，如日志存档、备份等。

5. CSV

特点：
- 文本文件存储：将数据以逗号分隔的格式存储在文本文件中，易于导入和导出数据。
- 不支持索引和事务：CSV引擎不支持索引和事务，但具有较好的可读性和易用性。
适用场景：适用于需要与其他应用程序交换数据的场景。

其他引擎

除了上述常见的存储引擎外，MySQL还支持其他一些存储引擎，如Blackhole、Federated、NDB等。这些存储引擎具有各自的特点和适用场景，可以根据具体需求选择合适的存储引擎。

如何选择存储引擎

在选择MySQL的存储引擎时，需要考虑以下因素：

事务支持：是否需要事务处理功能。
并发性能：并发读写操作的频率和性能要求。
数据完整性：是否需要外键约束等保持数据完整性的功能。
存储空间：对存储空间的需求。
索引速度：对索引速度的要求。

以下是mysql常见引擎类型的整理表，可以更直观的展现出来各个引擎的区别；

-- 查询当前数据库支持的存储引擎：
show engines;
 
-- 查看当前的默认存储引擎：
show variables like ‘%storage_engine%’;

-- 查看某个表用了什么引擎(在显示结果里参数engine后面的就表示该表当前用的存储引擎): 
show create table student; 
 
-- 创建新表时指定存储引擎：
create table(...) engine=MyISAM;
 
-- 修改数据库引擎
alter table student engine = INNODB;
alter table student engine = MyISAM;

4.MySQL事务的操作

1、事务是将一组sql看做是一个整体，这组SQL只有全部成功才算成功，只要有一条失败就算失败
2、事务的动作：

  #1、开启事务：Start Transaction

    任何一条DML语句(insert、update、delete)执行，标志事务的开启
    命令：BEGIN 或 START TRANSACTION

    #2、提交事务：Commit Transaction
    成功的结束，将所有的DML语句操作历史记录和底层硬盘数据来一次同步
    命令：COMMIT

    #3、回滚事务：Rollback Transaction
    失败的结束，将所有的DML语句操作历史记录全部清空
    命令：ROLLBACK 
use mydb_transcation;
-- 创建账户表
create table account(
    id int primary key, -- 账户id
    name varchar(20), -- 账户名
    money double -- 金额
);
 
 
--  插入数据
insert into account values(1,'zhangsan',1000);
insert into account values(2,'lisi',1000);
-- 设置MySQL的事务为手动提交(关闭自动提交)
select @@autocommit;
set autocommit = 0;
 
-- 模拟账户转账
-- 开启事务 
begin;
update account set money = money - 200 where name = 'zhangsan';
update account set money = money + 200 where name = 'lisi';
-- 提交事务
commit;
 
 
-- 如果转账中的任何一条出现问题，则回滚事务
rollback;

5.MySQL的优化

#一、表结构优化：
1、选择合适的数据类型，如使用MEDIUMINT而不是BIGINT来定义整型字段。
2、使用ENUM类型：对于离散数据，如“省份”或“性别”，星期几，可以使用ENUM类型来提高处理速度，因为ENUM在MySQL中被当作数值型数据来处理。

#二、查询优化：
1、使用连接(JOIN)代替子查询：在某些情况下，使用JOIN操作代替子查询可以显著提高查询效率，尤其是当连接字段建有索引时。
2、避免SELECT *：在查询时，直接指定需要的字段名，避免使用SELECT *，这样可以减少不必要的数据读取和网络传输开销。
3、使用LIMIT限制结果集：当只需要查询少量数据时，使用LIMIT来限制结果集的大小，提高查询效率。

#合理使用索引：
4、创建合适的索引是提高查询效率的关键。根据查询需求，选择正确的索引类型（如主键索引、唯一索引、普通索引等）并合理设计索引字段的顺序。
5、可以使用执行计划（explain）来分析SQL执行的效率，进一步优化

#三、服务器配置优化：
1、调整MySQL的缓存配置参数，如innodb_buffer_pool_size（InnoDB存储引擎的缓存池大小）和query_cache_size（查询缓存大小）等，以适应实际的工作负载和数据量。
2、启用慢查询日志，并定期分析慢查询日志，找出并优化那些执行时间较长的查询语句。

#四、数据分区和分表：
1、当数据量非常大时，可以考虑使用数据分区或分表技术来将数据分散到多个主机上，提高查询性能和系统的可扩展性。

# 五、插入数据优化
如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

-- 优化后的方案为：

insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

6.视图

视图（view）是一个虚拟表，非真实存在，其本质是根据SQL语句获取动态的数据集，并为其命名，用户使用时只需使用视图名称即可获取结果集，并可以将其当作表来使用。

创建语法:

create or replace view  视图名称
as
select 语句;

z小郑敲哥们儿

关注

15
点赞
踩
6

收藏

觉得还不错? 一键收藏
0
评论
索引-事务-引擎-优化（MySQL高级）

索引是数据库中用于提高数据检索效率的数据结构，通常基于表中的一个或多个列创建。它可以大大加快数据查询的速度，特别是在处理大量数据时效果更为显著。索引还可以减少查询时分组和排序的时间，加速表和表之间的连接操作，从而优化数据库的整体性能。事务是数据库管理系统执行过程中的一个逻辑单位，由一个或多个SQL语句组成，这些语句作为一个整体一起向系统提交，要么全部执行，要么全部不执行。存储引擎是数据库管理系统中用于存储数据、处理数据更新和查询的技术实现方式。不同的存储引擎具有不同的特点和适用场景。
复制链接

扫一扫