Mysql索引

最新推荐文章于 2024-07-17 15:00:15 发布

mrun

最新推荐文章于 2024-07-17 15:00:15 发布

阅读量129

点赞数

分类专栏： MySql 文章标签： mysql

本文链接：https://blog.csdn.net/u013032345/article/details/85217315

版权

MySql 专栏收录该内容

6 篇文章 0 订阅

订阅专栏

之前被公司的架构问到关于mysql索引的内容,回答不是很好,在家再看下,总结下.

参考文档:

https://blog.csdn.net/wulex/article/details/69540136
https://www.cnblogs.com/bypp/p/7755307.html

如果有不对的地方请指正,谢谢.

1.什么是索引?

网上的解释很多,不多重复,只说自己的理解
我理解的索引是一个键,类似于一个数组的key,字典里的目录,只需要通过这个键,快速定位到一条数据

2.为什么要加索引?

当一个表里的数据量过多,每一次都是全表扫描,是很费时间的.因此要适当的添加索引.

3.如何添加和删除索引?

主键索引(primary index) 在一个表中是唯一的,且不为空
唯一索引(unique index)在一个表中是唯一的,可控
普通索引(index) 没有任何约束
联合索引(index,index…)多个索引的结合
全文索引(fulltext index) 很少使用.适用于字符较长的字段,不推荐使用

#1 . 创建表时建立索引
CREATE TABLE 表名 (
             字段名1  数据类型 [完整性约束条件…],
             字段名2  数据类型 [完整性约束条件…],
             [UNIQUE | FULLTEXT | SPATIAL ]   INDEX | KEY
	         [索引名]  (字段名[(长度)]  [ASC |DESC])
             );
# EXAMPLE:            
create table t3 (
     id tinyint unsigned NOT NULL PRIMARY KEY AUTO_INCREMENT,
     name varchar(32) NOT NULL UNIQUE KEY,
     gender char(6) NOT NULL,
     email varchar(32) NOT NULL,
     index idx_gender(gender) #这种普通索引必须在这里添加
     # PRIMARY KEY(id) ,UNIQUE KEY (name) 
     #主键和唯一索引可以在字段后面定义,也可以在这里定义
     );

#2 . CREATE在已存在的表上创建索引
CREATE  [UNIQUE | FULLTEXT | SPATIAL ]  INDEX  索引名 
                ON 表名 (字段名[(长度)]  [ASC |DESC]) ;
# EXAMPLE  在这里好像不能添加主键
CREATE INDEX ix_name_email on t1(gender,email);  #在t1表上创建联合索引

#3. ALTER TABLE在已存在的表上创建索引
ALTER TABLE 表名 ADD  [UNIQUE | FULLTEXT | SPATIAL ] INDEX
                      索引名 (字段名[(长度)]  [ASC |DESC]) ;
# EXAMPLE 这里可以直接添加主键
ALTER TABLE t1 ADD PRIMARY KEY (id);

注意:在创建表时,不可以像primary key 和 unique key一样直接定义到字段后面,因为普通索引是没有约束的. 必须得在定义好字段之后才能添加普通索引

#删除索引  普通索引和唯一索引可以直接drop也alter删除 而主键必须得alter删除
drop index ix_name_email  on t1; # drop index 后面直接跟索引名就行.不用管是普通索引还是唯一索引还是联合索引
alter table t1 drop primary key; #删除主键(因为它添加的时候是按照alter来增加的，那么我们也用alter来删)

# 查看索引    
show  index from t1

4.什么时候需要添加索引?

现在已经知道了如何添加删除和查看一个表的索引了.
那么什么时候添加,什么时候又不加?
以下是自己的一点理解,加上网上查阅的资料.
未必所有的字段都适合添加索引,需要结合实际来.

表的主键和外键添加索引.
数据量较大的表应该添加索引
对于唯一确定的字段添加唯一索引,如手机号,身份证等等
经常用于查询条件的字段,如你经常使用select * from t1 where name = 'xxx' 那么你可以添加一个name字段的索引
如果你经常使用select * from t1 where gender ='xxx' and name ='xxx' 这种查询,那么你可以考虑联合索引
对于联合索引的添加需要自行判断,这里贴一个别人的理解对于联合索引的添加需要自行判断,这里贴一个别人的理解

A、正确选择复合索引中的主列字段，一般是选择性较好的字段；
B、复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
C、如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
E、如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；

如果你经常进行连表查询,那么需要连表的字段应该建立索引.且两个字段字符类型应该是相同的,否则索引不起作用
索引应该建立在小字段上,对于大的文本字段甚至超长字段，不要建索引
频繁进行数据操作的表，不要建立太多的索引
字段中相同数据过多时,添加索引是没用的
删除多余没必要的索引
给需要排序的字段设置一个索引也是不错的.

对于索引的选择必须要结合实际.根据自己的需求来选择,不能盲目的认为索引加了就是好的

对于一个状态字段,是否应该添加索引呢?

状态字段,这个字段的值肯定有大多是相同的 ,并且状态字段频繁的进行修改
对于这种字段是不建议添加索引的.建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)
如: select count(distinct name) / count(*) from t1
区分度过低则不太适合添加索引

对于联合索引的选择?

比如有一条语句是这样的：select * from t1 where name=’xxx’ and gender='xxx' and age = 22;
如果我们给 name,gender,age这三个字段分别都添加索引的话,那么虽然看上去是三个索引,但在and xxx and xxx这种查询语句上实际只使用了一种.

不如联合索引查询的快.因此我们建立 index(name,gender,age)联合索引,

这种联合索引又可以看作 (name,gender)索引和(name)索引. 相当于建立了三个索引,这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

如果在查询条件中同时包含 name,gender,age这个三个条件,那么顺序无所谓

MySql在建立索引优化时需要注意的问题
设计好MySql的索引可以让你的数据库飞起来，大大的提高数据库效率。设计MySql索引的时候有一下几点注意：

以下是copy https://blog.csdn.net/wulex/article/details/69540136 过来的

1，创建索引

对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引(联合索引)

比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效
率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,
salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列

只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引

对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题

mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作

一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%a%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算

select * from users where YEAR(adddate)

8，不使用NOT IN和操作

NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id3则可使用id>3 or id

暂时写到这

实践才是检验真理的唯一标准.

mrun

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Mysql索引

之前被公司的架构问到关于mysql索引的内容,回答不是很好,在家再看下,总结下.参考文档:https://blog.csdn.net/wulex/article/details/69540136https://www.cnblogs.com/bypp/p/7755307.html如果有不对的地方请指正,谢谢.1.什么是索引?网上的解释很多,不多重复,只说自己的理解我理解的索引是一...
复制链接

扫一扫