索引的作用
起到优化查询的作用
索引的分类(算法)
B树 默认使用的索引类型
R树
Hash
FullText
GIS索引
BTree索引算法演变
BTree索引功能上的分类
辅助索引
提取索引列的所有值，进行排序
将排好序的值，均匀的存放在叶子节点，进一步生成枝节点和根节点
在叶子节点中的值，都会对应存储主键ID
聚集索引
MySQL会自动选择主键作为聚集索引列，没有主键会选择唯一键，如果都没有会生成隐藏的
MySQL进行存储数据时，会按照聚集索引列值的顺序，有序存储数据行
聚集索引直接将原表数据页，作为叶子节点，然后提取聚集索引列向上生成枝和根
聚集索引和辅助索引的区别
表中任何一个列都可以创建辅助索引，在你有需要的时候，只要名字不同即可
在一张表中，聚集索引只能有一个，一般是主键
辅助索引叶子节点只存储索引列的有序值+聚集索引列值
聚集索引，叶子节点存储的是有序的整行数据
MySQL的表数据存储是聚集索引组织表
辅助索引细分
单列辅助索引
联合索引(覆盖索引)
唯一索引
索引树高度
索引树高度应当越低越好，一般维持在3-4最佳
数据行数较多
分表:parttion
分片，分布式架构
字段长度
业务允许尽量选择字符长度短的列作为索引列
业务不允许采用前缀索引
数据类型
CHAR和VARCHAR
ENUM
索引的命令操作
查询索引
DESC city;
PRI 主键索引
MUL 辅助索引
UNI 唯一索引
SHOW index FROM city\G
创建索引
单列的辅助索引
ALTER TABLE city ADD index idx_name(name);
多列的联合索引
ALTER TABLE city ADD index_c_p(countrycode,population);
唯一索引
ALTER TABLE city ADD UNIQUE index uidx_dis(district);
SELECT COUNT(district) FROM city;
SELECT COUNT(DISTINCT district) FROM city;
前缀索引
ALTER TABLE city ADD index idx_dis(district(5));
删除索引
SHOW index FROM city;
ALTER TABLE city DROP index idx_name;
ALTER TABLE city DROP index idx_c_p;
ALTER TABLE city DROP index idx_dis;
压力测试准备
use test
source /tmp/t100w.sql
未做优化之前测试
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schame='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
执行计划分析
作用：将优化器选择后的执行计划截取出来，便于管理判断语句的执行效率
获取执行
desc SQL语句
explain SQL语句
desc SELECT * FROM test.t100w WHERE k2='MN89';
分析执行计划
table 表名
type
查询的类型
全表扫描 ALL
索引扫描 index，range，ref，eq_ref，const(system)，NULL
index 全索引扫描
desc SELECT id FROM city;
range 索引范围扫描
desc SELECT * FROM city WHERE id>2000;
desc SELECT * FROM city WHERE countrycode LIKE '%CH';
对于辅助索引来讲，!=和mot in等语句是不走索引的，对于主键索引来讲，!=和not in等语句是走range
desc SELECT * FROM city WHERE countrycode='CHN' OR countrycode='USA';
desc SELECT * FROM city WHERE countrycode IN {'CHN','USA'};
一般改写为
desc SELECT * FROM city WHERE countrycode='CHN' UNION ALL SELECT * FROM city WHERE countrycode='USA';
ref 辅助索引等值查询
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
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) 主键或者唯一键的等值查询
desc SELECT * FROM city WHERE id=100;
use test;
ALTER TABLE t100w ADD index idx_k2(k2);
索引优化后
mysqlslap --defaults-file=/etc/my.cnf --concurrency=100 --iterations=1 --create-schame='test' --query="select * from test.t100w where k2='MN89'" engine=innodb --number-of-queries=2000 -uroot -p123 -verbose
possible_key 可能会用到的索引
key 真正选择了哪个索引
key_len 索引覆盖长度
varchar(20)
能存20个任意字符
不管存储的是字符，数字，中文，都一个字符最大预留长度是4个字节
对于中文，1个占4个字节
对于数字，1个实际占用大小是1个字节
联合索引 ADD index idx(a,b,c,d)
唯一值多的列放在最左侧
只要我们将来的查询，所有索引列都是<等值>查询条件下，无关排列顺序，优化器会自动做查询条件的排列
desc SELECT * FROM test WHERE k1='aa' AND k2='中国' AND k3='aaaa' AND k4='中国你好';
desc SELECT * FROM test WHERE k2='中国' AND k3='aaaa' AND k4='中国你好' AND k1='aa' ;
不连续部分条件
desc SELECT * FROM test WHERE k2='中国' AND k4='中国你好' AND k1='aa' ;
在where查询中如果出现><like
esc SELECT * FROM test WHERE k1='aa' AND k2>'中国' AND k3='aaaa' AND k4='中国你好';
esc SELECT * FROM test WHERE k1='aa' AND k3='aaaa' AND k4='中国你好' AND k2>'中国';
ALTER TABLE test ADD index idx1(k1,k3,k4,k2);
多子句查询，应用联合索引
desc SELECT * FROM test WHERE k1='aa' ORDER BY k2;
ALTER TABLE test ADD index idx3(k1,k2);
Extra Using filesort
出现Using filesort，说明在查询中有关排序的条件列没有合理应用索引
ORDER BY
GROUP BY
DISTINCT
UNION
explain(desc)使用场景
公司业务慢，从数据库的角度分析原因
应急性的慢，资源耗尽
处理过程
show processlist; 获取到导致数据库慢的语句
explain 分析SQL的执行计划，有没有走索引，索引的类型情况
建索引，改语句
一段时间慢(持续性的)
记录慢日志showlog，分析showlog
explain分析SQL的执行计划，有没有走索引，索引的类型情况
建索引，改语句
索引应用规范
建立索引的原则(DBA运维规范)
必须要有主键，一般是无关列，自增长
经常做为where条件列，order by group by join on distinct的条件
最好使用唯一值多的列作为联合索引前导列
列值长度较长的索引列，我们建议使用前缀索引
降低索引条目，一方面不要创建没有索引，不经常使用的索引清理，percona toolkit ()
索引维护要避开业务繁忙期
小表不建索引
不走索引的情况(开发规范)
没有查询条件，或者查询条件没有建立索引
查询结果集是原表中的大部分数据，应该是25%以上
索引本身失效，统计数据不真实
查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/)
隐式转换导致索引失效
<>，not in不走索引(辅助索引)
like "%aa"百分号在最前面不走索引
联合索引
网友评论