1:什么是索引?
排好序的数据结构
2:为什么mysql索引结构不是二叉树、红黑树、B-tree?
1:如果是二叉树:
如图:当字段为有顺序递增时,二叉树子节点大于父节点原则,二叉树会无限单边递增。这样高度不可控。查询效率不高。
2:红黑树
红黑数高度 2的n次方等于数据量,如果数据量为500万,那么高度大,查询次数多,性能不高,而且插入时,节点还自旋。
3:B-tree
1:节点数据索引从左到右依次增加。所有索引不重复。
因为每个大节点mysql默认存储空间为16kb,而每个索引下存了具体的数据。如果表的字段多。那么一个大节点存的数据也不是很多,那么必定会增加高度,那么查询次数好是会增多,查询速度还是不可控
4:B+ tree
1:非叶子节点不存数据,值存索引(冗余),可以放更多索引
2:叶子节点包含所有字段数据
3:叶子节点间有双向指针连接,提高区间访问性能
当高度为3时,假设data为1kb,指针6b,索引8b
数据量=16kb/8b+6b =1170
117011703 =2千万条
而且 根节点 是存在内存中的,所以根据索引查询一个数值要2次
3:mysql支持两种索引数结构hash和B+tree,为什么不用hash呢?
对于等值查询,hash只要查询1次就可以了,可是当范围查询、排序、模糊查询hash结构就不能做索引查询了
4:mysql两种存储引擎
4.1 myIsam存储引擎(非聚集索引)
索引文件和数据文件分开,叶子节点存储数据的地址,根据地址去数据文件查找数据
4.2 innodb索引实现
1:主键索引(聚集索引)
2:叶子节点存储表所有数据
3:表文件就是B+tree 组织的一个索引结构文件
5 :为什么innodb必须要有主键,并且推荐使用自增整型的主键?
1:因为innodb数据文件是B+tree结构组织的索引文件,而且该文件非叶子节点存储都是索引。
2:叶子节点索引大小从左到右依次递增,如果前面存了许多数据,现在插入一条索引较小的数据时,导致节点会分裂,需要调整平衡,主键自增会减少这种节点分裂,调整平衡的次数。
3:如果用uuid做主键索引,当查询走索引时,uuid要转化为整型再做比较,而且uuid消耗存储空间
6:为什么非主键索引叶子节点存储的事索引值?
为了保证数据一致性,如果非主键索引存数据的话,当修改一个值时,多个文件多要修改,不好保证数据一致性,也浪费存储空间。
7:联合索引存储结构
a,b,c三个字段联合索引
最左原则:三种走索引情况: a ab abc
8: explain查询sql是否走索引
注意:1:如果from中包含子查询,会执行子查询,并将结果放在内存中
2:join连接,explain执行会有两行记录
字段 | 含义 |
---|---|
id | id列的编号是 select 的序列号,有几个 select 就有几个id,并且id的顺序是按 select 出现的顺序增长的,id列越大执行优先级越高,id相同则从上往下执行,id为NULL最后执行。 |
select_type | 1:simple:简单查询 2:primary:复杂查询中最外层的 select 3:subquery:包含在 select 中的子查询(不在 from 子句中)4:derived:包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中,也称为派生表 |
table | 这一列表示 explain 的一行正在访问哪个表,当 from 子句中有子查询时,table列是 格式,表示当前查询依赖 id=N 的查询,于是先执行 id=N 的查询。 当有 union 时,UNION RESULT 的 table 列的值为<union1,2>,1和2表示参与 union 的select 行id |
type | 这一列表示关联类型或访问类型,即MySQL决定如何查找表中的行,查找数据行记录的大概范围。依次从最优到最差分别为:system > const > eq_ref > ref > range > index > ALL一般来说,得保证查询达到range级别,最好达到ref |
possible_keys | 这一列显示查询可能使用哪些索引来查找 |
key | 这一列显示mysql实际采用哪个索引来优化对该表的访问 |
key_len | 显示了mysql在索引里使用的字节数,通过这个值可以算出具体使用了索引中的哪些列。(1):字符串:char(n):n字节长度,varchar(n):2字节存储字符串长度,如果是utf-8,则长度 3n+ 2 ( 2):数值类型:tinyint:1字节,smallint:2字节,int:4字节,bigint:8字节(3:):时间类型: date:3字节timestamp:4字节datetime:8字节…索引最大长度是768字节,当字符串过长时,mysql会做一个类似左前缀索引的处理,将前半部分的字符提取出来做索引。 |
ref | 这一列显示了在key列记录的索引中,表查找值所用到的列或常量,常见的有:const(常量),字段名(例:film.id) |
rows | 这一列是mysql估计要读取并检测的行数, |
extra | 这一列展示的是额外信息。(1)Using index:使用覆盖索引 (2)Using where:使用 where 语句来处理结果,查询的列未被索引覆盖 (3)Using index condition:查询的列不完全被索引覆盖,(4)Using temporary:mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的,首先是想到用索引来优化。(5)Using filesort:将用外部排序而不是索引排序,数据较小时从内存排序,否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的。()6)Select tables optimized away:使用某些聚合函数(比如 max、min)来访问存在索引的某个字段 |
explain select (select 1 from actor where id =1 ) from (select * from film where id =1) der;
综上:1:当type为ALL时,走全表扫描,则需要优化
:2:当extra为using temporary,mysql创建临时表处理查询,需要用索引优化
:3:当extra为using filesore ,当数据小的时候是内存排序,数据大的时候是磁盘排序,需优化为索引排序
优化sql总结:
- 最左前缀法则
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列
- 尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句
- mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
- is null,is not null 也无法使用索引
- like以通配符开头(’$abc…’)mysql索引失效会变成全表扫描操作
- 字符串不加单引号索引失效
- 少用or或in,用它查询时,mysql不一定使用索引,mysql内部优化器会根据检索比例、表大小等多个因素整体评估是否使用索引