MySQL优化

mysql易学难精

Mysql的架构介绍

mysql简介

mysql概述

mysql优化

mysql5.5安装：

yum下载的mysql是5.1版本，但我们需要至少5.5版本

1、方法一

mysql开发者相关文档 https://dev.mysql.com/downloads/mysql/

下载5.5教程https://www.cnblogs.com/littlemonsterksn/p/10154662.html

wget是Linux系统中的一个下载文件的命令工具。对于经常要下载一些软件或从远程服务器恢复备份到本地服务器的用户来说是必不可少的工具。

没有wget就下一个：yum -y install wget

得到安装包：mysql-5.5.62-linux-glibc2.12-x86_64.tar.gz，解压后安装

方法二

在资料中找到了对应mysql5.5的rpm包，本地上传到Linux

pscp -r D:\MySQL\MySQL高级\mysql5.5.48 root@192.168.1.15:/opt

上传成功

mysqladmin --version有东西说明安装成功
service mysql start
mysql——出现欢迎词，开始按照提示设置密码
/usr/bin/mysqladmin -u root password 123456
mysql——出现报错
mysql -u root -p出现欢迎词
exit

top命令后，实时显示系统中各个进程的资源占用状况，即当前系统正在执行的进程的相关信息，包括进程ID、内存占用率、CPU占用率等。

可以参考：

https://www.cnblogs.com/peida/archive/2012/12/24/2831353.html

设置开机自启动mysql

chkconfig mysql on
chkconfig --list|grep mysql
ntsysv	命令用于设置系统的各种服务,报不是命令就下载-> yum install ntsysv

看到【*】mysql,表示开机后 自动启动mysql（带星号表示，开机自启动，反之相反）

修改配置文件位置：

将 /usr/share/mysql/my-huge.cnf拷贝到/etc下

cp /usr/share/mysql/my-huge.cnf /etc/my.cnf

service mysql stop
service mysql start
mysql -u root -p登录，建立数据库和表发现中文乱码

修改字符集和数据存储路径：

show variables like 'character%';
发现database（客户端）和server（服务器）都是用的latinl，所以乱码

在 /etc/my.cnf对应位置修改

修改完成后重新启动mysql,添加数据还是中文乱码原因：配置文件没错，否者不能重新启动mysql，乱码的原因是库在修改配置之前建立，因此装完mysql之后应该立即修改配置文件。

MySQL的安装位置——在linux下查看安装目录ps -ef|grep mysql

mysql主要配置文件：

mysql存储引擎：

查看命令：

两种引擎可否混用？

引擎是针对表，而不是针对库的，在建表的时候声明成不同的引擎即可。 比如： CREATE TABLE test_1 ( name varchar(20) DEFAULT NULL, year int(5) DEFAULT NULL ) ENGINE=MyISAM

两种存储引擎区别

阿里巴巴、淘宝用哪个

索引优化

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

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

索引主要分类：

1.主键索引 2.唯一索引3…普通索引 4.组合索引5.全文索引

索引CREATE创建

CREATE INDEX index_name ON  table_name (col_name,...)

单值索引
创建索引 索引名idx_表名_字段名 on 表名（字段名）
create index idx_user_name on user (name)
复合索引
创建索引 索引名（idx_表名_字段名字段名）on 表名（字段名，字段名）
create index idx_user_nameEmail on user (name,email)

数据库只支持一条查询语句只使用一个索引

ALTER命令添加数据表索引:

1.添加一个主键，意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

2.这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)

3.添加普通索引，索引值可出现多次。

ALTER TABLE tbl_name ADD INDEX index_name(column_list)

4.该语句指定了索引为FULLTEXT，用于全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)

删除索引

DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY

mysql300万记录以内没问题，超过就需要优化。

常见通用的Join查询

SQL执行顺序

手写

机读

join关联图：内连接、外连接（ 左(外)连接、右(外)连接、全(外)连接）、笛卡尔积

join 等同于 inner join，默认是内连接.

left join(左联接) 返回包括左表中的所有记录和右表中联结字段相等的记录。

right join(右联接) 返回包括右表中的所有记录和左表中联结字段相等的记录。

inner join(等值连接) 只返回两个表中联结字段相等的行。

select * from A inner join B on A.id = B.id;
select * from A join B on A.id = B.id;
select * from A,B where A.id = B.id;

MySQL不支持全外连接，但可以模拟，详细看《笛卡尔积-内连接-外连接》。

性能分析

MySql Query optimizer(mysql查询优化器)

mysql常见瓶颈（略）

Explain关键字解释：

https://www.cnblogs.com/dlp-527/p/11824467.html

其中重要的几个就是 type、key 、rows、extra。

用法：Explain+SQL语句

**type:**在表中找到所需行的方式，又称“访问类型”，是较为重要的一个指标。结果从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system: 这是 const 连接类型的一个特例，表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况下通常都是all或者index

const：用于用常数值比较 PRIMARY KEY 时。**使用唯一索引或者主键**，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描，当查询的表仅有一行时,使用 System。

ref：返回数据不唯一的等值查找就可能出现。

range：索引范围扫描

