MYSQL---索引

一、索引的分类

1.普通索引index ：加速查找
创建第一种格式：

CREATE INDEX IndexName(索引名) ON ‘tableName’ (‘column(length)’);

第二种格式：

ALTER TABLE tableName ADD INDEX IndexName (column(length))；

如果是char、varchar类型，length可以小于字段的实际长度；如果是BLOB或TEXT就必须指定长度

2.唯一索引
1）主键索引：primary key：加速查找+约束（不为空且唯一）
2）唯一索引：unique：加速查找+约束（唯一）

CREATE UNIQUE INDEX account_UNIQUE_Index ON tableName(column);

3.联合索引
primary key(id,name)：联合主键索引
unique(id,name)：联合唯一索引
index(id,name)：联合普通索引

4.全文索引：用于搜索很长一篇文章的时候，效果最好

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

5.空间索引spatial：几乎不用

注意：从底层数据结构来划分的话，主要有两种：一种是基于B+树的索引，一种是基于哈希表的索引。
基于哈希表的索引在等值查询上有绝对优势，单其他方面就不是很好用了。
基于B+树是一种多分支的树结构，相比二叉树来说高度降低了很多，能够有效的减少磁盘IO，所以我们平时使用的都是基于B+树的索引

二、索引的优缺点

优点：
1）可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性
2）建立索引可以大大提高检索的数据，以及减少表的检索行数
3）在表连接的连接条件可以加速表与表直接的相连
4）在分组和排序字句进行数据检索，可以减少查询的时间中分组和排序时所消耗的时间（数据库的记录会重新排序）
5）建立索引，在查询中使用索引可以提高性能
缺点：
1）在创建索引和维护索引会消耗时间，随着数据量的增加而增加
2）索引文件会占用物理空间，除了数据表需要占用物理空间之外，每一个索引还会占用一定的物理空间
3）当对表的数据进行insert、update、delete的时候，索引也要动态的维护，这样就会降低数据的维护速度，（建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件会膨胀很快）

三、索引的使用场景

1.首先要确定优化的目标，在什么样的业务场景下，表的大小等。如果表比较小，可能就不需要加索引

2.哪些字段可以建索引？一般都是where、order by或者group by后面的字段

3.记录修改的时候需要维护索引，所以会有开销，要衡量建索引之后的得与失（空间+维护换时间）
比如学生表，可以认为name的重复度比较小，而age重复度比较大，对于单列索引来说，比较适合建在重读低的列上
对于

select * from students where name = ‘张三’ and age = 18;

A、name和age各自单独建立索引：
一般来说mysql会选择其中一个索引，name的可能性比较大，因为mysql会统计每个索引上的重复度，选用低重复度的字段。所以不使用age，否则增加太多成本。
B、name和age的联合索引：
这种索引的切合度最好。但是相对索引来说，维护的成本大，索引数据占用的存储空间也要更大。

可是！有必要这样使用联合索引吗？一般没必要：需要有1w个学生，叫张三的会超过5个吗？5个找一个比建立联合索引花销小的多。

4、什么情况下使用联合索引好呢？

比如：大学修课，需要创建一个关系对应表，有2个字段，student_id和teacher_id,想要查询某个老师和某个学生是否存在师生关系。
一个学生会选50老师，一个老师会带200个学生
如果只为student_id建立索引的情况下，经过索引会选出50条记录，然后再内存中where一下，去除其余的老师。
相反如果只为teacher_id建立索引，经过索引会选出200条记录，然后再内存中where一下，去除其余的学生。
两种情况都不是最优的，因为使用索引后范围依然很大，这个时候使用联合索引最合适，通过索引直接找到对应记录，差不多提高了一倍的效率。

mysql在以下操作场景下会使用索引
1）快速查找符合where条件的记录
2）快速确定候选集。若where条件使用了多个索引字段，则mysql会优先使用能使候选记录集规模最小的那个索引，以便尽快淘汰不符合条件的记录
3）如果表中存在几个字段构成的联合索引，则查找记录时，这个联合索引的最左前缀匹配字段也会被自动作为索引来加速查找
例如：若为某表创建了3个字段(c1,c2,c3)构成的联合索引，则(c1),(c1,c2),(c1,c2,c3)均会作为索引，(c2,c3)就不会被作为索引，而(c1,c3)其实只利用到c1索引
4）多表做join操作时会使用索引(如果参与join的字段在这些表中均建立了索引的话)
5）若某字段已建立索引，求该字段的main()或者max()时，mysql会使用索引
6)对建立了索引的字段做sort或group操作时，mysql会使用索引

四、普通索引&唯一索引&复合索引使用场景

