MySQL数据库|数据类型|数据库索引|explain执行计划|慢查询|mysql的锁

最新推荐文章于 2024-09-15 08:03:51 发布

一贤不穿小鞋

最新推荐文章于 2024-09-15 08:03:51 发布

阅读量213

点赞数

文章标签：数据库 mysql 数据库锁数据库索引

本文链接：https://blog.csdn.net/m0_53222768/article/details/125795190

版权

一、数据库三范式设计

1.1 数据库设计的第一范式

数据库表中的所有字段都只具有单一属性。
单一属性的列是由基本的数据类型所构成的。
设计出来的表都是简单的二维表。

1.2 数据库设计的第二范式

要求一个表中只具有一个业务主键，也就是说符合第二范式的表中不能存在非主键列的值对部分主键的依赖关系。

1.3 数据库设计的第三范式

指每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖。

二、数据库反范式设计讲解

反范式化是针对范式化而言，为了性能和读取效率的考虑，而适当的对数据库设计的要求进行违反，而允许存在少量的数据冗余，换句话来说反范式化就是使用空间来换取时间。

三、数据库表字段设计总结（常用数据类型）

3.1 整数类型

tinyint (1字节）
smallint (2字节）
mediumint (3字节)
int (4字节）（32bit）（常用）
bigint （8字节）

3.2 实数类型

float（4字节）（常用）
double（8字节）（常用）
decimal
numeric

3.3 字符串类型

char 字符串（固定长度）
varchar 可变长字符串（常用）
variable 可变的

char(4） varchar(4） 数据库里面所有的字符串类型

binary 二进制
varbinary 可变二进制
blob 二进制大文本
text 正常字符大文本

3.4 日期类型

date 日期（常用）
time 时间
datetime 日期&时间（常用）
timestamp 时间戳

四、数据库索引介绍

帮助我们高效获取数据的一种数链结构。

数组（不能通过数组取出当前内容值来比对，做索引不合适）
链表（一个一个查询，做索引不适合）
二叉树（数据量过大时二叉树过深，且磁盘交互读取磁盘次数过多，做索引不适合）
平衡二叉树（深度还是有的，有缺陷）
红黑二叉树（没有根本解决树的高度）
hash（hash有冲突，等值查询好，范围查询不合适，排序也麻烦）
经验：尽可能结构要比对次数少（优化树的高度）
B-Tree（B树）（磁盘存储性能好，优化查询性能）
磁盘预加载，不是所有的数据都在叶子节点，非叶子节点也存数据，通过遍历才能得到有序的一个结构，排序和范围查询有缺点。
B+Trees（B加树）（最适合做索引）
结构特点：数据都在叶子节点上，并且叶子节点有指针，互相连接。
装载树的结构上，装载非叶子节点，关键字存的越多，意味着有大量的比对，是在内存中进行，而不是在磁盘进行读取的。
减少了读取磁盘次数，并且可以在叶子节点上可以进行范围查询。
mysql在innodb数据引擎，它在底层采用B+Trees，其目的是加快我们数据的查询。比对次数少的情况下能存储大量的数据的。

五、数据库索引文件体现形式

5.1 索引实现对比

	MyISAM	InnoDB
事务	不支持	支持
读写效率	高	低
索引	支持全文索引	不支持全文索引
外键	不支持	支持
锁	表锁	行锁
文件存储形式	.MYD .MYI *.FRM	*.FRM(默认为共享表空间，可修改)
适用场景	大量select结构	大量update语句
删除表后数据文件是否存在	自动清除	不自动清除

5.2 InnoDB索引实现

主键索引：最终索引和数据保存在一起。
二级索引（辅助索引）：经常查询的列建立索引。从叶子节点的索引，回表，回到主键索引查询，最终由主键定位到要查的数据行。二级索引是跟一级索引关联的。

5.3 MyISAM索引实现

主键索引：叶子节点存的是数据文件在磁盘上的位置，指针值，磁盘地址。
二级索引（辅助索引）：经常查询的列建立索引。最终得到在叶子节点存的地址。

5.4 聚族索引和非聚族索引对比

聚族索引：InnoDB就是一张表只能有一个聚族索引，基于主键索引和辅助键构建的检索结果。最终的数据和索引在一起。
非聚族索引：MyISAM基于主键索引和辅助键构建的检索结构。最终的数据和索引未在一起。
覆盖索引：指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取，聚族索引的叶子节点存放的是主键值和数据行，支持覆盖索引。（InnoDB可以）

六、索引左前缀匹配

mysql有唯一索引、主键索引、联合索引、全文索引。
mysql数据库用B+trees来进行保存，数据库用InnoDB建立的索引是聚族索引，主键和数据是存在一起的，二级索引是根据主键索引来关联的。二级索引在构建B+Trees的时候，它是根据这列的值来构建的，列的值名在没有指定最左边的条件的情况下是没有办法来判断当前的查询条件，它在这个树路径无法前进。
例子及经验：尽量全值匹配、左前缀匹配

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//查看索引
explain select * from emp where name='z3';
explain select * from emp where name='z3' and job='manage';
explain select * from emp where name='z3' and age='22' and job='manage';

七、索引不在索引列上操作

在索引列上做与索引列相关的函数，会导致用不了索引查询。
在索引列上做操作，包括做一些运算，会导致其用不了索引查询。

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//这样在索引列上做函数操作不可以用索引查询
explain select * from emp where left(name,4)='july';
//这样也不可以
explain select * from emp where id+1=2;
//这样是可以的
explain select * from emp where id=2 +1;

八、索引覆盖索引

概念：mysql数据库用InnoDB索引构建一个B+trees，是一个聚族索引。聚族索引里面有一个主键索引，主键索引里面是带数据的，聚族索引的二级索引是基于当前的列去构建的一个B+trees，如果我们去查询的内容是在当前的辅助索引里面所包含的列，这种情况下不用回表根据主键再去查了，这样就是一个覆盖索引。
例子及经验：返回的数据，全部在当前的索引，使用索引来返回。

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//没有使用覆盖索引
explain select * from emp where name='z3';
//使用了覆盖索引
explain name  * from emp where name='z3';
explain name,age,job  * from emp where name='z3';
explain id,name,age,job  * from emp where name='z3';
//没有使用覆盖索引，需要回表
explain id,name,age,job,add_time  * from emp where name='z3';

九、索引不等于和空的条件

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//如果条件写了！=，要尽量把返回值写成覆盖索引的列
//如下：如果返回的列不是索引列的话只能走全文索引全盘扫描了
explain select * from emp where name !='z3';
//数据表里约束非空，不会使用索引
//数据表里未约束非空，是为null，如果写is not null，会使用索引
//正常数据表不会让空，会默认写入一个null的默认值
explain select * from emp where name is not null;

十、索引like条件查询

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//通配符在后可以，通配符在前不可以
explain select * from emp where name like 'jul%';
//如果通配符在前，那么建议用覆盖索引,提高查询性能
explain select name from emp where name like 'jul%';

十一、索引数字字符串类型转换

//依照姓名+年龄+工作给表建立的二级索引
alter table emp add index idx_emp_nameAgeJob(name,age,job);

//mysql对字符串和数字有个内部转换，会使用索引
explain select * from emp where id="1";
//当字符串列和数字比较，会将字符串列做一个类似于函数的转换，不会使用索引
explain select * from emp where name=2000;

表关联的字符集不同，不会使用索引

十二、执行计划explain之ID列

嵌套查询或子查询的时候，id会变成多个，就有ID执行顺序的一个选择。如果ID相同的，执行计划执行顺序从上到下执行。如果ID是不同的，比如递增，ID的值越大越先执行。

十三、执行计划explain之select-type列

select-type的值有以下几个：

simple：代表一条简单查询计划，不包含子查询，也没有union联合、合并的关键字。
primary：代表最外层的，包含子查询的最外层的查询。
subquery：代表有子查询。有子查询的结果。
derived：派生表
union：合并
union result：合并的一个结果

十四、执行计划explain之type列

值：性能最好到不好依次往下

const、system：代表系统表只有一条记录或仅仅只能查到一条数据sql或使用当前主键里面或唯一索引，而且只查到一条记录。
eq_ref：代表当前唯一索引，匹配的行只有一行。
ref：代表非唯一索引，匹配的行要么0行，要么很多行。
range：代表检索行的范围一般后面标注where，一般标注范围。
index：代表按照索引进行扫描。
all：代表全盘扫描。

十五、执行计划explain之相关列

possible_keys、key、key_len：执行sql的时候，可能用到的列，先看列有无索引，有索引要列出来，可能有索引用到。内部会做成本计算，决定是否用索引，哪个效率更高。一个是可能用到的索引，一个是真正使用到的索引，一个是当前索引列被使用的长度。

十六、执行计划explain之ref和extra相关列

ref：等值查询显示const，链接查询关键字条件表达式条件时显示function。
rows：当前sql执行扫描预估的记录数。
extra：覆盖索引会显示Using index、还有很多临时表，分组等的显示。

十七、SQL执行效率慢查询定位和分析

慢查询：

17.1：已经执行完成的SLQ

查看慢查询是否开启

show variables like 'slow_query_log';

慢查询的日志存放位置

show variables like 'slow_query_log_file';

慢查询的时间

show variables like 'long_query_time';

17.2：正在执行的SQL

查看当前sql执行的进程

//默认显示前100个
show processlist
//显示所有
show FULL processlist

可用工具（虚拟机里）：mysqldumpslow

//查询慢查询访问次数最多的前10条数据
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log

可用工具（虚拟机里）：pt-query-digest软件

//详细内容：一、总体统计结果；二、分组统计结构；三、详细结构，表，设计结果
pt-query-digest  --explain u=root,p=123456 /var/lib/mysql/localhost-slow.log

十八、mysql共享锁

共享锁：读锁，s锁（特点：可用多个事务读，不可以改）

begin
//锁的时候语句后加lock in share mode。
//锁的是id=3，这条记录，其它记录不受影响。
select * from lock1 where id= 3 lock in share mode
commit

begin
select * from lock1 where id=3 lock in share mode
//可以执行，不受影响
update lock1 set num =45 where id =4
commit

十九、mysql排它锁

排他锁：写锁，x锁（经验：java读与读可并发，写与写互斥，读与写互斥）
用索引列来上写锁的这行记录，其它的锁操作不了这行记录但可以操作其它行记录。如果不用带索引的列来上写锁，那么将会写锁会锁住整张表。
等值查询操作当前行加排它锁，查出几条记录就加几个锁，之后，其它操作对这几行数据返问不了了。

begin
//写锁与写锁互斥，写锁加上共享锁也不行。
//id可以锁，因为id是索引。
select * from lock1 where id= 3 for update
//当前number是没有索引的，对当前加锁的时候，会对整张表锁住。导致其它事务操作不了此表。
select * from lock1 where num=90 for update
update lock1 set num=45 where id =4
commit

二十、mysql意向锁

意向锁分：意向排它锁（lx）意向共享锁（ls）
意向锁都是表锁，lx和ls相互之间都兼容。
存在的意义：不同事务之间并发，提高当前返问效率。优化行里锁和表锁的性能优化问题。解决情景：有表锁后，如果想要再锁，如果发现意向锁和想要加的锁是互斥的，那么它就直接互斥阻塞了。就不需要再进行扫描，判断是否有锁的操作了。