mysql索引笔记

Mysql 索引笔记

create table

数据类型 int char varchar decimal date(time)
主键-联合主键（每张表都应该有个主键 ） 主键——数据库表中每一行的唯一标识
约束： not null/ unique / primart key / foreign key

如果说数据是字典的话，索引就是字典的目录

mysql常用的索引有两种B+Tree和Hash

默认是前者（实际上InnoDB只支持BTree）

创建索引 删除索引：

create index idx_email on student(email)
drop index idx_email on student
create index_idx_email using hash on student(email) # 还是BTree

like ‘%A’ 这种查询不一定快

索引

为什么要有索引

1. mysql 数据存储在什么地方

   磁盘

2. 查询数据比较慢，一般情况下卡在那里？

   IO

3. 去磁盘读取数据的时候，是用多少读取多少吗？

   磁盘预读

4. 索引存储在哪里?

   磁盘，查询数据的时候会优先将索引加载到内存中

5. 索引在存储的时候需要什么信息？需要存储什么字段值

   key: 实际数据行中存储的值

   文件地址

   offset：偏移量

6. 这种格式的数据要使用什么样的数据结构进行存储

   K-V

   哈希表，树（二叉树，红黑树，AVL树，B树，B+树）

7. mysql 的索引系统中不是按照刚刚说的格式存储的，为什么

mysql 索引系统采用的数据结构

根据这个课做的笔记
2021年最新马士兵老师MySQL_基础+高级篇-MySQL数据库_性能优化_mysql底层原理_mysql教程_mysql索引优化_mysql实战_马士兵教育_哔哩哔哩_bilibili

BST 在递增的情况下查询 退化成链表

AVL：为了保证平衡，在插入数据的时候必须要旋转，通过插入性能的损失来弥补查询性能的提升

红黑树: 插入性能和查询性能近似

随着数据的插入，发现树的深度会变深，树 的深度越深，意味着IO次数越多，影响数据读取的效率

（原因：每一个节点有且只有两个分支）

解决：把原来的有序二叉树变成有序多叉树

实际存储表数据的时候

key

完整的数据行

16×16×16 =4096

B+树

叶子节点才存储数据, 非叶子节点不存储数据

mysql一般情况3到4层,要不然就要分库分表

创建索引的时候 到底用 int 还是varchar? 取决于int varchar 谁更小,占的空间更小

回表 聚簇索引 非聚簇索引

索引的创建跟存储引擎是挂钩的------存储引擎表示不同的数据在磁盘的不同组织形式

聚簇索引对用户不可见

非聚簇索引放的是数据所在key的值

myisam 非聚簇索引放的实际数据行的地址

在满足业务需求,主键尽量自增,减少磁盘块或者数据页分裂的过程,append效率高

开始没主键,后来有主键,聚簇索引会变成主键

1. mysql

2. 回表

   select * from table where name= 'zhou'
   先根据name查询id,再根据id查询 整行的记录
   走了2颗B+树,此时这种现象叫做回表
   当根据普通索引查询到聚簇索引的key值之后,再根据key值在聚簇索引中获取所有行记录
   

3. 索引覆盖

   如果一个索引包含所有需要查询字段的值,我们称之为覆盖索引

   select id name from table where name='zhou'
   根据name可以直接查询到id(聚簇索引),name两个列的值,直接返回即可,不需要从聚簇索引查询任何数据,此时叫做索引覆盖
   

4. 最左匹配

   
   组合索引,先比较第一列再比较第二列
   

5. 索引下推

   直接根据name和age的值从存储引擎中筛选数据,不需要server做任何处理,减少了server层和存储引擎的数据交互io量

索引匹配方式

1. 全值匹配
2. 匹配最左前缀
3. 匹配列前缀
4. 匹配范围值
5. 精确匹配某一列并范围匹配另外一列
6. 只访问索引的查询

索引优化

索引排序 file_sort

索引涉及到页维护 页分裂

猜不了用没用索引 看执行计划explain select * from table where …\

