首页 / MYSQL / MySQL的索引及执行计划
MySQL的索引及执行计划
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了MySQL的索引及执行计划，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含6836字，纯文字阅读大概需要10分钟。
内容图文
MySQL的索引及执行计划
类似于一本书中的目录,起到优化查询作用
索引的分类
B树 默认使用的索引类型
R树
Hash
FullText
GIS 索引
Btree索引功能分类
聚集索引
如果有PK,MySQL默认使用PK作为聚簇索引
将来存储数据时,能够保证所有数据行按照主键列的顺序存储到磁盘页中.
自动生成聚集索引树
如果没有PK,会自动选择唯一键(UK)
什么都没有,会自动按照时间戳的hash值作为聚簇索引.
一张表中只能有一个聚簇索引.
建议是数字的自增列最佳.
构建过程:
(1) 在建表时,设定了主键.MySQL InnoDB 自动将主键作为聚簇索引列
(2) 在存储数据时,会按照聚簇索引组织存储数据
(3) InnoDB引擎会将数据行所在的数据页作为叶子节点
(5) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(6) 提取枝节点的最小值和指针,最终生成根节点
辅助索引
按照业务的查询特点,一般经常需要where group order by 条件列
创建辅助索引.
构建过程:
(1) 将索引键值进行自动排序(默认从小到大排序)
(2) 将排好序的键值+PK,生成叶子节点(16KB)
(3) 提取每个叶子节点的最小值,生成枝节点,保留叶子节点指针
(4) 提取枝节点的最小值和指针,最终生成根节点
查询过程:
一旦我们拿索引键值作为条件发起查询
(1) 按照Btree查找算法,找到辅助索引叶子节点,获取到PK值
(2) 按照PK的值回表,通过聚簇索引Btree进一步找到具体数据行
聚集索引和辅助索引的区别
表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
在一张表中,聚集索引只能有一个,一般是主键.
辅助索引,叶子节点只存储索引列的有序值+聚集索引列值.
聚集索引,叶子节点存储的时有序的整行数据.
MySQL的表数据存储是聚集索引组织表
辅助索引细分
#普通的单列辅助索引
#联合索引
多个列作为索引条件,生成索引树,理论上设计的好的,可以减少大量的回表查询
#唯一索引
索引列的值都是唯一的.
索引数高度
索引树高度应当越低越好,一般维持在3-4最佳
#数据行数较多
分表 : parttion 用的比较少了.
分片,分布式架构.
#字段长度
业务允许,尽量选择字符长度短的列作为索引列
业务不允许,采用前缀索引.
#数据类型
char 和 varchar
enum
索引的命令操作
#查询索引
mysql> desc city;
mysql> show index from city\G
PRI ==> 主键索引
MUL ==> 辅助索引
UNI ==> 唯一索引
#创建索引
#单列的辅助索引
mysql> alter table city add index idx_name(name);
#多列的联合索引
mysql> alter table city add index idx_c_p(countrycode,population);
#唯一索引
mysql> alter table city add unique index uidex_dir(district);
#前缀索引
mysql> alter table city add index idx_dir(district(5));
#删除索引
mysql> alter table city drop index idx_name;
压力测试
mysql> create database test;
mysql> use test
mysql> source /root/t100w.sql
#未做优化之前测试
[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 1323.035 seconds
Minimum number of seconds to run all queries: 1323.035 seconds
Maximum number of seconds to run all queries: 1323.035 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@mysql ~]#
#做优化之后测试
mysql> alter table t100w add index idx_k2(k2);
[root@mysql ~]# mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schema='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123456 -verbose
mysqlslap: [Warning] Using a password on the command line interface can be insecure.
Benchmark
Running for engine rbose
Average number of seconds to run all queries: 2.794 seconds
Minimum number of seconds to run all queries: 2.794 seconds
Maximum number of seconds to run all queries: 2.794 seconds
Number of clients running queries: 100
Average number of queries per client: 20
[root@mysql ~]#
执行计划分析
#作用
将优化器 选择后的执行计划 截取出来.便于管理管判断语句得执行效率.
#获取执行
desc SQL语句
explain SQL 语句
mysql> desc select * from test.t100w where k2='MN89';
mysql> explain select * from test.t100w where k2='MN89';
#分析执行计划
#table 表名
#type
查询的类型:
全表扫描 : ALL
索引扫描 : index,range,ref,eq_ref,const(system),NULL
index:全索引扫描
mysql> desc select id from city;
range:索引范围扫描(> < >= <= , between and ,or,in,like )
mysql> desc select * from city where id>2000;
对于辅助索引来讲,!= 和not in等语句是不走索引的
对于主键索引列来讲,!= 和not in等语句是走range
ref: 辅助索引等值查询
mysql> desc select * from city where countrycode='CHN' union all select * from city where countrycode='USA';
eq_ref : 多表连接时,子表使用主键列或唯一列作为连接条件
A join B on a.x = B.y
mysql> desc select b.name,a.name,a.population from city as a join country as b on a.countrycode=b.code where a.population<100;
const(system) : 主键或者唯一键的等值查询
mysql> desc select * from city where id=100;
#possible_key:可能会用到的索引
#key: 真正选择了哪个索引
#key_len: 索引覆盖长度
varchar(20) utf8mb4
1. 能存20个任意字符
2. 不管存储的时字符,数字,中文,都1个字符最大预留长度是4个字节
3. 对于中文,1个占4个字节
4. 对于数字和字母,1个实际占用大小是1个字节
select length() from test;
#Extra
出现Using filesort,说明在查询中有关排序的条件列没有合理的应用索引
order by
group by
distinct
union
关注key_len应用的长度
联合索引
#联合索引应用细节
只要我们将来的查询,所有索引列都是<等值>查询条件下,无关排列顺序
唯一值多的列放在最左侧
abcd
acbd
adbc
acbd
等等
mysql> alter table test add index idx(k1,k2,k3,k4);
mysql> desc select * from test where k1='aa' and k2='中国' and k3='aaaa' and k4='中国你好';
mysql> desc select * from test where k2='中国' and k3='aaaa' and k4='中国你好' and k1='aa';
原因: 优化器,自动做查询条件的排列
#不连续部分条件
cda ----> acd ---> a -----> idx(c,d,a)
dba ----> abd ---> ab ----> idx(d,b,a)
#在where查询中如果出现> < >= <= like
(1)
mysql> alter table test add index idx1(k1,k3,k4,k2);
(2)
mysql> desc select * from test where k1='aa' and k3='aaaa' and k4='中国你好' and k2>'中国';
#多子句 查询,应用联合索引
mysql> alter table test add index idx3(k1,k2);
mysql> desc select * from test where k1='aa' order by k2;
不走索引的情况
(1) 语句本身就是全表扫描
select * from city;
select * from city where 1=1;
(2) 查询条件没建索引
mysql> desc select * from city where name='dalian';
(3) 查询条件不满足索引应用逻辑
mysql> desc select * from city where countrycode like '%CH%';
如果业务中有大量的需求,可以使用ES
<>,not in()
(5) 查询条件中出现计算
mysql> desc select * from city where id-1=9;
(6) 出现隐式转换
mysql> desc select * from t1 where telnum=110;
mysql> desc select * from t1 where telnum='110';
索引应用规范
1.建索引原则
(1) 必须要有主键,如果没有可以做为主键条件的列,创建无关列
(2) 经常做为where条件列 order by group by join on, distinct 的条件(业务:产品功能+用户行为)
(3) 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
(4) 列值长度较长的索引列,我们建议使用前缀索引.
(5) 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit(xxxxx)
(6) 列值经常变化,没必要建索引,小表可以不用建索引.
(7) 索引维护要避开业务繁忙期(pt-toolkit)
内容总结
以上是互联网集市为您收集整理的MySQL的索引及执行计划全部内容，希望文章能够帮你解决MySQL的索引及执行计划所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】