mysql数据库

最新推荐文章于 2023-05-31 09:56:03 发布

活学编程思想

最新推荐文章于 2023-05-31 09:56:03 发布

阅读量447

点赞数

本文链接：https://blog.csdn.net/weixin_45080272/article/details/105947407

版权

mysql优化的8种方式：

Mysql数据库的优化

在这里插入图片描述
软优化一般是操作数据库即可,而硬优化则是操作服务器硬件及参数设置.

软优化：

1、查询语句优化
首先我们可以用EXPLAIN或DESCRIBE(简写:DESC)命令分析一条查询语句的执行信息.

explain select * from tb_item where title='中兴 U288 珠光白 移动3G手机'

显示:
在这里插入图片描述
说明：
其中会显示索引和查询数据读取数据条数等信息.
Key是Null说明没有使用索引
创建索引的话是在刚创建完表之后加上，或者在凌晨

面试题：如何确认一条查询有没有用到索引？

2、优化子查询
在MySQL中,尽量使用JOIN来代替子查询.因为子查询需要嵌套查询,嵌套查询时会建立一张临时表,临时表的建立和删除都会有较大的系统开销,而连接查询不会创建临时表,因此效率比嵌套子查询高。

SELECT order_id, 
(SELECT username FROM tb_user WHERE tb_user.id=tb_order.user_id) AS username
FROM tb_order

SELECT order_id,username FROM tb_order  INNER JOIN tb_user 
ON tb_order.user_id=tb_user.id

3、使用索引
不使用索引，使用profile查看执行时间。
SELECT * FROM tb_item WHERE title=‘华为 G9 青春版黑色移动联通电信4G手机双卡双待’
权限-准备-打开-查询-关闭表
创建索引，使用索引

4、分解表
对于字段较多的表,如果某些字段使用频率较低,此时应当,将其分离出来从而形成新的表,

5、中间表
对于将大量连接查询的表可以创建中间表,从而减少在查询时造成的连接耗时.

6、增加冗余字段
类似于创建中间表,增加冗余也是为了减少连接查询。

7.查询时，能不用* 就不用，尽量写全字段名。
8.SQL语句尽量用大写的
9.用IN来替换OR
10.索引不是越多越好，每个表控制在6个索引以内。范围where条件的情况下，索引不起作用，比如where value<100
11.大部分情况连接效率远大于子查询，但是有例外。当你对连接查询的效率都感到不能接受的时候可以试试用子查询
12.多用explain 和 profile分析查询语句学会用explain 和 profile判断是什么原因使你的SQL慢
13.有时候可以1条大的SQL可以分成几个小SQL顺序执行，分了吧，速度会快很多。
14.每隔一段时间用alter table table_name engine=innodb;优化表
15.连接时注意:小表 jion 大表的原则
16.查看慢查询日志，找出执行时间长的SQL进行优化
17.尽量避免使用order by
18.因为where子句后面的条件是执行顺序是从右到左，所以尽量把能过滤掉大部分数据的条件放在最后

硬优化：

1.硬件三件套（CPU，内存，磁盘）
1.配置多核和频率高的cpu,多核可以执行多个线程.
2.配置大内存,提高内存,即可提高缓存区容量,因此能减少磁盘I/O时间,从而提高响应速度.
3.配置高速磁盘或合理分布磁盘:高速磁盘提高I/O,分布磁盘能提高并行操作的能力。

2.优化数据库参数
优化数据库参数可以提高资源利用率,从而提高MySQL服务器性能.MySQL服务的配置参数都在my.cnf或my.ini,下面列出性能影响较大的几个参数.
· key_buffer_size:索引缓冲区大小
· table_cache:能同时打开表的个数
· query_cache_size和query_cache_type:前者是查询缓冲区大小,后者是前面参数的开关,0表示不使用缓冲区,1表示使用缓冲区,但可以在查询中使
用SQL_NO_CACHE表示不要使用缓冲区,2表示在查询中明确指出使用缓冲区才用缓冲区,即SQL_CACHE.
· sort_buffer_size:排序缓冲区

