【mysql 详解】mysql 索引详解

snakelis

已于 2024-05-16 15:04:55 修改

阅读量323

点赞数 5

分类专栏： mysql 文章标签： mysql 数据库

于 2024-05-16 14:18:07 首次发布

本文链接：https://blog.csdn.net/snakelis/article/details/138959860

版权

mysql 专栏收录该内容

16 篇文章 0 订阅

订阅专栏

索引

用来快速检索出具有特定值的记录

概念

存储类型

BTREE
HASH

INNODB和MYISAM只支持BTREE

优点

加快数据查询速度
减少查询中分组和排序的时间
唯一索引可以保证每一行数据的唯一性
表关联的查询条件加速表之间的连接

缺点

占用磁盘空间、物理空间
损耗性能（增删改），索引页需要动态维护
创建索引和维护索引的时候耗费时间

索引使用

正确使用

避免使用select *
=和in可以乱序
连接代替子查询
尽量使用短索引
count(id)或者count(1)代替count(*)
B-tree 索引 is null 不会走， is not null 会走

错误使用

like ‘%xx’ 不走索引
!= 、<> 不走索引
索引列不能做计算
用or分割条件，若or前后只要有一个列没有索引，就都不会用索引
当根据索引排序时候，选择的映射如果不是索引，则不走索引
数据类型出现隐式转化，不会使用索引（类型不一致）
索引不会包含NULL列,如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串

注意事项

最左前缀匹配原则
建表的时候char 代替varchar
表字段顺序固定长度的字段优先
组合索引代替单列索引
连表注意条件类型一致
索引散列值（重复少）不适合建索引。例：性别不适合
查询中很少使用到的列不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求
定义为text和image和bit数据类型的列不应该增加索引
当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
不等号<>在主键字段和唯一索引字段中会走索引，在普通索引的字段上不会走索引。

查看全部索引

    SELECT  table_name,index_name FROM mysql.`innodb_index_stats` a WHERE a.`database_name` = 'sf' and index_name <> "PRIMARY" GROUP BY index_name,table_name;

联合索引作用

减少开销

建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销！

覆盖索引

对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。

效率高

索引列越多，通过索引筛选出的数据越少。有1000W条数据的表，有如下sql:select from table where col1=1 and col2=2 and col3=3,假设假设每个条件可以筛选出10%的数据，如果只有单值索引，那么通过该索引能筛选出1000W10%=100w条数据，然后再回表从100w条数据中找到符合col2=2 and col3= 3的数据，然后再排序，再分页；如果是联合索引，通过索引筛选出1000w10% 10% *10%=1w，效率提升可想而知！