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

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)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

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.

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.

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

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

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.

The difference between MySql and SQL optimization: The difference between MySQL optimization strategy and SQL optimization strategy The difference between MySql and SQL optimization: The difference between MySQL optimization strategy and SQL optimization strategy Jun 15, 2023 am 09:52 AM

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

MySQL database optimization tips: improve query efficiency MySQL database optimization tips: improve query efficiency Jun 15, 2023 pm 11:12 PM

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.

See all articles