SQL 索引与数据库安全

INDEX

-- 创建索引
CREATE INDEX idx_state ON customers (state);

-- 查看索引
-- 主键 聚集索引
SHOW INDEXES IN customers;
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table     | Non_unique | Key_name   | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| customers |          0 | PRIMARY    |            1 | customer_id | A         |        1010 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customers |          1 | idx_state  |            1 | state       | A         |          48 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| customers |          1 | idx_points |            1 | points      | A         |         788 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+-----------+------------+------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

-- 外键自动创建索引
SHOW INDEXES IN orders;
+--------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| Table  | Non_unique | Key_name                     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+--------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+
| orders |          0 | PRIMARY                      |            1 | order_id    | A         |          10 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | fk_orders_customers_idx      |            1 | customer_id | A         |           6 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
| orders |          1 | fk_orders_shippers_idx       |            1 | shipper_id  | A         |           5 |     NULL |   NULL | YES  | BTREE      |         |               | YES     | NULL       |
| orders |          1 | fk_orders_order_statuses_idx |            1 | status      | A         |           2 |     NULL |   NULL |      | BTREE      |         |               | YES     | NULL       |
+--------+------------+------------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+---------+------------+

-- 优化查询性能
ANALYZE TABLE customers;
+---------------------+---------+----------+----------+
| Table               | Op      | Msg_type | Msg_text |
+---------------------+---------+----------+----------+
| sql_store.customers | analyze | status   | OK       |
+---------------------+---------+----------+----------+

字符串索引

前缀索引

-- 以前20个字符做索引
CREATE INDEX idx_lastname ON customers (last_name(20))

全文索引

CREATE FULLTEXT INDEX idx_title_body ON posts (title, body);

-- 他会返回包括react或是redux的结果
SELECT *, WHERE MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');

-- 布尔模式 不要redux 要 form
SELECT *, WHERE MATCH(title, body) AGAINST('react redux')
FROM posts
WHERE MATCH(title, body) AGAINST('react -redux +form' IN BOOLEAN MODE);

复合索引

-- 把最常用的列放在前面    
-- 把基数最大的列放在前面  
-- 最重要还是结合实际
CREATE INDEX idx_state_points ON customers (state, points);

用户操作 / 数据库安全

创建用户

CREATE USER lubenwei@127.0.0.1 / localhost / "%.domain_name"
IDENTIFIED BY '123456';

查看用户

SELECT * FROM mysql.user;

删除用户

DROP USER user_name@???

修改密码

SET PASSWORD FOR name = 'passwd';

权限

GRAND SELECT, INSERT, UPDATE, DELETE, EXECUTE -- ALL 给予全部权限
ON DB_NAME.* / DB_NAME.TABLE  -- *.* 所有数据库所有表
TO USER_NAME@???;

查看权限

SHOW GRANTS FOR USER_NAME;

删除权限

REVOKE CREATE VIEW
ON DB_NAME.TABLE
FROM USER_NAME;