Index：索引全表扫描，把索引从头到尾扫一遍

all：最差，指全表扫描数据文件，然后在server层进行过滤返回符合要求的记录。最坏的情况,从头到尾全表扫描。百万记录以下不用管，百万以上需要优化。

type为all的地方，通常需要进行优化，一般需要达到 ref、eq_ref 级别，范围查找需要达到 range。

例子：

子查询是主键，where等值查询，为const，d1是别名也是衍生。

外面一层查询，是对常量进行查询只有一条记录的表，类型为system。

key:实际从 possible_keys 选择使用的索引，如果为 NULL,则没有使用索引，key为null时，说明没有使用到索引，需要调整索引。

rows:认为必须检查的用来返回请求数据的行数，即需要扫描的行数。

extra:额外的比较重要的信息，解决查询的详细信息。有Using filesort、Using temporary 的一定需要优化，根据rows可以直观看出优化结果。

using index：查询时不需要回表查询，直接通过索引就可以获取查询的数据。利用覆盖索引，无需回表即可取得结果数据，这种结果是好的。

using where：在查找使用索引的情况下，需要回表去查询所需的数据，需要在server层进行过滤。

using filesort：
不是利用磁盘的文件排序，而是通过内存排序。排序时无法使用到索引时，就会出现这个。
常见于order by和group by语句中。没有办法利用现有索引进行排序，需要额外排序，建议：根据排序需要，创建相应合适的索引。

using temporary：表示使用了临时表存储中间结果。临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来

id:表示查询中执行select子句或操作表的顺序，id值越大优先级越高，越先被执行

ref:表示MySQL在执行查询时使用的索引或常数值来匹配和访问表中的行

覆盖索引（Covering Index）,一说为索引覆盖。

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

理解方式二：索引是高效找到行的一个方法，一般来说，数据库能通过索引找到一个列的数据，而不必读取整个行，因为索引叶子节点存储了它们索引的数据；
当能通过读取索引就可以得到想要的数据，也就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

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

样例

执行顺序1:
第四行，id为4，select_type为union,说明第四个select是union里的第二个select,最先执行【select name,id,from t2】

执行顺序2:
第二行,id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,name from t1 where other_column = ''】

执行顺序3:
第三行，id为2，select列表中的子查询select_type为subquery,为整个查询中的第二个select。【select id from t3】

执行顺序4:
第一行，id为1，表示是union里的第一个select,select_type列的primary表示该查询为外层查询，table列被记为<derived3>，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name....】

执行顺序5:
id为null,代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

索引优化——索引分析

单表分析：

