文章首页 Web开发 Windows开发 编程语言 数据库技术 移动平台 系统服务 微信 设计 布布扣 其他

MySQL - - 索引原理与慢查询优化

时间：2018-11-12 16:41:49   收藏：0   阅读：162

目录

1， 索引介绍

2， 索引方法

2.1 B+TREE 索引

技术分享图片

技术分享图片

2.2 HASH 索引

技术分享图片

f(‘Arjen‘) = 2323
f(‘Baron‘) = 7437
f(‘Peter‘) = 8784
f(‘Vadim‘) = 2458

技术分享图片

2.3 HASH与BTREE比较:

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量越大,范围查询和随机查询快（innodb默认索引类型）

不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 Btree、Hash 等索引，不支持Full-text 索引；
MyISAM 不支持事务，支持表级别锁定，支持 Btree、Full-text 等索引，不支持 Hash 索引；
Memory 不支持事务，支持表级别锁定，支持 Btree、Hash 等索引，不支持 Full-text 索引；
NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 Btree、Full-text 等索引；
Archive 不支持事务，支持表级别锁定，不支持 Btree、Hash、Full-text 等索引；

3， 索引类型

3.1 普通索引

#创建表同时添加name字段为普通索引
create table tb1(
   id int not null auto_increment primary key,
   name varchar(100) not null,
   index idx_name(name)  
);
#单独为表指定普通索引

create index idx_name on tb1(name);
drop index idx_name on tb1;
show index from tb1;
1、Table 表的名称。

2、 Non_unique 如果索引为唯一索引,则为0,如果可以则为1。

3、 Key_name 索引的名称

4、 Seq_in_index 索引中的列序列号，从1开始。

5、 Column_name 列名称。

6、 Collation 列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）。

7、Cardinality 索引中唯一值的数目的估计值。

8、Sub_part 如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。

9、 Packed 指示关键字如何被压缩。如果没有被压缩，则为NULL。

10、 Null 如果列含有NULL，则含有YES。如果没有，则该列含有NO。

11、 Index_type 用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

12、 Comment 多种评注

3.2 唯一索引

create table tb2(
  id int not null auto_increment primary key,
  name varchar(50) not null,
  age int not null,
  unique index idx_age (age)   
)
create unique index idx_age on tb2(age);

3.3 主键索引

#方式一:
create table tb3(
   id int not null auto_increment primary key,
   name varchar(50) not null,
   age int default 0 
);

#方式二:
create table tb3(
   id int not null auto_increment,
   name varchar(50) not null,
   age int default 0 ,
   primary key(id)
);
alter table tb3 add primary key(id);
#方式一
alter table tb3 drop primary key;

#方式二:
#如果当前主键为自增主键,则不能直接删除.需要先修改自增属性,再删除

alter table tb3 modify id int ,drop primary key;

3.4 组合索引

create table tb4(
  id int not null ,
  name varchar(50) not null,
  age int not null,
  index idx_name_age (name,age)   
)
create index idx_name_age on tb4(name,age);
举个例子来说，比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表
有下列字段：
会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）

4， 聚合索引和辅助索引

技术分享图片

技术分享图片

技术分享图片

动作描述 使用聚集索引 使用非聚集索引
列经常被分组排序 应 应
返回某范围内的数据 应 不应
一个或极少不同值 不应 不应
频繁更新的列 不应 应
外键列 应 应
主键列 应 应
频繁修改索引列 不应 应

5， 测试索引

5.1 创建数据

-- 1.创建表
CREATE TABLE userInfo(
    id int NOT NULL,
    name VARCHAR(16) DEFAULT NULL,
    age int,
    sex char(1) not null,
    email varchar(64) default null
)ENGINE=MYISAM DEFAULT CHARSET=utf8;

5.2 创建存储过程，插入数据

-- 2.创建存储过程
delimiter$$
CREATE PROCEDURE insert_user_info(IN num INT)
BEGIN
    DECLARE val INT DEFAULT 0;
    DECLARE n INT DEFAULT 1;
    -- 循环进行数据插入
    WHILE n <= num DO
        set val = rand()*50;
        INSERT INTO userInfo(id,name,age,sex,email)values(n,concat(‘alex‘,val),rand()*50,if(val%2=0,‘女‘,‘男‘),concat(‘alex‘,n,‘@qq.com‘));
        set n=n+1;
    end while;
END $$
delimiter;

5.3 调用存储过程,插入500万条数据

call insert_user_info(5000000);

技术分享图片

5.4 此步骤可以忽略。修改引擎为INNODB

ALTER TABLE userinfo ENGINE=INNODB;

