创建唯一索引
CREATE UNIQUE INDEX uniq_name ON jrs_sys_role
(name
);
ALTER TABLE jrs_sys_role
ADD UNIQUE uniq_name(name
)
多个字段做唯一索引
ALTER TABLE jrs_sys_user_org
ADD UNIQUE uniq_user_org(user_name
,org_id
);
删除索引
drop index index_name on table_name ;
alter table table_name drop index index_name ;
alter table table_name drop primary key ;
1 索引前缀
2 全表 不如索引
3 频繁修改字段
4 like 前边生效 后边不生效
5 varchar不 使用’’ ‘’ 失效
6 非法字符
> < 是怎么样走索引的
联合索引 name age sex
name=‘qwe’ and age= 20 and sex = ‘nan’
name=‘qwe’ and age> 20 and sex = ‘nan’
主键索引 primary key
好处:innnodb 一个表只有一个主键索引,且不能为空。保证数据唯一性。
最好使用自增序列。
自动编号,速度快,增量增长,按顺序存放,对于检索非常有利;
数字型,占用空间小,易排序,在程序中传递也方便。
如果表使用自增主键,那么每次插入新的记录,记录就会顺序添加到当前索引节点的后续位置,当一页写满,就会自动开辟一个新的页。如下图所示:
这样就会形成一个紧凑的索引结构,近似顺序填满。由于每次插入时也不需要移动已有数据,因此效率很高,也不会增加很多开销在维护索引上。
如果使用非自增主键(如果身份证号或学号等),由于每次插入主键的值近似于随机,因此每次新纪录都要被插到现有索引页得中间某个位置:
此时MySQL不得不为了将新记录插到合适位置而移动数据,甚至目标页面可能已经被回写到磁盘上而从缓存中清掉,此时又要从磁盘上读回来,这增加了很多开销,同时频繁的移动、分页操作造成了大量的碎片,得到了不够紧凑的索引结构,后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
自增主键什么时候会有坏处?
对于高并发工作负载,在InnoDB中按主键顺序插入可能会造成明显的争用。主键上界会成为”热点”,因为所有的插入都发生在这里,所以并发插入可能导致间隙锁竞争。另一个热点可能是AUTO_INCREMENT锁机制:如果遇到这个问题,则可能需要考虑重新设计表或者应用,或者更改innodb_autoinc_lock_mode配置。
自增长在数据库中是非常常见的一种属性,也是很多DBA或开发人员首选的主键方式。在InnoDB存储引擎的内存结构中,对每个含有自增长值的表都有一个自增长计数器。当对含有自增长的计数器的表进行插入操作时,这个计数器会被初始化,执行如下的语句来得到计数器的值:
select max(auto_inc_col) from t for update;
插入操作会依据这个自增长的计数器值加1赋予自增长列。这个实现方式称为AUTO-INC Locking。这种锁其实是采用一种特殊的表锁机制,为了提高插入的性能,锁不是在一个事务完成后才释放,而是在完成对自增长值插入的SQL语句后立即释放。
虽然AUTO-INC Locking从一定程度上提高了并发插入的效率,但还是存在一些性能上的问题。首先,对于有自增长值的列的并发插入性能较差,事务必须等待前一个插入的完成,虽然不用等待事务的完成。其次,对于INSERT….SELECT的大数据的插入会影响插入的性能,因为另一个事务中的插入会被阻塞。
从MySQL 5.1.22版本开始,InnoDB存储引擎中提供了一种轻量级互斥量的自增长实现机制,这种机制大大提高了自增长值插入的性能。并且从该版本开始,InnoDB存储引擎提供了一个参数innodb_autoinc_lock_mode来控制自增长的模式,该参数的默认值为1
接下来分析参数innodb_autoinc_lock_mode以及各个设置下对自增长的影响,其总共有三个有效值可供设定,即0、1、2,具体说明如下:
0:这是MySQL 5.1.22版本之前自增长的实现方式,即通过表锁的AUTO-INC Locking方式,因为有了新的自增长实现方式,0这个选项不应该是新版用户的首选了。
1:这是该参数的默认值,对于”simple inserts”,该值会用互斥量(mutex)去对内存中的计数器进行累加的操作。对于”bulk inserts”,还是使用传统表锁的AUTO-INC Locking方式。在这种配置下,如果不考虑回滚操作,对于自增值列的增长还是连续的。并且在这种方式下,statement-based方式的replication还是能很好地工作。需要注意的是,如果已经使用AUTO-INC Locing方式去产生自增长的值,而这时需要再进行”simple inserts”的操作时,还是需要等待AUTO-INC Locking的释放。
2:在这个模式下,对于所有”INSERT-LIKE”自增长值的产生都是通过互斥量,而不是AUTO-INC Locking的方式。显然,这是性能最高的方式。然而,这会带来一定的问题,因为并发插入的存在,在每次插入时,自增长的值可能不是连续的。此外,最重要的是,基于Statement-Base Replication会出现问题。因此,使用这个模式,任何时候都应该使用row-base replication。这样才能保证最大的并发性能及replication主从数据的一致。
注:最好使用主键Id 来进行自增,主键id查询,通过主键id 修改这条数据。