好吧整理的是再是太多了 , 一次放不下要分两回 ! 这可是我沥尽心血弄出来的 , 如果你看到了感觉还不错 , 那就给我点个赞吧 !

太多了

这篇里面主要涉及一些结构、锁 以及myisam 和 innoDB 的差异等

数据结构

B+Tree的数据结构

在算法搜索的时候会非常的高效，这种数据结构特别适合现在的硬盘这个存储的介质。

扩展：操作系统上的文件系统

也是使用的B+Tree的数据结构。这个不区分操作系统。

非聚(集)簇结构

myisam 的索引结构 (图就不截了)

image.png

总结 : 主索引是不能重复的,我们的索引下面的数据去保存的是innoDB(硬盘数据区的编号),找到索引对应的编号,通过这个编号区数据区找到这个数据,就把需要的数据返回给客户端

普通索引

image.png

总结 : 这个就是普通索引,索引的值是可以重复的,和主索引是一样的

聚(集) 簇结构

主索引 :

image.png

总结 : innoDB 索引的数据在一起的,所以我们创建 innoDB 与 myisam 是不一样的。innoDB必须创建主键 ID。必须要创建主键 ID，必须实现 auto_increment。 保存索引的 ID 是有序增长的，如果不是有序增加的，当你插入一条 ID 小于已经存在的 ID 的时候，这个时候 ID　就会去排序，后面的值就会在数据区进行移动，这个移动过程就会消耗IO，后面移动的数据越大，这个消耗就越大。 如果不创建，他会自动去找一个可以作为主键的值，如果没有他会隐藏创建一个主键的值

普通索引

image.png

普通索引下面的输进去保存的是主键的 ID , 这些 ID 会在进行普通索引搜索的时候 , 返回 , 返回之后再到主键索引去进行搜索 . 这就说明 , 如果 innoDB 不实用主键进行查询 , 就会出现二次遍历 , 第一次遍历普通索引 , 第二次遍历主键索引 普通索引 , 第二次遍历主键索引

总结 :

myisam 的有瘾结构都是一样的 , 主索引比其他索引就是多了一个不重复的功能 , 所以在使用 myisam 的时候 , 创建索引 , 可以任意 . 但是以数字 ID进行自增长的索引 , 暂用的长度与空间更小更小更少 , 还是建议使用 auto_increment innoDB 的主索引与普通索引完全不一样 . 主索引之遍历一次 , 普通索引必须进行两次遍历才能得到结果

没想到我也能整一篇理论这么多的东西 ~ 兄弟们不要紧张这些内容主要来源是百度

************************** 我是分割线 **************************

MyISAM 与 innoDB 引擎的区别

• 数据结构不一样

• MYISAM 不支持事物,INNODB 支持事物

• MYISAM 不支持外检,INNODB 支持外检

• MYISAM 支持全文搜索,INNODB 不支持全文搜索(在 5.6 以后支持)

• MYISAM 支持地理位置空间索引,INNODB 不支持

• MYISAM 支持压缩 , INNODB 不支持压缩

MYISAM 引擎与 InnoDB 引擎的备份与还原

数据库备份

数据备份需要工具

找到工具(Linux 中)

image.png

备份 innodb

mysqldump -u 用户名 -p 库名 表明 > 保存的绝对路径

image.png

还原操作

mysql -u 用户名 -p 库名 < 表的绝对路径

image.png

对数据库进行备份

备份方式一

mysqldump -u 用户名 -p 数据库名 > 保存的绝对路径

image.png

查看备份的数据内容： 备份的文件内容只有创建表与数据。并没有创建库的操作。所以使用这个还原的时候，一定要有库名。

备份方式二

mysqldump -u 用户名 -p -B test > 保存的绝对路径

image.png

查看备份的内容： 这样备份的内容有创建库的操作

还原数据库

mysql -u 用户名 -p < 库文件的绝对路径

image.png

总结 :