技术分享图片

5.5 测试索引

SELECT * FROM userinfo WHERE id = 4567890;

技术分享图片

CREATE INDEX idx_id on userinfo(id);

技术分享图片

select * from userinfo where id  = 4567890;

技术分享图片

技术分享图片

6， 正确使用索引

#1. 范围查询(>、>=、<、<=、!= 、between...and)
    #1. = 等号
    select count(*) from userinfo where id = 1000 -- 执行索引,索引效率高
    
    #2. > >= < <= between...and 区间查询
    select count(*) from userinfo where id <100; -- 执行索引,区间范围越小,索引效率越高
    
    select count(*) from userinfo where id >100; -- 执行索引,区间范围越大,索引效率越低
    
    select count(*) from userinfo where id between 10 and 500000; -- 执行索引,区间范围越大,索引效率越低
    
   #3. != 不等于
   select count(*) from userinfo where id != 1000;  -- 索引范围大,索引效率低
   
   
#2.like ‘%xx%‘
    #为 name 字段添加索引
    create index idx_name on userinfo(name);
    
    select count(*) from userinfo where name like ‘%xxxx%‘; -- 全模糊查询,索引效率低
    select count(*) from userinfo where name like ‘%xxxx‘;   -- 以什么结尾模糊查询,索引效率低
  
    #例外: 当like使用以什么开头会索引使用率高
    select * from userinfo where name like ‘xxxx%‘; 

#3. or 
    select count(*) from userinfo where id = 12334 or email =‘xxxx‘; -- email不是索引字段,索引此查询全表扫描
    
    #例外：当or条件中有未建立索引的列才失效，以下会走索引
    select count(*) from userinfo where id = 12334 or name = ‘alex3‘; -- id 和 name 都为索引字段时, or条件也会执行索引

#4.使用函数
    select count(*) from userinfo where reverse(name) = ‘5xela‘; -- name索引字段,使用函数时,索引失效
    
    #例外:索引字段对应的值可以使用函数,我们可以改为一下形式
    select count(*) from userinfo where name = reverse(‘5xela‘);

#5.类型不一致
    #如果列是字符串类型，传入条件是必须用引号引起来，不然...
    select count(*) from userinfo where name = 454;
        
    #类型一致
    select count(*) from userinfo where name = ‘454‘;

#6.order by
    #排序条件为索引，则select字段必须也是索引字段，否则无法命中  
    select email from userinfo ORDER BY name DESC; -- 无法命中索引

    select name from userinfo ORDER BY name DESC;  -- 命中索引
        
    #特别的:如果对主键排序，则还是速度很快：
    select id from userinfo order by id desc;

7， 组合索引

select * from mytable where a=3 and b=5 and c=4;
　  #abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where  c=4 and b=6 and a=3;
　　#这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
　　#a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;
　　#a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
　　#因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
　　#a用到了  b没有使用，c没有使用

select * from mytable where a=3 order by b;
　　#a用到了索引，b在结果排序中也用到了索引的效果

select * from mytable where a=3 order by c;
　　#a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了

select * from mytable where b=3 order by a;
　　#b没有用到索引，排序中a也没有发挥索引效果

8， 注意事项

9， 查询计划

explain  select count(*) from userinfo where  id = 1;

技术分享图片

执行计划：让mysql预估执行操作(一般正确)
　　type : 查询计划的连接类型, 有多个参数，先从最佳类型到最差类型介绍

　　性能： null > system/const > eq_ref > ref > ref_or_null > index_merge >  range > index >  all 

    慢：
        explain select * from userinfo where email=‘alex‘;
        type: ALL(全表扫描)
        特别的: select * from userinfo limit 1;
    快：
        explain select * from userinfo where name=‘alex‘;
        type: ref(走索引)

10， 慢日志查询

long_query_time     ：  设定慢查询的阀值，超出设定值的SQL即被记录到慢查询日志，缺省值为10s
slow_query_log      ：  指定是否开启慢查询日志
log_slow_queries    ：  指定是否开启慢查询日志(该参数已经被slow_query_log取代，做兼容性保留)
slow_query_log_file ：  指定慢日志文件存放位置，可以为空，系统会给一个缺省的文件host_name-slow.log
log_queries_not_using_indexes: 如果值设置为ON，则会记录所有没有利用索引的查询.
#.查询慢日志配置信息 :
show variables like ‘%query%‘;
#.修改配置信息
set global slow_query_log  = on;
# 显示参数　　
show variables like ‘%log_queries_not_using_indexes‘;
# 开启状态
set global log_queries_not_using_indexes  = on;
#查看慢日志记录的方式
show variables like ‘%log_output%‘;
 
