mysql索引及索引创建原则

1. mysql索引及索引创建原则

目录

• 1. mysql索引及索引创建原则
  • 1.1. 使用场景
    • 1.1.1. 什么时候用索引
    • 1.1.2. 索引的弱点
    • 1.1.3. MySQL 会使用到索引的场景如下：
  • 1.2. 查看表上的索引
  • 1.3. 索引类型
    • 1.3.1. 组合索引
      • 1.3.1.1. 组合索引生效规则
    • 1.3.2. 前缀索引
    • 1.3.3. 函数索引
    • 1.3.4. 唯一索引
    • 1.3.5. 全文索引
    • 1.3.6. 空间索引
  • 1.4. 索引优化
    • 1.4.1. 主键优化
    • 1.4.2. 外键优化
  • 1.5. MySQL优化之索引创建规则

1.1. 使用场景

1.1.1. 什么时候用索引

首先mysql只对<,<=,=,>=,>,between,in,like使用索引，因此where和join(on)的时候就应该考虑到使用什么样的索引，而且要记住一条sql语句只能使用一个索引，比如有where和order by同时出现的时候，order by 即使用的是索引字段，他也不会走索引。

1.1.2. 索引的弱点

(1) 加了索引之后，那insert、delete和update操作都要更新索引，会造成一些附加操作
(2) 索引是保存在物理文件(.MYI)里的，所以如果在一张大表上创建索引，势必会造成索引文件也比较大

1.1.3. MySQL 会使用到索引的场景如下：

1. 根据一个条件快速的匹配到对应的行。

2. 缩小查询影响行数。如果一个查询字段有多个索引，MySQL 通常选择使用影响行数最小的索引（选择性最高的索引）。索引的选择性的计算

select count(distinct name) / count(*) from table; 

3. 对于组合索引，索引左边的列可以用索引前缀优化器来查询数据。例如，你有个三列的组合索引（col1,col2,col3） ，那么你可以使用索引查询（col1）,(col1,col2),(col1,col2,col3)这三种组合的数据。

4. 当和其他表进行连表查询的时候，如果进行判断的列的数据类型和大小相同，那么再这两个列上使用索引，可以让判断更加效率。例如：在如下查询中，给tb1.name和tb2.name添加索引会提升查询效率。

SELECT * FROM tb1, tb2 WHERE tb1. name = tb2. name

在这里，VARCHAR 与 CHAR 被认为是相同的类型。需要注意的是，如果要让索引生效，不仅需要类型一致，大小也必须一致。例如，VARCHAR(10) 和 CHAR(10) 大小相同可以使用索引，但 VARCHAR(10) 与 CHAR(15)就无法使用索引。

5. 查找索引列的 MIN() 或 MAX()值。

6. 通过索引列进行排序或分组，或者组合索引的左前缀进行排序或分组。

7. 查询索引列的内容。（如果只需要返回索引列的值，那么不需要查询数据行，直接从内存中读取检索值。这种情况称为覆盖索引）
   例如：

SELECT key_part FROM table WHERE key_part=1 

对于小型表或报表查询处理大多数或所有行的大型表的查询，索引不太重要。当查询需要访问大多数行时，顺序读取比通过索引更快。顺序读取可以最大限度地减少磁盘搜索，即使查询不需要所有行也是如此。只有数据较大，并且需要访问其中一部分数据的时候，索引才会显得比较重要。

1.2. 查看表上的索引

SHOW INDEX FROM table；

   table： 表的名称
　　Non_unique： 索引是否可以重复。不可以重复则为0；可以重复则为1。
　　Key_name： 索引名称。创建的时候，可以选择输入，不输入 MySQL 自动生成。如果索引是主键，则名称始终为 PRIMARY。
　　Seq_in_index： 索引中的列序列号，从1开始。
　　Column_name： 索引涉及到的列的名称。
　　Collation：列如何在索引中排序。这可以具有值 A（ascending 升序），D （ descending 降序）或NULL（未排序）。
　　Cardinality： 索引中唯一值的数量（不是实时更新的准确数据）。
　　Sub_part： 索引前缀长度。如果使用字段的部分字符作为索引，那么显示索引字符数量。如果使用整个字段都被索引，那么为 NULL。
　　Packed： key的打包方式，NULL 表示不打包。
　　Null： 索引列包含 NULL 或者 ‘’ 的时候，会是 YES。
　　Index_type： 索引类型。（BTREE， FULLTEXT，HASH， RTREE）之一。
　　Comment： 未在当前列中描述的索引信息，例如 disabled 索引是否已禁用。
　　Index_comment： 在创建索引时提供的注释。
　　Visible： 索引是否对优化程序可见（有的版本会出现该信息）。

1.3. 索引类型

