MySQL数据库优化（七）：MySQL如何使用索引
对于联合索引(Gid,Cid,SId)，查询语句SELECTFROMstudentWHERECid=465176354;是否能够触发索引？大多数人都会说NO，实际上却是YES。索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。
mysql时间索引 mysql时间索引的使用
mysql时间索引 mysql时间索引的使用
大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。
通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。
想要尽快找到匹配 WHERE 子句的记录。
根据条件排除记录。如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。
做表连接查索引的字段可以是任意顺序的，如：询时从其他表中检索记录。
想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的
key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# = constant 子句。这样的话，
MySQL会为 MIN() 或 MAX() 表达式分别单独索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：
SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如 ORDER
BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。
有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：
SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
设有如下 SELECT 语句：
如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。
mysql全文索引 很慢，速度不如like的百分之一
l此外还有多种查看方式：SQL窗口查询、PL/SQL DEVELPOER 解释窗口、通过toad查看等涉及到中文索引时,使用mysql自带的索引在对中文索引时会有瓶颈的 建议使用sphinx 等全文索引引擎,同时在查询时避免使用 而使用具体的查询字段代替, mysql的性能优化有很多中方式
SHOW INNODB STATUS对数据库有大量数据的读写时,使用读写分离和数据库数据同步来实现数据的读和写
「进阶」MySQL中如何使用索引
2.2 平衡二叉树1、普通索引
Packed: NULL这是最基本的索引，它没有任何限制。
2、索引
它与前面的普通索引类似，不同的就是：索引列的值必须，但允许有空值。如果是组合索引，则列值的组合必须。
它是一种特殊的索引，不允许有空值。一般是在建表的时候同时创建主键索引。
4、聚簇索引
聚簇索引的索引顺序就是数据存储的物理存储顺序，这样能保证索引值相近的元组所存储的物理位置也相近。
5、全文索引（FULLTEXT）
全文索引只能创建在数据类型为VARCHAR或TEXT的列上，建立全文索引后，能够在建立了全文索引的列上进行全文查找。全文索引只能在MyISAM存储引擎的表中创建。
实际工作使用中，索引可以建立在单一列上，称为单列索引，也可以建立在多个列上，称为组合索引。
mysql有几种索引类型？使用索引时都有那些地方要注意？sql优化原则是什么？
mysql的索引类型及使用索引时的注意事项有：
一、普通索引。这是最基本的索引，它没有任何限制。它有以下几种创建方式：
1、创建索引
代码如下字符串默认都是自动压缩前缀和后缀中的空格。:
CREATE INDEX indexName ON mytable(username(length));
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length，下同。
2、修改表结构
代所以索引即便是在MySQL中也没有统一的标准，不同存储引擎的所实现的索引工作方式也并不一样。码如下:
ALTER mytable ADD INDEX [indexName] ON (username(length)) -- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX [indexName] (username(length)) );
-- 删除索引的语法：
DROP INDEX [indexName] ON mytable;
二、索引。它与前面的普通索引类似，不同的就是：索引列的值必须，但允许有空值。如果是组合索引，则列值的组合必须。它有以下几种创建方式：
代码如下:
CREATE UNIQUE INDEX indexName ON mytable(username(length))
-- 修改表结构
ALTER mytable ADD UNIQUE [indexName] ON (username(length))
-- 创建表的时候直接指定
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, UNIQUE [indexName] (username(length)) );
三、主键索引。它是一种特殊的索引，不允许有空值。一般是在建表的时候同时创建主键索引：
代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, PRIMARY KEY(ID) );
当然也可以用 ALTER 命令。记住：一个表只能有一个主键。
四、组合索引。为了形象地对比单列索引和组合索引，为表添加多个字段：
代码如下:
CREATE TABLE mytable( ID INT NOT NULL, username VARCHAR(16) NOT NULL, city VARCHAR(50) NOT NULL, age INT NOT NULL );
为了进一步榨取MySQL的效率，就要考虑建立组合索引。就是将 name, city, age建到一个索引里：
代码如下:
ALTER TABLE mytable ADD INDEX name_city_age (name(10),city,age);[code]
建表时，usernname长度为 16，这里用 10。这是因为一般情况下名字的长度不会超过10，这样会加速索引查询速度，还会减少索引文件的大小，提高INSERT的更新速度。
如果分别在 usernname，city，age上建立单列索引，让该表有3个单列索引，查询时和上述的组合索引效率也会大不一样，远远低于我们的组合索引。虽然此时有了三个索引，但MySQL只能用到其中的那个它认为似乎是最的单列索引。
建立这样的组合索引，其实是相当于分别建立了下面三组组合索引：usernname,city,age usernname,city usernname 为什么没有 city，age这样的组合索引呢？这是因为MySQL组合索引“最左前缀”的结果。简单的理解就是只从最左面的开始组合。并不是只要包含这三列的查询都会用到该组合索引，下面的几个SQL就会用到这个组合索引：
[code]
SELECT FROM mytable WHREE username="admin" AND city="郑州" SELECT FROM mytable WHREE username="admin"
Mysql 日期比较
在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配，应该是：
3.如果条件中有or，即使其中有条件带索引也不会使用。select NO, name, date, MAX, num, cost, describeS from Activity where Datediff(date,getdate())<0;
mysql的datediff()只能判断天数。
mysql数据库怎么建索引？
主键索引：不能为null，且内容只能。表名右击，菜单里面就有索引，然后选择要索引的字段。。确定就可以了
好难
create index 索引名称 on 表
(字FULLTEXT KEY `title` (`title`,`content`)段
);
mysql按主键排序为什么比索引快
例如，MYSQL文档介绍索引是一种方便快速查询数据的数据结构。用我们生活中的例子来讲，索引就好比书的目录，如果没有目录，每次你想要查找某些内容，你必须从头开始查找，这样的效率极其低下。
索引一般比较大，所以大部分情况下索引是存在磁盘的索引文件上，也有可能是存在数据文件上。
索引的种类有很多：主键索引（这是最常见的一种索引，主键不能为空且必须）、索引（相对于主键索引，它的值可以为空）、全文索引（在char、varchar、text类型可以使用）、普通索引、前缀索引。按照列数来区分：单一索引、组合索引（多字段组成）
2.MYSQL索引的数据结构
在讲解MYSQL索引的数据结构之前，我们先看看了解一下其他的数据结构，看看他们的优缺点进行对比。
2.1 二叉树
二叉树简单来说就是左大于右，在理想的情况下，他的查找速度就接近与二分法的性能O(log2n)。因为在内存排序的时间是非常快的，可以忽略不计，所以总的消耗时间就取决于IO的作次数。二叉树查找速度取决树高,每次查询接口都是一次IO作，也是性能的瓶颈所在。
但是也会有这种一种情况,同样也是二叉树，但是他的树非常高，导致查询一次需要多次IO作，效率及其低下
平衡二叉树可以解决二叉树不稳定导致查询效率低下的缺点。平衡二叉树的特点：树的左右层级相一层。在插入或者删除的情况下，通过2、普通索引左旋转或右旋转使得整个二叉树平衡，不会出现层级相很多的情况。平衡二叉树的性能接近二分法查找O(log2n)。
平衡二叉树查找id为8的记录，只需要IO作2次即可。但是仔细想一下，如果数据量很多呢？设数据表有100W的数据，根据O(log2n)计算，大约需要20次IO作。磁盘寻道大概需要10ms，总的查询时间为20 10 = 0.2，效率也比较低下。
还有就是平衡二叉树不支持范围查询，范围查询每次都需要从根遍历，效率及其低下。
2.3 B-树(改造二叉树成多叉树)
之前的几种树形结构适合与小数据量的内存查找，也叫做内查找。在1970年，R.Bayer和E.Mccreight提出了一种适合于外查找的平衡多叉树B-树。MYSQL数据文件是存在磁盘的，每次都是按照一页大小（一般而16K）读取内存。像二叉树、平衡二叉树，每次读取都要进行一次IO作，所以树越高IO作次数越多。想要提高
mysql 的索引怎么做自平衡的
1、理论上索引对顺序是敏感的，但是由于MySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引。1.由于索引扫描效率，因此能使用索引，尽量使用索引；
8.平衡索引与更新的关系。如果一个大表的更新作多于查询作，建议可以少建立索引；如果一个大表的查询作多于更新作则可以考虑多使用索引。2.对于取值基数较少的字段，如性别、状态、Check项，尽量使用位图索引；
3.由于NULL值在索引中没有定义，因此建议尽量不要在允许NULL值的列上建立索引，虽然Oracle宣称在允许NULL值的列上位图索引，也是有效的，但我们依然强烈建议可以将null指定为一个默认的值后再建立索引；
4.根据实际情况，一般对于列不超过10个，数据不超过1万行的表不要建立索引，此种情况在应用中往往能占40%以上；
5.一个表中不是建立的索引越多越好，相反过多的索引可能影响整体的性能，至少影响INSERT、DELETE和UPDATE的性能；
6.对于查询中的WHERE子句经常需要使用多个AND条件一起使用时，应建立联合索引，一般情况下，此种联合索引是标示一行记录的，而主键使用主键，并且尽量将值少的列放在前面；
7.对于多表联合查询，及表连接的问题，对于连接的字段，需要建立索引；
索引之性能体验和监测案例
1.Oracle Explain Plan结果中几个重要名次解释
（1）COST：
Oracle依据其查询优化器选择的执行路径估算的作开销（也称为成本、耗费）。开销并不决定表的访问作，其值不具备任何特定的度量单位，仅仅代表该
执行的加权值，是通过IO_COST和CPU_COST计算得出的(oracle文档中未公布算法）。
（2）CPU_COST： 查询优化器需要的CPU作开销估算值，其值与作所需的机器运转周期成正比。
（3）IO_COST：查询优化器需要的IO作开销估算值，其值与读取的数据块成正比。
1.Oracle Explain Plan结果中几个重要名次解释
（4）CARDINALITY： 查询优化器访问的数据行的估算基数。
（5）TIME：查询优化器消耗的时间估算值（单位为秒）。
2.删除索引
将表account_trade上的所有索引均删除掉
使用SQL语句
3.性能监测体验--使用SQL语句
MySQL的全文索引Fulltext Index | 包括ngram
InnoDB的全文索引使用反向索引的设计。反向索引存储了一个单词（word）列表，对于每个单词，都有一个文档的列表，来标识这个单词出现的地方。为了支持临近搜索（proximity search），每个单词的位置信息也以字节偏移的方式存储。
最前面的六个表包含了反向索引，它们被称作附属索引表（auxiliary index table）。当输入的表被索引（tokenized）后，每个的单词（亦称作“tokens”）会被携带其DOC_ID和位置信息插入到索引表中。根据单词个字符的字符集排序权重，在六个索引表中对单词进行完全排序和分区。
反向索引分区到六个附属索引表以支持并行的索引创建。默认有2个线程索引（Tokenize）、排序、插入单词和关联数据到索引表中。工作的线程的数量由 innodb_ft_sort_pll_degree 配置项控制的。对于大表的全文索引，可以考虑增加线程数量。
如果主表创建在 xx表空间，索引表存储在它们自己的表空间中。反之，索引表存储于其索引的表空间中。
前面例子展示的另外一种索引表被称作通用索引表，它们被用于全文索引的“删除处理（deletion handing）”和存储内部状态。不同于为每个全文索引都各自创建的反向索引表，这组表对特定表的所有全文索引都是共用的。
即使全文索引删掉了，通用索引（Common Index）也会被保留，当全文索引删除后，为这个索引而创建的FTS_DOC_ID列依然保留，因为移除FTS_DOC_ID列会导致重构之前被索引的表。管理FTS_DOC_ID列需要用到通用索引表。
为了防止大量并发读写附属表，InnoDB使用全文索引缓存去临时缓存最近的插入行。在存满并刷入磁盘之前，缓存的内容一直存储在内存之中，可以通过查询 INFORMATION_SCHEMA.INNODB_FT_INDEX_CACHE 表去查看最近缓存的插入行。
缓存和批处理刷新行为避免了对辅助索引表的频繁更新，频繁更新可能会在繁忙的插入和更新期间导致并发访问问题。批处理还避免了对同一个word的多次插入，化的减少了重复的条目。相同的word会先merge再刷入到磁盘中，而不是为每个word单独插入，这样提高了插入效率并且使得索引附属表尽可能的小。
全文索引缓存只缓存最近插入的行，查询时，已经刷入磁盘（附属索引表）的数据不会再回到索引缓存中。附属索引表中的内容是直接查询的，最终返回的结果返回前需要将附属索引表的结果和索引缓存中的结果合并。
InnoDB使用被称作DOC_ID的文件描述符，将全文索引中的单词与该单词在文档中的记录映射起来。映射关系需要索引表中的 FTS_DOC_ID 列。在创建全文索引时，如果没有定义 FTS_DOC_ID 列，InnoDB会自动的加入一个隐藏的 FTS_DOC_ID 列。下面是一个例子，
CREATE FULLTEXT INDEX ft_index ON xxxxxxxx(CONTEXT)
[2021-11-12 18:14:04] [HY000][124] InnoDB re众所周知在数据库中进行模糊查询是使用like关键字进行查询的，例如：building table to add column FTS_DOC_ID
在CREATE TABLE的过程中添加 FTS_DOC_ID 的时间成本要低于在已经有数据的表上建立全文索引。如果在表加载数据之前定义 FTS_DOC_ID 列，这个表和它的索引都不需要为了新增列而重新构建。如果你不需要考虑 CREATE FULLTEXT INDEX 的性能，可以让InnoDB为你创建 FTS_DOC_ID 列。InnoDB会新增一个隐藏的 FTS_DOC_ID 列，并且在 FTS_DOC_ID 上建立索引（FTS_DOC_ID_INDEX）。如果你想自行创建 FTS_DOC_ID 列，这个列必须定义为 BIGINT UNSIGNED NOT NULL 且命名为FTS_DOC_ID（全大写），如下例子：
如果你自行定义 FTS_DOC_ID 列的话，你需要负责管理这个列，避免空值（empty）或者重复值。 FTS_DOC_ID 的值是不能被重复利用的，所以也就是说 FTS_DOC_ID 的值是需要一直增加的。
或者，你可以在 FTS_DOC_ID 列上创建所必须的索引FTS_DOC_ID_INDEX（全大写）。
如果你没有创建FTS_DOC_ID_INDEX，InnoDB会自动创建。
在MySQL 5.7.13前，允许FTS_DOC_ID与的FTS_DOC_ID之间的间隔为10000，在MySQL 5.7.13及之后的版本中，这个允许的间隔为65535。
为了避免重新构建表，FTS_DOC_ID列在删除了全文索引之后依然被保留。
删除被索引文件的一个记录，可能会在附属索引表中产生非常多的小的删除项，在并发访问时，会产生热点问题。为了避免这个问题，每当被索引表中的记录被删除时，会将被删文档的DOC_ID记录在一个特别的 FTS__DELETED 表中，同时全文索引中已经索引了的记录依然被保存。在返回查询结果前，使用 FTS__DELETED 中的信息去过滤掉已经删除掉了的DOC_ID。这种设计的优势在于删除速度快且消耗低。不好的地方在于索引的大小不能随着记录的删除而立即减少。为了删除已删除记录在全文索引中的项，需要对被索引的表执行OPTIMIZE TABLE，配合[ innodb_optimize_fulltext_only=ON ]，去重构全文索引。
细节略，有例子：
全文搜索3、如果where条件中是OR关系，加索引不起作用只能看到已经提交了的数据。
你可以通过查询下面的INFORMATION_SCHEMA表，来或测试InnoDB的一些特殊文本处理。
默认的分词器不支持中文，不能检索到中文中的英文单词。
InnoDB默认的Stopwords：
select from rmation_schema.INNODB_FT_DEFAULT_STOPWORD;
SQL中的（保留关键字）：
Shell中的：for，while，echo
其他：###, , --,
被索引表数据量、索引表数据量
模糊匹配与严格匹配的性能距
需要将 innodb_optimize_fulltext_only 配置为ON，这里是否需要DBA在MySQL镜像中修改？
innodb_optimize_fulltext_only 设置为ON后，对系统有何影响需要评估。
innodb_optimize_fulltext_only
执行的时间、频率。
MySQL内建的全文检索解析器使用单词之间的空白作为分隔符以标识单词的头尾，但是这里有个限制，对于表意文字，它是没有单词分隔符的。为了解决这个限制，MySQL提供了支持中文、日语、韩语的 ngram 解析器。ngram解析器支持InnoDB和MyISAM。
Ngram是内建在服务中的插件，像其他自建在服务中的插件一样，服务启动时会自动加载它。全文检索的语法参考上面（ Section 12.10, “Full-Text Search Functions” ），这里只讨论一些不同的地方。除了单词的最小、长度配置项（[ innodb_ft_min_token_size ]innodb_ft_max_token_size,ft_min_word_len,ft_max_word_len，全文检索依赖一些配置项都是可以使用的。
Ngram默认索引的单词（token）的大小为2（ 2bigram ）。例如，索引的大小为2，Ngram解析器解析字符串“abc def”为四个单词元素(tokens)：“ab”, “bc”, “de” and “ef”。
ngram token size is configurable using the ngram_token_size configuration option, which has a minimum value of 1 and maximum value of 10.
作为只读变量, ngram_token_size 只能在启动配置或者配置文件中指定
与默认的解析器相不大，多了一句： xxx WITH PARSER ngram
Ngram在解析时去除空格，如
MySQL内建的默认全文检索解析器将单词与Stopword列表中的做对比，如果单词与Stopword列表中的元素相同的话，这个单词则不会被索引。对于Ngram解析器，Stopword的处理方式不同。Ngram解析器不排除与stopword列表中的条目相等的token，而是排除包含stopwords的token。例如，设 ngram_token_size=2 ，包含“a,b”的文档将被解析为 “a,” h和“,b”。如果将逗号（“,”）定义为停止字，则 “a,”和“,b”都将不会加入索引中，因为它们包含逗号。
例子：
默认Ngram解析器使用默认的Stopword列表，这里面含有英文的Stopword。如果需要中文的Stopword，需要你自己创建。
Stopword的长度超过 ngram_token_size则会被忽略。
有两个文档，一个包含“ab”，另一个包含“abc”。对于搜索文本“abc”将转换成“ab”，“bc”。
略。
For example, The search phrase “abc” is converted to “ab bc”, which returns documents containing “abc” and “ab bc”.
The search phrase “abc def” is converted to “ab bc de ef”, which returns documents containing “abc def” and “ab bc de ef”. A document that contains “abcdef” is not returned.
使用Ngram解析器好处是支持了中文的检索
mysql如何建立索引
` content` text,我们可以通过查看索引的属性来判断创建索引的方法。
查看索引的语法格式如下：
SHOW INDEX FROM <表名> [ FROM <数据库名>]
语法说明如下：
<表名>：指定需要查看索引的数据表名。
示例
使用 SHOW INDEX 语句查看《MySQL创建索引》一节中 tb_stu_2 数据表的索引信息，SQL 语句和运行结果如下所示。
mysql> SHOW INDEX FROM tb_stu_2G
1. row
Table: tb_stu_2
Non_unique: 0
Key_name: height
Seq_in_index: 1
Column_name: height
Collation:mysql> CREATE UNIQUE INDEX FTS_DOC_ID_INDEX on opening_lines(FTS_DOC_ID); A
Cardinality: 0
Sub_part: NULL
Null: YES
Index_type: BTREE
Comment:
Index_comment:
1 row in set (0.03 sec)
其中各主要参数说明如下：
参数 说明
Table 表示创建索引的数据表名，这里是 tb_stu_2 数据表。
Non_unique 表示该索引是否是索引。若不是索引，则该列的值为 1；若是索引，则该列的值为 0。
Key_name 表示索引的名称。
Seq_in_index 表示该列在索引中的位置，如果索引是单列的，则该列的值为 1；如果索引是组合索引，则该列的值为每列在索引定义中的顺序。
Column_name 表示定义索引的列字段。
Collation 表示列以何种顺序存储在索引中。在 MySQL 中，升序显示值“A”（升序），若显示为 NULL，则表示无分类。
Cardinality 索引中值数目的估计值。基数根据被存储为整数的统计数据计数，所以即使对于小型表，该值也没有必要是的。基数越大，当进行联合时，MySQL 使用该索引的机会就越大。
Sub_part 表示列中被编入索引的字符的数量。若列只是部分被编入索引，则该列的值为被编入索引的字符的数目；若整列被编入索引，则该列的值为 NULL。
Packed 指示关键字如何被压缩。若没有被压缩，值为 NULL。
Null 用于显示索引列中是否包含 NULL。若列含有 NULL，该列的值为 YES。若没有，则该列的值为 NO。
Comment 显示评注。