备份的文件是 SQL 文件 , 里面的内容就是sql语句 , 所以我们是可以修改里面的内容的(既然是备份,就不要轻易修改)
还原的时候,一定不要删除表 , 如果有同名的表或者库名 , 应该重命名 !

************************* 我是分割线

如果你看到了这里很好 , 你即将来到我们这篇文章的核心地带

MySql 中的执行计划

what ? 执行计划 ?

就是mysql自己生成的对于 SQL语句执行效果的说明结果。

作用

• 可以让我们对 SQL 语句的性能有详细的了解

• 可以让我们对 SQL语句使用了索引有了解

• 可以让我们对 SQL 语句扫描了多少数据有了解

基本语法

explain SQL 语句
重点 : 针对查询 SQL (select)

explain 详解

image.png

(⊙o⊙)… 这个有点看不清 换个方式

image.png

• const：扫描数据一行或者2行的时候。

• ref ：这个扫描的范围比range小。

• range：这个扫描的范围比index的小

• index：这个就是索引的扫描

• ALL：硬盘数据的扫描 type 越小越好 possible * keys : 可能用到的索引

• key : 用到的索引

• key len : 索引长度

• rows : 扫描的行 EXTRA :

• Using filesort : 这个参数出现就要优化 SQL 了 , 这个文件排序 , 效果最差

• Using temporary : 使用了临时表

• Using index : 这个是 索引覆盖 , 就是查询的时* 候 , 在索引上面找到了 , 不需要在查数据直接返回给客户端 !

• Using where : 数据过滤

这里讲一个分页优化 MySQL

limit n 20 :

加入有 3000w 数据,使用这种分页可定会挂掉这种分页只适合数据量小的时候操作

image.png

解决方案

where id>n limit 10;

image.png

两种特殊的索引结构

全文索引

其他几种索引,都是以字段值来进行索引的,全文索引事宜字段里面的内容来进行全文索引的 .全文索引能够所搜到一个字段值里面的某个单词

全文索引是 myisam 支持的 , innoDB 在 5.6 以后才支持

全文索引的使用方法:

select *from table_name where match(字段) against(搜索内容)

创建全文索引:

alter table table_name add fulltext  索引名(字段))

删除全文索引

alter table table_name  drop index 索引名
或
alter table table_name drop key 索引名

使用案例 :

创建表 :

image.png

插入数据 :

image.png

创建全文索引 :

alter table `fulltext` add fulltext full(content);

image.png

查询验证 :(搜索)

select * from `fulltext` where match(content) against('dream');

image.png

总结 : 全文索引对搜索的词汇自动过滤 , 会过滤一些高频词汇 , 就像这些词汇在每一个文章里面都会有很多 , 当我们进行搜索的时候 , 这些词相对来说是没有意义的 , 会被过滤掉 , 这个过滤是内部实现的 , 是不可控的 !

说明 : 全文索引使用较少 , 特别是汉字 , 搜索的时候必须使用第三方工具支持 (稍后再讲汉字搜索)

前缀索引

自己去百度吧 ~~~~

MySQL 中的其他功能

慢日志(执行超市就记录在日志里)

查看慢查询开关

image.png

slow_query_log :默认是关闭：OFF; 开启状态是ON。
    使用数字表示 : 1 开启     0 关闭
slow_query_log_file：慢日志的文件路径。
    不要修改,可能修改的地方没有权限

开启查询

set global slow_query_log = 1 ;

image.png

设置慢时间

set long_query_time = 1.1111;

image.png

查看慢日志文件的所在地

image.png

工作模式打开方式

tail  -f  文件路径

image.png

一直打开,有内容更新时,直接打印

案例 :

select * from myisam where content like '%毕业%';

image.png

查看日志

image.png

SQL 语句缓存

SQL 语句缓存 , 就是把 SQL 语句查询的结果缓存起来 , 下次在执行查询的时候 , 就会把这个结果返回给客户端 , 不用在进行查询了 !

查看是否开启

show variables like '%query_cache%';

• have_query_cache ：表示MYSQL服务器，支持缓存：YES

• query_cache_limit ：缓存单SQL的值最大结果是1048576（B） ： 1M

