MySQL到底怎么优化?

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 where  id <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)

官网给出的案例—>>

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);