1, MySQL index
1. Basic concepts of MySQL index
An index is a data structure that helps MySQL get data efficiently. We can simply understand it as: it is a data structure for fast search and sorting.
It can be used to quickly query specific records in database tables, and all data types can be indexed.
Mysql indexes mainly have two structures: B+Tree index and Hash index
1) Advantages and disadvantages
- It can greatly improve the retrieval speed of MySQL
- Indexing greatly reduces the amount of data that the server needs to scan
- Indexes help the server avoid sorting and temporary tables
- Indexes can change random IO into sequential io
- Although the index greatly improves the query speed, it will reduce the speed of updating the table, such as INSERT, UPDATE and DELETE. When updating tables, MySQL saves not only data but also index files.
- Index files that take up disk space when indexing. Generally, this problem is not too serious, but if you create multiple composite indexes on a large table, the index file will expand rapidly.
- If a data column contains many duplicate contents, indexing it will not have much practical effect.
- For very small tables, simple full table scanning is more efficient in most cases;
- An index with no restrictions applied, which can be created in any data type.
- The constraints of the field itself can determine whether its value is empty or unique.
- After creating this type of index, users can query through the index when querying.
- Use the UNIQUE parameter to set a UNIQUE index.
- When creating the index, the index value must be unique. Through the unique index, users can quickly locate a record
- A primary key is a special unique index.
Full text index
- Use the FULLTEXT parameter to set the index to a full-text index.
- Full TEXT indexes can only be created on fields of type CHAR, VARCHAR, or TEXT. When querying string type fields with large amount of data, using full-TEXT index can improve the query speed.
- By default, applying full-text search is case insensitive. If the indexed columns use binary sorting, case sensitive full-text indexing can be performed.
- As the name suggests, a single column index is an index that corresponds to only one field.
- The conditions for applying the index only need to ensure that the index value corresponds to a field.
- Can include normal, unique, full-text indexes
Multi column index
- Multi column index is to create an index on multiple fields of a table.
- The index points to the corresponding fields when it is created. Users can query through these fields.
- To apply the index, the user must use the first of these fields.
2. Create index
1) General index
- There can be multiple index es in a table
- The value of the field can be repeated and can be assigned null
- Index is usually configured on the field in the where condition
- The flag of the index field is mul
Create index when creating table
CREATE TABLE Table name( Field list, index(Field name), index(Field name), );
Create an index in an existing table
CREATE INDEX Index name ON Table name(Field name);
3. View index
DESC Table name; # Pay attention to the Key column or SHOW INDEX FROM Table name \G
4. Delete index
DROP INDEX Index name ON library.Table name;
2, User and authorization
1) Create user and authorize
GRANT Permission list ON Library name.Table name TO 'user name'@'Client address' IDENTIFIED BY 'password' WITH GRANT OPTION;
Permission list: the user's operation permissions, such as SELECT, INSERT, UPDATE, etc. if you want to grant the required permissions, use ALL
Table name: table name. If you want to grant the user the corresponding operation permissions on all databases and tables, it can be represented by *, such as **
WITH GRANT OPTION: the user has authorization permission
# Grant zzg user permission to log in locally mysql> grant select,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn'; # Grant zzg users the right to log in at any address GRANT SELECT, INSERT, UPDATE(phone_number,email) ON nsd2021.employees to zzg@'%' IDENTIFIED BY 'NSD2021@tedu.cn';
Client connection test
# Install mysql/mariadb client [root@zzgrhel8 ~]# yum install -y mariadb [root@zzgrhel8 ~]# mysql -h server - u username - p password
2. Relevant query instructions
1) View user information
2) Display the login user's own permissions
3) The administrator can view the permissions of the specified user. If the user does not exist, an error is reported
SHOW GRANTS FOR user name@'Client address';
4) Users modify their passwords
5) The administrator modifies the specified user password
SET PASSWORD FOR user name@'Client address'=password('password');
6) Delete user
DROP USER user name@'Client address';
3. Authorization database mysql
1) Correlation table
User: records the existing authorized users and permissions. This table is mainly concerned with the host and user fields
db: records the access rights of existing authorized users to the database. This table is mainly concerned with the host, db and user fields
tables_priv: records the access rights of an authorized user to the table
columns_priv: records the access rights of the existing authorized user to the field
mysql> grant select,insert,update(phone_number,email) on nsd2021.employees to zzg@'localhost' identified by 'NSD2021@tedu.cn';
2) View all authorized users
mysql> select user, host from mysql.user; +-----------+-----------+ | user | host | +-----------+-----------+ | root | % | | tom | % | | zzg | % | | mysql.sys | localhost | | root | localhost | | zzg | localhost | +-----------+-----------+ 6 rows in set (0.01 sec)
3) Permission to query zzg@ '%'
mysql> show grants for zzg@'%'; mysql> select host, user, db from mysql.db; +-----------+-----------+---------+ | host | user | db | +-----------+-----------+---------+ | % | tom | nsd2021 | | localhost | mysql.sys | sys | +-----------+-----------+---------+ 2 rows in set (0.00 sec) mysql> select * from tables_priv where User like '%zzg%'\G *************************** 1. row *************************** Host: localhost Db: nsd2021 User: zzg Table_name: employees Grantor: root@localhost Timestamp: 0000-00-00 00:00:00 Table_priv: Select,Insert Column_priv: Update 1 row in set (0.00 sec)
4. Revoke authority
REVOKE Permission list ON Library name.Table name FROM user name@'Client address';
# See what permissions the user has SELECT host, user FROM mysql.user;
# View permissions SHOW GRANTS FOR user name@'Client address';
# Revoke authorization REVOKE GRANT OPTION ON *.* FROM user name@'Client address';
# Revoke user delete permission REVOKE DELETE ON *.* FROM user name@'Client address';
# Create tom user with authorization authority mysql> grant all on *.* to tom@'%' identified by 'NSD2021@tedu.cn' with grant option;
# After tom logs in, create jerry user [root@zzgrhel8 ~]# mysql -utom -pNSD2021@tedu.cn -h192.168.1.11 MySQL [(none)]> grant select on nsd2021.* to 'jerry'@'%' identified by 'NSD2021@tedu.cn';
5.root password recovery
Stop MySQL service
Skip authorization table and start MySQL service program
Change root password
Restart MySQL service program in normal mode
# Stop MySQL service [root@mysql1 ~]# systemctl stop mysqld
# Modify the configuration file, skip the authorization table and start the MySQL service program [root@mysql1 ~]# vim /etc/my.cnf [mysqld] skip-grant-tables ... ...
# Start service [root@mysql1 ~]# systemctl start mysqld
# Change root password [root@mysql1 ~]# mysql mysql> update mysql.user set authentication_string=password('123456') -> where user='root' and host='localhost'; Query OK, 1 row affected, 1 warning (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 1 mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)
# Restart MySQL service program in normal mode [root@mysql1 ~]# systemctl stop mysqld [root@mysql1 ~]# vim /etc/my.cnf [mysqld] # skip-grant-tables ... ... [root@mysql1 ~]# systemctl start mysqld [root@mysql1 ~]# mysql -uroot -p123456