MySQL 索引

索引：

索引是帮助MySQL高效获取数据的数据结构。

普通索引语句：

1.创建索引
CREATE INDEX indexName ON table_name (column_name)

2.修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)

3.创建表的时候直接锁定
CREATE TABLE mytable(  
 
ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );

删除索引：

DROP INDEX [indexName] ON mytable;

查询算法：

常见的查询算法有顺序查找、二分查找、二叉树排序查找、哈希散列法、分块查找、平衡多路搜索B树(B-tree)

常见索引原则：

1、选择唯一性索引

• 唯一性索引值是唯一的，可以更快的通过该索引来确定某条记录；
• 主键索引和唯一索引，在查询中使用是效率最高的；
• 重复值很多建议用联合索引；

索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：

• 创建索引

  CREATE UNIQUE INDEX indexName ON mytable(username(length))
  

  修改表结构

  ALTER table mytable ADD UNIQUE [indexName] (username(length))
  

  创建表的时候直接指定

  CREATE TABLE mytable(  
   
  ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
  

2、为经常需要排序、分组和联合操作的字段建立索引

• 经常需要：排序(ORDER BY) 、分组(GROUP BY) 、连接(join on)等操作的字段，排序操作会浪费很多时间。

3、为常用where查询条件的字段建立索引

• 如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度。因此，为这样的字段建立索引，可以提高整个表的查询速度。经常查询、列值的重复值少(业务层面调整)

• 如果经常作为条件的列，重复值特别多，可以建立联合索引。

4、尽量使用前缀来索引

• 如果索引字段的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间。如果只检索字段的前面的若干个字符，这样可以提高检索速度。

5、限制索引的数目

• 索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大。修改表时，对索引的重构和更新很麻烦。越多的索引，会使更新表变得很浪费时间。

6、删除不在使用或者很少使用的索引

• 表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引，将它们删除，从而减少索引对更新操作的影响。

7、大表加索引，要在业务不繁忙期间操作

8 、最左前缀匹配原则，非常重要的原则

10 、 尽量选择区分度高的列作为索引

• 区分度的公式是表示字段不重复的比例

11 、索引列不能参与计算，保持列“干净”：带函数的查询不参与索引

12 、尽量的扩展索引，不要新建索引

注意事项：

标红重点关注的，以下是能保证则保证的。

建索引原则

• 必须要有主键,如果没有可以做为主键条件的列,创建无关列
• 经常做为where条件列 order by group by join on的条件(业务:产品功能+用户行为)
• 最好使用唯一值多的列作为索引,如果索引列重复值较多,可以考虑使用联合索引
• 列值长度较长的索引列,我们建议使用前缀索引.
• 降低索引条目,一方面不要创建没用索引,不常使用的索引清理,percona toolkit
• 索引维护要避开业务繁忙期