l 原则:减少系统瓶颈,减少资源占用,增加系统的反应速度。
一般可以分为
- 表的查询语句的优化(通常可以 使用由于其他的关键字或者查询语句 , 查看sql执行计划explain分析并改进sql,使用存储过程或者函数等)
- 表的结构优化(拆表或者分表)
- 通过对数据库的配置优化(配置mysql的配置文件my.ini 设置他的最大连接数,查询、排序等缓存开关和大小,)
- 对硬件需求的优化(提共足够大的内存、合理分配IO、使用多核cpu的)
- 使用框架优化,例如主从复制,读写分离;使用搭载mysql集群优化(将读写分离,事项查询高效,搭建集群,将负载均衡到多台数据库服务器上)
- 运维人员经常对数据库进行清理等,防止碎片化的出现影响性能
- 尽可能多使用类名查询,最好不要使用*号查询。
- where 和 having 都表示条件查询。 where效率高,where解析的顺序:右---> 左,一般使用and或者or是可以根据执行顺序结果省略一些计算结果,类似于java中&& ||用法,不可以使用分组函数;having常与order by 分组条件使用,可以使用分组函数。
- 尽量使用多表查询
- 尽量使用union all
- 尽量不要使用集合运算
- 查看效率低的sql语句,然后查看该句sql的执行计划,分析并优化
- 用TRUNCATE替代DELETE;TRUNCATE是删除表,在创建表结构;delete是逐行删除表里的数据
- 批量插入数据是可以暂时关闭唯一性检查、外键检查、和事务提交等操作,在插入完成后统一执行提交。
- 具有原子性,所有属性都可以是不可再分的属性,创建唯一的表示ID,减少数据库字段的冗余性,合理冗余数据可以优化数据性能。
- 合理创建标的索引,可以大大提高查询效率。
- 对表进行分表设计,当有一个表地段很多事,并且有些字段经常被修改,这些字段占用空间比较小,还有一些字段内容比较大,且不常被修改,那么我们完全将这两种字段的属性分成两张表来设计,同时可以使用同一个ID来存储。例如网上商城中商品表,其中像数量价格卖点等字段经常容易修改,我们将这些放在一个基础商品表中,同时商品描述有存储大量数据,而且一般同一个商品修改次数很少,那么我们可以将这种字段单独设计一张表。
- 可以使用增加中间表优化查询
- key_buffer_size 设置索引使用的缓冲区大小
- query_cache_size :设置查询语句缓存大小,值为0表示不适用缓存,值为1表示使用查询缓存,值为2是表示显示声明才使用查询缓存
- sort_buffer_size 设置排序缓冲区大小,越大效率越优
- max_connections:设置数据库的最大连接数,设置合理。
- read_buffer_size:设置读取每个表示使用的缓存大小。
-
配置较大的内存。足够大的内存,是提高MySQL数据库性能的方法之一。内存的IO比硬盘快的多,可以增加系统的缓冲区容量,使数据在内存停留的时间更长,以减少磁盘的IO。
-
配置高速磁盘,比如SSD。
-
合理分配磁盘IO,把磁盘IO分散到多个设备上,以减少资源的竞争,提高并行操作能力。
-
配置多核处理器,MySQL是多线程的数据库,多处理器可以提高同时执行多个线程的能力。
- 可以使用读写分离。主从复制,将主数据库只插入数据,从数据库读数据。
- 搭建数据库的集群框架,增加项目中的并发访问量,负载均衡
- 定期查询数据,优化数据碎片化。
- 尽量备份过往不在使用的数据库和表。
- 合理常见和优化索引和表
Orcale 和 Mysql 语法的区别:
- 整体性能:orcale性能更好,支持大并发,大访问量,同时占用存储空间也很大;mysql更加小巧,水平扩展(集群)非常方便,可以实现高并发,大多数企业使用。
- 主键策略:orcale使用的是序列,mysql使用的是auto increment
- 判断语句:decade是orcale自己的,mysql中只能使用SQL99语法中的case when then else end
- 分页语句:orcale中使用通过rownum产生的序号的两次排序选择获取想要的数据;mysql使用关键字limit实现
- orcale的恢复误删除机制比mysql更加复杂和完善
- 隔离等级:mysql默认为Repeatable Read(可重读);orcale默认为Read Committed(不可重复读或者读取提交内容)
- MYSQL日期字段分DATE和TIME两种,ORACLE日期字段只有DATE
Read Uncommitted(读取未提交内容)
在该隔离级别,所有事务都可以看到其他未提交事务的执行结果。本隔离级别很少用于实际应用,因为它的性能也不比其他级别好多少。读取未提交的数据,也被称之为脏读(Dirty Read)。
Read Committed(读取提交内容)
这是大多数数据库系统的默认隔离级别(但不是MySQL默认的)。它满足了隔离的简单定义:一个事务只能看见已经提交事务所做的改变。这种隔离级别 也支持所谓的不可重复读(Nonrepeatable Read),因为同一事务的其他实例在该实例处理其间可能会有新的commit,所以同一select可能返回不同结果。
Repeatable Read(可重读)
这是MySQL的默认事务隔离级别,它确保同一事务的多个实例在并发读取数据时,会看到同样的数据行。不过理论上,这会导致另一个棘手的问题:幻读 (Phantom Read)。简单的说,幻读指当用户读取某一范围的数据行时,另一个事务又在该范围内插入了新行,当用户再读取该范围的数据行时,会发现有新的“幻影” 行。InnoDB和Falcon存储引擎通过多版本并发控制(MVCC,Multiversion Concurrency Control)机制解决了该问题。
Serializable(可串行化)
这是最高的隔离级别,它通过强制事务排序,使之不可能相互冲突,从而解决幻读问题。简言之,它是在每个读的数据行上加上共享锁。在这个级别,可能导致大量的超时现象和锁竞争。