MySQL数据库之索引及优化详解

索引

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的排好序的数据结构。索引的本质是分散存储的数据结构

索引的数据结构

二叉树（二叉排序树、二叉查找树）

普通的二叉查找树存在缺陷，当数据阻止不规整时，插入的序列越接近有序，生成的二叉搜索树就越像一个链表，会出现类似链表一样的斜树，此时如果查询的数据是0007，和全局扫描没有区别

平衡二叉树（AVL树）

对于树中的任意一个节点，都有该节点的左子树的高度与右子树的高度只差的绝对值小于2

存在的缺陷：

• 搜索效率不足：一般来说树结构中的数据处的深度决定着它的搜索时IO次数

• 节点数据内容太少：每一个磁盘块（节点/页）保存的关键字数据量太小了

  没有很好地利用操作系统和磁盘的数据交换特性和磁盘的预读能力（空间局部性原理）

B-Tree（多路平衡查找树）

B+Tree

B+Tree节点关键字采用左闭合区间

B+Tree非叶子节点不保存数据相关信息，只保存关键字和子节点的引用

B+Tree关键字对应的数据保存在叶子节点中

B+Tree叶子节点是顺序排列的，并且相邻节点具有顺序引用的关系

MySQL为什么选用B+Tree？

• 是B-Tree的plus版，拥有B-Tree的全部优点
• B+Tree扫库、表能力强
• B+Tree的次破案读写能力更强
• B+Tree的排序能力更强
• B+Tree的查询效率更加稳定

索引的几大原则

1、列的离散性：列的比例值越高，离散性越好

2、最左匹配原则：对索引中关键字进行比对，一定是从左到右依次进行，且不可跳过

索引的分类

单列索引：即一个索引只包含单个列，一个表可以有多个单列索引

• 普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

  --1、建表时创建，在sql语句的最后面，如下：
  CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  INDEX [indexName] (username(length))  
  ); 
  --索引名可以省略，如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
  

  --2、建表后添加索引
  create INDEX indexName ON mytable(username(length)); 
  --3、修改表结构
  alter table table_name add index index_name(字段名)
  

• 主键索引：（主索引）根据主键pk_clolum(length)建立索引，不允许重复、不允许 空值，一般在创建表的时候创建主键索引primary key(字段名)

  --1、建表时创建索引
  CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  PRIMARY KEY ('ID')  
  ); 
  --2、修改表结构（一个表只允许有一个主键）
  alter table 'table_name' add primary key '字段名'
  

• 唯一索引：用来建立索引的列的值必须是唯一的、允许空值(只允许有一条空值)

  --1、建表时创建，在sql语句的最后面，如下：
  CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  UNIQUE [indexName] (username(length))  
  ); 
  --2、修改表结构
  alter table 'table_name' add unique index_name(字段名)
  --3、建表后添加索引
  create unique index index_name ON table_name(字段名)
  

组合索引（联合索引）：指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用组合索引时遵循最左前缀集合。

联合索引列选择原则：经常用的列（最左匹配原则）> 离散度高的列（高离散度原则）> 宽度小的列优先（最少空间原则）

--1、建表时一起创建
CREATE TABLE mytable(  
ID INT NOT NULL,   
username VARCHAR(16) NOT NULL,  
INDEX [indexName] ('ID','username'(length))  
); 
--2、建表后，创建组合索引
create index index_name ON table_name('ID','username')
--3、修改表结构
alter table table_name ADD INDEX index_name('ID','username')

聚集索引（聚簇索引）：即索引的逻辑顺序与数据的物理存放顺序相同。一个表中只能有一个聚集索引

非聚集索引：该索引的逻辑顺序与磁盘上的数据物理存放顺序不通，一个表可以用手多个非聚集索引

全文索引:主要用来查找文本中的关键字，而不是直接与索引中的值相比较。

fulltext索引跟其它索引大不相同，它更像是一个搜索引擎，而不是简单的where语句的参数匹配。

fulltext索引配合match against操作使用，而不是一般的where语句加like。

它可以在create table，alter table ，create index使用，不过目前只有char、varchar，text 列上可以创建全文索引。

空间索引

空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时，使用SPATIAL关键字。要求，引擎为MyISAM，创建空间索引的列，必须将其声明为NOT NULL。

覆盖索引：如果查询的列，通过索引项的信息可以直接返回，则该索引称之为查询SQL的覆盖索引

查看表索引信息

show index from 表名；

删除索引

drop index index_name ON 'table_name';
alter table 'table_name' drop index 'index_name';

索引的优缺点

优点

1. 极大地加速了检索过程，减少IO次数
2. 创建唯一索引，保证了数据库表中的唯一性
3. 加速了表与表之间的连接
4. 针对分组和排序检索时，能够显著减少查询查询中的分组和排序时间

缺点

1. 索引本身也是表，因此会占用存储空间
2. 一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大
3. 构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表

索引的使用策略

什么时候使用索引？

• 主键自动建立唯一索引；
• 经常作为查询条件在WHERE或者JOIN 语句中出现的列要建立索引,但也不完全如此，因为MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引；
• 作为排序的列要建立索引；
• 查询中与其他表关联的字段，外键关系建立索引
• 高并发条件下倾向组合索引；
• 用于聚合函数的列可以建立索引，例如使用了max(column_1)或者count(column_1)时的column_1就需要建立索引

什么时候不走索引？索引失效

表student中两个字段age,name加了索引

key 'idx_age'('age'),key 'idx_name'('name')

1. Like这种就是%在前面的不走索引，在后面的走索引

A:select * from student where 'name' `like '王%'
B:select * from student where 'name' `like '%小'

A走索引，B不走索引

2. 用索引列进行计算的，不走索引

A:select * from student where age = 10+8
B:select * from student where age + 8= 18

A走索引，B不走索引

3. 对索引列用函数了，不走索引

A:select * from student where concat('name','哈') ='王哈哈';
B:select * from student where name = concat('王哈','哈');

A不走索引，B走索引

4. 索引列使用了!= 不走索引

select * from student where age !=18

什么时候不要使用索引？

• 经常增删改的列不要建立索引；
• 有大量重复的列不建立索引（例如：表中的性别列）；
• 表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时，它的性能测试结果才有实际参考价值。如果在测试数据库里只有几百条数据记录，它们往往在执行完第一条查询命令之后就被全部加载到内存里，这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录超过了1000条、数据总量也超过了MySQL服务器上的内存总量时，数据库的性能测试结果才有意义。

索引的优化

1、最左前缀

索引的最左前缀和和B+Tree中的“最左前缀原理”有关，举例来说就是如果设置了组合索引<col1,col2,col3>那么以下3中情况可以使用索引：col1，<col1,col2>，<col1,col2,col3>，其它的列，比如<col2,col3>，<col1,col3>，col2，col3等等都是不能使用索引的。

2、带索引的模糊查询优化

在上面已经提到，使用LIKE进行模糊查询的时候，’%aaa%'不会使用索引，也就是索引会失效。如果是这种情况，只能使用全文索引来进行优化（上文有讲到）。

3、为检索的条件构建全文索引，然后使用

SELECT * FROM tablename MATCH(index_colum) ANGAINST(‘word’);

4、使用短索引

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

5、索引不会包含有NULL值的列

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

6、索引列排序

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

7、不要在列上进行运算

select * from users where YEAR(adddate)<2007;
--将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01’; 

8、不使用NOT IN和<>操作

InnoDB与MyISAM分析

InnoDB存储引擎是聚集索引（使用InnoDB存储引擎所生成的表，在本地mysql安装的目录data里，一张表两个文件.frm(表结构) .ibd(索引数据文件)索引和数据不是分离的）

MyISAM存储引擎是非聚集索引（使用MyISAM存储引擎所生成的表，在本地mysql安装的目录data里，一张表三个文件.frm(表结构) .MYD(表数据) .MYI(表索引)索引文件和数据文件是分离的）

select * from table_name where col1= 30;		--假设col1列为索引

以上sql语句查找的底层执行过程：先查看查找列是否为索引字段，如果查询的是索引字段，然后去.MYI文件根据B+Tree的特性（从树的根节点开始查找根节点常驻内存），然后使用二分法查找逐步查找在叶节点上找到key所对应的指针，根据指针去.MYD文件快速查找对应数据

聚集索引查找效率（.ibd文件）肯定比非聚集索引查找效率（.MYI+.MYD）要高

为什么InnoDB表必须要有主键，并推荐使用整形自增主键？

mysql的技术文档里面有如下文字：

If you do not define a PRIMARY KEY for your table, MySQL picks the first UNIQUE index that has only NOT NULL columns as the primary key and InnoDB uses it as the clustered index. If there is no such index in the table, InnoDB internally generates a clustered index where the rows are ordered by the row ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted. Thus, the rows ordered by the row ID are physically in insertion order.

如果表没有主键，甚至都没有唯一键索引的话，InnoDB内部会基于一个包含了ROW_ID值的列生成一个隐式的聚簇索引，行都会根据这个ROW_ID排序。ROW_ID是一个6个字节，即48位的单调递增字段。有新数据插入时，就会生成一个新的递增的ROW_ID。所以，根据ROW_ID排序的行，本质上是按照插入顺序排序。

推荐使用整形主键：是因为在查找时B+Tree特性，整形对比、占用空间相对都更高效

推荐使用自增主键：因为主键自增依据B+Tree的特性，插入数据是在叶节点从左到右依次递增添加，而不会遇到中间插入数据，导致树分裂，运算树平衡，需要大连的计算，影响效率