mysql之索引说明

1.普通索引及联合索引

访问过程

采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。

命名规则

表名_字段名

使用规则

1.需要加索引的字段，在where条件中；
2.数量少的字段不需要加索引；
3.如果where条件中为or关系，加索引不起作用；
4.符合最左原则：对于复合索引，应将最常用作限制条件的列放在最左边，依次递减；

注：mysql查询只能使用一个索引，若where子句中已经使用了索引的话，那order by中的列是不使用索引的。

使用条件

1.表的主关键字：自动建立唯一索引；
2.表的字段唯一属性：ORACLE利用索引来保证数据的完整性；
3.直接条件查询的字段：sql中用于条件约束的字段；
4.查询中与其他表关联的字段：字段外键关系；
5.查询中排序的字段：提高排序速度；
6.查询中统计或分组统计的字段。

不宜使用

1.表记录太少；
2.经常插入、修改、删除的表；
3.数据重复且分布平均的表字段；
4.经常和主字段一块查询但主字段索引值比较多的表字段。

注意事项

1.创建索引时，若字段名对应值太少，可不建索引，例如：性别；
2.索引不会包含有null值的列：复合索引中如有一个为null，则该索引无效；
3.使用短索引：对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作；
4.like语句操作：不鼓励使用，若必用：like “%aaa%” 不会使用索引而like “aaa%”可以使用索引；
5.列上不进行运算；
6.不适应not in 和操作：NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替。

2.唯一索引

定义

如果是组合索引，则列值的组合必须唯一。

与primary key 的区别

1.primary key = unique+not null
2.唯一性约束所在的列允许空值，但是主键约束所在的列不允许空值。
3.可以把唯一性约束放在一个或者多个列上，这些列或列的组合必须有唯一的。但是，唯一性约束所在的列并不是表的主键列。
4. 唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下，创建唯一性的非聚簇索引，但是，也可以指定所创建的索引是聚簇索引。
5.建立主键的目的是让外键来引用。
6.一个表最多只有一个主键，但可以有很多唯一键。

存在唯一键冲突时的避免策略

insert ignore

insert ignore会忽略数据库中已经存在的数据(根据主键或者唯一索引判断)，如果数据库没有数据，就插入新的数据，如果有数据的话就跳过这条数据。

注：主键自动增长。

replace into

1.replace into 首先尝试插入数据到表中。 如果发现表中已经有此行数据(根据主键或者唯一索引判断)则先删除此行数据，然后插入新的数据，否则，直接插入新数据。
2.使用replace into，你必须具有delete和insert权限。

insert on duplicate key update

1.如果在insert into 语句末尾指定了on duplicate key update，并且插入行后会导致在一个UNIQUE索引或PRIMARY KEY中出现重复值，则在出现重复值的行执行UPDATE；如果不会导致重复的问题，则插入新行，跟普通的insert into一样。
2.使用insert into，你必须具有insert和update权限。
3.如果有新记录被插入，则受影响行的值显示1；如果原有的记录被更新，则受影响行的值显示2；如果记录被更新前后值是一样的，则受影响行数的值显示0。

注：主键自动增长

死锁

产生

insert … on duplicate key 在执行时，innodb引擎会先判断插入的行是否产生重复key错误，
如果存在，在对该现有的行加上S(共享锁)锁，如果返回该行数据给mysql,然后mysql执行完duplicate后>的update操作，然后对该记录加上X(排他锁)，最后进行update写入。如果有两个事务并发的执行同样>>的语句，那么就会产生death lock。

解决方法

1、尽量对存在多个唯一键的table使用该语句；
2、在有可能有并发事务执行的insert 的内容一样情况下不使用该语句。

结论

1.这三种方法都能避免主键或者唯一索引重复导致的插入失败问题。
2.insert ignore能忽略重复数据，只插入不重复的数据。
3.replace into和insert … on duplicate key update，都是替换原有的重复数据，区别在于replace into是删除原有的行后，在插入新行，如有自增id，这个会造成自增id的改变；insert … on duplicate key update在遇到重复行时，会直接更新原有的行，具体更新哪些字段怎么更新，取决于update后的语句。