search
HomeDatabaseMysql TutorialMySql技巧个人笔记_MySQL

1.数据null时sum的用法

 

mysql数据库SUM(A+B)不一定等于SUM(A)+SUM(B),当A或B为NULL时,SUM(A+B)=NULL。

 

2.or改为in

 

同一字段,将or改写为in()。OR效率:O(n);IN效率:O(Log n);当n很大时,OR会慢很多。注意控制in的个数,建议n小于200。

MySql技巧个人笔记_MySQL

3. or和union的效率

 

(1)不同字段,将or改为union。

MySql技巧个人笔记_MySQL

(2)相同字段(针对单表操作)

 

对于索引列来最好使用union all,因复杂的查询【包含运算等】将使or、in放弃索引而全表扫描,除非你能确定or、in会使用索引。对于只有非索引字段来说你就老老实实的用or 或者in,因为 非索引字段本来要全表扫描而union all 只成倍增加表扫描的次数。对于及有索引字段【索引字段有效】又包含非索引字段来时,按理你也使用or 、in或者union all 都可以,但是我推荐使用or、in。

 

4.union all与union

 

若无需对结果进行去重,则用union all 而非union;union会自动对结果去重,有去重开销。

 

5.Group by去除排序

 

Gourp by 实现分组、自动排序。则无需排序:order by null。

MySql技巧个人笔记_MySQL

6.将字符转换为数字

 

数字型VS字符串型索引:数字型更高效、查询更快、占用空间更小。

 

7.拒绝大SQL

 

 拒绝大sql,拆解成多条简单sql:

 

(1)可能一条大sql就把整个数据库堵死;

 

(2)简单sql缓存命中率更高;

 

(3)减少锁表时间,特别是MyISAM;

 

(4)用上多cpu,一条sql只能在一个cpu中运行。

 

8. Load data导数据

 

  批量数据块导入:

 

(1)成批装载比单行装载更快,不需要每次都刷新缓存;

 

(2)无索引装载比索引装载更快;

 

(3)Insert values,values,values减少索引刷新;

 

(4)Load data 比Insert快约20倍

 

尽量不要用insert….select….

 

(1) 延迟

 

(2)同步出错

 

9.打散大批量更新

 

(1)大批量更新凌晨操作,避开高峰

 

(2)凌晨不限制

 

(3)白天上限默认为100条/秒(特殊再议)

MySql技巧个人笔记_MySQL

10. mysql int(3)与int(11)的区别

 

总结,int(M) zerofill,加上zerofill后M才表现出有点点效果,比如 int(3) zerofill,你插入到数据库里的是10,则实际插入为010,也就是在前面补充加了一个0.如果int(3)和int(10)不加zerofill,则它们没有什么区别.M不是用来限制int个数的.

 

注意:这里的M代表的并不是存储在数据库中的具体的长度,以前总是会误以为int(3)只能存储3个长度的数字,int(11)就会存储11个长度的数字,这是大错特错的。

 

 

其实当我们在选择使用int的类型的时候,不论是int(3)还是int(11),它在数据库里面存储的都是4个字节的长度,在使用int(3)的时候如果你输入的是10,会默认给你存储位010,也就是说这个3代表的是默认的一个长度,当你不足3位时,会帮你不全,当你超过3位时,就没有任何的影响。

 

11.count(distinct field)是魔鬼

Count(distinct field)查询效率极低,数据量大时甚至会爆出内存不足。优化技巧:使用临时表概念,先把distinct的field 经过group by过滤后,再对其进行count计算。

 

优化前:

MySql技巧个人笔记_MySQL

优化后:

MySql技巧个人笔记_MySQL

12.mysql函数

 

(1)replace函数

 

Update tr_app_data  set content = replace(content,'SGSN206','SGSN1') where app_data_cat='AlarmMonitor_Widget_195'

 

(2)concat函数

 

select concat(LAC,'_',CI) from `sheet1`

 

(3)IFNULL函数

 

IFNULL(expr1,expr2) ,如果expr1不是NULL,IFNULL()返回expr1,否则它返回expr2。IFNULL()返回一个数字或字符串值。

 

(4)IF函数

 

如果expr1是TRUE(expr10且expr1NULL),那么IF()返回expr2,否则它返回expr3。IF()返回一个数字或字符串值,取决于它被使用的上下文。

 

(5)CASE WHEN函数

 

Case具有两种格式。简单Case函数和Case搜索函数

 

--简单Case函数 如果case有条件,when 只能是对条件值的罗列,不能再加条件,否则出错  

 

CASE sex   

 

WHEN '1' THEN '男'   

 

WHEN '2' THEN '女'   

 

ELSE '其他' END  

 

如果case 没有条件,when 里面可以加个条件判断

 

--Case搜索函数  

 

CASE WHEN sex = '1' THEN '男'   

 

WHEN sex = '2' THEN '女'   

 

ELSE '其他' END  

 

13.单表去重并保留id最小的一条

 

