快捷搜索:
来自 网络数据库 2019-06-20 08:33 的文章
当前位置: 67677新澳门手机版 > 网络数据库 > 正文

SQL语句优化,常用的优化步骤

3、哪些情况下即使有索引也用不到?

  (1) MySQL使用MEMORY/HEAP引擎(使用的HASH索引),并且WHERE条件中不会使用”=”,in等进行索引列,那么不会用到索引(这是关于引擎部分特点,之后会介绍)。

  (2) 用OR分隔开的条件,如果OR前面的条件中的列有索引,而后面的列没有索引,那么涉及到的列索引不会被使用。

  执行命令show index from user可以看出password字段并没有使用任何索引,而id使用了两个索引,但是where id=1 or password='2d7284808e5111e8af74201a060059ce' 导致没有使用id列的primary索引与id_name_index索引

mysql> show index from user;
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| user  |          0 | PRIMARY       |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | index_name    |            1 | name        | A         |           3 |        2 | NULL   | YES  | BTREE      |         |               |
| user  |          1 | id_name_index |            1 | id          | A         |           3 | NULL     | NULL   |      | BTREE      |         |               |
| user  |          1 | id_name_index |            2 | name        | A         |           3 | NULL     | NULL   | YES  | BTREE      |         |               |
 ------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
4 rows in set

mysql> explain select*from user where id=1 or password='2d7284808e5111e8af74201a060059ce';
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys         | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | PRIMARY,id_name_index | NULL | NULL    | NULL |    3 |    55.56 | Using where |
 ---- ------------- ------- ------------ ------ ----------------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

  (3) 不是用到复合索引中的第一列即最左边的列的话,索引就不起作用(上面已经介绍)。

  (4) 如果like是以%开头的(上面已经介绍)

  (5) 如果列类型是字符串,那么where条件中字符常量值不用’’引号引起来的话,那就不会失去索引效果,这是因为MySQL会把输入的常量值进行转换再使用索引。

  select * from user_1 where name =250,其中name的索引为name_index,并且是varchar字符串类型,但是并没有将250用引号变成’250’,那么explain之后的ref仍然为NULL,rows为3

mysql> show index from user_1;
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| Table  | Non_unique | Key_name      | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
| user_1 |          1 | index_id_name |            1 | id          | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | index_id_name |            2 | name        | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| user_1 |          1 | name_index    |            1 | name        | A         |           3 |        5 | NULL   | YES  | BTREE      |         |               |
 -------- ------------ --------------- -------------- ------------- ----------- ------------- ---------- -------- ------ ------------ --------- --------------- 
3 rows in set

mysql> explain select*from user_1 where name=250;
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | user_1 | NULL       | index | name_index    | index_id_name | 38      | NULL |    3 |    33.33 | Using where; Using index |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
1 row in set

mysql> explain select*from user_1 where name='250';
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user_1 | NULL       | ref  | name_index    | name_index | 18      | const |    1 |      100 | Using where |
 ---- ------------- -------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
1 row in set

 

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

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

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

 

2、如何高效使用索引?

  (1) 创建多列索引时,**只要查询条件中用到最左边的列,索引一般都会被用到**

  我们创建一张没有索引的表user_1:

mysql> show create table 
user_1;
 -------- -------------------------------------------------------------------------------------------------------------------------- 
| Table  | Create Table                                                                                                             |
 -------- -------------------------------------------------------------------------------------------------------------------------- 