range类型查询字段后面的索引无效，出现using filesort->删除索引，重建索引（将>的字段去掉`）

双表分析：

其实将class和book对调一下位置查询也是可以的，不用再删除

三表分析：

类似双表，写左连接，就设右边的表为索引

后2行的type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

join语句优化结论

主键自带主键索引（不需要建立）

尽可能减少join语句中NestedLoop的循环总次数（不要join过多或嵌套）；

永远用小表驱动大表（圆规，不动的为小表，大的要IO访问）；

优先优化NestedLoop的内层循环（鸡蛋壳内加快）；

保证join语句中被驱动表（大表）上join字段已经被索引；

当无法保证被驱动表的join条件字段被索引 且内存资源充足的前提下，不要吝啬JoinBuffer的设置；

索引优化——索引失效

带头大哥不能少，中间兄弟不能断，索引列上少计算，范围之后全失效，百分like加右边，字符串里有引号。

带头大哥不能少（第一字段），中间兄弟不能断（断了索引只能用到前面的，后面用不上影响精确度，key_len和ref少了），索引列上少计算（动了容易索引失效，性能降低），范围之后全失效（索引用到范围字段为止，后面用不上索引），百分like加右边（若like '%字符%'必须用，则用覆盖索引解决），字符串里有引号（比如varchar类型,查询必须加单引号，如name=‘XXX’，否者索引失效）

最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配。
比如a = 1 and b = 2 and c > 3 and d = 4， 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
复合索引来说（a,b,c）其相当于3个索引（a），（a,b），（a,b,c）3个索引。
in可以乱序. 但会导致回表查询——using where
where和order by的字段可以联和使用索引吗?——不会，只有where使用了,若where用索引order by使用非索引字段，会导致filesort ,若where+in字段+order by非索引会导致索引失效

尽量的扩展索引，不要新建索引

例如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可。

尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，一般需要join的字段都要求是0.1以上，即平均1条扫描10条记录。

OR和AND

OR条件的联合（union），AND条件的相交（intersection），两种情况的联合及相交，做联合操作时（通常是 or），通常耗费CPU、内存资源。
如果是一个是单列索引，另一个非索引，那么会引发回表查询， explain 的 extra 显示 using where，此时先根据单列索引查到数据后，再回表进一步筛选非索引列。

使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

索引列排序

MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。
因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

参考：https://blog.csdn.net/qq_22238021/article/details/80922166

题目SQL：

若where后and连接。a1=XX and a2=XX and a4=XX order by a3,最终是用到a1a2，a3用于排序但不统计

分组之前必排序，顺序乱了会导致临时表的产生

一般性建议

like 后面是常量+%，虽是范围，但可落地，后面索引不失效

where a=3 and b like ‘kk%’ and c=4

口诀：

查询优化——order by 关键字优化

order by多个字段，有索引会使用索引排序，但其中任何一个字段没有索引，就会利用filesort进行辅助排序,会有额外的IO开销和内存开销.filesort目前有两种排序算法，双路排序和单路排序

双路排序与单路排序

双路排序查询数据时，只是将排序字段和记录ID加载到内存中，排好序后在根据id回表加载数据。 
单路排序会一次性将数据查出到内存中，占用内存大，但效率高，因为排好序后就已经是可以返回的结果了。

由于索引默认升序，用了一升一降违反了规则（要么同升，要么同降都是ok的）

提高Order By的速度

小总结

MySQL两种排序方式：文件排序、扫描有序索引排序
排序和查询能使用相同的索引

KEYa_b_c(a_b_c)
order by能使用索引最左前缀
-order by a
-order by a,b
-order by a,b,c
-order by a desc,b desc,c desc

如果where使用索引的最左前缀定义为常量，则order by能使用索引
-where a=const order by b,c
-where a=const and b=const order by c
-where a=const and b> const order by b,c

不能使用索引进行排序
order by a asc,b desc,c desc //排序不一致
where g= const order by b,c //丢失a索引
where a= const order by c //丢失b索引
where a= const order by a,d //d不是索引的一部分
where a in(...) order by b,c //对于排序来说，多个相等条件也是范围查询

查询优化——group by关键字优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where高于having，能写在where限定的条件就不要去having限定。

查询截取分析

1观察，至少跑1天，看看生产的慢SQL情况。

2.开启慢查询日志，设置阙值，比如超过5秒钟就是慢SQL，并将它抓取出来。

3.explain + 慢SQL分析

4.show profile

5.运维经理 或 DBA,进行SQL数据库服务器的参数调优

总结

1.慢查询的开启并捕获

2.explain + 慢SQL分析

3.show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况

4.SQL数据库服务器的参数调优。

Show profile :

是mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于sql 调优的测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果

查询优化——永远小表驱动大表类似全套循环NestedLoop

慢查询日志

用select sleep(4)命令，模拟超过超过3s的慢sql

mysqld配置:

slow_query_log=1

slow_query_log_file=/var/lib/mysql/atguigu-slow.log

long_query_time=3

log_output=FILE

日志分析工具mysqldumpslow

>mysqldumpslow --help

mysqldumpslow的帮助信息

s:是表示按照何种凡=方式排序
c:访问次数
I:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条的数据
g:后边搭配一个正则匹配模式，大小写不敏感的

批量数据脚本

插入数据1000万条，为防止压坏，每次插入50w条数据

建表插入数据后会报错，解决办法

1、随机产生字符串

substring(字符串，开始位置，长度)截取字符串

concat(str,str)字符串连接，如果有任何一个参数为null，则返回值为null

concat(str1, seperator,str2,seperator,…)返回结果为连接参数产生的字符串并且有分隔符，如果有任何一个参数为null，则返回值为null

2、随机产生部门编号

3、创建存储过程

emp表中插入数据insert_emp(开始编号，插入条数)

删除

delimiter;

drop procedure insert_em;

dept表中插入数据

4、调用存储过程

​ 插入10个部门

Show Profile

1、查看是否支持

2、开启功能

3、运行sql

4、查看结果

5、诊断sql

6、日常注意

有4条注意，出现一条必须优化！

全局查询日志

只能在测试环境用，不可用于生产

配置启用

编码启用

子查询和JOIN如何选择

MySQL中的子查询和JOIN查询在性能上有所不同，具体取决于数据量和查询的类型。以下是关于这两种查询方式的详细讨论：

JOIN查询：

• 优点：JOIN查询可以将多个表的数据通过关联条件联合起来，一次性处理多个表的记录，提高了查询效率。
• 缺点：如果在JOIN查询中使用的连接条件不当，可能会导致查询结果出错。另外，由于需要连接多个表，因此对于连接的表数量的上限有所限制，超过这个数量后，查询性能可能会降低。

子查询：

• 优点：子查询能够在一个查询中嵌套另一个查询，实现更为复杂的查询逻辑。
• 缺点：子查询会导致查询过程变得更复杂，因为它们通常在主查询执行之前就已经完成计算。如果子查询的结果集非常大，其执行速度可能相对较慢。

在某些情况下，如数据量较小（例如1000条数据），使用JOIN查询的时间会更短，而在数据量较大的情况下（例如1000000条数据），使用IN子查询的性能会更好。

然而，如果子查询的结果集过大，可能导致SQL语句过长，甚至无法执行。此外，如果存在分页需求，IN子查询也无法满足。

综上所述，JOIN查询在大多数情况下是更好的选择，尤其是在处理大量数据时。但是，如果子查询的结果集很小且不需要分页，那么子查询也是一个有效的解决方案。在实际应用中，应根据具体的业务需求和数据量来选择最合适的方法。

可参考：

https://blog.51cto.com/u_16099232/6712681

https://www.shence123.com/s/23262.html