测试开发mysql性能调优总结（一）

测试开发mysql性能调优总结

mysql在创建表的时候，对每个字段选择合适的数据类型很重要！

根据个人的经验总结：

整数类型选择 INT
小数类型选择 DECIMAL
字符串类型选择 TEXT
日期时间选择 DATETIME

一般在表中我们都是以字符串类型居多，但是如果字符串类型的字段作为主键的时候，就不能用TEXT了，最好选用CHAR或VARCHAR

主要原因是：
CHAR表示固定长度的字符串
VARCHAR表示的是可变长度的字符串
TEXT表示的是长度不可控，系统会根据实际长度存储这个TEXT字段，不适合建索引

 

各种主键的缺点：
业务字段做主键，缺点：业务字段的值可能重复
自增字段做主键，缺点：分布式的时候字段值可能重复
UUID或雪花算法做主键，缺点：长度太长
手动赋值字段做主键，缺点：操作比较麻烦

mysql的数据文件：
frm：存放表结构
myd：存放表数据
myi：存放表索引

一条mysql语句执行的过程：
客户端------->连接器-------> 缓存-------> 分析器-------> 优化器-------> 执行器------->存储器

总结：客，连，缓，分析，优化，执行，器

mysql语句的正常顺序：

select ....... from ....... where ....... group by ....... having ....... order by

group by一定要和索引的字段顺序都一致，否则会出现using temporary 创建临时表大大影响数据库性能！！！

explain的type结果值：
system > const > eq_ref > ref > range > index > ALL
最好要达到ref级别，最差也要达到index，一般是range

const：通过索引一次就找到了
eq_ref：主键或唯一索引扫描，只有一条记录匹配
ref：非唯一索引扫描，可能有多条记录匹配
range：范围扫描
index：全索引扫描
all：全表扫描

 

mysql索引失效的情况 七字口诀：“模型数空运最快”
模：使用like进行模糊查询的时候，以百分号%开头的，索引就会失效。
型：代表数据类型，数据类型错误了，索引也会失效。
数：函数，对索引的字段使用内部函数的，索引也会失效。这种情况，应该建立基于函数的索引。
空：null，索引不存储空值，如果没有声明nou null，数据库会认为有可能存在空值，也不会按照索引进行计算。
运：运算，对索引列进行加减乘除等运算的，会造成索引失效。
最：复合索引中索引列的顺序非常重要，如果不是按照索引列最左列开始查找，则无法使用索引。
快：全表扫描最快，如果数据库认为全表扫描更快的时候，它就不会使用索引。

不适合建索引的情况：
1，表里面的数据量太少
2，经常对表进行增删改
3，数据大量重复

 

 

 

聚簇索引和非聚簇索引的区别：
聚簇索引：数据和对应的索引列存储在一起，索引树的叶子节点存储的是需要查询的数据，innodb既支持聚簇索引也支持非聚簇索引。
非聚簇索引：数据和对应的索引列分开存储，索引树的叶子节点存储的是主键的值，再通过主键值查找到要查询的数据（回表），myisam只支持非聚簇索引。

覆盖索引：查询的数据只需要在索引中就能够获取到，不需要再从数据标中获取，也就是：where里面的索引列包含了select里面的列

 

order by一定不能和select * 一起使用，因为：
如果要查询的只有2个字段，但是select * 却需要查询所有字段（10个字段），很有可能select * 远远超出了sort_buffer的容量大小，就会导致创建tmp文件进行合并排序，就会导致多次IO。

 

 

set global slow_query_log = 1 只是对当前数据库生效
永久生效需要修改my.cnf文件里面的内容：
slow_query_log = 1
slow_query_log_file = /var/lib/mysql/chenjz-slow.log

set global long_query_time = 2 设置慢查询为2秒
show variables like 'long_query_time%' 显示慢查询时长
show variables like '%slow_queries%' 查询慢查询语句的个数

tail -10 /var/lib/mysql/chenjz-slow.log 查看具体的慢查询日志
可以用mysqldumpslow查询slow.log里面的日志

show profiling查看每一步耗时
show variables like 'profiling' 查看profiling的配置是否开启
set profiling = on

show profiles;  显示每一条sql的耗时和query_ID
show profile cpu, block io for query 2;  查看第2个sql的每一步耗时
如果有creating/copy/removing tmp table都是比较耗时的

show processlist 查询当前mysql有些线程正在运行，重点查看Command 这一列：
查看mysql正在运行的线程，按照时间长短排列
select * from information_schema.processlist where Command != 'Sleep' order by Time desc;
查看mysql运行超过500毫秒的线程，按照时间长短排列
select * from information_schema.processlist where Command != 'Sleep' and Time > 500 order by Time desc;

show status like 'table%'; 查找表锁
show status like 'innodb_row_lock%'; 查找行锁