MySQL运维—索引

一、前期准备

1、写脚本刷数据

[root@computer opt]# vi slap.sh
#!/bin/bash
HOSTNAME="localhost"
PORT="3306"
USERNAME="root"
PASSWORD=""
DBNAME="oldboy"
TABLENAME="t1"
##create database
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "drop database if exists ${DBNAME}"
create_db_sql="create database if not exists ${DBNAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} -e "${create_db_sql}"
#create table
create_table_sql="create table if not exists ${TABLENAME}(stuid int not null primary key,stuname varchar(20) 
not null,stusex char(1) not null,cardid varchar(20) not null,birthday datetime,entertime datetime,address varchar(100) default null)"
mysql -h  ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e"${create_table_sql}"
#insert data to table
i="1"
while [ $i -le 500000 ]
do
insert_sql="insert into ${TABLENAME}
values($i,'alexsb_$i','1','110011198809163418','1990-05-16','2017-09-13','oldboyedu')"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${insert_sql}"
let i++
done
#select data
select_sql="select count(*) from ${TABLENAME}"
mysql -h ${HOSTNAME} -P${PORT} -u${USERNAME} -p${PASSWORD} ${DBNAME} -e "${select_sql}"

2、执行脚本,验证数据

[root@computer opt]# sh slap.sh

图片[1]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

二、索引介绍

1、索引分类

Btree、HASH、Rtree、Fulltext

2、Btree结构分类

B -tree

图片[2]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

B+tree

图片[3]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

B*tree

图片[4]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

3、Mysql Btree种类细分

普通二级索引(辅助索引):人为操作最多

(1)创建索引时,选择表中的某个列作为索引键(key)
(2)会将整个列的值提取出来,做排序
(3)将排序后的值,均匀的分布到BTree索引的“叶子节点”中,进而生成“枝节点”,最终生成根节点
注意:将来我们应当尽量控制索引树的高度,来减少索引树遍历的次数
(4)叶子节点同时会存储,原表的数据行的指针,进而可以找到行中的其他列的数据,我们把这个动作叫做回表查询(随机IO)

聚集索引(cluster index): 主键索引,建表时创建

(1)覆盖索引(联合索引)
分析业务,将大部分的数据查询的列,联合起来建立Btree,可以头量减少回表查询,从而减少随机IO

唯一索引(uniqueindex):人为操作
(1)唯一素引(人为操作的)
列的值必须是不重复的

(2)聚集索引(主键索引,建表时创建)
生成条件:
1)会选择主键列作为聚集素引列(一般主键实在建表时加入)
2)没有主键,会选择唯一键
结构:
1)按照聚集索引列的值的顺序,按顺序存储数据页,作为叶子节点
2)枝节点和根节点依然只存储下层的最小值及指针

4、索引的高度

(1)素引的高度
索引其实也是表,也占磁盘空间
1)数据行数越多,高度越高
解决方案:
(1)表分区,一般以800w行*(比较早期的解决方案)
(2)分布式架构(MyCat,TDDL,DBLE,DRDS)
2)索引列值很长的时候,高度越高
(1)索引列值本来很长
解决方案:前缀索引
(2)变长长度列,fEEChar()
解决方案:使用varchar()

三、索引的基本管理

1、压力测试,用mysql自带的压力测试工具,试一下没有索引的情况下,差不多跑了四个小时还没跑完

 mysqlslap --defaults-file=/etc/my.cnf \
> --concurrency=100 --iterations=1 --create-schema='oldboy' \
> --query="select * from oldboy.t1 where stuname='alexsb_100'" engine=innodb \
> --number-of-queries=200000 -uroot -p123456 -verbose

2、辅助索引管理

1)单列普通辅助索引

图片[5]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

查看索引

图片[6]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

命令:

alter table city add index idx_name(name);                    创建索引
create index idx_name on city(name);                          创建索引
alter table city drop index idx_name;                         删除索引
desc city;                                                    查询索引
show index from city;                                         查询索引

2)覆盖索引(联合索引)

创建索引

图片[7]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

查看索引

图片[8]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

删除索引

图片[9]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

3)前缀索引

创建索引

图片[10]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

查看索引

图片[11]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

4)唯一索引

创建索引,建之前,确认一下,这一列没有重复值

图片[12]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

 四、执行计划

1、查看执行的语句

图片[13]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

关注信息:

table:city                                                                                  —->查询操作的表

possible——keys:CountryCode                                           —-> 可能会走的索引

key:CountryCode                                                                —-> 真正会走的索引

type:ref                                                                                  —->索引类型

Extra:Using index condition                                             —->额外信息

2、type类型

     type详解
     ALL:全表扫描
     desc  select * from city;
     注意:生产中几乎是没有这种需求的。尽量避免。

     INDEX:全索引扫描
     desc  select  id  from world.cxty;
     需要获取整个索引列的值。
     注意:生产中几乎也没有这种需求

     RANGE:索引范围扫描
     select范围查询:
    (1)> < >= <=
    (2)in or
    (3)between and
     desc  select * from City where id<10;
   
     REF:辅助索引的等值查询
     desc setect * from C1ty where country code='CHN
     unxon
     select * from C1ty where country code='USA;
     eq—ref:多表连接查询中,连接条件是主键或者唯一键的时候
 
     system,const
     主键或键,等值查询
     desc setect * from city where id=l;

生产优化的目标:保证ERANGE以上。

3、其他字段的解

    其他字段解释:
    Using fitesort
    看到这个段,就要引起注意了
    desc setect * from city where countrycode='CHN' order by population desc limit 10;

优化:

   解决思路:
   索引可以减少排序,可以很大程度减少CPU时间
   辅助索引应用顺序(优化器选择的)
   如果查询条件:符合覆盖索引的顺序时,优先选择覆盖索引
   不符合顺序,优先会走where条件的索引
   优化方法,将where列和order列建立联合索引
   alter table city add index idx_co_po(countrycode,population);
   图片[14]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹
   图片[15]众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站MySQL运维—索引众客华禹 – 网站运维分享-IT技术资源教程-运维成长之路-个人随笔-华禹个人博客网站众客华禹

 

© 版权声明
THE END
喜欢就支持一下吧
点赞0 分享
评论 抢沙发
头像
欢迎您留下宝贵的见解!
提交
头像

昵称

取消
昵称表情代码图片