| user_1 | CREATE TABLE `user_1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
 -------- -------------------------------------------------------------------------------------------------------------------------- 
 1 row in set

 之后同样插入数据:

mysql> select *from user_1;
 ---- ---------- 
| id | name     |
 ---- ---------- 
|  1 | Zhangsan |
|  2 | Lisi     |
 ---- ---------- 
2 rows in set

 创建多列索引index_id_name

mysql> create index index_id_name on user_1(id,name);
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

 实验查询explain分析name与id

mysql> explain select * from user_1 where id=1;
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
| id | select_type | table  | partitions | type | possible_keys | key           | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user_1 | NULL       | ref  | index_id_name | index_id_name | 5       | const |    1 |      100 | Using index |
 ---- ------------- -------- ------------ ------ --------------- --------------- --------- ------- ------ ---------- ------------- 
1 row in set

mysql> explain select * from user_1 where name='Lisi';
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
| id | select_type | table  | partitions | type  | possible_keys | key           | key_len | ref  | rows | filtered | Extra                    |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
|  1 | SIMPLE      | user_1 | NULL       | index | NULL          | index_id_name | 38      | NULL |    2 |       50 | Using where; Using index |
 ---- ------------- -------- ------------ ------- --------------- --------------- --------- ------ ------ ---------- -------------------------- 
1 row in set

  可以看到使用最左列id的时候,rows为1,并且Extra明确使用了index,key的值为id_name_index,type的值为ref,而where不用到id,而是name的话,rows的值为2。filtered为50%,虽然key是index_id_name,但是表明是索引(个人理解,应该不太准确)

  (2) 使用like的查询,只有%不是第一个字符并且%后面是常量的情况下,索引才可能会被使用。

   执行explain select *from user where name like ‘%Li’后type为ALL且key的值为NULL,执行explain select *from user where name like ‘Li%’后key值不为空为index_name。

mysql> explain select*from user where name like '%Li';
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set
mysql> explain select*from user where name like 'Li%';
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type  | possible_keys | key        | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | range | index_name    | index_name | 9       | NULL |    1 |      100 | Using where |
 ---- ------------- ------- ------------ ------- --------------- ------------ --------- ------ ------ ---------- ------------- 
1 row in set

  (3) 如果对打的文本进行搜索,使用全文索引而不是用like ‘%...%’(只有MyISAM支持全文索引)。

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

mysql> explain select*from user where name is null;
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------------ --------- ------- ------ ---------- ------------- 
1 row in set

mysql> explain select*from user where password
 is null;
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

索引的存储分类

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

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

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

  • 如何使用索引?
  • 使用索引应该注意的事项
  • 查看索引使用情况

   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 执行情况的说明和描述

 

1、使用索引

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

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

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

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

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

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

 

 (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 nullG
*************************** 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、定位哪些需要优化的SQL------------通过慢查询记录 show processlist命令查看当前线程

索引问题

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

一、查看SQL执行频率

  使用show [session|gobal] status命令了解SQL执行频率、线程缓存内的线程的数量、当前打开的连接的数量、获得的表的锁的次数等。

比如执行show status like 'Com_%'查看每个语句执行的次数即频率,其中Com_xxx中xxx表示就是语句,比如Com_select:执行select操作的次数。

 1 mysql> use test;
 2 Database changed
 3 mysql> show status like 'Com_%';
 4  ----------------------------- ------- 
 5 | Variable_name               | Value |
 6  ----------------------------- ------- 
 7 | Com_admin_commands          | 0     |
 8 | Com_assign_to_keycache      | 0     |
 9 | Com_alter_db                | 0     |
10 | Com_alter_db_upgrade        | 0     |
11 | Com_alter_event             | 0     |
12 | Com_alter_function          | 0     |
13 | Com_alter_instance          | 0     |
14 | Com_alter_procedure         | 0     |
15 | Com_alter_server            | 0     |
16 | Com_alter_table             | 0     |
17 | Com_alter_tablespace        | 0     |
18 | Com_alter_user              | 0     |
19 | Com_analyze                 | 0     |
20 | Com_begin                   | 0     |
21 | Com_binlog                  | 0     |
22 | Com_call_procedure          | 0     |
23 | Com_change_db               | 2     |
24 | Com_change_master           | 0     |
25 | Com_change_repl_filter      | 0     |
26 | Com_check                   | 0     |
27 | Com_checksum                | 0     |
28 | Com_commit                  | 0     |
29 | Com_create_db               | 0     |
30 | Com_create_event            | 0     |
31 | Com_create_function         | 0     |
32 | Com_create_index            | 0     |
  ..............................

比如执行show status like 'slow_queries'查看慢查询次数(黑人问号??什么是慢查询呢?就是通过设置查询时间阈值long_query_time(0-10s)并打开开关show_query_log(1=OFF/0=ON),当超过这个阈值的查询都称之为慢查询,通常用来划分执行SQL效率)

mysql> show status like 'slow_queries';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Slow_queries  | 0     |
 --------------- ------- 
1 row in set

比如执行show status like 'uptime'查看服务工作时间(即运行时间):

mysql> show status like 'uptime';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Uptime        | 21645 |
 --------------- ------- 
1 row in set

比如执行show status like 'connections'查看MySQL连接数:

mysql> show status like 'connections';
 --------------- ------- 
| Variable_name | Value |
 --------------- ------- 
| Connections   | 6     |
 --------------- ------- 
1 row in set

  通过show [session|gobal] status命令很清楚地看到哪些SQL执行效率不如人意,但是具体是怎么个不如意法,还得继续往下看,使用EXPLAIN命令分析具体的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:慢查询的次数

四、 关于索引的优化

MySQL如何使用索引

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

 二、定位效率低的SQL

  上面也提到过慢查询这个概念主要是用来划分效率低的SQL,但是慢查询是在整个查询结束后才记录的,所以光是靠慢查询日志是跟踪不了效率低的SQL。一般有两种方式定位效率低的SQL:

  1、通过慢查询日志查看效率低的SQL语句,慢查询日志是通过show_query_log_file指定存储路径的,里面记录所有超过long_query_time的SQL语句(关于日志的查看,日后再一步研究学习),但是需要慢查询日志的产生是在查询结束后才有的。

  2、通过show processlist命令查看当前MySQL进行的线程,可以看到线程的状态信息

mysql> show processlist;
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
| Id | User | Host            | db   | Command | Time | State    | Info             |
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
|  2 | root | localhost:58377 | NULL | Sleep   | 2091 |          | NULL             |
|  3 | root | localhost:58382 | test | Sleep   | 2083 |          | NULL             |
|  4 | root | localhost:58386 | test | Sleep   | 2082 |          | NULL             |
|  5 | root | localhost:59092 | test | Query   |    0 | starting | show processlist |
 ---- ------ ----------------- ------ --------- ------ ---------- ------------------ 
4 rows in set

  其中主要的是state字段,表示当前SQL语句线程的状态,如Sleeping 表示正在等待客户端发送新请求,Sending data把查询到的data结果发送给客户端等等,具体请看

 

(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 salesG

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

三、 查看分析效率低的SQL

  MYSQL 5.6.3以前只能EXPLAIN SELECT; MYSQL5.6.3以后就可以EXPLAIN SELECT,UPDATE,DELETE,现在我们先创建一个user_table的表,之后分析select* from user where name=''语句

mysql> create table user(id int, name varchar(10),password varchar(32),primary key(id))engine=InnoDB;
Query OK, 0 rows affected

之后插入三条数据:

mysql> insert into user values(1,'Zhangsan',replace(UUID(),'-','')),(2,'Lisi',replace(UUID(),'-','')),(3,'Wangwu',replace(UUID(),'-',''));
Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0
mysql> select* from user;
 ---- ---------- ---------------------------------- 
| id | name     | password                         |
 ---- ---------- ---------------------------------- 
|  1 | Zhangsan | 2d7284808e5111e8af74201a060059ce |
|  2 | Lisi     | 2d73641c8e5111e8af74201a060059ce |
|  3 | Wangwu   | 2d73670c8e5111e8af74201a060059ce |
 ---- ---------- ---------------------------------- 
3 rows in set

下面以分析select*from user where name='Lisi'语句为例:

mysql> explain select*from user where name='Lisi';
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
|  1 | SIMPLE      | user  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
 ---- ------------- ------- ------------ ------ --------------- ------ --------- ------ ------ ---------- ------------- 
1 row in set

 

下面讲解select_type等常见列的含义的:

(1)select_type:表示SELECT的类型,主要有:

  • SIMPLE:简单表,没有表连接或者子查询
  • PRIMARY:主查询,即最外城的查询
  • UNION:UNION中的第二个或者后面的语句
  • SUBQUERY:子查询中的第一个SELECT

(2)table:结果输出的表

(3)type:表示表的连接类型,性能由好到差为:

  • system:常量表
  • const:单表中最多有一行匹配,比如primary key,unique index
  • eq_ref:多表连接中使用primary key,unique index
  • ref:使用普通索引
  • ref_or_null:与ref类似,但是包含了NULL查询
  • index_merge:索引合并优化
  • unique_subquery:in后面是一个查询主键字段的子查询
  • index_subquery:in后面是非唯一索引字段的子查询
  • range:单表中范围查看,使用like模糊查询
  • index:对于后面每一行都通过查询索引得到数据
  • all:表示全表查询

(3)possible_key:查询时可能使用的索引

(4)key:表示实际使用的索引

(5)key_len:索引字段的长度

(6)rows:查询时实际扫描的行数

(7)Extra:执行情况的说明和描述

(8)partitions:分区数目

(9)filtered:查询过滤的表占的百分比,比如这里查询的记录是name=Lisi的记录,占三条记录的33.3%

优化SQL语句的一般步骤

主要参考资料:《深入浅出MySQL》,

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

本文由67677新澳门手机版发布于网络数据库,转载请注明出处:SQL语句优化,常用的优化步骤

关键词: