Mysql基本知识篇

zxc_user

已于 2022-11-26 15:22:25 修改

阅读量447

点赞数

分类专栏：数据库与缓存文章标签： mysql 数据库

于 2022-11-26 00:27:28 首次发布

本文链接：https://blog.csdn.net/zxc_user/article/details/128046311

版权

数据库与缓存专栏收录该内容

10 篇文章 0 订阅

订阅专栏

Mysql三大范式

严格上来说是有6大范式的，但是我们一般只实现了三个，所以这里只讨论三大范式

第一范式

数据表中字段要小到不能再进行切分，比如如果有一个name-age字段，既保存了用户的名字和年龄，这种情况下就需要把他拆成name和age两个字段才符合第一范式

第二范式

数据表中如果要索引到一个数据，尽可能要通过一个唯一字段来索引，比如说商品表和分类表，商品可以属于多个分类，这种情况下就需要一张中间表来保存这个关系

第三范式

尽可能不要冗余数据，比如你有以下的表格

orderTable

id  orderNo  userName


UserTable

userId userName


像上面的orderTable来说userName就是一个冗余数据，应该改为

orderTable

id  orderNo  userId

反范式设计

有时为了性能的考虑，我们经常会使用反范式来进行设计以提示查询性能，常用的有以下几种方式

1. 冗余，缓存，汇总：汇总实际上就是把一些数据计算好入库，典型例子就是统计用户有多少种不同类型的订单数据

2. 计数表：比如用户的浏览量之类的

3. 分库分表查询

尽可能遵循范式设计，当范式设计成为我们系统的性能瓶颈时，可以考虑做一些反范式设计

写热点分散

当写更新冲突比较大的时候，这是一种常用的解决方案，比如数据表有一条记录user_id为3，count为0的记录，如果有几千个线程对这一条记录进行update操作，那么mysql肯定是会加锁的，这几千个线程就会排队执行，冲突是非常大的，这种情况下，我们就可以对user_id再进行拆分为多条记录，通过hash算法把user_id落到多条数据中去更新，LongAddr和分段锁的思路都有点类似于这种做法，当要获取数据的时候再把全部数据拿出来统计就行了

索引类型

在Innodb中，索引被分为了聚集索引和辅助索引，数据跟聚集索引是在一个位置的，而辅助索引下面存的是到聚集索引的一个位置

innodb中，默认会使用主键索引作为聚集索引，如果没有主键索引，会使用唯一索引作为聚集索引，如果都没有mysql会维护一个row_id作为聚集索引，而这是一个字符串来的，排序性能肯定就会很低了，所以尽可能要创建主键索引，而且尽可能是有序的

回表与MRR优化

回表指的是在辅助索引上没有办法找到全部的数据，需要再从聚集索引获取其他的数据，这个过程就称为回表，回表过多性能是会比较差的

MRR优化：首先，要理解mysql的数据是存在页里面的，默认是16kb，而早期的Mysql查询从辅助索引查询后如果数据不够，它是会直接回表去查其他数据的，这样回表的次数和性能可能不会很高，比如辅助索引要回表的id可能为1,32,16,54,2等，它是无序的，而且可能部分数据是在同一个页而导致了多次的回标，MRR优化是会先对数据排序后再统一回表，也就是说MRR优化可以在一定程度上减小回表的次数，注意：只是减少，不是一定会生效的

索引的离散性

离散性简单理解就是重复度低的离散性就越高，索引的性能就越好，反之则越差，我们在建立索引的时候要尽可能在离散型高的字段创建，字段的离散型可以简单的通过以下方式判断：

select COUNT(DISTINCT order_no)/count(*) cnt from order_exp;

索引的创建和使用

尽可能使用复合索引，可以用一个索引就支持很多种的查询，尽可能利用覆盖索引，就可以减少回表的次数，mysql全文索引的使用

前缀索引的使用，只要有足够的区分读，可以只指定前16个字符，不一定要按阿里说的前缀到20个字符

不要在离散率低的字段上使用索引，简单理解就是重复度高，导致区分度低，索引就没什么太大的意义了，离散率可以使用以上形式进行判断

select COUNT(DISTINCT order_no)/count(*) cnt from order_exp;

当然也可以从业务上去判断

索引的成本

索引的使用也不是完全没有坏处的，过多的使用索引会带来以下的问题

空间占用：索引也是需要存储的，索引越多，暂用的空间就越大

时间占用：为了让索引生效，那么在数据新增，删除时就需要维护索引，如果索引很多，那么时间就会更长，因为每个索引树都得去维护数据的有序性

阿里建议尽量使用复合索引，而且最多不要超过3个，这些其实不是一定的，具体要根据自己的业务情况进行相应的设置

Cost成本计算

mysql对一个条sql查询性能的cost计算，主要分为下面两个维度

IO成本：1.0，根据数据存储所占用的页个数来计算

CPU成本：0.2 根据数据量来统计

两者计算后还有个微调数的概念，这是mysql底层写死的一个东西

mysql中关于成本计算维护在两张表中

mysql.engine_cost：引擎层的，cost相关的比较少

mysql.server_cost：服务层的，cost相关的比较多

mysql数据统计

mysql中会对innodb的表数据和索引数据进行记录，在5.7以前是使用未持久化的数据统计，也就是在内存中的，5.7以后使用的是持久化的数据统计，也就是说在磁盘的，下面是两张相关的表存储相应的信息

innodb_table_stats存储了关于表的统计数据，每一条记录对应着一个表的统计数据。

innodb_index_stats存储了关于索引的统计数据，每一条记录对应着一个索引的一个统计项的统计数据。

innodb_table_stats表的字段信息

database_name 数据库名

table_name 表名

last_update 本条记录最后更新时间

n_rows 表中记录的条数

clustered_index_size 表的聚簇索引占用的页面数量

sum_of_other_index_sizes 表的其他索引占用的页面数量

innodb_index_stats表的字段信息

database_name 数据库名

table_name 表名

index_name 索引名

last_update 本条记录最后更新时间

stat_name 统计项的名称

stat_value 对应的统计项的值

sample_size 为生成统计数据而采样的页面数量

stat_description 对应的统计项的描述

innodb_index_stats表的每条记录代表着一个索引的一个统计项。可能这会大家有些懵逼这个统计项到底指什么，别着急，我们直接看一下关于order_exp表的索引统

对于有多个列的联合索引来说，采样的页面数量是：innodb_stats_persistent_sample_pages × 索引列的个数。默认是20