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。
3. or和union的效率
(1)不同字段,将or改为union。
(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。
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条/秒(特殊再议)
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计算。
优化前:
优化后:
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);

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 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 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.

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.

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

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.

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 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.


Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

ZendStudio 13.5.1 Mac
Powerful PHP integrated development environment

VSCode Windows 64-bit Download
A free and powerful IDE editor launched by Microsoft

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 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.