一、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类型的表和索引的最大缓存。这个值越大,查询的速度就会越快。这个值太大了就会影响操作系统的性能。
四、事务优化
- 脏读:一个事务对数据进行了修改,但还没有提交到数据库中,这时另一个事务也访问了同一份数据。
- 不可重复读:在一个事务内多次读取同一份数据。在这个事务还没有结束时,另一个事务也读取了这份数据。那么,在第一个事务读取数据之间,第二个事务对这个数据进行了修改,这个时候第一个事务两次读取的数据就可以不一样。这样就发生了一个事务内两次读取的数据不一样,即不可重复读。
- 幻读: