MySQL调优
#### 1. 性能监控
show profile
此工具默认是禁用的-可以通过服务器变量在绘画级别动态的修改
set profiling=1;
当设置完成之后-在服务器上执行的所有语句-都会测量其耗费的时间和其他一些查询执行状态变更相关的数据。
select * from emp;
在mysql的命令行模式下只能显示两位小数的时间-可以使用如下命令查看具体的执行时间
show profiles;
执行如下命令可以查看详细的每个步骤的时间：
show profile for query 1;
show profiles 查看执行时间
只精确到后两位
show profile 最近执行的sql , 每个步骤多长时间
show profile for query 2 查第二个
show profile cpu
各连接池性能
#### 2. 表结构优化
应该尽量使用可以正确存储数据的最小数据类型-更小的数据类型通常更快-因为它们占用更少的磁盘、内存和CPU缓存-并且处理时需要的CPU周期更少-但是要确保没有低估需要存储的值的范围-如果无法确认哪个数据类型-就选择你认为不会超过范围的最小类型
案例：
设计两张表-设计不同的数据类型-查看表的容量
``java
import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;public class Test { public static void main(String[] args) throws Exception{ Class.forName("com.mysql.jdbc.Driver"); Connection conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/db1","root","123456"); PreparedStatement pstmt = conn.prepareStatement("insert into psn2 values(?,?)"); for (int i = 0; i < 20000; i++) { pstmt.setInt(1,i); pstmt.setString(2,i+""); pstmt.addBatch(); } pstmt.executeBatch(); conn.close(); }}
`
bigint tinyint 占用空间不同 查询时间也不同
ip地址转数值 INET_ATON/NTOA
简单数据类型的操作通常需要更少的CPU周期-例如-
1、整型比字符操作代价更低-因为字符集和校对规则是字符比较比整型比较更复杂-
2、使用mysql自建类型而不是字符串来存储日期和时间
3、用整型存储IP地址
案例：
创建两张相同的表-改变日期的数据类型-查看SQL语句执行的速度
如果查询中包含可为NULL的列-对mysql来说很难优化-因为可为null的列使得索引、索引统计和值比较都更加复杂-坦白来说-通常情况下null的列改为not null带来的性能提升比较小-所有没有必要将所有的表的schema进行修改-但是应该尽量避免设计成可为null的列
可以使用的几种整数类型：TINYINT-SMALLINT-MEDIUMINT-INT-BIGINT分别使用8-16-24-32-64位存储空间。
尽量使用满足需求的最小数据类型
1、char长度固定-即每条数据占用等长字节空间；最大长度是255个字符-适合用在身份证号、手机号等定长字符串
2、varchar可变程度-可以设置最大长度；最大空间是65535个字节-适合用在长度可变的属性
3、text不设置长度-当不知道属性的最大长度时-适合用text
按照查询速度：char>varchar>text
4k对齐
MySQL 把每个 BLOB 和 TEXT 值当作一个独立的对象处理。
两者都是为了存储很大数据而设计的字符串类型-分别采用二进制和字符方式存储。
1、不要使用字符串类型来存储日期时间数据
2、日期时间类型通常比字符串占用的存储空间小
3、日期时间类型在进行查找过滤时可以利用日期来进行比对
4、日期时间类型还有着丰富的处理函数-可以方便的对时间类型进行日期计算
5、使用int存储日期时间不如使用timestamp类型
有时可以使用枚举类代替常用的字符串类型-mysql存储枚举类型会非常紧凑-会根据列表值的数据压缩到一个或两个字节中-mysql在内部会将每个值在列表中的位置保存为整数-并且在表的.frm文件中保存“数字-字符串”映射关系的查找表
create table enum_test(e enum(‘fish’,’apple’,’dog’) not null);
insert into enum_test(e) values(‘fish’),(‘dog’),(‘apple’);
select e+0 from enum_test;
人们经常使用varchar(15)来存储ip地址-然而-它的本质是32位无符号整数不是字符串-可以使用INET_ATON()和INET_NTOA函数在这两种表示方法之间转换
案例：
select inet_aton(‘1.1.1.1’)
select inet_nt
