恒富网

优化SQL语句的一般步骤

  1 通过show status命令了解各种SQL的执行频率。

  格式：mysql> show [session|global]status;

   其中：session（默认）表示当前连接，

     global表示自数据库启动至今

mysql>show status;

mysql>show global status;

mysql>show status like ‘Com_%’;

mysql>show global status like ‘Com_%’;

参数说明：

Com_XXX表示每个XXX语句执行的次数如：

Com_select 执行select操作的次数，一次查询只累计加1

Com_update 执行update操作的次数

Com_insert 执行insert操作的次数，对批量插入只算一次。

Com_delete 执行delete操作的次数

只针对于InnoDB存储引擎的。

InnoDB_rows_read 执行select操作的次数

InnoDB_rows_updated 执行update操作的次数

InnoDB_rows_inserted 执行insert操作的次数

InnoDB_rows_deleted 执行delete操作的次数

其他：

connections 连接mysql的数量

Uptime 服务器已经工作的秒数

Slow_queries:慢查询的次数

   2 定位执行效率较低的SQL语句

1)explain select * from table  where id=1000;

2)desc select * from table where id=1000;

 

   3 通过EXPLAIN分析较低效SQL的执行计划

mysql> explain select count(*) from stu where name like "a%"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: stu
         type: range
possible_keys: name,ind_stu_name
          key: name
      key_len: 50
          ref: NULL
         rows: 8
        Extra: Using where; Using index
1 row in set (0.00 sec)

 

每一列的简单解释

id: 1

select_type: SIMPLE 表示select的类型，常见的取值有SIMPLE（）简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个SESECT）等

table: stu   输出结果集的表

type: range  表示表的连接类型，性能有好到差：system（表仅一行）、const（只一行匹配）、eq_ref（对于前面的每一行使用主键和唯一）、ref（同eq_ref，但没有使用主键和唯一）、ref_or_null（同前面对null查询）、index_merge（索引合并优化）、unique_subquery（主键子查询）、index_subquery（非主键子查询）、range（表单中的范围查询）、index（都通过查询索引来得到数据）、all（通过全表扫描得到的数据）

possible_keys: name,ind_stu_name  表查询时可能使用的索引。

key: name   表示实际使用的索引。

key_len: 50  索引字段的长度

ref: NULL 

rows: 8   扫描行的数量

Extra: Using where; Using index 执行情况的说明和描述

索引问题

索引是数据库优化中最常见也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。

索引的存储分类

  MyISAM存储引擎的表的数据和索引是自动分开存储的，各自是独一的一个文件；InnoDB存储引擎的表的数据和索引是存储在同一个表空间里面，但可以有多个文件组成。

  MySQL目前不支持函数索引，但是能对列的前面某一部分进行索引，例如name字段，可以只取name的前4个字符进行索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活设计。

  mysql>create index ind_company2_name on company2(name(4));
  其中company表名 ind_company2_name索引名

MySQL如何使用索引

索引用于快速找出在某个列中有一特定值的行。对相关列使用索引是提高SELECT操作性能的最佳途径。

1、使用索引

  (1)对于创建的多列索引，只要查询的条件中用到最左边的列，索引一般就会被使用。如下创建一个复合索引。

mysql>create index ind_sales2_com_mon onsales2(company_id,moneys);

然后按company_id进行查询，发现使用到了复合索引

mysql>explain select * from sales2 where company_id=2006\G

使用下面的查询就没有使用到复合索引。

mysql>explain select * from sales2 where moneys=1\G

 

 (2) 使用like的查询，后面如果是常量并且只有%号不在第一个字符，索引才可能会被使用，如下：

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)

 

如下这个使用到了索引，而下面例子能够使用索引，区别就在于“%”的位置不同，上面的例子是吧“%”放在了第一位，而下面的例子则没有

mysql> explain select * from company2 where name like “3%"\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: range
possible_keys: ind_company2_name
          key: ind_company2_name
      key_len: 11
          ref: NULL
         rows: 103
        Extra: Using where
1 row in set (0.00 sec)

 

(3)如果对大的文本进行搜索，使用全文索引而不使用 like“%...%”.

(4)如果列名是索引，使用column_name is null将使用索引。如下

mysql> explain select * from company2 where name is null\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: company2
         type: ref
possible_keys: ind_company2_name
          key: ind_company2_name
      key_len: 11
          ref: const
         rows: 1
        Extra: Using where
1 row in set (0.00 sec)

2、存在索引但不使用索引

 

(1)如果MySQL估计使用索引比全表扫描更慢，则不使用索引。例如如果列key_part1均匀分布在1到100之间，查询时使用索引就不是很好

mysql>select * from table_name where key_part1>1 and key_part<90;

  (2)如果使用MEMORY/HEAP表并且where条件中不使用“=”进行索引列，那么不会用到索引。Heap表只有在“=”的条件下会使用索引。

  (3)用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

mysql>show index from sales\G

 *************************** 1. row ***************************       
    … …
   key_name: ind_sales_year
   seq_in_index：1
   Column_name: year
   

(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=294\G
*************************** 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)

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)

 

两个简单实用的优化方法

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

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)

4 常用SQL的优化

1 大批量插入数据

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

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

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

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表并不能提高导入数据的效率

 

（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

（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

（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

2 优化insert语句

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

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

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

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

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

3 优化group by语句

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

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

mysql> explain select id,sum(moneys) from sales2 group by id\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 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 null\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 temporary
1 row in set (0.00 sec)

 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 ***************************
  … …

4.2 大存储量解决

1.分库分表

2.分区

 

主要目的:

1.减少表的记录数

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

myisam读锁定

1.lock table t1 read

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

select * from t1

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

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

mysqldump -uroot -p123 test >test.sql

myisam写锁定

1.lock table t1 write

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

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

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

慢查询日志

1.有关慢查询

开户和设置慢查询时间:

vi /etc/my.cnf

log_slow_queries=slow.log

long_query_time=5

顶一下

(0)

100%

订阅 回复

踩一下

(0)

100%