mysql数据库索引

mysql数据库索引

Mysql索引详解
1.索引
一. 什么是索引：
索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息。
2.作用是什么？
索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容，提高性能（查询速度） 打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
3.索引和普通查询的区别?
比如，查询一个学校所有学生的成绩，将不及格的学生姓名和成绩显示下来。
普通查询方法： select name,[成绩字段] from [表名] where [成绩字段]<60;
查询的过程： 数据库将所有学生的成绩全部查询一遍，将不及格的学生显示出来。
数据量小看不出区别，如果数据量达到百万，需要将所有学成全部查出，耗时非常大。
索引查询： 首先建立一个索引，create index 索引名 on 表名（成绩字段）;
如果数据多，创建索引时会消耗大量时间，和内存。
再次查询select name,（成绩字段） from （表名） where 成绩字段<60;
查询过程： 索引将表中的数据排序，查询时列表有成绩10，30，60，70，100。
查询时,查到60的时候，将不再向后查询。节省时间。
4.索引是如何排序的？1.索引的本质是排序：有待研究，和数据结构有关系

5.索引的优点和缺点：
有了索引.对于记录数量很多的表,可以提高查询速度.
缺点 索引是占用空间的. 索引会影响update insert delete速度
6.什么样的字段不适合创建索引：
1.数据量较少
2.定义的text，image，bit数据类型的列不应该增加索引。
3.当修改性能远远大于检索性能时，不应该创建索 引
。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
4、唯一性太差的字段不适合建立索引什么是唯一性太差的字段。
如状态字段、类型字段。那些只存储固定几个值的字段，例如用户登录状态、消息的status等。这个涉及到了索引扫描的特性。例如：通过索引查找键值为A和B的某些数据，通过A找到某条相符合的数据，这条数据在X页上面，然后继续扫描，又发现符合A的数据出现在了Y页上面，那么存储引擎就会丢弃X页面的数据，然后存储Y页面上的数据，一直到查找完所有对应A的数据，然后查找B字段，发现X页面上面又有对应B字段的数据，那么他就会再次扫描X页面，等于X页面就会被扫描2次甚至多次。以此类推，所以同一个数据页可能会被多次重复的读取，丢弃，在读取，这无疑给存储引擎极大地增加了IO的负担。
7.什么时候适合创建索引：
1、较频繁地作为查询条件的字段这个都知道。
什么是叫频繁呢？分析你执行的所有SQL语句。最好将他们一个个都列出来。然后分析，发现其中有些字段在大部分的SQL语句查询时候都会用到，那么就果断为他建立索引。
2、更新太频繁地字段不适合创建索引
当你为这个字段创建索引时候，当你再次更新这个字段数据时，数据库会自动更新他的索引，所以当这个字段更新太频繁地时候那么就是不断的更新索引，性能的影响可想而知。大概被检索几十次会更新一次的字段才比较符合建立索引的规范。而如果一个字段同一个时间段内被更新多次，那么果断不能为他建立索引。
7.创建索引注意事项：
1.索引要创建在where和join用到的字段上.
2.以下操作符号可以应用索引：<，<=，=，>，>=，BETWEEN，IN，LIKE不以%_开头
以下需要注意不使用索引<>, NOT IN, LIKE %_开头<> 可以用 a>1 or a<3 代替,NOT IN 可以用NOT exists代替
3.在使用max() min()时最好加上索引.
4.单索引要创建在确实需要的地方.多列索引 有最佳左前缀特性 所以尽量左边的字段是最常用的.
6.索引不会包括有NULL值,就是有NULL,索引就失效
7.使用短索引,一个字段字太多,可以建立部分索引,只取前十个字.节约空间.
8.不要在列上运算.例如:where MD5(“password”) = “myz”
9.explain select * from myz 来测试一下语句的效率…输入 explain select * from customer; 后，出现一张表，个行的意思如下：table－显示此行数据属于哪张表；type－重要的一列，显示使用了何种连接，从好到差依次为const、eq_ref、ref、 range、index、all；possible_keys－可以应用在这张表中的索引，如果为null，则表示没有可用索引；key－实际使用的索 引，如为null，表示没有用到索引；key_len－索引的长度，在不损失精确度的情况下，越短越好；ref－显示索引的哪一列被使用了，如果可能的 话，是个常数；rows－返回请求数据的行数；extra－关于mysql如何解析查询的额外信息，下面会详细说明。
type的描述：system－表只有一行，这是const连接类型的特例；const－表中一个记录的最大值能够匹配这个查询(索引可以是主 键或唯一索引)。因为只有一行，这个值实际就是常数，因为mysql先读这个值，再把它当作常数对待；eq_ref－从前面的表中，对每一个记录的联合都 从表中读取一个记录。在查询使用索引为主键或唯一索引的全部时使用；ref－只有使用了不是主键或唯一索引的部分时发生。对于前面表的每一行联合，全部记 录都将从表中读出，这个连接类型严重依赖索引匹配记录的多少－越少越好；range－使用索引返回一个范围中的行，比如使用>或<查找时发 生；index－这个连接类型对前面的表中的每一个记录联合进行完全扫描(比all好，因为索引一般小于表数据)；all－这个连接类型多前面的表中的每 一个记录联合进行完全扫描，这个比较糟糕，应该尽量避免。
extra行的描述：distinct－mysql找到了域行联合匹配的行，就不再搜索了；not exists－mysql优化了left join，一旦找到了匹配left join的行，就不再搜索了；range checked for each－没找到理想的索引，一次对于从前面表中来的每一个行组合；record(index map: #)－检查使用哪个索引，并用它从表中返回行，这是使用索引最慢的一种；using filesort－看到这个就需要优化查询了，mysql需要额外的步骤来发现如何对返回的行排序。他根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。using index－列数据是从单单使用了索引中的信息而没有读取实际行的表返回的，这发生在对表的全部的请求列都是同一个索引时；using temporary－看到这个就需要优化查询了，mysql需要创建一个临时表来查询存储结果，这通常发生在多不同的列表进行order by时，而不是group by；where used－使用了where从句来限制哪些行将与下一张表匹配或是返回给用户。如不想返回表中用的全部行，并连接类型是all或index，这就会发生，也可能是查询有问题。
10查看索引信息show index from tablename;
8.索引的类型及创建例子:
1.PRIMARY KEY （主键索引）
mysql> alter table table_name add primary key ( column )
2.UNIQUE 或 UNIQUE KEY (唯一索引)
mysql> alter table table_name add unique (column)
3.FULLTEXT (全文索引)
mysql> alter table table_name add fulltext (column )
4.INDEX (普通索引)
mysql> alter table table_name add index index_name ( column )
5.多列索引 (聚簇索引)
mysql> alter table table_name add index index_name ( column1, column2, column3 )