mysql索引实践

  |   0 评论   |   0 浏览

存储引擎分类

innodb 聚簇索引(clustered index)的数据存储方式

MyISAM 非聚簇索引(secondary index) 的数据存储方式
聚簇索引

行数据和主键B+树存储在一起,其他索引键B+树只存储索引键和主键,主键和非主键索引B+树是两种类型的树。
非聚簇索引

主键和索引的B+树在叶子节点存储指向真正数据行的指针。

image201811211812.png

问题: innodb & myisam 存储引擎优缺点对比。

innodb索引原理: https://www.cnblogs.com/shijingxiang/articles/4743324.html

索引调优

explain sql;

idselect_typetabletypepossible_keyskeykey_lenrefrowsextra
* 序号* 语句的类型* SIMPLE, PRIVARY, UNION* 正常都是SIMPLE* 表名

* 常用的类型: ALL, index, range, const, NULL(从左到右,性能从差到好)* 使用哪个索引在表中找到记录,查询涉及到的字段上若存在索引,则该索引将被列出,但不一定被查询使用* 实际使用的索引* 通过该列计算查询中使用的索引的长度* 不损失精确性的情况下,长度越短越好 * 根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数Using whereUsing indexUsing filesortUsing temporary

索引是否高效的本质:Cardinality:索引基数

  • 索引选择性是不重复的索引值也叫基数(cardinality)表中数据行数的比值,索引选择性=基数/数据行,基数可以通过“show index from 表名”查看。
  • 高索引选择性的好处就是mysql查找匹配的时候可以过滤更多的行,唯一索引的选择性最佳,值为1。

索引实践指导:

  • 选择索引基数高的列
  • 选择 where,on,group by,order by 中出现的列
  • 选择较小的数据列,这样的索引文件更小,同时可装载更多的索引键
  • 为较长的字符串使用前缀索引
  • 组合索引能减低索引文件的大小,使用速度也优于多个单列索引
  • 切勿滥用索引,因为除了磁盘空间的开销外,每次增删改都需要重新建立索引
  • 索引不会包含有NULL值的列(若组合索引包含NULL值的列则整个组合索引无效)

实际例子分析:

activity库:

explain select * from app_paint_record where create_time >= '2018-10-30 10:00:01.026' and create_time < '2018-10-31 10:00:01.025' order by heat desc limit 0,10;