1:什么是索引?
索引是一种用于快速查询和检索数据的数据结构。mysql中的索引结构有: B+树和Hash。
索引的作用就相当于目录的作用。我们只需要先去目录里查找字的位置,然后直接翻到那一页就行了。这样查找就会非常快。
2:索引的优缺点?
优点:
1:可以大大加快 数据的检索速度(大大减少的检索的数据量), 这也是创建索引的最主要的原因。毕竟大部分系统的读请求总是大于写请求的.
2: 通过索引列对数据进行排序,降低数据排序的成本,减少cpu的消耗
缺点:
1:创建索引和维护索引需要耗费额外的时间, 所以更新表的时候会慢一点
2:占用物理存储空间 。
3.索引的分类
- 功能分类 :
- 普通索引: 最基本的索引,它没有任何限制。
- 唯一索引:索引列的值必须唯一,但允许有空值。如果是组合索引,则列值组合必须唯一。
- 主键索引:一种特殊的唯一索引,不允许有空值。一般在建表时同时创建主键索引。
- 组合索引:顾名思义,就是将单列索引进行组合。
- 外键索引:只有InnoDB引擎支持外键索引,用来保证数据的一致性、完整性和实现级联操作。
- 全文索引:快速匹配全部文档的方式。InnoDB引擎5.6版本后才支持全文索引。MEMORY引擎不支持。
- 结构分类:
- B+Tree索引 :MySQL使用最频繁的一个索引数据结构,是InnoDB和MyISAM存储引擎默认的索引类型。
- Hash索引 : MySQL中Memory存储引擎默认支持的索引类型。
4.索引失效的几种情况
失效的几种情况
- 如果查询条件中有or,即使其中有些条件创建了索引,索引也不起作用,除非你对or中每个查询列都创建了索引。(mysql中测试也是无效的)
- 模糊查询like,查询以’%xxx’开头的不使用索引,以’xxx%'结尾会使用索引
- 使用了比较运算符<>或!= 不等于的运算符,不使用索引
- mysql如果发现不使用索引更快,则不使用索引。
5.为什么索引会提高查找速度?
这首先要从mysql的存储说起: mysql的存储形式是页, 在没有使用索引时,定位到记录所在的页:需要遍历双向链表,找到所在的页
从所在的页内中查找相应的记录:由于不是根据主键查询,只能遍历所在页的单链表了,这种查找时间复杂度为O(n)
当添加索引后: 就是将无序的数据变成有序(相对),通过 “目录” 就可以很快地定位到对应的页上了!(二分查找,时间复杂度近似为O(logn))
6.使用索引的注意事项
-
经常需要搜索的列上,可以加快搜索的速度;
-
在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度;
-
在经常需要排序的列上创 建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间;
-
避免 where 子句中对宇段施加函数,这会造成无法命中索引;
-
在经常用在连接的列上,这 些列主要是一些外键,可以加快连接的速度;
-
在使用InnoDB时使用与业务无关的自增主键作为主键,即使用逻辑主键,而不要使用业务主键;
-
对于中到大型表索引都是非常有效的,但是特大型表的话维护开销会很大,不适合建索引;
-
删除长期未使用的索引。
sql优化
1、在表中建立索引,优先考虑where、group by使用到的字段。
*2、尽量避免使用select ,返回无用的字段会降低查询效率。
如下:
SELECT * FROM t
优化方式:使用具体的字段代替*,只返回使用到的字段。
**3、尽量避免使用in 和not in,会导致数据库引擎放弃索引进行全表扫描。**如下:
SELECT * FROM t WHERE id IN (2,3)
SELECT * FROM t1 WHERE username IN (SELECT username FROM t2)
优化方式:如果是连续数值,可以用between代替。如下:
SELECT * FROM t WHERE id BETWEEN 2 AND 3
如果是子查询,可以用exists代替。如下:
SELECT * FROM t1 WHERE EXISTS (SELECT * FROM t2 WHERE t1.username = t2.username)
**4、尽量避免使用or,会导致数据库引擎放弃索引进行全表扫描。**如下:
SELECT * FROM t WHERE id = 1 OR id = 3
优化方式:可以用union代替or。如下:
SELECT * FROM t WHERE id = 1
UNION
SELECT * FROM t WHERE id = 3
(PS:如果or两边的字段是同一个,如例子中这样。貌似两种方式效率差不多,即使union扫描的是索引,or扫描的是全表)
**5、尽量避免在字段开头模糊查询,会导致数据库引擎放弃索引进行全表扫描。**如下:
SELECT * FROM t WHERE username LIKE ‘%li%’
优化方式:尽量在字段后面使用模糊查询。如下:
SELECT * FROM t WHERE username LIKE ‘li%’
6、尽量避免进行null值的判断,会导致数据库引擎放弃索引进行全表扫描。如下:
SELECT * FROM t WHERE score IS NULL
优化方式:可以给字段添加默认值0,对0值进行判断。如下:
SELECT * FROM t WHERE score = 0
7、尽量避免在where条件中等号的左侧进行表达式、函数操作,会导致数据库引擎放弃索引进行全表扫描。如下:
SELECT * FROM t2 WHERE score/10 = 9
SELECT * FROM t2 WHERE SUBSTR(username,1,2) = ‘li’
优化方式:可以将表达式、函数操作移动到等号右侧。如下:
SELECT * FROM t2 WHERE score = 10*9
SELECT * FROM t2 WHERE username LIKE ‘li%’
8、当数据量大时,避免使用where 1=1的条件。通常为了方便拼装查询条件,我们会默认使用该条件,数据库引擎会放弃索引进行全表扫描。如下:
SELECT * FROM t WHERE 1=1
优化方式:用代码拼装sql时进行判断,没where加where,有where加and。
1.四大特性(ACID)
-
原子性(Atomicity): 事务是最小的执行单位,不允许分割。事务的原⼦性确保动作要么全部完成,要么完全不起作⽤;
-
一致性(Consistency): 执行事务前后,数据保持⼀致,多个事务对同⼀个数据读取的结果是相同的;
-
隔离性(Isolation): 并发访问数据库时,⼀个用户的事务不被其他事务所⼲扰,各并发事务之间数据库是独立的;
-
持久性(Durability): ⼀个事务被提交之后。它对数据库中数据的改变是持久的,即使数据库发生故障也不应该对其有任何影响。
2.并发事务带来的问题
脏读
某个事务已更新一份数据,另一个事务在此时读取了同一份数据,由于某些原因,前一个RollBack了操作,没有提交事务,则后一个事务所读取的数据就会是不正确的。
不可重复读
一个事务多次查询却返回了不同的数据值,这是由于在查询间隔,被另一个事务修改并提交了。
幻读
在一个事务的两次查询中数据笔数不一致(在查询间隔中,被另一个事务插入了数据)。
3.四种隔离级别
READ-UNCOMMITTED(读取未提交): 最低的隔离级别,允许读取尚未提交的数据变更。
READ-COMMITTED(读取已提交): 允许读取并发事务已经提交的数据。
REPEATABLE-READ(可重复读): 对同⼀字段的多次读取结果都是⼀致的,除非是事务本身修改的。
SERIALIZABLE(可串行化): 最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰。
1.常用引擎的特性对比
- 常用的存储引擎
- MyISAM存储引擎
- 访问快,不支持事务和外键。表结构保存在.frm文件中,表数据保存在.MYD文件中,索引保存在.MYI文件中。
- InnoDB存储引擎(MySQL5.5版本后默认的存储引擎)
- 支持事务 ,占用磁盘空间大 ,支持并发控制。表结构保存在.frm文件中,如果是共享表空间,数据和索引保存在 innodb_data_home_dir 和 innodb_data_file_path定义的表空间中,可以是多个文件。如果是多表空间存储,每个表的数据和索引单独保存在 .ibd 中。
- MEMORY存储引擎
- 内存存储 , 速度快 ,不安全 ,适合小量快速访问的数据。表结构保存在.frm中。
- 特性对比
特性 |
MyISAM |
InnoDB |
MEMORY |
存储限制 |
有(平台对文件系统大小的限制) |
64TB |
有(平台的内存限制) |
事务安全 |
不支持 |
支持 |
不支持 |
锁机制 |
表锁 |
表锁/行锁 |
表锁 |
B+Tree索引 |
支持 |
支持 |
支持 |
哈希索引 |
不支持 |
不支持 |
支持 |
全文索引 |
支持 |
支持 |
不支持 |
集群索引 |
不支持 |
支持 |
不支持 |
数据索引 |
不支持 |
支持 |
支持 |
数据缓存 |
不支持 |
支持 |
N/A |
索引缓存 |
支持 |
支持 |
N/A |
数据可压缩 |
支持 |
不支持 |
不支持 |
空间使用 |
低 |
高 |
N/A |
内存使用 |
低 |
高 |
中等 |
批量插入速度 |
高 |
低 |
高 |
外键 |
不支持 |
支持 |
不支持 |
2.总结:引擎的选择
- MyISAM :由于MyISAM不支持事务、不支持外键、支持全文检索和表级锁定,读写相互阻塞,读取速度快,节约资源,所以如果应用是以查询操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
- InnoDB : 是MySQL的默认存储引擎, 由于InnoDB支持事务、支持外键、行级锁定 ,支持所有辅助索引(5.5.5后不支持全文检索),高缓存,所以用于对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,读写频繁的操作,那么InnoDB存储引擎是比较合适的选择,比如BBS、计费系统、充值转账等
- MEMORY:将所有数据保存在RAM中,在需要快速定位记录和其他类似数据环境下,可以提供更快的访问。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的。MEMORY表通常用于更新不太频繁的小表,用以快速得到访问结果。
- 总结:针对不同的需求场景,来选择最适合的存储引擎即可!如果不确定、则使用数据库默认的存储引擎!
char和varchar区别
1、char 类型是固定长度的,Mysql 在程序处理的时候不需要计算长度,所以它的速度比 varchar 要快,但是其缺点是浪费存储空间,插入时如果长度不够会在尾部以空格填充,查询时 Mysql 程序会对尾部的空格进行处理,浪费性能,综合来说,如果业务需求中字段长度变化不大的情况下使用 char 类型来存储(例如 性别 字段,不是 0 就是 1,可以设计成 char(1) )
2、在使用 varchar 类型的时候不能因为其长度可变就都为 varchar 定义一个很大的长度,实际开发中仍然要根据业务需求来设计合适的长度,定义一个远超实际需求长度的字段可能会影响 Mysql 程序的性能,并且还有可能出现一些未知的 BUG,所以设计 varchar 类型的长度时,一定要根据业务需求去评估,选择一个合适的长度
-----待更新-----