Mysql高级（一）

最新推荐文章于 2023-09-21 14:47:26 发布

Continue。

最新推荐文章于 2023-09-21 14:47:26 发布

阅读量253

点赞数 1

分类专栏： MySQL 文章标签： mysql 数据库 sql 索引数据结构

本文链接：https://blog.csdn.net/weixin_45787495/article/details/109642859

版权

MySQL 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

尚硅谷mysql高级部分学习

一、mysql Linux版的安装和配置

ps -ef|grep mysql #查看mysql安位置

在这里插入图片描述

cd /usr/share/mysql/my-huge.cnf #mysql配置文件的位置

一般会保留原配置文件的位置和配置，将配置文件拷贝一份，拷贝到/etc/my.cnf

在这里插入图片描述
修改数据库字符集
在数据库插入中文数据时，出现乱码问题，一般是字符集不匹配造成的，我们要将其修改为utf-8

先通过一下两个指令来查看字符集

show variables like ‘character%’;
show variables like ‘%char%’;

在这里插入图片描述
对配置文件进行修改

show engines #看你的mysql现在提供什么存储引擎
show variables like ‘%storage_engine%’; #看你的mysql当前默认的存储引擎

二、索引优化分析

1、性能下降SQL慢，执行时间长，等待时间长
原因：

1、查询语句写的烂
2、索引失效
3、关联查询太多join(设计缺陷或不得已的需求)
4、服务器调优及各个参数设置(缓冲、线程数等)

常见的Join查询
SQL的执行顺序
手写
在这里插入图片描述
机读

SQL Join连接图

索引
1、索引是帮助MySQL高效获取数据的数据结构。索引就是数据结构。
2、索引的目的在于提高查询效率，可以类比字典
3、可以将索引理解为排好序的快速查找数据结构

数据库中除了数据本身之外，还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级的查找算法，这种数据结构就是索引

4、一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件的形式存储在磁盘中。

优势

类似于大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。
通过索引列对数据进行排序，降低数据排序的成本，降低CPU的消耗。

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间的。
虽然索引大大的提高了检索速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。都会调整因为更新所带来的键值变化后的索引信息

MySQL索引分类

1、单值索引

单值索引，即一个索引只包含单个列，一个表可以有多个单值索引

create index 索引名(idx_表名_字段名) on 哪个表(哪个字段)
以user表的name字段为例
create index idx_user_name on user(name)

2、唯一索引
唯一索引的值必须唯一，但允许有空值

3、复合索引
复合索引,给某张表的多个字段建一个索引,即一个索引包含多个列

create index 索引名(idx_表名_字段名) on 哪个表(哪个字段)

以user表的name、email字段为例
create index idx_user_nameEmail on user(name,email)

4、基本语法
在这里插入图片描述

MySQL索引结构

BTree索引 #重点，其它三个先作为了解
Hash索引
full-text全文索引
R-Tree索引

在这里插入图片描述

哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单更新了记录还会更新索引
Where条件里用不到的字段不创建索引
单键/复合索引的选择问题（在高并发下倾向创建复合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或分组的字段

哪些情况不要创建索引

表记录太少
经常增删改的表
数据重复且分布平均的表字段，因此应该只为最常查询和最经常排序的数据建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

三、性能分析

Explain(查看执行计划) 重点
使用EXPLAIN关键字可以模拟优化服务器执行SQL查询语句，从而知道My’SQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

Explain能干嘛？
在这里插入图片描述
对于group by和order by
要么就别建索引，如果要建索引那么使用group by 和order by的时候索引的顺序和个数一定要按建的索引顺序和个数来，否则特别容易产生文件内排序。

覆盖索引
官方解释：

就是select的数据列只用从索引中就能够获得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所创建的索引覆盖

自己理解：

即建了一个索引，在查询的时候使用到了该索引，且个数和顺序相同。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要列，不可select *。
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

四、索引优化

1、单表
存在问题：sql语句范围查询会导致索引失效

没有建立索引时查询，虽然可以查出我们想要的结果，但type为全表查询，Extra存在文件排序的问题
在这里插入图片描述

建立索引

ALTER TABLE article ADD INDEX idx_article_ccv(category_id,comments,views);

建立索引后查询
在这里插入图片描述
发现仍然存在文件排序的问题

我们已经建立了索引为什么不起作用呢？

这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments则再排序views。当comments字段在联合索引里处于中间位置，因为comments>1条件是一个范围值，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

