Mysql学习笔记

视图：视图是一个虚拟表，是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:

1. 慢查询日志 –log_slow_queries
2. 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);

       索引的使用：

1. 避免索引失效：全值匹配
2. 最左前缀法则：索引了多个列， 查询从索引的最左列开始，并且不跳过索引中的列（和语句中指明的先后顺序没有关系）
3. 范围查询右边的列，不能使用索引
4. 不要再索引列上进行运算操作，否则索引失效
5. 如果是字符串类型，字符串不加单引号，造成索引失效
6. 尽量使用覆盖索引，避免select * (展示的是索引中的数据，不需要回表查询)
7. 用OR分割开的条件，如果OR前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到
8. 以%开头的like模糊查询，索引失效（可使用覆盖索引解决）
9. 如果mysql评估使用索引比全表更慢，则不使用索引
10. IS NULL， IS NOT NULL，有时候索引失效（根据匹配的数据量来决定）
11. IN 走索引，NOT IN索引失效
12. 单列索引，符合索引；尽量使用符合索引，少使用单列索引

查看索引使用情况

       Show status like ‘Headler_read%’

       Show global status like ‘Handler_read%’;

SQL优化

1. 大批量插入数据

本地文件系统load加载数据时，按主键顺序插入

关闭唯一性校验

手动提交事务

1. 优化insert语句

一张表中插入多行数据时，尽量使用多个值表的insert语句

在事务中进行数据插入

数据有序插入

1. 优化order by语句

通过对返回数据进行排序，filesort排序，不通过索引，效率低，（和覆盖索引类似，select *语句尝尝出现）

通过有序索引顺序扫描直接返回有序数据，即using index，操作效率高（数据索引字段即可拿到）

多字段索引：where条件和order by使用相同的索引，并且order bu的顺序和索引顺序 相同，同升序或降序

FileSort的优化：1. 两次扫描算法；2. 一次扫描算法（效率高）

        提高sort_buffer_size和max_length_for_sort_data系统变量

1. 优化group by语句

利用索引和order by null

1. 优化嵌套查询

尽量少使用子查询而使用多表连接查询JOIN

1. 优化OR条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

建议使用union替换or

1. 分页查询

在索引上完成排序分页操作，根据主键关联原表查询其它需要的内容

用于主键自增表，把limit查询转换成某个位置的查询

1. 使用SQL提示

USE INDEX:查询数据库时在查询表名后面，建议参考的索引列表

IGNORE_INDEX：建议忽略的索引

FORCE_INDEX:强制使用索引

SQL应用层优化：

1. 使用数据库连接池
2. 减少对mysql的访问；增加cache层
3. 负载均衡：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锁问题

1. 锁分类：表锁、行锁、读锁（共享锁）、写锁
2. Mysql锁：不同的存储引擎支持不同锁机制
3. MyISAM只支持表锁：

MyISAM会自动给涉及的所有表假读锁

显示方式：lock table table_name read/write;

读锁会阻塞写，但是不会阻塞读；写锁即阻塞读，又阻塞写(写优先)

查看锁的情况：show open tables;  show status like ‘Table_locks%’;

1. 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技巧：

1. 编写步骤：

SELECT DISTINCT

        ….

FROM

        …

JOIN

        …

WHERE

.      ….

GROUP BY

        …

HAVING

        …

ORDER BY

        …

LIMIT

        …

执行步骤：

FROM ON JOIN WHERE GROUP BY HAVING SELECT ORDER BY LIMIT

1. 正则表达式
2. 常用函数：数字函数、字符串函数、时间函数、聚合函数