#设置慢日志在文件和表中同时记录
set global log_output=‘FILE,TABLE‘;
#查询时间超过10秒就会记录到慢查询日志中
select sleep(3) FROM user ;
 
#查看表中的日志
select * from mysql.slow_log;

11， 大数据量分页优化

select * from userinfo limit 3000000,10;

技术分享图片

select * from userinfo where id>3000000 limit 10;
select * from userinfo where id>100*10 limit 10;
select * from userinfo limit 3000000,10;
select id from userinfo limit 3000000,10;
select table.* from userinfo inner join ( select id from userinfo limit 3000000,10 ) as tmp on tmp.id=userinfo.id;

12， EXPLAIN 详解

12.1 介绍

EXPLAIN SELECT  * FROM person,dept WHERE person.dept_id = dept.did and person.salary >20000

技术分享图片

12.2 id : 查询序列号

EXPLAIN select * from person where dept_id =(select did from dept where dname =‘python‘);

技术分享图片

12.3 select_type : 查询类型

12.3.1 simple 简单查询 (没有union和子查询)

EXPLAIN SELECT * FROM person;

技术分享图片

12.3.2 primary 最外层查询 (在存在子查询的语句中，最外面的select查询就是primary)

12.3.3 derived 子查询(在FROM列表中包含的子查询)

EXPLAIN SELECT *FROM (SELECT* FROM person LIMIT 5) AS s

技术分享图片

12.3.4 subquery 映射为子查询(在SELECT或WHERE列表中包含了子查询)

EXPLAIN SELECT person.*,(select 2 from person as p2) FROM person where dept_id = (select did from dept where dname=‘python‘);

技术分享图片

12.3.5 union 联合

EXPLAIN SELECT * FROM person union all select * from person ;

技术分享图片

12.3.6 union result 使用联合的结果

EXPLAIN SELECT * FROM person union  select * from person ;

技术分享图片

12.4 table 输出的行所用的表

EXPLAIN SELECT * FROM person;

技术分享图片

12.5 type 连接类型

12.5.1 type=NULL　在优化过程中就已得到结果，不用再访问表或索引。

EXPLAIN SELECT max(id) FROM person;

技术分享图片

12.5.2 type=const/system 常量

EXPLAIN SELECT * FROM person where id =2;

技术分享图片

12.5.3 type=eq_ref 使用有唯一性 索引查找（主键或唯一性索引）

EXPAIN select * from person,dept where person.id = dept.did;

技术分享图片

12.5.4 type=ref　非唯一性索引访问

EXPLAIN select * from person where name=‘alex‘;

技术分享图片

12.5.5 ref_or_null 该联接类型如同ref类似,结果包含空行.

12.5.6 type=range

EXPLAIN select * from person where id BETWEEN 1 and 5;

技术分享图片

12.5.7 type=index

EXPLAIN select id,name from person;

技术分享图片

12.5.8 type=ALL

EXPLAIN select * from person;

技术分享图片

12.6 possible_keys

12.7 key

12.8 key_len

12.9 ref

create table a11(id int primary key, age int);
insert into a11 value(1, 10),(2, 10);

mysql> desc select * from a11 where age=10;
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | a11   | ALL  | NULL          | NULL | NULL    | NULL |    2 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+------+-------------+
注意:当 key 列为 NULL ， ref 列也相应为 NULL 。

mysql> desc select * from a11 where id=1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | a11   | const | PRIMARY       | PRIMARY | 4       | const |    1 |       |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------+

注意:这次 key 列使用了主键索引，where id=1 中 1 为常量， ref 列的 const 便是指这种常量。

12.10 row

12.11 Extra

建表及插入数据：
create table a13 (id int primary key, age int);
insert into a13 value(1, 10),(2, 10);
mysql> explain select id from a13;
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| 1  | SIMPLE        | a13   | NULL         | index | NULL            | PRIMARY | 4   | NULL|  2   | Using index |
+----+-------------+-------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
因为 id 为主键索引，索引中直接包含了 id 的值，所以无需访问表，直接查找索引就能返回结果。

