mysql优化

一、sql优化原则：

• 使用EXPLAIN分析；
• 使用小结果集驱动大结果集；
• 尽可能在索引中完成排序；
• 只取出需要的字段；
• 尽可能避免复杂的join和子查询。

二、sql优化

1、创建索引；

      创建组合索引时，选择范围较小的字段放到前面。

2、分页：先分页查出主键，再使用inner join查询，如：

select id，name from user t inner join （select id from user u limit 1000000，100） a on t.id=a.id

 3、当只查询一行数据时加上：

LIMIT 1

    因为找到一条记录时就会返回，不会再查询下去。

 4、使用join关联查询时，使用相同类型的字段进行匹配，这样可以用到索引。

 5、保存IP地址时，把IP地址保存为UNSIGNED INT：

       因为使用字符串存IP地址时设置数据类型和长度为： VARCHAR(15) ，占用30个字节，而如果用整形来存放，只需要4个字节，并且可以有定长的字段。而且，这还会带来查询上的优势，尤其是需要使用如下的WHERE条件：

IP between ip1 and ip2

 6、时间字段类型选择，

      datetime类型时间范围：1000-01-01 00:00:00 到 9999-12-31 23:59:59，占8个字节。

      timestamp类型时间范围：1970-01-01 00:00:00 到 2037-12-31 23:59:59，占4个字节，具有初始化和自动更新特性。

`FUpdateDate` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP

 

 

 误区：

1、where条件的顺序对性能有影响。---答案是没有影响，但建立索引的顺序有。

 

 

三、配置优化

MySQL参数的优化：内存中会为MySQL保留部分的缓冲区。这些缓冲区可以提高MySQL的速度。缓冲区的大小都是在MySQL的配置文件中进行设置的。

key_buffer_size：表示索引缓存的大小。这个值越大，使用索引进行查询的速度就越快

table_cache：表示同时打开的表的个数。这个值越大，能同时打开的表的个数就越多。这个值不是越大越好，因为同时打开的表过多会影响操作系统的性能。

query_cache_size：表示查询缓冲区的大小。使用查询缓存区可以提高查询的速度。这个方式只使用与修改操作少且经常执行相同的查询操作的情况；默认值是0.

Query_cache_type：表示查询缓存区的开启状态。0表示关闭，1表示开启。

Max_connections：表示数据库的最大连接数。这个连接数不是越大越好，因为连接会浪费内存的资源。

Sort_buffer_size：排序缓存区的大小，这个值越大，排序就越快。

Innodb_buffer_pool_size：表示InnoDB类型的表和索引的最大缓存。这个值越大，查询的速度就会越快。这个值太大了就会影响操作系统的性能。

 

四、事务优化

• 脏读：一个事务对数据进行了修改，但还没有提交到数据库中，这时另一个事务也访问了同一份数据。
• 不可重复读：在一个事务内多次读取同一份数据。在这个事务还没有结束时，另一个事务也读取了这份数据。那么，在第一个事务读取数据之间，第二个事务对这个数据进行了修改，这个时候第一个事务两次读取的数据就可以不一样。这样就发生了一个事务内两次读取的数据不一样，即不可重复读。
• 幻读：