SQL优化

11/22/2022 sql后端
(adsbygoogle = window.adsbygoogle || []).push({});

# 索引

空间换时间,索引的体积大约是表的1.5倍

一般设置在表的某些列上

两个列合起来索引是复合索引

索引只能放在硬盘中,所以磁盘的IO次数决定了查询性能

# 二叉树

B树(Balance Tree)

高度小于二叉树

P表示指针

B+树

# 应该加索引

  1. 经常被查询的列并且数据量比较大

  2. 自增主键默认索引

# 不需要加索引的列

数据离散值低(数据重复率高,例如性别)

# 测试

添加索引前,大约0.5s

添加索引后,大约25ms,差了20倍

默认添加的索引是BTREE

SELECT * from product WHERE title = "998083趾饭"

CREATE INDEX index_title on product(title)
1
2
3

# HASH索引

Hash函数: 值 ==> 数

Hash索引: 值 ==> 桶 ==> 数据行

应用条件更为苛刻

最常用的索引也就是B-tree索引和Hash索引,且只有MemoryNDB两种引擎支持Hash索引。

# 联合索引

两个列合起来是一个索引

# 联合索引的最左原则:

一定要按照顺序查询,最左侧列一定要出现

# 索引失效

  1. %%,双%like会使索引失效

  2. 右侧单%,索引正常

  3. or的左右两侧有一个没有索引会失效

  4. != > <

  5. 索引提高了查询的效率,但也降低了增删改的效率

  6. 尽量使用数字类型字段

# 执行计划

语句前加explain可以查看执行时是否有索引等信息

  1. rows 扫描行数,不准,一般值越小越好

EXPLAIN SELECT * from product WHERE title = "998105驼跺"

id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE product ref index_title index_title 767 const 1 Using where

# 5. 性能分析

show global status like "com_______"

# 慢查询日志

SHOW GLOBAL VARIABLES LIKE '%log%';

开启方法

# 6. Mysql查询优化方法 重点

思路:便面全表扫描

  1. 禁止用* 来查询,需要指定字段

  2. in的个数在1000个以内

  3. 查询一条数据使用limit 1

  4. 尽量使用inner join 避免使用left join

  5. != 用 > or < 替换

  6. between代替in

  7. exist 代替in

例如:优化前10s,优化后5s

SELECT * from  product WHERE title IN (SELECT title FROM product WHERE title = '24吻' or title = '25折')

SELECT * FROM product p WHERE EXISTS (SELECT 0 FROM product t WHERE p.id = t.id and (p.title = '24吻' or p.title ='25折'))
1
2
3
  1. left josin 代替in

  2. 尽量不适用mysql自己的函数

  3. 使用union all 代替 union

# 主从备份

双机热备就是使用MySQL提供的一种主从备份机制实现。所谓双机热备其实是一个复制的过程,复制过程中一个服务器充当主服务器,一个或多个服务器充当从服务。这个复制的过程实质上是从服务器复制主服务器上MySQL的二进制日志(bin-log),并在从服务器上还原主服务器上的操作。

# 要求

  1. MySQL的版本高于3.2

  2. 从服务器的MySQL版本不可以低于主服务器的数据库版本

# 操作

# 1. 主服务器授权

GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.11.40 IDENTIFIED BY '123456';
1

# 2. 同步数据

# 3. 配置主服务器

停止MySQL服务,修改主的my.ini,找到mysqId

[mysqld]
server-id=1
#是1~2^23-1内的唯一值且不能与B或其它slave服务中的配置相同

log-bin = bin_log
#日志文件以bin_log为前缀,如果不给log-bin赋值,日志文件将以#master-server-hostname为前缀
binlog-ignore-db= test
#日志文件跳过的数据库(可选属性 ),如果有多个数据库,可以重复配置这个属性
binlog-do-db= db_test
#日志文件操作的数据库(可选属性 ,默认所有数据库的相关操作都写入二进制日志文件) ,如果有多个数据库,可以重复配置这个属性
expire_logs_days=30
1
2
3
4
5
6
7
8
9
10
11

启动MySQL

# 4. 修改从服务器

修改/etc/my.cnf

server-id=2
#唯一并与主服务器上的server-id不同。
replicate-do-db=db_test
#复制操作要针对的数据库(可选,默认为全部),同样如果有多个数据库则可配置多次本属性。
expire_logs_days=30
#设置bin-log日志保存的天数
1
2
3
4
5
6
systemctl restart mysqld.service #重启 mysql
用mysql -u root -p 登录MySQL,并执行如下命令:
mysql>change master to \
->master_host='192.168.11.184',
->master_user='backup',
->master_password='123456';
systemctl restart mysqld.service #重启 mysql
1
2
3
4
5
6
7
  1. start slave;

  2. 检查状态show slave status \G;

必须两个都是yes,如果是connecting,关闭下防火墙试一下,进一步在主数据库插入一条数据后在从数据库检查下有没有数据

Slave_IO_Running: Yes Slave_SQL_Running: Yes

*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.11.184
                  Master_User: backup
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: bin_log.000001
          Read_Master_Log_Pos: 107
               Relay_Log_File: localhost-relay-bin.000004
                Relay_Log_Pos: 302
        Relay_Master_Log_File: bin_log.000001
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: security
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 107
              Relay_Log_Space: 513
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID:
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set:
            Executed_Gtid_Set:
                Auto_Position: 0
         Replicate_Rewrite_DB:
                 Channel_Name:
           Master_TLS_Version:
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59

# 失败的解决办法

lQLPJxbk9Cy0gznNAw7NA0iwDiEo4xB9fiMDeC8mxoCbAA_840_782

MySQL主从同步故障:Slave_SQL_Running:No 两种解决办法_Lucky@Dong的博客-CSDN博客 (opens new window)MySQL主从同步故障:Slave_SQL_Running:No 两种解决办法_Lucky@Dong的博客-CSDN博客 (opens new window)

# spring boot 读写分离

dynamic-datasource-spring-boot-starter: 基于 SpringBoot 多数据源 动态数据源 主从分离 快速启动器 支持分布式事务 (gitee.com) (opens new window)

如果数据库挂了一个,例如从服务器,会一直报错,项目也不能启动,需要注释不能使用的数据源

# MySQL备份