MySql优化
一:尽量把字段设置为NOT NULL,这样将来在执行的时候,数据库不用去比较NULL值。NOT IN和<> 操作都不会使用索引将进行全表扫描。NOT IN可以用NOT EXTSITS代替,id<>3则可以使用id>3 or id<3代替。
二:使用连接(JOIN)来代替子查询
原因:
在连接(Join)这一块,不用创建临时表,因为它引入了Join Buffer(连接缓冲区),就是在执行连接前申请的一块固定大小的内存。先把若干驱动表(join左边的表)的记录先装入join buffer当中,然后和被驱动表(join右边的表)进行匹配,由于匹配过程是在内存中完成的,所以会显著减少被驱动表的I/O代价。这个Join Buffer的大小可以通过系统变量join_buffer_size进行配置,默认大小是256KB,如果我们实在不能用索引(因为有的表太大了,几个亿的记录),并且自己的机器内存比较大,则可以尝试调大join_buffer_size的值来查询进行优化。而且最好不要把*作为查询列表(join_buffer只放筛选条件的列),这样可以在Join Buffer中放置更多的记录。
三:索引
- 索引是提高数据库性能的常用方法。例如我们创建一个联合索引(k1,k2,k3),那么在使用order by排序和group by分组时就会很快。因为联合索引他会先按照k1排序,在k1相等时候对k2排序,在k2相等时对k3排序。所以排序和分组时就直接用了。
- 由于我们已经创建了联合索引,那么此时再给k1单独创建冗余索引就没必要了,如果id默认是聚簇索引,我们再给id创建一个二级索引(重复索引)就没必要了。
- 一般情况下,可以给查询语句指定limit子句来限制查询返回的记录数,这可能会让查询优化器倾向于选择使用二级索引+回表的方式进行查询,原因是回表的记录越少,性能提升越高。
- 有的时候不要用索引,如果说我们使用索引查询的记录占据了总记录的99%,那么它几乎不仅要全局扫描,还需要全局回表,这样是不划算的。为了彻底告别回表操作带来的性能损耗,建议最好在查询列表中只包含索引列。
- 索引列的数据类型情况尽量小一些,比如能用INT就别用BIGINT,因为数据类型越小,索引占据的存储空间就越小。性能损耗就越小。索引也不是越多越好。空间上:每建立一个索引,都要为他建立一颗B+树。每一颗B+树的每一个节点都是一个数据页。一个数据页默认会占用16KB的存储空间,而一颗很大的B+树由许多数据页组成,这将占据很大一片存储空间。时间上:每当对表中的数据进行增删改操作时,都需要修改各个B+树索引,这些操作可能会对节点和记录的排序造成破坏,所以存储引擎需要额外的时间进行页面分裂,页面回收等操作,以维护节点和记录的排序。如果建立了许多索引,每个索引对应的B+树都要进行相关的维护操作,会降低性能。
四:锁
如果一个数据页中的记录已经满了,再插入一个主键值的记录,那么就会导致页面分裂,就是将当前页面分成两个页,这样会造成性能损耗,为了避免这种情况,最好主键设置AUTO_INCREMENT属性。这个属性会自动为新插入的记录生成递增的主键值。
对于页面分裂可以避免:即表级别的AUTO_INC锁
也就是在执行插入语句时就加一个表级别的AUTO_INC锁,然后为每条待插入记录的AUTO_INCREMENT修饰的列分配递增的值,在该语句执行结束后,再把AUTO_INC锁释放掉。这样一来,一个事务在持有AUTO_INC锁的过程中,其他事务的插入语句都要被阻塞,从而保证一个语句中分配的递增值是连续的
五:事务
我们想要保持数据库中的一致性和完整性。事务以BEGIN关键字开始,Commit关键字结束,在这之间的一条SQL操作失败,那么ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态,我们还可以设置保存点,它就很像虚拟机当中的快照功能,我们想要把他回滚到哪个地方就回滚到哪个地方。