第五部分:脱颖而出——数据类型、设计数据库、索引、保护
数据类型
其他的基本的就不过多介绍了,都是常用的,用过就知道了。
-
JSON
-
SET JSON
直接set列名为JSON字符串
-- SET JSON -- 直接赋值 UPDATE products SET properties = '{ "dimensions" : [1,2,3], "weight" : 10, "manufacture" : { "name" : "sony" } }'WHERE product_id = 1;
使用MySQL自带的函数
-- 使用MySQL自带的函数 UPDATE products SET properties = JSON_OBJECT( 'dimension',JSON_ARRAY(1,2,3), 'weight',10, 'manufacture',JSON_OBJECT( 'name','apple' ) ) WHERE product_id = 2;
结果:
-
读取JSON中的属性值
-
JSON_EXTRACT
JSON_EXTRACT(列名,'属性路径: . m a n u f a c t u r e . n a m e ′ ) , .manufacture.name'), .manufacture.name′),为当前的JSON
-- 读取JSON中的属性值 -- $表示的当前JSON文档 SELECT name, JSON_EXTRACT(properties,'$.manufacture.name') AS manufacture FROM products WHERE product_id = 1;
结果:
-
使用列路径运算符
->
-- -> SELECT name, properties->'$.dimensions[0]' AS dimension FROM products WHERE product_id = 1;
结果:
->> 可以获取字符串的内容,如果使用->会带引号"apple"
-- ->> SELECT name, properties->>'$.manufacture.name' AS manufacture FROM products WHERE properties->>'$.manufacture.name' = 'apple';
结果:
-
-
修改JSON中的属性值
-
JSON_SET
JSON_SET(列名 , ‘字段对应的JSON路径表达式’ , ‘要修改的值’);有就更新,没有就新增
-- JSON_SET -- 已有的属性修改,没有的新增 UPDATE products SET properties = JSON_SET( properties, '$.age',10, '$.weight',30 ) WHERE product_id = 1;
结果:
-
JSON_REMOVE
JSON_REMOVE(列名 , ‘JSON字段对应的JSON路径表达式’)
-- JSON_REMOVE UPDATE products SET properties = JSON_REMOVE( properties, '$.age' ) WHERE product_id = 1;
结果:
-
-
设计数据库
设计规范,看看就行了,不用刻意遵守,为了满足规范而设计数据库表,还是那句好能满足需求就可以了,程序和你有一个能跑就行了
-- 第一范式,每一行数据中的每一列都有唯一的值,不允许有重复的列
-- 第二范式, 一张表中的每一列都在描述该表代表的实体
-- 第三范式, 表中的列不应派生自其他列
一些DDL SQL语句
-- CREATE DATABASE
CREATE DATABASE IF NOT EXISTS sql_store2;
-- DROP DATABASE
DROP DATABASE IF EXISTS sql_store2;
-- CREATE TABLE
USE sql_store2;
-- DROP TABLE
DROP TABLE IF EXISTS orders;
DROP TABLE IF EXISTS customer;
CREATE TABLE IF NOT EXISTS customer(
-- 表名(列名, 类型名, 约束名)
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) NOT NULL ,
points INT DEFAULT 0 NOT NULL ,
email VARCHAR(255) UNIQUE NOT NULL
);
-- ALTER TABLE
ALTER TABLE customer
ADD COLUMN last_name VARCHAR(50) NOT NULL AFTER first_name,
MODIFY COLUMN first_name VARCHAR(50) DEFAULT '',
DROP COLUMN points;
-- 添加外键约束
CREATE TABLE IF NOT EXISTS orders(
order_id INT PRIMARY KEY ,
customer_id INT NOT NULL ,
CONSTRAINT fk_orders_customers FOREIGN KEY(customer_id)
REFERENCES customer(customer_id)
ON UPDATE CASCADE
ON DELETE RESTRICT
);
-- 更改主键和外键约束
-- 所有语句都写在一个alter里面会出现操作顺序问题
-- [HY000][1826] Duplicate foreign key constraint name 'fk_orders_customers'
ALTER TABLE orders
DROP PRIMARY KEY ,
ADD PRIMARY KEY (order_id),
DROP FOREIGN KEY fk_orders_customers,
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
-- 分开写没什么问题
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_customers;
ALTER TABLE orders
ADD CONSTRAINT fk_orders_customers FOREIGN KEY (customer_id)
REFERENCES customer(customer_id)
ON UPDATE CASCADE
ON DELETE RESTRICT;
-- 修改字符集
-- 在数据库级别上修改
CREATE DATABASE db_name
CHARACTER SET latin1;
-- 在表级别上修改
ALTER TABLE orders
CHARACTER SET latin1;
-- 在特定列上修改字符集
CREATE TABLE IF NOT EXISTS customer(
customer_id INT PRIMARY KEY AUTO_INCREMENT,
first_name VARCHAR(50) CHARACTER SET latin1 NOT NULL ,
points INT DEFAULT 0 NOT NULL ,
email VARCHAR(255) UNIQUE NOT NULL
);
-- 在表中修改存储引擎
-- 5之前是MyISAM 不支持事务,不支持外键约束,如级联删除或更新;使用表级锁试用于多读,在对表进行操作时会锁定整张表,因此在写操作频繁时可能会造成性能瓶颈,适合读操作较多的应用场景。
-- 8之后是InnoDB支持事务;使用行级锁适用于多写,允许在高并发的情况下进行更细粒度的控制。多个事务可以并发地操作同一张表中的不同行,而不互相阻塞,适合写操作较多的应用场景。
ALTER TABLE orders
ENGINE = InnoDB;
索引
-
EXPLAIN
帮助我们了解查询的执行过程,方便看到使用的索引和预估要查询查询的行数
-- EXPLAIN EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';
结果:
-
CREATE INDEX
-- CREATE INDEX '索引名' ON 表名(列名) CREATE INDEX idx_state ON customers(state);
-
SHOW INDEX
-- SHOW INDEX SHOW INDEXES IN customers; ANALYZE TABLE customers; -- 更新表结构 SHOW INDEXES IN customers;
结果:
除主键外的其他都是二级索引,每一个二级索引都包含了customer_id,查找是先去查找二级索引对应的customer_id,然后通过customer_id在聚集索引(一级索引)找到对应的行
-
前缀索引
针对一些长文本内容,截取前一部分字符串作为索引,减少索引大小的同时,保持唯一性
-- 前缀索引 -- 针对一些长文本内容,截取前部分字符串作为索引,减少索引大小的同时,保持其唯一性 CREATE INDEX idx_lastname ON customers(last_name(20));
判断最佳索引----增加长度,判断其唯一性
-- 判断最佳索引 SELECT COUNT(DISTINCT LEFT(last_name,1)), -- 25 COUNT(DISTINCT LEFT(last_name,5)) , -- 966 5为最佳索引长度 COUNT(DISTINCT LEFT(last_name,10)) -- 996 FROM customers;
-
全局索引
用于检测一些比较长度字符串,常用于全文的索引,和搜索引擎的搜索优化
-
CREATE FULLTEXT INDEX
USE sql_blog; -- 全局索引 CREATE FULLTEXT INDEX idx_title_body ON posts(title,body);
-
MATCH(列名1, 列名2) AGAINST(‘指定要搜索的关键字’ [字符串模式] )
默认是自然语言模式
- 自动分词:MySQL会自动将输入的搜索关键字分成多个词语,并在指定的全文索引列中搜索这些词。
- 忽略停用词:在搜索中会忽略停用词(the , is , and等)。
- 相关性排序:MATCH子句返回的就是相关系数。
- 支持复合搜索:如果搜索词中包含多个单词,MySQL 会搜索包含这两个单词的记录,包含一个的也会查出来,并按相关性进行排序。
-- MATCH全文检索(要检索的列名) -- AGAINST 指定搜索的关键字 -- AGAINST('react redux) 自然语言模式 结果会按照相关性排序 SELECT *,MATCH(title,body) AGAINST('react redux') FROM posts WHERE MATCH(title,body) AGAINST('react redux');
布尔模式
-
AGAINST 布尔模式,允许使用特殊字符来控制搜索行为 + - *,+必须包含,-不包含,*类似%
-- AGAINST 布尔模式,允许使用特殊字符来控制搜索行为 + - * -- +必须包含,-不包含,*类似% SELECT *,MATCH(title,body) AGAINST('react redux') FROM posts WHERE MATCH(title,body) AGAINST('+react -redux' IN BOOLEAN MODE ); -- 搜索指定短语 SELECT *,MATCH(title,body) AGAINST('react redux') FROM posts WHERE MATCH(title,body) AGAINST('"handling a form"' IN BOOLEAN MODE );
-
-
复合索引
在涉及多列查询的时候使用,如果单列索引和多列索引都存在的话,MySQL会自动选择性能更优的
-- 复合索引 USE sql_store; -- 使用的是idx_state索引 选择性能更优的索引 CREATE INDEX idx_state ON customers(state); EXPLAIN SELECT * FROM customers WHERE state = 'CA' AND points > 1000;
结果:使用的idx_state_lastname,讲道理我觉得应该是idx_state_poinsts来着
-
创建复合索引
-- 复合索引 最多可以包含16列 CREATE INDEX idx_state_points ON customers(state,points); DROP INDEX idx_points ON customers; DROP INDEX idx_state ON customers; SHOW INDEX IN customers;
-
复合索引的特点
-
顺序性
复合索引的顺序很重要。复合索引按列的顺序存储,因此对索引的有效使用必须遵循该顺序。例如,如果有一个 (A, B, C) 的
复合索引,查询 WHERE A = 1 AND B = 2 会有效利用索引,但查询 WHERE B = 2 AND C = 3 则不会,因为没有从第一列(A)开始。 -
前缀原则
当查询条件匹配复合索引的前缀时,索引仍然可以被使用。例如,复合索引 (A, B, C) 可以用于以下查询:
• WHERE A = 1
• WHERE A = 1 AND B = 2
• WHERE A = 1 AND B = 2 AND C = 3
但不能用于只涉及后面列的查询,比如 WHERE B = 2 或 WHERE C = 3。
-
覆盖索引
如果查询的所有列都包含在复合索引中,那么该索引可以成为覆盖索引,从而避免访问数据表,进一步提高查询性能。
-
-
复合索引中的列顺序
一般是按照常用的顺序来排,还有列的独特性
-- 复合索引中的列顺序 -- 按照常用的顺序 -- 按照列的独特性 SELECT COUNT(DISTINCT state), COUNT(DISTINCT last_name) FROM customers; SHOW INDEX IN customers; CREATE INDEX idx_lastname_state ON customers(last_name,state); CREATE INDEX idx_state_lastname ON customers(state,last_name);
查询条件都是=,就可以充分利用各列的独特性,因为last_name的独特性为996,所以使用的索引为idx_lastname_state,可能你会说这违反了顺序性原则,但是大多数现代数据库查询优化器会自动调整条件的顺序,以便最有效地利用可用的索引。所以即使你在查询中写 WHERE state = ‘CA’ AND last_name = ‘Smith’,优化器通常会识别出复合索引idx_lastname_state可以被利用,并调整查询执行计划。
-- 查询条件是特定的state和last_name,可以充分利用各列的独特性 -- idx_lastname_state DROP INDEX idx_lastname ON customers; EXPLAIN SELECT * FROM customers WHERE state = 'CA' AND last_name = 'Smith';
然后就是如果有查询条件里面有模糊查询,情况是怎么样的呢
都是一样的,先去找精确查询的索引,然后根据索引筛选出来的结果继续根据第二列索引顺序查找,反正那个精确度高先查哪个
-
USE INDEX( )
强制规定使用哪个索引,也可以用他来判断那个索引效率高点的
EXPLAIN SELECT * FROM customers USE INDEX (idx_lastname_state) WHERE state = 'CA' AND last_name LIKE 'A%'; -- 40row EXPLAIN SELECT * FROM customers USE INDEX (idx_state_lastname) WHERE state = 'CA' AND last_name LIKE 'A%'; -- 7row
-
保护
就是写用户权限的管理,很简单的东西,不分开讲了,直接在代码里面注释下吧
-- create user 用户名 [@规定可以从哪里访问的地址] [IDENTIFIFED BY 密码];
CREATE USER study00 IDENTIFIED BY '1234'; -- IDENTIFIFED BY设置密码
CREATE USER study01@127.0.0.1;
CREATE USER study03@xiongyan.site;
CREATE USER study02@'%.xiongyan.site'; -- %通配符
-- views users
SELECT * FROM mysql.user;
-- DROP users
DROP USER study01@127.0.0.1;
-- changes password
-- FOR 用户名 = 新密码
SET PASSWORD FOR study00 = '12345';
-- grant power
-- GRANT 允许的操作 ON 哪个数据库的哪张表 TO 用户名
GRANT INSERT,UPDATE,SELECT,DELETE,EXECUTE
ON sql_store.*
TO study00;
-- grant admin power
GRANT ALL
ON *.*
TO study01@127.0.0.1;
-- view privileges
SHOW GRANTS ;
-- view privileges for user
SHOW GRANTS FOR study00;
-- revoke privileges
-- 赋予是 TO
GRANT CREATE VIEW ON sql_store.* TO study00;
SHOW GRANTS FOR study00;
-- 移除是 FROM
REVOKE CREATE VIEW ON sql_store.* FROM study00;