索引
mkdir mysql
tar -xvf mysqlxxxxx.tar -c myql
cd mysql
rpm -ivh .....rpm
yum install openssl-devel
systemctl start mysqld
gerp 'temporary password' /var/log/mysqld.log
mysql -u root -p
mysql>
show variables like 'validate_password.%'
set global validate_password.policy = 0;
set global validate_pawwword.length = 4;
alter user 'root'@'localhost' identified by '1234'
create user 'root'@'localhost' identified with mysql_native_password by '1234';
索引概述
高效获取数据的有序数据结构。数据库除了存储原始的数据外，还要存储索引这种数据结构。 通过这些数据结构来指向原始的数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
select * from emp where age > 35;无索引状况：
1、根据条件逐条查找，直到最后一条记录。【全表扫描，性能极低】
有索引状况：
1、对年龄二叉树【二叉排序树】进行维护。
优点：
1、提高数据检索效率，降低数据库IO成本。
2、通过索引对数据排序，降低数据怕徐成本，降低CPU消耗。
缺点：
1、索引也是占用空间的。
2、虽然提高了查询效率，但是要维护索引，降低了表更新的速度。
索引数据结构
二叉树缺点：顺序插入时，一直往左侧插入，形成一个单向链表，查询性能大大降低。数据量较大的情况下，层级较深，检索速度慢。
【红黑树，需要自平衡的二叉树。减少了层级】，但是数量大的情况下，检索也会较慢。
B树：
多路平衡查找树，不止两个子节点。指针比父节点的元素+1。【根据阶数，满阶就向上分裂。p68级有完整演示】
B+树：
所有的元素都会出现在叶子节点。上边的节点起到了索引的作用，叶子节点是用来存放数据的。
MySQL的B+树索引结构
哈希索引
采用一定的哈希算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储到hash表中。
1、先算出这张表当中，每一行数据的hash值，接下来再拿到name字段的所有值，针对于name字段通过它内部的hash函数去计算每一个name值它应该落在哪一个hash槽位上。
【hash表的槽位上存储：字段属性值+字段属性值所在的行的hash值。】
如果产生了哈希冲突，那么则在槽位上存储一个链表来记录新值。
特点：
1、只能用于等值匹配(=,in)，不能用户范围查询。【between < > ... 】
2、无法利用索引完成排序操作
3、查询效率较高，通常一次检索就够了，效率通常要高于B+树索引。
4、目前只有Memory存储引擎支持hash索引。【但是InnoDB引擎会在指定条件下，自动将B+树索引构建为hash索引】
思考：
为什么InnoDB引擎选了B+树索引，而不是其他的呢？
二叉树：顺序插入，形成了链表，搜索时造成性能下降。
红黑树：本周上也是一棵二叉树，变成了平衡树。
B树：叶子节点和非叶子节点都存放数据，都存放到一页中，但是页的大小是有限的，为16K，这将导致一页当中存储的数据更少，指针跟着减少，只能增加树的高度，导致性能降低。放到B+树中，能存储更多的数据，层数更少，所以性能更优。
B+树：相对于二叉树来说，层级更少，搜索效率更高。【无论如何，都到叶子节点中查找值，搜索效率稳定。并且是双向链表，搜索效率相对高一点，便于范围内搜索和排序】
hash索引：对于hash索引来说，只支持等值匹配。【不支持范围内匹配和排序操作。】
索引分类
聚集索引选取规则：
1、如果存在主键，主键索引就是聚集索引
2、如果不存在主键，将使用第一个唯一索引作为聚集索引
3、如果表没有主键，或者是没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
select * from emp where ename='pshdhx';
回表查询：
1、先根据二级索引查询到name值的位置，在其叶子节点下有改行的id值
2、根据id值再跑到聚集索引中，然后找到改行的值。
InnoDB的B+树索引有多高
索引语法
--创建索引
create [unique|fulltext] index index_name on table_name(index_col_name,....);
create index idx_user_name on tb_user(name);
--查看索引 加上\G之后，由原来的一行转为1列，看的清楚，不会错行。
show index from table_name\G;
--删除索引
drop index index_name on table_name;
SQL性能分析
SQL执行频率
【是插入，修改，删除为主，还是查询为主】
查看当前数据库的访问频次：是不是以增删改为主。
show [session|global] status 命令可以提供服务器状态信息
show global status like 'com_ _ _ _ _ _ _'; 7个字符
慢查询日志
慢查询日志默认没有开启，需要在/etc/my.cnf中开启配置
show variables like 'slow_query_log';
show_query_log=1
long_query_time=2配置完毕之后，通过以下指令启动MySQL服务器进行测试，
systemctl restart mysqld查看慢查询日志记录的信息/var/lib/mysql/localhost-slow.log
tail -f localhost-slow.log只要尾部有内容追加进去，就可以输出出来。
profile详情
show profiles能够在做SQL优化时，帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作。
select @@have_profiling
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling；
select @@profiling;
set profiling = 1;
show profiles; --查看各个语句的执行情况[时间]
show profile for query query_id;
--比如第16条语句执行慢,可以看看时间浪费在什么地方
show profile for query 16;
show profile cpu for query 16; --查看每个执行阶段cpu的耗费情况
explain执行==desc执行
explain select * from user where id = 1;
desc select * from user where id = 1;
1、id
select查询的序列号。id相同【联表查询会产生多个id】，执行顺序从上到下。id不同（子查询），值越大，越先执行。
2、select_type
表示select的类型，常见的取值有simple（简单表，不用联表和子查询）、primary（主查询，即外层的查询）、union、subquery（select /where之后 包含的子查询）等。
3、type
表示连接类型。性能由好到差的连接类型为 null、system、const、eq_ref、ref、range、index、all
null：不访问任何表，就是null，在业务系统中不可能出现。
system：访问系统表，才是system
const：访问主键，或者是唯一索引，才是const
ref：当我们使用非唯一性的索引查询时，会出现ref
index：用了索引，但是也会带索引进行扫描，遍历整个索引数
all：全表扫描。
4、possible_key：显示可能应用在这张表上的索引，一个或者多个
5、key：展示实际使用到的索引，如果为null，则没有使用索引
6、key_len：标识索引使用的字节数，该值为索引字段最大可能长度，并非实际长度。
7、rows：执行查询的行数。预估值。
8、filtered：查询返回的行数，占读取总行的百分比。值，自然越大越好。
索引使用
验证索引效率：
1000万条数据，某字段没有索引，以该字段为条件，查询效率很慢。
create index idx_sku_sno on sku(sno);此条指令执行很慢，因为需要为1000万条数据，创建索引，构造索引二叉树。
最左前缀法则
如果索引了多列【联合索引】，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。【索引中的最左侧字段得存在，跟放的位置没有关系。】
例如：三个字段加上了同一个索引，要想索引生效，必须从左到右，不能跳过字段。只筛选1 、筛选1 2、筛选1 2 3，索引生效。如果只有1和3，那么3的索引是不生效的。3 21 也生效，1存在就生效。
范围查询
联合索引中，出现范围查询> <号时，范围右侧的列索引失效。
如果是>=，那么列索引也生效。
索引列运算
不要在索引列上进行运算操作【包含函数运算操作】，索引将失效。
explain select * from user where substring(phone,10,2) = '15';
这样的操作，索引将会失效，进行全表扫描。
字符串不加引号
虽然字符串不加单引号是能查询出来的，但是索引是不生效的。
模糊查询索引
如果仅仅是尾部模糊查询匹配，索引在不会失效。但是，如果是头部模糊查询匹配，索引失效。
例如：'软件%' 索引生效
'%工程' 索引失效。
or-索引
用or分割开的条件，如果or前的条件中的列有索引，而后边的列中没有索引，那么涉及到的索引都不会被用到。
只有两侧都有索引的时候，所以才会被用到。
数据分布影响
如果MySQL评估使用索引比全表更慢，则不使用索引。
例如：1 2 3 4.。。 都是顺序递增的。 筛选条件是 >=1 ，所以MySQL认为走索引还不如直接走全表扫描效率高呢，故使用explain没用到索引。
【绝大多数数据满足，就不走索引。否则，就走索引】
SQL提示
如果某个字段有多个索引，例如有联合索引和普通索引，MySQL会自动选择一个合适的索引进行查找。
但是我们也可以人为用哪个索引。
use index【建议】 、ignore index 、force index【强制】
explain select * from user use index(idx_user_name) where name = '软件工程';
覆盖索引
尽量使用覆盖索引【查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到】，减少使用select *
Extra：Using where using index 【性能高】不需回表（根据name找主键，根据主键找rowid）。
Extra：Using inedex condition【性能低】查询的时候，使用了索引，但是需要回表查询。
联合索引是属于二级索引的，二级索引当中，叶子节点挂的就是id值，所以查询id不需要回表，但是查询name，需要回表。
id name password status
select id name password form emp where name="pshdhx";需要在name和password建立联合索引，id就在叶子节点上。不用回表查询。
前缀索引
当字段类型为字符串（varchar、text等）时 ，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘IO，影响查询效率。
此时可以将字符串的一部分前缀，建立索引。这样可以大大节约索引空间，从而提高索引效率。
create index idx_tablename_idxName on tableName(column(n));只需要指定一个n，就可以建立前缀索引。n代表该字段的前几个字符。
回表查询之后，拿出row，再看看该行的值是否与过滤条件的值完全相同。
单列索引&联合索引
1、select id ,phone , name from emp where phone="xxx" and name="xxxxx";
通过explain，可以看到只走了phone的索引。但是查询不到name的值，所以会回表查询。
但是如果使用了联合索引，就不会产生回表查询了，所以建议使用联表索引。
索引设计原则
1、针对于数据量较大，且查询较为频繁的表建立索引。
2、针对于常作为查询条件，where，order by ，group by操作的字段建立索引或联合索引。
3、选择区分度高的字段建立索引，尽量建立唯一索引。区分度越高，索引的使用效率越高。
4、字符串类型字段，建立前缀索引。
5、尽量使用联合索引，减少单列索引。联合索引很多时候可以覆盖索引，避免回表，提高查询效率。
6、控制索引的数量，索引越多，维护索引结构的代价越大，会影响增删改的效率。
7、如果要建立索引的列不能为null值，建议使用非空约束。有利于索引查询。
SQL优化
插入数据
insert 优化
1、执行批量插入insert into emp values(1,'pshdhx'),(2,'pshdhx2')
2、手动提交事务。否则，执行insert之前开启事务，执行之后关闭事务，性能较低。
3、主键顺序插入。顺序插入的性能要高于乱序插入。因为MySQL的组织结构。
大批量数据插入：load指令
<<<<<<< HEAD
=======
主键优化
数据组织方式：
在InnoDB存储引擎当中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。【index organized table IOT】
就像是二级索引，主键从左到右存放。
列分页：
页可以为空，也可以填充一半，也可以填充100%，固定大小为16K。段固定大小为1M，有64页。每页包含了2-N行数据（如果一行数据大，会行溢出），根据主键排列。
主键顺序插入：
主键乱序插入：
1号页满，1号页数据分裂，形成3号页，然后分裂的数据和新插入的数据到3号页，页面指针改为1 3 2。
页合并：
当删除一行记录时，实际上记录并没有被物理删除，只是被标记为删除。当标记的数量达到页的50%时，InnoDB会尝试有没有合并页的可能，优化空间使用。
合并页的阈值，可以自己设置，在创建表或者创建索引时指定。
主键设计原则
1、满足业务需求的情况下，尽量降低主键的长度。
在二级索引中挂的是主键， 主键比较长，二级索引占得空间比较大。 在搜索的时候占用较多的磁盘IO。
2、插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
3、尽量不要使用UUID做主键或者是其他自然主键，如身份证号码。
4、业务操作时，尽量避免对主键的修改。 修改主键还需要修改索引的结构。
order by优化
1、Using filesort：通过表的索引或者是全表扫描，读取满足条件的数据行，然后在排序缓冲区sort Buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序，都叫file sort排序。
2、Using Index：通过有序索引顺序扫描，直接返回有序数据。不需要额外排序，操作效率高。
优化掉filesort
1、对order by后边的字段建立索引，如果是单字段，建立单字段索引。如果是多字段，建立联合索引。
2、注意：索引默认是asc排序，desc排序时，extra显示需要倒序索引 。一个字段asc 一个字段desc，会显示这种情况。
3、如果我就是想使用desc，可以创建索引时指定。
create index idx_user_age_phone_asc_desc on user(age asc,phone desc);
以上说的这一切，前提是**覆盖索引。**多字段排序时，也遵循最左前缀法则。
show variables like 'sort_buffer_size'; 默认256K如果需要可以调大一点。
group by优化
explain select profession,count(*) from user group bu profession;extra:Using temporary：使用到了临时表，效率较低。
如果对profession创建了联合索引（profession_age_status），Extra优化为了 Using index
如果不遵循最左前缀法则，Extra：Using index ; Using temporary
explain select age,count(*) from user where profession = '软件工程' group by age;虽然group by没有最左前缀法则，但是where里边有，所以Extra还是Using index
limit优化
select * from sku limit 1000000,10;
select * from sku limit 10000000,10; 越往后越耗时。【19秒】
此时，MySQL排序前100000010记录，仅仅返回10000000,到10000010的记录，其他记录丢弃，查询排序 的代价非常大。
select id from sku order by id limit 10000000,10; 先查询10条的id【10秒】
select * from sku where id in (select id from sku order by id limit 10000000,10) ;【语法报错 子查询中不允许有limit】
select s.* from sku , (select id from sku order by id limit 10000000,10) tmp where s.id = tmp.id ;【10秒，比起直接分页查询，提高了近9秒】
优化思路：
一般分页查询时，通过创建覆盖索引，能够比较好的提高性能。可以通过通过覆盖索引加子查询形式进行优化。
count优化
MyISAM存储引擎，把一个表的总行数记录到了磁盘上，因此执行count(*)的时候会直接返回总数，效率很高。
InnoDB，它在执行count(*)的时候，需要把数据一行一行地从存储引擎里边读出来，然后计算个数。
优化思路：自己计数。
count(*)：InnoDB引擎并不会把数字全部取出来，而是专门做了优化，不取值，服务层直接按照行进行累加。
count(主键)：主键不可能是null，遍历整张表，把每行的主键取出来，返回给服务层，服务层进行累加操作。
count(字段)：遍历整张表，取字段，还有判断是不是null。看看not null约束。
count(1)： InnoDB会遍历整张表，但是不取值。服务层对于返回的每一行，放一个数字1，然后按行进行累加。什么数字都行，不一定是1。
效率：count(字段)<count(主键)<count(1)≈count(*)
所以尽量使用count(*)；
update优化
session1： update user set name="pshdhx" where id = 1;[开启事务begin，提交commit]
session2：update user set name = "pshdhx2" where id = 3;[开启事务begin，提交commit]
此时是没有问题的，InnoDB锁住的是行。
但是： 如果存在
update user set name="pshdhx3" where name="pshdhx";[开启事务begin，提交commit]
update user set name = "pshdhx4" where id = 3;[开启事务begin，提交commit]
此时就会出问题，因为name没有索引，行锁升级为表锁。
总结：
update时，并发事务时，要根据索引字段进行更新，否则就会锁住表。