• query_cache_min_res_unit ：数据块是4K来划分。

• query_cache_size ：设置缓存的总大小：0就是没有给空间。

• query_cache_type ：ON就是已经支持了缓存。 总结。没有给大小，支持了也没有缓存。

查看缓存的状态！

image.png

• Qcache_free_memory ：没有使用的空间：

• Qcache_hits ：缓存的命中数量！

• Qcache_inserts ：缓存的插入次数

• Qcache_lowmem_prunes ：超过缓存的界限。数据量太大。

• Qcache_not_cached ：不缓存：

• Qcache_queries_in_cache ：缓存的SQL语句数量

开启缓存,设置大小

set global query_cache_size = 1024 * 1024 * 64;

说明 : 计算机大小数据设置最好是双数 , 至于为什么 我也不知道查百度吧

案例 :

image.png

插入了一条,保存了一条数量

使用SQL语句查询：

image.png

相同的 SQL , 命中增加一条 注意 : SQL 语句任意改变都是新的 SQL 语句 , 对于这个缓存来说 , 如 : where编程了 where (多了个空格),也是一个新的~ what 总结到这里我发现这东西 , 工作中根本用不到~~~

既然用不到 , 那就清空(人工清空缓存)

reset query cache;

锁

MySql 里面的锁有几种形式

• 表锁 : myisam 实现的表锁 , 表锁的性能非常好

• 行锁 : innoDB 实现的行锁 , 性能很好 表锁与行锁最大区别在于并发 , 行锁的并发比较大 . 功能 :

• 读锁 : 共享锁 : 因为本用户可以读 , 其他也可以读 , 大家一起都可以读 , 所以叫共享锁

• 写锁 : 排他锁 : 因为这个用户想写 , 其他用户就不可写 , 所以只能一个人写 其他锁

• 悲观锁 :

• 乐观锁 : 自己百度吧

表锁 myisam 引擎

• 照一张 myisam 引擎表

image.png

• 实现锁的功能

读锁 :
        开始 : lock tables table_name read;
        结束 : unlock tables;
写锁
        开始 : lock tables table_name write;
        结束 : unlock tables;

案例 : 读锁开始 :

image.png

当前用户开始查询

image.png

其他用户也可以使用

image.png

当前用户更新从挨揍

image.png

提示我们已经被锁定 , 不能进行更新 , 也就是不能修改

image.png

其他用户执行更新 :

image.png

出现进程等待 , 等待其他用户关闭锁 , 他就能更新成功 , 但是有等待时间 , 时间操作会报错 结束锁

image.png

总结 : 当前用户可以读 , 其他用户也可以读 当前用户不可以写 , 其他用户也不可以写

写锁案例 : 开始写锁

image.png

当前进程查询

image.png

其他进程查询

image.png

其他进程出现了查询等待 .

当前进程可以更新数据

image.png

其他进程更新

image.png

出现了 进程等待 解锁

image.png

行锁 innoDB 引擎

事物(transaction) 及其 ACID 属性

• 原子性(Atomicity) : 事物是一个原子操作单元 , 其对数据的修改 , 要么全部执行 , 要么全都不执行

• 一致性(Consistent) : 事物的开始和完成 , 数据都必须保持一致状态, 这意味着所有相关的数据规则都必须应用事物修改 , 以保持数据的完整性 ; 事物结束的时候 , 所有的内部数据结构也都必须是正确的 .

• 隔离性(isolation) : 数据库系统提供一定的隔离机制 , 保证事物在不受外部并发造作影响的"独立"环境执行 . 这意味着事物处理过程中的中间状态对外部是不可见的 , 反之亦然

• 持久性(Durable) : 事物完成之后 , 他对数据的修改是永久性的 , 即使出现系统故障也能保持 必须在是事物里面 :

• 开启事物 : begin

• 结束事物 : commit 读锁 :

SQL语句 + lock in share mode

写锁 :

SQL + for update

案例 : 开始事物 :

image.png

读锁

image.png

