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

这篇里面主要涉及一些结构、锁 以及myisam 和 innoDB 的差异等
数据结构
B+Tree的数据结构
在算法搜索的时候会非常的高效,这种数据结构特别适合现在的硬盘这个存储的介质。
扩展:操作系统上的文件系统
也是使用的B+Tree的数据结构。这个不区分操作系统。
非聚(集)簇结构
myisam 的索引结构 (图就不截了)

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

总结 : 这个就是普通索引,索引的值是可以重复的,和主索引是一样的
聚(集) 簇结构
主索引 :

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

普通索引下面的输进去保存的是主键的 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 中)

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

还原操作
mysql -u 用户名 -p 库名 < 表的绝对路径

对数据库进行备份
备份方式一
mysqldump -u 用户名 -p 数据库名 > 保存的绝对路径

查看备份的数据内容: 备份的文件内容只有创建表与数据。并没有创建库的操作。所以使用这个还原的时候,一定要有库名。
备份方式二
mysqldump -u 用户名 -p -B test > 保存的绝对路径

查看备份的内容: 这样备份的内容有创建库的操作
还原数据库
mysql -u 用户名 -p < 库文件的绝对路径

总结 :
备份的文件是 SQL 文件 , 里面的内容就是sql语句 , 所以我们是可以修改里面的内容的(既然是备份,就不要轻易修改)
还原的时候,一定不要删除表 , 如果有同名的表或者库名 , 应该重命名 !
************************* 我是分割线
如果你看到了这里很好 , 你即将来到我们这篇文章的核心地带
MySql 中的执行计划
what ? 执行计划 ?
就是mysql自己生成的对于 SQL语句执行效果的说明结果。
作用
- 可以让我们对 SQL 语句的性能有详细的了解
- 可以让我们对 SQL语句使用了索引有了解
- 可以让我们对 SQL 语句扫描了多少数据有了解
基本语法
explain SQL 语句
重点 : 针对查询 SQL (select)
explain 详解

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

- 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 数据,使用这种分页可定会挂掉这种分页只适合数据量小的时候操作

解决方案
where id>n limit 10;

两种特殊的索引结构
全文索引
其他几种索引,都是以字段值来进行索引的,全文索引事宜字段里面的内容来进行全文索引的 .全文索引能够所搜到一个字段值里面的某个单词
全文索引是 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 索引名
使用案例 :
创建表 :

插入数据 :

创建全文索引 :
alter table `fulltext` add fulltext full(content);

查询验证 :(搜索)
select * from `fulltext` where match(content) against('dream');

总结 : 全文索引对搜索的词汇自动过滤 , 会过滤一些高频词汇 , 就像这些词汇在每一个文章里面都会有很多 , 当我们进行搜索的时候 , 这些词相对来说是没有意义的 , 会被过滤掉 , 这个过滤是内部实现的 , 是不可控的 !
说明 : 全文索引使用较少 , 特别是汉字 , 搜索的时候必须使用第三方工具支持 (稍后再讲汉字搜索)
前缀索引
自己去百度吧 ~~~~
MySQL 中的其他功能
慢日志(执行超市就记录在日志里)
查看慢查询开关

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

设置慢时间
set long_query_time = 1.1111;

查看慢日志文件的所在地

工作模式打开方式
tail -f 文件路径

一直打开,有内容更新时,直接打印
案例 :
select * from myisam where content like '%毕业%';

查看日志

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就是已经支持了缓存。 总结。没有给大小,支持了也没有缓存。
查看缓存的状态!

- 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;
说明 : 计算机大小数据设置最好是双数 , 至于为什么 我也不知道查百度吧
案例 :

插入了一条,保存了一条数量
使用SQL语句查询:

相同的 SQL , 命中增加一条 注意 : SQL 语句任意改变都是新的 SQL 语句 , 对于这个缓存来说 , 如 : where编程了 where (多了个空格),也是一个新的~ what 总结到这里我发现这东西 , 工作中根本用不到~~~
既然用不到 , 那就清空(人工清空缓存)
reset query cache;
锁
MySql 里面的锁有几种形式
- 表锁 : myisam 实现的表锁 , 表锁的性能非常好
- 行锁 : innoDB 实现的行锁 , 性能很好 表锁与行锁最大区别在于并发 , 行锁的并发比较大 . 功能 :
- 读锁 : 共享锁 : 因为本用户可以读 , 其他也可以读 , 大家一起都可以读 , 所以叫共享锁
- 写锁 : 排他锁 : 因为这个用户想写 , 其他用户就不可写 , 所以只能一个人写 其他锁
- 悲观锁 :
- 乐观锁 : 自己百度吧
表锁 myisam 引擎
- 照一张 myisam 引擎表

