数据库性能优化

Mysql性能优化

设计表

  • 使用innodb存储引擎,innodb支持事务,支持行级锁，更好的恢复性，高并发下性能更好。

  • 数据库和表的字符集统一使用UTF8

  • 选取合适的字段属性

    将表中字段宽度设的尽可能小一些。

    例如,可以设成CHAR(6)完成任务,就不要设成CHAR(255)

  • 尽量把字段设置为NOTNULL,这样数据库执行的时候就不用去比较null值。

  • 避免使用ENUM类型

  • 修改 ENUM 值需要使用 ALTER 语句

    ENUM 类型的 ORDER BY 操作效率低，需要额外操作

    禁止使用数值作为 ENUM 的枚举值

  • 使用TIMESTAMP(4个字节)或DATETIME类型(8个字节)存储时间

    TIMESTAMP 存储的时间范围 1970-01-01 00:00:01 ~ 2038-01-19-03:14:07

    TIMESTAMP 占用 4 字节和 INT 相同，但比 INT 可读性高

    超出 TIMESTAMP 取值范围的使用 DATETIME 类型存储

  • 同财务相关的金额类数据必须使用decimal类型

    非精准浮点:float,double

    精准浮点:decimal

  • 每个innodb表必须有个主键

  • 常见索引列建议

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

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

    并不要将符合1和2中的字段的列都建一个索引，通常江1，2中的字段建立联合索引效果更好。

    多表join的关联列。

  • 如何选择索引列的顺序

    建立索引的目的是：希望通过索引进行数据查找，减少随机 IO，增加查询性能 ，索引能过滤出越少的数据，则从磁盘中读入的数据也就越少。

    • 区分度最高的放在联合索引的最左侧（区分度=列中不同值的数量/列的总行数）
    • 尽量把字段长度小的列放在联合索引的最左侧（因为字段长度越小，一页能存储的数据量越大，IO 性能也就越好）
    • 使用最频繁的列放到联合索引的左侧（这样可以比较少的建立一些索引）

  • 尽量避免使用外键约束

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

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

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

  • 使用索引，索引应建立在那些将用于JOIN,WHERE判断和ORDERBY排序的字段上。尽量不要对数据库中某个含有大量重复的值的字段建立索引。 索引最好在相同类型的字段间进行比较的操作，在建有索引的字段上尽量不要使用函数。

  • 尽可能的使用 varchar/nvarchar 代替 char/nchar

  • 使用短索引，不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

    对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

  • 数据库默认排序可以符合要求的情况下不要使用排序操作 ，尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

  • 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有 必要。

  • 存储引擎不能使用索引中范围条件右边的列

    如这样的sql: select * from user where username='123' and age>20 and phone='1390012345',其中username, age, phone都有索引，只有username和age会生效，phone的索引没有用到。

  • 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型

  • 尽量避免使用游标，因为游标的效率较差 ,如果游标操作的数据超过1万行，那么就应该考虑改写。

  • 避免使用TEXT，BLOB数据类型，最常见的text类型可以存储64k的数据。

  • 对于程序连接数据库的账号，遵循权限最小原则

    • 程序使用数据库账号只能在一个 DB 下使用，不准跨库
    • 程序使用的账号原则上不准有 drop 权限

  • 对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。

SQL语句

  • 使用内连接(join)代替子查询

    原因是用连接时，MYSQL不需要在内存中创建临时表来完成逻辑上需要两个步骤的查询工作。

    避免数据类型的隐式转换

    隐式转换会导致索引失效如：

    select name ,phone from customer where id = '111'

  • 禁止使用select * 必须使用select<字段列表>查询

    消耗更多的cpu和io以及网络带宽资源

    无法使用覆盖索引

    可减少表结构变更带来的影响

  • 禁止使用不含字段列表的insert语句

    insert into values ('a','b','c')

    --->

    insert into t(c1,c2,c3) values ('a','b','c');

  • 避免使用join关联太多的表

  • 禁止使用order by rand()进行随机排序

  • 在明显不会有重复值时使用union all 而不是union

  • 超 100 万行的批量写 (UPDATE,DELETE,INSERT) 操作,要分批多次进行操作

  • 使用联合(UNION)来代替手动创建的临时表

  • 使用事务，可以保持数据库中数据的一致性和完整性。

  • 可以通过锁定表来获得更好的性能。

    LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';

    UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCK TABLES

  • 对查询进行优化，要尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

  • 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

    select id from t where num = 0

  • 不使用NOT IN和<>操作

  • 不要在列上进行运算

    select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这 将导致索引失效而进行全表扫描，因此我们可以改成 select * from users where adddate<‘2007-01-01';

  • 尽量不要用like操作，like"%aaa%"不会使用索引，like"aaa%"可以使用索引。

  • 应尽量避免在 where 子句中使用 or 来连接条件

  • select count(*) from table；这样不带任何条件的count会引起全表扫描

  • 任何地方都不要使用 select * from t ，用具体的字段列表代替

  • 用IN代替OR

  • 读取适当的记录LIMIT M,N

    select id,name 
from table_name limit 866613, 20
--->
select id,name from table_name 
where id> 866612 limit 20

  • 避免随机取记录

    select * from t1 where 1=1 order by RAND() LIMIT 4
--->
select * from t1 where id >=CEIL(RAND()*1000) LIMIT 4

  • 批量INSERT插入

    insert into t (id,name) values(1,'bea')
insert into t (id,name) values(2,'bela')
insert into t (id,name) values(3,'tom')
----->
insert into t(id,name)values(1,'bea'),(2,'bela'),(3,'tom')

  • 不要使用NOT等负向查询条件

posted @ 2020-12-11 16:44  小白白白白白白白白白  阅读(91)  评论(0)    收藏  举报