mysql index - 书闪Bookchips

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

作者：

贺及楼

成为作者

更新日期：2024-04-07 12:34:46

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

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、Table：索引所在的表名
2、Non_unique：0表示该索引是唯一索引，1表示该索引不是唯一索引
3、Key_name：索引的名称
4、Seq_in_index：索引列在索引中的位置，从1开始。对于组合索引来说，这个字段很重要。
5、Column_name：索引列的名称
6、Collation：索引列的值以什么方式存储在索引中。在MySQL中，A 表示有排序，B+树索引使用该方式；NULL 表示无序的，Heap索引使用该方式；
7、Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。索引列所有值去重后的数量。该值除以该表的行数越接近1越好，如果非常小，则需要考虑是否可以删除该索引！
8、Sub_part：数值 N 表示只对该列的前 N 个字符进行索引；NULL 表示索引整个列的值
9、Packed：指示关键字是否被压缩，NULL 表示没有压缩
10、Null：索引列是否可以为空
11、Index_type：索引类型，BTREE 表示B+树索引。一共有四种（BTREE, FULLTEXT, HASH, RTREE）。
12、Comment：注释
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) 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段上取值相同的记录，系统再按照第二个字段的取值排序，以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，会使系统最大可能地使用此索引，发挥索引的作用。