MySQL中的索引原理事务存储引擎及运维优化

李奇技

已于 2022-09-22 11:30:21 修改

阅读量421

点赞数

分类专栏：数据库文章标签： mysql 运维 b树

于 2022-09-22 11:18:57 首次发布

本文链接：https://blog.csdn.net/qq_43408971/article/details/126988294

版权

数据库专栏收录该内容

6 篇文章 0 订阅

订阅专栏

前置内容

局部性原理

程序和数据的访问都有聚集成群的倾向，在一段时间之内，仅使用其中一小部分（空间局部性），或者最近访问过的程序和数据，很快又被访问的可能性很大（时间局部性）。

磁盘预读

操作系统将主存和磁盘区分割为连续的大小相等的块，每一个存储块称为一页（一般为4k或8k），主存和磁盘以页为单位交换数据。

InnoDB默认每次获取16k数据

索引

索引基础

索引：帮助MySQL高效获取数据的数据结构，索引存储在文件系统中，索引文件的存储结构和引擎有关。

索引可选的索引结构有：hash、二叉树、B树、B+树

数据结构

Hash

Hash存储结构：数组+链表

Hash索引的缺点：

需要将所有的数据文件添加到内存，耗费内存空间；
如果所有查询都是等值查询，Hash查询速度很快。但如果是范围查询，需要比较每一个数据，Hash效率极低；
存在Hash值碰撞问题，扰动函数也不能彻底解决。

所以，Memory存储引擎使用Hash索引，原因：内存空间足够，内存查询速度快，选择合适的Hash算法。

二叉树

二叉树索引格式

缺点：无论是二叉树还是红黑树，都会因为深度过深，而增加io次数，影响数据读取效率。

B-Tree

