MySQL优化一

作者: 记住你姓李 | 来源:发表于2017-10-23 08:41 被阅读182次

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

太多了

这篇里面主要涉及一些结构、锁以及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 没有关系 , 如果出现锁冲突 , 就没有办法自己解决 !