MySQL index underlying data structure and algorithm
MySQL performance optimization principle - Part 1
The first two articles have finished the index underlying data structure and performance optimization principle basic concepts. This article will talk about specific practices. Divided into two parts, this is the first part of practice. For a data-centric application, the quality of the database directly affects the performance of the program, so database performance is crucial. Generally speaking, to ensure the efficiency of the database, the following four aspects must be done:
Database table design
SQL statement optimization
Database parameter configuration
Appropriate hardware resources and operating system
In addition, using appropriate stored procedures can also improve performance. This order also shows the impact of the four aspects on performance. Database table designA common understanding of the three paradigms is of great benefit to database design. In database design, in order to better apply the three paradigms, it is necessary to understand the three paradigms popularly. First normal form: 1NF - ensuring atomicity is the atomicity constraint
on the attribute, which requires the attribute (column) to be atomic and cannot be decomposed; (as long as All relational databases satisfy 1NF)
Second normal form: 2NF - ensure that each column in the table is related to the primary key is the unique constraint on
records , requirements The record has a unique identifier, that is, the uniqueness of the entity;
First satisfy 1NF, and then each table must have a primary key, and ensure that each column is related to the primary key, not part of the primary key (mainly for joint primary keys). In other words, only one type of data is stored in a table instead of multiple types of data.
Error Demonstration: Wrong design of product order information
Correct Demonstration: Correct design of product order information
Third Normal Form: 3NF - Ensure that each column is directly related to the primary key, not indirectly related3NF is a constraint on
field redundancy, which requires that fields are not redundant.
The third normal form needs to ensure that each column of data in the data table is directly related to the primary key and cannot be indirectly related. Transitive dependencies are not allowed, for example, non-primary key column A depends on non-primary key column B, and non-primary key column B depends on the primary key.
1
关键字段 -> 非关键字段x -> 非关键字段y复制代码
Copy after login
Case 1:
For example, when designing an order data table, you can use the customer number as a foreign key to establish a corresponding relationship with the order table. You cannot add other information about the customer (such as name, company, etc.) fields to the order form. The design shown in the following two tables is a database table that satisfies the third normal form. ##Case 2:
Assume that the student relationship table is
Student
(Student number, name, age, college, college location, college phone number), the key word is the single keyword "student number", because there is the following determination relationship: <div class="code" style="position:relative; padding:0px; margin:0px;"><pre class="brush:php;toolbar:false;">(学号)-> (姓名、年龄、所在学院、学院地点、学院电话)复制代码</pre><div class="contentsignin">Copy after login</div></div> That is, there is a non-key field "college location" , the transfer function dependence of "college phone number" on the key field "student number".
It will also have data redundancy, update anomalies, insertion anomalies and deletion anomalies. Correctly, the student relationship table should be divided into the following two tables:
Student: (student number, name, age, college)
College: (college, location, phone number)
Advantages and disadvantages of normalization
Advantages of normalization:
##Less duplicate data and no redundancy;
Fast maintenance and updates;Normalized tables are smaller and can be run in memory. Disadvantages of normalization:
Querying often requires many associations, which increases the cost of querying. It may also invalidate some indexing strategies because normalization places columns in different tables that would otherwise belong to the same index in one table.
Advantages and disadvantages of denormalization
Advantages of denormalization:
Avoid correlation, almost all data can be displayed in one table .
Can design effective indexes. Disadvantages of denormalization:
There is a lot of redundant data, which is less troublesome to maintain. It is also easy to lose important information when deleting data.
dtl 数据事物语言(commit rollback savepoint)Ddl数据定义语言(create alter drop..)
Dcl(数据控制语言) grant revoke-- Show status 常用命令--查询本次会话Show session status like 'com_%'; //show session status like 'Com_select'--查询全局Show global status like 'com_%';-- 给某个用户授权grant all privileges on *.* to 'abc'@'%';--为什么这样授权 'abc' 表示用户名 '@' 表示host, 查看一下mysql->user表就知道了--回收权限revoke all on *.* from 'abc'@'%';--刷新权限[也可以不写]flush privileges;复制代码
Copy after login
SQL语句优化-show参数
MySQL客户端连接成功后,通过使用 show [session|global] status 命令可以提供服务器状态信息。其中的session来表示当前的连接的统计结果,global来表示自数据库上次启动至今的统计结果。默认是session级别的。
)ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film`(`id`,`name`) VALUES (3,'film0'),(1,'film1'),(2,'film2');DROP TABLE IF EXISTS `film_actor`;CREATE TABLE`film_actor`(`id` int(11) NOT NULL,`film_id` int(11) NOT NULL,`actor_id` int(11) NOT NULL,`remark` varchar(255) DEFAULT NULL,
INSERT INTO employees(name,age,position,hire_time)VALUES('ZhangSan',23,'Manager',NOW());INSERT INTO employees(name,age,position,hire_time)VALUES('HanMeimei', 23,'dev',NOW());INSERT INTO employees(name,age,position,hire_time) VALUES('Lucy',23,'dev',NOW());复制代码
Copy after login
全值匹配
1
EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan';复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22;复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22 AND position ='manager';复制代码
Copy after login
最左前缀法则
如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
1
EXPLAIN SELECT * FROM employees WHERE age = 22 AND position ='manager';复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE position = 'manager';复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE name = 'ZhangSan';复制代码
Copy after login
不在索引列上做任何操作
不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
1
EXPLAIN SELECT * FROM employees WHERE name = 'ZhangSan';EXPLAIN SELECT * FROM employees WHERE left(name,3) = 'ZhangSan';复制代码
Copy after login
给hire_time增加一个普通索引:
1
ALTER TABLE `employees`ADD INDEX `idx_hire_time` (`hire_time`) USING BTREE;复制代码
Copy after login
1
EXPLAIN select * from employees where date(hire_time) ='2020-09-30';复制代码
Copy after login
转化为日期范围查询,会走索引:
1
EXPLAIN select * from employees where hire_time >='2020-09-30 00:00:00' andhire_time <='2020-09-30 23:59:59';复制代码
Copy after login
还原最初索引状态
1
ALTER TABLE `employees`DROP INDEX `idx_hire_time`;复制代码
Copy after login
存储引擎不能使用索引中范围条件右边的列
1
EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 22 AND position ='manager';EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age > 22 AND position ='manager';复制代码
Copy after login
尽量使用覆盖索引
尽量使用覆盖索引(只访问索引的查询(索引列包含查询列)),减少select *语句。
1
EXPLAIN SELECT name,age FROM employees WHERE name= 'ZhangSan' AND age = 23 AND position ='manager';复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE name= 'ZhangSan' AND age = 23 AND position ='manager';复制代码
Copy after login
mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描
1
EXPLAIN SELECT * FROM employees WHERE name != 'ZhangSan';复制代码
Copy after login
is null、is not null 也无法使用索引
1
EXPLAIN SELECT * FROM employees WHERE name is null复制代码
Copy after login
like以通配符开头('%abc...')mysql索引失效会变成全表扫描操作
1
EXPLAIN SELECT * FROM employees WHERE name like '%Zhang'复制代码
Copy after login
1
EXPLAIN SELECT * FROM employees WHERE name like 'Zhang%'复制代码
Copy after login
问题:解决like'%字符串%'索引不被使用的方法?
使用覆盖索引,查询字段必须是建立覆盖索引字段
1
EXPLAIN SELECT name,age,position FROM employees WHERE name like '%Zhang%';复制代码
Copy after login
如果不能使用覆盖索引则可能需要借助搜索引擎
字符串不加单引号索引失效
1
2
EXPLAIN SELECT * FROM employees WHERE name = '1000';
EXPLAIN SELECT * FROM employees WHERE name = 1000;复制代码
The above is the detailed content of Practice (1)--MySQL performance optimization. For more information, please follow other related articles on the PHP Chinese website!
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn