其数据文件本身就是索引文件。
相比myISAM,索引文件和数据文件是分离的,表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。
这个索引的key就是数据表的主键,
因此innoDB表数据文件本身就是主索引,被成为聚蔟索引,也叫聚集索引。
其余的索引都为辅助索引,
辅助索引data域存储相应记录主键的值而不是地址。
在根据主索引搜索时,直接找到key所在的节点即可取出数据,
根据辅助索引查找时,需要先取出主键的值,再走一遍主索引,
设计表时,不建议使用过长的字段作为主键,
也不建议使用非单调的字段作为主键,这样会造成主索引频繁分裂。
通过 explain 命令打印sql语句的执行计划,判断是否命中索引,
type:all 表示全表扫描
key:表示使用的索引,就是主键
extra:using filesort 需要额外的步骤来发现如何对返回的行排序
using temporary 需要创建一个临时表来存储结果,说明查询需要优化
参数说明
id : 表示SQL执行的顺序的标识,SQL从大到小的执行
select_type:表示查询中每个select子句的类型
table:显示这一行的数据是关于哪张表的,有时不是真实的表名字
type:表示MySQL在表中找到所需行的方式,又称“访问类型”。常用的类型有:ALL, index, range, ref, eq_ref, const, system, NULL(从左到右,性能从差到好)
possible_keys:指出MySQL能使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用
Key:key列显示MySQL实际决定使用的键(索引),如果没有选择索引,键是NULL。
key_len:表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度(key_len显示的值为索引字段的最大可能长度,并非实际使用长度,即key_len是根据表定义计算而得,不是通过表内检索出的)
ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值
rows:表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数,理论上行数越少,查询性能越好
Extra:该列包含MySQL解决查询的详细信息
EXPLAIN的特性
EXPLAIN不会告诉你关于触发器、存储过程的信息或用户自定义函数对查询的影响情况
EXPLAIN不考虑各种Cache
EXPLAIN不能显示MySQL在执行查询时所作的优化工作
部分统计信息是估算的,并非精确值
EXPALIN只能解释SELECT操作,其他操作要重写为SELECT后查看执行计划。
当全表扫描,或者索引字段数据重复率太高,将不会使用索引
-
最左前缀原则
查询的适合查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到 -
避免使用where 子句对字段是假函数,to_date() 等。会造成无法命中索引。
-
使用与业务无关的自增主键作为主键,即使用逻辑主键,不要使用业务主键
-
避免冗余索引
-
索引列为not null, 避免全表扫描
-
删除不必要的、长期未使用的索引
-
联表查询使用索引提高性能
-
group by 和order by 中只设计一个表中的列,才有可能使用索引来优化
-
查询条件的字段应使用正确的数据类型,否则mysql会自动做数据类型转换。导致无法命中索引。
慢查询优化基本步骤
先运行看看是否真的很慢,注意设置SQL_NO_CACHE
where条件单表查,锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起,单表每个字段分别查询,看哪个字段的区分度最高
explain查看执行计划,是否与1预期一致(从锁定记录较少的表开始查询)
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果,不符合预期继续从0分析
https://mp.weixin.qq.com/s?__biz=MzI0MjQxNjAyOQ==&mid=2247485299&idx=4&sn=d1e575cdc39de5e842a7d9882d0d150b&chksm=e97de781de0a6e979ce76d49d3fa38d37539cadc70cfdd8762d31c0c51d7da0defd5a30148db&scene=0&xtrack=1#rd