mysql> explain select age from a13;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1  | SIMPLE        | a13   | NULL         | ALL  | NULL            | NULL |  NULL  | NULL|  2   |  NULL |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------+
age 列没有索引，因此没有 Using index ，意即需要访问表。
为 age 列添加索引：
create table a14 (id int primary key, age int);
insert into a14 value(1, 10),(2, 10);
create index age on a14(id, age);
mysql> explain select age from a14;
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows |  Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
| 1  | SIMPLE      | a14   | NULL       | index|     NULL        | age |       9    | NULL| 2     |Using index |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-------------+
现在索引 age 中也包含了 age 列的值，因此不用访问表便能返回结果了。
mysql> EXPLAIN SELECT p.id,d.did from person p LEFT JOIN dept d ON p.dept_id = d.did group by p.dept_id ORDER BY p.dept_id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+---------------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                           |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+---------------------------------+
|  1 | SIMPLE      | p     | ALL    | NULL          | NULL    | NULL    | NULL       |    8 | Using temporary; Using filesort |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | test.p.dept_id| 1 | Using where; Using index        |

我们发现在执行这条SQL语句时出现了 using temporary,我们再来看看下面这条SQL语句,去掉 条件中 group by 分组

mysql> EXPLAIN SELECT p.id,d.did from person p LEFT JOIN dept d ON p.dept_id = d.did ORDER BY p.dept_id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | p     | ALL    | NULL          | NULL    | NULL    | NULL       |    8 | Using filesort           |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | test.p.dept_id|1  | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+

而为什么第一个用了临时表，而第二个没有用呢？
因为如果有GROUP BY子句，或者如果GROUP BY中的字段都来自其他的表而非连接顺序中的第一个表的话，就会创建一个临时表了。

那么如何解决呢?
咱们为group by 字段添加一个索引 

mysql> alter table person add index did_idx(dept_id);
Query OK, 0 rows affected

mysql> EXPLAIN SELECT p.id,d.did from person p LEFT JOIN dept d ON p.dept_id = d.did group by p.dept_id ORDER BY p.dept_id;
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref        | rows | Extra                    |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+
|  1 | SIMPLE      | p     | index  | NULL          | did_idx | 5       | NULL       |    8 | Using index              |
|  1 | SIMPLE      | d     | eq_ref | PRIMARY       | PRIMARY | 4       | test.p.dept_id| 1 | Using where; Using index |
+----+-------------+-------+--------+---------------+---------+---------+------------+------+--------------------------+

为什么添加个索引就不会创建临时表了呢? 原因就在于 SQL查询时优先在索引树中执行,如果索引树满足不了当前SQL,才会进行数据表查询,那么现在加了索引,
已经可以满足查询条件了,就没有必要创建临时表了
mysql> EXPLAIN select * from person ORDER BY id;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | person | index | NULL          | PRIMARY | 4       | NULL |    8 |       |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-------+
如果我们用聚合主键进行排序,则Extra 为null,我们知道在innodb引擎中,主键为聚合索引,插入数据就会排好顺序.最后说明mysql是按照表内的索引顺序进行读的

再看下面的列子:
mysql> EXPLAIN select * from person ORDER BY salary;
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | person | ALL  | NULL          | NULL | NULL    | NULL |    8 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+------+----------------+
我们使用非主键字段进行排序,这是mysql就不能按照表内的索引顺序进行读了.需要读取数据行后再进行排序处理
建表及插入数据：
create table a16 (num_a int not null, num_b int not null, key(num_a));
insert into a16 value(1,1),(1,2),(2,1),(2,2);
mysql> explain select * from a16 where num_a=1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+
| 1  | SIMPLE      | a16   | NULL       | ref  | num_a         | num_a | 4        | const| 2     |  NULL |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------+

虽然查询中有 where 子句，但只有 num_a=1 一个条件，且 num_a 列存在索引，通过索引便能确定返回的行，无需进行“后过滤”。
所以，并非带 WHERE 子句就会显示"Using where"的。
mysql> explain select * from a16 where num_a=1 and num_b=1;
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+
| 1  | SIMPLE      | a16   | NULL       | ref  | num_a            | num_a | 4     | const | 2  | Using where |
+----+-------------+-------+------------+------+---------------+-------+---------+-------+------+----------+-------------+

此查询增加了条件 num_b=1 ，此列没有索引，但可以看到查询同样能使用 num_a 索引。 MySQL 先通过索引 num_a 找到 num_a=1 的行，然后读取整行数据，
再检查 num_b 是否等于 1 ，执行过程看上去象这样：

num_a索引|num_b 没有索引，属于行数据
+-------+-------+
| num_a | num_b | where 子句(num_b=1)
+-------+-------+
| 1 | 1 | 符合
| 1 | 2 | 不符合
| ... | ... | ...
+-------+-------+

12.12 EXPLAIN结果中哪些信息要引起关注

原文：https://www.cnblogs.com/xiaoqshuo/p/9947020.html

评论（0）
© 2014 bubuko.com 版权所有 - 联系我们:wmxa8@hotmail.com
打开技术之扣，分享程序人生！