MySQL的索引有什么,什么情况下不会用到索引
1. MySQL中索引有哪些类型
1.1 普通索引
普通索引是MySQL里最基本的索引,没有什么特殊性,在任何一列上都能进行创建。
-- 创建索引的基本语法
CREATE INDEX indexName ON table(column(length));
-- 例子 length默认我们可以忽略
CREATE INDEX idx_name ON user(name);
1.2 主键索引(也叫唯一索引)
我们知道每张表一般都会有自己的主键,MySQL会在主键上建立一个索引,这就是主键索引。主键是具有唯一性并且不允许为NULL,所以他是一种特殊的唯一索引。一般在建立表的时候选定。
1.3 复合索引
复合索引也叫组合索引,指的是我们在建立索引的时候使用多个字段。
例如同时使用身份证和手机号建立索引,同样的可以建立为普通索引或者是唯一索引。
复合索引的使用复合最左匹配原则。举个例子 我们使用 phone和name创建索引。
-- 创建索引的基本语法
CREATE INDEX indexName ON table(column1(length),column2(length));
-- 例子
CREATE INDEX idx_phone_name ON user(phone,name);
我们看下面的查询语句,
SELECT * FROM user_innodb where name = '冯宝宝';
SELECT * FROM user_innodb where phone = '13100046637';
SELECT * FROM user_innodb where phone = '13100046637' and name = '冯宝宝';
SELECT * FROM user_innodb where name = '冯宝宝' and phone = '13100046637';
三条sql只有 2 、 3、4能使用的到索引idx_phone_name,因为条件里面必须包含索引前面的字段才能够进行匹配。而3和4相比where条件的顺序不一样,为什么4可以用到索引呢?
是因为MySQL本身就有一层sql优化,他会根据sql来识别出来该用哪个索引,我们可以理解为3和4在MySQL眼中是等价的。
1.4 全文索引
全文索引主要用来查找文本中的关键字,而不是直接与索引中的值相比较。fulltext索引跟其它索引大不相同,它更像是一个搜索引擎,而不是简单的where语句的参数匹配。fulltext索引配合match against操作使用,而不是一般的where语句加like。
它可以在create table,alter table ,create index使用,不过目前只有char、varchar,text 列上可以创建全文索引。正常情况下我们也不会使用到全文索引,因为这不是MySQL的专长。
1.5 空间索引
空间索引是对空间数据类型的字段建立的索引,MYSQL中的空间数据类型有4种,分别是GEOMETRY、POINT、LINESTRING、POLYGON。MYSQL使用SPATIAL关键字进行扩展,使得能够用于创建正规索引类型的语法创建空间索引。
创建空间索引的列,必须将其声明为NOT NULL,空间索引只能在存储引擎为MYISAM的表中创建。空间索引一般是用不到了,了解即可。
2. 什么情况下不会用到索引
- 如果条件中有or,即使其中有部分条件带索引也不会使用(这也是为什么尽量少用or的原因)
- 对于多列索引,不是使用的第一部分,则不会使用索引
- like查询是以%开头
- 存在索引列的数据类型隐形转换,则用不上索引,比如列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则不使用索引
- where 子句里对索引列上有数学运算,用不上索引
- where 子句里对有索引列使用函数,用不上索引
- 如果MySQL估计使用全表扫描要比使用索引快,则不使用索引
3. 什么情况下不推荐使用索引?
-
数据唯一性差(一个字段的取值只有几种时)的字段不要使用索引(比如性别,只有两种可能数据。意味着索引的二叉树级别少,多是平级。这样的二叉树查找无异于全表扫描。)
-
数据重复且分布平均的字段,因此他建立索引就没有太大的效果(例如性别字段,只有男女,不适合建立索引)
-
频繁更新的字段不要使用索引(比如logincount登录次数,频繁变化导致索引也频繁变化,增大数据库工作量,降低效率。)
-
字段不在where语句出现时不要添加索引,如果where后含IS NULL /IS NOT NULL/ like ‘%输入符%’等条件,不建议使用索引(只有在where语句出现,mysql才会去使用索引)
-
where 子句里对索引列使用不等于(<>),使用索引效果一般
-
数据量小的表不需要建立索引,建立会增加额外的索引开销
-
不经常引用的列不要建立索引,因为不常用,即使建立了索引也没有多大意义
-
经常频繁更新的列不要建立索引,因为肯定会影响插入或更新的效率
-
数据变更需要维护索引,意味着索引越多维护成本越高。
-
更多的索引也需要更多的存储空间
4. 联合索引的优势
联合索引 (a,b,c)
联合索引 (a,b,c) 实际建立了 (a)、(a,b)、(a,b,c) 三个索引
我们可以将组合索引想成书的一级目录、二级目录、三级目录,如index(a,b,c),相当于a是一级目录,b是一级目录下的二级目录,c是二级目录下的三级目录。要使用某一目录,必须先使用其上级目录,一级目录除外。
4.1 减少开销
建一个联合索引 (a,b,c) ,实际相当于建了 (a)、(a,b)、(a,b,c) 三个索引。每多一个索引,都会增加写操作的开销和磁盘空间的开销。对于大量数据的表,使用联合索引会大大的减少开销!
4.2 覆盖索引
对联合索引 (a,b,c),如果有如下 sql 的,
SELECT a,b,c from table where a='xx' and b = 'xx';
那么 MySQL 可以直接通过遍历索引取得数据,而无需回表,这减少了很多的随机 io 操作。减少 io 操作,特别是随机 io 其实是 DBA 主要的优化策略。所以,在真正的实际应用中,覆盖索引是主要的提升性能的优化手段之一。
4.3 效率高
索引列多,通过联合索引筛选出的数据越少。比如有 1000W 条数据的表,有如下SQL:
select col1,col2,col3 from table where col1=1 and col2=2 and col3=3;
假设:假设每个条件可以筛选出 10% 的数据。
- 如果只有单列索引,那么通过该索引能筛选出 1000W10%=100w 条数据,然后再回表从 100w 条数据中找到符合 col2=2 and col3= 3 的数据,然后再排序,再分页,以此类推(递归);
- 如果是(col1,col2,col3)联合索引,通过三列索引筛选出 1000w10% 10% *10%=1w,效率提升可想而知!
5. 额外
- MySql的优化步骤
请直接参考:
http://www.cnblogs.com/hongfei/archive/2012/10/19/2731342.html
参考:https://mp.weixin.qq.com/s/rh6RCuLHbGxGoxpDKsbbWw
https://blog.csdn.net/kaka1121/article/details/53395628