【MySQL】20个经典面试题
最全MySQL面试题和答案
MYSQL调优
数据库备份
SQL 优化的一般步骤
常用的SQL的优化
sql语句练习50题(Mysql版)
MySql面试题
网站首页
常用的SQL的优化
#### 1. 对大批量插入数据 - MYISAM 表 可以通过DISABLE KEY 和 ENABLE KEY 关闭和打开MYISAM 表索引的更新来提高效率。 ```sql ALTER TABLE table_name DISABLE KEY //在导入数据前关闭索引更新 loading the data ALTER TABLE table_name ENABLE KEY //导入完成后开启 ``` 一个书上的例子： 直接导入数据(115.12 s) VS 优化导入(18.59 s) - InnoDB 表 - 按主键顺序导入 - 关闭唯一性检验，导入后再开启 - 关闭自动提交，导入后再开启 #### 2. 优化INSERT 语句 - 同一个客户端，应使用多个值表的insert 语句，这种方式可以大大缩减客户端与数据库之间的连接、关闭等消耗。 ```sql insert into table_name values(1,2)(1,3)(1,4)... ``` - 从不同的客户端插入的话，可以采用INSERT DELAYED 语句获得更高的速度。它的意思是让INSERT 语句马上执行，因为数据都被放在内存的队列中，并没有真正写入磁盘。 - 从文本文件装载一个表时，使用LOAD DATA INFILE 通常比INSERT 快20 倍。 - 将索引文件和数据文件分放在不同的磁盘上（利用建表中的选项）。 - 如果是批量插入，MYISAM 可以通过改变bulk_insert_buffer_size （插入缓存容量大小）变量值提高速度。 #### 3. 优化order by 语句 在MySQL 数据库中有两种排序方式： - 通过有序索引扫描直接返回有序数据 - 通过对返回数据进行排序（Filesort 排序） 其中Filesort 会多排一次序，所以在使用order by 语句的时候尽量用到索引。下面的规则是可以用到索引的情况： - where 和 order by 使用相同的索引 - order by 的顺序和索引的顺序相同 - order by 的字段都是升序或都是降序 同理用不到索引的情况： - order by 的字段混合ASC 和DESC - 用于查询行的关键字与ORDER BY 中所使用的不同 - 对不同的关键字使用order by #### 4. 优化group by 语句 默认情况下group by 语句对分组的数据进行排序操作，如果不需要排序操作可以通过order by null 禁止排序。 #### 5. 优化嵌套语句 如果可以的话，特别是where 中包含索引的情况，用join 语法来代替嵌套语法（in）因为join 不需要在MySQL 的内存中创建临时表。 #### 6. 优化or语句 对于含有or 的查询语句，如果要利用索引，则or 之间的每一个条件都必须用到索引，如果没用索引，可以考虑增加索引。否则会全表扫面。 #### 7. 优化分页查询 一般分页查询时，通过创建覆盖索引能够比较好的提高性能。一个常见又头痛的场景是"limit 1000,20" 此时MySQL 排序出前1020 条数据后，只需要返回20条数据，查询和排序的代价都很高。有两种优化方案 - 在索引上完成排序分页的操作，最后根据主键关联查询需要的内容，让MySQL 扫描尽可能少的页面来提高分页操作。 ```sql mysql> explain select comment_id,comment_content from comment order by comment_create_time limit 50,5; +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ | 1 | SIMPLE | comment | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+ 1 row in set mysql> explain select a.comment_id,a.comment_content from comment a inner join(select comment_id from comment order by comment_create_time limit 50,5)b on a.comment_id=b.comment_id ; +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ | 1 | PRIMARY | <derived2> | NULL | ALL | NULL | NULL | NULL | NULL | 55 | 100 | NULL | | 1 | PRIMARY | a | NULL | eq_ref | PRIMARY | PRIMARY | 4 | b.comment_id | 1 | 100 | NULL | | 2 | DERIVED | comment | NULL | ALL | NULL | NULL | NULL | NULL | 92 | 100 | Using filesort | +----+-------------+------------+------------+--------+---------------+---------+---------+--------------+------+----------+----------------+ 3 rows in set ``` - 把limit 查询转换成某个位置的查询，limit m,n -> limit n #### 8. 使用SQL 提示 SQL 提示是优化数据库的一个重要手段，常用的SQL 提示： USE INDEX 推荐数据库使用某个索引，可以让Mysql 不再考虑其他可用索引 ```sql sql语句 use index(index_name); ``` IGNORE INDEX 忽视数据库某个索引，可以让Mysql 不再考虑这个索引 ```sql sql语句 ignore index(index_name); ``` FORCE INDEX 强迫数据库使用某个索引，使用use index 数据库还是可能不用这个索引，但是force index 数据库必须使用这个索引 ```sql sql语句 force index(index_name); ``` ---- 原文链接：https://blog.csdn.net/qq_36906627/article/details/86634518
上一篇：
SQL 优化的一般步骤
下一篇：
sql语句练习50题(Mysql版)
