mysql视图会导致索引失效嘛 mysql视图能用索引吗

转载

误会一场 2023-08-12 19:21:30

文章标签 mysql视图会导致索引失效嘛字段主键全文索引 文章分类 MySQL 数据库

简介Mysql的索引和视图

索引

索引相当于字典中的查找条件，常用于快速找出某一列中一特定的值，使用索引可以提升查找的速度。

索引的设计原则

1.选择唯一性

唯一性索引的值是唯一的，可以更快速的通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段。为该字段建立唯一性索引可以很快的确定某个学生的信息。如果使用姓名的话，可能存在同名现象，从而降低查询速度。

2.为经常需要排序、分组和联合操作的字段建立索引

经常需要ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间。如果为其建立索引，可以有效地避免排序操作。

3.为常作为查询条件的字段建立索引

如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。

4.限制索引的数目

索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

5.尽量使用数据量少的索引

如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间要多。

6.尽量使用前缀进行索引

如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

7.删除不再使用或者是很少使用的索引

表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

8.遵循最左前缀匹配原则

mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a 1=”” and=”” b=”2” c=”“> 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

9.=和in可以乱序

比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式

10.尽量选择区分度高的列作为索引

区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录

11.索引列不能参与计算保持“干净”

比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

12.尽量扩展索引不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

Mysql索引

1.普通索引：最基本的索引没有任何的限制

2.唯一索引：索引的列必须唯一没有重复值但是允许有空值

3.主键索引：特殊的唯一索引，不能有空值

4.全文索引：MyISAM引擎生成索引非常耗时

5.组合索引：提高查询效率多个字段组合遵循最左原则

索引的类型

1.B-Tree 索引最常见的索引类型大部分引擎都支持的索引类型

2.hash索引 memory 引擎才支持

3.R-tree 索引 MyISAM 引擎特有的索引类型主要运用于地理空间数据

4.Full-text MyISAM 引擎特有的索引类型用于全文索引 MySQL5.6 innodb引擎也支持了全文索引

创建索引

alter 和 create 2种方式创建索引。注：create不支持创建主键索引

create index in_name on t1(name);
show index from t1\G; 查看所有的索引 \G以更好地阅读体验阅读
drop index in_name on t1;删除索引
create unique index un_name on t1(name); #保证字段列不能有重复值
drop index un_name on t1;

alter table t1 add index in_name(name); #alter方式创建索引
show index from t1\G;
alter table t1 drop index in_name;#删除索引
alter table t1 drop primary key; #删除主键索引必须先消除自增
alter table t1 modify id int(11) unsigned not null; #消除自增

alter table t1 add primary key(id);#创建主键索引
alter table t1 modify id int(11) unsigned not null auto_increment;

alter table t1 add unique(name);
alter table t1 drop index name; #没有索引名称的唯一索引

alter table t1 add unique un_name(name);#唯一索引有名字
alter table t1 drop index un_name;

alter table t3 add index name_age(name,age); #组合索引最左原则创建完组合索引以后 where条件中必须有 name 或者name age 条件都有否则这个索引失效