mysql 开发进阶篇系列 4 SQL 优化(各种优化方法点

2019-09-20 06:11 来源:未知

彩民之家高手论坛 1

3 优化group by语句

如果查询包含group by但用户想要避免排序结果的损耗,则可以使用使用order by null来禁止排序:

  如下没有使用order by null来禁止排序

mysql> explain select id,sum(moneys) from sales2 group by idG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using temporary;Using filesort
1 row in set (0.00 sec)

如下使用order by null的效果:

mysql> explain select id,sum(moneys) from sales2 group by id order by nullG
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using temporary
1 row in set (0.00 sec)

3.系统的瓶颈 1).磁盘搜索.
并行搜索,把数据分开存放到多个磁盘中,这样能加快搜索时间。
2).磁盘读写(IO)
可以从多个媒介中并行的读取数据。
3).CPU周期
数据存放在主内存中.这样就得增加CPU的个数来处理这些数据。
4).内存带宽
当CPU要将更多的数据存放到CPU的缓存中来的话,内存的带宽就成了瓶颈。

SHOW STATUS LIKE 'Handler_read%';

 

? contents 2.使用合成的散列值,分离BLOB或者TEXT 3.货币使用定点数(decimal或者numberic) 4.sql_mode? 5.order by rand() limit 1000; 6.优化show stat...

彩民之家高手论坛 2

接上一部分

(4)如果不是索引列的第一部分,如下例子:可见虽然在money上面建有复合索引,但是由于money不是索引的第一列,那么在查询中这个索引也不会被MySQL采用。

mysql> explain select * from sales2 where moneys=1 G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sales2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

(5)如果like是以%开始,可见虽然在name上面建有索引,但是由于where条件中like的值的“%”在第一位了,那么MySQL也会采用这个索引。

mysql> explain select * from company2 where name like‘%3’G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

(6)如果列类型是字符串,但在查询时把一个数值型常量赋值给了一个字符型的列名name,那么虽然在name列上有索引,但是也没有用到。

mysql> explain select * from company2 where name=294G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ALL
possible_keys: ind_company2_name
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using where
1 row in set (0.00 sec)

 

  而下面的sql语句就可以正确使用索引

mysql> explain select * from company2 where name=‘294’G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ref
possible_keys: ind_company2_name
          key: ind_company2_name
      key_len: 23
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

mysql> select count(1) from song2;
----------
| count(1) |
----------
|   500000 |
----------
1 row in set (0.91 sec)
mysql> delete from song2;
Query OK, 500000 rows affected (15.70 sec)
mysql> truncate table song2;
Query OK, 502238 rows affected (0.17 sec)

彩民之家高手论坛 3

两个简单实用的优化方法

分析表的语法如下:(检查一个或多个表是否有错误 )

mysql> CHECK TABLE tbl_name[,tbl_name] … [option] … option =
  { QUICK | FAST | MEDIUM | EXTENDED | CHANGED}

mysql> check table sales;
-------------- ------- ---------- ----------
| Table        | Op    | Msg_type | Msg_text |
-------------- ------- ---------- ----------
| sakila.sales | check | status   | OK       |
-------------- ------- ---------- ----------
1 row in set (0.01 sec)

优化表的语法格式:

OPTIMIZE [LOCAL | NO_WRITE_TO_BINLOG] TABLE tbl_name [,tbl_name]

如果已经删除了表的一大部分,或者如果已经对含有可变长度行的表进行了很多的改动,则需要做定期优化。这个命令可以将表中的空间碎片进行合并,但是此命令只对MyISAM、BDB和InnoDB表起作用。

mysql> optimize table sales;
-------------- ---------- ---------- ----------
| Table        | Op       | Msg_type | Msg_text |
-------------- ---------- ---------- ----------
| sakila.sales | optimize | status   | OK       |
-------------- ---------- ---------- ----------
1 row in set (0.05 sec)

