数据库
2020-07-29 22:16:05 0 举报AI智能生成
Mysql和Mysql优化
作者其他创作
大纲/内容
Mysql
查询
连接查询
左连接
以左表为基准进行查询，左表数据全部显示，右表如果和左表匹配，则显示相应字段的数据，如果不匹配，则显示为NULL
右连接
以右表为基准进行查询，右表数据全部显示，左表如果和右表匹配，则显示相应字段的数据，如果不匹配，则显示为NULL
全连接
全连接就是先以左表进行左外连接，然后以右表进行右外连接
内连接
显示表之间有连接匹配的所有行
聚合查询
是对一组值执行计算并返回单一的值的函数，经常与select语句的groupBy子句一同使用
语句执行顺序
from-->where-->group by-->having-->计算所有的表达式-->order by-->select 输出
sql注入问题
通过sql语句，实现无账号登录，甚至篡改数据库
如何防止？
使用预编译语句（在sql语句中，变量用？表示）
使用mybatis时mapper中#方式能够很大程度防止sql注入，$方式无法防止sql注入
存储引擎
数据库管理系统使用数据引擎进行创建、查询、更新和删除数据
主要有
InnoDB
底层数据结构为B+树，B树的每个节点对应innodb的一个page，page大小是固定的，一般设为16k，其中非叶子节点只有键值，叶子节点包含完成数据
适用场景
经常更新的表，适合处理多重并发的更新请求
支持事务
可以从灾难中恢复（通过bin-log日志等）
外键约束，只有InnoDNB支持外键
支持自动增加列属性auto-increment
MyIASM
不支持行级锁和外键，当进行修改操作时需要锁定整个表
读取速度很快，而且不占用大量的内存和存储资源
是一种静态索引结构，缺点是它不支持事务处理
三大范式
第一范式（列的原子性）
如果每列都是不可再分的最小数据单元，则满足第一范式
第二范式（非主键列必须完全依赖主键）
第三范式（实体中的属性不能是其他实体中的非主属性）
事务
ACID（四大特性）
原子性
事务是一个完整的操作，各步操作是不可分的
一致性
当事务完成时，数据必须处于一致状态
隔离性
不应以任何方式依赖于或影响其他事务
永久性
对数据库的修改被事务日志永久保存
Mysql事务处理
BEGIN、ROLLBACK、COMMIT
BEGIN开始一个事务
ROLLBACK事务回滚
COMMIT事务确认
SET 改变Mysql的自动提交模式
SET AUTOCOMMIT=0 禁止自动提交
SET AUTOCOMMIT=1 开启自动提交
四种隔离级别
Read uncommitted
读未提交，一个事务可以读取另一个未提交事务的数据
Read committed
读已提交，一个事务要等另一个事务提交后才能读取数据
Repeatable read（Mysql默认隔离级别）
可重复读，开始读取数据（事务开启）时，不再允许修改操作
Serializable
序列化，最高的事务隔离级别，事务串行化顺序执行。效率低下，比较消耗数据库性能，一般不使用
触发器
当对一个表进行操作时触发，系统自动调用执行该表上对应的触发器
并发控制
乐观锁（写少）
时间戳、版本号、CAS
悲观锁（写多）
数据库锁
行级锁
速度慢，冲突少
表级锁
速度快，冲突多
页级锁
介于行级锁表级锁之间
分区分表
垂直切分
将表按照功能模块、关系密切程度划分
水平切分
索引
索引（index）是帮助Mysql高效获取数据的数据结构类似于书籍的索引。
索引类型
普通索引（index）
创建方式
1.直接创建
create index index_name on table（column（length））
2.修改表结构
alter table table_name add index index_name on （column（length））
3.创建表的同时创建索引
index index_name（title（length））
删除索引
drop index index_name on table
特点：没有任何限制
唯一索引（unique）
索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一
创建方式
1.直接创建
create unique index index_name on table(column(length))
2.修改表结构
alter table table_name add unique index index_name on(column(length))
3.创建表的同时创建索引
unique index index_name （title（length））
主键索引（primary key）
是一种特殊的唯一索引，一个表只能由一个主键，不允许有空值。一般在建表的同时创建主键索引
primary key（‘id’）
组合索引（index）
指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用，使用组合索引时遵循最左前缀集合
alter table table_name add index name_city_age(name,city,age)
全文索引（fulltext）
主要用来查找文本中的关键字，而不是直接与索引中的值相比较
与其它索引大不相同，更像一个搜索引擎，而不是简单的where语句的参数匹配
配合match against使用
目前只有char、varchar、text列上可以创建全文索引
在数据量较大时，先将数据放入一个没有全局索引的表中，然后再用create index创建fulltext索引，要比先为一张表建立fulltext然后再将数据写入的速度快很多
创建方式
创建表的同时创建索引
fulltexy（column）
修改表结构
alter table table_name add fulltext fulltext_name(column)
直接创建
create fulltext index fulltext_name on article(content)
索引简历原则
1.选择唯一性索引
2.为经常需要排序、分组和联合操作的字段建立索引
3.为常作为查询条件的字段简历索引
4.限制索引的数目
5.尽量使用数据量少的索引
如果索引的值很长，那么查询的速度会受到影响
6.尽量使用前缀来索引
如果索引字段的值很长，最好使用值的前缀来索引
7.尽量删除不再使用和很少使用的索引
8.最左前缀匹配原则
9.尽量选择区分度高的列作为索引
区分度的公式是表示字段不重复的比例
10.索引列不能参与计算
带函数的查询不参与索引
11.尽量的扩展索引，不要新建索引
索引优点
创建唯一索引，保证数据库表中的每一行数据的唯一性
大大加快数据的检索速度
加快表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义
在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间
可以在查询的过程中使用优化隐藏器，提高系统的性能
索引缺点
创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
索引需要占用物理空间，如果要建立聚簇索引，那么需要的空间就会更大
对表中数据进行增删改的时候，索引也要动态的维护，降低了数据的维护速度
索引效果
可以应用索引的操作符
> =
between
in
like 不以%开头
不能应用索引的操作符
not in
like %开头
or
nill值判断
!=或<>
列运算（数据库教程函数、计算表达式）
Mysql优化
SQL查询优化
1.尽量避免在where子句中使用!=或<>操作符，否则引擎将放弃使用索引而进行全表扫描
2.对查询进行优化，应尽量避免全表扫描，首先应考虑在where和order by涉及的列上建立索引
3.应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描
错误：select id from t where num is null
正确：可以在num上设置默认值0，确保表中num列没有null值，然后这样查询
select id from t where num=0
4.尽量避免在where子句中使用or来连接条件，否则将导致引擎放弃使用索引而进行全表扫描
错误：select id from t where num=10 or num=20
正确：select id from t where num=10 union all select id from t where num=20
5.尽量避免前置百分号，否正将导致引擎放弃使用索引
错误：select id from t where name like '%c%'
正确：select id from t where name like 'c%'
6.in和not in也要慎用，否则会导致全表扫描
错误：select id from t where num in （1，2，3）
正确：select id from t where num between 1and 3
7.如果在where 子句中使用参数，也会导致全表扫描
错误：select id from t where num=@num
正确(强制使用索引)：select id from t with(index(索引名))where num=@num
8.应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
错误：select id from t where num/2=100
正确：select id from t where num=100*2
9.应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
错误：select id from t where substring(name,1,3)=’abc‘ //name以abc开头的id
正确：select id from t where name like 'abc%'
10.不要再where子句中的“=”左边进行函数、算数运算或其它表达式运算，否则系统将可能无法正确使用索引
11.在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致
12.不要写一些没有意义的查询，如需要生成一个空表结构：
错误：select col1,col2 into #t from t where 1=0
正确：create table #t(...)
13.很多时候用exist代替in是一个好的选择
错误：select num from a where num in(select num from b)
正确：select num from a where exists(select 1 from b where num=a.num)
并不是所有的索引都对查询有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male，female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用
索引并不是越多越好，索引固然可以提高相应的select的效率，但同时也降低了insert及update的效率，因为insert或update时有可能会重建索引。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要
应尽可能的避免更新clustered索引数据列，因为clustered索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源，若应用系统需要频繁更新clustered索引数据列，那么需要考虑是否应将该索引建为clustered索引
尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了
尽可能使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些
任何地方都不要使用select * from t，用具体的字段列表代替”*“，不要返回用不到的任何字段
尽量使用表变量来代替临时表，如果表变量包含大量数据，请注意索引非常有限（只有主键索引）
避免频繁创建和删除临时表，以减少系统表资源的消耗
临时表并不是不可使用，适当的使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表
在新建表时，如果一次性插入数据量很大，那么可以使用select into 代替 create table，避免造成大量log，以提高速度；如果数据量不大，应先create table，然后insert
如果使用到了临时表，在存储过程的最后无比将所有的临时表显示删除，先truncate table，然后drop table，这样可以避免系统表的较长时间锁定
尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写
基于游标的方法或临时表方法之前，应先寻找基于集的解决方法来解决基本问题，基于集的方法通常更有效
与临时表一样，游标并不是不可使用，对于小型数据集使用FAST_FORWORD 游标通常要优于其他逐行处理方法
在所有的存储过程和触发器的开始处设置SET NOCOUNT ON，在结束时设置SET NOCOUNT OFF。无需在执行存储过程和触发器的每个语句后向客户端发送DONEINPROC消息
尽量避免向客户端返回大数据量，若数据量过大，应该考虑需求是否合理
尽量避免大事务操作，提高系统并发能力
代码优化
查看代码是否合理
定位慢SQL，并优化
用自带的慢查询日志或者开源的慢查询系统定位到具体的出问题的sql，然后使用explain、profile等工具来逐步调优，最后经过测试达到效果后上线
合理使用索引
选择在where、group by、order by、on从句中出现的列作为索引项，对于离散度不大的列没有必要创建索引
分表
水平分割
垂直分割
按时间分表
当数据具有很强的时效性，例如微博的数据，可以按月分割
按区间分表
例如用户表1到一百万一张表，一百万到两百万一张表
读写分离
当一台服务器不能满足用户需求时，采用读写分离的方式进行集群
主从同步
数据库最终会把数据持久化到磁盘，集群必须确保每个数据库服务器的数据是一致的
缓存
本地缓存
HashMap/ConcurrentHashMap、Ehcache、GuavaCache等
缓存服务
Redis/Tair/Memcache
使用场景
短时间内相同数据重复查询多次并且数据更新不频繁，这个时候可以选择先从缓存查询，查询不到再从数据库加载并回设到缓存的方式。此场景较适合用单机缓存
高并发查询热点数据，后端数据库不堪重负，可以用缓存来抗
作用
减轻数据库压力，减少访问时间
0 条评论
下一页
