MySQL优化之BTree索引使用规则
MySQL优化之BTree索引使用规则 从一道题开始分析: 假设某个表有一个联合索引(c1,c2,c3,c4)一下mdash;mdash;只能使用该联合索
MySQL优化之BTree索引使用规则
从一道题开始分析:
假设某个表有一个联合索引(c1,c2,c3,c4)一下——只能使用该联合索引的c1,c2,c3部分
A where c1=x and c2=x and c4>x and c3=x
B where c1=x and c2=x and c4=x order by c3
C where c1=x and c4= x group by c3,c2
D where c1=? and c5=? order by c2,c3
E where c1=? and c2=? and c5=? order by c2,c3
有谁知道下面A-E能否可以使用索引!!为什么?
OK;开始
创建表:
insert into t
values
('a1','a2','a3','a4','a5'),
('b1','b2','b3','b4','b5');
插入数据:
insert into t
values
('a1','a2','a3','a4','a5'),
('b1','b2','b3','b4','b5');
添加索引:
alter table t add index c1234(c1,c2,c3,c4);
对第一种情况:说明c1,,c2,c3,c4被使用
稍作改变:
使用group by 一般先生成临时文件,在进行排序
order by 哪?同上面类似啦
稍微改变一下,分析:知道原理都很容易啦!
上面问题答案是多少?反正我是不知道!
总结规律可得:
--------------------------------------分割线 --------------------------------------
Ubuntu 14.04下安装MySQL
《MySQL权威指南(原书第2版)》清晰中文扫描版 PDF
Ubuntu 14.04 LTS 安装 LNMP Nginx\PHP5 (PHP-FPM)\MySQL
Ubuntu 14.04下搭建MySQL主从服务器
Ubuntu 12.04 LTS 构建高可用分布式 MySQL 集群
Ubuntu 12.04下源代码安装MySQL5.6以及Python-MySQLdb
MySQL-5.5.38通用二进制安装
--------------------------------------分割线 --------------------------------------
本文永久更新链接地址:

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

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics

How to improve performance by optimizing the AVG function in MySQL MySQL is a popular relational database management system that contains many powerful functions and functions. The AVG function is widely used in calculating averages, but because this function needs to traverse the entire data set, it will cause performance problems in the case of large-scale data. This article will introduce in detail how to optimize the AVG function through MySQL to improve performance. 1. Using indexes Indexes are the most important part of MySQL optimization.

MySQL is a relational database management system widely used in the field of e-commerce. In e-commerce applications, it is crucial to optimize and secure MySQL. This article will analyze MySQL’s optimization and security project experience in e-commerce applications. 1. Performance optimization database architecture design: In e-commerce applications, database design is the key. Reasonable table structure design and index design can improve the query performance of the database. At the same time, using table splitting and partitioning technology can reduce the amount of data in a single table and improve query efficiency.

MySQL is a widely used open source database management system for storing and managing large amounts of data. However, when using MySQL, you may encounter a variety of problems, from simple syntax errors to more complex performance issues and glitches. In this article, we will explore some of the most common MySQL problems and solutions. Connection Problems Connection problems are common. If you cannot connect to the MySQL server, please check the following points: 1) Whether the MySQL server is running 2) Whether the network connection is normal 3) MySQ

How to properly configure and optimize MySQL's double-write buffering technology Introduction: MySQL's double-write buffering technology is an important technology that improves data security and performance. This article will introduce how to properly configure and optimize MySQL's double-write buffering technology to better protect data and improve database performance. 1. What is double-write buffering technology? Double-write buffering technology is an I/O optimization technology of MySQL. It can significantly reduce the number of disk I/O operations and improve the write performance of the database. When MySQL performs a write operation, first

MySQL database is a common relational database. As the amount of data in the database increases and query requirements change, underlying optimization becomes particularly important. In the process of underlying optimization of MySQL, SQL statement optimization is a crucial task. This article will discuss common techniques and principles for SQL statement optimization and provide specific code examples. First of all, SQL statement optimization needs to consider the following aspects: index optimization, query statement optimization, stored procedure and trigger optimization, etc. In these aspects, we will have

How to optimize MySQL connection number management MySQL is a popular relational database management system that is widely used in various websites and applications. In the actual application process, MySQL connection number management is a very important issue, especially in high concurrency situations. Reasonable management of the number of connections can improve the performance and stability of the system. This article will introduce how to optimize MySQL connection number management, including detailed code examples. 1. Understand connection number management In MySQL, the number of connections refers to the number of connections that the system can connect at the same time.

As the amount of data continues to increase and the number of users continues to increase, databases have become an indispensable part of modern enterprises. Therefore, the improvement and optimization of database efficiency is crucial to the operation and development of enterprises. In the database optimization process, MySql optimization and SQL optimization are the two most common optimization strategies. MySQL is a widely used open source relational database management system. Its optimization strategy is mainly aimed at the MySql system itself, and SQL is a structured query language that is often used with various data

As the amount of data continues to increase, database performance optimization becomes more and more important. As one of the most popular relational database management systems, MySQL also has many techniques and methods for performance optimization. This article will introduce some MySQL database optimization techniques to improve query efficiency. Avoid Excessive Query Querying is one of the most common database operations, but too many queries often lead to performance bottlenecks. Therefore, avoiding executing too many queries is the first step in optimizing database performance. This can be achieved by caching results, using indexes, and memory-based tables.