1.3.1. 组合索引

组合索引是一个由多个列组成的索引。举例说明：例如在表 address 中有三个字段，分别为 Provincial 省 city 市 county 县 在建表的时候，用这三个字段组成一个组合索引。代码如下：

 CREATE TABLE address (
     provincial VARCHAR (10),
     city VARCHAR (10),
     county VARCHAR (10),
     INDEX (provincial, city, county)
 )
CREATE TABLE address

这里的索引是这样创建的：首先按照省排序，然后，再根据同一个省的内容，按照市进行排序，最后，按照县去排序。即，首先按照第一列进行索引排序，如果第一列内容一致，那么按照第二列进行排序，以此类推。

1.3.1.1. 组合索引生效规则

1. 组合索引的生效原则是 从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；
   比如
   （a,b,c） 三个列上加了联合索引（是联合索引 不是在每个列上单独加索引）

where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

2. 还需注意， (a,b,c)多列索引和 (a,c,b)是不一样的，看上面的图也看得出来关系顺序是不一样的；
   分析几个实际例子来加强理解；
   分析句子中使用的索引情况

(0)    select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用
(1)    select * from mytable where  c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样
(2)    select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的
(3)    select * from mytable where a=3 and b>7 and c=3;
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引
(4)    select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果
(5)    select * from mytable where a>4 and b=7 and c=9;
a用到了  b没有使用，c没有使用
(6)    select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的
(7)    select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort
(8)    select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

快速生成1000W测试数据库,创建测试表：

create table user (  
    id int(10) not null auto_increment,   
    uname  varchar(20) ,  
    regtime  char(30)  ,  
    age  int(11)   ,
    primary key (id)
)  
engine=myisam default charset=utf8 collate=utf8_general_ci  ,
auto_increment=1 ;

编写存储过程:

delimiter $$
SET AUTOCOMMIT = 0$$
 
create  procedure test()
begin
declare v_cnt decimal (10)  default 0 ;
dd:loop
          insert  into user values
    (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50),
        (null,rand()*10,now(),rand()*50);
      commit;
        set v_cnt = v_cnt+10 ;
           if  v_cnt = 10000000 then leave dd;
          end if;
         end loop dd ;
end;$$

delimiter ;

调用存储过程：
call test();

如果组合索引是(A,B)，则对于条件A=a，是可以用上这个组合索引的，因为组合索引是先按照第一列进行排序的，所以没必要对A单独建立一个索引，但是对于B=b就用不上了，因为只有在第一列相同的情况下，才比较第二列，因而第二列相同的，可以分布在不同的节点上，没办法快速定位

但是复合索引对于or查询不起作用

加上索引后and查询是可以走索引的，但是只有一个索引起作用，对于另一个索引字段还是要进行遍历，而且and查询会根据关联性高(符合该条件的行数少)选择具体走哪个索引

1.3.2. 前缀索引

前缀索引是用列的一部分字符去建立索引，更加节省空间（因为只使用一部分字符，索引建立时长度会降低），选择合适的话，效率也会更高。

如果将字符串的列作为索引，可以创建前缀索引。一般情况下某个前缀的选择性也是足够高的，足以满足查询性能。对于BLOB，TEXT，或者很长的VARCHAR类型的列，必须使用前缀索引。前缀索引以字节为单位。前缀索引支持的长度取决于存储引擎。例如，对于InnoDB 使用 REDUNDANT 或 COMPACT 行格式的表， 前缀最长可达767字节。对于InnoDB使用DYNAMIC 或 COMPRESSED 行格式的表， 前缀长度限制为3072字节 。对于MyISAM表，前缀长度限制为1000个字节。

如果指定的索引前缀超过最大列数据类型大小，对于非唯一索引，如果启用了严格的SQL模式，创建会发生错误。如果未启用严格SQL模式，索引长度减少到最大列数据类型大小，并产生警告。

创建前缀索引的长度，取决于索引的选择性。
创建前缀索引语法如下(这里的10 表示截取前10个字符)：

CREATE INDEX key_part_name ON table_name (key_part(10)); 
ALTER TABLE table_name ADD INDEX index_name (key_part(10)) 

前缀索引可以兼顾索引大小和查询速度。可以利用相对更小的空间，用更快的速度，查出数据。但是它也有缺点：前缀索引无法用于 ORDER BY 和 GROUP BY 操作，也不能用于索引覆盖。

1.3.3. 函数索引

这里的索引类型英文名称为：Functional Key Parts
这个索引类似于两个前缀索引的拼接。直接举个例子就明白了：在 t1 表中有两个列，col1 和 col2 我要创建一个包含完整的 col1 列和 col2 列的前10个字节组成一个组合索引。代码如下：

