MySQL 基础与高性能索引策略

时间：2022-05-16 20:08:50

MySQL 基础与高性能索引策略

MySQL基础知识点

用户管理

#查看已有用户信息
SELECT User,Host,Password FROM mysql.user;
#创建新用户并查看信息
CREATE USER 'tom'@'172.16.0.%' IDENTIFIED BY 'tom';
SELECT User,Host,Password FROM mysql.user;
#重命名用户
RENAME USER 'tom'@'172.16.0.%' TO 'obama'@'172.16.0.%';
  • 修改密码的三种方法
#方法1
SET PASSWORD FOR 'jerry'@'172.16.50.%' = PASSWORD('jerry');
#方法2
UPDATE user SET Password=PASSWORD('') WHERE Host='localhost';
#方法3
mysqladmin -ujerry -h'172.16.50.9' -p password 'jerry';
  • 假如管理员密码忘记了，可使用如下办法
#修改配置文件，登录时跳过身份验证
vim /usr/lib/systemd/system/mariadb.service 
ExecStart=/usr/bin/mysqld_safe --basedir=/usr --skip-grant-tables --skip-networking
#重读配置，重启服务
systemctl daemon-reload
systemctl restart mariadb
#进入mysql、修改密码，然后退出mysql，将修改的配置文件恢复原样
vim /usr/lib/systemd/system/mariadb.service 
ExecStart=/usr/bin/mysqld_safe --basedir=/usr 
#重读配置，重启服务
systemctl daemon-reload
systemctl restart mariadb
#使用已知的密码登录mysql
  • ==执行用户或权限管理类语句后，一定要刷新授权信息==
FLUSH PRIVILEGES;

权限管理

  • 授权：GRANT
GRANT  priv_type [(column_list)] [, priv_type [(column_list)]] ... ON [object_type] priv_level TO user_specification [, user_specification] ... [REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}] [WITH with_option ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name


ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'             

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count
  • 查看授权：SHOW GRANTS
SHOW GRANTS [FOR 'user'@'host']
  • 取消授权：REVOKE
REVOKE  priv_type [(column_list)][, priv_type [(column_list)]] ... ON [object_type] priv_level FROM  'user'@'host' [,  'user'@'host'] ...
REVOKE ALL PRIVILEGES, GRANT OPTION FROM user [, user] ...

INDEX —— MySQL 的索引

索引

索引的优点：可以降低需要扫描的数据量，减少了IO次数；可以帮助避免排序操作，避免使用临时表；帮助将随机IO转为顺序IO。

注意：==创建索引后会降低写性能==，表数据量越大，降低性能越多。

  • 索引类型：B+ TREE，HASH
    B+ TREE：顺序存储，每一个叶子结点到根结点的距离相同；左前缀索引，适合于范围类型的数据查询；

  • 适用于B+ TREE索引的查询类型：全键值、键值范围或键前缀；

全值匹配：精确匹配某个值；

WHERE CLOUMN = 'value';

匹配最左前缀：只精确起头的部分；

    WEHRE COLUMN LIKE 'PREFIX%'; 

匹配范围值：

精确匹配某一列，范围匹配另一列；

只用访问索引的查询：覆盖索引；

    index(Name)

SELECT Name FROM students WHERE Name LIKE 'L%';
  • 不适用B+ TREE索引：
如查条件不是从最左侧列开始，索引无效；
index(age,name), WHERE name='Jerry';    , WHERE age>30;
不能跳过索引中的某列；
index(name,age,gender)
WHERE second_name='black' and age > 30;
如果查询中的某个列是为范围查询，那么其右侧的列都无法再使用索引优化查询；

  • Hash索引：基于哈希表实现，特别适用于值的精确匹配查询；

  • 适用场景：

只支持等值比较查询，例如=, IN(), <=>

  • 不用场景：

所有非精确值查询；MySQL仅对memory存储引擎支持显式的hash索引；

索引使用方法

  • 创建索引
#创建单字段索引
CREATE INDEX index_name ON table_name(column_name);

#创建多字段组合索引
CREATE INDEX index_name ON table_name(column_name1,column_name2);
  • 查询某表上的索引
SHOW INDEXES FROM table_name;
  • 删除索引
DROP INDEX index_name ON table_name;

==高性能索引策略==

  1. 在WHERE中独立使用列，尽量避免其参与运算，如： WHERE age > 30
  2. 左前缀索引：索引构建于字段的最左侧的多少个字符，要通过索引选择性来评估
  3. 多列索引：使用 AND 连接的多个查询条件情况更适合使用多列索引，而非多个单键索引,如：
CREATE INDEX gender_age ON students(Gender,Age);
SELECT * FROM students WHERE Gender='M' AND Age>=19;

  1. ==选择合适的索引列次序：选择性最高的放左侧==

    • 对于下面的查询语句，建立合适的索引导致查询性能差异很大，可建立的索引为

CREATE INDEX gender_age ON students(Gender,Age);

CREATE INDEX age_gender ON students(Age,Gender); ==> 性能优先

  • 建立组合索引的原则：按照精确性高的放左侧、精确性低的放右侧的顺序建立索引
#因为gender字段为精确查询，不涉及范围查询，所以精确性高，而age字段涉及到范围查询，精确性低，所以：
CREATE INDEX age_gender ON students(Age,Gender);
SELECT * FROM students WHERE Gender='M' AND Age>=19;
  • 可以在 SELECT 语句前面加上 EXPLAIN 来查看查询语句的执行过程和效率。
标签：

相关文章