做刷票的网站,快速进入网站,网页设计报价怎么做,网站视频超链接怎么做前言 之前已经简单介绍了MySQL的优化步骤#xff0c;那么接下来自然而是就是常用的SQL优化#xff0c;比如inseer、group by等常用SQL的优化#xff0c;会涉及SQL语句内部细节#xff08;这正是我缺乏的#xff09;。最后希望自己能记录完成的一套MySQL优化博文#xff0…前言 之前已经简单介绍了MySQL的优化步骤那么接下来自然而是就是常用的SQL优化比如inseer、group by等常用SQL的优化会涉及SQL语句内部细节这正是我缺乏的。最后希望自己能记录完成的一套MySQL优化博文 注其中部分我并没有全部实验并不代表是错的这里只相当于记录下接下来会慢慢补充 参考资料《深入浅出MySQL》有需要PDF电子书的伙伴可以评论或者私信我 1、大批量插入数据优化 1对于MyISAM存储引擎的表可以使用DISABLE KEYS 和 ENABLE KEYS 用来打开或者关闭 MyISAM 表非唯一索引的更新。 ALTER TABLE tbl_name DISABLE KEYS;
loading the data
ALTER TABLE tbl_name ENABLE KEYS; 2对于InnoDB引擎有以下几种优化措施 ① 导入的数据按照主键的顺序保存这是因为InnoDB引擎表示按照主键顺序保存的如果能将插入的数据提前按照排序好自然能省去很多时间。 比如bulk_insert.txt文件是以表user主键的顺序存储的导入的时间为15.23秒 mysql load data infile mysql/bulk_insert.txt into table user;
Query OK, 126732 rows affected (15.23 sec)
Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 没有按照主键排序的话时间为26.54秒 mysql load data infile mysql/bulk_insert.txt into table user;
Query OK, 126732 rows affected (26.54 sec)
Records: 126732 Deleted: 0 Skipped: 0 Warnings: 0 ② 导入数据前执行SET UNIQUE_CHECKS0关闭唯一性校验带导入之后再打开设置为1校验会消耗时间在数据量大的情况下需要考虑。 ③ 导入前设置SET AUTOCOMMIT0关闭自动提交导入后结束再设置为1这是因为自动提交会消耗部分时间与资源虽然消耗不是很大但是在数据量大的情况下还是得考虑。 2、INSERT的优化 1尽量使用多个值表的 INSERT 语句这种方式将大大缩减客户端与数据库之间的连接、关闭等消耗。同一客户的情况下即 INSERT INTO tablename values(1,2),(1,3),(1,4) 实验插入8条数据到user表中使用navicat客户端工具 insert into user values(1,test,replace(uuid(),-,));
insert into user values(2,test,replace(uuid(),-,));
insert into user values(3,test,replace(uuid(),-,));
insert into user values(4,test,replace(uuid(),-,));
insert into user values(5,test,replace(uuid(),-,));
insert into user values(6,test,replace(uuid(),-,));
insert into user values(7,test,replace(uuid(),-,));
insert into user values(8,test,replace(uuid(),-,)); 得到反馈 [SQL] insert into user values(1,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.033s
[SQL]
insert into user values(2,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.034s
[SQL]
insert into user values(3,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.056s
[SQL]
insert into user values(4,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.008s
[SQL]
insert into user values(5,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.008s
[SQL]
insert into user values(6,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.024s
[SQL]
insert into user values(7,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.004s
[SQL]
insert into user values(8,test,replace(uuid(),-,));
受影响的行: 1
时间: 0.004s 总共的时间为0.171秒接下来使用多值表形式 insert into user values
(9,test,replace(uuid(),-,)),
(10,test,replace(uuid(),-,)),
(11,test,replace(uuid(),-,)),
(12,test,replace(uuid(),-,)),
(13,test,replace(uuid(),-,)),
(14,test,replace(uuid(),-,)),
(15,test,replace(uuid(),-,)),
(16,test,replace(uuid(),-,)); 得到反馈 [SQL] insert into user values
(9,test,replace(uuid(),-,)),
(10,test,replace(uuid(),-,)),
(11,test,replace(uuid(),-,)),
(12,test,replace(uuid(),-,)),
(13,test,replace(uuid(),-,)),
(14,test,replace(uuid(),-,)),
(15,test,replace(uuid(),-,)),
(16,test,replace(uuid(),-,));
受影响的行: 8
时间: 0.038s 得到时间为0.038这样一来可以很明显节约时间优化SQL 2如果在不同客户端插入很多行可使用INSERT DELAYED语句得到更高的速度DELLAYED含义是让INSERT语句马上执行其实数据都被放在内存的队列中。并没有真正写入磁盘。LOW_PRIORITY刚好相反。 3将索引文件和数据文件分在不同的磁盘上存放InnoDB引擎是在同一个表空间的。 4如果批量插入则可以增加bluk_insert_buffer_size变量值提供速度只对MyISAM有用 5当从一个文本文件装载一个表时使用LOAD DATA INFILE通常比INSERT语句快20倍。 3、GROUP BY的优化 在默认情况下MySQL中的GROUP BY语句会对其后出现的字段进行默认排序非主键情况就好比我们使用ORDER BY col1,col2,col3...所以我们在后面跟上具有相同列与GROUP BY后出现的col1,col2,col3...相同ORDER BY子句并没有影响该SQL的实际执行性能。 那么就会有这样的情况出现我们对查询到的结果是否已经排序不在乎时可以使用ORDER BY NULL禁止排序达到优化目的。下面使用EXPLAIN命令分析SQL。 在user_1中执行select id, sum(money) form user_1 group by name时会默认排序注意group by后的column是非index才会体现group by的排序如果是primary key那之前说过了InnoDB默认是按照主键index排好序的 mysql select*from user_1;
---------------------
| id | name | money |
---------------------
| 1 | Zhangsan | 32 |
| 2 | Lisi | 65 |
| 3 | Wangwu | 44 |
| 4 | Lijian | 100 |
---------------------
4 rows in set 不禁止排序即不使用ORDER BY NULL时有明显的Using filesort。 当使用ORDER BY NULL禁止排序后Using filesort不存在 4、ORDER BY 的优化 MySQL可以使用一个索引来满足ORDER BY 子句的排序而不需要额外的排序但是需要满足以下几个条件 1WHERE 条件和OREDR BY 使用相同的索引即key_part1与key_part2是复合索引where中使用复合索引中的key_part1 SELECT*FROM user WHERE key_part11 ORDER BY key_part1 DESC, key_part2 DESC; 2而且ORDER BY顺序和索引顺序相同 SELECT*FROM user ORDER BY key_part1, key_part2; 3并且要么都是升序要么都是降序 SELECT*FROM user ORDER BY key_part1 DESC, key_part2 DESC; 但以下几种情况则不使用索引 1ORDER BY中混合ASC和DESC SELECT*FROM user ORDER BY key_part1 DESC, key_part2 ASC; 2查询行的关键字与ORDER BY所使用的不相同即WHERE 后的字段与ORDER BY 后的字段是不一样的 SELECT*FROM user WHERE key2 ‘xxx’ ORDER BY key1; 3ORDER BY对不同的关键字使用即ORDER BY后的关键字不相同 SELECT*FROM user ORDER BY key1, key2; 5、OR的优化 当MySQL使用OR查询时如果要利用索引的话必须每个条件列都使独立索引而不是复合索引多列索引才能保证使用到查询的时候使用到索引。 比如我们新建一张用户信息表user_info mysql select*from user_info;
--------------------------------------
| user_id | idcard | name | address |
--------------------------------------
| 1 | 111111 | Zhangsan | Kunming |
| 2 | 222222 | Lisi | Beijing |
| 3 | 333333 | Wangwu | Shanghai |
| 4 | 444444 | Lijian | Guangzhou |
--------------------------------------
4 rows in set 之后创建ind_name_id(user_id, name)复合索引、id_index(id_index)独立索引idcard主键索引三个索引。 mysql show index from user_info;
---------------------------------------------------------------------------------------------------------------------------------------------------
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
---------------------------------------------------------------------------------------------------------------------------------------------------
| user_info | 0 | PRIMARY | 1 | idcard | A | 4 | NULL | NULL | | BTREE | | |
| user_info | 1 | ind_name_id | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | |
| user_info | 1 | ind_name_id | 2 | name | A | 4 | NULL | NULL | YES | BTREE | | |
| user_info | 1 | id_index | 1 | user_id | A | 4 | NULL | NULL | | BTREE | | |
---------------------------------------------------------------------------------------------------------------------------------------------------
4 rows in set 测试一OR连接两个有单独索引的字段整个SQL查询才会用到索引(index_merge)并且我们知道OR实际上是把每个结果最后UNION一起的。 mysql explain select*from user_info where user_id1 or idcard222222;
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | index_merge | PRIMARY,ind_name_id,id_index | ind_name_id,PRIMARY | 4,62 | NULL | 2 | 100 | Using sort_union(ind_name_id,PRIMARY); Using where |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set 测试二OR使用复合索引的字段name与没有索引的address整个SQL都是ALL全表扫描的 mysql explain select*from user_info where nameZhangsan or addressBeijing;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 43.75 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set 交换OR位置并且使用另外的复合索引的列也是ALL全表扫描 mysql explain select*from user_info where addressBeijing or user_id1;
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 43.75 | Using where |
----------------------------------------------------------------------------------------------------------------------
1 row in set 6、优化嵌套查询 使用嵌套查询有时候可以使用更有效的JOIN连接代替这是因为MySQL中不需要在内存中创建临时表完成SELECT子查询与主查询两部分查询工作。但是并不是所有的时候都成立最好是在on关键字后面的列有索引的话效果会更好 比如在表major中major_id是有索引的 select * from student u left join major m on u.major_idm.major_id where m.major_id is null; 而通过嵌套查询时在内存中创建临时表完成SELECT子查询与主查询两部分查询工作会有一定的消耗 select * from student u where major_id not in (select major_id from major); 7、使用SQL提示 SQL提示SQL HINT是优化数据库的一个重要手段就是往SQL语句中加入一些人为的提示来达到优化目的。下面是一些常用的SQL提示 1USE INDEX使用USE INDEX是希望MySQL去参考索引列表就可以让MySQL不需要考虑其他可用索引其实也就是possible_keys属性下参考的索引值 mysql explain select* from user_info use index(id_index,ind_name_id) where user_id0;
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where |
----------------------------------------------------------------------------------------------------------------------
1 row in setmysql explain select* from user_info use index(id_index) where user_id0;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | id_index | NULL | NULL | NULL | 4 | 100 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set 2IGNORE INDEX忽略索引 我们使用user_id判断用不到其他索引时可以忽略索引。即与USE INDEX相反从possible_keys中减去不需要的索引但是实际环境中很少使用。 mysql explain select* from user_info ignore index(primary,ind_name_id,id_index) where user_id0;
---------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
---------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | NULL | NULL | NULL | NULL | 4 | 33.33 | Using where |
---------------------------------------------------------------------------------------------------------------
1 row in set 3FORCE INDEX强制索引 比如where user_id 0但是user_id在表中都是大于0的自然就会进行ALL全表搜索但是使用FORCE INDEX虽然执行效率不是最高where user_id 0条件决定的但MySQL还是使用索引。 mysql explain select* from user_info where user_id0;
----------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
----------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | ALL | ind_name_id,id_index | NULL | NULL | NULL | 4 | 100 | Using where |
----------------------------------------------------------------------------------------------------------------------
1 row in set 之后强制使用独立索引id_index(user_id) mysql explain select* from user_info force index(id_index) where user_id0;
------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user_info | NULL | range | id_index | id_index | 4 | NULL | 4 | 100 | Using index condition |
------------------------------------------------------------------------------------------------------------------------------
1 row in set 总结 1很多时候数据库的性能是由于不合适是指效率不高可能会导致锁表等的SQL语句造成本篇博文只是介绍简单的SQL优化 2其中有些优化在真正开发中是用不到的但是一旦出问题性能下降的时候需要去一一分析。 转载于:https://www.cnblogs.com/jian0110/p/9410981.html
