SELECT count(*) FROM tablename
即使对于千万级别的数据mysql也能非常迅速的返回结果
而对于
SELECT count(*) FROM tablename WHERE…..
mysql的查询时间开始攀升
现当没有WHERE语句对于整个mysql的表进行count运算的时候
MyISAM类型的表中保存有总的行数,而当添加有WHERE限定语句的时候Mysql需要对整个表进行检索,从而得出count的数值
mysql的DISTINCT的关键字有很多你想不到的用处
1.在count 不重复的记录的时候能用到
比如SELECT COUNT( DISTINCT id ) FROM tablename;
就是计算talbebname表中id不同的记录有多少条
2,在需要返回记录不同的id的具体值的时候可以用
比如SELECT DISTINCT id FROM tablename;
返回talbebname表中不同的id的具体的值
3.上面的情况2对于需要返回mysql表中2列以上的结果时会有歧义
比如SELECT DISTINCT id, type FROM tablename;
实际上返回的是 id与type同时不相同的结果,也就是DISTINCT同时作用了两个字段,必须得id与tyoe都相同的才被排除了,与我们期望的结果不一样
4.这时候可以考虑使用group_concat函数来进行排除,不过这个mysql函数是在mysql4.1以上才支持的
5.其实还有另外一种解决方式,就是使用
SELECT id, type, count(DISTINCT id) FROM tablename
虽然这样的返回结果多了一列无用的count数据(或许你就需要这个我说的无用数据)
返回的结果是 只有id不同的所有结果和上面的4类型可以互补使用,就是看你需要什么样的数据了
分享到:
相关推荐
High Performance MySQL_ Optimization, Backups, Replication, and More (2nd Edition) ,国外知名数据库教程, PDF格式 英文版
Optimization of advanced querying features, such as full-text searches Four new appendices The book also includes chapters on benchmarking, profiling, backups, security, and tools and techniques to ...
对于mysql性能、架构的深入剖析,尤其是提高性能方面有独到的见解,对于有一定基础,向深入学习mysql的朋友值得一读
Beginning MySQL Database Design and Optimization From Novice to Professional
* Shows how to take advantage of MySQL’s built-in functions, minimizing the need to process data once it’s been retrieved from the database. * Demonstrates how to write and use advanced and complex...
Beginning_MySQL_Database_Design__Optimization
innodb_performance_optimization_final.pdf mysql_high_availability.pdf Mysql_logs_ebook.pdf mysql_performance_schema.pdf mysql_performance_tuning.pdf mysql_server_memory_usage_ebook.pdf
mysql优化,比较经典的一个,其实我是想下一个东西,唉,为什么一定要这个积分呢,还要字数限制,够了,快够了,加油。。
网站访问量越来越大,MySQL自然成为瓶颈,作为一个访问量很大的网站(日20万人次以上)的数据库系统,不可能指望 MySQL 默认的系统参数能够让 MySQL运行得非常顺畅。 通过在网络上查找资料和自己的尝试,对MySQL的...
SQL优化---将理论付诸实践(https://blog.csdn.net/tian330726/article/details/88775231)对应导入数据的代码
The use of optimization techniques is becoming essential to address rapidly increasing stringency of requirements for automotive systems. In particular, there is a growing interest in systematic ...
mysql 数据库开发、优化、维护。讲述详尽,通俗易懂
A comprehensive guide to performing query optimization, security and a whole host of other administrative tasks in MySQL 8 Table of Contents: Introduction to MySQL 8 Installing & Upgrading MySQL 8 ...
Performance Optimization.rar Performance Optimization.rar
matlab优化工具,可以方便的查询一些函数的使用方法
1.1 Data Uncertainty in Linear Optimization 3 1.2 Uncertain Linear Problems and their Robust Counterparts 7 1.3 Tractability of Robust Counterparts 16 1.4 Non-Affine Perturbations 23 1.5 Exercises 25 ...
a good reference to optimization
Query Optimization with MySQL 5.7 and MariaDB 10 Even newer tricks
Numerical_Optimization.pdfNumerical_Optimization.pdfNumerical_Optimization.pdfNumerical_Optimization.pdfNumerical_Optimization.pdfNumerical_Optimization.pdfNumerical_Optimization.pdfNumerical_...