sql执行过程
- 将from后面的数据库表文件加载到内存
- 如果有where,则对表进行过滤,取出符合条件的数据并生成一张临时表,(where是对从数据库表文件加载到内存中的原生数据过滤)
- 若果有group by,则将表按条件进行分隔,分隔成若干个临时表
- select执行读取时,按有无group by两种情况:
a) 没有group by,select会根据后面的字段名称,对内存中的临时表整列读取
b) 有group by,会对内存中的若干临时表分别执行select,而且只取各临时表的第一条数据,然后形成新的临时表
索引
索引种类
- 主键索引:是一种特殊的唯一索引,不允许有空值。
- 唯一索引:与"普通索引"类似,不同的就是:索引列的值必须唯一,但允许有空值。
- 普通索引:最基本的索引,没有任何限制。
- 全文索引:仅可用于 MyISAM 表, 用于在一篇文章中,检索文本信息的, 针对较大的数据,生成全文索引很耗时好空间。
- 组合索引:为了更多的提高mysql效率可建立组合索引,遵循”最左前缀“原则。
触发索引
- WHERE 和 JOIN子句中的 < , <= , = , > , >= ,BETWEEN , IN , 非 % 或 _ 开头的LIKE 都会触发索引
索引的缺点
- 虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行INSERT、UPDATE和DELETE。因为更新表时,MySQL不仅要保存数据,还要保存一索引文件。
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重,但如果你在一个大表上创建了多种组合索引,索引文件会得膨胀很快。
mysql的优化
- 使用索引
- limit分页
- 判断是否存在使用:select 1 from xxx where xx=xx limit 0, 1
- 使用执行计划:
- explain + 查询SQL - 用于显示SQL执行信息参数,根据参考信息可以进行SQL优化
- 选取最适用的字段属性
- 尽量将字段设置为NOT NULL,这样在查询的时候,数据库就不用比较NULL值
- 字段的长度要适宜
- 对于某些字段,如“省份”或者“性别”,我们可以将它们定义为ENUM类型。因为在MySQL中,ENUM类型被当作数值型数据来处理,而数值型数据被处理起来的速度要比文本类型快得多。这样,我们又可以提高数据库的性能。
- 使用联合查询来代替子查询
- 执行子查询时,MYSQL需要创建临时表,查询完毕后再删除这些临时表,所以,子查询的速度会受到一定的影响,这里多了一个创建和销毁临时表的过程
锁
- 乐观锁
- 最后一步更新数据的时候在进行加锁,
乐观锁的实现方式一般为每一条数据加一个版本号。 - update其实在不在事物中都无所谓,在内部是这样的:update是单线程的,及如果一个线程对一条数据进行update操作,会获得锁,其他线程如果要对同一条数据操作会阻塞,直到这个线程update成功后释放锁。
- 另外,乐观锁不需要数据库底层的支持。
- 最后一步更新数据的时候在进行加锁,
- 悲观锁
- 在整个数据处理过程中,将数据处于锁定状态
- 首先需要set autocommit=0,即不允许自动提交
- for update仅适用于InnoDB,且必须在事务块(BEGIN/COMMIT)中才能生效。在进行事务操作时,通过“for update”语句,MySQL会对查询结果集中每行数据都添加排他锁,其他线程对该记录的更新与删除操作都会阻塞,select for update获取的行锁会在当前事务结束时自动释放。排他锁包含行锁、表锁。