如何准备一千万条数据?【存储过程】
影响数据库性能的关键要素
为什么要进行分页查询显示
1、响应时间、扫描的行数、返回的数据行数
2、具体时间:数据库设计不合理、sql慢查询
如何进行数据库优化?
1、数据库设计
2、sql语句优化
3、架构优化
适度违反三大范式【适度】
遵循三大范式后续查询时需要经常使用join,导致查询效率降低,结合业务需求适当做数据冗余
适度建立索引
IO(更新操作速度降低、索引的操作)、存储空间
建立索引的规则
索引的字段必须是经常用来作为查询条件的字段,wehere
如果是多个字段的情况,第一个字段要是经常经常作为查询条件的
索引的字段必须有足够的区分度
对表进行水平划分
按年或按月对数据库进行区分建表
对表进行垂直划分
多字段或字段长进行多表
选择适当字段类型
文件图片等大文件使用文件存储系统,数据库只存放文件路径
外键要表示清除,实际工作中大部分不会建立外键
宁可集中批量操作也不频繁读写
选择合适的引擎
字段数据类型的选择,能使用数字类型的字段尽量使用数字型
尽量避免使用游标
尽量避免大事务操作,索引优化
注意:主键列和唯一约束的列,会默认加索引
修改表结构的语句、语法
alter table 表名 add index 索引名(要添加的索引字段)
注意:
索引是把双刃剑
建立索引后查询效率会提升,但是修改索引、修改数据、新增数据、
当某列作为查询条件出现的概率较大时,可以考虑增加索引
经常需要对某列数据进行修改,慎重
SQL语句优化:
1、慢日志、explain
2、避免全表扫描,考虑在where和order by 子句中建立索引
3、尽量避免在where子句中使用null,会放弃索引,进行全表扫描
4、尽量避免在where子句中使用运算符(!=,<> )或函数(year())
5、避免使用or,会走全表扫描。union all
6、能使用between就不要使用in
7、like尽量避免使用%%,不会走索引,D%汇总剖索引
8、查询时尽量避免使用select*,仅列出需要查询的字段
9、join的操作,小结果驱动大结果
10、分页查询时、基数比较大时,不要使用limit,尽量换成between
11、随机取几条记录的时候,尽量避免使用rand
12、分组统计,count(列名)与count(*),
count(*)统计表中所有行数据,会统计null,count(列名)null不统计,全表扫描
13、不要做无谓的排序操作
14、子查询exist、in,使用exist子查询代替in子查询
15、索引不是越多越好、最好不要超过6个
影响数据库性能的关键要素
为什么要进行分页查询显示
1、响应时间、扫描的行数、返回的数据行数
2、具体时间:数据库设计不合理、sql慢查询
如何进行数据库优化?
1、数据库设计
2、sql语句优化
3、架构优化
适度违反三大范式【适度】
遵循三大范式后续查询时需要经常使用join,导致查询效率降低,结合业务需求适当做数据冗余
适度建立索引
IO(更新操作速度降低、索引的操作)、存储空间
建立索引的规则
索引的字段必须是经常用来作为查询条件的字段,wehere
如果是多个字段的情况,第一个字段要是经常经常作为查询条件的
索引的字段必须有足够的区分度
对表进行水平划分
按年或按月对数据库进行区分建表
对表进行垂直划分
多字段或字段长进行多表
选择适当字段类型
文件图片等大文件使用文件存储系统,数据库只存放文件路径
外键要表示清除,实际工作中大部分不会建立外键
宁可集中批量操作也不频繁读写
选择合适的引擎
字段数据类型的选择,能使用数字类型的字段尽量使用数字型
尽量避免使用游标
尽量避免大事务操作,索引优化
注意:主键列和唯一约束的列,会默认加索引
修改表结构的语句、语法
alter table 表名 add index 索引名(要添加的索引字段)
注意:
索引是把双刃剑
建立索引后查询效率会提升,但是修改索引、修改数据、新增数据、
当某列作为查询条件出现的概率较大时,可以考虑增加索引
经常需要对某列数据进行修改,慎重
SQL语句优化:
1、慢日志、explain
2、避免全表扫描,考虑在where和order by 子句中建立索引
3、尽量避免在where子句中使用null,会放弃索引,进行全表扫描
4、尽量避免在where子句中使用运算符(!=,<> )或函数(year())
5、避免使用or,会走全表扫描。union all
6、能使用between就不要使用in
7、like尽量避免使用%%,不会走索引,D%汇总剖索引
8、查询时尽量避免使用select*,仅列出需要查询的字段
9、join的操作,小结果驱动大结果
10、分页查询时、基数比较大时,不要使用limit,尽量换成between
11、随机取几条记录的时候,尽量避免使用rand
12、分组统计,count(列名)与count(*),
count(*)统计表中所有行数据,会统计null,count(列名)null不统计,全表扫描
13、不要做无谓的排序操作
14、子查询exist、in,使用exist子查询代替in子查询
15、索引不是越多越好、最好不要超过6个