innodb_flush_log_at_trx_commit:0|1|2
innodb_additional_mem_pool_size:1M
innodb_table_locks:0|1
innodb_lock_wait_timeout:
innodb_support_xa:通过该参数设置是否支持分布式事务,默认值是ON 或者1,表示支持分布式事务。如果确认应用中不需要使用分布式事务,则可以关闭这个参数,减少磁盘刷新的次数并获得更好的InnoDB 性能。
innodb_doublewrite:
innodb_log_buffer_size:
innodb_log_file_size:

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

 4.1 优化表的类型

在MySQL中,可以使用函数PROCEDUREANALYSE()对当前应用的表进行分析,改函数可以对数据表中列的数据类型提出优化建议,用户可以根据应用的实际情况酌情考虑是否实施

mysql> select * from duck_cust procedure analyse()G
*************************** 1. row ***************************
             Field_name: sakila.duch_cust.cust_num
              Min_value: 1
              Max_value: 6
             Min_length: 1
             Max_length: 1
       Empties_or_zeros: 0
                  Nulls: 0
Avg_value_or_avg_length: 3.5000
                    Std: 1.7078
      Optimal_fieldtype: ENUM(‘1’,‘2’,‘3’,‘4’) NOT NULL
*************************** 2. row ***************************
  … …

2.系统的用途 1).及时的关闭对MYSQL的连接。
2).explain 复杂的SQL语句。(这样能确定你的SELECT 语句怎么优化最佳)
3).如果两个关联表要做比较话,做比较的字段必须类型和长度都一致.(在数据庞大的时候建立INDEX)
4).LIMIT语句尽量要跟order by或者 distinct.这样可以避免做一次full table scan.
5).如果想要清空表的所有纪录,建议用truncate table tablename而不是delete from tablename.
不过有一个问题,truncate 不会在事务处理中回滚。因为她要调用create table 语句。
(Truncate Table 语句先删除表然后再重建,这个是属于文件级别的,所以自然快N多)
实测例子:
song2为INNODB表。

2. 优化定期分析表

(2)关闭唯一性效验可以提高导入效率

在导入数据前先执行set unique_checks=0,关闭唯一性效验,在导入结束后执行set unique_checks=1,恢复唯一性效验,可以提高导入效率。

当unique_checks=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当unique_checks=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (19.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

1.数据库的设计 尽量把数据库设计的更小的占磁盘空间.
1).尽可能使用更小的整数类型.(mediumint就比int更合适).
2).尽可能的定义字段为not null,除非这个字段需要null.(这个规则只适合字段为KEY的情形)
3).如果没有用到变长字段的话比如varchar,那就采用固定大小的纪录格式比如char.(CHAR 总是比VARCHR快)
4).表的主索引应该尽可能的短.这样的话每条纪录都有名字标志且更高效.
5).只创建确实需要的索引。索引有利于检索记录,但是不利于快速保存记录。如果总是要在表的组合字段上做搜索,那么就在这些字段上创建索引。索引的第一部分必须是最常使用的字段.如果总是需要用到很多字段,首先就应该多复制这些字段,使索引更好的压缩。
(这条只适合MYISAM引擎的表,对于INNODB则在保存记录的时候关系不大,因为INNODB是以事务为基础的,如果想快速保存记录的话,特别是大批量的导入记录的时候)
6).所有数据都得在保存到数据库前进行处理。
7).所有字段都得有默认值。
8).在某些情况下,把一个频繁扫描的表分成两个速度会快好多。在对动态格式表扫描以取得相关记录时,它可能使用更小的静态格式表的情况下更是如此。
(具体的表现为:MYISAM表的MERGE类型,以及MYISAM和INNODB通用的分区,详情见手册)
9).不会用到外键约束的地方尽量不要使用外键。

--  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;

(3)关闭自动提交可以提高导入效率

  在导入数据前先执行set autocommit=0,关闭自动提交事务,在导入结束后执行set autocommit=1,恢复自动提交,可以提高导入效率。

