索引的作用
拿汉语字典的目录页(索引)打比方,我们可以按拼音、笔画、偏旁部首等排序的目录(索引)快速查找到需要的字。
索引分单列索引和组合索引。单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引。组合索引,即一个索引包含多个列。
创建索引时,你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录。
上面都在说使用索引的好处,但过多的使用索引将会造成滥用。因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件。
索引的分类(算法)
B树 默认使用的索引类型
R树
Hash
FullTest(全文索引)
GIS
Btree索引算法演变
B-tree
B+tree
B*tree
Btree索引的分类
辅助索引
1.管理员选择一个列创建辅助索引
2.mysql会自动将此列的值取出来
3.将此列的值进行自动排序
4.将排好序的数据,均匀的存储到索引的叶子节点
5.生成枝节点和根节点
提取索引列的所有值,进行排序
将排好序的值,均匀的存放在叶子节点,进一步生成枝节点和根节点
在叶子节点中的值,都会对应存储主键ID
单列辅助索引:
联合索引(覆盖索引):
唯一索引:
索引树高度:越低越好,应该维持在3~4层
数据行数较多的时候:1.分表(parttion用的较少,逻辑切成多个小表)2. 分片(分布式架构)
字段长度:1.尽量选择字符长度短得列作为索引列 2.采用前缀索引
数据类型:char和varchar enum
聚集索引
聚集索引一般是主键列
1.mysql会自动选择主键作为聚集索引列,没有主键会选择唯一键,如果都没有会生成隐藏的
2.mysql进行存储数据时,会按照聚集索引列值的顺序,有序存储数据行
3.聚集索引直接将原表数据页,作为叶子节点,然后提取聚集索引列向上生成枝和根
聚集索引和辅助索引区别
1.表中任何一个列都可以创建辅助索引,在你有需要的时候,只要名字不同即可
2.在一张表中,聚集索引只能有一个,一般是主键
3.辅助索引,叶子节点只存储索引列的有序值+聚集索引列值
4.聚集索引,叶子节点存储的是有序的整行数据
5.mysql的表数据存储是聚集索引组织表
索引命令操作
查询索引
1.desc table_name; (PRI 主键索引 MUL 辅助索引 UNI 唯一索引)
2.show index from table_name;
创建索引
单列辅助索引 :alter table city add index idx_name(name);
多列的联合辅助索引:alter table city add index idx_c_p(countrycode,population);
唯一索引:alter table city add unique index uidx_dis(disstrict);
前缀索引:alter table city add index idx_dis(district(5));
删除索引
alter table city drop index idx_name;
压力测试
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 -p123 -verbose
concurrency:100用户连接数据库
number-of-queries:2000次查询
执行计划分析
作用:将优化器选择后的执行计划,截取出来。便于管理判断语句得执行效率
获取执行计划:desc SQL语句或者explain SQL语句
分析执行计划:
table:表名
type:全表扫描(ALL) 索引扫描(1.index 2.range 3.ref 4.eq_ref 5.const(system) 6.NULL)
索引类型从左到右依次变好(index是全索引扫描)(range是索引范围扫描)(ref是辅助索引等值查询 sql使用union all)(eq_ref多表连接时,子表使用主键列或唯一索引列作为连接条件 join)
(const(system) 是主键或唯一键等值查询)
对于辅助索引来讲 != ,not in等语句是不走索引的
对于主键缩影来讲 !=,not in等语句是走range索引的
key:
key_len
extra
索引应用规范
1.建表必须要有主键,一般是无关列,自增长
2.经常作为where条件列 order by group by join on distinct的条件
3.最好使用唯一值多的列作为联合索引前导列,其他的按照联合索引优化细节来做
4.列值长度较长的索引列,我们建议使用前缀索引
5.降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
6.索引维护要避开业务繁忙期
7.小表不要建索引
不走索引的情况
1.没有查询条件,或者查询条件没有建立索引
2.查询结果集是原表的大部分数据,应该是25%以上
3.索引本身失效,统计数据不真实
4.查询条件使用函数在索引列上,或者对索引列进行运算,运算包括(+,-,x,等)
5.隐式转换导致索引失效
6.not in 不走索引
7.like 百分号在最前面不走索引
8.联合索引
存储引擎查看
show engines;
alter table t111 engine=innodb;
innodb物理存储结构
ibdata1:系统数据字典信息(统计信息),undo表空间等数据
ib_logfile0-ib_logfile1:REDO日志文件,事务日志文件
ibtmp1:临时表空间磁盘位置,存储临时表
frm:存储表的列信息
idb:表的数据行和索引
表空间(Tablespace)
共享表空间
需要将所有数据存储到同一个表空间,管理比较混乱