mysql怎么提升效率 mysql性能提升
如何提升MySQL批量插入的效率
需要将大量数据(大概5W条)插入MySQL数
创新互联建站服务项目包括随州网站建设、随州网站制作、随州网页制作以及随州网络营销策划等。多年来,我们专注于互联网行业,利用自身积累的技术优势、行业经验、深度合作伙伴关系等,向广大中小型企业、政府机构等提供互联网行业的解决方案,随州网站推广取得了明显的社会效益与经济效益。目前,我们服务的客户以成都为中心已经辐射到随州省份的部分城市,未来相信会继续扩大服务区域并继续获得客户的支持与信任!
据库,用普通的SQL
Statement执行,时间大概是几分钟。于是想到用PreparedStatement,但是改了之后发现效率并没有很大的提升。不成,想到了
load data local
infile...命令,以前一直认为这条命令仅限MySQL终端上使用而不是标准的SQL语句,今天看了几篇文章之后有了很大的收获。
1. 使用PreparedStatement batch operation
以前使用PreparedStatement性能没有很大提升的原因在于:
没有使用批处理方法
在语句执行之前应关闭事务自动提交,语句执行完之后再提交
public
void batchLoad(Connection connection)
{
try
{
connection.setAutoCommit(false);
BufferedReader reader =
new BufferedReader(new
FileReader("tfacts_result"));
String sqlString =
"insert into test(node1, node2, weight) values(?, ?, ?)";
PreparedStatement pstmt = connection.prepareStatement(sqlString);
String line =
null;
while(true)
{
line = reader.readLine();
if(line == null)
{
break;
}
String[] columns = line.split("\t");
for(int
i = 1; i = columns.length; i++)
{
pstmt.setString(i, columns[i-1]);
}
pstmt.addBatch();
}
pstmt.executeBatch();
connection.commit();
pstmt.close();
reader.close();
}
catch (FileNotFoundException e) {
e.printStackTrace();
}catch
(SQLException e){
e.printStackTrace();
}catch
(IOException e){
e.printStackTrace();
}
2.使用load data local infile into tabel XXX(注意在文件中用\t将每列数据隔开)
public
void loadData(Connection connection)
{
long
starTime = System.currentTimeMillis();
String sqlString =
"load data local infile ? into table test";
PreparedStatement pstmt;
try
{
pstmt = connection.prepareStatement(sqlString);
pstmt.setString(1,
"tfacts_result");
pstmt.executeUpdate();
pstmt.close();
}
catch (SQLException e) {
e.printStackTrace();
}
long
endTime = System.currentTimeMillis();
System.out.println("program runs "
+ (endTime - starTime) + "ms");
}
测试了5W条数据,PreparedStatement耗时10s,而load data infile耗时3s。
mysqlexecutebatch效率慢
mysqlexecutebatch效率慢
1. 数据查询过慢一般是索引问题,可能是因为选错索引,也可能是因为查询的行数太多。
2.客户端和数据库连接数过小,会限制sql的查询并发数,增大连接数可以提升速度。
3.innodb里会有一层内存buffer pool用于提升查询速度,命中率一般99%,如果低于这个值,可以考虑增大buffer pool的大小,这样也可以提升速度。
提高MySQL数据库查询效率有哪些技巧?
1.合理使用索引
索引是数据库中重要的数据结构,它的根本目的就是为了提高查询效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:
●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度。
●如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。
●使用系统工具。如Informix数据库有一个tbcheck工具,可以在可疑的索引上进行检查。在一些数据库服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的查询不明不白地慢下来,可以试着用tbcheck工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量数据后,删除并重建索引可以提高查询速度。
2.避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。以下是一些影响因素:
●索引中不包括一个或几个待排序的列;
●group by或order by子句中列的次序与索引的次序不一样;
●排序的列来自不同的表。
为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。
3.消除对大型表行数据的顺序存取
在嵌套查询中,对表的顺序存取对查询效率可能产生致命的影响。比如采用顺序存取策略,一个嵌套3层的查询,如果每层都查询1000行,那么这个查询就要查询10亿行数据。避免这种情况的主要方法就是对连接的列进行索引。例如,两个表:学生表(学号、姓名、年龄……)和选课表(学号、课程号、成绩)。如果两个表要做连接,就要在“学号”这个连接字段上建立索引。
还可以使用并集来避免顺序存取。尽管在所有的检查列上都有索引,但某些形式的where子句强迫优化器使用顺序存取。下面的查询将强迫对orders表执行顺序操作:
SELECT * FROM orders WHERE (customer_num=104 AND order_num;1001) OR order_num=1008
虽然在customer_num和order_num上建有索引,但是在上面的语句中优化器还是使用顺序存取路径扫描整个表。因为这个语句要检索的是分离的行的集合,所以应该改为如下语句:
SELECT * FROM orders WHERE customer_num=104 AND order_num;1001
UNION
SELECT * FROM orders WHERE order_num=1008
这样就能利用索引路径处理查询。
4.避免相关子查询
一个列的标签同时在主查询和where子句中的查询中出现,那么很可能当主查询中的列值改变之后,子查询必须重新查询一次。查询嵌套层次越多,效率越低,因此应当尽量避免子查询。如果子查询不可避免,那么要在子查询中过滤掉尽可能多的行。
5.避免困难的正规表达式
MATCHES和LIKE关键字支持通配符匹配,技术上叫正规表达式。但这种匹配特别耗费时间。例如:SELECT * FROM customer WHERE zipcode LIKE “98_ _ _”
即使在zipcode字段上建立了索引,在这种情况下也还是采用顺序扫描的方式。如果把语句改为SELECT * FROM customer WHERE zipcode ;“98000”,在执行查询时就会利用索引来查询,显然会大大提高速度。
另外,还要避免非开始的子串。例如语句:SELECT * FROM customer WHERE zipcode[2,3] ;“80”,在where子句中采用了非开始子串,因而这个语句也不会使用索引。
6.使用临时表加速查询
7.用排序来取代非顺序存取
怎么提高数据库查询效率
提高查询效率首先要想到的就是加索引,那什么是索引呢?
MySQL索引的建立对于MySQL的高效运行是很重要的,索引可以大大提高MySQL的检索速度。
打个比方,如果合理的设计且使用索引的MySQL是一辆兰博基尼的话,那么没有设计和使用索引的MySQL就是一个人力三轮车。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
如何使用索引呢?
首先索引有窄索引和宽索引两个概念,窄索引是指索引的列数为1~2,宽索引就是说索引的列数大于2。
因为窄索引的效率要高于宽索引,所以能用窄索引就不要使用宽索引。
那么对单字段索引和复合索引应该如何使用?
目录
单字段索引的情况:
复合索引的优势:
两者的比较:
单字段索引的情况:
1.表的主键,外键必须有索引
2.数据量超过300的表应该有索引
3.经常与其他表进行连接的表,在连接字段上应该建立索引
4.经常出现在where字句中的字段,特点是大表的字段,应该建立索引
5.索引应该建在选择性高的字段上
6.索引应该建在小字段上,对于大的文本字段甚至超长字段,不要建立索引
7.尽量用单字段索引代替复合索引,复合索引的建立需要仔细的斟酌
复合索引的优势:
1.单字段索引很少甚至没有
2.复合索引的几个字段经常同时以AND的方式出现在where语句
当where语句中的条件是OR时,索引不起作用。
两者的比较:
以一个sql语句来举例:SELECT * FROM STUDENT WHERE SEX="男" AND SAGE=18;
若在sex 和 sage 两个字段分别创建了单字段索引,mysql查询每次只能使用一个索引,虽然对于未添加索引时使用全盘扫描,我们的效率提升了很多,但如果在sex 和 sage两个字段添加复合索引,效率会跟高,如: 创建(sex, age,teacher)的复合索引,那么其实相当于创建了(area,age,teacher)、(area,age)、(area)三个索引,这被称为最佳左前缀特性。
那对于两者优缺点的比较:
1.对于具有2个用and连接条件的语句,且2个列之间的关联度较低的情况下,复合索引有一定优势。
2.对于具有2个用and连接条件的语句,且2个列之间的关联度较高的情况下,复合索引有很大优势。
3.对于具有2个用or连接条件的语句,单索引有一定优势,因为这种情况下复合索引将会导致全表扫描,而前者可以用到indexmerge的优化。
以上就是如何提高查询效率的全部内容,如果有帮助到你的话记得点个关注哟
分享题目:mysql怎么提升效率 mysql性能提升
网页地址:http://myzitong.com/article/doosssp.html