Mysql索引（二叉树，红黑树，B+树 ，B树，聚簇

可以使用B+Tree索引的查询类型：

1.全值匹配：精确所有索引列，如：姓wang，名xiaochun，年龄30

2.匹配最左前缀：即只使用索引的第一列，如：姓wang

3.匹配列前缀：只匹配一列值开头部分，如：姓以w开头的

4.匹配范围值：如：姓ma和姓wang之间

5.精确匹配某一列并范围匹配另一列：如：姓wang,名以x开头的只访问索引的查询

B+Tree索引的限制：

1.如不从最左列开始，则无法使用索引，如：查找名为xiaochun，或姓为g结尾

2.不能跳过索引中的列：如：查找姓wang，年龄30的，只能使用索引第一列

特别提示：

1.索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引

2.为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

1.5 Hash索引

---

hash索引，有些引擎会自己建立（不需要手工建立）

Hash索引：基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持

适用场景：只支持等值比较查询，包括=, <=>, IN()

不适合使用hash索引的场景

不适用于顺序查询：索引存储顺序的不是值的顺序

不支持模糊匹配

不支持范围查询

不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

1.6空间数据索引R-Tree（ Geospatial indexing ）

---

MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访

问，常用于做地理数据存储，使用不多

InnoDB从MySQL5.7之后也开始支持

1.7全文索引(FULLTEXT)

---

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎

InnoDB从MySQL 5.6之后也开始支持

1.8聚簇和非聚簇索引

---

区别：数据和索引是否存储在一起

存放在一起：聚簇索引

不在一起：非聚簇索引

叶子节点既有索引又有数据

主键 就是聚簇索引

1.9聚簇和非聚簇索引，主键和二级索引

---

InnoDB（聚簇索引）

···主键 就是聚簇索引（存放的是数据）主键次序就是索引次序

···非主键索引-----二级索引：利用主键，最终找到数据（存放的是主键和索引）

**MyISAM（非聚簇索引）**两个索引里面存放的都是数据

索引

冗余和重复索引：

冗余索引：（A），（A，B）

重复索引：已经有索引，再次建立索引

索引优化策略：

1.独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧

2.左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值

3.多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

4.选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

1.只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

2.尽量使用短索引，如果可以，应该制定一个前缀长度

3.对于经常在where子句使用的列，最好设置索引

4.对于有多个列where或者order by子句，应该建立复合索引

5.对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

6.尽量不要在列上进行运算（函数操作和表达式操作）

7.尽量不要使用not in和<>操作

SQL语句性能优化

查询时，能不要用就不用*，尽量写全字段名

大部分情况连接效率远大于子查询

多表连接时，尽量小表驱动大表，即小表 join 大表

在有大量记录的表分页时使用limit

对于经常使用的查询，可以开启缓存

多使用explain和profile分析查询语句

查看慢查询日志，找出执行时间长的sql语句优化

二 .管理索引

==========================================================================

创建索引：

CREATE [UNIQUE] INDEXindex_name ON tbl_name (index_col_name[(length)],…);

ALTER TABLE tbl_name ADD INDEX index_name(index_col_name);

help CREATE INDEX;

注意：创建标的时候指定primary key就会自动生成索引

create index idx_age on students(age);

对name创建索引，取前10个字段

create index idx_name on students(name(10));

建立复合索引（只有当前面的有了范围，才能查询后面的）

create index ind_name_age on students(name,age);

删除索引：

DROP INDEX index_name ON tbl_name;

ALTER TABLE tbl_name DROP INDEX index_name(index_col_name);

drop index ind_name on students;

查看索引：

SHOW INDEXES FROM [db_name.]tbl_name;

优化表空间：

OPTIMIZE TABLE tb_name;

查看索引的使用

记录索引

SET GLOBAL userstat;

查看是否使用了索引

SHOW INDEX_STATISTICS;

查看是否用了索引

explain（分析查询语句）工具

---

通过EXPLAIN来分析索引的有效性

EXPLAIN SELECT clause

获取查询执行计划信息，用来查看查询优化器如何执行查询

输出信息说明：

参考 https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

id: 当前查询语句中，每个SELECT语句的编号

复杂类型的查询有三种：

简单子查询

用于FROM中的子查询

联合查询：UNION

注意：UNION查询的分析结果会出现一个额外匿名临时表

select_type：

---

简单查询为SIMPLE

复杂查询：

SUBQUERY 简单子查询

PRIMARY 最外面的SELECT

DERIVED 用于FROM中的子查询

UNION UNION语句的第一个之后的SELECT语句

UNION RESULT 匿名临时表

三. 并发控制（锁）

=============================================================================

锁粒度：

表级锁:整个表枷锁MyISAM

行级锁：只锁一行（InnoDB）

锁：

读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞

写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和它锁

实现

存储引擎：自行实现其锁策略和锁粒度

服务器级：实现了锁，表级锁，用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁

显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

显式使用锁

LOCK TABLES 加锁

tbl_name [[AS] alias] lock_type

[, tbl_name [[AS] alias] lock_type] …

lock_type: READ ， WRITE

UNLOCK TABLES 解锁

FLUSH TABLES [tb_name[,…]] [WITH READ LOCK]

关闭正在打开的表（清除查询缓存），通常在备份前加全局读锁（如果后面不加表明，全局锁）

SELECT clause [FOR UPDATE | LOCK IN SHARE MODE]查询时加写或读锁

加读锁（自己 别人----只能读）共享锁

lock tables students read;

加读锁（自己都可以 别人都不行）独占锁

lock tables students write;

解锁

ublock tables；

全局锁（不能改，不能增加用户）----用于备份

flush tables with read lock;

常用

加锁导致别人无法（修改表）

1.show processlist;查看进程

直接杀掉该进程kill id；

这样就会把锁删掉

mysql详解

---

查看： show processlist;

显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果：

select * from information_schema.processlist

id: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。

User: 就是指启动这个线程的用户。

Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。

DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。

Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释

Time: 表示该线程处于当前状态的时间。

State: 线程的状态，和 Command 对应，下面单独解释。

Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist。

Command 的值：