微信登录

表 - 列 - 索引(index) - 看索引show index from tb

表 - 列 - 索引(index) - 看索引show index from tb
  1. show index from tb # 看索引
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
表名 是否唯一索引 索引名称 索引列在索引中的位置 索引列的名称 方式 唯一值数量 前第几个为索引 压缩 列能否为空 索引类型 注释 注释
user_t 0 PRIMARY 1 id A 100000 N BTREE
user_t 1 Key_name 1 ticket_id A 100000 NULL NULL无压缩 BTREE
FULLTEXT
HASH
RTREE 
  1. 1、Table：索引所在的表名
  2. 2、Non_unique：0表示该索引是唯一索引，1表示该索引不是唯一索引
  3. 3、Key_name：索引的名称
  4. 4、Seq_in_index：索引列在索引中的位置，从1开始。对于组合索引来说，这个字段很重要。
  5. 5、Column_name：索引列的名称
  6. 6、Collation：索引列的值以什么方式存储在索引中。在MySQL中，A 表示有排序，B+树索引使用该方式；NULL 表示无序的，Heap索引使用该方式；
  7. 7、Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。索引列所有值去重后的数量。该值除以该表的行数越接近1越好，如果非常小，则需要考虑是否可以删除该索引！
  8. 8、Sub_part：数值 N 表示只对该列的前 N 个字符进行索引；NULL 表示索引整个列的值
  9. 9、Packed：指示关键字是否被压缩，NULL 表示没有压缩
  10. 10、Null：索引列是否可以为空
  11. 11、Index_type：索引类型，BTREE 表示B+树索引。一共有四种（BTREE, FULLTEXT, HASH, RTREE）。
  12. 12、Comment：注释
  13. 13、Index_comment：注释

索引

把常用的列加索引可以增加查询速度
任何标准表最多可以创建 16 个索引列。

索引的作用

  • 提高查询效率
  • 消除数据分组、排序
  • 避免“回表”查询（索引覆盖）
  • 优化聚合查询
  • 用于多表JOIN关联查询
  • 利用唯一性约束，保证数据唯一性
  • InnDB行锁实现
  • 查字典：有索引：看拼音
  • 查字典：无索引：一页一页找

索引的副作用

  • 增加I/O成本
  • 增加磁盘空间
  • 不合适的索引或索引过多，会降低增删改的效率

索引的分类 1 存储结构

索引 数据库 例子
BTREE（B+树结构存储数据） InnoDB 、 MyISAM、MEMORY、HEAP 范围查找，顺序存储
HASH HEAP、NDB、InnoDB、AHI hash表，hash码，无法用于排序，不适用于区分度小的列上，如性别字段
Fractal Tree TokuDB
RTREE
FULLTEXT
数据库 支持索引
InnoDB BTREE
MyISAM BTREE
MEMORY HASH、BTREE
HEAP HASH、BTREE

索引的分类 2 数据的存储方式

存储方式 描述 存在数量 例子1 例子2
聚集索引 聚簇索引的顺序就是数据的物理存储顺序，索引与数据存放在同一个文件中。 1个 字典：a-z 图书馆：a-z
非聚集索引 非聚簇索引的顺序与数据的物理存储顺序不同，索引与数据存放在不同的文件。 多个 字典：部首 图书馆：作者/出版社

索引的分类 3 应用层次

  • 单列：主键索引、唯一索引、普通索引
  • 多列：复合索引

创建索引原则

(1) 定义主键的数据列一定要建立索引。

(2) 定义有外键的数据列一定要建立索引。

(3) 对于经常查询的数据列最好建立索引。

(4) 对于需要在指定范围内的快速或频繁查询的数据列;

(5) 经常用在WHERE子句中的数据列。

(6) 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要和这些关键字后面的字段顺序一致，否则索引不会被使用。

(7) 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

(8) 对于定义为text、image和bit的数据类型的列不要建立索引。

(9) 对于经常存取的列避免建立索引

(10) 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了访问速度，但太多索引会影响数据的更新操作。

(11) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。

表 - 列 - 索引(index) - 看索引show index from tb