一、MySQL概述

1.1、MySQL文件含義

通過如下命令查看

show variables like '%dir%';

MySQL文件位置及含義

名稱

值

備注

basedir

/usr/

安裝路徑

character_sets_dir

/usr/share/mysql-8.0/charsets/

保存字符集目錄

datadir

/var/lib/mysql/

數據存放路徑

lc_messages_dir

/usr/share/mysql-8.0/

plugin_dir

/usr/lib64/mysql/plugin/

插件

slave_load_tmpdir

/tmp

緩存文件

tmpdir

/tmp

緩存文件

配置文件位置

Linux:/etc/my.cnf

win:C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

1.2、MySQL主要配置文件

二進制日志log-bin：用于主從復制

錯誤日志log-error：默認關閉，記錄嚴重警告和錯誤信息，啟動和關閉的詳細信息等。

查詢日志：默認關閉，可顯式指定，記錄慢查詢日志

數據文件：

MyISAM中： 1. frm 存放表結構

?2. myd 存放表數據

?3. myd 存放表索引

InnoDB 中：ibd文件存放數據

1.3、MySQL引擎

查詢引擎

show engines;

show variables like '%storage_engine%'

MyISAM

InnoDB

構成上的區別：

每個MyISAM在磁盤上存儲成三個文件。第一個文件的名字以表的名字開始，擴展名指出文件類型。 .frm文件存儲表定義。 數據文件的擴展名為.MYD (MYData)。 索引文件的擴展名是.MYI (MYIndex)。

基于磁盤的資源是InnoDB表空間數據文件和它的日志文件，InnoDB 表的大小只受限于操作系統文件的大小，一般為 2GB

事務處理上方面：

MyISAM類型的表強調的是性能，其執行數度比InnoDB類型更快，但是不提供事務支持

InnoDB提供事務支持事務，外部鍵(foreign key)等高級數據庫功能

SELECT、UPDATE、INSERT、Delete操作

如果執行大量的SELECT，MyISAM是更好的選擇

1.如果你的數據執行大量的INSERT或UPDATE，出于性能方面的考慮，應該使用InnoDB表 2.DELETE FROM table時，InnoDB不會重新建立表，而是一行一行的刪除。 3.LOAD TABLE FROM MASTER操作對InnoDB是不起作用的，解決方法是首先把InnoDB表改成MyISAM表，導入數據后再改成InnoDB表，但是對于使用的額外的InnoDB特性(例如外鍵)的表不適用

對AUTO_INCREMENT的操作

每表一個AUTO_INCREMEN列的內部處理。 MyISAM為INSERT和UPDATE操作自動更新這一列。這使得AUTO_INCREMENT列更快(至少10%)。在序列頂的值被刪除之后就不能再利用。(當AUTO_INCREMENT列被定義為多列索引的最后一列，可以出現重使用從序列頂部刪除的值的情況)。 AUTO_INCREMENT值可用ALTER TABLE或myisamch來重置 對于AUTO_INCREMENT類型的字段，InnoDB中必須包含只有該字段的索引，但是在MyISAM表中，可以和其他字段一起建立聯合索引 更好和更快的auto_increment處理

如果你為一個表指定AUTO_INCREMENT列，在數據詞典里的InnoDB表句柄包含一個名為自動增長計數器的計數器，它被用在為該列賦新值。 自動增長計數器僅被存儲在主內存中，而不是存在磁盤上 關于該計算器的算法實現，請參考 AUTO_INCREMENT列在InnoDB里如何工作

表的具體行數

select count(*) from table,MyISAM只要簡單的讀出保存好的行數，注意的是，當count(*)語句包含 where條件時，兩種表的操作是一樣的

InnoDB 中不保存表的具體行數，也就是說，執行select count(*) from table時，InnoDB要掃描一遍整個表來計算有多少行

鎖

表鎖

提供行鎖(locking on row level)，提供與 Oracle 類型一致的不加鎖讀取(non-locking read in SELECTs)，另外，InnoDB表的行鎖也不是絕對的，如果在執行一個SQL語句時MySQL不能確定要掃描的范圍，InnoDB表同樣會鎖全表， 例如update table set num=1 where name like "%aaa%"

二、索引優化分析

2.1、什么是索引

MySQL官方的定義為：

索引(Index)是幫助MySQL高效地獲取數據的數據結構

索引的本質是數據結構

可簡單的理解為“排好序的快速查找數據結構”

2.2、索引分類

索引類型

索引含義

單值索引

一個索引僅包含一個列

唯一索引

索引列的值必須唯一，可以有空值

復合索引

一個索引包含多個列

2.3、基本語法

2.3.1、創建

方法一：

CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));

方法二：

ALTER mytable ADD [UNIQUE] INDEX [indexname] on (columnname(length));

2.3.2、刪除

DROP INDEX [indexName] ON mytable;

2.3.3、查看

SHOW INDEX FROM table_name;

2.4、explain

2.4.1、基本語法

EXPLAIN select語句;

2.4.2、字段解釋

id：select查詢的序列號，包含一組數字，表示select字句或操作表的順序

id相同，執行順序自上向下

id不同，如果是子查詢，id的序號會遞增，id值越大優先級越高，越先被執行

id相同不同，同時存在

select_type

id

select_type

含義

1

SIMPLE

簡單select查詢

2

PRIMARY

包含復雜查詢的最外層查詢

3

SUBQUERY

子查詢

4

DERIVED

衍生，遞歸執行，結果保存至臨時表

5

UNION

若第二個SELECT出現在UNION之后，標記為UNION

6

UNION RESULT

從UNION表獲取結果的SELECT

table 這一行的數據關于哪張表

partitions

type

從最好到最差排序 system>const>eq_ref>ref>range>index>ALL

類型

含義

system

表中只有一行數據，等于系統表

const

通過索引一次就找到了，被視為常量

eq_ref

唯一性索引掃描，表中只有一個記錄匹配

ref

非唯一性索引掃描，表中有多個記錄匹配

range

范圍

index

全索引掃描

ALL

全表掃描

possible_keys

可能會在該表上使用的索引，一個或者多個

查詢字段上存在的索引將被列出，不一定實際使用

key 實際使用的索引，如果為NULL，未使用索引；若有覆蓋索引(從索引就可以獲得數據，不需要查表)，則僅在key字段出現

key_len 索引字段的最大可能長度，并非實際長度

列類型

KEY_LEN

備注

id int

key_len = 4+1

int為4bytes,允許為NULL,加1byte

id bigint not null

key_len=8

bigint為8bytes

user char(30) utf8

key_len=30*3+1

utf8每個字符為3bytes,允許為NULL,加1byte

user varchar(30) not null utf8

key_len=30*3+2

utf8每個字符為3bytes,變長數據類型,加2bytes

user varchar(30) utf8

key_len=30*3+2+1

utf8每個字符為3bytes,允許為NULL,加1byte,變長數據類型,加2bytes

detail text(10) utf8

key_len=30*3+2+1

TEXT截取部分,被視為動態列類型。

ref 引用的字段，為NULL未引用

rows 根據表統計信息和索引選用情況，大致估算出所需要讀取的行數

filtered

Extra 不適合包含在其他列但十分重要的信息

Using filesort 使用外部排序，不使用索引的排序；無法使用索引完成的排序成為“文件排序”

Using temporary 使用了臨時表存儲中間結果

Using index 覆蓋索引

Using where 使用了where

Using join buffer 使用了連接緩存

Impossible where 不存在的條件

select tables optimized away 沒有GROUP BY的情況下，優化MIN/MAX或者對于MyISAM存儲引擎優化COUNT(*)操作，查詢計劃生成階段即完成優化

distinct 使用了distinct

2.5、join語句的優化

盡可能減少Join語句中的NestedLoop的循環總次數；“ 永遠用小結果集驅動大的結果集”。

優先優化NestedLoop的內層循環;

保證Join語句中被驅動表上Join條件字段已經被索引;

當無法保證被驅動表的Join條件字段被索引且內存資源充足的前提下，不要太吝惜JoinBuffer的設置;

2.6、索引失效的情況

全值匹配我最愛

最佳左前綴法則

不在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描

存儲引擎不能使用索引中范圍條件右邊的列

盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，減少select*

mysql在使用不等于(!=或者<> )的時候無法使用索引會導致全表掃描圖

is null ,is not null也無法使用索引

like以通配符開頭('%ab...')mysq|索引失效會變成全表掃描的操作

字符串不加單引號索引失效

少用or,用它來連接時會索引失效

總結

以上是生活随笔為你收集整理的mysql优化varchar索引_MySQL优化--概述以及索引优化分析的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。