Mysql性能优化总结

Mysql性能优化总结

1、定位慢查询SQL

修改mysql的配置文件my.cnf：

vi /etc/mysql/my.cnf 

在mysqld文件中添加慢查询配置

#开启慢日志 
slow_query_log=TRUE        

#慢查询日志存放位置                               
slow_query_log_file=/var/log/mysql/mysql-slow.log      

 #超时记录时间2秒
long_query_time=2                                     

在客户端查看刚刚设置是否成功：

show variables like '%quer%';

可以在慢查询日志中查看查询时间较久的SQL语句，可以使用慢查询分析工具对SQL进行分析，如Mysqldumpslow
。可以对数据耗时、访问次数等排序返回指定条。

2、explain执行计划

explain 可以获取 MySQL 中 SQL 语句的执行计划，比如语句查询类型、表的访问方法、是否使用了索引、使用到索引的长度、扫描行数等。通过分析这些类型，让我们知道优化的方向。

explain select * from news WHERE content like '我%';

上诉语句的执行计划结果如下：

执行计划中的各个字段解释：

3、索引

1、索引类型和创建方式

1、普通索引：没有任何限制，用于加速查询。

//方式一：
create table news (
title varchar(200) ,
index index_title (title(200))
);

//方式二：
create index  title_index on news(title(200));

//方式三：
alter table  news add index  title_index   (title(200));

2、组合索引：多个字段上创建索引，只有在查询条件中使用创建索引时的第一个字段，索引才会被使用

//方式一：
create table news (
title varchar(200) ,
content varchar(255) ,
index content_title_index (title(200),content(255))
);

//方式二：
create index  content_title_index on news(title(200),content(255));

//方式三：
alter table  news add index  content_title_index (title(200),content(255));

3、唯一索引
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

//方式一：
create table news (
title varchar(200) ,
unique index  title_index (title(200))
);

//方式二：
create unique index  title_index on news (title(200));

//方式三：
alter table  news add unique index  title_index   (title(200));

4、主键索引
特殊的唯一索引，一个表只能有一个主键，索引列的值必须唯一，不允许有空值。一般在建表时同时创建主键索引

//方式一：
create table news (
title varchar(200) ,
primary key(title)
);

5、全文索引
只能用于char、varchar和text类型的字段上。查询数据量较大的字符串类型字段时，使用全文索引提高查询速度。

//方式一：
create table news (
title varchar(200) ,
fulltext key title_index (title(200))
);

//方式二：
create fulltext index  title_index on news (title(200));

//方式三：
alter table  news add fulltext index  title_index  (title(200));

查看索引

show index from news;

删除索引

drop index title_index  on news;

alter table  news drop index  title_index;

2、索引的适用场景

适用：

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表有关联的字段，例如外键关系
4. 高并发的情况下一般选择复合索引
5. 查询中排序的字段创建索引将大大提高排序的速度（索引就是排序加快速查找）
6. 查询中统计或者分组的字段

不适用：

1. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新记录，还会更新索引，保存索引文件
2. .where条件里用不到的字段，不创建索引
3. 表记录太少，不需要创建索引
4. 经常增删改的表
5. 数据重复的字段

3、索引失效场景

1、如果是联合索引，要遵守最左前缀法则（离散性）。查询要从最左列开始并且不跳过索引中的列，否则会出现索引会失效。例如索引是index (a,b,c)，可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c或c进行查找 。

2、不在索引列上做计算、函数、类型转化等操作，否则会出现索引失效

3、存储引擎不能使用索引中范围条件右边的列，否则会出现索引失效

4、在使用is null、is not null 、!=、<>、or、in等会出现索引失效

5、使用like操作时，最左边必须是确定的值，如‘我%’，如果是‘%我’则会出现索引失效

4、反范式设计优化

三大范式：

三大范式的规则是为了减少冗余，新增，修改，删除时候操作更方便，但是查询操作可能变慢，存在表关联。所以反范式设计允许少量冗余以提升数据库的查询效率，如合并表，以空间换时间来提升性能。。

5、其他优化方案

1. 只要一行数据时使用limit1，这样找到一条记录后会直接停止搜索

2. 避免使用select *，减少读取出来的数据

3. 一些字段长度有限的固定字段使用enum代替varchar，如性别、国家等。枚举类型速度快，保存的是tinyint类型

4. 尽可能使用not null，占用额外空间且进行比较时候运算复杂。

5. 垂直分表和水平分表

   • 垂直分表时把一些不常用的字段分割出来，放到另一张表中
   • 水平分表时依照时间条件等，把常用的一部分和不常用的一部分分成两张表

6. 拆分大的delete或insert语句，因为大语句执行较久，会占用较长时间锁。可以使用limit条件限定分批次插入或删除

7. 数量统计选择，效率： count（*） >或（≈） count（1） > count（id） > count（字段）

   • count(*)：mysql对它做了优化，不会取值出来，且肯定不为空，不用判断空值，统计行数。
   • count(1)：不会取值出来，且肯定不为空，统计行数。
   • count(id)：会把id取出来，且肯定不为空，统计行数。
   • count(字段)：如果字段not null ，将字段取出来累加。如果允许null，把字段取出来判断不为空，统计行数。

8. char和varchar的选择

   • char适合存储很短的字符串，或者所有值都接近同一个长度。
   • varchar适用于字符串很长或者所要存储的字符串长短不一，差别很大；列的更新很少

9. 选择正确的搜索引擎
   

二、使用EXPLAIN 来查看你的 SELECT 查询
三、不要使用表达式作为查询条件
四、尽量使用in运行符来替代or运算
五、条件列表值如果连续使用between替代in
六、无重复记录的结果集使用union all合并
七、有条件使用where就不使用having
八、使用like操作符时通配符要放在右侧