单列索引和联合索引
单列索引是指在表的某一列上创建索引,联合索引是在多个列上联合创建索引。单列索引可以出现在where条件的任何位置,而联合索引需要按照一定的顺序来写。在多条件查询的时候,联合索引的效率更高,我们联合索引也最多创建两列。
我们创建索引的时候也得考虑到我们这张表的更新频率,如果表里索引比较多的话是比较影响更新速度的,因为创建索引的过程其实就是构建一个二叉树,而每次更新完数据都得重新计算二叉树,所以就影响更新速度。
索引并不是时时都会生效的,比如以下几种情况就能导致索引失效:
- 如果条件中有or,即使其中有条件带索引也不会使用,这也是为什么尽量少用or的原因,如果要想使用or,又想让索引生效,只能将or条件中的每个列都加上索引
- like查询是以%开头,会导致索引失效
- 如果列类型是字符串,那一定要在条件中将数据使用引号引用起来,否则索引失效
- 如果mysql估计使用全表扫描要比使用索引快,则不使用索引
所以呢,创建索引的话,也不是随便创建的,接下来就是常用的创建索引的原则
创建索引的原则
- 经常需要搜索的列上建立索引,可以加快搜索的速度。
- 在作为主键的列上创建索引,强制该列的唯一性,并组织表中数据的排列结构。
- 在经常使用表连接的列上创建索引,这些列主要是一些外键,可以加快表连接的速 度。
- 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序,所以其指定的 范围是连续的。
- 在经常需要排序的列上创建索引,因为索引已经排序,所以查询时可以利用索引的 排序,加快排序查询。
- 在经常使用 WHERE 语句的列上创建索引,加快条件的判断速度。
使用索引查询的优缺点
使用索引优点第一:可以保证数据库表中每一行的数据的唯一性,第二:可以大大加快数据的索引速度,在使用分组和排序语句进行数据检索时,同样可以显著减少查询中分组和排序的时间;
缺点:创建索引和维护索引要耗费时间,这种时间随着数据量的增加而增加
MySQL存储引擎都有哪些?区别?
MyISAM、InnoDB、BDB、MEMORY等,对于 MySQL 5.5 及更高版本,默认的存储引擎是 InnoDB。在 5.5 版本之前,MySQL 的默认存储引擎是 MyISAM
- InnoDB 存储引擎:
- 支持自增长列(auto_increment),自增长列的值不能为空,如果在使用的时候为空的话就会从现有的最大值自动+1,如果有但是比现在的还大,则就保存这个值。
- 支持外键(foreign key),外键所在的表称为子表而所依赖的表称为父表。
- 支持事务,回滚以及系统崩溃的修复能力,并且支持多版本并发控制的事务安全。
- 支持mvcc(多版本并发控制)的行级锁,就是通过多版本控制来实现的乐观锁
- 索引使用的是B+Tree
优缺点:InnoDB的优势在于提供了良好的事务处理、崩溃修复能力和并发控制。缺点是读写效率较差,占用的数据空间相对较大。
- MyISAM 存储引擎
不支持事务、支持表级锁
支持全文搜索
缓冲池只缓存索引文件,
不缓存数据文件 MyISAM 存储引擎表由 数据文件(MYD)和索引文件( MYI)组成
左连接、右连接、内连接的区别
内连接的话,就是两表关联的数据才能查出来,关联不到的就查询不到。左连接就是以左表为主,左表数据全查,右表数据没有就显示null,右连接相反