性能优化--索引

一、业务场景

        随着业务系统的用户访问量越来越大，数据表存储的数据量也会越来越大。建表阶段如果没有做到很好的表结构设计，系统响应会越来越慢，在数据量没有达到需要分库分表的时候，可以考虑增加和优化索引来快速实现查询性能的提升。

分库分表会增加系统复杂度，数据量没有达到一定程度，优先考虑通过索引优化性能

二、解决办法

分析表原有的索引和一些慢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 作为隐藏的聚集索引，它会随着行记录的写入而主键递增。