B树索引格式 [外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-lsbsK750-1663817343031)(http://img-S.sunyog.top/blog/note_mysq02_01.png)]

为了解决树结构深度过深的问题，引入B树。以上是一个4阶B树，每个节点最多存储3个数据。随着B树阶数的增加，同样层数保存的数据量急剧增加。

MySQL采用B+树的存储结构，其中一个节点大小为16k，一个节点即对应内存中的一页数据，即一次磁盘io读取的数据。

B树的不足：非叶节点也需要保存数据，而数据库中的数据占用内存一般都比索引更大，相比B+树，同样数据量下，非叶节点都用来存储索引，数据都在叶节点中保存，则树的层数减少，能较少更多的io次数，提高性能。

B+Tree

B+树索引结构

B+树，非叶子节点只保存索引，叶子节点保存数据。

MySQL索引结构

MySQL索引结构 ![](https://img-blog.csdnimg.cn/img_convert/16c595a93b8d71aa96a140aea9997608.png)

注意：在B+Tree上有两个指针，一个指向根节点，另一个只想关键字最小的叶子结点，而所有的叶子结点之间是一种双向链表结构，因此，可以对B+Tree进行两种查找运算，一种是对于主键的范围查找和分页查找，另一种是从根节点开始进行随机查找。

每个磁盘块占用16kb空间，假设指针和键所占空间为10b，则一个磁盘块可以指向1600行数据，则一个三层的B+树，可以存储的数据量为：1600*1600行数据。一般一个三层的B+树即可索引到千万级别的数据量，具体数量和索引类型有关。

MySQL数据库B+树的层数和数据量有关，也和索引所占空间有关，数据量过大层数会变多，索引所占空间大每一个磁盘块存储的索引更少，所以需要更多的层数。因此，索引的类型要尽量占用空间小。

聚簇索引和非聚簇索引

InnoDB和MyISAM两个存储引擎都适用以上这种存储结构，不同的是两种引擎在data中保存的数据是不一样的。InnoDB引擎的data中存储的就是数据本身，而MyISAM引擎的data中存储的是一个地址，指向真实数据。因此，在InnoDB引擎在磁盘中保存的是.idb文件，在MyISAM引擎在磁盘中保存的是.myd和.myi的文件。InnoDB的这种方式称为聚簇索引，MyISAM的这种方式称为非聚簇索引

聚簇索引–数据和索引key保存在同一个树中。非聚簇索引–数据和索引key没有保存在同一个树中。

InnoDB引擎的索引创建方式

InnoDB存储引擎存储数据时，使用B+树对主键创建索引，然后叶子结点存储数据记录。如果没有主键，则会选择唯一键作为主键，如果没有唯一键，则会生成一个6字节的row id作为主键，这个row id对用户不可见。
如果创建索引的键是其他字段，那么叶子结点中存储的是该记录的主键，而不是数据，然后通过主键索引找到对应的记录，这一过程叫做回表。（辅助索引）
如果创建了多个索引，则每创建一个索引则会创建一个新的B+树。除主键之外的B+树中叶子结点所保存的就不是数据了，而是数据对应的主键。

索引分类

主键索引，关系到数据的组织形式
唯一索引，当前列不允许有重复值，如果没有主见索引，使用唯一索引组织数据
普通索引，辅助索引/二级索引
全文索引，全文检索、分词，一般使用ES（ElasticSearch）
组合索引，多列

索引匹配方式

全值匹配，和索引中的所有列进行匹配
最左匹配，只匹配前面的几列
匹配列前缀，在like查询中like条件为like 'a%'可以走索引，条件为like '%a%'不走索引
精确匹配某列，并范围匹配另一个列。范围查询where age=11 and name > 'abc'也可以走索引
只访问索引的查询，意思同索引覆盖

索引失效

like查询中开头字符模糊会失效，如like '%a'
多条件查询中，如果中间的条件是范围查询，则范围查询后边的条件不走索引，如where name='abc' and age > 11 and addr='aaaa'，如果存在组合索引（name，age，addr），则此条查询语句只有name，age会匹配索引
or查询条件是否走索引需要看情况
1. 如果是单列索引，or条件会使用索引
2. 如果表中所有字段都是组合索引，or条件会走索引
3. 如果所查询字段都是一个组合索引中的字段，or会走索引（索引覆盖了）
4. 如果所查询字段中含有不在组合索引中的字段，or不会走索引
5. in查询和or基本一致

哈希索引

在MySQL中只有memory引擎显示支持哈希索引，基于哈希索引的实现只有精确匹配所有列的查询才有效，哈希索引自身之存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引的查询速度非常快。

哈希索引的限制：

哈希索引只包含哈希值和行指针，而不存储字段值，不能使用索引中的值来避免读取行
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
哈希索引不支持部分列匹配查找，哈希索引使用索引列的全部内容来计算哈希值
哈希索引支持等值比较查询，不支持任何范围查询
当出现哈希值冲突时，存储引擎必须遍历链表中所有行指针，进行逐行比较，直到找到所有符合条件的行
哈希冲突过多时，维护代价也更高

（必须使用所有索引字段，不能排序，不能范围查询，存在哈希值冲突）

索引监控

语句show status like 'Handler_read%'可以查询索引监控信息，查询结果及说明如下：

variable_name	value	备注
Handler_read_first	18	读取索引第一个条目的次数
Handler_read_key	2755	通过index获取数据的次数
Handler_read_last	0	读取索引最后一个条目的次数
Handler_read_next	108	通过索引读取下一条数据的次数
Handler_read_prev	0	通过索引读取上一条数据的次数
Handler_read_rnd	373	从固定位置读取数据的次数
Handler_read_rnd_next	8919	从数据节点读取下一条数据的次数

索引优化原则

使用索引列进行查询时，不要对索引列使用表达式，把计算逻辑放到业务层。例如：
```
explain select id_card,person_name from dlcq_person where person_id+1=4;
```
此查询不会走主键索引。但是因为id_card,person_name两个字段创建了组合索引，会走组合索引，但是组合索引是在所有id中进行匹配，匹配速度也不如直接走主键索引快。
尽量使用主键查询（尽量使用代理主键），因为主键查询中没有回表

代理主键：和业务无关的字段做主键，如id

自然主键：和业务相关的字段做主键，如id_card
使用前缀索引

在varchar类型的数据中有时长度很长，如果将整个字段作为索引则会使索引占用资源更高，索引效率降低。应该将varchar类型的前几位用作索引字段可减少磁盘IO量，具体是几位需要根据具体数据计算。一个简单的方法是：使用如下sql尽量保证两个查询结果在同一个数量级
```
select name,count(name) from table group by name
select name,count(left(name,3)) from table group by name
```
使用索引扫描来排序（即不要出现Using filesort）

如果只是order by中使用了组合索引，而where条件没有使用索引，则不会使用索引排序。

在排序时，多有排序字段要么都生序，要么都降序。一升一降不走索引排序，因为索引的数据机构不支持这种排序
union all，in，or都有可能使用索引，但推荐使用in
范围查询可以用到索引，<,<=,>,>=,between都是范围查询

但是范围条件后边的条件不会用到索引，所以范围查询条件最好放在最后

强制类型转换会全表扫描

-- 不走索引
explain select * from user where phone=13800000000;
-- 走索引
explain select * from user where phone='13800000000';

更新十分频繁，数据区分度不高的字段上不宜建立索引

更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能。类似于性别这种区分不大的属性，建立索引没有意义，不能有效的过滤数据。一般区分度在80%以上的字段可以建立索引，区分度可以使用count(distinct(列名))/count(*)计算
创建索引的列不允许为null，可能会得到不符合预期的结构
当需要进行表连接时，最好不要超过三张表，需要join的字段数据类型必须一致才会走索引，不一致会导致索引失效
能使用limit时尽量使用limit，但尽量不要使用如下语句limit 10000,5，limit的作用是限制输出数量

大数据量的分页可以通过子查询和union all实现
单表索引建议控制在5个以内（现在没有太多的限制），索引越多，磁盘IO量也就越多
单组合索引字段数量不要超过5个，key变大，B+树变大，磁盘IO量增多
索引不是越多越好，不要在不了解系统情况下进行优化

索引越多，保存的文件就越多，文件越多，磁盘IO量也就越多

索引相关命令

-- 查看存储引擎
show engines;

-- 创建索引SQL
create index person_index on dlcq_person(person_name asc);
-- 只取person_name字段的前三位计算索引
create index person_index on dlcq_person(person_name(3));
-- 删除索引
drop index person_index on dlcq_person;
-- drop databases|index|procedure|table|trigger|user|view itemName;

-- innodb引擎不支持hash索引，只有B+树，即便说明使用hash索引也是B+树
-- innoDB引擎使用自适应hash
create index person_index using hash on dlcq_person(person_name desc);

-- 查看执行计划
explain -- SQL语句，如：select * from table;

explain命令结果解析

key–所使用的索引名称

key_len–索引长度

rows–总共查询了多少行，不一定是查询结果

Extra–Using where：使用where条件查询，Using index：索引覆盖，Using filesort：使用当前文件排序（尽量不要出现，不出现时使用索引排序）

事务

事务基础

commit，rollback

事务回滚可用来回退insert，update，delete语句，不能回滚create和drop操作

在事务代码块中可以使用create和drop语句，但事务回滚之后，这些操作不会被撤销

start transaction;
insert...
update...
delete...
commit;//rollback;

保留点

如果发生错误，只回退到某个特定的位置即可，不用全部回退；
在mysql中可以设置任意个保留点；
事务处理完成后自动释放保留点；

savepoint delete1;
...
rollback to delete1;

事务和存储引擎

事务中混合使用多种存储引擎的表（包括事务型和非事务型存储引擎，如InnoDB和MyISAM）在正常情况下没有问题，但如果发生回滚则非事务型的表不会发生回滚。

-- 查看自动提交，no--非自动提交，在事务中关闭自动提交
show VARIABLES like 'autocommit';

-- 设定开启自动提交，0或off表示关闭
set autocommit = 1
-- 或set autocommit = on

-- 设定事务隔离级别，mysql支持所有的4个隔离级别，read commited,read uncommited,repeatable read,serializable
set session transaction isolation level read commited;

存储引擎

不同的存储引擎，在次盘保存数据的方式不同（不同的文件保存形式）

	MyISAM	InnoDB
索引类型	非聚簇索引	聚簇索引
支持事务	否	是
支持表锁	是	是
支持行锁	否	是
支持外键	否	是
支持全文索引	是	是（5.6版本之后）
适合的场景	大量select	大量insert，delete，update

MySQL性能提升

表创建过程的优化

单表字段不宜过多，参考使用预留字段
使用正确的数据类型
1. 尽量使用整形表示字符串，如ip地址可以通过函数inet_ntoa(number)数字转换为字符，inet_aton(str)字符转换为数字
2. 设置合理的字段长度
尽可能使用not null约束
选择合适的存储引擎

范式要求：（减少数据冗余）

字段原子性。省、市、县作为三个字段，而不是使用地址作为一个字段
消除对主键的部分依赖，根据主键能唯一定位到一个列
消除对主键的传递依赖，（数据列A–>数据列B–>主键）

数据增删改查过程的优化

减少使用select * ···；
找出select语句中重复的where和group by语句，建立索引；
索引能提升select性能，但对插入、删除、更新性能有损；
复杂的or条件通过使用多条语句，用union替代；
存储过程比循环执行SQL语句更快；

导入数据时，关闭自动提交，禁用索引；

-- 禁用索引
alter table table_name disable keys;
-- 启用索引
alter table table_name enable keys;

-- 可以通过手动开启事务的方式关闭自动提交

使用fulltext全文索引代替like；

数据库运维优化

定期调整内存分配、缓冲区大小等（show variables；show status；查看当前设置）；

命令show variables like 'query_cache_size%'用于查看缓存大小

在sql语句中增加select sql-cache来主动缓存

数据分区，数据量较大时性能开始变慢，需要将数据分散到多组存储文件中，进行数据查找时只查找对应的文件即可。分区算法包括：hash(field)，key(field)，range算法，list算法

hash(field)适用于整形字段
key(field)适用于字符串类型

range算法，按照数据大小范围分区

create table article_range(
	id int auto_increament,
  title varchar(64),
  content text,
  create_time int,-- 毫秒值
  paimary key (id,create_time)-- 用作分区的字段必须是主键的一部分
)charset=utf8
partition by range(create_time)(
	partition p201808 values less then (1535731199)，
  partition p201909 values less then (1538323199)
);

list算法

create table article_list(
	id int auto_increament,
  title varchar(64),
  content text,
  status tinyint(1),
  primary key (id,status)
)charset=utf8
partition by list(status)(
	partition writing value in(0,1),-- 状态为0，1的在一个分区
  partition published value in (2)-- 状态为2的在一个分区
)

开启慢查询日志，配置项为：slow_query_log

-- 查看是否开启慢查询日志
show variables like 'slow_query_log';
-- 开启慢查询日志
set global slow_query_log = on;

mysql常见配置

配置名称	备注
max_connections	最大客户端连接数
table_open_cache	表文件缓存
key_buffer_size	索引缓存大小
innodb_buffer_pool_size	innodb存储引擎缓存池大小
innodb_file_per_table	将每一个表的数据放在一个文件中，而不是共享表空间