1. 当数据多且字段值有相同的值得时候用普通索引。
2. 当字段多且字段值没有重复的时候用唯一索引。
3. 当有多个字段名都经常被查询的话用复合索引。
4. 普通索引不支持空值，唯一索引支持空值。
5. 但是，若是这张表增删改多而查询较少的话，就不要创建索引了，因为如果你给一列创建了索引，那么对该列进行增删改的时候，都会先访问这一列的索引，
6. 若是增，则在这一列的索引内以新填入的这个字段名的值为名创建索引的子集，
7. 若是改，则会把原来的删掉，再添入一个以这个字段名的新值为名创建索引的子集，
8. 若是删，则会把索引中以这个字段为名的索引的子集删掉。
9. 所以，会对增删改的执行减缓速度，
10. 所以，若是这张表增删改多而查询较少的话，就不要创建索引了。
11. 更新太频繁地字段不适合创建索引。
12. 不会出现在where条件中的字段不该建立索引。

五、判断是否用到并充分用到索引

怎么判断一个sql是否用到索引
执行语句：

如果有key值表示用到了索引，
key_len查看是否充分用到索引
len值计算：
a、一般地，key_len 等于索引列类型字节长度，例如int类型为4-bytes，bigint为8-bytes；
b、如果是字符串类型，还需要同时考虑字符集因素，例如：CHAR(30) UTF8则key_len至少是90-bytes；
c、若该列类型定义时允许NULL，其key_len还需要再加 1-bytes；
d、若该列类型为变长类型，例如 VARCHAR（TEXT\BLOB不允许整列创建索引，如果创建部分索引，也被视为动态列类型），其key_len还需要再加 2-bytes;

备注：key_len只指示了where中用于条件过滤时被选中的索引列，是不包含order by/group by这一部分被选中的索引列的。
例如,有个联合索引idx(c1,c2,c3),3列均是int not null,那么下面的SQL执行计划中,key_len的值是8而不是12:
select … from tb where c1=? and c2=? order by c1;

六、索引的有效利用

哪些语句会真正用到索引：
从MySQL官网文档"Comparison of B-Tree and Hash Indexes"可知，下面这些类型的SQL可能会真正用到索引：
1）索引列上的范围查找；对于某个条件进行范围查找时，如果这个列上有索引，且使用 where … between and … =, >, >=, <, <=等范围操作，那么可能用到索引范围查找，如果索引范围查找的成本太高，数据库可能会选择全表扫描的方式。注意in不属于范围查找的范畴。
2) 若like语句的条件是不以通配符开头的常量串，MySQL也会使用索引
比如，
SELECT * FROM tbl_name WHERE key_col LIKE 'Patrick%'或
SELECT * FROM tbl_name WHERE key_col LIKE 'Pat%_ck%'可以利用索引，
而SELECT * FROM tbl_name WHERE key_col LIKE ‘%Patrick%’（以通配符开头）
和SELECT * FROM tbl_name WHERE key_col LIKE other_col（like条件不是常量串）无法利用索引。
对于形如LIKE '%string%'的sql语句，若通配符后面的string长度大于3，则MySQL会利用Turbo Boyer-Moore algorithm算法进行查找。
3) 若已对名为col_name的列建了索引，则形如"col_name is null"的SQL会用到索引
4) 对于联合索引，sql条件中的最左前缀匹配字段会用到索引
就目前来说，mysql 暂时只支持最左前缀原则进行筛选。
例子：创建复合索引 如：
create index idx_a_b_c on tb1(a,b,c)
只有使用如下条件才可能应用到这个复合索引
1.where a=?
2.where a = ? and b = ?
3.where a = ? and b = ? and c = ?
4.where a = ? and c ＝ ？只会使用到mysql 索引 a 列的信息

5）join列
在联合查询两个表时，比如查询语句为

select a.col1,b.col2 from a join b on a.id = b.id,

其中id 为两个表的主键，如果a是小表，那么a 就被视为驱动表，那么数据库可能全表扫描a 表，并用 a表的每个id 去探测b表的索引查询匹配的记录。

6）where子句：
形如：

where a = ? and b = ? and c>1000
where a = ? and b = ? and c = ? and d>1000

where 子句的条件列是复合索引前面的索引列＋另一个列的范围查找

create index idx_a_b_c_d on tb1(a,b,c,d);

形如：

where a = ? and b = ? and c>1000
where a = ? and b = ? and c = ? and d>1000

才会用到这个索引
下面两个查询：

where a = ? and b =? and c>10000 and d< 10000

这个例子中d
d <10000这个操作不会走索引

where a >? and b =? and c>10000 and d< 10000

这个例子中a列上有范围查找，那么b、c、d列上的索引信息都不能被利用
原则，创建索引，考虑把复合索引的范围查找放到最后。
注意：创建索引时将带范围查询的列放在最后，这样能充分用到索引