1. 当使用索引列进行查询的时候尽量不要使用表达式,把计算放到业务层而不是数据库层

   select actor_id from actor where actor_id=4
   select actor_id from actor where actor_id+1=4
   

2. 尽量使用主键查询,而不是其他索引,因此主键查询不会触发回表查询

3. 使用前缀索引--------select count(*) as cnt,left(city,5) as pref from citydemo group by pref limit 10

   有时候需要索引很长的字符串,这会让索引变的大且慢,通常情况下可以使用某个列开始的部分字符串,这样大大的节约索引空间,从而提高索引效率,但这会降低索引的选择性,索引的选择性是指不重复的索引值和数据表记录总数的比值,范围从1/#T到1之间.索引的选择性越高则查询效率越高,因为选择性更高的索引可以让mysql

   在查找的时候过滤更多的行

   一般情况下某个列前缀的选择性也是足够高的,足以满足查询的性能,但是对应BLOB,TEXT,VARCHAR类型的列,必须要使用前缀索引,因为mysql不允许索引这些列的完整长度,使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性,通过又不能太长

4. 使用索引扫描来排序

   explain select rental_id,staff_id from rental where rental_date='2005-05-25' order by
   inventory_id,customer_id\G
   

   mysql有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描,如果explain出来的type列的值为

   index,则说明mysql使用了索引扫描来做排序

   扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录.但如果索引不能覆盖查询所需的全部列,那么不得不每扫描一条索引记录就得回表查询一次对应的行,这基本都是随机IO,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢

   mysql可以使用同一个索引即满足排序,又用于查找行,如果可能的话,设计索引时应该尽可能地同时满足这两种任务

   只有当索引的列顺序和order by子句的顺序完全一致,并且所有列的排序方式都一样时,mysql才能够使用索引来对结果进行排序,如果查询需要关联多张表,**则只有当order by子句引用的字段全部为第一张表时,才能使用索引做排序.**order by 子句和查找型查询的限制是一样的,需要满足索引的最左前缀的要求,否则,mysql都需要执行顺序操作,而无法利用索引排序

5. union all,in,or都能够使用索引,但是推荐使用in

   explain select * from actor where actor_id=1 union all select * from actor where actor_id=2
   explain select * from actor where actor_id in (1,2)
   explain select * from actor where actor_id=1 or actor_id=2 
   

6. 范围列可以用到索引

   • 范围条件是: <,<=,>,>=,between
   • 范围列可以用到索引,但是范围列后面的列无法用到索引,索引最多用于一个范围列

7. 强制类型转换会全表扫描

   explain select * from user where phone=123241241    不会触发索引
   explain select * from user where phone=`123241241`  触发索引
   

8. 更新十分频繁,数据区分度不高的字段上不宜建立索引

   1. 更新会变更B+树,更新频繁的字段建议索引会大大降低数据库性能
   2. 类似于性别这类区分不大的属性,建议索引是没有意义的,不能有效的过滤数据
   3. 一般区分度在80%以上的时候就可以建立索引,区分度可以使用count(distinct(列名))/count(*) 来计算

9. 创建索引的列,不允许为null,可能会得到不符合预期的结果

10. 当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致

11. 能使用limit的时候尽量使用limit

12. 单表索引建议控制在5个以内

13. 单索引字段数不允许超过5个(组合索引)

14. 创建索引的时候应该避免以下错误概念

    1. 索引越多越好
    2. 过早优化,在不了解系统的情况下进行优化

来计算

9. 创建索引的列,不允许为null,可能会得到不符合预期的结果

10. 当需要进行表连接的时候,最好不要超过三张表,因为需要join的字段,数据类型必须一致

11. 能使用limit的时候尽量使用limit

12. 单表索引建议控制在5个以内

13. 单索引字段数不允许超过5个(组合索引)

14. 创建索引的时候应该避免以下错误概念

    1. 索引越多越好
    2. 过早优化,在不了解系统的情况下进行优化