Home Database Mysql Tutorial MySQL优化之BTree索引使用规则

MySQL优化之BTree索引使用规则

Jun 07, 2016 pm 04:45 PM
mysql optimization

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被使用

MySQL优化之BTree索引使用规则

稍作改变:

MySQL优化之BTree索引使用规则

MySQL优化之BTree索引使用规则

使用group by 一般先生成临时文件,在进行排序

order by 哪?同上面类似啦

MySQL优化之BTree索引使用规则

稍微改变一下,分析:知道原理都很容易啦!

MySQL优化之BTree索引使用规则

上面问题答案是多少?反正我是不知道!

总结规律可得:

MySQL优化之BTree索引使用规则

--------------------------------------分割线 --------------------------------------

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通用二进制安装

--------------------------------------分割线 --------------------------------------

本文永久更新链接地址:

linux

Statement of this 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

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

Video Face Swap

Video Face Swap

Swap faces in any video effortlessly with our completely free AI face swap tool!

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

How to optimize AVG function through MySQL to improve performance How to optimize AVG function through MySQL to improve performance May 11, 2023 am 08:00 AM

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 optimization based on TokuDB engine: improving writing and compression performance MySQL optimization based on TokuDB engine: improving writing and compression performance Jul 25, 2023 pm 11:45 PM

MySQL optimization based on TokuDB engine: improving writing and compression performance Introduction: As a commonly used relational database management system, MySQL is facing increasing writing pressure and storage requirements in the context of the big data era. To meet this challenge, TokuDB engine came into being. This article will introduce how to use the TokuDB engine to improve MySQL's writing performance and compression performance. 1. What is TokuDB engine? TokuDB engine is a big data-oriented engine designed to handle high write

How to achieve low-level optimization of MySQL: tips and best practices for advanced optimization of SQL statements How to achieve low-level optimization of MySQL: tips and best practices for advanced optimization of SQL statements Nov 08, 2023 pm 04:32 PM

MySQL is a widely used relational database management system commonly used for web application development and data storage. In practical applications, the underlying optimization of MySQL is particularly important, among which the advanced optimization of SQL statements is the key to improving database performance. This article will introduce some tips and best practices for implementing MySQL's underlying optimization, as well as specific code examples. Determine the query conditions When writing SQL statements, you must first clearly define the query conditions and avoid using unlimited wildcard queries, that is, avoid using "%" to open the query.

Analysis of MySQL optimization and security project experience in e-commerce applications Analysis of MySQL optimization and security project experience in e-commerce applications Nov 03, 2023 am 10:42 AM

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.

How to optimize MySQL connection number management How to optimize MySQL connection number management Mar 16, 2024 am 08:12 AM

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.

How to implement MySQL underlying optimization: common techniques and principles for SQL statement optimization How to implement MySQL underlying optimization: common techniques and principles for SQL statement optimization Nov 08, 2023 pm 08:19 PM

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

A complete collection of solutions to common MySQL problems A complete collection of solutions to common MySQL problems Jun 15, 2023 am 09:51 AM

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 How to properly configure and optimize MySQL's double-write buffering technology Jul 25, 2023 pm 01:01 PM

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

See all articles