那样的网站,广州档案馆建设网站,重庆有没有做网站的,搜狐员工做网站的工资多少钱目录
一、索引
1#xff09;索引定义
2#xff09;工作方式
3#xff09;创建索引的依据
4#xff09;索引类型
1、index普通索引
2、unique唯一索引
3、主键索引
4、多列组合索引
5、全文索引
5#xff09;删除索引
6#xff09;查看索引
7#xff09;索…目录
一、索引
1索引定义
2工作方式
3创建索引的依据
4索引类型
1、index普通索引
2、unique唯一索引
3、主键索引
4、多列组合索引
5、全文索引
5删除索引
6查看索引
7索引失效
二、事务
1事务的概念
2事务的ACID特性【☆】
3查看隔离级别
4设置隔离级别
5事务管理操作 三、存储引擎
1存储引擎定义
2MyISAM 和 InnoDB 的区别
3MySQL 查询数据的执行过程
4查看存储引擎
5存储引擎管理操作
6行锁、表锁、死锁 一、索引
1索引定义 索引是一个排序的列表包含索引字段的值和其对应的行记录数据所在的物理地址。保存索引需要额外的磁盘空间
作用加快表的查询速度主要作用还可以对字段排序可以降低数据库的IO成本和排序成本。
副作用会额外占用磁盘空间更新包含索引的表会花费更多的时间。 2工作方式 没有索引的情况下要查询某行记录数据时需要先扫描全表再根据条件判断定位到某行记录数据的位置有了索引后会先通过索引查询到行记录数据所在的物理地址即可直接访问相应的行记录数据就像通过书目录的页码快速查找书内容一样。
3创建索引的依据
表的记录行数较多时一般超过三五百行时且读操作多的情况下应该要创建索引建议在表的主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引。
4索引类型
1、index普通索引
普通索引 没有唯一性之类的限制
#直接创建索引长度不指定的话会默认所有字符都创建到索引
create index 索引名 on 表名(字段(长度));#修改表方式创建
alter table 表名 add index 索引名(字段); #创建表使创建索引但一般不建议
create table 表名 (.... , index 索引名(字段)); 2、unique唯一索引
唯一索引的列的值都唯一存在允许有空值组合创建索引组合的值必须唯一存在。创建唯一键时会自动创建唯一索引
#直接创建
create unique index 索引名 on 表名(字段);#修改表方式创建
alter table 表名 add unique 索引名(字段);#创建表时指定
create table 表名 (.... , unique 索引名(字段)); 3、主键索引
主键索引 primary key一个表只能有一个主键不允许有空值添加主键会自动创建主键索引可以多字段组合创建为一个索引
#修改表方式创建
alter table 表名 add primary key(字段1,[字段2]);#创建表时指定一个字段做索引
create table 表名 (字段1 数据类型 primary key字段2 数据类型... );
create table 表名 (字段1 数据类型字段2 数据类型... primary key(字段1));#创建表时指定多个字段做索引
create table 表名 (字段1 数据类型字段2 数据类型... , primary key(字段1,[字段2]);注意
create table 表名 (字段1 数据类型 primary key字段2 数据类型 primary key...);此命令表示两个字段单独创建索引是不允许的会报错 4、多列组合索引
多个字段组合在一起创建为一个索引要满足最左原则字段查询顺序要与索引设置的字段顺序保持一致才能生效。
#直接创建
create index 索引名 on 表名(字段2, 字段1, ....);#修改表方式创建
alter table 表名 add index 索引名(字段2, 字段1, ....);#查询语句
select * from 表名 where 字段2XX and 字段1XX .... ;
#查询语句使用 and 做逻辑运算符时字段顺序要与创建的多列索引的字段顺序一致要满足最左原则 5、全文索引
#直接创建
create fulltext index 索引名 on 表名(字段);#修改表方式创建
alter table 表名 add fulltext 索引名(字段);#创建表时创建全文索引
create table 表名 (.... , fulltext 索引名(字段));#全文索引查询语句
select 字段列表 from 表名 where match(字段) against(单词); 全文索引可以模糊查询关键字查询。只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引5.7.6版本之前只支持InnoDB和MyISAM引擎。全文索引只支持英文全文索引不支持中文全文索引。需要使用ngram全文解析器用来支持中文、日文、韩文全文索引。
#修改配置文件
vim /etc/my.cnf
[mysqld]
ngram_token_size2 #指定查询的单词的最小字数#重启mysql
systemctl restart mysql ① 创建全文索引
create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);
② 全文检索模式
#默认使用自然语言模式不能使用操作符指定关键词必须出现或-不能出现等复杂查询
select 字段列表 from 表名 where match(字段) against(单词); #BOOLEAN模式可以使用操作符可以指定关键词必须出现或者-不能出现等复杂查询
select 字段列表 from 表名 where match(字段) against(单词1 -单词2 IN BOOLEAN MODE);
#使用BOOLEAN模式必须包含单词1且不能包含单词2select 字段列表 from 表名 where match(字段) against(单词1 单词2 IN BOOLEAN MODE);
#使用BOOLEAN模式必须同时包含单词1和单词2select 字段列表 from 表名 where match(字段) against(单词1 单词2 IN BOOLEAN MODE);
#使用BOOLEAN模式要么包含单词1要么包含单词2 5删除索引
#这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引
drop index 索引名 on 表名;
alter table 表名 drop index 索引名; #删除主键索引
alter table 表名 drop primary key; 6查看索引
show create table 表名; #查看表结构查看索引信息
show index from 表名;
show keys from 表名; 遇到 select 查询语句执行速度慢该怎么办 1升级 CPU 内存 硬盘 硬件性能 2对 MySQL 配置进行优化 max_connections的值需要比默认的151连接数更大的值innodb_buffer_pool_size设置缓冲池它是数据和索引缓存的地方这个值越大越好这能保证你在大多数的读取操作时使用的是内存而不是硬盘。典型的值是5-6GB(8GB内存)20-25GB(32GB内存)100-120GB(128GB内存)。innodb_log_file_size redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6你可以一开始就把它这是成4G。 3对查询语句的结构进行优化比如将嵌套子查询优化成表连接查询或连接表时可以先用where条件对表进行过滤然后做表连接 4进行索引优化先使用 explain 分析 select 语句看 key rows type 字段判断这个查询语句是否正确的使用了索引再根据查询语句中的 where 使用的条件字段建立相应的单列索引或者多列组合索引多列组合索引要满足最左原则 7索引失效 我们创建了索引但不代表查询时使用索引都能失效这取决于查询语句是否会导致索引失效做了全表扫描。那么哪些情况应该避免索引失效呢
避免在where条件中对字段进行null判断如select * from 表名 where 字段 is null避免在where条件中使用 ! 或 如select * from 表名 where 字段 ! 值避免在where条件中对表达式进行操作如select * from 表名 where age*2 30; 可以写为select * from 表名 where age 30/2;避免在where条件中对字段进行函数操作避免在like查询中将%放在开头如select * from 表名 where 字段 like %值; 二、事务
1事务的概念 事务的定义事务就是一组数据库操作序列包含一个或多个SQL操作命令事务会把所有操作看成一个不可分割的整体向数据库系统提交或撤销所有操作所有操作要么都执行要么都不执行。事务是最小的控制单元适用于多用户同时操作的数据库系统的场景如银行、保险公司及证券交易系统等等通过事务的整体性以保证数据的一致性。 一个可靠的数据库具备ACID特性在事务的管理中原子性是基础隔离性是手段一致性是目的持久性是结果。
2事务的ACID特性【☆】
原子性Atomicity事务中的所有操作看做是一个不可分割的工作单元要么都执行要么都不执行。案例A给B转账100元的时候只执行了扣款语句就提交了此时如果突然断电A账号已经发生了扣款B账号却没收到价款在生活中就会引起纠纷这种情况就需要事务的原子性来保证一致性Consistency保证事务开始前和事务结束后数据的完整和一致。案例对银行转帐事务不管事务成功还是失败应该保证事务结束后表中和B的存款总额跟事务执行前一致。隔离性Isolation多个事务并发操作同一个表数据时每个事务都有各自独立的数据空间一个事务的执行不会受到其他事务的干扰。可通过设置隔离级别来解决不同的一致性问题。持久性Durability事务管理的结果。当事务被提交以后事务中的命令操作修改的结果会被持久化保存且不会被回滚。
隔离的运行级别未提交读read uncommitted会出现脏读、不可重复读、幻读最低级别的隔离提交读read committed不会出现脏读会出现不可重复读、幻读 生产环境中大量 使用的隔离级别 可重复读repeatable read不会出现脏读、不可重复读有条件的允许幻读InnoDB存储引擎可以通过多版本并发控制MVCC解决幻读问题用的也还挺多性能会受到影响且会出现死锁问题串行读serializable脏读、不可重复读、幻读的问题都不会出现相当于标记锁定不能并发处理事务会影响数据库的读写效率性能实际应用中比较少用不可重复读打个比方事务1开启后先查询表1的数据为data1事务2开启后对表1进行了改操作表1的数据为data2。在事务2操作后,事务1再次查看表1数据却显示不是data1了或者是在事务2提交后查看到表1的数据不是data1。简单说就是在一个事务中即使对表数据没有任何操作数据依旧发生改变的情况就叫不可重复读。
一致性问题脏读比如A在银行atm机上存1000元现金还未提交但此时A的女朋友登录app查看到账户有1000元不可重复读 指在一个事务内多次读同一数据。在这个事务还没有结束时另外一个事务也访问该同一数据。那么在第一个事务中的两次读数据之间由于第二个事务的修改那么第一个事务两次读到的的数据可能是不一样的。 比如A查询账户余额为0时B此时存入现金1000元把余额改为了1000并不知情的A再刷新余额一看突然变成了1000元。 幻读A对表的全部数据进行了统一的修改此时B插入了一条数据在A的视角会疑惑怎么还有一条数据没有修改呢像出现幻觉一样。丢失更新两个事务同时读取同一条记录A先修改记录B也修改记录B不知道A修改过B提交数据后B的修改结果覆盖了A的修改结果
3查看隔离级别
#%百分号表示任意长度的任意字符like模糊匹配此方式会做全表查询
show global variables like %isolation%;
show session variables like %isolation%; 4设置隔离级别
#全局级隔离级别可在所有会话有效当前会话需要重新登录方可有效
set global transaction isolation level 隔离级别名称; #会话级隔离仅在当前会话中立即有效
set session transaction isolation level 隔离级别名称; 5事务管理操作
begin; #显式的开启一个事务
insert into | update |delete from #事务性操作
savepoint XX; #在事务中创建回滚点
rollback to XX; #在事务中回滚到指定的回滚点位置
commit; #提交结束事务永久保存所有操作
rollback; #或回滚结束事务即撤销或rollback to 回滚点 自动提交事务 mysql默认自动提交事务是开启的操作后自动commit保存我们对表数据的修改。如果关闭此功能每次修改数据后都需要手动commit;提交才能实现保存效果。 show global/session variables like autocommit; #查看自动提交是否开启 set global/session autocommit 0/1 #global全局级别session会话级别0关闭自动提交1开启自动提交 三、存储引擎
1存储引擎定义 存储引擎是MySQL数据库的组件负责执行实际的数据IO操作数据的存储和提取工作在文件系统之上数据库的数据会先传输到存储引擎再按照存储引擎的存储格式保存到文件系统。MySQL常用的存储引擎MyISAM、InnoDB等。 2MyISAM 和 InnoDB 的区别
MyISAM
不支持事务、外键约束支持全文索引只支持表级锁定适合单独的查询和插入的操作读写会相互阻塞硬件资源占用较小数据文件和索引文件是分开存储的存储成三个文件表结构文件.frm、数据文件.MYD、索引文件.MYI使用场景适用于不需要事务支持单独的查询或插入数据的业务场景
InnoDB
支持事务、外键约束也支持全文索引支持行级锁定但在全表扫描时仍会表级锁定读写并发能力较好缓存能力较好可以减少磁盘IO的压力数据文件也是索引文件存储成表结构文件.frm、表空间文件.ibd使用场景适用于需要事务支持数据一致性要求较高数据会频繁更新读写并发高的业务场景
3MySQL 查询数据的执行过程 客户端向 MySQL 服务器发送一条查询请求连接器负责处理连接并进行身份验证和权限控制。MySQL 先检查查询缓存如果命中缓存则立刻返回存储在缓存中的结果否则使用查询解析器进行SQL语句解析、预处理再由优化器生成对应的执行计划。MySQL 根据执行计划调用存储引擎来执行查询。将结果返回给客户端同时缓存查询结果。
4查看存储引擎
show engines; show create table 表名; show table status [from 库名] where name 表名; 5存储引擎管理操作
#针对已存在的表修改存储引擎
alter table 表名 engineinnodb/myisam; #新建表时指定存储引擎
create table 表名 (....) engineinnodb/myisam; #设置默认存储引擎
set global/session default_storage_engineinnodb/myisam; #修改配置文件修改默认存储引擎
vim /etc/my.cnf
default_storage_engineINnoDB/MyISAM
#重启mysql生效
systemctl restart mysqld 6行锁、表锁、死锁 行锁
表锁 结论InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。 死锁 两个或两个以上的事务在执行过程中因争夺锁资源而造成的一种互相等待的现象若无外力作用事务都将无法继续运行。此时称系统处于死锁状态或系统产生了死锁。比如事务1根据索引删除了表A的数据1事务2根据索引删除了表A的数据2都未提交事务。此时的数据1和数据2的行内容都各自触发了行锁。然后事务1请求操作数据2因为行锁无法执行一直挂着事务2再来请求操作数据1互相访问了各自锁定的数据行记录就会触发死锁。 如何避免死锁 1设置事务的锁等待超时时间 innodb_lock_wait_timeout show VARIABLES like innodb_lock_wait_timeout; #查看当前系统是否设置锁等待超时时间 2设置开启死锁检测功能 innodb_deadlock_detect show VARIABLES like innodb_deadlock_detect; #查看当前死锁检测是否开启 set global innodb_deadlock_detect ON; #设置ON为开启死锁检测OFF为关闭 3为表建立合理的索引减少表锁发生的概率 4如果业务允许可以降低隔离级别比如选用 提交读 Read Committed 隔离级别从而避免间隙锁导致死锁 5建议开发人员尽量使用更合理的业务逻辑比如多表操作时以固定顺序访问表尽量避免同时锁定多个资源 6建议开发人员尽量保持事务简短减少对资源的占用时间和占用范围 7建议开发人员在读多写少的场景下采用乐观锁机制 补充 间隙锁事务操作过程中操作语句条件中根据索引字段删选操作的范围内的数据记录都会被上锁范围之外的不会上锁。 乐观锁在操作数据时不会上锁认为别人不会同时修改数据只会在执行更新的时候判断一下在此期间别人是否修改了数据如果别人修改了数据则放弃操作否则执行操作。适用于读多写少的场景。 悲观锁操作数据时直接把数据锁住直到操作完成后才会释放锁上锁期间其他人不能修改数据。一般适用于写多的场景系统默认使用悲观锁。
