mysql索引重构

mysql索引重构,第1张

1,创建索引(PRIMARY KEY,INDEX,UNIQUE)

支持创建主键索引,联合索引和普通索引命令

mysql>ALTER TABLE tbl_name ADD INDEX index_name (column list)

mysql>ALTER TABLE tbl_name ADD UNIQUE index_name (column list)

mysql>ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column list)

2,删除索引(PRIMARY KEY,INDEX,UNIQUE)

支持删除主键索引,联合索引和普通索引命令

mysql>ALTER TABLE tbl_name DROP INDEX index_name (column list)

mysql>ALTER TABLE tbl_name DROP UNIQUE index_name (column list)

mysql>ALTER TABLE tbl_name DROP PRIMARY KEY index_name (column list)

3,重建索引

mysql>REPAIR TABLE tbl_name QUICK

4,查看某个数据表的索引

mysql>SHOW INDEX FROM tbl_name

其中tbl_name表示数据表名,index_name表示索引名,column list表示字段列表

索引就是为特定的mysql字段进行一些特定的算法排序,比如二叉树的算法和哈希算法,哈希算法是通过建立特征值,然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引,没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引,不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表,针对较大的数据,生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构,

第二类是innodb存储引擎使用的叫做聚簇结构(也是一种 b-tree)。 如下图:

注意:

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

​ 有索引列参与计算的查询条件对索引不友好(甚至无法使用索引),如from_unixtime(create_time) = '2014-05-29'。

​ 原因很简单,如何在节点中查找到对应key?如果线性扫描,则每次都需要重新计算,成本太高;如果二分查找,则需要针对from_unixtime方法确定大小关系。

因此,索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

​ 如有索引(a, b, c, d),查询条件a = 1 and b = 2 and c >3 and d = 4,则会在每个节点依次命中a、b、c,无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

​ ​ 冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引,应当尽量避免这种索引,发现后立即删除。比如有一个索引(A,B),再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时,比如有人新建了索引(A,B),但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

        select user.* from user where login_time >'2017-04-01' and age between 18 and 30

​ 比如想查询某个时间段内登录过的用户:它有两个范围条件,login_time列和age列,MySQL可以使用login_time列的索引或者age列的索引,但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

​ 如果一个索引包含或者说覆盖所有需要查询的字段的值,那么就没有必要再回表查询,这就称为覆盖索引。覆盖索引是非常有用的工具,可以极大的提高性能,因为查询只需要扫描索引会带来许多好处:

1.索引条目远小于数据行大小,如果只读取索引,极大减少数据访问量2.索引是有按照列值顺序存储的,对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如,用性别作索引,那么索引仅能将1000w行数据划分为两部分(如500w男,500w女),索引几乎无效。

区分度的公式是count(distinct ) / count(*),表示字段不重复的比例,比例越大区分度越好。唯一键的区分度是1,而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上(键值唯一的记录数/总记录条数),选择性越高索引的效果越好、价值越大,唯一索引的选择性最高;

组合索引中字段的顺序,选择性越高的字段排在最前面;

where条件中包含两个选择性高的字段时,可以考虑分别创建索引,引擎会同时使用两个索引(在OR条件下,应该说必须分开建索引);

不要重复创建彼此有包含关系的索引,如index1(a,b,c) 、index2(a,b)、index3(a);

组合索引的字段不要过多,如果超过4个字段,一般需要考虑拆分成多个单列索引或更为简单的组合索引;

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销,对于插入、删除、更新 *** 作也会增加处理上的开销,而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之,索引的建立必须慎重,对每个索引的必要性都应该经过仔细分析,要有建立的依据。

PRIMARY, INDEX, UNIQUE 这3种是一类

PRIMARY 主键。 就是 唯一 且 不能为空。

INDEX 索引,普通的

UNIQUE 唯一索引。 不允许有重复。

FULLTEXT 是全文索引,用于在一篇文章中,检索文本信息的。

举个例子来说,比如你在为某商场做一个会员卡的系统。

这个系统有一个会员表

有下列字段:

会员编号 INT

会员姓名 VARCHAR(10)

会员身份z号码 VARCHAR(18)

会员电话 VARCHAR(10)

会员住址 VARCHAR(50)

会员备注信息 TEXT

那么这个 会员编号,作为主键,使用 PRIMARY

会员姓名 如果要建索引的话,那么就是普通的 INDEX

会员身份z号码 如果要建索引的话,那么可以选择 UNIQUE (唯一的,不允许重复)

会员备注信息 , 如果需要建索引的话,可以选择 FULLTEXT,全文搜索。

不过 FULLTEXT 用于搜索很长一篇文章的时候,效果最好。

用在比较短的文本,如果就一两行字的,普通的 INDEX 也可以。


欢迎分享,转载请注明来源:内存溢出

原文地址: https://outofmemory.cn/zaji/5933553.html

(0)
打赏 微信扫一扫 微信扫一扫 支付宝扫一扫 支付宝扫一扫
上一篇 2023-03-08
下一篇 2023-03-08

发表评论

登录后才能评论

评论列表(0条)

保存