其他用户加读锁

image.png

当前用户修改

image.png

其他用户去修改

image.png

其他用户修改 , 出现了进程等待 结束事物

image.png

总结 :

当前用户开启读锁的时候 , 其他用户也可以使用读锁 . 当前用户可以修改数据 , 其他用户是不可以修改的 .

写锁

开始事物

image.png

当前用户写锁

image.png

其他用户使用共享锁尝试一下 :

image.png

其他用户不能使用共享锁 当前用户修改内容

image.png

其他用户不能修改内容 结束事物

image.png

同一个表中 , 不同的数据

开启事物

image.png

对一个行,数据进行写锁

image.png

其他用户 , 操作其他行的数据

image.png

对其他行的数据操作是成功的 证明其使用的是行锁 当用户操作其他数据 :

image.png

操作成功 其他用户查看当前用户操作的数据 , 出现了进程等待 总结 : 当前用户修改过的数据 , 都会加 排他锁 结束事物 :

image.png

总结 : innoDB 确实是行锁 , 但是innoDB 也可以实现表锁 说明 : innoDB 的锁是加载索引上面的 , 如果你使用的数据不是索引数据 , 就会出现表锁 . innoDB 的行锁可以自己解决锁冲突 , 而表锁是 mysql 实现的 , 与 innoDB 没有关系 , 如果出现锁冲突 , 就没有办法自己解决 !

锁冲突

当前用户第一条数据需要 ID1 , 第二条数据需要 ID10 其他用户第一条数据需要 ID10 , 第二条数据需要 ID1 所以写代码的时候 加锁一定要有顺序 , 不要随便跳转位置

验证表锁

• 验证表结构

image.png

这里 id 和 passwd 添加了 索引

• 对 name 进行了加锁擦欧洲哦 开始事物

image.png

对 xiao3333 加写锁

image.png

其他用户对 xiao3333 加排他锁

image.png

对这个值加排他锁 , 不成功 , 是正常的 , 符合预期 其他用户对 xiao11111 家排他锁

image.png

这个值对没有加排他锁 , 也不成功 , 就验证了 , 当前用户是加的表锁 结束任务 :

image.png

name : 没有添加索引

对 passwd 字段进行验证 开始事物

image.png

对 28ef7f90c15dc88dfe68d2912d22c8a7 值进行加排它锁

image.png

其它用户对 28ef7f90c15dc88dfe68d2912d22c8a7 值进行加排它锁

image.png

其他用户加锁不成功 , 符合预期 其它用户对 ff766a6f53fd4e4f4d615f44f59bfee9 值进行加排它锁：

image.png

加锁成功,符合预期 提交事物 :

image.png

innoDB : 引擎只有在有索引的数据字段上才会实现行锁 , 在非索引字段是表锁 , 所以索引对于 innoDB 是非常重要的 innoDB 引擎 ,尽量不要使用范围 , 如果你使用 id > 1000 加排他锁 , 就表示把 id > 1000 的所有值进行了加锁

PHP 实现锁+压力测试

需求 : 把访问次数 , 记录到表里面

• 创建一张表 :

image.png

预热 :

image.png

• 代码实现功能

image.png

• 访问

image.png

• 查看数据

image.png

• 并发测试 这里有个压力测试软件 : ab 这个 ab 软件 , 就是 Apache 自带的

image.png

ab -n number -c number url地址 -n 总的访问量 -c 并发访问量

• 使用 ab 进行测试

image.png

查看结果

image.png

结果不理想 , 有差异

再次测试

image.png

查看结果;

image.png

还是有差异

• 给代码加锁

image.png

再次测试

image.png

查看数据 :

image.png

说明 : 加锁之后 , 我们的并发 , 就是串行执行的 . 加锁成功的就操作数据 , 加锁不成功的 , 就等待加锁成功之后继续操作 . (访问速度慢) 注 : 还有文件锁 , 通常是单服务器使用 , 自行百度吧

作者：记住你姓李
链接：https://www.jianshu.com/p/fe8b144627c1