时间:2021-07-01 10:21:17 帮助过:18人阅读
f(‘Arjen‘) = 2323
f(‘Baron‘) = 7437
f(‘Peter‘) = 8784
f(‘Vadim‘) = 2458
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 等索引;
#创建表同时添加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 多种评注
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);
#方式一:
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;
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 (唯一的,不允许重复)
何时使用聚集索引或非聚集索引
下面的表总结了何时使用聚集索引或非聚集索引
动作描述 | 使用聚集索引 | 使用非聚集索引 |
---|---|---|
列经常被分组排序 | 应 | 应 |
返回某范围内的数据 | 应 | 不应 |
一个或极少不同值 | 不应 | 不应 |
频繁更新的列 | 不应 | 应 |
外键列 | 应 | 应 |
主键列 | 应 | 应 |
频繁修改索引列 | 不应 | 应 |
-- 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;
-- 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;
call insert_user_info(5000000);
ALTER TABLE userinfo ENGINE=INNODB;
SELECT * FROM userinfo WHERE id = 4567890;
注意:无索引情况,mysql根本就不知道id等于4567890的记录在哪里,只能把数据表从头到尾扫描一遍,此时有多少个磁盘块就需要进行多少IO操作,所以查询速度很慢.
2 在表中已经存在大量数据的前提下,为某个字段段建立索引,建立速度会很慢
CREATE INDEX idx_id on userinfo(id);
select * from userinfo where id = 4567890;
#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;
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也没有发挥索引效果
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(走索引)
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;
select * from userinfo limit 3000000,10;
最多翻到72页就不让你翻了,这种方式就是从业务上解决;
2 在查询下一页时把上一页的行id作为参数传递给客户端程序,然后sql就改成了
select * from userinfo where id>3000000 limit 10;
select * from userinfo where id>100*10 limit 10;
3 最后第三种方法:延迟关联
分析一下这条语句为什么慢,慢在哪里。
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;
EXPLAIN SELECT * FROM person,dept WHERE person.dept_id = dept.did and person.salary >20000
EXPLAIN select * from person where dept_id =(select did from dept where dname =‘python‘);
EXPLAIN SELECT * FROM person;
EXPLAIN SELECT *FROM (SELECT* FROM person LIMIT 5) AS s
EXPLAIN SELECT person.*,(select 2 from person as p2) FROM person where dept_id = (select did from dept where dname=‘python‘);
EXPLAIN SELECT * FROM person union all select * from person ;
EXPLAIN SELECT * FROM person union select * from person ;
EXPLAIN SELECT * FROM person;
EXPLAIN SELECT max(id) FROM person;
EXPLAIN SELECT * FROM person where id =2;
EXPAIN select * from person,dept where person.id = dept.did;
EXPLAIN select * from person where name=‘alex‘;
EXPLAIN select * from person where id BETWEEN 1 and 5;
EXPLAIN select id,name from person;
EXPLAIN select * from person;
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 便是指这种常量。
建表及插入数据:
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 | 不符合
| ... | ... | ...
+-------+-------+
其他状态例如:Using index、Using index condition、Using index for group-by 则都还好,不用紧张。
转自:http://www.cnblogs.com/wangfengming/articles/8275448.html
MySQL - - 索引原理与慢查询优化
标签:创建组 目的 条件查询 record 高效 sql优化 也有 查找 ext