当autocommit=1时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
当autocommit=0时
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK,1587168 rows affected (20.87 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

1.查询帮助 ? contents
2.使用合成的散列值,分离BLOB或者TEXT
3.货币使用定点数(decimal或者numberic)
4.sql_mode?
5.order by rand() limit 1000;
6.优化show status like 'Com' Com_select Com_insert等查看插入多还是查询多
7.Handler_read_key 的值将很高,这个值代表了一个行被索引值读的次数,很低的值表明增加索引得到的性能改善不高,因为索引并不经常使用.Handler_read_rnd_next 的值高则意味着查询运行低效,并且应该建立索引补救。这个值的含义是在数据文件中读下一行的请求数。如果你正进行大量的表扫描,该值较高。通常说明表索引不正确或写入的查询没有利用索引.
8.定期分析表   ANALYZE TABLE   CHECK TABLE    CHECKSUM TABLE
9.优化表 OPTIMIZE TABLE
10.导入大数据:Myisam  ALTER TABLE tblname DISABLE KEYS    loading the data    ALTER TABLE tblname ENABLE KEYS;
Innodb  SET UNIQUE_CHECKS=0     SET AUTOCOMMIT=0
11.优化insert:LOAD DATA INFILE    replace   ignore
12.优化group by     ORDER BY NULL
13.show status like 'Table%';               show status like 'innodb_row_lock%';
14.CREATE TABLE innodb_monitor(a INT) ENGINE=INNODB;
15.影响Mysql 性能的重要参数:
key_buffer_size :  说明:键缓存(变量key_buffer_size) 被所有线程共享;服务器使用的其它缓存则根据需要分配。此参数只适用于myisam 存储引擎。
table_cache:数据库中打开表的缓存数量。table_cache 与max_connections 有关。例如,对于200 个并行运行的连接,应该让表的缓至少有200 * N,这里N 是可以执行的查询的一个联接中表的最大数量。还需要为临时表和文件保留一些额外的文件描述符。
innodb_buffer_pool_size:缓存InnoDB 数据和索引的内存缓冲区的大小。你把这个值设得越高,访问表中数据需要得磁盘I/O 越少。

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

myisam读锁定

1.lock table t1 read

2.开启另一个mysql连接终端,接着去尝试:

select * from t1

3.再insert、update和delete t1这张表,你会发现所有的数据都停留在终端上没有真正的去操作

4.读锁定对我们在做备份大量数据时非常有用.

mysqldump -uroot -p123 test >test.sql

6).能使用STORE PROCEDURE 或者 USER FUNCTION的时候.(ROUTINE总是减少了服务器端的开销)
7).在一条insert语句中采用多重纪录插入格式.而且使用load data infile来导入大量数据,这比单纯的indert快好多.(在MYSQL中具体表现为:INSERT INTO TABLEQ VALUES (),(),...();)
(还有就是在MYISAM表中插入大量记录的时候先禁用到KEYS后面再建立KEYS,具体表现语句:
 ALTER TABLE TABLE1 DISABLE KEYS;ALTER TABLE TABLE1 ENABLE KEYS;
而对于INNNODB 表在插入前先 set autocommit=0;完了后:set autocommit=1;这样效率比较高。)
8).经常OPTIMIZE TABLE 来整理碎片.
9).还有就是date 类型的数据如果频繁要做比较的话尽量保存在unsigned int 类型比较快。

 -- 优化表
OPTIMIZE TABLE city;

慢查询日志

1.有关慢查询

开户和设置慢查询时间:

vi /etc/my.cnf

log_slow_queries=slow.log

long_query_time=5

复制代码 代码如下:

-- 分析表
ANALYZE TABLE city;

4 常用SQL的优化

1 大批量插入数据

    当用load命令导入数据的时候,适当设置可以提高导入的速度。

  对于MyISAM存储引擎的表,可以通过以下方式快速的导入大量的数据。

ALTER TABLE tbl_name DISABLE KEYS
loading the data
ALTER TABLE tbl_name ENABLE KEYS

mysql 开发进阶篇系列 4 SQL 优化(各种优化方法点)彩民之家高手论坛。DISABLE KEYS 和ENABLE KEYS 用来打开或关闭MyISAM表非唯一索引的更新,可以提高速度,注意:对InnoDB表无效。

 

