MYSQL学习——基础、索引、优化

2022/1/14 19:04:52

编程Tag： 优化 mysql 函数 参数 查询 索引 语句 表名

本文主要是介绍MYSQL学习——基础、索引、优化，对大家解决编程问题具有一定的参考价值，需要的程序猿们随着小编来一起学习吧！

逃跑计划——MYSQL

1、MYSQL常见命令

1>显示所有数据库：show databases;

2>进入某个库：use 库名称;

3>显示所有的表：show tables 或者 show tables from 库名;

4>查看在那个库里：select database();

5>创建表：create table user( id int,name vaarchar(20));chakan

6>查看表结构：desc 表名称;

7>查看有哪些数据：select * from 表名称;

8>插入数据：insert into 表名称(id,name) values(1,'zhang';

9>修改数据：update 表名称 set name ='li' where id =1;

10>删除数据：delete from 表名称 where id =1;

2、MYSQL语法规范

  1. 不区分大小写，建议关键字大写，表名、列名小写。

  2. 每条命令最好用分号结尾。

  3. 注释：单行：#文字或者-- 文字，多行注释 /* 文字 */

3、DQL语言

  1. 关键字：去重DISTINCT

  2. +号：如果一方为字符则运算时会把字符转为0，再做运算。如果一方为null，则结果为null。

  3. 不再。。。。之间的。NOT（num>x AND num<y) 。

  4. 查询第三个字符为a,第五个字符为e的，like '__a_e%'。

  5. 查询第二个字符为下划线的：like '-\_%' 或者 like '_$_%' ESCAPE '$' 定义转译字符。

  6. between and 包括临界值，两个临界值位置不能替换。

  7. in 里边不支持通配符。

  8. 运算符号不能判断NULL值。

  9. <=>安全等于，可以判断NULL值，也可以判断普通的值。

  10. 常见函数：

    1. 字符函数：LENGTH()获取字符串长度, CONCAT()拼接字符串，UPPER(),LOWER()大小写转换，SUBSTR()截取字符串，INSTR()返回子串第一次出现的索引，如果没有，则返回0，TRIM()去掉空格，TRIM('字符' FROM '字符串'）去掉前后的某个字符，LAPD('字符串'，10，'*')用指定的长度左填充，RPAD('字符串'，'10','*')用指定长度右填充。

    2. 数学函数：ROUND()四舍五入函数，CEIL()向上取整，FLOOR()向下取整，TRUNCATE()截断函数，MOD()取余，

    3. 日期函数：NOW()当前系统日期+时间，CURDATE()返回当前日期，CURTIME()返回当前时间，通过YEAR(),MONTH()获取年，月。STR_To_DATE()字符转日期，DATE_FORMAT()日期转字符。

    4. 其他函数：version() 版本号。

    5. 流程控制函数：IF函数if(2>1,'Y','N')类似三目表达式。CASE()函数,相当于if...else。

    6. 分组函数SUM(),AVG(),MIN(),MAX(),COUNT(),SUM,AVG处理数值类型，MAX，MIN，COUNT处理任何类型，SUM和AVG忽略NULL值，MAX，MIN忽略NULL值。COUNT忽略NULL值。在MYISAM引擎下COUNT（*）效率高，在INNODB引擎下COUNT(*) 和COUNT(1)效率差不多。

  11. 联合查询UNION（），将多个结果合并为一个。默认去重，UNINO ALL不去重。

4、DML语言

  1. INSERT 插入语句。

  2. UPDATE更新语句

  3. DELETE删除语句，TRUNCATE 删除没有返回值，效率高点，不能回滚事物，主键自增从0开始。

5、DDL语言

  1. 库的管理

  2. 表的管理

修改列名：ALTER TABLE 表名CHANGE COLUMN 旧名 新名 类型。

修改列的类型约束：ALTER TABLE 表名 MODIFY COLUMN 列名 类型。

添加新列：ALTER TABLE 表名 ADD COLUMN 列名 类型。

删除列：ALTER TABLE 表名 DROP COLUMN 列名。

修改表名：ALTER TABLE 表名 RENAME TO 新表名。

删除表：DROP TABLE 表名。

表的复制：仅仅复制表结构：CREATE TABLE COPY LIKE 表名。

  1. 数据类型：char()固定长度、比较耗费资源，效率高，varchar()不定长度，节省资源，效率低。

  2. 常见的约束：

六大约束：NOT NULL 非空约束。

DE FAULT 默认值

PRIMARY KEY 主键，保证非空和唯一性。

UNIQUE 唯一性。可以为空。

CHECK 检查约束 mysql不支持。

FOREIGN KEY 外键。

标识列：AUTO_INCREMENT 伴随数值型使用，可以有多个，不一定和主键搭配使用。但必须是个key.

6、TCL语言

  1. 事务的ACID属性：原子性（Atomicity)事务是一个不可分割的工作单位事务中操作要么都发生，要么都不发生。一致性（Consistency)事务必须使数据从一个状态变换到另一个一致性状态。隔离性(Isolation)一个事务的执行不能被其他事务干扰。持久性(Durability)事务一旦被提交，是对数据库的永久性改变。

  2. 开启事务的步骤：

