索引有什么用
- 为从数据库读取数据加速
- 强制约束 (唯一索引 UNIQUE, 外键 FOREIGN KEY)
- 没有任何索引的情况下查询页能正常运行
- 但是那可能需要执行很长的时间
你可能听说过的索引类型
- BTREE索引 – mysql中主要的索引类型
- RTREE索引 – 只有MyISAM支持, 用于GIS
- HASH 索引 – MEMORY, NDB 支持
- BITMAP 索引 – MySQL 不支持
- FULLTEXT 索引 – MyISAM, Innodb（MySQL 5.6以上支持）
MyISAM、Innodb索引对比
-
MyISAM
数据指针指向数据文件中的物理位置
所有索引都是一样的（指向物理位置）） Innodb
主键索引 (显式或隐式) - 直接将数据存储于索引的叶子节点，而不是指针
二级索引 – 保存主键索引的值作为数据指针
BTREE索引能用于什么操作 ?
查询所有 KEY=5 的记录 (点查询)
查询所有 KEY>5 的记录 (开合间)
查询所有 5<KEY<10 的记录 (闭合间)
不适用于：查询KEY最后一个数字等于0的所有记录
因为这不能定义为范围查询操作
字符索引
这（和数值）没什么区别… 真的
collation是为字符串定义的排序规则
如： “AAAA” < “AAAB”
前缀LIKE 查询是一种特殊的范围查询
LIKE “ABC%” 的意思是：
“ABC[最小值]”<KEY<“ABC[最大值]”
LIKE “%ABC” 无法使用索引查询
联合索引
是这样进行排序的， 比较首列，然后第二列，第三列以此类推，如：
KEY(col1,col2,col3)
(1,2,3) < (1,3,1)
使用一个BTREE索引，而不是每个层级一个单独的BTREE索引
索引的开销
索引是昂贵的，不要添加多余的索引
多数情况下，扩展索引比添加一个新的索引要好
写 - 更新索引常常是数据库写操作的主要开销
读 - 需要再硬盘和内存开销空间; 查询优化中需要额外的开销
索引成本的影响
- 长主键索引（Innodb） – 使所有相应的二级索引 变得更长、更慢
- “随机”主键索引（Innodb） – 插入导致大量的页面分割
- 越长的索引通常越慢
- Index with insertion in random order – SHA1(‘password’)
- 低区分度的索引是低劣的 – 在性别字段建的索引
- 相关索引是不太昂贵的– insert_time与自增id是相关的
Innodb表的索引
- 数据按主键聚集
- 选择最佳的字段作为主键
- 比如评论表 – (POST_ID,COMMENT_ID) 是作为主键的不错选择，使得单个post的评论聚在一起
- 或者 “打包” 单个 BIGINT（字段）
- 主键隐式地附加到所有索引中
- KEY (A) 实质上是 KEY (A,ID)
- 覆盖索引，有利于排序
MySQL是如何使用索引的
查询
排序
避免读取数据（只读取索引）
其他专门的优化
MySQL优化器的第一法则
在复合索引中，MySQL在遇到返回查询(<,>,BETWEEN)时，将停止中止剩余部分（索引）的使用；但是使用IN(…)的"范围查询"则可以继续往右使用索引（的更多部分）
MySQL使用索引排序的规则
- 不能对两个字段进行不同顺序的排序
- 对非ORDER BY部分的字段只能使用点查询（=）– 在这种情形下，IN()也不行