没有使用打开或关闭MyISAM表非唯一索引:
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2 fields terminated by “,”;
Query OK,529056 rows affected (1 min 55.12 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0

使用打开或关闭MyISAM表非唯一索引:
mysql> alter table film_test2 disable keys;
Query OK,0 rows affected (0.0 sec)
mysql> load data infile ‘/home/mysql/film_test.txt’into table film_test2;
Query OK,529056 rows affected (6.34 sec)
Records:529056 Deleted:0 Skipped:0 Warnings:0
mysql> alter table film_test2 enable keys;
Query OK,0 rows affected (12.25 sec)
以上对MyISAM表的数据导入,但对于InnoDB表并不能提高导入数据的效率

 

彩民之家高手论坛 4

(1)针对于InnoDB类型表数据导入的优化

因为InnoDB表的按照主键顺序保存的,所以将导入的数据主键的顺序排列,可以有效地提高导入数据的效率。

使用test3.txt文本是按表film_test4主键存储顺序保存的
mysql> load data infile ‘/home/mysql/film_test3.txt’into table film_test4;
Query OK, 1587168 rows affected (22.92 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0
使用test3.txt没有任何顺序的文本(效率慢了1.12倍)
mysql> load data infile ‘/home/mysql/film_test4.txt’into table film_test4;
Query OK, 1587168 rows affected (31.16 sec)
Records:1587168 Deleted:0 Skipped:0 Warnings:0

8. 优化order by 语句

4.2 大存储量解决

1.分库分表

2.分区

 

主要目的:

1.减少表的记录数

2.减小对操作系统的负担压力

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

2 优化insert语句

尽量使用多个值表的insert语句,这样可以大大缩短客户与数据库的连接、关闭等损耗。

可以使用insert delayed(马上执行)语句得到更高的效率。

将索引文件和数据文件分别存放不同的磁盘上。

可以增加bulk_insert_buffer_size 变量值的方法来提高速度,但是只对MyISAM表使用

当从一个文件中装载一个表时,使用LOAD DATA INFILE。这个通常比使用很多insert语句要快20倍。

4.    优化optimize

3 查看索引使用情况

  如果索引正在工作,Handler_read_key的值将很高,这个值代表了一个行被索引值读的次数。

  Handler_read_rnd_next的值高则意味着查询运行低效,并且应该建立索引补救。

   mysql> show status like 'Handler_read%';
  ----------------------- -------
  | Variable_name         | Value |
  ----------------------- -------
  | Handler_read_first    | 0     |
  | Handler_read_key      | 5     |
  | Handler_read_next     | 0     |
  | Handler_read_prev     | 0     |
  | Handler_read_rnd      | 0     |
  | Handler_read_rnd_next | 2055  |
  ----------------------- -------
   6 rows in set (0.00 sec)

 

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

myisam写锁定

1.lock table t1 write

2.打开另一个mysql终端,尝试去select、insert、update和delete这张表t1,你会发现都不能操作,都会停留在终端上,只有等第一个终端操作完毕,第二个终端才能真正执行.

3.可见表的写锁定比读锁定更严格

4.一般情况下我们很少去显式的去对表进行read和write锁定的,myisam会自动进行锁定的.

彩民之家高手论坛 5

彩民之家高手论坛 6

 

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

7. 优化group by语句

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

(1) 不同客户插入很多行数据时,更改INSERT INTO为 INSERT DELAYED INTO,这使语句得到更高的速度。
(2) 将索引文件和磁盘文件分在不同磁盘上存放(利用表的选项)。
(3) 如果是批量插入 对myisam表可使用bulk_insert_buffer_size 来提高速度。
mysql 开发进阶篇系列 4 SQL 优化(各种优化方法点)彩民之家高手论坛。(4) 使用load data infile 通常比insert语句快20倍。

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

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

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

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

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

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

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

-- 检查表
CHECK TABLE city;

3. 优化检查表

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

5.  优化大批量插入数据

6. 优化insert 语句

  5.2 针对innodb表

TAG标签: PHP
版权声明:本文由彩民之家高手论坛发布于彩民之家高手论坛,转载请注明出处:mysql 开发进阶篇系列 4 SQL 优化(各种优化方法点