视图:视图是一个虚拟表,是sql的查询结果,其内容由查询定义。可以作为权限控制
创建:create view 视图名 as select 字段名 from 表名;
修改:alter view 视图名 as select 语句
显示:show create view 视图名;
删除: drop view 视图名[,视图名…];
重命名:Rename table 视图名 to 新视图名;;
存储过程:在数据库中存储复杂程序,以便外部程序调用的一种数据库对象。
存储过程是为了完成特定功能的SQL语句集,经编译创建并保存在数据库中,用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。
区分存储过程和存储函数
创建存储过程:create procedure
调用存储过程:call
查询存储过程:mysql.proc表;或show procedure status;show create procedure
删除存储过程:drop procedure
语法:变量声明 declare 变量 value
变量复制:set或select into
条件判断:If then else end if
参数传递:IN OUT INOUT @代表会话变量
Case语法:case when end case
循环:while do end while
Repeate until end repeate 满足条件退出循环
Loop end loop
游标:用来存储查询结果集:
声明:delcare yourname cursor for 语句
打开:open yourname
Fetch:fetch yourname into variname
关闭:close yourname
存储函数:存储函数有返回值, 存储过程没有返回值
Create function func_name()
Returns type
Begin
End;
调用:select func();
触发器:与表有关的数据对象。在insert/update/delete之前或者之后,触发病执行触发器中定义的SQL语句集合。(mqsql 只支持行级触发器)
变量:NEW和OLD
创建触发器:create trigger trigger_name berfore/after insert/update/delete on table_name [for each row] trigger_stmt;
删除触发器:drop trigger [schema_name.]trigger_name
查看触发器:show triggers;
Mysql体系结构:
Myql server组成:Connection Pool连接池
Management services&utilltles:管理服务和工具组件
SQL Interface
Parser:解析器
Optimizer:优化器
Caches & buffers:缓存
Storage Engines:存储引擎(插件式)
存储File system:文件系统
存储引擎:
基于表;
查看引擎:show engines;
InnoDB:支持事务、行锁,支持外键
MyISAM:不支持事务,也不支持外键,但是访问速度快
Memory:数据存放在内存中
MERGE:一组MyISAM表的组合
优化SQL步骤:
查看sql执行频率:show [global] status linke ‘Com_______’;
Show global status like ‘Innodb_rows_%’;
定位低效执行的sql:
- 慢查询日志 –log_slow_queries
- Show processlist
Explain分析执行计划:(常用)
Explain select语句
Show profile分析sql:
是否支持:select @@have_profiling;
是否开启:select @@profiling;
分析:show profile [all/cpu/…] for query id;
Trace分析优化器执行计划:
打开trace,进行设置:
Set optimizer_trace=”enabled=on”, end_markers_in_json=on;
Set optimizer_trace_max_mem_size=10000;
执行语句
Select * from information_schema.optimizer_trace\G;
索引的使用
索引提升查询效率,ceate index index_name on table(col);
索引的使用:
- 避免索引失效:全值匹配
- 最左前缀法则:索引了多个列, 查询从索引的最左列开始,并且不跳过索引中的列(和语句中指明的先后顺序没有关系)
- 范围查询右边的列,不能使用索引
- 不要再索引列上进行运算操作,否则索引失效
- 如果是字符串类型,字符串不加单引号,造成索引失效
- 尽量使用覆盖索引,避免select * (展示的是索引中的数据,不需要回表查询)
- 用OR分割开的条件,如果OR前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到
- 以%开头的like模糊查询,索引失效(可使用覆盖索引解决)
- 如果mysql评估使用索引比全表更慢,则不使用索引
- IS NULL, IS NOT NULL,有时候索引失效(根据匹配的数据量来决定)
- IN 走索引,NOT IN索引失效
- 单列索引,符合索引;尽量使用符合索引,少使用单列索引
查看索引使用情况
Show status like ‘Headler_read%’
Show global status like ‘Handler_read%’;
SQL优化
- 大批量插入数据
本地文件系统load加载数据时,按主键顺序插入
关闭唯一性校验
手动提交事务
- 优化insert语句
一张表中插入多行数据时,尽量使用多个值表的insert语句
在事务中进行数据插入
数据有序插入
- 优化order by语句
通过对返回数据进行排序,filesort排序,不通过索引,效率低,(和覆盖索引类似,select *语句尝尝出现)
通过有序索引顺序扫描直接返回有序数据,即using index,操作效率高(数据索引字段即可拿到)
多字段索引:where条件和order by使用相同的索引,并且order bu的顺序和索引顺序 相同,同升序或降序
FileSort的优化:1. 两次扫描算法;2. 一次扫描算法(效率高)
提高sort_buffer_size和max_length_for_sort_data系统变量
- 优化group by语句
利用索引和order by null
- 优化嵌套查询
尽量少使用子查询而使用多表连接查询JOIN
- 优化OR条件
对于包含OR的查询子句,如果要利用索引,则OR之间的每个条件列都必须用到索引,而且不能使用到复合索引;如果没有索引,则应该考虑增加索引。
建议使用union替换or
- 分页查询
在索引上完成排序分页操作,根据主键关联原表查询其它需要的内容
用于主键自增表,把limit查询转换成某个位置的查询
- 使用SQL提示
USE INDEX:查询数据库时在查询表名后面,建议参考的索引列表
IGNORE_INDEX:建议忽略的索引
FORCE_INDEX:强制使用索引
SQL应用层优化:
- 使用数据库连接池
- 减少对mysql的访问;增加cache层
- 负载均衡:mysql的复制来完成读写分离;采用分布式数据库架构
Mysql查询缓存优化:
参数配置:是否支持:show variables like ‘have_query_cache’
是否开启:show variable like ‘query_cache_type’
查询缓存的大小:show variables like ‘variable_cache_size’
状态:show status like ‘Qcache%’
Select选项:SQL_CACHE 查询结果是可缓存的
SQL_NO_CACHE: 服务器不使用查询缓存
查询缓存失效:1. SQL语句不一样(大小写也算);2 查询语句中有一些不确定的数据时,不会缓存;3 不使用任何表查询语句;4 查询mysql information_schema 或performance_schema数据库中的表时,不会走缓存;5 在存储的函数,触发器或事件的主体内执行的查询;6 表更改
Mysql内存优化:
原则:1. 尽可能多的内存分配给mysql做缓存;2. MyISAM表,就需要预留更多的内存给操作系统做IO缓存;3. 排序区、连接区等缓存时分配给每个数据库会话专用的。
MyISAM内存优化:使用key_buffer缓存索引;key_buffer_size决定了索引块缓冲区的大小;read_buffer_size;read_rnd_buffer_size;
InnoDB内存优化:用一块内存区做IO缓存,不仅有索引,还有数据块;
参数:innodb_buffer_pool_size; innodb_log_buffer_size
Mysql并发参数调整:
参数: 1, max_connection 2. back_log 3. table_open_cache 4. Thread_cache_size 5. Innodb_lock_wait_timeout
Mysql锁问题
- 锁分类:表锁、行锁、读锁(共享锁)、写锁
- Mysql锁:不同的存储引擎支持不同锁机制
- MyISAM只支持表锁:
MyISAM会自动给涉及的所有表假读锁
显示方式:lock table table_name read/write;
读锁会阻塞写,但是不会阻塞读;写锁即阻塞读,又阻塞写(写优先)
查看锁的情况:show open tables; show status like ‘Table_locks%’;
- InnoDB(默认)行锁:开销大,加锁慢。
隔离级别:read uncommiteed / read committed/repeateable read/ Serializable
查看隔离级别:show variables like ‘tx_isolation’
共享锁S:读锁;排他锁 X:写锁
UPDATE/DELETE/INSERT默认排他锁,SELECT 默认不加任何锁
共享锁:LOCK IN SHARE MODE 排他锁:FOR UPDATE
表锁:如果不通过索引条件索引数据,那么InnoDB将对表中所有的数据增加锁,实际效果和表锁一样;
间隙锁:使用范围条件查询,请求共享或排他锁时,InnoDB会给符合条件的已有数据进行加锁,InnoDBy也会对这个其中的间隙进行加锁。
InnoDB行锁情况:show status like ‘innodb_row_lock%’;
尽可能让所有数据检索都能通过索引来完成,避免升级成表锁
合理涉及索引,缩小锁的范围
减少索引条件,即索引范围,减少间隙锁
控制事务大小,减少锁定资源量和时间
使用低级别事务索引
SQL技巧:
- 编写步骤:
SELECT DISTINCT
….
FROM
…
JOIN
…
WHERE
. ….
GROUP BY
…
HAVING
…
ORDER BY
…
LIMIT
…
执行步骤:
FROM ON JOIN WHERE GROUP BY HAVING SELECT ORDER BY LIMIT
- 正则表达式
- 常用函数:数字函数、字符串函数、时间函数、聚合函数