sql调优

一台64G内存的服务器QPS可以达到9W，TPS（事务）可以达到5K，每个TPS大约包含18个QPS.只读的话QPS可以达到30~40万.阿里云有相关测试工具、测试方法、测试结果。

官方文档：mysql.com

1、根据执行计划进行调整：sql语句前使用explain

主要看：

type（MySQL :: MySQL 5.7 Reference Manual :: 8.8.2 EXPLAIN Output Format）

key

Extra

2、海量数据存储

索引：使用的B+树，为什么使用B+树？

因为mysql数据存在磁盘，索引也存在磁盘，查询时，索引文件会加载到内存中，当索引较大时，不可能把索引一次性加载到内存中，所以要分批加载，这就涉及到局部性原理和磁盘预读。

磁盘预读：内存跟磁盘交互的时候，基本逻辑单位是页，大小一般为4K或8K，在进行数据读取的时候，一般读取的是页的整数倍

下面是B-树：三层只能存4000条数据

下面是B+树： 三层可以存40960000，即4千万数据

 *****做数据迁移的时候一定要把索引关掉。

******************************回表、索引覆盖、最左查询**************************

 

 *****************************mysql 组件*********************

 *****频繁修改的字段不适合做索引

 ****************************************主从复制*************************

 使用MTS解决主从延时问题

用shardingsphere处理读写分离及分库分表

*******************************************分库分表***************************************

水平切分：不同的行存在不同表中

垂直切分：不同的列存在不同表中

**分片键：查询条件中尽可能带分键字段，数据库根据分键字段确定哪个分表

******************************************单机事务******************************************

 

**************************************上面所讲的：隔离性*****************************************

隔离级别分为四种：读未提交、读已提交（RC）、可重复读（RR）、串行化。Mysql默认使用的是RR，Orcle默认使用的是RC。

常用的数据操作有：
读读：不会存在并发问题，不需要并发控制

读写：存在并发问题，一般我们想到的解决方法是锁，但锁的效率较低，所以要使用MVCC的效率来解决，效率比较高。

写写：存在并发问题，一般我们想到的解决方法是锁，但锁的效率较低，所以要使用MVCC的效率来解决，效率比较高。

下面说下MVCC：多版本并发控制，解决数据并发读写控制。

 隐藏字段操作示例：

 

 readview操作示例：

 ***************************************幻读问题*********************************

待学习

***************************************锁*****************************************

***************************************性能监控*****************************************

1、profiling：常用的较简单的分析（即将被淘汰，5.*版本仍旧可以使用） 

 

 2、performance schema

数据库中有此数据库：performance_schema

打开my.cnf进行修改配置

存储在内存中，不持久化

记录数据运行过程中对资源消耗的监控

3、show processlist：查看当前连接数量。

 ***********************************连接池*******************************

目前比较好的是阿里的druid

********************************schema与数据类型优化**************************

数据类型的优化

• 尽量使用整形而不使用字符串，ip可通过mysql自带的INAT_NTOA，INAT_ATON进行整型和字符串之间的转换。
• 能不使用null的就不要使用：不好优化，不好比较。
• 加密密码等长度基本固定的字段，使用char(最大255)比varchar效率更高
• 一般不用text和blob，内容太多可存储在文件中，数据库中存储文件地址。
• datetime（8个字节）（0000-01-01~9999-12-31）和datestamp（4个字节）（1970-0101~2038-01-19）。，date（3个字节）。datestamp受时区影响。常用的是datestamp。
• 使用枚举enum代替字符串​​​​​​​