SELECT id,name,age FROM table WHERE name=’’ and age>10 and age<30
创建索引：CREATE INDEX index_name ON table_name (age,name)
改为：CREATE INDEX index_name ON table_name (name,age)

7）mysql优化器
mysql 优化器会做一些特殊优化，比如对于索引查找max(索引列)可以直接进行定位。遇到max，min 是可以在列上做索引。

七、无法使用索引的情况

(1)字段或者where子句中使用函数或者表达式，将无法使用索引
(2)Join 语句中 Join 条件字段类型不一致的时候 MySQL 无法使用索引
(3)复合索引的情况下，如果查询条件不包含索引列的最左边部分，即不满足最左前缀原则，则不会使用索引
(4)如果mysql估计使用索引扫描比全表扫描更慢，则不使用索引。(扫描数据超过30%，都会走全表)
(5)以%开头的like查询
(6)数据类型出现隐式转换的时候也不会使用索引，如varchar不加单引号的话可能会自动转换为int型
(7)or语句前后没有同时使用索引，比如：用or分割开的条件，如果 or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到

八、索引的注意事项

1.where 条件中的索引列不能是表达式的一部分，mysql 不支持函数索引

2.InnoDB 二级索引底层叶子极点存储的是索引+主键值
InnoDB 的非主键索引存储的不是实际的记录的指针，而是主键的值，所以主键最好是整数型，如自增ID ,基于主键存取数据是最高效的，使用二级索引存取数据则需要进行二次索引查找。

3.索引尽量是高选择性的
而且要留意基数值，基数值指的是一个列中不同值的个数，显然，最大基数意味着该列中的每个值都是唯一的，最小基数意味着该列中的所有值都是相同的，索引列的基数相对于表的行数较高时，也就重复值更少，索引的工作效果更好。有种情况虽然基数小，但由于数据分布很不均匀因此也会导致某些记录数很小，那么这种情况也适合建立索引加速查找这部分数据。

4.使用更短的索引
可以考虑前缀索引，但应确保选择的前缀的长度可以保证大部分值是唯一的。
如：alter table test add key(col(6))
衡量不同前缀索引唯一值比例。
select count(distinct left(col_name,5))/count() As sele5，
select count(distinct left(col_name,6))/count() As sele6，
select count(distinct left(col_name,7))/count() As sele7，
select count(distinct left(col_name,8))/count() As sele8，
select count(distinct left(col_name,9))/count(*) As sele9
from table_name;

5.避免创建过多的索引
索引过多可能会浪费大量空间尤其本身字段量较大的字符串，索引过多可能会浪费空间，且降低修改数据的速度，所以，不要创建过多的索引，也不要创建重复的索引。

6.如果是唯一值得列，创建唯一索引会更佳，也可以确保不会出现重复数据

7.使用覆盖索引能大大提高性能
覆盖索引：所有数据都可以从索引中得到，而不需要去读物理记录。例如某个复合索引idx_a_b_c 建立在表tb1 的 a、b、c 列上，那么对于如下的sql 语句:

select a,b from tb1 where a = ? and b = ? and c =?

mysql可以直接从索引idx_a_b_c 中获取数据。使用覆盖索引也可以避免二次索引查找。
使用explain 命令输出查询计划，如果extra列是“using index ” 那就表示使用的是覆盖索引。

8.利用索引来排序
mysql 有两种方式可以产生有序结果，一种是使用文件排序，另一种是扫描有序的索引，我们尽量使用索引来排序
注意事项：
1)尽量保证索引列和order by 的列相同，且各列按照相同的顺序排序。
比如在表table1 的复合索引idx_a_b_c（创建在a,b,c上)；
如：select * from table1 order by a,b,c;
select * from table1 where a=? and b =? order by c
以上查询都可以利用有序索引来加速检索顺序。

2)如果连接多张表，那么order by 引用的列需要再表连接顺序的首张表内。

9 添加冗余索引需要权衡
如果一个索引column A 那么一个新的索引（columnA,columnB）就是冗余索引
一般情况下不论是新增冗余索引，还是扩展原索引为冗余索引，都会导致索引文件的增大，并且增加了维护索引的开销。比如更改了列值，并且在此列上建立了索引，那么这个列值更改之后，索引是要进行重新排序的。

10 索引不会包含NULL列
如果列中包含NULL值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ’ '字符串

11.使用短索引
如果你的一个字段是Char(32)或者int(32),在创建索引的时候指定前缀长度 比如前10个字符 (前提是多数值是唯一的…)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.

12.不要在列上进行运算,这样会使得mysql索引失效,也会进行全表扫描

13.选择越小的数据类型越好
因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快