尝试将范围查询的>改为=，查询结果如下
在这里插入图片描述
虽然解决了问题，但结果并不是我们想要的，说明建立的索引结合我们目前的诉求并不是最合适的，因为中的有一个大于1 导致后面的索引用不上

解决
给中间的范围查找的comments字段建立索是导致索引失效的根本原因，可通过如下方式解决

CREATE INDEX idx_article_cv ON article(category_id,views);

在这里插入图片描述
2、两表
表一

表二

未建索引两表连接的结果

未建索引EXPLAIN的结果

结论：type有All

问题：索引建立在哪张表中？

以左连接为例，这是由左连接的特性决定的，LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。

结论：左连接相反加，右连接也是相反加

3、三表
在二表的基础上再加一个phone表
在这里插入图片描述
未建索引EXPLAIN的结果
可以看到表多了以后MySQL为了提高性能，使用了连接缓存，且type都为All。

问题：索引建立在哪张表中？
与两表一样左连接相反加，右连接也是相反加，只不过三表多加了一个索引
在这里插入图片描述
大结论
Join语句的优化

尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小的结果集驱动大的结果集”以书为例，书籍类数量少于书的数量。
优先优化NestedLoop的内层循环。
保证Join语句中被驱动的表上Join条件字段已经被索引。
当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下，不要太吝啬JoinBuffer的设置

五、索引失效（应该如何避免）

1、建表
创建表建立并建立复合索引
在这里插入图片描述
2、案例（索引失效）

1、全值匹配我最爱

结论：如建立的索引为name,age,pos,如果没有了开头的name，只有单独的age或pos或只有age和pos都不能使用到索引。（应遵守最佳做前缀法则）

2、最佳左前缀法则
在这里插入图片描述

如果索引了多列，要遵守，最左前缀法则。指的是查询从索引的最左前列开始且并不跳过索引中的列。（带头大哥不能死，中间兄弟不能断【中间兄弟断了就如同空中楼阁，只有带头大哥的会使用到部分索引，空中楼阁部分索引会失效】）

3、不在索引列上做任何操作(计算、函数、类型转换)，会导致索引失效而转向全表扫描，索引列上无计算
在这里插入图片描述

4、存储引擎不能使用索引中范围条件右边的列，范围之后全失效
在这里插入图片描述
5、尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *

6、mysql在使用不等于(!=或<>)的时候无法使用索引会导致全表扫描

7、is null,is not null也无法使用索引

8、like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作
在这里插入图片描述

结论: like百分加右边

问题：解决like %字符串%时索引不被使用的方法
推荐使用覆盖索引的方式解决索引失效问题
在这里插入图片描述

注意id也是索引

9、字符串不加单引号索引失效，字符串里有引号
在这里插入图片描述

10、少用or，用它来连接时会索引失效
在这里插入图片描述
11、group by和order by
group by表面上叫分组，分组之前比排序，所以说group by和order by其排序的法则和索引优化的原则几乎是一致的。

在索引分析的时候定值为常量，范围之后是失效，最终看排序，一般order by是给一个范围
group by基本上都需要进行排序，会有临时表产生

一般性建议
1、对于单键索引，尽量选择针对当前query过滤性更好的索引
2、在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
3、在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
4、尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

优化总结口诀
全值匹配我最爱，最左前缀要遵守
带头大哥不能死，中间兄弟不能断
索引列上少计算，范围之后全失效
LIKE百分写最右，覆盖索引不写星
不等空值还有or，索引失效要少用
VARCHAR引号不可丢，SQL高级也不难

六、查询截取分析

一、查询优化

1、永远小表驱动大表，即小的数据集驱动大的数据集。
在这里插入图片描述

2、order by关键字优化

1)、mySQL支持两种方式的排序，FileSort和Index，Index效率高。它指mySQL扫描索引本身完成排序。FileSort方式效率低。

2)、 order by满足以下两种情况，会使用Index方式排序:

order by语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列

3)、尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀法则

4)、如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

5)、提高order by的速度
5.1、使用order by时select *是一个大忌，只查询需要的字段，这点非常重要，在这里的影响是：

5.1.1、当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法–单路排序，否则用老算法–双路排序
5.1.2、两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是使用单路排序算法的风险会更大一些，所以要提高sort_buffer_size

5.2、尝试提高sort_buffer_size，不管哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每一个进程的。

5.3尝试提高max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率增大，明显症状是高的I/O磁盘活动和低的处理器使用率。
在这里插入图片描述
3、group by关键字优化
1)、group by实质是先排序后进行分组，遵照索引建的最佳左前缀
2)、无法使用索引列时，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
3)、where高于having，能写where限定的条件就不要去having限定了。