认识mysql索引

认识索引

1.什么是索引

• 官方上面说索引是帮助MySQL高效获取数据的数据结构，通俗点的说，数据库索引好比是一本书的目录，可以直接根据页码找到对应的内容，目的就是为了加快数据库的查询速度
• 索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
• 一种能帮助mysql提高了查询效率的数据结构：索引数据结构。

1.1 索引原理

索引的存储大致可以概括为一句话：以空间换时间
一般来说索引的本身也很大。不可能全部存储在内存中，因此索引往往是存储在磁盘上的文件中的（可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）。
数据库在未添加索引进行查询的时候默认是进行全文搜索，也就是说有多少数据就进行多少次查询，然后找到相应的数据就把它们放到结果集中，直到全文扫描完毕。

1.2 索引的分类

• 主键索引
  • 设定为主键后，数据库自动建立索引，InnoDB为聚簇索引，主键索引列值不能为空（Null）。
• 唯一索引
  • 索引列的值必须唯一，但允许有空值（Null），但只允许有一个空值（Null）。
• 复合索引
  • 一个索引可以包含多个列，多个列共同构成一个复合索引。
• 全文索引
  • Full Text（MySQL5.7之前，只有MYISAM存储引擎引擎支持全文索引）。
  • 全文索引类型为FULLTEXT，在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引可以在Char、VarChar 上创建。
• 前缀索引
  • 在文本类型为char、varchar、text类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定。
• 空间索引
  • MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型，MySQL在空间索引这方年遵循OpenGIS几何数据模型规则。

1.3 聚集索引和非聚集索引的区别

• 聚集索引
  其实，我们的汉语字典的正文本身就是一个聚集索引。比如，我们要查“安”字，因为“安”的拼音是“an”，而按照拼音排序汉字的字典是以英文字母“a”开头并以“z”结尾的，那么“安”字就自然地排在字典的前部。如果您翻完了所有以“a”开头的部分仍然找不到这个字，那么就说明您的字典中没有这个字。也就是说，字典的正文部分本身就是一个目录，您不需要再去查其他目录来找到您需要找的内容。我们把这种正文内容本身就是一种按照一定规则排列的目录称为“聚集索引”。主键就是聚集索引
• 非聚集索引
  除了主键索引其他都是非聚集索引、

1.4 索引的优缺点

优点

• 大大提高数据查询速度
• 可以提高数据检索效率，降低数据库的io成本，类似与书的目录
• 通过索引对数据进行排序，降低数据的排序成本降低了cpu的消耗
• 被索引的列会自动进行排序，包括单列索引和组合索引，只是组合索引的排序会比较复杂一些
• 如果按照索引列的顺序进行排序，对不用order语句来说，效率会提高很多
  缺点
• 索引会占用磁盘空间
• 索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改查操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件。
• 维护索引需要消耗数据库资源。
  综合索引的优缺点：
• 数据库表中不是索引越多越好，而是仅为那些常用的搜索字段建立索引效果最佳!

1.5 索引失效的原因

1.当数据库认为走索引还不如全表搜索的时候
2.存在null值条件
我们在设计数据库表时，应该尽力避免NULL值出现，如果非要不可避免的要出现NULL值，也要给一个DEFAULT值，数值型可以给0、-1之类的， 字符串有时候给空串有问题，就给一个空格或其他。如果索引列是可空的，是不会给其建索引的，索引值是少于表的count(*)值的，所以这种情况下，执行计划自然就去扫描全表了。

select * from test where id is not null;

3.not条件

• 建立索引时，给每一个索引列建立一个条目，如果查询条件为等值或范围查询时，索引可以根据查询条件去找对应的条目。反过来当查询条件为非时，索引定位就困难了，执行计划此时可能更倾向于全表扫描，这类的查询条件有：<>、NOT、in、not exists

  select * from test where id<>500;
  select * from test where id in (1,2,3,4,5);
  select * from test where not in (6,7,8,9,0);
  select * from test where not exists (select 1 from test_02 where test_02.id=test.id);

  4.在使用like语法的时候使用”a%%”而不是”%%a”
  5.查询条件上尽量不要对索引列使用函数

  select * from test where upper(name)='SUNYANG';

  6.数据类型的转换
  当查询条件存在隐式转换时，索引会失效。比如在数据库里id存的number类型，但是在查询时，却用了下面的形式

  select * from sunyang where id='123';

  7.在where后使用or，导致索引失效（尽量少用or）如果or的条件中有任何一列没有索引 则不会命中索引
  8.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

  CREATE INDEX index_name ON students(name);
  # 使用索引
  EXPLAIN select * from students where name='110'
  
  # 没有使用索引
  EXPLAIN select * from students where name=110

数据库优化

• 1.1 因为数据库要存储比较大的数据 那么再创建表的时候宽度尽可能的精确一些 以免不必要的空间浪费

• 1.2 用join代替子查询 因为子查询会再内存里面建立一张临时表，在索引的字段上查询会更快

• 1.3 使用Union来代替手动创建临时表,union是mysql4.0提出来的 它可以将集合查询合并到一个查询中客户端查询会话结束的时候临时表就会被删除

• 1.4 使用事务

  • A : (Atomic)，原子性，将所有SQL作为原子工作单元执行，要么全部执行，要么全部不执行；
  • C : (Consistent)，一致性，事务完成后，所有数据的状态都是一致的，即A账户只要减去了100，B账户则必定加上了100；
  • I : (Isolation)，隔离性，如果有多个事务并发执行，每个事务作出的修改必须与其他事务隔离；
  • D : (Duration)，持久性，即事务完成后，对数据库数据的修改被持久化存储。

• 1.5 锁定表，由于在执行事务期间，数据库将会被锁定，这在用户多的情况下会产生很严重的延迟，使用lock table锁定表，锁定表可以保证在锁表unlock table之前不会有其他访问对table进行操作

• 1.6 使用外键

  • 在锁定表的时候为了维护数据的完整性，使用外键可以达到这个效果

• 1.7 使用索引 : 索引一般建立与where join order by 的字段上，不要再有过多重复的字段上建立索引

• 1.8 创建索引

  对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引

• 1.9 复合索引

  比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
  如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。
  如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

• 2.0.索引的字段不能包含null值

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

• 2.1 使用短索引

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

• 2.2 排序的索引问题

  mysql一条语句只会使用1个索引，比如一条sql 有where 有order by 那么命中了where 那么order上的索引就失效了，所以再基本数据库排序能满足的情况下的不要使用排序，尽量不要包含多个列的排序，如果需要那么就给这些列增加复合索引。

• 2.3 like语句的操作

  一般情况下不鼓励使用like去做模糊查询 如果非使用不可 like ‘%aaa%’不会命中索引 但是like’aaa%’可以命中索引

• 2.4 不要在列上进行运算

  比如 select * from A where Year(create_time)>’2003’
  会对每一列进行运算
  可以使用 select * from A where create_time>’2001-01-01’

• 2.5 不使用not in 和 <> 操作

  这两个都不会命中索引 会进行全表操作
  not in 可以not exists代替
  id<>3则可以使用id>3 or id<3来代替