数据库存储引擎、索引及使用原则_合适的数据库引擎和索引策略。-优快云博客

本文链接：https://blog.youkuaiyun.com/weixin_43978588/article/details/127184826

数据库存储引擎、索引及使用原则

数据库存储引擎、索引及使用原则

存储引擎

存储引擎：数据库存储数据、建立索引、更新/查询数据等技术的实现方式（机制）。存储引擎是基于表的。(Mysql 建表默认的存储引擎是InnoDB)

//建表时指定存储引擎
create table 表名(
	字段1 字段1类型，
	...
)engine=INNODB;
//查询数据库支持存储引擎
show engines;

在这里插入图片描述

1、InnoDB

InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL5.5之后，作为默认存储引擎。
特点：

DML操作遵循ACID模型，支持事务；
行级锁，提高并发访问性能；
支持外键约束，保证数据的完整性和正确性。

2、MyISAM

MySQL早期默认存储引擎。
特点：

不支持事务和外键；
支持表锁，不支持行锁；
访问速度快。

3、Memory

表数据存储在内存中，只能作为临时表或缓存使用。
特点：

内存存放；
hash索引（默认）;

对比

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	-	-
锁机制	行锁	表锁	表锁
外键支持	支持	-	-
B+tree索引	支持	支持	支持
Hash索引	-	-	支持
全文索引	5.6版本后支持	支持	-
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高

存储引擎选择

InnoDB:对事物的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作。
MyISAM:以读和插入操作为主，只有很少的更新和删除，并且对事务的完整性、并发性要求不是很高。
MEMORY:将所有数据保存在内存中，访问速度快，通常用于临时表和缓存。

索引

帮助MySQL高校获取数据的数据结构。

优点：提高数据检索效率，降低数据库IO成本。通过索引列对数据进行排序，降低数据排序的成本和CPU消耗。
缺点：索引列要占用空间。虽然索引提高了查询效率，但更新数据时可能也要更新索引，降低了更新表的速度。

索引结构

索引结构	说明
B+Tree索引	底层数据结构使用B+树实现，最常见的索引类型，大部分引擎都支持B+树索引
Hash索引	底层用哈希表实现，只有精确匹配索引列查询才有效，不支持范围查询
R-Tree(空间索引)	MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型
Full-Text(全文索引)	一种通过建立倒排索引，快速匹配文档的方式

索引分类

分类	含义	特点	关键字
主键索引	用于表中主键的索引	默认自动创建且只能有一个	PRIMARY
唯一索引	避免表中某数据列存在重复值	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	查找文本中的关键词而不是比较索引中的值	可以有多个	FULLTEXT

InnoDB存储引擎中，根据索引的存储形式，可以分为以下两种：

分类	含义	特点
聚集索引（Clustered Index）	将数据存储与索引放在一起，索引结构的叶子节点保存了行数据	有且只有一个
二级索引（Secondary Index）	将数据与索引分开存储，索引结构的叶子节点关联对应主键	可以存在多个

聚集索引选取规则：

如果存在主键，主键索引就是聚集索引；
如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引；
如果没有主键且没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
聚集索引B+树叶子节点下挂的是行数据，二级索引叶子节点下挂的是主键值。
回表查询：先从二级索引查询到主键ID,再根据主键ID通过聚集索引查询到该行数据。

索引语法

创建索引

//创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,...);
//为tb_user表name列创建索引，可重复
create index idx_user_name on tb_user(name);
//为tb_user表phone创建唯一索引
create unique index idx_user_phone on tb_user(phone);
//为name,age,phone创建联合索引
create index idx_user_name_age_phone on tb_user(name,age,phone);

查看索引

//查看索引
show index from table_name;

删除索引

//删除索引
drop index index_name on table_name;
drop index idx_user_phone on tb_user;

SQL性能分析

//查看当前数据库增删查改访问频次
show global status like 'Com__________";

//慢查询日志  记录所有执行时间超过指定参数（默认10s）的sql语句的日志
//查看慢查询开关
show variables like 'slow_query_log';

慢查询配置文件(/etc/my.cnf),配置完毕后重启MySQL服务，日志保存在/var/lib/mysql/localhost-slow.log

//开启慢查询日志开关
slow_query_log=1;
//设置慢查询时间
long_query_time=2;

profile

//查看每条SQL耗时基本情况
show profiles;
//查看指定query_id的SQL语句各个阶段耗时情况
show profile for query_id;
#查看指定query_id的

explain执行计划
通过explain或者desc命令获取mysql执行select语句的信息，包括在select语句执行过程中表如何连接和连接顺序。

explain select 字段列表 from 表名 where 条件；

索引使用原则

最左前缀法则（针对联合索引）查询从索引开始的最左列开始，并且不跳过索引中的列。如果跳过某一列，后面字段索引将失效（部分失效）。
范围查询联合索引中，出现范围查询（>，<）,范围查询右侧的索引失效。（业务允许情况下尽量使用>=代替）。
索引列运算不要再索引列上进行运算操作，否则索引将失效。
字符串不加引号字符串类型字段使用，不加引号，索引将失效。
模糊查询尾部模糊匹配，索引不会失效。头部模糊匹配，索引失效。
or连接的条件用or分割开的条件，如果or前的条件中的列有索引，后面的列中没有索引，所有索引都将失效。
数据分布影响如果MySQL评估使用索引比全表慢，则不适用索引。
SQL提示在SQL中控制如何使用索引

//use index  建议使用（mysql检查是否正确）
explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
//ignore index  忽略（不适应）
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
//force index 强制使用
explain select * from tb_user force  index(idx_user_pro) where profession='软件工程';