欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  IT编程

mysql 开发进阶篇系列 4 SQL 优化

程序员文章站 2022-05-28 21:22:22
1 通过handler_read 查看索引使用情况 如果索引经常被用到 那么handler_read_key的值将很高,这个值代表了一个行被索引值读的次数, 很低的值表明增加索引得到的性能改善不高,索引并不经常使用。 handler_read_rnd_next 的值高 则意味着查询运行低效,应该建立 ......

1 通过handler_read 查看索引使用情况

  如果索引经常被用到 那么handler_read_key的值将很高,这个值代表了一个行被索引值读的次数, 很低的值表明增加索引得到的性能改善不高,索引并不经常使用。
handler_read_rnd_next 的值高 则意味着查询运行低效,应该建立索引, 这个值表示在数据文件中读下一行的请求数,如果是正进行大量扫描 值会较高,一般是索引不正确或没有利用到索引。

SHOW STATUS LIKE 'Handler_read%';

mysql 开发进阶篇系列 4 SQL 优化

2. 优化定期分析表

  analyze 语句用于分析和存储表的关键字分布,分析的结果将可以使得系统得到准确的统计信息,使得sql能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划,执行一次分析表可能会解决问题。 在分析时使用一个读取锁对表进行了锁定,这个对于myisam,bdb,innodb表有作用。

-- 分析表
ANALYZE TABLE city;

mysql 开发进阶篇系列 4 SQL 优化

3. 优化检查表

  check 检查表的作用是检查一个或多个表是否有错误。check table对myisam和innodb表有作用。

-- 检查表
CHECK TABLE city;

mysql 开发进阶篇系列 4 SQL 优化

4.    优化optimize

  如果一个表已经删除了一大部分,更者对可变长度行的表(varchar,blob,text)进行了很多更改,则就使用optimize table命令来进行优化, 它是将表空间碎片进行合并,可以消除由于删除或者更新造成的空间浪费,对myisam, bdb ,innodb表起作用。具体参考

 -- 优化表
OPTIMIZE TABLE city;

mysql 开发进阶篇系列 4 SQL 优化

  总结: analyze, check, OPTIMIZE 执行期间将对表进行锁定,在繁忙时候不要操作

5.  优化大批量插入数据

   5.1 针对大量数据导入到一个非空的myisam表,可以通过以下方式快速导入大量数据。

    ALTER TABLE tab_name DISABLE KEYS;
        loading the DATA
    ALTER TABLE tab_name ENABLE KEYS;

     DISABLE KEYS和 ENABLE KEYS是打开或者关闭myisam表非唯一索引的更新,对于myisam空表则默认是先导入数据然后才创建索引,所以不用设置。

  5.2 针对innodb表

     在导入之前设置unique_checks=0 导完后开启set unique_checks=1。设置autocommit=0 导完后开启autocommit=1。

6. 优化insert 语句

(1) 不同客户插入很多行数据时,更改INSERT INTO为 INSERT DELAYED INTO,这使语句得到更高的速度。
(2) 将索引文件和磁盘文件分在不同磁盘上存放(利用表的选项)。
(3) 如果是批量插入 对myisam表可使用bulk_insert_buffer_size 来提高速度。
(4) 使用load data infile 通常比insert语句快20倍。

7. 优化group by语句

  默认情况下 group by 会对字段进行排序,如果想避免排序结果带来的消耗,可以指定order by null 来禁止排序 如下:

-- CityCode  默认使用了排序   (如果CityCode已建索引,默认就排序好了 不用优化)
EXPLAIN SELECT COUNT(country_id), CityCode FROM city   GROUP BY CityCode

mysql 开发进阶篇系列 4 SQL 优化

-- 使用 order by null 来禁止排序
EXPLAIN SELECT COUNT(country_id), CityCode FROM city   GROUP BY CityCode ORDER BY NULL

mysql 开发进阶篇系列 4 SQL 优化

8. 优化order by 语句

  mysql 可以使用一个索引来满足order by 子句,而不需要额外的排序(上面group by 就是未键索引 需要再排序),并且order by 的顺序与索引顺序相同,升序或降序。

--  order by 使用到了索引的排序
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id,city_id;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id ASC ,city_id ASC;
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id DESC ,city_id DESC;
-- order by 未使用到索引的排序
EXPLAIN SELECT country_id, city_id FROM city   GROUP BY country_id,city_id ORDER BY country_id ASC ,city_id DESC;