一、业务场景
随着业务系统的用户访问量越来越大,数据表存储的数据量也会越来越大。建表阶段如果没有做到很好的表结构设计,系统响应会越来越慢,在数据量没有达到需要分库分表的时候,可以考虑增加和优化索引来快速实现查询性能的提升。
分库分表会增加系统复杂度,数据量没有达到一定程度,优先考虑通过索引优化性能
二、解决办法
分析表原有的索引和一些慢sql,设计出更合理的索引。
给表userinf添加索引,语句:
alter table `userinf`
add index `name` (`name`);
三、概念
索引:是数据库管理系统中一个有序的数据结构,以协助快速查询。
通俗一点:索引的作用相当于图书的目录,可以根据目录中的页码快速找到所需的内容。
特点:
1、索引是有序的。
2、索引是一种数据结构。
3、提升查询数据的效率,但同时会降低更新数据的效率。
优点
1.大大加快数据的查询速度;
2.创建唯一性索引,保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时,可以显著减少查询中分组和排序的时间。
缺点
1.索引需要占物理空间。
2.当对表中的数据进行更新(增、删、改)的时候,索引也要动态的维护,降低了数据的更新速度。
四、索引
(一)MySQL索引结构
MySQL中一般支持以下几种常见的索引结构:
- B+树索引(默认结构,且重点要掌握)
- 全文索引
- 哈希索引
(二)B+树索引
这个后续我再详细来写,比较重要,掌握了不仅对面试,对工作也很有帮助。
(三)索引的类型
(四) 什么字段适合创建索引
1、频繁用于查询条件的字段上创建索引
2、在order 排序 和 join的 on 字段上创建索引
3、每张表必须有自己的主键索引(建议用自增主键)
(五) 什么字段不适合创建索引
1、区分度低的字段,例如性别,不要建索引。离散度太低,导致扫描行数过多
2、频繁更新的值,不要作为主键或者索引,页分裂
3、过长的字段,一般不建索引,真有需要,建议使用前缀索引
4、大内容大文本字段不建索引
(六)创建索引注意要点
1、索引的个数不要过多,浪费空间,更新变慢。一般建议每张表的索引不要超过5个。
2、数据量大的表创建索引会很慢,建议在用户访问量少的时候执行创建索引。
3、多字段作为条件查询时,要善于利用联合索引
(七)什么情况索引会失效
1、索引列上使用函数(replace/SUBSTR/sum/count/avg)、表达式、计算(+ - * /)等;
2、字符串不加引号,出现隐式转换;
3、like 条件中前面带%
4、负向查询 NOT LIKE,!=,<>等
怎么判断索引是不是失效的?用explain(执行计划)分析。后续文章会详细讲怎么通过explain分析自己的sql
(八)如果一张表没有主键索引怎么办?
如果没有定义主键,则 InnoDB 会选择第一个不包含有 NULL 值的唯一索引作为主键索引。
如果也没有这样的唯一索引,则 InnoDB 会选择内置 6 字节长的 ROWID 作为隐藏的聚集索引,它会随着行记录的写入而主键递增。