MySql优化

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中放置更多的记录。

三：索引

1. 索引是提高数据库性能的常用方法。例如我们创建一个联合索引(k1,k2,k3),那么在使用order by排序和group by分组时就会很快。因为联合索引他会先按照k1排序，在k1相等时候对k2排序，在k2相等时对k3排序。所以排序和分组时就直接用了。
2. 由于我们已经创建了联合索引，那么此时再给k1单独创建冗余索引就没必要了，如果id默认是聚簇索引，我们再给id创建一个二级索引(重复索引)就没必要了。
3. 一般情况下，可以给查询语句指定limit子句来限制查询返回的记录数，这可能会让查询优化器倾向于选择使用二级索引+回表的方式进行查询，原因是回表的记录越少，性能提升越高。
4. 有的时候不要用索引，如果说我们使用索引查询的记录占据了总记录的99%，那么它几乎不仅要全局扫描，还需要全局回表，这样是不划算的。为了彻底告别回表操作带来的性能损耗，建议最好在查询列表中只包含索引列。
5. 索引列的数据类型情况尽量小一些，比如能用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开始之前的状态，我们还可以设置保存点，它就很像虚拟机当中的快照功能，我们想要把他回滚到哪个地方就回滚到哪个地方。