- 实现锁的功能
读锁 :
开始 : lock tables table_name read;
结束 : unlock tables;
写锁
开始 : lock tables table_name write;
结束 : unlock tables;
案例 : 读锁开始 :

当前用户开始查询

其他用户也可以使用

当前用户更新从挨揍

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

其他用户执行更新 :

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

总结 : 当前用户可以读 , 其他用户也可以读 当前用户不可以写 , 其他用户也不可以写
写锁案例 : 开始写锁

当前进程查询

其他进程查询

其他进程出现了查询等待 .
当前进程可以更新数据

其他进程更新

出现了 进程等待 解锁

行锁 innoDB 引擎
事物(transaction) 及其 ACID 属性
- 原子性(Atomicity) : 事物是一个原子操作单元 , 其对数据的修改 , 要么全部执行 , 要么全都不执行
- 一致性(Consistent) : 事物的开始和完成 , 数据都必须保持一致状态, 这意味着所有相关的数据规则都必须应用事物修改 , 以保持数据的完整性 ; 事物结束的时候 , 所有的内部数据结构也都必须是正确的 .
- 隔离性(isolation) : 数据库系统提供一定的隔离机制 , 保证事物在不受外部并发造作影响的"独立"环境执行 . 这意味着事物处理过程中的中间状态对外部是不可见的 , 反之亦然
- 持久性(Durable) : 事物完成之后 , 他对数据的修改是永久性的 , 即使出现系统故障也能保持 必须在是事物里面 :
- 开启事物 : begin
- 结束事物 : commit 读锁 :
SQL语句 + lock in share mode
写锁 :
SQL + for update
案例 : 开始事物 :

读锁

其他用户加读锁

当前用户修改

其他用户去修改

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

总结 :
当前用户开启读锁的时候 , 其他用户也可以使用读锁 . 当前用户可以修改数据 , 其他用户是不可以修改的 .
写锁
开始事物

当前用户写锁

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

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

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

同一个表中 , 不同的数据
开启事物

对一个行,数据进行写锁

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

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

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

总结 : innoDB 确实是行锁 , 但是innoDB 也可以实现表锁 说明 : innoDB 的锁是加载索引上面的 , 如果你使用的数据不是索引数据 , 就会出现表锁 . innoDB 的行锁可以自己解决锁冲突 , 而表锁是 mysql 实现的 , 与 innoDB 没有关系 , 如果出现锁冲突 , 就没有办法自己解决 !
锁冲突
当前用户第一条数据需要 ID1 , 第二条数据需要 ID10 其他用户第一条数据需要 ID10 , 第二条数据需要 ID1 所以写代码的时候 加锁一定要有顺序 , 不要随便跳转位置
验证表锁
- 验证表结构

这里 id 和 passwd 添加了 索引
- 对 name 进行了加锁擦欧洲哦 开始事物

对 xiao3333 加写锁

其他用户对 xiao3333 加排他锁

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

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

name : 没有添加索引
对 passwd 字段进行验证 开始事物

对 28ef7f90c15dc88dfe68d2912d22c8a7 值进行加排它锁

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

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

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

innoDB : 引擎只有在有索引的数据字段上才会实现行锁 , 在非索引字段是表锁 , 所以索引对于 innoDB 是非常重要的 innoDB 引擎 ,尽量不要使用范围 , 如果你使用 id > 1000 加排他锁 , 就表示把 id > 1000 的所有值进行了加锁
PHP 实现锁+压力测试
需求 : 把访问次数 , 记录到表里面
- 创建一张表 :

预热 :

- 代码实现功能

- 访问

- 查看数据

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

ab -n number -c number url地址 -n 总的访问量 -c 并发访问量
- 使用 ab 进行测试

查看结果

结果不理想 , 有差异
再次测试

查看结果;

还是有差异
- 给代码加锁

再次测试

查看数据 :

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