mysql数据库索引相关知识

最新推荐文章于 2024-05-23 18:28:57 发布

IT超哥

最新推荐文章于 2024-05-23 18:28:57 发布

阅读量285

点赞数 2

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

本文链接：https://blog.csdn.net/baidu_19965419/article/details/131054606

版权

1 篇文章 0 订阅

订阅专栏

索引的分类

主键索引
唯一索引
常规索引
全文索引
聚集索引，每个表必须有，且只有一个，优先使用主键，无主键时取第一个唯一索引，再无时会自动生成rowid作为聚集索引
二级索引
www.cs.usfca.edu – 教育网站，数据结构动画显示过程。

sql提示是优化数据库的一个重要手段，简单来说，就是在sql语句中加入一些人为的提示来达到优化操作的目的。

select * from table_name use index(idx_user_pro) where profession = '软件工程'
-- use index 建议使用索引
-- ingnore index 忽略索引
-- force index 强制索引

尽量使用覆盖索引，查询使用索引，并且需要返回的列在该索引中已经全部能够找到，减少select *
using index condition:查找使用了索引，但需回表查询数据，查询性能会下降
using where
using index

字段如果是很长的字符串，创建索引会导致i/o性能很差，查询效率非常低。

create index idx_name on user_info(e_mail(5)) -- 基于e_mail字段的前5个字符创建前缀索引

id值相同，从上到下，ID值越大，越先执行。
select_type simple，即简单表，不使用表连接或子查询；primary 主查询，即外层的查询；union union中的第二个或者后面的查询语句；subquery select/where之后包含的子查询
type 表示连接类型，性能由好到差的连接类型为 null,system,const,eq_ref,ref,range,index,all。
select ‘a’ 相当于null
访问系统表，相当于system
主键或unique索引，相当于const
非唯一索引相当于ref
用了索引，但对索引进行了全量搜索 index
possible_key 显示可能用到的索引
key 实际到的索引，如果为null 则表示没有使用到索引
key_len
rows 执行查询的行数，是一个预估值
filtered，返回结果的行数战胜读取行数百分比，100%最好
extra，执行时额外信息

insert 优化
1、尽量批量插入数据
2、手动提交事务
3、大数据量时，可以使用load指令进行更大批量的加载数据。
主键优化
innoDB存储引擎中，表数据是根据主键顺序组织存放的，称为索引组织表（Index Organized table IOT）
乱序主键会导致而分裂，数据删除会导致页合并
所以主键设计原则建议：
1、满足业务需求情况下，主键长度尽量降低
2、插入时，尽量顺序插入，选择使用Auto_Increment自增主键
3、尽量不要使用uuid 或其他自然主键例如身份证号
4、业务操作时，避免对主键的修改
order by 优化
using filesort：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果排序都叫filesort排序
using index：通过有序索引顺序扫描直接返回有序数据，这种情况为using index，不需要额外排序，操作效率高。
创建索引时默认为升序，可以指定索引排序，例如

create index idx_age_pro on tb_user(age asc,pro desc);

1、根据排序字段建立合适的索引，多个字段排序时，也遵循最左前缀法则。
2、尽量使用覆盖索引
3、多字段排序，一个升一个降，此时需要注意联合索引在创建时的规则
4、如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小，sort_buffer_size(默认256k)

关注