3.分库分表
因为数据库压力过大，首先一个问题就是高峰期系统性能可能会降低，因为数据库负载过高对性能会有影响。另外一个，压力过大把你的数据库给搞挂了怎么办？所以此时你必须得对系统做分库分表 + 读写分离，也就是把一个库拆分为多个库，部署在多个数据库服务上，这时作为主库承载写入请求。然后每个主库都挂载至少一个从库，由从库来承载读请求。
在这里插入图片描述
4.搭建mysql数据库缓存集群
如果用户量越来越大，此时你可以不停的加机器，比如说系统层面不停加机器，就可以承载更高的并发请求。然后数据库层面如果写入并发越来越高，就扩容加数据库服务器，通过分库分表是可以支持扩容机器的，如果数据库层面的读并发越来越高，就扩容加更多的从库。但是这里有一个很大的问题：数据库其实本身不是用来承载高并发请求的，所以通常来说，数据库单机每秒承载的并发就在几千的数量级，而且数据库使用的机器都是比较高配置，比较昂贵的机器，成本很高。如果你就是简单的不停的加机器，其实是不对的。所以在高并发架构里通常都有缓存这个环节，缓存系统的设计就是为了承载高并发而生。所以单机承载的并发量都在每秒几万，甚至每秒数十万，对高并发的承载能力比数据库系统要高出一到两个数量级。所以你完全可以根据系统的业务特性，对那种写少读多的请求，引入缓存集群。具体来说，就是在写数据库的时候同时写一份数据到缓存集群里，然后用缓存集群来承载大部分的读请求。这样的话，通过缓存集群，就可以用更少的机器资源承载更高的并发。
在这里插入图片描述

mysql去重

distinct
利用group by

mysql常见约束

主键约束
外键约束
唯一约束
非空约束
自增约束
默认值default

MySql索引

mysql函数

MySQL数据库中提供了很丰富的函数。MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数、格式化函数等。通过这些函数，可以简化用户的操作。例如，字符串连接函数可以很方便的将多个字符串连接在一起。

数学函数
字符串函数
日期和时间函数
条件判断函数
系统信息函数
加密函数
格式化函数
MySQL函数是MySQL数据库提供的内部函数。这些内部函数可以帮助用户更加方便的处理表中的数据。
MySQL函数包括数学函数、字符串函数、日期和时间函数、条件判断函数、系统信息函数、加密函数等。SELECT语句及其条件表达式都可以使用这些函数。同时，INSERT 、UPDATE、DELECT语句及其条件表达式也可以使用这些函数。
例如，表中的某个数据是负数，现在需要将这个数据显示为正数。这就可以使用绝对值函数。从上面可以知道，MySQL函数可以对表中数据进行相应的处理，以便得到用户希望得到的数据。这些函数可以使MySQL数据库的功能更加强大。

参考：http://c.biancheng.net/mysql/function/
https://www.cnblogs.com/luxd/p/9916677.html
https://blog.csdn.net/liurongsheng123/article/details/85960722

储存过程

SQL 查询的执行顺序是怎样的？

SQL 查询的执行顺序

sql按照以下顺序执行
在这里插入图片描述
说明：
先执行from + join——where——group by——having——select——order by——limit

但数据库引擎并不一定严格按照这个顺序执行 SQL 查询，因为为了更快地执行查询，它们会做出一些优化

如果你想要知道一个查询语句是否合法，或者想要知道一个查询语句会返回什么，可以参考这张图；
在涉及查询性能或者与索引有关的东西时，这张图就不适用了。

混合因素：列别名

有很多 SQL 实现允许你使用这样的语法：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY full_name

从这个语句来看，好像 GROUP BY 是在 SELECT 之后执行的，因为它引用了 SELECT 中的一个别名。但实际上不一定要这样，数据库引擎可以把查询重写成这样：

SELECT CONCAT(first_name, ' ', last_name) AS full_name, count(*)
FROM table
GROUP BY CONCAT(first_name, ' ', last_name)

这样 GROUP BY 仍然先执行。

数据库引擎还会做一系列检查，确保 SELECT 和 GROUP BY 中的东西是有效的，所以会在生成执行计划之前对查询做一次整体检查。

数据库可能不按照这个顺序执行查询（优化）

在实际当中，数据库不一定会按照 JOIN、WHERE、GROUP BY 的顺序来执行查询，因为它们会进行一系列优化，把执行顺序打乱，从而让查询执行得更快，只要不改变查询结果。

这个查询说明了为什么需要以不同的顺序执行查询：

SELECT * FROM
owners LEFT JOIN cats ON owners.id = cats.owner
WHERE cats.name = 'mr darcy'

如果只需要找出名字叫“mr darcy”的猫，那就没必要对两张表的所有数据执行左连接，在连接之前先进行过滤，这样查询会快得多，而且对于这个查询来说，先执行过滤并不会改变查询结果。

数据库引擎还会做出其他很多优化，按照不同的顺序执行查询，不过我并不是这方面的专家，所以这里就不多说了。

LINQ 的查询以 FROM 开头

LINQ（C#和 VB.NET 中的查询语法）是按照 FROM…WHERE…SELECT 的顺序来的。这里有一个 LINQ 查询例子：

var teenAgerStudent = from s in studentList
                      where s.Age > 12 && s.Age < 20
                      select s;

pandas 中的查询也基本上是这样的，不过你不一定要按照这个顺序。我通常会像下面这样写 pandas 代码：

df = thing1.join(thing2)      # JOIN
df = df[df.created_at > 1000] # WHERE
df = df.groupby('something', num_yes = ('yes', 'sum')) # GROUP BY
df = df[df.num_yes > 2]       # HAVING, 对 GROUP BY 结果进行过滤
df = df[['num_yes', 'something1', 'something']] # SELECT, 选择要显示的列
df.sort_values('sometthing', ascending=True)[:30] # ORDER BY 和 LIMIT
df[:30]

这样写并不是因为 pandas 规定了这些规则，而是按照 JOIN/WHERE/GROUP BY/HAVING 这样的顺序来写代码会更有意义些。不过经常会先写 WHERE 来改进性能，而且我想大多数数据库引擎也会这么做。

Mybatis 框架下 SQL 注入攻击的 3 种方式

Mybatis的SQL语句可以基于注解的方式写在类方法上面，更多的是以xml的方式写到xml文件。

Mybatis中SQL语句需要我们自己手动编写或者用generator自动生成。编写xml文件时，Mybatis支持两种参数符号，一种是#，另一种是$。比如：

<select id="queryAll"  resultMap="resultMap">  
	select * from news where id = #{id}
</select>

使用预编译，$使用拼接SQL。

Mybatis框架下易产生SQL注入漏洞的情况主要分为以下三种：
1、模糊查询

错误写法

Select * from news where title like ‘%#{title}%’

在这种情况下使用#程序会报错，新手程序员就把#号改成了$,这样如果java代码层面没有对用户输入的内容做处理势必会产生SQL注入漏洞。
正确写法：

select * from news where tile like concat(‘%’,#{title}, ‘%’)

2、in 之后的多个参数

in之后多个id查询时使用# 同样会报错，

Select * from news where id in (#{ids})

正确用法为使用foreach，而不是将#替换为$

id in
<foreach collection="ids" item="item" open="("separatosr="," close=")">
	#{ids} 
</foreach>

3、order by 之后

这种场景应当在Java层面做映射，设置一个字段/表名数组，仅允许用户传入索引值。这样保证传入的字段或者表名都在白名单里面。需要注意的是在mybatis-generator自动生成的SQL语句中，order by使用的也是$，而like和in没有问题。

#和$的区别

1、#{ }是预编译处理，MyBatis在处理#{ }时，它会将sql中的#{}替换为？，然后调用PreparedStatement的set方法来赋值，传入字符串后，会在值两边加上单引号，如上面的值“4,44,514”就会变成“ ‘4,44,514’ ”；
2、$ { }是字符串替换， MyBatis在处理$ { }时,它会将sql中的 $ { }替换为变量的值，传入的数据不会加两边加上单引号。
注意：使用${ }会导致sql注入，不利于系统的安全性！
SQL注入：就是通过把SQL命令插入到Web表单提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
常见的有匿名登录（在登录框输入恶意的字符串）、借助异常获取数据库信息等

应用场合：
1、#{ }：主要用户获取DAO中的参数数据,在映射文件的SQL语句中出现#{}表达式,底层会创建预编译的SQL；
2、$ { }:主要用于获取配置文件数据,DAO接口中的参数信息,当 $ 出现在映射文件的SQL语句中时创建的不是预编译的SQL,而是字符串的拼接,有可能会导致SQL注入问题.所以一般使用$接收dao参数时,这些参数一般是字段名,表名等,例如order by {column}。

注意：
${}获取DAO参数数据时,参数必须使用@param注解进行修饰或者使用下标或者参数#{param1}形式；
#{}获取DAO参数数据时,假如参数个数多于一个可有选择的使用@param。

参考：https://mp.weixin.qq.com/s/coCG7GZ3cGBRxQkGaKLsBg

mysql面试题参考：https://mp.weixin.qq.com/s/b1lVhSHvzvEBXk9U-r2zYg