Mysql优化

谁践踏了优雅 2024-04-17 05:27 17阅读 0赞

**1.常见索引列建议**

**出现在select,update,delete语句的where从句中的列**

**包含在order by ,group by ,distinct中的字段**

**多表的join关联列**

2.**避免建立冗余和重复索引**

**重复索引示例：primary key(id)、index(id)、unique index(id)**

**冗余索引示例：index(a,b,c)、index(a,b)、index(a)**

3.**优先选择符合存储需要的最小数据类型**

4.避免使用TEXT，BLOG数据类型。（如果查询中包含这样的数据，在排序等操作时，就不能使用内存临时表，必须使用磁盘临时表进行，Mysql还要进行二次查询，是sql性能变差）

5.**使用TIMESTAMP(4字节)或DATETIME（8字节）存储时间**

6.**财务相关金额类数据必须使用decimal类型**（Decimal类型为精准浮点数，在计算时不会丢失精度）

7.**限制每张表上的索引数量，建议但张表索引不超过5个。**（索引不是越多越好，索引可以增加查询效率，但同样也会降低插入和更新效率。）

8.**冷热数据分离，减小表的宽度**

MySQL限制每个表最多存储4096列，并且每一行数据的大小不能超过65535字节 减少磁盘IO,保证热数据的内存缓存命中率（表越宽，把表装载进内存缓冲池时所占用的内存也就越大,也会消耗更多的IO） 更有效的利用缓存，避免读入无用的冷数据 经常一起使用的列放到一个表中（避免更多的关联操作）

**9.不要在数据库中存储图片，文件等二进制数据**

10.**尽量避免使用外键约束**。

不建议使用外键约束foregin key，但一定要在表与表之间的关联键上建立索引；

外键可以用于保证数据的参照完整性，但建议在业务端实现；

外键会影响父表和子表的写操作从而降低性能。

11.**建议使用预编译语句进行数据库操作**

预编译语句可以重复使用这些计划，减少SQL编译所需要的时间，还可以解决动态SQL所带来的SQL注入的问题 只传参数，比传递SQL语句更高效 相同语句可以一次解析，多次使用，提高处理效率。

12.**避免使用前%号的查询条件**

如a like %123%，（如果无前置%,只有后置%，是可以用到列上的索引的）

在我们查询中经常会用到select id,fnum,fdst from table\_name where user\_name like '%zhangsan%'; 。这样的语句，普通索引是无法满足查询需求的。庆幸的是在MySQL中，有全文索引来帮助我们。

创建全文索引的sql语法是：

ALTER TABLE \`table\_name\` ADD FULLTEXT INDEX \`idx\_user\_name\` (\`user\_name\`);

使用全文索引的sql语句是：

select id,fnum,fdst from table\_name where match(user\_name) against('zhangsan' in boolean mode);

13.**禁止使用SELECT \* 必须使用SELECT <字段列表> 查询**

原因：

*  消耗更多的CPU和IO以网络带宽资源
 *  无法使用覆盖索引
 *  可减少表结构变更带来的影响

**14.避免使用子查询，可以把子查询优化为join操作**

通常子查询在in子句中，且子查询中为简单SQL(不包含union、group by、order by、limit从句)时，才可以把子查询转化为关联查询进行优化。

子查询性能差的原因：

*  子查询的结果集无法使用索引，通常子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响；
 *  特别是对于返回结果集比较大的子查询，其对查询性能的影响也就越大；
 *  由于子查询会产生大量的临时表也没有索引，所以会消耗过多的CPU和IO资源，产生大量的慢查询。

15.**避免使用JOIN关联太多的表**

对于Mysql来说，是存在关联缓存的，缓存的大小可以由join\_buffer\_size参数进行设置。

在Mysql中，对于同一个SQL多关联（join）一个表，就会多分配一个关联缓存，如果在一个SQL中关联的表越多，所占用的内存也就越大。

如果程序中大量的使用了多表关联的操作，同时join\_buffer\_size设置的也不合理的情况下，就容易造成服务器内存溢出的情况，就会影响到服务器数据库性能的稳定性。

同时对于关联操作来说，会产生临时表操作，影响查询效率Mysql最多允许关联61个表，建议不超过5个。

**16.在明显不会有重复值时使用UNION ALL而不是UNION**

UNION会把两个结果集的所有数据放到临时表中后再进行去重操作  
UNION ALL不会再对结果集进行去重操作

17.**使用left join或 not exists来优化not in操作**

因为not in 也通常会使用索引失效。

18.**使用合理的分页方式以提高分页的效率**

列：select id,name from table\_name limit 88888, 20

使用上述sql语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。

优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是88888。sql可以采用如下的写法：

select id,name from table\_name where id> 88888 limit 20

19.**避免在 where 子句中对字段进行 null 值判断**

应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：      
select id from t where num is null      
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：      
select id from t where num=0

20.**避免隐式类型转换**

隐式转换会导致索引失效

where 子句中出现 column 字段的类型和传入的参数类型不一致的时候发生的类型转换，建议先确定where中的参数类型

21.**对于联合索引来说，要遵守最左前缀法则，常用的查询字段放在最前面**

22.**应尽量避免在 where 子句中使用!=或<>操作符，否则将导致引擎放弃使用索引而进行全表扫描。**

**23.应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：    **  
select id from t where num=10 or num=20      
**可以这样查询： **     
select id from t where num=10      
union all      
select id from t where num=20

**24.in 和 not in 也要慎用，否则会导致全表扫描，如：  **    
select id from t where num in(1,2,3)      
对于连续的数值，能用 between 就不要用 in 了：      
select id from t where num between 1 and 3

**25.应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：    **

select id from t where num/2=100

应改为:

select id from t where num=100\*2

**26.用 exists 代替 in    **  
select num from a where num in(select num from b)      
用下面的语句替换：      
select num from a where exists(select 1 from b where num=a.num)

**27.对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高;  **

**select \* from table1 where col1=A AND col2=B AND col3=D     如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引**