数据库性能优化
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等负向查询条件
