Mysql优化

SQL优化技巧

1. 使用mysql自带查询缓存，避免使用函数 （NOW() 和 RAND()，CURDATE()） ，可以将函数替换成变量

2. EXPLAIN 你的 SELECT 查询。会显示他使用的索引和扫描的总条数

3. 当只要一行数据时使用 LIMIT 1 ，并且使用select 1 from

4. 为搜索字段建索引,like只有左面不带%号时生效

5. 在 Join 表的时候使用相当类型的例，并将其索引， join关联的字段俩个表都应该创建索引，且保证join相连的俩个字段的数据类型和字符集是一致的。

6. 千万不要 ORDER BY RAND() 想打乱返回的数据行，RAND()函数非常消耗CPU时间，可以先查出总条数，再用limit，在java里进行随机数当参数传进去

7. 避免 SELECT \*， 查询的字段越多，查询速度越慢，而且会增加对应传输到应用服务器的网络负载

8. 永远为每张表设置一个 ID，且最好使用无符号的int类型 UNSIGNED，设置成自增ID。varchar类型会使主键使用的性能下降。集群，分区，搜索

9. 使用 ENUM 而不是 VARCHAR ，枚举类型使用的是tinyint

 

 创建枚举sql：

Create table marks(id int Primary key NOT NULL, name Varchar(255) NOT NULL, result ENUM('Pass', 'Fail') NOT NULL); 

如果用到mybatis，则对应的Mapper.xml为：

<result column="result " property="result " typeHandler="org.apache.ibatis.type.EnumTypeHandler"/>

  则对应的实体类为：

@TableField(typeHandler = EnumTypeHandler.class) 
private SeasonEnum result ; 

10. 从 PROCEDURE ANALYSE() 取得建议，这个当数据量少时，会提示的不准 PROCEDURE ANALYSE() 会让 MySQL 帮你去分析你的字段和其实际的数据，并 会给你一些有用的建议。

 

 SELECT * FROM tbl_ name PROCEDURE ANALYSE() ; SELECT * FROM tbl_ _name PROCEDURE ANALYSE(16, 256) ; 

16: max_elements （默认值256） analyze查找每一列不同值时所需关注的最大不同值的数量.,就是该字段存储的不同的值类型，如果超过了就不统计了 
25: max_memory （默认值8192） analyze查找每一列所有不同值时可能分配的最大的内存数 

11. 尽可能的使用 NOT NULL, NULL会占用额外的空间

12. Prepared Statements， 防SQL 注入。使用二进制形势，网络传输非常有效率。 当一个相同的查询被使用多次的时候，这会为你带来可观的 性能优势。你可以给这些 Prepared Statements 定义一些参数，而 MySQL 只会解析一次。

13. 无缓冲的查询，mysql\_unbuffered\_query() 发送sql 到mysql，进行异步返回， 正常是使用mysql\_query()一样去自动 fethch 和缓存结果。

14. 把 IP 地址存成 UNSIGNED INT， INET\_ATON()来把一个字符串 IP 转成一个整形。并使用 INET\_NTOA() 把一个整形转成一个字符串 IP。

15. 固定长度的表会更快。当表里有 varchar、TEXT、BLOB类型，则会被认为不是固定长度的表。mysql会选择俩种不同的引擎处理。快是因为这些固定的长度是很容易计算下一个数据的偏移量的，所以读取的自然也会很快。而如果字段不是定长的，那么，每一次要找下一条的话，需要程序找到主键。 唯一的副作用是，固定长度的字段会浪费一些空间，因为定长的字段无论你用不用，他都是要分配 那么多的空间

16. 垂直分割 ，“垂直分割”是一种把数据库中的表按列变成几张表的方法，这样可以降低表的复杂度和字段的数目，从而达到优化的目的，经常更新的字段单独拎出来到另外的表。 这些被分出去的字段所形成的表，你不会经常性地去 Join 他们，不然的话，这样的性能会比不分割时还要差

17. 拆分大的 DELETE 或 INSERT 语句，这俩个操作会锁表， 可以在delete的时候用limit分批操作。

18. 越小的列会越快。如果你不需要记录时间，使用 DATE 要比 DATETIME 好得多。如果一个表的列比较少，可以使用MEDIUMINT, SMALLINT 或是更小的 TINYINT 当主键

19. 选择正确的存储引擎.MyISAM 和 InnoDB,MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好，甚至你只是需要 update 一个字段，整个表都会被锁起来。InnoDB 支持行锁，事务

20. 使用一个对象关系映射器(Object Relational Mapper)。ORM框架有 Lazy Loading。只有在需要的去取值的时候才会去真正的去做。ORM 还可以把你的 SQL 语句打包成一个事务，这会比单独执行他们快得多得多

21. 小心“永久链接” 或者长连接sql

大数据量插入

大批量插入数据sql
首先使用：foreach 循环插入

<insert id="saveBatch"> 
insert into test_table(bs_id, ref_plan_id, ctr_id) 
<foreach collection="dataList" item="data" separator ="union all"> 
    select #{data.bsId}, #{data.refPlanId}, #{data.ctrId} from dual 
</foreach> 
</insert>

更佳方式： mybatis的默认执行器类型为Simple，会为每个语句创建一个新的预处理语句，也就是创建一个PreparedStatement对象，当values特别多的时候，占位符的 “？”替换参数的过程会特别耗时。可以使用了 ExecutorType.BATCH 的插入方式，性能显著提升，很快便能全部插入完成。

public void batchInsertData( List<Person> records) {    
     SqlSession sqlSession = sqlSessionFactory.openSession(ExecutorType.BATCH);   
     PersonMapper mapper = sqlSession.getMapper(PersonMapper.class);      records.forEach(mapper::insert);
}