索引的分类：
根据存储引擎可以分为
聚簇索引
非聚簇索引
还可以分为
单例索引（一个索引只包含单个列，但一个表中可以有多个单列索引。 这里不要搞混淆了。）
普通索引 （MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点）
唯一索引 (索引列中的值必须是唯一的，但是允许为空值)
主键索引 (是一种特殊的唯一索引，不允许有空值)
组合索引
全文索引 (只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引,就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行)
空间索引 (空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。可能跟游戏开发有关)
存储过程 https://www.yiibai.com/mysql/stored-procedure.html
索引是什么，一种可以高效查询的数据结构
索引的类型：unique联合索引 normal普通索引
索引方法：btree hash
inndb和myisam都使用btree索引，但是有区
聚簇索引 非聚簇索引
inndb 数据就在叶子底下(主键节点上) myisam
不用回行(不用去数据文件上找数据) 需要回行
乱序插入的数据查出来是顺序的 乱序插入的数据查出来仍然是乱序
数据和索引挤在一块，所以叫聚簇 索引文件和数据文件分开
多个索引会指向对主键的引用(先找到主键) 多个索引互不相干
优势：不用回行，数据就在主键节点上
劣势：碰到不规则数据插入时，造成频繁的页分裂，会很慢
页分裂：不规则数据插入时，数据不规则的转移
用到的工具
①explain sql语句；
id: 查询的序号
select_type: SIMPLE 简单的查询 PRIMARY主键查询
table: 查询的表名
partitions:
type: 索引发挥的作用，以下几种情况从左到右效果依次递增
all(查全表)->index->range->ref->eq ref->const,system,null(这三个效果最好，优化到常量级别，基本不需要查询时间，一般按主键来查的时候易出现const、system,不过表时出现null，比如count)
possible_keys: 可能使用到的索引
keys: 使用到的索引
key_len: 使用到的索引长度
ref: 表之间的字段引用关系，两表联查的时候
rows: 扫描的行数
extra: 额外信息,有以下几种可能
index 用到了索引覆盖，效率较高
using where 只用索引还差不多，还需where条件
using reamporary 用了临时表（group by和order by用了不同的字段）
using filesort 文件排序，文件可能在磁盘也可能在内存，要回行，可以改进
②show profiles;显示历史查询记录（包括sql语句和时间），使用命令行时可能要开启：set profiling=1;
show profile for query 5;查看单条语句的详细信息
理想的索引：
查询频繁 区分度高 长度小 覆盖常用字段
优化：
思路：不查（有些数据并不需要那么准确）--少查--高效查
1 定长和变长的字段分离，放在不同的表
2 常用字段和不常用字段分离
3 在一对多，需要关联统计的表上添加冗余字段（如要count副表就在主表建count字段自增）
4 字段类型优先级 int->time->date->enum->char->varchar->blod,text
5 选择正确的存储引擎
6 少用select * 等通配符，改为查询具体的字段
7 使用enum 而不是 varchar
8 尽可能的使用not null
字段长度够用就行，不要慷慨,尽量避免用null(),不利于索引，尽量不要使用三表以上联查
把多表连接查询分解为多个sql语句,对于按字段前缀来查询的建立的索引长度3-4为宜
伪哈希索引 给下图的urlcrc加索引
crc32是一种哈希算法，能把字符串换算为32位的整数
有些字段如url不方便加索引时我们可以用crc32转成整数再去加索引
btree 索引 可以理解为“ 排好序的快速查找结构”，在树叶子底下藏着一个该条数据的地址，在通过改地址找到该数据
4
2 6
3 5
1 7
hash索引 在memory表使用（内存引擎）
理论查询时间复杂度为0
缺点：
01 不对范围查询进行优化
02 无法利用前缀索引
03 排序无法优化
04 必须回行（到磁盘上找）
btree常见误区
1 两个字段都加了索引，怎么只用了一个。答：因为建的不是联合索引
2 优化时要根据实际使用场景来优化，哪个字段使用最多，就在哪个字段上建索引
image.png
网友评论