SQL优化
(adsbygoogle = window.adsbygoogle || []).push({});
# 索引
空间换时间，索引的体积大约是表的1.5倍
一般设置在表的某些列上
两个列合起来索引是复合索引
索引只能放在硬盘中，所以磁盘的IO次数决定了查询性能
# 二叉树
B树(Balance Tree)
高度小于二叉树
P表示指针
B+树
# 应该加索引
- 经常被查询的列并且数据量比较大
- 自增主键默认索引
# 不需要加索引的列
数据离散值低(数据重复率高，例如性别)
# 测试
添加索引前，大约0.5s
添加索引后，大约25ms，差了20倍
默认添加的索引是BTREE
SELECT * from product WHERE title = "998083趾饭"
CREATE INDEX index_title on product(title)
# HASH索引
Hash函数： 值 ==> 数
Hash索引： 值 ==> 桶 ==> 数据行
应用条件更为苛刻
最常用的索引也就是B-tree索引和Hash索引，且只有Memory，NDB两种引擎支持Hash索引。
# 联合索引
两个列合起来是一个索引
# 联合索引的最左原则：
一定要按照顺序查询，最左侧列一定要出现
# 索引失效
- %%,双%like会使索引失效
- 右侧单%，索引正常
- or的左右两侧有一个没有索引会失效
- != > <
- 索引提高了查询的效率，但也降低了增删改的效率
- 尽量使用数字类型字段
# 执行计划
语句前加explain可以查看执行时是否有索引等信息
- 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查询优化方法 重点
思路：便面全表扫描
- 禁止用* 来查询，需要指定字段
- in的个数在1000个以内
- 查询一条数据使用limit 1
- 尽量使用inner join 避免使用left join
- != 用 > or < 替换
- between代替in
- 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折'))
- left josin 代替in
- 尽量不适用mysql自己的函数
- 使用union all 代替 union
# 主从备份
双机热备就是使用MySQL提供的一种主从备份机制实现。所谓双机热备其实是一个复制的过程，复制过程中一个服务器充当主服务器，一个或多个服务器充当从服务。这个复制的过程实质上是从服务器复制主服务器上MySQL的二进制日志（bin-log），并在从服务器上还原主服务器上的操作。
# 要求
- MySQL的版本高于3.2
- 从服务器的MySQL版本不可以低于主服务器的数据库版本
# 操作
# 1. 主服务器授权
GRANT FILE,SELECT,REPLICATION SLAVE ON *.* TO backup@192.168.11.40 IDENTIFIED BY '123456';
# 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
启动MySQL
# 4. 修改从服务器
修改/etc/my.cnf
server-id=2
#唯一并与主服务器上的server-id不同。
replicate-do-db=db_test
#复制操作要针对的数据库(可选，默认为全部)，同样如果有多个数据库则可配置多次本属性。
expire_logs_days=30
#设置bin-log日志保存的天数
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
- start slave;
- 检查状态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)# 失败的解决办法
MySQL主从同步故障：Slave_SQL_Running:No 两种解决办法_Lucky@Dong的博客-CSDN博客 (opens new window)MySQL主从同步故障：Slave_SQL_Running:No 两种解决办法_Lucky@Dong的博客-CSDN博客 (opens new window)
# spring boot 读写分离
如果数据库挂了一个，例如从服务器，会一直报错，项目也不能启动，需要注释不能使用的数据源