SQL-结构化查询语言

SQL基本语法

SQL语句可以单行或者多行书写，以分号结尾

可以使用空格或者缩进，来增强语句的可读性

MySQL不区分大小写，建议关键字使用大写

SQL语句分类

DDL：数据定义语言，对库表结构的操作。

DML：数据操作语言，对表记录进行更新（增删改）。

DQL：数据查询语言，对表的记录进行查询。

DCL：数据控制语言，对用户进行创建及授权。

索引

索引，是帮助MySQL高效获取数据的数据结构。

索引，目的在于提高查找效率，可以类比字典。

索引，是排好序的快速查找的数据结构。

索引，会影响where和orderby（查找和排序）。

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构；

这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，

这种数据结构就是索引。

默认一遍都是B+数索引。

索引优势劣势

劣势：

索引也是要占用空间的。

虽然索引提供了查询速度；但是降低了写入（插入更新）速度。

索引只是提供查询效率的一个因子，需要花大量时间研究优化。

优势：

提高数据检索效率，降低数据库的IO成本。

通过索引对数据排序，降低数据排序成本，降低了cpu的消耗。

索引分类

单值索引，即一个索引只包含单个列，一个表可以有多个单值索引。

唯一索引，索引列的值必须唯一，但允许有空值。

符合索引，即一个索引包含多个列。

添加索引：

添加主键： alter table tb1 add primary key (column_list)

添加唯一索引：alter table tb1 add unique index_name (column_list)

添加一般索引：alter table tb1 add index index_name(column_list)

添加全文索引：alter table tb1 add fulltext index_name(column_list)

索引结构：BTree索引

创建索引的情况

主键自动建立唯一索引

频繁作为查询条件的列，应该创建索引

查询中与其他表关联的字段，外键关系建立索引

频繁更新的字段不适合创建索引

where条件里用不到的字段，不要创建索引

单键索引与组合索引，高并发情况下，倾向于选择组合索引

查询中，需要排序的字段

查询中，统计或者分组的字段

不需要创建索引的情况

表数据很少

经常增删改的列

数据重复且分布均匀。

例如：10w行数据，某列A，只有两种状态且每种状态分布为50%；那么一般对A建索引，不会提高查询效率。

索引的选择性是指索引列中不同指的数量与表数据量的比值。如果一个表有10w条记录，表索引列有9w9999个不同值；那么这个索引的选择性就是：9w9999 / 10w 约等于 0.999。一个索引的选择性越接近于1，这个索引的效率就越高。

索引失效

1、使用!= 或者 <> 导致索引失效

例如：

SELECT a.* FROM coupon_state_manager a WHERE a.customer_id != 101           

2、类型不一致导致的索引失效

（1）字段varchar，条件为int，会导致失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_phone = 13756494953           

（2）字段int，条件字符型，不会导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = '101'           

3、函数导致的索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE DATE(receive_date) = '2020-02-17 14:08:31'           

4、运算符导致的索引失效

对列进行了（+，-，*，/，!）, 那么都将不会走索引

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id -1 = 100           

5、OR引起的索引失效

（1）or连接的字段，都建立了索引，不会失效

（2）or连接的字段，如果其中一个没建立索引，则会是整个索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id = 101 or coupon_id = 2           

6、模糊搜索导致索引失效

（1）'%abc' '%abc%'会导致索引失效（要查询的字段，都在索引中，不会失效）

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '%李%'           

（2）'abc%'不会导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name LIKE '李%'           

7、NOT IN、NOT EXISTS导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE customer_id NOT IN (100,101)           

8、IS NOT NULL会导致索引失效

EXPLAIN SELECT * FROM coupon_state_manager  WHERE  customer_name IS NOT NULL           

9、复合索引

Mysql从左到右的使用索引中的字段；

一个查询可以只使用索引中的一部分，但只能是最左侧部分。

例如索引是key index （a,b,c）。

可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。

当最左侧字段是常量引用时，索引就十分有效。

创建复合索引时，应该仔细考虑列的顺序。

对索引中的所有列执行搜索或仅对前几列执行搜索时，复合索引非常有用；

仅对后面的任意列执行搜索时，复合索引则没有用处。