CREATE TABLE t1 (
  col1 VARCHAR(10),
  col2 VARCHAR(20),
  INDEX (col1, col2(10))
);

在 MySQL 8.0.13版本及更高版本中，MySQL 支持表达式进行索引（函数索引）。这里，需要将运算表达式写在括号内进行索引的声明。例如：

1 -- 函数索引
CREATE TABLE t1 (
      col1 INT,
      col2 INT,
      INDEX func_index ((ABS(col1)))
  );

CREATE INDEX idx1 ON t1 ((col1 + col2));

CREATE INDEX idx2 ON t1 (
     (col1 + col2),
     (col1 - col2),
     col1
 );

ALTER TABLE t1 ADD INDEX ((col1 * 40) DESC);

1.3.4. 唯一索引

通过 UNIQUE 创建的索引。索引列的内容非null值的时候必须是唯一的，null值可以不唯一。如果添加重复值，则会发生错误。如果在创建唯一索引的时候指定前缀值，那么前缀必须是唯一的。
创建语法：

CREATE UNIQUE INDEX unique_index_name ON table_name (key_part) 

1.3.5. 全文索引

全文索引，顾名思义，支持全文检索的索引。仅支持 Innodb 和 MyISAM 两种存储引擎。并且只能包括 CHAR, VARCHAR 和 TEXT 列，索引始终发生在整个列上，不支持前缀索引。（即使写了也没用）可以对字段进行全文检索。对于数据量比较大的数据集，先将数据加载到没有数据的表中，然后再添加索引，效率要比把数据直接向有索引的表中添加高。

1.3.6. 空间索引

空间索引是为空间搜索提供一种合适的数据结构，以提高搜索速度。对于空间索引，作者研究并不多，这里大概介绍下空间索引的用处。等以后研究深刻后，再补上这部分内容。首先，空间索引是干什么用的？举例：当我们需要按照某个点，查询附近的50米内都有哪些客户存在。对于这个需求，可能的解法如下：
　　1.我们可以根据用户的经纬度，去计算每个人跟我们的当前点的距离，然后跟50米去作对比。这在客户数据量少的时候，还可以这么做，数据量大的时候，将会特别的浪费性能。
　　2.先画一个方框，把50米范围的数据的经纬度画出来，通过经纬度的值进行筛选后，得到一个正方形的区域，然后再进行计算。这时候，会少很多计算，但依然不是最优方案。
　　3.使用空间索引。将空间按照一定规则划分为不同的区域，在检索的时候，根据设计的区域，取出相应的数据。空间索引结构图如下（图片来自知乎）：

1.4. 索引优化

1.4.1. 主键优化

　　表的主键是唯一且非空的索引，在使用InnoDB存储引擎的时候，表数据直接挂载在主键的叶子节点上，是查询速度最快的索引。

　　如果表的内容很多，并且很重要。但是没有明显的列和列的集合作为主键的话，可以单独创建一个自动增长的值作为主键。当使用外链查询的时候，这个id可以作为指向内容的指针。

1.4.2. 外键优化

　　如果你的表有很多列，你可以将查询频率比较低的列拆分到其他表格，并通过复制id的方式让它们与主表关联。这样，每个小表都会有个主键来快速查找其他数据。在查询的时候，就可以仅查询自己需要的列集。这时，查询会执行较少的 I/O 并且占用较少的内存。整体原则是：为了提高性能，尽可能少的从磁盘读取数据。这就是拆表的原则

1.5. MySQL优化之索引创建规则

1.表的主键、外键必须有索引；
2.数据量超过300的表应该有索引；
3.经常与其他表进行连接的表，在连接字段上应该建立索引；
4.经常出现在Where子句中的字段，特别是大表的字段，应该建立索引；
5.索引应该建在选择性高的字段上；
6.索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引；
7.复合索引的建立需要进行仔细分析；尽量考虑用单字段索引代替：
8.正确选择复合索引中的主列字段，一般是选择性较好的字段；
9.复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引；否则考虑单字段索引；
10.如果复合索引中包含的字段经常单独出现在Where子句中，则分解为多个单字段索引；
11.如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段；
12.如果既有单字段索引，又有这几个字段上的复合索引，一般可以删除复合索引；
13.频繁进行数据操作的表，不要建立太多的索引；
14.删除无用的索引，避免对执行计划造成负面影响；

以上是一些普遍的建立索引时的判断依据。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。因为太多的索引 与不充分、不正确的索引对性能都毫无益处：在表上建立的每个索引都会增加存储开销，索引对于插入、删除、更新操作也会增加处理上的开销。另外，过多的复合 索引，在有单字段索引的情况下，一般都是没有存在价值的；相反，还会降低数据增加删除时的性能，特别是对频繁更新的表来说，负面影响更大。