SQL 索引与数据库安全 INDEX1234567891011121314151617181920212223242526272829303132-- 创建索引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 |+---------------------+---------+----------+----------+ 字符串索引前缀索引12-- 以前20个字符做索引CREATE INDEX idx_lastname ON customers (last_name(20)) 全文索引1234567891011CREATE FULLTEXT INDEX idx_title_body ON posts (title, body);-- 他会返回包括react或是redux的结果SELECT *, WHERE MATCH(title, body) AGAINST('react redux')FROM postsWHERE MATCH(title, body) AGAINST('react redux');-- 布尔模式 不要redux 要 formSELECT *, WHERE MATCH(title, body) AGAINST('react redux')FROM postsWHERE MATCH(title, body) AGAINST('react -redux +form' IN BOOLEAN MODE); 复合索引1234-- 把最常用的列放在前面 -- 把基数最大的列放在前面 -- 最重要还是结合实际CREATE INDEX idx_state_points ON customers (state, points); 用户操作 / 数据库安全创建用户12CREATE USER lubenwei@127.0.0.1 / localhost / "%.domain_name"IDENTIFIED BY '123456'; 查看用户1SELECT * FROM mysql.user; 删除用户1DROP USER user_name@???; 修改密码1SET PASSWORD FOR name = 'passwd'; 权限123GRAND SELECT, INSERT, UPDATE, DELETE, EXECUTE -- ALL 给予全部权限ON DB_NAME.* / DB_NAME.TABLE -- *.* 所有数据库所有表TO USER_NAME@???; 查看权限1SHOW GRANTS FOR USER_NAME; 删除权限123REVOKE CREATE VIEWON DB_NAME.TABLEFROM USER_NAME; Linux SQL 索引与数据库安全 http://www.kirisamkekano.com/2023/08/29/SQL12/ 作者 Ame 发布于 2023年8月29日 许可协议 SQL 数据库与表操作 下一篇