日常记录——MySQL—优化

一、设计

选择合适存储引擎https://blog.csdn.net/weixin_43001336/article/details/107677329
选择合适字段的类型和长度https://blog.csdn.net/weixin_43001336/article/details/107746215

二、功能

创建合适索引,增加sql效率https://blog.csdn.net/weixin_43001336/article/details/107644124
a.不要过度索引。索引越多,占用空间越大,反而性能变慢;
b.只对WHERE子句中频繁使用的建立索引;
c.尽可能使用唯一索引,重复值越少,索引效果越强;
d.使用短索引,如果char(255)太大,应该给它指定一个前缀长度,大部分情况下前10位或20位值基本是唯一的,那么就不要对整个列进行索引;
e.充分利用左前缀,这是针对复合索引,因为WHERE语句如果有AND并列,只能识别一个索引(获取记录最少的那个),索引需要使用复合索引,那么应该将WHERE最频繁的放置在左边;
f.索引存在,如果没有满足使用原则,也会导致索引无效;
g.数据量超过300的表应该有索引;
使用EXPLAIN 查询sql的执行计划https://blog.csdn.net/weixin_43001336/article/details/107723370
使用show profiles 分析性能 :可以用来分析当前会话中语句执行的资源消耗情况,如会话内sql的耗时、cpu、io等信息。
默认关闭,使用前需要开启:

--查看状态
show variables like ‘profiling%;
--开启
set profiling=on;
--查看sql信息
show profiles;
--查看指定sql信息
show profile for query Query_ID;

表分区:表过于庞大,导致数据库在查询和插入的时候耗时太长,性能低下,联合查询效率更低。将数据分段划分在多个位置存放,可以是同一块磁盘也可以在不同的机器。分区后,表面上还是一张表,但数据散列到多个位置了,减少数据库的负担,提高数据库的效率。
慢查询日志:开启慢查询日志,记录sql查询效率低的语句,定向处理。

Create table table_name (定义)
Partition by 分区算法 (参数) 分区选项。
--示例
create table user_partitions (id int auto_increment, 
     name varchar(20),primary key(id))
	 //按照id分区,id<200 A分区,否则B分区
     partition by range(id)(
         partition A values less than(200),
         partition B values less than maxvalue
     );

垂直分割:按业务逻辑,把数据库中的表中不常用的列变成几张表或者把text,blog等大字段拆分出来放在附表中。

三、架构

主从复制:Mysql服务器内部支持复制功能,通过binlog和relaylog实现主从复制。
读写分离:主服务器负责写数据。从服务器负责读数据,可通过mycat是实现。
目的:压力分发,高可用,提升整体服务器的效率,高性能。

四、合理SQL

1.尽量避免SELECT *命令,从表中读取越多的数据,查询会变得更慢,增加磁盘IO负担,只查询自己需要的列。
2.利用LIMIT 1取得唯一行,取消目标数据后的扫描。
3.对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。
4.应尽量避免在 where 子句中对字段进行 null 值判断,创建表时NULL是默认值,但大多数时候应该使用NOT NULL,或者使用一个特殊的值,如0,-1作为默认值。
5.不要轻易用order by rand()。
6.尽可能的使用索引字段作为查询条件。
7.应尽量避免在 where 子句中使用!=或<>操作符或者系统参数, MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,模糊查询也只支持前缀匹配(XXX%)。
8.不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则将可能无法正确使用索引。
9.当在SQL语句中连接多个表时,使用表的别名并把别名前缀于每个Column上,就可以减少解析的时间并减少那些由Column歧义引起的语法错误。
10.在IN后面值的列表中,将出现最频繁的值放在最前面,出现得最少的放在最后面,减少判断的次数。
11.当有一批处理的插入或更新时,用批量插入或批量更新。
12.对于连续的数值,能用 between 就不要用 in 了:

select id from t where num in(1,2,3);
select id from t where num between 1 and 3

13.用or分割开的条件,如果 or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。
14.避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描,如:

select name from t where id=10 or age=20
可以这样查询:
select namefrom t where id=10
union all
select namefrom t where age=20

15.Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引。
16.ORDER BY语句查询使用了两种不同的排序方向,将不使用索引排序。
17.查询在索引列的第一列上是范围条件时,后面的列将不会使用索引。
18.分页查询可修改为自连接:

select film_id , description from film order by title limit 50,5;
select a.film_id , a.description from film a inner join (select film_id from film order by title limit 50,5) b on a.film_id=b.film_id;
©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页