1》set autocommit =0; start transaction;(可选的）

2》提交事务：commit；

3》回滚事务：rollback；

  1. 隔离级别：查看隔离级别：select @@tx_isolation ;设置隔离级别：set session transaction isolation lavel 隔离级别；隔离级别分类：1、read uncommited 可能会出现幻读、脏读，不可重复读。2、read commited 可能会出现幻读，或者不可重复读。3、repeatable read 可能会出现幻读，幻读主要是针对插入的。4、serializable 串行化，在这个级别事务持续期间，禁止其他事务对该表进行增删改操作。所以导致性能很低。

  2. 视图：虚拟表，和普通表一样使用，创建语句：CREATE VIEW 视图名 AS SELECT .....。查看视图：DESC 视图名。对视图的增删改会影响原表的增删改。包含分组、关联、常量等的视图不能进行更新操作。

  3. DELETE 和TRUNCATE 在事务使用时的区别：TRUNCATE 在事务中不支持回滚。

  4. 变量：查看变量：SHOW GLOBAL VARIABLES, 给变量赋值：SET GLOBAL 变量名=“值”，SET @@GLOBAL.变量名=“值” 局部变量：声明 DECLARE 变量名 类型，局部变量用在订一他的BEGIN...ENG之间。

  5. 存储过程：一组预先编译好的SQL语句的集合，减少编译次数和数据库的连接次数。提高了效率。

1》创建语法：CREATE PROCEDURE 存储过程名 (参数模式，参数名，参数类型）

BEGIN

存储过程（一组合法的SQL语句）

END

2》参数模式:IN：该参数可以作为输入，需要调用方传过来

OUT：该参数可以作为输出，也就是该参数可以作为返回值

INOUT：该参数既可以作为输出，也可以作为输入，也就是既需要传 入，也可以输出。

3》如果存储主体中只有一句SQL，则BEGIN ..END 可以省略。存储过程中的每一条 语句末尾都必须加分号。末尾可以使用DELIMITER重新设置。

4》调用过程:CALL 存储过程名（模式，参数，类型）；

5》案例：CREATE PROCEDURE(IN username varchar(20).OUT nameInfo varchar(20))

BEGIN

SELECT NAME INTO nameInfo

FROM 表名

WHERE NAME =username

END $

备注：SELECT DATEDIFF(参数一，参数二)如果参数一比参数二大，返回一个 正数，相反则返回负数。

6》存储过程删除：DROP PROCEDURE 存储过程名。

7》查看存储过程：SHOW CREATE PROCEDURE存储名。

  1. 函数：一组预先编译好的SQL语句的集合，减少编译次数和数据库的连接次数。提高了效率。

1》创建函数：CREATE FUNCTION 函数名（参数列表）RETURNS 返回类型

BEGIN

函数体

END

2》调用函数：SELECT 函数名（参数列表）

3》查看函数：SHOW CREATE FUNCTION 函数名。

4》删除函数：DROP FUNCATION 函数名。

  1. 存储过程和函数的区别：存储过程可以有0个或多个返回值，函数有且仅有一个返回值。

  2. 流程控制结构：

1》顺序结构：从上往下执行

2》分支结构：从多条路径选择一条执行

①if函数if（表达式，返回值一，返回值二）

②case 变量 | 表达式 | 字段

when 要判断的值 then 返回的值1.

when 要判断的值 then 返回的值2.

else 返回值 0

end case.

③if 条件1 then语句1

elseif 条件2 then语句2

else 语句n

end if；

3》循环结构：循环执行同一条语句

① while 循环条件 do

循环体；

end while;

②while（循环条件）{

循环体

}

③repeat:先执行后判断。

④loop:没有循环条件，死循环。

7、MYSQL架构介绍

  1. MYSQL 安装查询 rpm -qa | grep -i mysql

  2. 用CAT 命令查看用户和用户组

  3. 设置开机自启：chkconfig mysql on.

8、索引优化分析

  1. 性能下降sql慢

  2. 执行时间长

  3. 等待时间长

  4. 索引：index 是一种数据结构，排好序的快速查找数据结构。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构，就是索引。

  5. Explain + sql 语句: Explain 能干嘛：

1》表的读取顺序

id 相同：执行顺序由上至下。

id不同：如果是子查询，id的序号会自增，id的值越大，优先级越高，优先被执行

id相同不同同时存在

2》数据读取操作的操作类型

select_type:

a:simple:不包含自查询和union语句。

b:primary:包含复杂的子查询，最外层的查询则被标记为primary

c:subquery:子查询被标记为subquery

d:derived:把生成的零时表标记为drived

e:union:在第二个select之前出现union

3》那些索引可以使用

possible_keys:显示可能应用到这张表中的索引

4》那些索引被实际使用

key:实际被应用的索引

5》表之间的引用

6》每张表有多少张被优化器查询

7》访问类型排序

从最好到最差依次是：system>const>eq_ref>ref>range>index>all

system:只有一条数据，相当于系统表

const：常用于比较主键级别的。

eq_ref:唯一性索引扫描或者主键扫描

ref:非唯一性索引扫描，返回匹配某个值的所有行。

range:只检索指定范围的行，使用一个索引来选择行，

index：只便利索引树

all：遍历全表。

  1. 索引失效（应该避免的）：

多个字断的的联合索引，查询从索引的最左侧开始，不能跳过中间的索引。

9、查询截取分析

分析： 1、把跑的慢的服务的sql抓取出来

2、explain +慢sql分析。

3、show profile:查看sql在mysql服务器上的执行细节和生命周期

4、数据库服务参数调整

  1. 小表驱动大表

  2. order by 排序：尽量使用index排序，避免使用fileSort排序

  1. group by: 先分组后排序，也是适用索引最佳左前缀。

  2. 慢查询日志分析：mysql有个慢查询日志，一般不建议开启。因为写日志会影响性能，

show variables like "%slow_query_log" 设置阈值 long_query_time.

日志分析工具：MySQLdumpshow

5、show profile:mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。挣断命令：show profile cpu,block io for query

10、MYSQL锁机制

  1. 锁的分类：读锁（共享锁）和写锁（排他锁），行锁（偏写）和表锁（偏读）。

  2. 表锁（MYISAM）：手动增加表锁：lock table 表名 read(write)，查看锁：show open tables，释放锁：unlock tables

  3. 加了读锁后，除了读自己，其他的什么都不能干。其他的用户可以读他和读其他，但是修改和插入操作是阻塞等待。

  4. 加了写锁：自己可以读写锁定的表，但是不能读其他的表。其他用户可以查询是阻塞等待。

  5. MYISAM的读写锁调度写优先，所以myisam不适合做写为主的表的引擎。

  6. 行锁（innoDB):

  9. 行锁：读己只锁写

  10. 间隙锁的危害：当我们用范围条件而不是相等条件检索数据，innoDB会给符合条件的已有记录的索引项加锁，对于键值在条件范围内但并不存在的记录叫做“间隙”（GAP）。

  11. 如何锁定一行：命令begin;锁定一行，commit；结束锁定。

11、主从复制

  1. 主从复制的原理：slave会从master读取binlog来进行数据同步。mysql的复制是异步串行化的。

  2. 主从复制常见配置：1、数据库版本一致，在同一网段，主从机关闭防火墙，主机上建立账户并授权slave

12、Mycat读写分离、分库分表

  1. mycat数据库中间件：读写分离、数据分片（垂直+水平拆分）、多数据源整合。

  2. mycat原理：拦截

  3. 分库：选择分库的表，然后配置schema.xml文件。

  4. 分表：

  5. 全局序列化：推荐使用数据库方式



这篇关于MYSQL学习——基础、索引、优化的文章就介绍到这儿，希望我们推荐的文章对大家有所帮助，也希望大家多多支持为之网！


相关编程文章

更多>
本站官方QQ
官方QQ:
23002807

沪ICP备2023033072号-1

     

扫一扫关注最新编程教程