mysql随笔

在使用一段时间的mysql后，回过头来听听mysql相关课程，记录的一些普通的、未使用及未意思到的知识点，仅供参考，欢迎指正。 1、库操作 2、表操作 3、索引 最好用联合索引，减少索引数量，查询时覆盖索引，提高效率。 3.1、聚集索引，主键；无主键则第一个唯一索引为聚集索引，以上皆无，自动生成一个row_id为聚集索引。 3.2、二级索引，聚集索以外的索引。 3.3、联合索引 多个字段组成的索引，遵循最左前缀法则（查询条件需要从索引的第一个开始查询，如果跳过某个索引会导致之后的索引失效）； 用于范围查询时，可以通过添加‘=’来符合最左法则 3.4、索引失效 索引列进行运算 字符串类型字段，需要添加单引号，否则会失效。（xx=‘xx’）。 模糊匹配，尾部匹配不失效（like 'xx%'），头部匹配会失效(like '%xx')。 or关键字连接，需要两侧都是索引才会生效，联合索引不适用于or关键字 3.5、索引使用 use index 查询时建议使用指定的索引 ignore index 查询时忽略指定的索引 force index 查询时强制使用指定的索引 3.6、前缀索引 用于文本类型的数据，指定字段前几个字符串作为索引 create index index_name on table_name(column_name(n)); 4、优化 sql优化主要针对索引的涉及进行优化 4.1、插入优化 小于1000条时，批量插入。 手动提交，避免频繁提交。 大量插入，可使用load命令。 4.2、排序优化 order by 排序时，需要与创建索引时索引的升降一致。 排序缓冲区：sort_buffer_size，默认256k。 5、视图 5.1、创建视图 5.2、修改视图 5.3、删除视图 5.4、检查选项 5.5、视图数据更新 需要视图数据与基础表数据一一对应才可以更新。 5.6、视图作用 简单：只展示客户需要的数据，屏蔽多余的字段 安全：只开放视图，无法直接操作表 数据独立：更改原表数据结果，不改变视图的展示 6、存储过程 6.1、创建语句 create procedure p1() begin sql end; 6.2、调用 call p1(); 6.3、查看存储过程 查看实例下的所有存储过程 select * from information_scheme.ROUTINES where ROUTINES_SCHEME=database_name; 查看单个存储过程 show create procedure p1; 6.4、变量 6.4.1、系统变量 show session\global variables; --查看当前会话\全局变量 show session\global variables like ''； --模糊查询 6.4.2、设置系统变量 set session\global 系统变量=xxx; --当前会话只对当前会话有效，全局修改，在重启后失效，需要一直生效需要在配置文件my.ini/my.cnf里配置 set @@session\global 系统变量=xxx; 6.4.3、用户自定义变量 set @var_name=xxx set @var_name:=xxx select @var_name:=xxx select column_name into @var_name from table_name 使用：select @var_name 6.4.4、局部变量 声明：declare 变量名 变量类型（默认default） 赋值：同用户自定义变量 6.5、参数 in 输入参数 out 输出参数 inout 输入输出参数 6.6、逻辑判断 case 循环 while、repeat、loop 退出循环 leave 直接跳过当前循环，进入下一个循环 iterate create procedure pq(in n int) begin declare total int default 0; sum:loop if n<=0 then leave sum; end if; if n%2=1 then set n:=n-1; iterater sum; end if; set total :=total+n; set n=n-1; end loop sum; select total; end; 6.7、游标cursor 声明游标：declare cursor_name cursor for 查询语句 打开游标：open cursor_name 获取游标记录：fetch cursor_name into 变量 关闭游标：close sursor_name create procedure p(in uage int) begin declare uname varchar(100); declare pro varchar(100); declare u_cursor cursor for select name,pro from user where age <=uage; declare exit handle for SQLSTATE '02000' close u_sursor; --定义退出条件 drop table if exists tb_use_pro; create table if not exists tb_use_pro( id int primary key auto_increment; name varchar(100); pro varchar(100) ); open u_cursor; while true do fetch u_cursor into uname,upro; insert into tb_use_pro values(null,uname,upro); end while; close cursor end; 7、触发器 create trigger trigger_name after/before insert/update/delete on table_name for each row -- 对某表的增删改操作，在操作的前后 begin insert into table_log () values (); --触发将记录写入其他表 end; 更新操作：原数据old.column 新数据new.column 查看触发器：show triggers; 删除触发器：drop trigger trigger_name； 8、锁 -- performance_scheme.data_lock 8.1、全局锁(备份数据库) flush tables with read lock； mysqldump -uroot -pxxx database>xx.sql unlock tables; 8.2、表锁 lock tables table_name read/write unlock tables; 8.2.1、表共享读锁（read lock） 客户端只能读不能写，不阻塞其他客户端的读，但阻塞写 8.2.2、表独占写锁（write lock） 客户端可读可写，但阻塞其他客户端的读和写 8.2.3、元数据锁 避免dml和ddl冲突，保证读写的正确性 8.2.4、意向锁 避免dml行锁和表锁冲突 意向共享锁（IS）：与表共享读锁兼容，与表排他锁互斥 意向排他锁（IX）：与表锁共享锁及排他锁都互斥 8.3、行级锁 8.3.1、行锁：锁定一行，需针对索引，否则会对整个表加锁 共享锁（S）：兼容共享锁，排斥排他锁 排他锁（X）：排斥共享锁和排他锁 8.3.2、间隙锁：两个相邻键之间加锁，防止其他事务插入间隙，间隙锁可以共存 8.3.3、临键锁：行锁+间隙锁 9、innodb 看视频吧，理解不了了。