数据库现在不支持这样的操作(对一个表进行select等子操作后,然后对该表做delete或者updata这类的操作。so,可以用临时表解决。如下:

 

DELETE FROM table_test WHERE id NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM table_test AS t GROUP BY uid) t1);

Statement
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
MySQL: Essential Skills for Beginners to MasterMySQL: Essential Skills for Beginners to MasterApr 18, 2025 am 12:24 AM

MySQL is suitable for beginners to learn database skills. 1. Install MySQL server and client tools. 2. Understand basic SQL queries, such as SELECT. 3. Master data operations: create tables, insert, update, and delete data. 4. Learn advanced skills: subquery and window functions. 5. Debugging and optimization: Check syntax, use indexes, avoid SELECT*, and use LIMIT.

MySQL: Structured Data and Relational DatabasesMySQL: Structured Data and Relational DatabasesApr 18, 2025 am 12:22 AM

MySQL efficiently manages structured data through table structure and SQL query, and implements inter-table relationships through foreign keys. 1. Define the data format and type when creating a table. 2. Use foreign keys to establish relationships between tables. 3. Improve performance through indexing and query optimization. 4. Regularly backup and monitor databases to ensure data security and performance optimization.

MySQL: Key Features and Capabilities ExplainedMySQL: Key Features and Capabilities ExplainedApr 18, 2025 am 12:17 AM

MySQL is an open source relational database management system that is widely used in Web development. Its key features include: 1. Supports multiple storage engines, such as InnoDB and MyISAM, suitable for different scenarios; 2. Provides master-slave replication functions to facilitate load balancing and data backup; 3. Improve query efficiency through query optimization and index use.

The Purpose of SQL: Interacting with MySQL DatabasesThe Purpose of SQL: Interacting with MySQL DatabasesApr 18, 2025 am 12:12 AM

SQL is used to interact with MySQL database to realize data addition, deletion, modification, inspection and database design. 1) SQL performs data operations through SELECT, INSERT, UPDATE, DELETE statements; 2) Use CREATE, ALTER, DROP statements for database design and management; 3) Complex queries and data analysis are implemented through SQL to improve business decision-making efficiency.

MySQL for Beginners: Getting Started with Database ManagementMySQL for Beginners: Getting Started with Database ManagementApr 18, 2025 am 12:10 AM

The basic operations of MySQL include creating databases, tables, and using SQL to perform CRUD operations on data. 1. Create a database: CREATEDATABASEmy_first_db; 2. Create a table: CREATETABLEbooks(idINTAUTO_INCREMENTPRIMARYKEY, titleVARCHAR(100)NOTNULL, authorVARCHAR(100)NOTNULL, published_yearINT); 3. Insert data: INSERTINTObooks(title, author, published_year)VA

MySQL's Role: Databases in Web ApplicationsMySQL's Role: Databases in Web ApplicationsApr 17, 2025 am 12:23 AM

The main role of MySQL in web applications is to store and manage data. 1.MySQL efficiently processes user information, product catalogs, transaction records and other data. 2. Through SQL query, developers can extract information from the database to generate dynamic content. 3.MySQL works based on the client-server model to ensure acceptable query speed.

MySQL: Building Your First DatabaseMySQL: Building Your First DatabaseApr 17, 2025 am 12:22 AM

The steps to build a MySQL database include: 1. Create a database and table, 2. Insert data, and 3. Conduct queries. First, use the CREATEDATABASE and CREATETABLE statements to create the database and table, then use the INSERTINTO statement to insert the data, and finally use the SELECT statement to query the data.

MySQL: A Beginner-Friendly Approach to Data StorageMySQL: A Beginner-Friendly Approach to Data StorageApr 17, 2025 am 12:21 AM

MySQL is suitable for beginners because it is easy to use and powerful. 1.MySQL is a relational database, and uses SQL for CRUD operations. 2. It is simple to install and requires the root user password to be configured. 3. Use INSERT, UPDATE, DELETE, and SELECT to perform data operations. 4. ORDERBY, WHERE and JOIN can be used for complex queries. 5. Debugging requires checking the syntax and use EXPLAIN to analyze the query. 6. Optimization suggestions include using indexes, choosing the right data type and good programming habits.

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Best Graphic Settings
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
Will R.E.P.O. Have Crossplay?
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

ZendStudio 13.5.1 Mac

ZendStudio 13.5.1 Mac

Powerful PHP integrated development environment

VSCode Windows 64-bit Download

VSCode Windows 64-bit Download

A free and powerful IDE editor launched by Microsoft

MantisBT

MantisBT

Mantis is an easy-to-deploy web-based defect tracking tool designed to aid in product defect tracking. It requires PHP, MySQL and a web server. Check out our demo and hosting services.

SecLists

SecLists

SecLists is the ultimate security tester's companion. It is a collection of various types of lists that are frequently used during security assessments, all in one place. SecLists helps make security testing more efficient and productive by conveniently providing all the lists a security tester might need. List types include usernames, passwords, URLs, fuzzing payloads, sensitive data patterns, web shells, and more. The tester can simply pull this repository onto a new test machine and he will have access to every type of list he needs.