mysql优化|MySQL到底怎么优化? mysql|数据库|database|Database_All

Mysql的优化

Mysql的优化
- 在数据库级别进行优化----
- 硬件级别的优化---->>
优化sql语句
Mysql优化细节
- - 优化select语句
  - where条款优化

Mysql的优化数据库的性能取决于数据库级别的多个因素,比如表的设计、查询语句和数据库配置设置。这些软件结构导致硬件级别的 CPU 和 I/O 操作,必须减少CPU 和IO的使用才可以尽可能提高效率;
在数据库级别进行优化---- 1,考虑表结构是否合理?----范式三原则
数据类型是否合适
2,是否设置了正确的索引?----索引重复字段最好不能有
3,每个表的存储引擎是否使用得当?(特别是，选择事务性存储引擎（如innodb）或非事务性存储引擎（如mysiam）对于性能和可伸缩性可能非常重要),
4,是否使用适当的锁定策略----表锁/行锁以便数据库可以并发操作;
5,用于缓存的所有内存区域大小是否合适?
这个可以调整mysql中缓存区的大小(bufferpoolmoren 64M),但又不能过大;
硬件级别的优化---->> 1,磁盘,磁盘查找数据需要一段时间,通常在10ms左右,因此理论上每秒会进行100次搜索,优化寻道时间是将数据分发到多个磁盘上;
或者可以更换高性能磁盘可以减少磁盘寻道时间比如使用固态硬盘;
2,cpu速度,更换为高性能cpu也可以加快处理速度;
3,内存带宽,当 CPU 需要的数据超过 CPU 缓存所能容纳的数据时，主内存带宽将成为瓶颈;

硬件级别的暂不在本次讨论范围

优化sql语句 1,查询语句是经常用的sql语句,优化查询的主要注意事项:

要使慢查询更快----->>检查是否可以添加索引,为子句中的列设置索引;
索引并不是越多越好,要考虑索引也占用磁盘空间;
对于使用联结和外键等引用不同的表的查询,可以使用explain语句来确定用到了哪些索引;
隔离和优化查询的任何部分
尽可能减少全表扫描的次数,尤其是对于大型表
可以通过定期分析表使得表系统的统计信息保持最新,优化sql的执行计划(select等)
如果性能问题不能通过某个基本准则轻松解决，可使用 EXPLAIN 分析sql语句并调整索引、子句、联接子句等来调查特定查询的内部详细信息。
调整MySQL用于缓存的内存区域的大小和属性。通过有效使用缓冲池、密钥缓存和 MySQL 查询缓存，重复查询的运行速度更快，因为结果会在第二次和后续时间从内存中检索。

Mysql优化细节平时读数据的频率一般高于写数据,所以第一个应该考虑优化select语句;
数据表的设计:
数据类型的优化---->>>

1,数据类型占用更小的通常更好(因为一次能读取的数据量更多,这意味着在读取相同数据量的时候通常需要更少的CPU处理周期;
比如使用mysql中的日期数据类型而不是用字符串来存储日期;
用整型存储ip而不是字符串,(mysql中有一个函数可以将IP转换为整型 INET_ATON())
2,简单就好,尽量避免使用null
3,尽量避免使用null,在mysql中nul=null 的结果是null,但是有时候会出现填写信息时用户没有填写,此时我们可以指定数据为’’ 空字符串来代替null

文章图片

4,合理设置主键
5,合理使用范式与反范式(根据实际情况来,可以适当有数据冗余)
6,字符集的选择
7,存储引擎的选择
8
优化select语句
1,解决慢查询的问题
首先是查看慢查询日志,找到慢查询产生的原因;

一般慢查询的优化需要从索引入手------尝试给需要的字段添加索引;
对于使用联接和外键等功能引用不同表的查询，索引尤其重要。

2,将常用的数据缓存下来;

调整MySQL用于缓存的内存区域的大小和属性。通过有效使用缓冲池、密钥缓存和 MySQL 查询缓存，重复查询的运行速度更快，

3,减少全表扫描的次数;

尽量使用触发cont ,index等的查询,避免回表查询;

4,定期使用analyze table语句保持统计信息保持最新;
where条款优化
删除不必要的括号:

((a AND b) AND c OR (((a AND b) AND (c AND d))))
-> (a AND b AND c) OR (a AND b AND c AND d)

1,索引合并优化

索引合并访问方法检索具有多个范围扫描的行，并将其结果合并为一个。此访问方法仅合并来自单个表的索引扫描，而不合并跨多个表的扫描。合并可以生成其基础扫描的并集、交集或交集并集。

我们先看一张表----->>

mysql> select * from test ; +----+----------+------+ | id | name| age| +----+----------+------+ |2 | 张三2| NULL | |4 | 张三4| NULL | |6 | 李四| NULL | |7 | 小七| NULL | |8 | 王五| NULL | | 10 | 李四10| NULL | | 13 | 赵13| NULL | | 18 | 王五18| NULL | +----+----------+------+ 8 rows in set (0.00 sec) #第一张表,有主键和索引name; mysql> show create table test \G *************************** 1. row *************************** Table: test Create Table: CREATE TABLE `test` ( `id` int NOT NULL, `name` varchar(8) COLLATE utf8mb4_general_ci DEFAULT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`id`), KEY `nameindex` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.00 sec)# 另一张表只有主键 mysql> show create table test3 \G *************************** 1. row *************************** Table: test3 Create Table: CREATE TABLE `test3` ( `id` int NOT NULL, `name` varchar(8) COLLATE utf8mb4_general_ci DEFAULT NULL, `age` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci 1 row in set (0.01 sec)

接下来我们查询 id 小于10 和 name 为小七的数据,如何才能够条查询效率??
第一张表----->>

mysql> explain select * from test whereid <10 and name="小七" \ G *************************** 1. row *************************** id: 1 select_type: SIMPLE table: test partitions: NULL type: index_merge possible_keys: PRIMARY,nameindex key: nameindex,PRIMARY key_len: 39,4 ref: NULL rows: 1 filtered: 100.00 Extra: Using intersect(nameindex,PRIMARY); Using where 1 row in set, 1 warning (0.00 sec)

【mysql优化|MySQL到底怎么优化?】

文章图片

官网给出的案例—>>

SELECT * FROM tbl_name WHERE key1 = 10 OR key2 = 20; SELECT * FROM tbl_name WHERE (key1 = 10 OR key2 = 20) AND non_key = 30; SELECT * FROM t1, t2 WHERE (t1.key1 IN (1,2) OR t1.key2 LIKE 'value%') AND t2.key1 = t1.some_col; SELECT * FROM t1, t2 WHERE t1.key1 = 1 AND (t2.key1 = t1.some_col OR t2.key2 = t1.some_col2);