实际上即为一般MySQL的优化步骤

四、 关于索引的优化

3、解析为何SQL施行效能低————使用explain/desc命令剖判

 

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

1、查看SQL的施行成效—————使用show
status命令

  • 怎么着利用索引?
  • 动用索引应该注意的事项
  • 翻开索引使用状态

1、使用索引优化的比喻

  上个例子大家来看到实行explain
select*from user where
name=’Lisi’,扫描了3行(全体行数)使用了全表寻觅all。假设实在专业中name是平时用到查询的字段(是指平常跟在where后的字段,不是select后的字段)何况数据量不小的情况呢?那时候就须要索引了(索引平常用到where前边的字段比select后边的字段效果越来越好,只怕说就是要利用在where前边的字段上)

扩充name前缀索引(这里只是比喻,并未选用最合适的前缀):

mysql> create index index_name on user(name(2));
Query OK, 0 rows affected
Records: 0  Duplicates: 0  Warnings: 0

执行explain分析

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       | ref  | index_name    | index_name | 9       | const |    1 |      100 | Using where |
+----+-------------+-------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
1 row in set

  能够看来type变为ref、rows降为1(实际上只要使用了目录都是1),filtered过滤百分比为百分百,实际使用的目录为index_name。如若数据量十分大的话使用索引正是很好的优化措施,对于怎么着选拔索引,哪一天用索引,作者做出了之类总括:

  • 相关列轻巧表达:type、table、select_type…

4、因材施教选拔优化措施———–譬如选择index进行优化

 二、定位功效低的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结果发送给客户端等等,具体请看

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

  (1) 实际上即为一般MySQL的优化步骤。MySQL使用MEMO途观Y/HEAP引擎(使用的HASH索引),而且WHERE条件中不会采取”=”,in等开始展览索引列,那么不会用到目录(那是关于引擎部分特点,之后会介绍)。

  (2) 用OR分隔断的规格,假设OEvoque前面包车型大巴口径中的列有索引,而背后的列未有索引,那么涉及到的列索引不会被接纳。

  执行命令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)
不是用到复合索引中的第一列即最左侧的列的话,索引就不起成效(上面已经介绍)。

实际上即为一般MySQL的优化步骤。  (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

 

一、查看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语句

要害仿照效法资料:《深入浅出MySQL》,

在开首博客以前,依旧一直以来的给二个大致的目录结构,实则即为一般MySQL的优化步骤

2、定位怎样供给优化的SQL————通过慢查询记录+show
processlist命令查看当前线程

4、查看索引的选拔状态

执行show status like ‘Handler_read%’能够看到贰个值Handler_read_key,它表示一行被索引值读的次数,假如值异常低表达扩张索引得到的习性改进不高,因为索引并临时常选拔。

mysql> show status like 'Handler_read%' ;
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 3     |
| Handler_read_key      | 5     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 20    |
+-----------------------+-------+
7 rows in set

(1)Handler_read_实际上即为一般MySQL的优化步骤。first:索引中第一条被读的次数。即便较高,它表示服务器正实施大气全索引围观;

(2)Handler_read_实际上即为一般MySQL的优化步骤。key实际上即为一般MySQL的优化步骤。:要是索引正在干活,这几个值代表一个行被索引值读的次数,若是值越低,表示索引获得的品质改正不高,因为索引不平时使用。

(3)Handler_read_next
:遵照键顺序读下一行的伸手数。即使你用范围约束或只要实行索引围观来查询索引列,该值扩大。

(4)Handler_read_prev:依据键顺序读前一行的伸手数。该读方法主要用以优化O奇骏DER
BY … DESC。

(5)Handler_read_rnd
:依照固定地方读一行的诉求数。假使您正实践大气查询并必要对结果实行排序该值较高。你或者应用了大气亟需MySQL扫描整个表的查询或你的总是未有科学使用键。那一个值较高,意味着运行作效果能低,应该创立索引来补救。

(6)Handler_read_rnd_next:在数据文件中读下一行的央浼数。如若你正开始展览多量的表扫描,该值较高。平时表达您的表索引不正确或写入的查询未有行使索引。

   注:以上6点来自于网络总括,当中相比根本的三个参数是Handler_read_key与Handler_read_rnd_next。

三、 查看深入分析效用低的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:简单表,没有表连接大概子查询
  • P福睿斯IMAEnclaveY:主查询,即最外城的询问
  • UNION:UNION中的第三个或然后边的言语
  • SUBQUE凯雷德Y:子查询中的首个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%

 


You may also like...

发表评论

电子邮件地址不会被公开。 必填项已用*标注

网站地图xml地图